Class Frameworks in VB .NET

Class Frameworks in VB .NET - Defining Data Access
(Page 2 of 5 )

Displaying a list of products on a web page will require us to run a database query and then bind the resulting DataReader to an ASP.NET Web control, such as ASP Repeater or Data Grid. A common approach would be to create an ADO.NET database connection and command objects, execute appropriate SQL code or Stored procedure call and use the DataReader object to show data on the page. This code can reside either within the page itself or inside the code-behind component.

We’ll take a different approach here. Lets plan ahead for building a much larger application against the same database schema, and even for different (maybe client/server) applications that may need to manipulate the same data. We then need to build something highly re-usable, which brings us to a class framework.

Accessing a database is such a common function that you will likely be using it in almost every page on your site. Therefore, it makes sense to separate and reuse it via a shared component, rather than copy and paste the same code.

Any component in .NET is a class, just like in Java. So we’ll create a new class named MyDatabase inside our own AspnetObjects namespace:

Namespace AspnetObjects
  Public Class MyDatabase

Creating a “shared” database access method in MyDatabase class allows any class within the Web application to make a database call without creating a new instance of the class:

Public Shared Function GetReader (p_strQuery As String) As SqlDataReader

Before we proceed with code for GetReader function, one more “helper” function is required – our shared class needs to know the database connection string. We will use ASP.NET web.config configuration setting and make it a read-only property of MyDatabase class:

Public Shared ReadOnly Property DbUrl() As String
  Get
    Return ConfigurationSettings.AppSettings("DbConnUrl")
  End Get
End Property

The GetReader() function itself is very straightforward – it uses DbUrl property mentioned above and ADO.NET classes to connect to database, execute the query (passed as parameter) and return the resulting SqlDataReader object back to the caller:

connection = New SqlConnection(DbUrl)
command = New SqlCommand(p_strQuery, connection)
connection.Open()
dataReader = command.ExecuteReader()
Return dataReader

That’s it. Now that code for our reusable component is ready, compile it into a library, then drop the DLL into Web application /bin folder and use it from any ASP.NET page in the application. Here is the code from productList.aspx:

dtrProducts = MyDatabase.GetReader(strQuery)
rptProducts.DataSource = dtrProducts
rptProducts.DataBind()

Hey, you don’t even need code behind in this case! ASP Repeater Web control named “rptProducts” will handle HTML formatting, while the data is being delivered as a SqlDataReader directly from MyDatabase class.

The Product List page already executes successfully at this point. However, the shared database access component is not yet complete. Since we can use “GetReader” function to retrieve data from database, lets create a similar function to execute an “Update” query when required. This way all database communications will flow through the shared class.

We’ll call this function ExecNonQuery, and it will return the number of records updated by the SQL statement. Once again, the function is just a wrapper for ADO.NET database access:

Public Shared Function ExecNonQuery (p_strSQL As String) As Int32
  ……..
  command = New SqlCommand( p_strSQL, connection )
  ……..
  intResult = command.ExecuteNonQuery()
  ……..
  Return intResult
End Function

This logic takes care of the first page, which displays the product records to customers in read-only format (see screenshot). Next page however, will require an update functionality, which sets us up another object-oriented logic twist.
blog comments powered by Disqus