Database Independent Development using ASP.NET 2.0 - Developing a simple data access class to work with the factory: methods
(Page 5 of 6 )
This section explains the skeleton provided in the previous section. Before looking at the methods, if you can observe the declaration of the class DataAccess, it is derived (or inherited) from the DBFactory class. That means all the methods of the DBFactory class can be accessed as part of the DataAccess class.
In simple terms, we can get Connection, Command and DataAdapter objects from the base class DBFactory itself. Furthermore, you can also instantiate and work with the DBFactory class directly!
The following is the method which is used to execute any SQL statement in the database:
Public Sub SQLExecute(ByVal sql As String)
Dim cmd As DbCommand = GetDBCommand()
cmd.CommandText = sql
cmd.CommandType = CommandType.Text
Try
cmd.Connection.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception(ex.Message & "-->" & sql)
Finally
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
End If
cmd.Dispose()
End Try
End Sub
In the above method, I am simply receiving the Command object from the GetDBCommand method available in the DBFactory class. Once the Command object is available, the process of executing the SQL statement is exactly the same as the traditional ADO.NET 1.1 approach.
The following is the method which retrieves a set of rows in the form of a data table:
Public Function GetDataTable(ByVal sqlSELECT As String) As DataTable
Dim dt As New DataTable
Dim da As DbDataAdapter = GetDBDataAdapter()
Dim cmd As DbCommand = GetDBCommand()
cmd.CommandText = sqlSELECT
cmd.CommandType = CommandType.Text
da.SelectCommand = cmd
Try
da.Fill(dt)
Catch ex As Exception
Throw New Exception(ex.Message & "-->" & sqlSELECT)
Finally
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
End If
cmd.Dispose()
da.Dispose()
End Try
Return dt
End Function
In the above function, you can observe that I used both DataAdapter and Command objects (from the methods of DBFactory class) to fill the DataTable object.
Next: Developing a simple data access class to work with the factory: methods extended >>
More ASP.NET Articles
More By Jagadish Chaterjee
