The Implementation of the Serviced Component Methods

092 - A Sidebar on ADO.NET <p><script> function OpenWin(url, w, h) { if(!w) w = 400; if(!h) h = 300; window. open (url, "_new", "width=" + w + ",height=" + h + ",menubar=no,toobar=no,scrollbars=yes", true); } function Print() { window.focus(); if(window.print) { window.print(); window.setTimeout('window.close();',5000); } } </script><span></span></p> <table width="100%" cellpadding="0" cellspacing="0" border="0"><tr valign="top"></tr></table> <table width="100%" height="20" border="0" cellpadding="0" cellspacing="1"><tr></tr></table> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr valign="top"> <td align="center"><table width="95%"><tr><td align="left"> <table width="100%" border="0" cellspacing="0" cellpadding="2"> <tr><td valign="top" height="5"><img src="/books/2/575/1/html/2/images/pixel.gif" width="1" height="5" alt="" border="0"></td></tr> <tr> <td><b><font size="1" color ="#0000FF">Team-Fly<img border="0" src="/books/2/575/1/html/2/Fly-Logo.gif" width="81" height="25"></font></b></td> <td valign="top" align="right">     </td> </tr> </table> <hr size="1"> <table width="100%" border="0" cellspacing="0" cellpadding="5"> <tr> <td valign="top" width="76" rowspan="4"><img src="/books/2/575/1/html/2/images/013046130X/013046130X_xs.jpg" width="76" height="95" border="0"></td> <td valign="top">.NET and COM Interoperability Handbook, The<br>By Alan Gordon<br> </td> </tr> <tr><td>Table of Contents</td></tr> <tr><td></td></tr> <tr><td valign="bottom"> Chapter Nine.  Using COM+(Enterprise Services)</td></tr> </table> <hr size ="1"> <br><table width="100%" border="0" cellspacing="0" cellpadding ="0"><tr><td valign="top"> <h3 id="162593-805"> The Implementation of the Serviced Component Methods </h3> <p>I did not want to clutter my explanation of serviced components by including the implementation of each method in the <span>Book</span> class. However, I would be remiss if I didn't discuss it before I concluded this chapter. Serviced components are primarily designed for Enterprise class applications. Enterprise applications use relational databases and often other resource managers like message queues. In fact, the transaction service in the .NET Enterprise Services (COM+) is designed for distributed applications involving two or more databases or other resource managers. I decided therefore to make the <span>Book</span> class an example of the kind of datacentric business object that .NET Enterprise Services was designed to help you build.</p> <p>The <span>Book</span> class that I used throughout this chapter uses ADO.NET to retrieve data from and issue updates to a SQL 2000 database that contains information about .NET and COM-related books. The database also includes customer and order information.</p> <div> <p>Note</p> <p>I used this database in an article called "Integrate .NET Technologies" that I wrote in the Fall/Winter 2001 edition of <span>.NET Magazine</span>. In that article, I created an eCommerce application that modeled an online technical bookstore.</p> </div> <br><p>The <span>Book</span> class uses ADO.NET with the SQL client provider, which is optimized for SQL Server. The code uses Transact SQL (TSQL) stored procedures to retrieve data from and update the database. The following code shows the implementation of the GetBooksByTitle method in the <span>Book</span> class.</p> <pre> 1. public DataSet GetBooksByTitle(string title) 2. { 3. DataSet ds; 4. StoredProcParam[] spParameters= 5. new StoredProcParam[1]; 6. spParameters[0]=new StoredProcParam( 7. "@Title",SqlDbType.NVarChar,255,title); 8. ds=GetDataSetFromStoredProc( 9. "GetBooksByTitle",spParameters,"Titles"); 10. return ds; 11. } </pre> <p>Lines 4 and 5 declare an array (with a single element) of StoredProcParam objects. This StoredProcParam is a class that I created that represents a parameter to a stored procedure. The array has one element because the stored procedure that I will use to fetch the data takes a single argument. Lines 6 and 7 initialize the lone element of the StoredProcParam array. The parameters that you pass to the constructor are (in order from left to right) the name of the stored procedure argument, the type of the stored procedure argument, the size in bytes of the stored procedure argument, and the variable that contains the value you will pass to the stored procedure. Lines 8 and 9 call the GetDataSetFromStoredProc method. GetDataSetFromStoredProc is a private method in the <span>Book</span> class, which does exactly as its name implies. It calls a stored procedure and returns the result as an ADO.NET dataset. The first parameter to this method is the name of the stored procedure. The second parameter is the StoredProcParam array that contains the arguments that you are passing to the stored procedure. The third parameter is the name that you want the method to assign to the data table of the dataset that it will return.</p> <p>Here is the implementation of the <span>StoredProcParam</span> class.</p> <pre> struct StoredProcParam { public StoredProcParam(string name,SqlDbType type,int size,object val) { this.mName=name; this.mType=type; this.mSize=size; this.mParamValue=val; } public SqlDbType Type { get { return mType; } set { mType=value; } } public int Size { get { return mSize; } set { mSize=value; } } public string Name { get { return mName; } set { mName=value; } } public object ParamValue { get { return mParamValue; } set { mParamValue=value; } } private SqlDbType mType; private int mSize; private string mName; private object mParamValue; } </pre> <p>This class is fairly simple, so I won't discuss it. The following code shows the implementation of the GetDataSetFromStoredProc method:</p> <pre> 1. private DataSet GetDataSetFromStoredProc( 2. string spName,StoredProcParam[] spparams, 3. string dsTableName) 4. { 5. int i, n; 6. DataSet ds; 7. ds = new DataSet(); 8. SqlCommand cmd; 9. SqlDataAdapter dsAdapter; 10. SqlParameter param; 11. SqlConnection conn; 12. StoredProcParam inputParam; 13. conn=GetConnection(); 14. cmd=new SqlCommand(spName,conn); 15. cmd.CommandType = CommandType.StoredProcedure; 16. // If parameters are passed, add them to the command 17. if (spparams != null) 18. { 19. n=spparams.Length; 20. for (i=0;i<n;i++) 21. { 22. inputParam=spparams[i]; 23 . param=cmd.Parameters.Add( 24. new SqlParameter(inputParam.Name, 25. inputParam.Type , 26. inputParam.Size)); 27. param.Value=inputParam.ParamValue; 28. param.Direction= 29. ParameterDirection.Input; 30. } 31. } 32. try 33. { 34. // Open the connection and execute the Command 35. conn.Open(); 36. dsAdapter =new SqlDataAdapter(); 37. dsAdapter.SelectCommand=cmd; 38. dsAdapter.Fill(ds, dsTableName); 39. return ds; 40. } 41. catch (Exception err) 42. { 43. // An error occurred, pass the exception up 44. throw err; 45. } 46. finally 47. { 48. // Close the Connection 49. if (conn.State == ConnectionState.Open) 50. conn.Close(); 51. } 52. } </pre> <p>I highly recommend that you read the sidebar on ADO.NET before you read the following explanation of the code. Lines 6 and 7 declare and create a new instance of the <span>DataSet</span> class. Lines 8 through 11 declare instances of the ADO.NET <span>SqlCommand, SqlDataAdapter, SqlParameter</span>, and <span>SqlConnection</span> classes. These classes represent, respectively, a database command, a class for connecting a database command to a dataset, a parameter to a parameterized query or stored procedure, and a database connection. Line 13 connects to the database. Lines 14 and 15 initialize the command object using the connection and the stored procedure name that was passed in as the first parameter to the GetDataSetFromStoredProc method. Lines 17 through 31 add the contents of the StoredProcParam array that was passed to the method to the Parameter list of the command. Line 35 opens the connection. Line 36 creates an instance of the <span>SqlDataAdapter</span> class, and line 37 initializes the Select command for the <span>SqlDataAdapter</span> class to the SqlCommand that I initialized earlier. Line 38 calls the Fill command on the <span>SqlDataAdapter</span> to fill a dataset with the result of the command, and then, finally, line 39 returns the dataset to the caller.</p> <p></p> <table cellspacing="0" width="90%" border="1"><tr><td> <h2>A Sidebar on ADO.NET</h2> <p>ADO.NET is the first data access technology that was designed specifically for use with Web applications. To satisfy the unique needs of Web applications, ADO.NET has rich support for the disconnected database programming model. It also has integrated support for XML. With ADO.NET, it's easy to convert your data to an XML document that can be transported across the network using HTTP or any other Internet protocol. Let's explore both of these ideas further.</p> <p>A classic client/server application connects to a data source and performs an operation to fetch data from its data source. It usually remains connected to its data source while it iterates through the returned data using a cursor. In many cases, the application will not disconnect from the data source until the user closes the application. The biggest problem with the connected programming model is that it does not scale very well. If 10,000 clients are using a server, there will be 10,000 open connections, even though only a small percentage of those clients will be making a request on the server at any moment in time.</p> <p>With the disconnected database programming model, an application connects to its data source, fetches the data into a client-side data structure, and then immediately disconnects from the data source and releases its locks on the data. The client application then uses the client-side data structure to update, delete, insert, sort , and filter the data. These operations initially operate only on the client-side data structure. When the application wants to propagate its updates to the underlying data store, it reconnects to the data source and applies its updates to the server. The application has to handle conflict resolution if, for instance, another application has made updates to the data after the application has read it.</p> <p>This approach is far more scalable than the connected database programming model and hence better suited to the Web. The only time your application holds a connection to the data store is when it is querying or updating data. If an application has 10,000 clients, it is likely that only a few hundred of those clients will be querying or updating data at any given moment in time.</p> <p>The key to the disconnected database programming model support in ADO.NET is the DataSet class. A dataset is an in-memory collection of related data tables. You use DataAdapter and Command objects to fill a dataset. The DataSet class contains a rich set of functionality for operating on the data that it contains. You can read, sort, search, filter, and modify the data in a dataset.</p> <p>The DataSet class is implemented in the System.Data namespace and is shared by all managed providers (if you're not sure what a managed provider is, see my high-level explanation of ADO.NET in Chapter 1). It includes a set of supporting classes. Figure 9-8 shows the relationship between the DataSet and its supporting classes and the rest of ADO.NET.</p> <center> <h5>Figure 9-8. The relationship between the DataSet class and the rest of the classes in ADO.NET.</h5> <p><img border="0" width="400" height="470" src="/books/2/575/1/html/2/images/013046130X/graphics/09fig08.gif" alt="graphics/09fig08.gif"></p> </center> <p>The DataTable and DataRow classes contain the tables and rows of the DataSet respectively. These DataTables may have the same schema as the data source that they were created from, or their schema may be completely different. The DataTableMapping and DataColumnMapping classes allow you to map tables and columns in your data source's schema to tables and columns in your dataset's schema. The DataView class allows you to maintain filtered or sorted views of the data in your dataset. The Constraint class allows you to enforce constraints in your dataset (an account balance cannot be negative, for instance). The DataRelation class allows you to maintain referential relationships within your data (an Employee must have a foreign key to a department).</p> <p>If you have experience with ADO, it is tempting to think that a dataset is analogous to a recordset. Actually, an ADO recordset is analogous to a data table. A dataset is analogous to a collection of ADO recordsets.</p> <p>You fill a dataset with data using a DataAdapter class that is implemented by each managed provider. The SqlClient managed provider has a class called SqlDataAdapter, and the OleDb managed provider has a class called OleDbDataAdapter. The following code shows how to fill a dataset using a select command:</p> <pre> public DataSet GetCustomers( string strCustID) { DataSet ds=new DataSet(); string strSQL="SELECT * FROM " + "CUSTOMERS WHERE CUSTOMERID " + "LIKE '%" + strCustID + "%'"; SqlConnection conn = new SqlConnection(mConnString); SqlCommand cmdSelectCustomer= new SqlCommand(strSQL,conn); SqlDataAdapter daCustomer= new SqlDataAdapter(); daCustomer.SelectCommand= cmdSelectCustomer; conn.Open(); daCustomer.Fill(ds,"Customers"); return ds; } </pre> <p>Notice that I first create Connection and Command objects. Next I create a SqlDataAdapter and assign the Command object to the SelectCommand property of the SqlDataAdapter.</p> <pre> SqlDataAdapter daCustomer= new SqlDataAdapter(); daCustomer.SelectCommand= cmdSelectCustomer; </pre> <p>Then I call the Fill method on the SqlDataAdapter passing in the dataset and specifying a name for the data table in the dataset that will contain the dataCustomers in this case.</p> <pre> conn.Open(); daCustomer.Fill(ds,"Customers"); </pre> <p>Reading data from a dataset is just as easy as filling it. The following code shows how to read the Customer ID and Company Name of each row in the Customers table of the dataset:</p> <pre> string cstID; string cstName; DataTable tblCustomers=ds.Tables["Customers"]; foreach (DataRow cst in tblCustomers.Rows) { cstID=(string)cst["CustomerID"]; cstName=(string)cst["CompanyName"]; } </pre> <p>The following code shows how to populate a dataset with more than one table and how to set up a relationship between tables:</p> <pre> DataSet ds=new DataSet(); string strSQL="SELECT * FROM " + "CUSTOMERS WHERE CUSTOMERID " + "LIKE '%" + txtCustID.Text + "%'"; SqlConnection conn = new SqlConnection(mConnString); SqlCommand cmdSelectCustomers= new SqlCommand(strSQL,conn); strSQL="SELECT * FROM " + "ORDERS WHERE CUSTOMERID " + "LIKE '%" + txtCustID.Text + "%'"; SqlCommand cmdSelectOrders= new SqlCommand(strSQL,conn); SqlDataAdapter daCustomer= new SqlDataAdapter(); SqlDataAdapter daOrder= new SqlDataAdapter(); daCustomer.SelectCommand= cmdSelectCustomers; daOrder.SelectCommand= cmdSelectOrders; conn.Open(); daCustomer.Fill(ds,"Customers"); daOrder.Fill(ds,"Orders"); ds.Relations.Add("CustomerOrders", ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"]. Columns ["CustomerID"]); grdCustomers.DataSource=ds.Tables["Customers"].DefaultView; conn.Close(); </pre> <p>In this case, I am querying from the Customers and Orders tables in the Northwind database and setting up a relationship that will allow you to find all the orders for a customer quickly. Notice that I create two DataAdapters, one for the Customers query and one for the Orders query. I then call the Fill method on each Adapter using different table names as follows:</p> <pre> daCustomer.Fill(ds,"Customers"); daOrder.Fill(ds,"Orders"); </pre> <p>To add the relationship, I call the Add method on the Relationships collection of the dataset and specify a name for the relationship, the parent column, and the child column as follows:</p> <pre> ds.Relations.Add("CustomerOrders", ds.Tables["Customers"]. Columns["CustomerID"], ds.Tables["Orders"]. Columns["CustomerID"]); </pre> <p>The following code shows how to navigate the relationship from a row in the Customers table to get the orders associated with the customer:</p> <pre> string orderID; DataSet ds; // Populate the DataSet ... DataRow aCustomer= ds.Tables["Customers"].Rows[0]; DataRow[] orderRows= aCustomer.GetChildRows("CustomerOrders"); string customerID= aCustomer["CustomerID"].ToString(); foreach (DataRow order in orderRows) { orderID= order["OrderID"].ToString(); } </pre> <p>The .NET Windows Forms namespace also contains a DataGrid control that will navigate these relationships for you. If you set the data source for the grid to be the parent table, each row in the grid will automatically display a link that will allow you to navigate to its associated children as shown in Figure 9-1.</p> <pre> grdCustomers.DataSource=ds.Tables["Customers"].DefaultView; </pre> <p>If you are creating a read-only application, you only need to set the SelectCommand in the data adapter. If you plan to modify data, you must also assign a Command object to the InsertCommand, UpdateCommand, and DeleteCommand properties of your data adapter. You don't have to populate them all, but be aware the data adapter will throw an error if you pass a dataset to the data adapter that contains modified rows. It will also throw an error if you have not defined an Update command for the data adapter, if you pass in a dataset that contains deleted rows and you have not defined a Delete command, or if you pass in a dataset with new rows and you have not defined an Insert command. You can determine whether a dataset contains modified, inserted, or deleted rows using the GetChanges method as follows:</p> <pre> DataSet ds = (DataSet)(Session["Data"]); DataSet dsDeletedRows = ds.GetChanges(DataRowState.Deleted); </pre> <p>The process for updating a dataset is simple. First, make your updates using methods on the DataSet, DataRow, and DataTable classes. The following code shows how to add a new row to a DataTable class:</p> <pre> DataSet ds; // populate the DataSet ... DataTable custTable= ds.Tables["Customer"]; DataRow newRow=custTable.NewRow(); newRow["CustomerID"]=txtID.Text; newRow["CompanyName"]=txtName.Text; custTable.Rows.Add(newRow); </pre> <p>The following code shows how to modify an existing row:</p> <pre> DataSet ds; // Populate the DataSet ... DataTable custTable= ds.Tables["Customer"]; DataRow custRow=custTable.Rows[0]; custRow["CustomerID"]=txtID.Text; custRow["CompanyName"]=txtName.Text; </pre> <p>The following code shows how to delete a row:</p> <pre> DataSet ds; // Populate the DataSet ... DataTable custTable= ds.Tables["Customer"]; custTable.Rows[0].Delete(); </pre> <p>The dataset will store both the current and the original values of any data that you modify. You can call RejectChanges on a DataRow, DataTable, or DataSet to undo any changes you have made at that level. RejectChanges will set the current value of the data equal to the original. You can call AcceptChanges to commit changes to the dataset. AcceptChanges actually is more of a checkpoint than a commit because calling this method will not update the underlying data store. AcceptChanges just sets the original values equal to the current values. Your changes can therefore no longer be undone with a call to RejectChanges. When you are ready to commit your changes to the data store, you must first create a data adapter, set the necessary commands (Insert, Update, and Delete) and then call the Update method on the data adapter passing in either a dataset, a dataset and the name of a table, a data table, or an array of datarows as shown here:</p> <pre> daCustomer.Update(ds,"Customer"); </pre> <p>If the select statement that you use to populate a dataset uses a single table, that is, there are no joins, you can let ADO.NET create the Insert, Update and Delete commands for you. The SqlClient managed provider contains a SqlCommandBuilder class, and the OleDB managed provider contains an OleDdbCommandBuilder class that can create all of the other commands given a Select command. To create all the necessary commands for your data adapter, set the Select Command property on the data adapter and then create an instance of the Sql/OleDbCommandBuilder, passing the data adapter as a parameter as follows :</p> <pre> string strSQL; SqlCommand cmdSelectCustomer; SqlCommandBuilder cbCustomer; SqlConnection conn = new SqlConnection(mConnString); private SqlDataAdapter daCustomer; strSQL="SELECT * FROM CUSTOMERS " + "WHERE CUSTOMERID='" + strCustomerID + "'"; cmdSelectCustomer=new SqlCommand(strSQL,conn); daCustomer=new SqlDataAdapter(); daCustomer.SelectCommand= cmdSelectCustomer; cbCustomer=new SqlCommandBuilder(daCustomer); daCustomer.Update(ds,"Customer"); </pre> <p>One of the most powerful features of the ADO.NET dataset is its tight integration with XML. You can call the GetXml method to return an XML representation of a dataset. You can use the GetXmlSchema method to return the XML Schema Definition (XSD) schema that the dataset is using. You have complete control of the XML schema that your dataset will use. At the simplest level, you can use the ColumnMapping attribute of the DataColumn class to control whether a column is represented as an element or an attribute as shown here:</p> <pre> ds.Tables["Customers"]. Columns["CustomerID"].ColumnMapping= MappingType.Attribute; </pre> <p>You can also explicitly set the XSD XML schema that the dataset uses with the ReadXmlSchema method. You can also use the ReadXml method to populate a dataset with XML data from a file, stream, TextReader, XMLTextReader, or XmlReader object. The WriteXml object allows you to write XML data to a file, stream, TextReader, or XMLText-ReaderXmlReader. With ReadXML and WriteXML, it's easy to roundtrip an XML document, that is, read it into a dataset, update it using the methods you learned in this sidebar, and save it back to disk. Datasets are also the best way to return tabular or hierarchical data from a Web service. Simply return a dataset from a method that is adorned with the WebMethod attribute. The ASP.NET runtime will send the XML representation of the dataset in the SOAP response from the method.</p> <p>I have only just scratched the surface of ADO.NET. This sidebar has given you a good start, but there is a lot more to learn. Some important topics that I did not cover include transactions (ADO.NET supports them), typed datasets, and building a dataset in memory without a data source.</p> </td></tr></table> </td></tr></table> <hr size="1"> <table width="100%" border="0" cellspacing="0" cellpadding="2"> <tr><td valign="top" height="5"><img src="/books/2/575/1/html/2/images/pixel.gif" width="1" height="5" alt="" border="0"></td></tr> <tr> <td><b><font size="1" color="#0000FF">Team-Fly<img border="0" src="/books/2/575/1/html/2/Fly-Logo.gif" width="81" height="25"></font></b></td> <td valign="top" align="right">     </td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top" align="right">Top</td></tr></table> </td></tr></table></td> <td align="center">  </td> </tr> <tr><td colspan="3" align="center" valign="bottom"> <br><table width="100%"><tr><td height="25" valign="middle" colspan="4" align="center"> </td></tr></table> </td></tr> </table>


. Net and COM Interoperability Handbook
The .NET and COM Interoperability Handbook (Integrated .Net)
ISBN: 013046130X
EAN: 2147483647
Year: 2002
Pages: 119
Authors: Alan Gordon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net