The DataAdapter and DataSet Objects
| The DataAdapter and DataSet objects offer a great deal of flexibility. They not only let you retrieve data in a flexible manner, but they also give you the opportunity to change data and update the database. This section talks about these two important objects. The DataSet ObjectThe DataSet object is the core component of ADO.NET. The DataSet object is best described as a collection of disconnected recordsets that contain a hierarchy of table, row, and column data. A major difference between DataSet objects and a group of disconnected recordsets is that the DataSet internally keeps track of the relationships between tables. With a DataSet, the information requested is cached on the client and disconnected from the server. As a result, the DataSet has no knowledge of its data sources, so separate objects have to pass information between the DataSet and the data source. Using the disconnected model minimizes resources for open connections and server load. A typical use of a DataSet would include the following steps:
Another important distinction of the DataSet from its ADO Recordset predecessor is the DataSet's capability to track changes as they are made to its internal data and provide error handling on a row-by-row basis. In the previous ADO Recordset model, changes were made and passed back to the server. The recordset would either succeed or fail. Now, with the DataSet model, row errors can be trapped before the data is passed back to the database.
NOTE: One of the most challenging feats for a developer working with ADO.NET will be using the disconnected DataSet model. ADO.NET uses this model for two main reasons: First, scalability reduces the demands placed on database servers, and second, it has XML support. Using XML enables DataSet objects to be independent of databases or a query language, leaving the data bound to a user-defined interface. DataAdapterThe DataAdapter object is much like the Command object, but it is used specifically to access and manipulate information in a DataSet. The Command object has only one CommandText property, whereas the Data Adapter contains four properties: SelectCommand, InsertCommand, DeleteCommand and UpdateCommand. The DataAdapter also contains two methods for receiving and sending data to a database. The Fill() method populates a DataSet, whereas the Update() method sends data from the DataSet back to the data source. The Fill() MethodThe Fill() method uses two parameters to populate a DataSet: the DataSet object and the name of the table to associate with the data begin loaded. Listings 4.3 through 4.6 are examples of using a DataAdapter object and filling a DataSet with the Fill() method. Listing 4.3 SQL Implementation Visual Basic Example Dim objConnection as new _ SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") Dim objDataAdapter as new _ SqlDataAdapter("SELECT * FROM Authors",objConnection) Dim objDataSet as new DataSet objDataAdapter.Fill(objDataSet,"Authors") Listing 4.4 SQL Implementation C# Example SqlConnection objConnection = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs"); SqlDataAdapter objDataAdapter = new SqlDataAdapter("SELECT * FROM Authors",objConnection); DataSet objDataSet = new DataSet(); objDataAdapter.Fill(objDataSet,"Authors"); Listing 4.5 OLE DB Implementation Visual Basic Example Dim objConnection as new _ OleDbConnection("Provider=SQLOLEDB;Data Source=localhost; uid=sa;pwd=;Initial Catalog=pubs") Dim objDataAdapter as new _ OleDbDataAdapter("SELECT * FROM Authors",objConnection) Dim objDataSet as new DataSet objDataAdapter.Fill(objDataSet,"Authors") Listing 4.6 OLE DB Implementation C# Example OleDbConnection objConnection = new OleDbConnection("Provider=SQLOLEDB;Data " + Source=localhost;uid=sa;pwd=;Initial Catalog=pubs"); OleDbDataAdapter objDataAdapter = new OleDbDataAdapter("SELECT * FROM Authors",objConnection); DataSet objDataSet = new DataSet(); objDataAdapter.Fill(objDataSet,"Authors"); Right away you will notice several differences between the Fill() method and a managed provider implementation of returning data. First, the connection to the database is not specifically opened or closed. The Fill() method encapsulates these calls, so the connection is automatically handled. After the data is returned, the data is cached in the DataSet and the connection terminated, thus the disconnected DataSet model. Second, the Fill() method populates a table named Authors in the DataSet. Because the table was not predefined in the DataSet before the Fill() method was called, the SqlClient and OleDb DataAdapter objects automatically create the table schema if one is not predefined. If a table schema is created before the table is loaded in the DataSet, the DataSet uses the defined one. Therefore, if an Authors table schema exists before the Fill() method is executed, the DataAdapter simply fills the existing defined schema. If you use the FillSchema() method, the DataSet's schema is forced to match the schema of the database. Because no physical relationships exist between the DataSet and the DataAdapter, the DataAdapter can be used to fill any number of DataSet instances. For example, I could add another table to the examples in Listings 4.3 through 4.6 by using the following code: Dim objConnection as new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") Dim objDataAdapter as new SqlDataAdaoter("SELECT * FROM Authors", objConnection) Dim objDataSet as new DataSet objDataAdapter.Fill(objDataSet,"Authors") objDataAdapter.CommandText = "SELECT * FROM Publishers" objDataAdapter.Fill(objDataSet,"Publishers") The DataSet now contains two different tables one Authors table and one Publishers table with completely different structures and data. The Update() MethodThe Update() method is used to send data from the DataSet back to the data source. Just like the Fill() method, the Update() method also requires two parameters: the DataSet object and the table name reference. The connection handling is done automatically. Listing 4.7 uses the Fill() method example, modifies some of the rows, and passes the results back to the database. Listing 4.7 Modifying Data with the Fill() Method Visual Basic Example <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <head> <script language="VB" runat="server" ID=Script1> Sub Page_Load(Sender as Object, E as EventArgs) Dim objConnection as _ new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") Dim objDataAdapter as _ new SqlDataAdapter("SELECT * FROM Authors", objConnection) Dim objDataSet as DataSet objDataAdapter.Fill(objDataSet,"Authors") Dim objDataView as DataView objDataView = new DataView(objDataSet.Tables("Authors")) ShowData(objDataView) objDataSet.Tables("Authors").Rows(0)("au_fname") = "John" objDataSet.Tables("Authors").Rows(0)("au_lname") = "Doe" objDataSet.Tables("Authors").Rows(1)("au_fname") = "Jane" objDataSet.Tables("Authors").Rows(1)("au_lname") = "Doe" objDataAdapter.Update(objDataSet,"Authors") End Sub Sub ShowData (objDataView as DataView) Dim I as integer Response.Write("<table border=1>") Response.Write("<th>au_id</th><th>au_fname</th>" + _ "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + _ "<th>zip</th><th>phone</th><th>contract</th>") for I = 0 to objDataView.Count - 1 Response.Write("<tr><td>") Response.Write(objDataView(I)("au_id").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("au_fname").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("au_lname").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("address").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("city").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("state").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("zip").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("phone").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("contract").ToString) Response.Write("</td></tr>") Next Response.Write("</table><br>") End Sub </script> </head> </html> Listing 4.8 Using the ShowData() Method C# Example void page_load(Object Sender, EventArgs e) SqlConnection objConnection = new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs" ); SqlDataAdapter objDataAdapter = new SqlDataAdapter( "SELECT * FROM Authors", objConnection ); DataSet objDataSet = new DataSet(); objDataAdapter.Fill( objDataSet,"Authors" ); int nIndex = objDataSet.Tables.IndexOf( "Authors" ); // The following assume that two rows of data exist. objDataSet.Tables[nIndex].Rows[0]["au_fname"] = "John"; objDataSet.Tables[nIndex].Rows[0]["au_lname"] = "Doe"; objDataSet.Tables[nIndex].Rows[1]["au_fname"] = "Jane"; objDataSet.Tables[nIndex].Rows[1]["au_lname"] = "Doe";\ objDataAdapter.Update(objDataSet,"Authors"); Dim objDataView as DataView; objDataView = new DataView(objDataSet.Tables("Authors")); ShowData(objDataView); End Sub If I change the first and last names of the first two rows of data and then call the Update() method, the data is changed in the database table. To show the modified data, I create a DataView object, and the ShowData() method writes the data to the browser. Another feature of the Update() method is its capability to automatically generate commands to complete the Update call if the Insert, Update, or Delete commands are not defined. A SQL Update statement is generated based on the rows modified. A DiffGram is an XML format used to identify current and original versions of data elements. The DataSet uses the DiffGram format to load and persist its contents, and to serialize its contents for transport across a network connection. When a DataSet is written as a DiffGram, it populates the DiffGram with all the necessary information to accurately recreate the contents, though not the schema, of the DataSet, including column values from both the Original and Current row versions, row error information, and row order. Table MappingsThe DataAdapter contains a table-mapping method that enables the data-source table names to be mapped to table names used by the DataSet. Likewise, the returned DataTableMappingCollection object contains a ColumnMappings property that enables column names to be mapped as well. The TableMappings() method becomes very useful if you have a SQL command or stored procedure that returns multiple SELECT statements. The following example performs two select statements in a single SQL command: Dim objConnection as new _ SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") Dim objDataAdapter as new _ SqlDataAdapter("SELECT * FROM Authors;SELECT * FROM Publishers", _ objConnection) objDataSetCOmmand.TableMappings.Add("Table","Authors") objDataSetCOmmand.TableMappings.Add("Table_1","Publishers") Dim objDataSet as DataSet _ objDataSetCommand.FillDataSet(objDataSet) Using the TableMappings property of the DataAdapter object, you can assign specific table names to each SELECT statement. Each SELECT statement is assigned a default name for the result, starting with Table. After the first SELECT, each additional mapping requires a sequence number as the source table name. DataSet Parameter BindingThe use of parameter binding with a DataAdapter is very similar to the managed provider's Command object implementation. The only additional item of consideration with parameter binding is the DataRowVersion used to bind each parameter. Each parameter needs five pieces of information to be bound correctly. These components are parameter name, data type, direction, SourceData, and SourceVersion. The first three elements work exactly like the managed provider's Command object. Now I'll explain the other two. The SourceData parameter tells the DataAdapter which column is to be used when passing data to the data source. The SourceVersion specifies which version of row information in the DataSet should be used for binding. A DataTable can keep track of changes as data changes are made to its internal rows. Data can be in one of four different states at any given time. Table 4.1 describes these states (which are source version values).
The next example demonstrates how to bind parameters to a DataAdapter. In this example, shown in Listings 4.9 and 4.10, I need to create two SQL statements: one for the initial DataSet load and another to update the DateSet using the UpdateCommand property when the Update() method is called. Notice in the example that I have created my own update command to update changes to the Authors table. I have created two SQL statements for the DataAdapter object: one for the initial DataSet load and another to update the DateSet when the Update() method is called. Because I defined an explicit UpdateCommand, the Update() method uses this explicit command instead of dynamically creating one. Next, I create the parameter bindings to the names in the update statement. The first and last names are bound using the DataRowVersion. Current property of the table to capture the modified values. I set the ID parameter to use the DataRowVersion.Original property. Because this value was not altered, the Current version would have worked just as well. However, if I had changed its value and it was the table's Primary Key, I would need to use the Original row version to find a match. Last, I modify the first two rows of data and call the Update() method. Listing 4.9 Two SQL Statements Get the Job Done in This VB Example. Sub Page_Load() Dim objConnection as new _ SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") Dim objDataAdapter as new _ SqlDataAdapter("SELECT * FROM Authors",objConnection) objDataAdapter.UpdateCommand = _ New SqlCommand("Update Authors set au_fname=@Fname," + _ "au_lname=@Lname WHERE au_id=@ID",objConnection) Dim objDataSet as DataSet objDataSet = new DataSet() objDataAdapter.Fill(objDataSet,"Authors") Dim objDataView as DataView objDataView = new DataView(objDataSet.Tables("Authors")) ShowData(objDataView) objDataSet.Tables("Authors").Rows(0)("au_fname") = "yyyy" objDataSet.Tables("Authors").Rows(0)("au_lname") = "Smith" objDataSet.Tables("Authors").Rows(1)("au_fname") = "Jane" objDataSet.Tables("Authors").Rows(1)("au_lname") = "Smith" objDataAdapter.UpdateCommand.Parameters.Add(_ new SqlParameter("@Fname",SqlDBType.Varchar,20)) objDataAdapter.UpdateCommand.Parameters("@Fname").Direction = _ ParameterDirection.Input objDataAdapter.UpdateCommand.Parameters("@Fname").SourceColumn = _ "au_fname" objDataAdapter.UpdateCommand.Parameters("@Fname").SourceVersion = _ DataRowVersion.Current objDataAdapter.UpdateCommand.Parameters.Add(_ new SQLParameter("@Lname",SqlDBType.Varchar,40)) objDataAdapter.UpdateCommand.Parameters("@Lname").Direction = _ ParameterDirection.Input objDataAdapter.UpdateCommand.Parameters("@Lname").SourceColumn = _ "au_lname" objDataAdapter.UpdateCommand.Parameters("@Lname").SourceVersion = _ DataRowVersion.Current objDataAdapter.UpdateCommand.Parameters.Add(_ new SQLParameter("@ID",SqlDBType.Varchar,11)) objDataAdapter.UpdateCommand.Parameters("@ID").Direction = _ ParameterDirection.Input objDataAdapter.UpdateCommand.Parameters("@ID").SourceColumn = _ "au_id" objDataAdapter.UpdateCommand.Parameters("@ID").SourceVersion = _ DataRowVersion.Original objDataAdapter.Update(objDataSet,"Authors") End Sub Sub ShowData (objDataView as DataView) Dim I as integer Response.Write("<table border=1>") Response.Write("<th>au_id</th><th>au_fname</th>" + _ "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + _ "<th>zip</th><th>phone</th><th>contract</th>") for I = 0 to objDataView.Count - 1 Response.Write("<tr><td>") Response.Write(objDataView(I)("au_id").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("au_fname").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("au_lname").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("address").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("city").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("state").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("zip").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("phone").ToString) Response.Write("</td><td>") Response.Write(objDataView(I)("contract").ToString) Response.Write("</td></tr>") Next Response.Write("</table><br>") End Sub Listing 4.10 Two SQL Statements Get the Job Done in This C# Example. void Page_Load() { SqlConnection objConnection = new SqlConnection( "server=localhost;uid=sa;pwd=;database=pubs"); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Authors",objConnection); objDataAdapter.UpdateCommand = new SqlCommand( "Update Authors set au_fname=@Fname," + "au_lname=@Lname WHERE au_id=@ID",objConnection); DataSet objDataSet = new DataSet(); objDataAdapter.Fill(objDataSet,"Authors"); DataView objDataView; objDataView = new DataView(objDataSet.Tables["Authors"]); ShowData(objDataView); objDataSet.Tables["Authors"].Rows[0]["au_fname"] = "John"; objDataSet.Tables["Authors"].Rows[0]["au_lname"] = "Smith"; objDataSet.Tables["Authors"].Rows[1]["au_fname"] = "Jane"; objDataSet.Tables["Authors"].Rows[1]["au_lname"] = "Smith"; objDataAdapter.UpdateCommand.Parameters.Add( new SqlParameter("@Fname",SqlDbType.VarChar,20)); objDataAdapter.UpdateCommand.Parameters["@Fname"].Direction = ParameterDirection.Input; objDataAdapter.UpdateCommand.Parameters["@Fname"].SourceColumn = "au_fname"; objDataAdapter.UpdateCommand.Parameters["@Fname"].SourceVersion = DataRowVersion.Current; objDataAdapter.UpdateCommand.Parameters.Add( new SqlParameter("@Lname",SqlDbType.VarChar,40)); objDataAdapter.UpdateCommand.Parameters["@Lname"].Direction = ParameterDirection.Input; objDataAdapter.UpdateCommand.Parameters["@Lname"].SourceColumn = "au_lname"; objDataAdapter.UpdateCommand.Parameters["@Lname"].SourceVersion = DataRowVersion.Current; objDataAdapter.UpdateCommand.Parameters.Add( new SqlParameter("@ID",SqlDbType.VarChar,11)); objDataAdapter.UpdateCommand.Parameters["@ID"].Direction = ParameterDirection.Input; objDataAdapter.UpdateCommand.Parameters["@ID"].SourceColumn = "au_id"; objDataAdapter.UpdateCommand.Parameters["@ID"].SourceVersion = DataRowVersion.Original; objDataAdapter.Update(objDataSet,"Authors"); } void ShowData (DataView objDataView) { Response.Write("<table border=1>"); Response.Write("<th>au_id</th><th>au_fname</th>" + "<th>au_lname</th><th>address</th><th>city</th><th>state</th>" + "<th>zip</th><th>phone</th><th>contract</th>"); for(int i=0;i<objDataView.Count - 1;i++) { Response.Write("<tr><td>"); Response.Write(objDataView[i]["au_id"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["au_fname"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["au_lname"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["address"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["city"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["state"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["zip"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["phone"].ToString()); Response.Write("</td><td>"); Response.Write(objDataView[i]["contract"].ToString()); Response.Write("</td></tr>"); } Response.Write("</table><br>"); } |
EAN: N/A
Pages: 175