| As mentioned on Day 8, "Beginning to Build Databases," there are five general steps to interacting with data in ASP.NET pages: Create a database connection object. Open the database connection. Populate a DataSet with the desired data. Set up a DataView to display data. Bind a server control to the DataView. You examined the fifth item in detail yesterday, so you'll concentrate on the first four today. A few of these steps can be done in multiple ways (with different objects), so all of them will be covered in turn. Before you move on, though, you need to examine the information needed to connect to a database. Connection Information Before you can use ADO.NET in your ASP.NET pages to talk to databases, you have to provide specific information about the database you're interested in accessing. This important information includes where the database is located, what type of database it is (for example, MS Access, SQL Server, or Oracle), what version it is, and so on. This information is supplied to ADO.NET using a connection string that you build manually. (Don't worry, it's not as bad as it sounds.)  | Once upon a time, the easiest way to provide this information for your database was to build a System Data Source Name (DSN) file. You should already have one of these files, which provides default information for some data stores installed by your operating system. These files contained all the information required by a connection string for your applications to use. While they were convenient, they weren't very efficient. There are better mechanisms to connect to databases in ASP.NET, but you may still occasionally hear the term DSN connection, so it's good to know. |
A very common way to connect to a database nowadays is to specify your connection string via a DSN-less connection. This requires you to specify all the necessary information in the connection string in your application. Using your banking user database as an example, the connection string would look similar to this: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\ASPNET\data\banking.mdb" For a SQL database, the connection string would look something like the following: "Provider=SQLOLEDB.1;Initial Catalog=Northwind; Data Source=MyServer;User ID=sa;" This string tells ADO.NET which provider to use and the location of the database. There are a number of other parameters you may specify as well, such as UID and PWD for a username and password used to connect to the database. The ones specified here are the most common, however, so we won't go into the others yet. The OleDbConnection Object Now that you've seen how to set up the connection with the connection string, let's try opening your database. The System.Data.OleDb.OleDbConnection object (or System.Data.SqlClient.SqlConnection object for SQL databases) does just this. Listing 10.2 shows an example. Listing 10.2 Opening a Database Connection with the OleDbConnection Object 1: Dim strConnectionString as string = _ 2: "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 3: "Data Source=c:\ASPNET\data\banking.mdb" " 4: Dim Conn as New OleDbConnection( _ 5: strConnectionString) 6: Conn.Open() 7: ... 8: Conn.Close() And that's it! You now have an open connection to your database. The connection string is declared on line 1, and the OleDbConnection object on line 4 uses it to connect to the database. Line 6 uses the Open method to open the connection to the database, and line 7 uses Close to close it. Always remember to close the connection when you're through with it. Most of the time, this is all you will ever need to do with an OleDbConnection object: open and close a database connection. You'll learn a few more tricks in Day 12, "Employing Advanced Data Techniques." The OleDbCommand Object Now that you've connected to the database, you can issue commands against the database, such as filling a DataSet or updating records. Figure 10.4 shows a partial object model for the OleDbCommand object. Figure 10.4. The OleDbCommand object model.
Commands to databases are in the form of SQL statements, so you should be familiar with them already. All you have to do is assign a SQL statement to your OleDbCommand object, as shown Listing 10.3. Listing 10.3 Creating and Initializing an OleDbCommand Object 1: 'set up SQL statement 2: Dim strSQL as string = "SELECT * FROM tblUsers" 3: 4: 'create object and set properties 5: Dim objCmd as New OleDbCommand() 6: objCmd.Connection = Conn 7: objCmd.CommandText = strSQL 8: 9: 'or 10: 'Dim objCmd as New OleDbCommand(strSQL, Conn) 11: 12: 'or 13: 'Dim objCmd as New OleDbCommand(strSQL, _ 14: strConnectionString)  | The OleDbCommand object provides a number of ways for you to initialize it by specifying different parameters. The parameters it will accept are the SQL statement and the OleDbConnection object (shown on line 10), or the connection string that will build the OleDbConnection object (shown on line 13). Simply specifying the command won't do much, however. You must execute the command with one of the Execute methods. The one you'll use depends on what you plan on doing with the returned data. For example, to fill an OleDbDataReader (see the next section for information on this object), use |
'create a DataReader dim objReader as OleDbDataReader objReader = objCmd.ExecuteReader To execute a query that doesn't return any data, use objCmd.ExecuteNonQuery You'll examine more Execute methods as we progress through the book. The OleDbDataReader Object The OleDbDataReader is a lightweight object that allows simple access to a data store. Essentially, it's a streaming DataSet. So why use the OleDbDataReader when you have the DataSet? When you retrieve data from a database, a DataSet grabs all the information and holds it in memory until you tell it otherwise. This allows you to do some pretty neat things with this disconnected data store. For instance, you can modify it without worrying about other users messing up the data, and you can transform it into different formats. However, after you start returning large amounts of data from a database, you run into memory limitations because the entire DataSet is stored in memory. Couple that with thousands of users accessing the database at the same time (with each user having his own DataSet), and you run into major problems. (Note that such an example is an extreme case, but it helps to illustrate the need for a smaller object than the DataSet.) The OleDbDataReader puts only one row into memory at a time. It streams the data from the data store as you request it. This can prevent a lot of memory problems, and consequently, it results in increased performance as well. Unfortunately, because the data is streamed, the OleDbDataReader doesn't provide as much functionality as a DataSet. It's read-only, and you can't go back to records you've already passed. After you've filled your OleDbDataReader, it's very easy to loop through the records. Simply call the Read method. Listing 10.4 shows an example. Listing 10.4 Looping Through Records in an OleDbDataReader Object 1: <%@ Page Language="C#" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: void Page_Load(Object Sender, EventArgs e) { 7: OleDbConnection objConn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.  4.0;Data Source= c:\\ASPNET\\data\\banking.mdb"); 8: 9: OleDbCommand objCmd = new OleDbCommand ("select * from tblUsers", objConn); 10: 11: OleDbDataReader objReader; 12: 13: objConn.Open(); 14: objReader = objCmd.ExecuteReader(); 15: 16: while (objReader.Read()) { 17: Response.Write(objReader.GetString(0) + "<br>"); 18: } 19: objConn.Close(); 20: } 21: </script> 22: 23: <html><body> 24: 25: </body></html> | Lines 7 9 should look familiar; you create an OleDbConnection and an OleDbCommand and execute a SQL statement. (Note that the connection string uses two backslashes in the file path; this is because a single backslash in C# has special meaning, so we need to escape it with another.) These few steps will become very familiar to you very quickly. Line 11 creates a new OleDbDataReader object. The Fill method of the OleDbDataAdapter object automatically opened and closed a database connection for you, but when working with an OleDbDataReader, you must perform this step manually, as shown on lines 13 and 19. Line 14 executes the SQL statement and streams the returned data through the OleDbDataReader object. Lines 16 18 loop through each record of the OleDbDataReader. |
The Read method automatically advances to the next record, stopping when there are no more. Line 17 retrieves the first field from each record using the GetString method, which appropriately returns a string value (I'll discuss this method more in a moment). This string is then displayed using Response.Write. The output is shown in Figure 10.5. Figure 10.5. The Read method allows you to loop through an OleDbDataReader's records.
You can test if there are any more records programmatically by examining the HasMoreRows property, which will return true if there are more records and false otherwise. Caution It's very important that you also close the OleDbDataReader when you're through with it. Closing the associated OleDbConnection object will also do this for you.
The OleDbDataReader also has a series of Get methods that return data from the fields as their native type (GetByte, GetInt32, GetString, and so on). Use these commands to avoid having to cast your data when it comes out of the OleDbDataReader. | Do | Don't | | Do use an OleDbDataReader when performance is critical and you only need to display database information. | Don't use a OleDbDataReader if you need to manipulate or modify the data before or after displaying it to a user. | |
Update, Insert, and Delete Because the OleDbDataReader is read-only, you cannot use it to modify data. Rather, you have to use appropriate SQL statements with the OleDbCommand object: Update, Insert and Delete. None of these statements returns any records like the Select statement, however. There-fore, you should use the ExecuteNonQuery method, which returns an integer that tells you how many records were affected by the statement Listing 10.5 shows an example. Listing 10.5 Executing Non-Select Statements with the OleDbCommand Object 1: dim I as integer 2: 3: 'set up SQL statement 4: Dim strSQL as string = "DELETE FROM tblUsers" & _ 5: " WHERE UserID = 5" 6: 'create object and set properties 7: Dim objCmd as New OleDbCommand(strSQL, Conn) 8: 9: I = objCmd.ExecuteNonQuery() Because UserID is your identity field, this statement should affect zero records or one record, depending on whether there was a row with that ID. You can examine the variable I to see whether your statement was effective. The Update and Insert statements will perform similarly (see Day 8 for more discussion about these SQL statements). The OleDbDataAdapter Object Just as the OleDbCommand object dealt with OleDbDataReaders, the OleDbDataAdapter deals with DataSets. The OleDbDataAdapter's main function is to pull data from a data store into a DataSet, and to push DataSet data back into the data store. The model for this object is outlined in Figure 10.6. The four command methods are used to delete, insert, select, and update data in a DataSet. The TableMappings collection defines how tables and columns from data sources are matched to DataSets. Figure 10.6. The OleDbDataAdapter object model.
The OleDbDataAdapter object is very powerful. Although its main purpose is simply to retrieve data, it can also create entirely new tables from existing data or spit data out as XML. We'll get to the more advanced features later, but first let's examine the basics. Creating an OleDbDataAdapter object is similar to creating an ADOCommand object, as shown in Listing 10.6. Listing 10.6 Creating an OleDbDataAdapter Object 1: 'set up SQL statement 2: Dim strSQL as string = "SELECT * FROM tblUsers" 3: 4: 'create object and set properties 5: Dim objCmd as New OleDbDataAdapter() 6: objCmd.SelectCommand.Connection = Conn 7: objCmd.SelectCommand.CommandText = strSQL 8: 9: 'or 10: 'Dim objCmd as New OleDbDataAdapter _ 11: ' (strSQL, Conn) 12: 13: 'or 14: 'Dim objCmd as New OleDbDataAdapter( _ 15: ' strSQL, strConnectionString) | As shown in Figure 10.6, the OleDbDataAdapter has four command methods. Each of these command methods is actually an OleDbCommand object, with its own Connection and CommandText properties, as shown on lines 6 and 7. The command object method that's set when you provide an SQL statement during the initialization of the object (lines 10 and 14) is the SelectCommand method. If you want to specify other command methods, such as an Insert, you must do so manually. |
Note Remember that when you use the Update, Insert, and Delete command methods, you're not actually altering the underlying data store, but rather the DataSet, which is disconnected from the data store. The changes are reflected on the data source only after the OleDbDataAdapter.Update method is called.
Populating DataSets Let's take a look at an example of populating a DataSet, and then you'll explore the various methods. See Listing 10.7. Listing 10.7 Filling a DataSet 1: dim strConnectionString as String = _ 2: "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 3: "Data Source=c:\ASPNET\data\banking.mdb" 4: Dim ds as DataSet = New DataSet("MyDataSet") 5: Dim strSQL as String = "SELECT * FROM tblUsers" 6: 7: Dim objCmd as new OleDbDataAdapter(strSQL, _ 8: strConnectionString) 9: 10: objCmd.Fill(ds, "Users") | On line 4, you create an empty DataSet named "MyDataSet", and on line 7, you create a new OleDbDataAdapter object with its Select command initialized to the SQL string you created on line 5. The result of this code snippet is a DataSet that contains a DataTable called "Users", which in turn contains all the records from the tblUsers table. |
How is this possible, since you only created an empty DataSet? The Fill method of the OleDbDataAdapter object takes any schema information (tables, columns, primary key definitions, and so on) from the data source that doesn't already exist in the DataSet and creates it automatically. Thus, it fills the "Users" DataTable with all the columns from the data source. Similarly, if you've already created a DataTable with some of the columns, the FillDataSet method will create the rest. If you create all the columns, this method will only fill the table with data. This is a very powerful feature of OleDbDataAdapter, and as you'll see in the next section, it only gets better. Updating Data Sources Yesterday you learned how to manipulate the data in a DataSet by accessing its fields and values through its collections. What happens, though, after you modify data in this way? After you change the data, you can use the OleDbDataAdapter's associated OleDbCommand objects to push those changes back to the data store. These commands concern only the data you've already changed. For instance, you can't simply specify an Insert statement and expect a new record to appear in the data source. The Insert statement must reference a new row you've already created in the DataSet. Listing 10.8 shows an example of setting the OleDbDataAdapter's UpdateCommand property. Listing 10.8 Manipulating a DataSet with the OleDbDataAdapter Object 1: sub Page_Load(Sender as Object, e as EventArgs) 2: 'create connection 3: dim Conn as new OleDbConnection( _ 4: "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 5: "Data Source=c:\ASPNET\data\banking.mdb") 6: 7: 'create DataSet and OleDbDataAdapter 8: Dim ds as new DataSet("MyDataSet") 9: Dim objCmd as new OleDbDataAdapter("SELECT * FROM " & _ 10: "tblUsers WHERE UserID < 10", Conn) 11: 12: 'fill DataSet 13: objCmd.Fill(ds, "tblUsers") 14: 15: 'change some data 16: ds.Tables("tblUsers").Rows(2)(3) = "ASPVille" 17: 18: dim dr as DataRow = ds.Tables("tblUsers").NewRow() 19: dr(0) = "Greg" 20: dr(1) = "Smith" 21: dr(2) = "434 Maple Apt B" 22: dr(3) = "Minneapolis" 23: dr(4) = "MN" 24: dr(5) = "12588" 25: dr(6) = "5189876259" 26: ds.Tables("tblUsers").Rows.Add(dr) 27: 28: 'provide SQL command and active connection 29: objCmd.UpdateCommand = new OleDbCommand 30: objCmd.UpdateCommand.CommandText = "Update tblUsers " & _ 31: "SET City='ASPVille' WHERE UserID=3" 32: objCmd.UpdateCommand.Connection = Conn 33: 34: 'provide another SQL command and active connection 35: objCmd.InsertCommand = new OleDbCommand 36: objCmd.InsertCommand.CommandText = "Insert INTO " & _ 37: "tblUsers (FirstName, LastName, Address, City, " & _ 38: "State, ZIP, Phone) VALUES ('Greg', 'Smith', " & _ 39: "'434 Maple Apt B', 'Minneapolis', 'MN', '12588', " & _ 40: "'5189876259'" 41: objCmd.InsertCommand.Connection = Conn 42: 43: end sub | In this listing, you first create your OleDbConnection and OleDbDataAdapter on lines 3 10, and fill your DataSet using the SQL select statement on line 13. This is similar to the process when using an OleDbCommand object. You make some changes to the DataSet on lines 16 26: You edit a single value on line 16, and add a completely new row on lines 18 26. On line 29, you create a new OldDbCommand object to use with the OleDbDataAdapter's Update command. On line 30, you specify an update statement to use when you update the data source. Similarly on line 36, you specify an Insert statement. Notice how the Insert and Update commands reference the data in the modified rows only. These statements don't actually do anything yet. They will be executed against the data source when you call the Update method. |
Note Note that these commands don't really alter any data. They merely provide commands to instruct ADO.NET how you want to push the data that you've already changed. For example, if you hadn't made any changes on lines 16 26, the command methods would do nothing, regardless of the SQL statements you specified.
The Update method of OleDbDataAdapter pushes changes in a DataSet back to the data source. It uses the Insert, Update, and Delete commands that you've specified to alter the data source. Let's modify Listing 10.8 to include the Update command. Add the following code after line 41: objCmd.Update(ds, "tblUsers") The method's parameters are the DataSet object variable, which contains the modified data, and the table from which you want to pull data. You can leave out the table parameter, but that requires a table mapping which we'll discuss in the next section. This might seem like a real pain having to change the data manually, and then building a SQL statement showing that you've changed it. Luckily, ADO.NET can help. If these commands aren't specified, and if any rows are actually changed in the DataSet, the OleDbDataAdapter object can generate the commands for you automatically, using an OleDbCommandBuilder object. For example, let's examine the following code snippet: dim ds as new DataSet("MyDataSet") dim objCmd as new OleDbDataAdapter _ ("select * from tblUsers", Conn) dim objAutoGen as New OleDbCommandBuilder(objCmd) ... 'retrieve and 'modify some data ... objCmd.Update(ds, "tblUsers") The first three lines are the same as usual. On the fourth line, however, you create a new OleDbCommandBuilder object specifying the OleDbDataAdapter as a parameter. This object, after the Update method is called, examines the differences between the DataSet and the data source, and generates SQL statements to make the former match the latter, taking a lot of headache out of the process. Look at the following line: ds.Tables("tblUsers").Rows(3).Delete When coupled with the OleDbCommandBuilder object and the Update method, this will generate the delete SQL statement: DELETE FROM tblUsers WHERE UserID = 4 The OleDbDataAdapter uses the primary key values to determine which row should be deleted. In this case, the fourth row's primary key "UserID" (indicated by Rows(3)) is 4. The auto-generate feature will work only if a primary key or unique column is present. You might be wondering why you need to specify a select statement if the command builder will build the statements for you. This is because your command builder needs an initial statement to tell it what kind of data and what fields to expect. Without the initial statement, the builder wouldn't know how to form the proper SQL statements. In other words, the statement you use to retrieve data from the data source will affect the rows you can update. If your initial SQL statement uses "select * ..." as shown in the previous code snippet, the DataSet that you are working with must also have all of the columns from the data source. The following code snippet demonstrates this, and will cause an error: 'global variables dim ds as new DataSet("MyDataSet") sub CreateData() 'create connection object here 'fill dataset dim objCmd as new OleDbDataAdapter _ ("select UserID, FirstName, LastName from tblUsers", Conn) objCmd.Fill(ds, "tblUsers") 'add new row to users table 'do some other modifications end sub sub UpdateData() dim objCmd as new OleDbDataAdapter _ ("select * from tblUsers", Conn) dim objAutoGen as New OleDbCommandBuilder(objCmd) objCmd.Update(ds, "tblUsers") end sub Assume you call the CreateData method first to populate your DataSet, for example, to display in a DataGrid. Then after the data has been modified, you call the UpdateData method to insert the data into the database from your DataSet. In this situation, you'll receive an error. The OleDbCommandBuilder will try to update every field in the table because the select * statement in the UpdateData method says so. However, the DataSet only has three fields, as shown in the CreateData method; you can't update columns that aren't there in the DataSet. Instead, match the two SQL statements together; so, in UpdateData, the statement should read: select UserID, FirstName, LastName from tblUsers The OleDbCommandBuilder must make a trip to the data source to figure out how to build the commands; it needs table schema information this is the reason for the initial select statement. If bandwidth is a critical factor, or you don't want to make extraneous trips to the data source (to improve performance, for instance), don't use an OleDbCommandBuilder object; build your SQL statements by hand instead. Mappings Table and column mappings allow you to map one table or column in the data store to one in the DataSet. This mapping will be used by ADO.NET when data is moved from one data source to another. This allows you to use columns or tables with different names in each location, or even to map two completely different columns or tables to each other. Figure 10.7 illustrates this concept. Figure 10.7. Mappings allow you to link seemingly unrelated columns.
These mappings are stored in the TableMappings collection of the OleDbDataAdapter object. Let's take a look at an example, shown in Listing 10.9. Listing 10.9 Creating Table Mappings for the banking Database 1: Dim ds As DataSet = New DataSet() 2: Dim Conn as New OleDbConnection _ 3: "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 4: "Data Source=c:\ASPNET\data\banking.mdb")) 5: Dim objCmd As New OleDbDataAdapter _ 6: ("SELECT * FROM tblUsers", Conn) 7: 8: 'add mappings 9: objCmd.TableMappings.Add("Table", "Users") 10: With objCmd.TableMappings(0).ColumnMappings 11: .Add("UserID", "ID") 12: .Add("LastName", "Lastname") 13: .Add("FirstName", "Firstname") 14: .Add("Phone", "Phone") 15: .Add("Address", "StreetAddress") 16: .Add("City", "City") 17: .Add("State", "State") 18: .Add("Zip", "Zip") 19: End With 20: objCmd.FillDataSet(ds) | This example uses the banking database you created on Day 8, and maps the columns in the tblUsers table to columns in a DataSet. |
Lines 1 6 should be pretty familiar by now. You create and initialize your DataSet, connection, and OleDbDataAdapter objects. On line 9, you add a new mapping from the table in the data store to a Users table in your DataSet. The first parameter is the source table (where you're pulling the data from), and the second parameter is the destination table (where the data is going to). Since you supplied a select statement with the OleDbDataAdapter declaration, ADO.NET knows that you want to use the results of that statement as the source table. The name Table that you specify on line 9 is a special name used by ADO.NET. When the Fill or Update command is issued without specifying a DataSet table parameter, ADO.NET looks for a mapping called Table to determine where the data should come from. For example, let's say you're using the mapping from Listing 10.9 and call the following: objCmd.Update(ds) ADO.NET will pull the data from the Users table in your DataSet because the Table mapping instructs it to. Normally, you would need to use the following to perform the update: objCmd.Update(ds, "Users") On lines 10 20, you simply add ColumnMappings to the TableMapping that you just defined. These mappings provide easier-to-use column names for a few of the columns and map the others as they are. Mappings serve purposes other than simply providing friendlier column names. When you start getting more familiar with SQL statements, you'll notice that some of them don't return fields or named columns some simply return a value. These situations aren't very likely to come up during ASP.NET development, though. We'll examine them more in Day 12. | 
