#Region "Constructor"
Private Sub New()
End Sub
#End Region
Private Shared instance As New DataAccess
''' <summary>
''' GetCustomerDataSet1 - Use of SqlDataAdpater and SqlConnection along with Stored procedure
''' and sql parameters to Return a DataSet. This option is good, if you
''' are returning multiple datatable in dataset.
''' </summary>
''' <param name="custID"></param>
''' <returns>Customer's DataSet with table name Customer</returns>
''' <remarks>when Fill method is called,SqlDataAdpater Opens
''' and Closes Connection internally, so no need to open connection explicitly.
''' You can open the connection explicitly if you will be using the same connection
''' for multiple DataTables on same SQLDataAdpater
''' </remarks>
Public Shared Function GetCustomerDataSet1(ByVal custID As Int32) As DataSet
Dim resultDataSet As New DataSet("Customers")
Using connection As New SqlConnection(ConnectionString)
Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)
With dataAdapter
.SelectCommand.CommandType = CommandType.StoredProcedure ' Always mention it for stored proc
.SelectCommand.CommandTimeout = 60 ' you can change based on the need
.SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Fill(resultDataSet, "Customer")
End With
End Using
End Using
Return resultDataSet
End Function
''' <summary>
''' GetCustomerDataSet2 - Use of SqlDataAdpater, SqlConnection and SqlCommand along with Stored procedure
''' and sql parameters to Return a DataSet. This option is good, if you
''' are returning multiple datatable in a dataset.
''' </summary>
''' <param name="custID"></param>
''' <returns>Customer's DataSet with table name Customer</returns>
''' <remarks>when Fill method is called,SqlDataAdpater Opens
''' and Closes Connection internally, so no need to open connection explicitly.
''' You can open the connection explicitly if you will be using the same connection
''' for multiple DataTables on same SQLDataAdpater
''' </remarks>
Public Shared Function GetCustomerDataSet2(ByVal custID As Int32) As DataSet
Dim dataAdapter As New SqlDataAdapter
Dim resultDataSet As New DataSet("Customers")
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure ' Always mention it for stored proc
.CommandTimeout = 60 ' you can change based on the need
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
dataAdapter.SelectCommand = command
dataAdapter.Fill(resultDataSet, "Customer")
End With
dataAdapter = Nothing
End Using
End Using
Return resultDataSet
End Function
''' <summary>
''' GetCustomerDataTable1 - Use of SqlDataAdpater and SqlConnection along with Stored procedure and sql parameters
''' to Return a DataTable,If you are expecting data from only one table, datatable is best option.
''' </summary>
''' <param name="custID"></param>
''' <returns>Customer's DataTable with table name Customer</returns>
''' <remarks>SqlDataAdpater Opens and Closes Connection internally when Fill method is called
''' </remarks>
Public Shared Function GetCustomerDataTable1(ByVal custID As Int32) As DataTable
Dim resultDataTable As New DataTable("Customer")
Using connection As New SqlConnection(ConnectionString)
Using dataAdapter As New SqlDataAdapter("dbo.Usp_GetCustomer", connection)
With dataAdapter
.SelectCommand.CommandType = CommandType.StoredProcedure ' Always mention it for stored proc
.SelectCommand.CommandTimeout = 60 ' you can change based on the need
.SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Fill(resultDataTable)
End With
End Using
End Using
Return resultDataTable
End Function
''' <summary>
''' GetCustomerDataTable2 - Use of SqlDataReader, SqlConnection and SqlCommand along with Stored procedure
''' and sql parameters to Return a DataTable, If you are expecting data from only one
''' table, datatable is best option.
''' </summary>
''' <param name="custID"></param>
''' <returns>Customer's DataTable with table name Customer</returns>
''' <remarks>SqlDataReader requires an open connection
''' </remarks>
Public Shared Function GetCustomerDataTable2(ByVal custID As Int32) As DataTable
Dim resultDataTable As New DataTable("Customer")
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 60
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Connection.Open()
Using reader As SqlDataReader = .ExecuteReader(CommandBehavior.CloseConnection)
resultDataTable.Load(reader)
End Using
End With
End Using
End Using
Return resultDataTable
End Function
''' <summary>
''' GetCustomer - Use of SqlDataReader, SqlConnection and SqlCommand along with Stored procedure
''' with Sql Parameter to Return a Customer record. This examples reads the data in most efficient way
''' based on ordinal number rather column name. It also avoids boxing/unboxing which happends during
''' reading data on coulmn name and converting into proper datatype.
''' </summary>
''' <param name="custID"></param>
''' <returns>Customer</returns>
''' <remarks>You should always read data based on ordinal number and avoid boxing/unboxing.</remarks>
Public Shared Function GetCustomer(ByVal custID As Int32) As DTOCustomer
Dim record As New DTOCustomer
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_GetCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 60 ' you can change based on the need
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Connection.Open()
End With
Using reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim custIDOrdinal As Integer = reader.GetOrdinal("Cust_ID")
Dim custNameOrdinal As Integer = reader.GetOrdinal("Cust_Name")
Dim custDOBOrdinal As Integer = reader.GetOrdinal("Cust_DOB")
Dim custAddressOrdinal As Integer = reader.GetOrdinal("Cust_Address")
Dim custDateCreatedOrdinal As Integer = reader.GetOrdinal("Date_Created")
Dim custDateModifiedOrdinal As Integer = reader.GetOrdinal("Date_Modified")
If reader.Read Then
'Option -1 **** Based on Column ordinal number - More efficient code
record.CustID = reader.GetInt32(custIDOrdinal) ' Cust ID is Primary Key so it can not be NULL
If Not reader.IsDBNull(custNameOrdinal) Then record.CustName = reader.GetString(custNameOrdinal)
If Not reader.IsDBNull(custDOBOrdinal) Then record.CustDOB = reader.GetDateTime(custDOBOrdinal)
If Not reader.IsDBNull(custAddressOrdinal) Then record.CustAddress = reader.GetString(custAddressOrdinal)
If Not reader.IsDBNull(custDateCreatedOrdinal) Then record.DateCreated = reader.GetDateTime(custDateCreatedOrdinal)
If Not reader.IsDBNull(custDateModifiedOrdinal) Then record.DateModified = reader.GetDateTime(custDateModifiedOrdinal)
'Option -2 *****Based on column name - Less efficient code THUS COMMENTED
'record.CustID = CInt(reader.Item("Cust_ID"))
'record.CustName = reader.Item("Cust_Name").ToString
'If Not IsDBNull(reader.Item("Cust_DOB")) Then record.CustDOB = CDate(reader.Item("Cust_DOB"))
'record.CustAddress = reader.Item("Cust_Address").ToString
'If Not IsDBNull(reader.Item("Date_Created")) Then record.DateCreated = CDate(reader.Item("Date_Created"))
'If Not IsDBNull(reader.Item("Date_Modified")) Then record.DateModified = CDate(reader.Item("Date_Modified"))
End If
End Using
End Using
End Using
Return record
End Function
''' <summary>
''' InsertTestData - Use of Inline SQL Statement with parameterized Query for Improved performance
''' and importantce of Prepare method of SqlCommand Object
''' </summary>
''' <param name="custName"></param>
''' <param name="custDOB"></param>
''' <param name="custAddress"></param>
''' <returns>No of rows affected</returns>
''' <remarks></remarks>
Public Shared Function InsertTestData(ByVal custName As String, ByVal custDOB As DateTime, ByVal custAddress As String) As Int32
Dim sqlString As String = "INSERT dbo.tbl_Customer(Cust_Name,Cust_DOB,Cust_Address) VALUES (@Name,@DOB,@Address)"
Using conConnection As New SqlConnection(ConnectionString)
Using cmdCommand As New SqlCommand(sqlString, conConnection)
With cmdCommand
.CommandType = CommandType.Text
.CommandTimeout = 60 ' you can change based on the need
.Parameters.Add(New SqlParameter("@Name", SqlDbType.VarChar, 50)).Value = custName
.Parameters.Add(New SqlParameter("@DOB", SqlDbType.DateTime)).Value = custDOB
.Parameters.Add(New SqlParameter("@Address", SqlDbType.VarChar, 100)).Value = custAddress
.Connection.Open()
.Prepare() ' Important for the performance of inline SQL
Return .ExecuteNonQuery()
End With
End Using
End Using
End Function
''' <summary>
''' InsertCustomer - Inserts a new customer
''' </summary>
''' <param name="custName"></param>
''' <param name="custDOB"></param>
''' <param name="custAddress"></param>
''' <returns>Auto Generated Cust ID</returns>
''' <remarks>It also use the Return Value Paramater</remarks>
Public Shared Function InsertCustomer(ByVal custName As String, ByVal custDOB As DateTime, _
ByVal custAddress As String) As Int32
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_InsertCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 60 ' The value you can change based on the need
.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
.Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
.Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
.Connection.Open()
.ExecuteNonQuery()
Return CInt(.Parameters("@Return").Value)
End With
End Using
End Using
End Function
''' <summary>
''' UpdateCustomer - Updates a Customer Record based on Cust ID
''' </summary>
''' <param name="custID"></param>
''' <param name="custName"></param>
''' <param name="custAddress"></param>
''' <returns>If Updated Successully then passed Cust ID else -1 if failed</returns>
''' <remarks></remarks>
Public Shared Function UpdateCustomer(ByVal custID As Int32, ByVal custName As String, ByVal custDOB As DateTime, _
ByVal custAddress As String) As Int32
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_UpdateCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 60 ' The value you can change based on the need
.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Parameters.Add(New SqlParameter("@CustName", SqlDbType.VarChar, 50)).Value = custName
.Parameters.Add(New SqlParameter("@CustDOB", SqlDbType.DateTime)).Value = custDOB
.Parameters.Add(New SqlParameter("@CustAddress", SqlDbType.VarChar, 100)).Value = custAddress
.Connection.Open()
.ExecuteNonQuery()
Return CInt(.Parameters("@Return").Value)
End With
End Using
End Using
End Function
''' <summary>
''' DeleteCustomer - Deletes a Customer record on Cust ID
''' </summary>
''' <param name="custID">integer</param>
''' <returns>If Deleted Successully then passed Cust ID else -1 if failed</returns>
''' <remarks></remarks>
Public Shared Function DeleteCustomer(ByVal custID As Int32) As Int32
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand("dbo.Usp_DeleteCustomer", connection)
With command
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 60 ' The value you can change based on the need
.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
.Parameters.Add(New SqlParameter("@CustID", SqlDbType.Int)).Value = custID
.Connection.Open()
.ExecuteNonQuery()
Return CInt(.Parameters("@Return").Value) ' If fails then -1 else passed cust id
End With
End Using
End Using
End Function
' The following 5 methods you should use only in exceptional case
''' <summary>
''' GetResultAsDataReader- A generic method to return a SqlDataReader to calling method.
''' </summary>
''' <param name="sqlString"></param>
''' <returns>SqlDataReader</returns>
''' <remarks>It is very important to close SqlDataReader after its use in calling method</remarks>
Public Shared Function GetResultAsDataReader(ByVal sqlString As String) As SqlDataReader
Dim connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand(sqlString, connection)
With command
.CommandType = CommandType.Text
.CommandTimeout = 60
.Connection.Open()
Return .ExecuteReader(CommandBehavior.CloseConnection)
End With
End Using
End Function
''' <summary>
''' GetResultAsDataTable - A generic method to return a datatable on a SQL String
''' </summary>
''' <param name="sqlString"></param>
''' <returns>DataTable</returns>
''' <remarks>SQL Statement must be returning one result set.
''' Inline SQL Statements are slower then Stored Procedure
''' You should avoid using this unless you have exceptional case
''' </remarks>
Public Shared Function GetResultAsDataTable(ByVal sqlString As String) As DataTable
Dim resultTable As New DataTable
Using connection As New SqlConnection(ConnectionString)
Using command As New SqlCommand(sqlString, connection)
With command
.CommandType = CommandType.Text
.CommandTimeout = 60
.Connection.Open()
Using reader As SqlDataReader = .ExecuteReader(CommandBehavior.CloseConnection)
If reader.HasRows Then resultTable.Load(reader)
End Using
End With
End Using
End Using
Return resultTable
End Function
''' <summary>
''' GetResultAsDataSet - A Generic Method to return a dataset on a SQL String
''' </summary>
''' <param name="sqlString"></param>
''' <returns>DataSet</returns>
''' <remarks>Inline SQL Statements are slower then Stored Procedure
''' Inline SQL Statements are slower then Stored Procedure
''' You should avoid using this unless you have exceptional case
''' </remarks>
Public Shared Function GetResultAsDataSet(ByVal sqlString As String) As DataSet
Dim resultDataSet As New DataSet
Using connection As New SqlConnection(ConnectionString)
Using dataAdapter As New SqlDataAdapter(sqlString, connection)
With dataAdapter
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandTimeout = 60
.Fill(resultDataSet)
End With
Return resultDataSet
End Using
End Using
End Function
''' <summary>
''' RunSQLStatement - A generic method to execute a sql statement
''' </summary>
''' <param name="sqlString"></param>
''' <returns>No of rows affected</returns>
''' <remarks>Inline SQL Statements are slower then Stored Procedure
''' You should avoid using this unless you have exceptional case
''' </remarks>
Public Shared Function RunSQLStatement(ByVal sqlString As String) As Int32
Using connection As New SqlConnection(ConnectionString)
Using dataAdapter As New SqlDataAdapter(sqlString, connection)
With dataAdapter
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandTimeout = 60
.SelectCommand.Connection.Open()
Return (.SelectCommand.ExecuteNonQuery())
End With
End Using
End Using
End Function
''' <summary>
''' ConnectionString - A generic Property to get connection string
''' </summary>
''' <value>none</value>
''' <returns>connection string</returns>
''' <remarks>One place to make any change in connection string call</remarks>
Public Shared ReadOnly Property ConnectionString() As String
Get
Try
Return WebConfigurationManager.ConnectionStrings.Item("ExampleDB").ConnectionString
'In ASP.NET Use ConfigurationManager instead of WebConfigurationManager
Catch exc As Exception
Return ""
End Try
End Get
End Property
''' <summary>
''' CloseConnection- A generic method to close SqlConnection
''' </summary>
''' <param name="connection">SqlConnection</param>
''' <remarks></remarks>
Public Shared Sub CloseConnection(ByRef connection As SqlConnection)
If Not connection Is Nothing AndAlso connection.State = ConnectionState.Open Then connection.Close()
connection = Nothing
End Sub
''' <summary>
''' CloseDataReader - A generic method to close SqlDataReaer
''' </summary>
''' <param name="reader">SqlDataReaer</param>
''' <remarks></remarks>
Public Shared Sub CloseDataReader(ByRef reader As SqlClient.SqlDataReader)
If Not reader Is Nothing AndAlso Not reader.IsClosed Then reader.Close()
reader = Nothing
End Sub
End Class