We now know how to access data and put it into a simple, readable form in our ASP .Net web pages, but there's so much more! In this article I am going to explain several more key objects that allow us to efficiently access our data in .Net. I will be using the OleDb flavor of ADO .Net classes for those of you who are not working with SQL Server, but just remember that if you are a SQL Server user, the difference is only in the naming of the classes you use and the connection string (refer to Part I of this series if you're not sure what I'm talking about). So let's get to work.
The objects we are going to look at are the OleDbCommand (a little more in depth than last time), OleDbDataAdapter, and DataSet objects. As in my previous articles, I am going to give you all of the code (click here to download a zip file of the source code) for a small data program that I have written. This program allows us to add records to the Authors table of the pubs database. After we add a record, it is displayed on the screen in a DataGrid object along with the rest of the records in the table. Mull it over and then continue on where I'll enumerate its functionality. There are some ASP .Net issues that we are going to have to discuss en route to understanding what is going on here, so hopefully you will learn a lot from this example.
This is our program's interface:
To begin with, note that in the second line of the program source code, that I had to import the OleDb namespace. We are accessing an MS Access data file, so we are unable to use the SQLClient classes.
Within the <script> tags of the file, resides our VB .Net code and procedures. There are five sub procedures, the first of which is 'Page_Load'. This procedure is the same as in my previous article's example, with one main difference. You will see the IsPostBack property of the Page object is being checked. ASP .Net pages are designed to post forms to themselves, so this boolean property is used to determine whether our program (page) is being accessed for the first time, or if we are posting information back to this page on the server. This can be a quite difficult concept to grasp, and it has bitten me in the rear more than once since I started developing with ASP .Net. The functionality of the Page_Load event goes like this: If the page is being loaded for the first time, we are going to call our GetConnection() and FillDataGrid() functions, both of which are defined by me, the developer. If this is a post-back operation, we execute no code from within the Page_Load event.
(Above the page load procedure, you will find that I declared our connection object and its connection string. This simply makes the connection object global so that we can use it in multiple sub procedures.)
The GetConnection() sub procedure simply builds a new connection out of the global connection we created at the beginning of our VB .Net code and opens it. Now we can use it in our FillDataGrid() procedure (shown below) where we find two new ADO .Net objects: the OleDbDataAdapter and the DataSet. The OleDbDataAdapter is a member of the OleDb namespace and is the "gateway" for our data to get into the DataSet object. The DataSet is a member of the System.Data namespace and is similar to the ADO recordset, but with tons of new functionality that we have never seen in ADO data access classes before.
1 Sub FillDataGrid()
2 Dim strSQL as String = "SELECT au_fname as [First Name], " & _
3 "au_lname as [Last Name], address as Address, city as City, " & _
4 "state as State, zip as ZIP FROM Authors ORDER BY au_lname"
6 Dim myDataAdapter as New OleDbDataAdapter(strSQL, myConnection)
8 Dim myDataSet as New DataSet()
9 myDataAdapter.Fill(myDataSet, "Authors")
11 myDataGrid.DataSource = myDataSet.Tables("Authors")
13 End Sub
Now when I say "gateway" for our data, I mean that the OleDbDataAdapter object fills our dataset for us. On line 5, we provide it the SQL query (from lines 2-4) and our connection object, which we opened in the Page_Load event. Next, we declare a new DataSet object on line 8, and finally we use the its Fill method on line 9 to populate the DataSet. We send in two parameters with the fill command, the first being the targeted DataSet, "myDataSet", and second, the name we wish to use to refer to the virtual table we are creating, "Authors". The last piece of code in lines 11-12 simply binds the DataSet to our DataGrid object. On line 11, the DataGrid's data source property is set to the "Authors" table in the tables collection of the DataSet object. Pretty nifty, eh?
In essence, the DataSet object is a virtual relational database. Not only can we define multiple tables within the object, but we can also enumerate relationships between tables. While we won't be going into that much depth in this article, be aware that this is possible and is a large advantage of the DataSet over ADO 2.6's recordset object. The OleDbDataAdapter's functionality looks like a trivial, extra step in this small program, but where its real power lies is in its data source update capabilities. After building a DataSet and making changes to it, the OleDbDataAdapter object can build appropriate update, delete and insert SQL statements to bring the data source up to date on changes made to the data within the DataSet object. This type of operation is much more difficult using ADO 2.6.
Okay now lets cover the AddAuthor() procedure which I have listed below. This procedure is called when a user clicks the 'Add Author' ASP:Button on the form. This is coded into the onClick property of the ASP server control (notice the Runat="server" property):
The Page_Load event code is bypassed when this happens because IsPostBack is true. Then, within this procedure, I am using an OleDbCommand object to insert a new record, and then again calling the FillDataGrid() procedure followed by a ClearForm() procedure that I have created to clear the field values entered into the form by the user.
1 Sub AddAuthor(ByVal sender As System.Object, ByVal e As System.EventArgs)
2 Dim intRecordsAffected as Integer
5 Dim strSQL as String = "INSERT INTO Authors(au_id,au_fname,au_lname,
address,city,state,zip) values('" & _
6 au_id.Text & "','" & _
7 au_fname.Text & "','" & _
8 au_lname.Text & "','" & _
9 address.Text & "','" & _
10 city.Text & "','" & _
11 state.Text & "','" & _
12 zip.Text & "')"
14 Dim myCommand as New OleDbCommand(strSQL, myConnection)
16 intRecordsAffected = myCommand.ExecuteNonQuery()
18 txtRecAff.Text = intRecordsAffected & " record added successfully."
21 End Sub
On line 14, "myCommand" is declared as an OleDbCommand object. I pass in a SQL INSERT string that I created in lines 5-12, (the values are simply pulled from the form input fields with the "Text" property, similar to a traditional VB windows form) and my connection object that was opened again on line 3 with the GetConnection() sub procedure. You may have noticed that I declared an integer variable, "intRecordsAffected" on line 2; this will hold the number of records affected by the execution of my OleDbCommand object with the ExecuteNonQuery() method on line 16. ExecuteNonQuery() simply executes a command that we know is not going to return any records, like an update, insert or delete.
After the insert command is complete, we bind the DataGrid to the Authors table in the FillDataGrid() procedure and clear all of the form fields with the ClearForm() call. All ClearForm() does is set each form field's text property equal to an empty string.
As I keep preaching, ADO .Net makes our lives a lot easier than traditional ADO does, and gives us more functionality to boot. The examples I am providing right now are farily simple, and are just to get you familiar with the classes and their associated methods and properties, but once we get into some more complex examples, the added-value of these data access methods will become increasingly more evident.
So now we have seen in action the following ADO .Net objects:
Along with some ASP .Net objects, properties and controls: