Reading and Updating Data with Commands

JavaScript EditorJavascript debugger     Javascript examples


Team LiB
Previous Section Next Section

Reading and Updating Data with Commands

The Command classes—SqlCommand and OleDbCommand—are the ADO.NET equivalents of the ADO Command object. You can use these classes to retrieve read-only data through a datareader (as you’ll see later in this chapter), execute Insert, Update, Delete, and other statements that don’t return records, retrieve aggregate results, or retrieve an XML representation of data. You can also use SqlCommand and OleDbCommand in conjunction with a related datareader to populate a dataset with data or to update a back-end database with updated data from a dataset. The SqlCommand and the OleDbCommand classes can work with either SQL statements or stored procedures (for datasources that support them).

SqlCommand

The SqlCommand class is the appropriate class to use when you want to run commands against a SQL Server database. Each of the following steps outlines one or more ways to initialize or use the SqlCommand class.

Use the SqlCommand class

  1. Create the SqlCommand object.

    Note that the constructor of the SqlCommand object is overloaded, so you can save steps by passing arguments (such as the query for the Command and/or the SqlConnection object to use for the command) to its constructor, rather than setting the properties after the object is created. The following examples demonstrate several of the arguments that can be passed to the SqlConnection constructor.

    // Default constructo SqlCommand mySqlCmd = new SqlCommand() //Passing in a quer string SQL = "SELECT au_id FROM authors" SqlCommand mySqlCmd2 = new SqlCommand(SQL) //Passing in a query and a connectio string ConnStr = "datasource=localhost\VSdotNET;    + "database=pubs;integratedsecurity=true" SqlConnection mySqlConn = new SqlConnection(ConnStr) string SQL = "SELECT au_id FROM authors" SqlCommand mySqlCmd3 = new SqlCommand(SQL, mySqlConn) //Passing in a query, a connection, and a transactio string ConnStr = "datasource=localhost\VSdotNET;    + "database=pubs;integratedsecurity=true" SqlConnection mySqlConn = new SqlConnection(ConnStr) SqlTransaction mySqlTrans = mySqlConn.BeginTransaction() string SQL = "SELECT au_id FROM authors" SqlCommand mySqlCmd4 = new SqlCommand(SQL, mySqlConn, mySqlTrans);
  2. If you haven’t set them in the constructor, set the CommandText property to the desired SQL query or stored procedure name and set the Connection property to an open SqlConnection object.

    mySqlCommand.CommandText = "SELECT au_id FROM authors" // Assumes that mySqlConn has already been created and opene mySqlCommand.Connection = mySqlConn;
  3. Call one of the following four methods that execute the command. (Note that the value of the CommandText property will vary depending on the method you call.)

    //Use ExecuteNonQuery to execute an INSERT, UPDATE o //DELETE query where that query type has been set using th //CommandText propert mySqlCmd.ExecuteNonQuery() //Use ExecuteReader to execute a SELECT command and //return a datareade SqlDataReader mySqlReader = mySqlCmd.ExecuteReade //Use ExecuteScalar to execute a command and return the value o //the first column of the first row. Any additional results //are ignored Object Result Result = mySqlCmd.ExecuteScalar() //Use ExecuteXmlReader to execute a SELECT command and //fill a DataSet using the returned XmlReade string SQL = "SELECT * FROM authors FOR XML AUTO, XMLDATA" SqlCommand mySqlCmd = new SqlCommand(SQL, mySqlConn) DataSet myDS = new DataSet() MyDS.ReadXml(mySqlCmd.ExecuteXmlReader(), XmlReadMode.Fragment);
    
  4. Make sure to close the connection when you’re finished with it.

The following example walks you through creating the code necessary to retrieve the contents of the Authors table of the Pubs sample SQL Server database as XML and to display that data in a Web Forms page.

Note 

The data access samples in this book are written to run against the MSDE sample database included with Microsoft Visual Studio .NET. For information on installing MSDE, see Appendix C. If you want to run the data access samples against a SQL Server database other than the VSdotNET MSDE database, or if you are unable to use a trusted connection to SQL Server or MSDE, you must modify the connection string in the examples to match the appropriate server name and login credentials.

Display XML Data in a Web Page

  1. Open Visual Studio and create a new project called Chapter_09. Rename the default Web Page created by Visual Studio from WebPage1.aspx to ExecuteXmlReader.aspx by selecting WebForm1.aspx in the Solution Explorer and then selecting File, then Save WebForm1.aspx As.

  2. Copy the file Authors.xsl from the practice files for this chapter into the folder where you just created the project. (If you have not downloaded and installed the practice files, you can add a new XSL file to the project, and then add the code from the Authors.xsl listing that follows this example.)

  3. Drag an Xml control from the toolbox onto the form. Use the Properties window to change the ID to XmlDisplay. Set the TransformSource to Authors.xsl.

  4. Switch to the source code window by using F7 or by selecting View, and then Code.

  5. Add the following using clause to the top of the source file:

    using System.Data.SqlClient;
  6. Scroll down to the Page_Load event handler and add the following code:

    string ConnStr // You may want to adjust this string.. ConnStr = "data source=(local)\\VSdotNET;"  "database=pubs;integrated security=true" // Create and open the connectio SqlConnection MySqlConn = new SqlConnection(ConnStr) MySqlConn.Open() tr  string SQL = "SELECT * FROM authors FOR XML AUTO, XMLDATA" SqlCommand mySqlCmd = new SqlCommand(SQL, MySqlConn) DataSet MyDS = new DataSet() // Fill the DataSet, using the XML read from MSDE/SQL Serve MyDS.ReadXml(mySqlCmd.ExecuteXmlReader(), XmlReadMode.Fragment) XmlDisplay.DocumentContent = MyDS.GetXml()  finall  MySqlConn.Close() }
  7. Save the project and both open files.

  8. Build the project.

  9. Test the page by right-clicking ExecuteXmlReader.aspx, selecting Browse With, and then selecting Microsoft Internet Explorer. The resulting screen should look like the illustration on the following page.

    Click To expand

This example creates a SqlConnection object that opens a connection to the Pubs database, creates a SQL query string to retrieve the contents of the Authors table as XML, and creates a new SqlCommand object, passing in the SQL query and SqlConnection object. Then it creates a dataset and uses the ExecuteXmlReader method of the SqlCommand object to pass an XmlReader object to the dataset’s ReadXml method, which allows the dataset to populate itself from the XmlReader. Finally, the code sets the DocumentContent property of the declared Xml server control to the result of the GetXml method of the dataset. The Xml control uses the XSL Transformation document authors.xsl to format the Xml content displayed by the Xml control.

Important 

The code in Step 6 in the previous example shows an important pattern when using database connections. Immediately after the call to Open, you enter a try block. In the finally section, you close the connection. This ensures that the connection is closed, even if an exception occurs in the code after the call to Open.

It's also not a bad idea to add catch blocks for exceptions that might occur in connecting to and reading from the database. This will make your database code more robust, which will make your users happier!

The following listing shows the content of authors.xsl.

Authors.xsl
Start example
<xsl:stylesheet version='1.0'     xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>    <xsl:template match="/">    <style>       .header{font-weight:bold;color:white;background-color:black;       .value{font-family:arial;font-size:.7em;background-color:silver    </style>    <table border="1" cellspacing="0" cellpadding="1       bordercolor="black">       <tr class="header">          <th>Author ID</th>          <th>Last Name</th>          <th>First Name</th>          <th>Phone</th>          <th>Address</th>          <th>City</th>          <th>State</th>          <th>Zip</th>          <th>Contract</th>       </tr>    <xsl:for-each select='Schema1/authors'>       <tr>          <td nowrap="true" class="value">             <b>                <xsl:value-of select='@au_id' />             </b>          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@au_lname' />          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@au_fname' />          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@phone' />          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@address' />          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@city' />          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@state' />          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@zip' />          </td>          <td nowrap="true" class="value">             <xsl:value-of select='@contract' />          </td>       </tr>    </xsl:for-each>    </table>    </xsl:template> </xsl:stylesheet>
End example

OleDbCommand

For most purposes, using the OleDbCommand class is effectively the same as using the SqlCommand class. Instead of connecting with the SqlConnection class, you can just use the OleDbConnection class. One significant difference, however, is that the OleDbCommand class does not have an ExecuteXmlReader method.

The following example assumes that you have the Northwind.mdb database installed locally.

  1. Create and open an OleDbCommand object with the appropriate connection string for connecting to the Northwind database, where <filepath> is the path to Northwind.mdb on your machine.

    string ConnStr ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" ConnStr += "Data Source=<filepath>\northwind.mdb;" OleDbConnection myOleDbConn = new OleDbConnection(ConnStr) myOleDbConn.Open;
  2. Create a variable to contain the SQL query. (Note that the query can also be passed as a literal string to the constructor of the OleDbCommand class.)

    string SQL  = "SELECT Count(*) FROM products";
  3. Create an OleDbCommand object, passing the SQL query and the connection object to the constructor.

    OleDbCommand myOleDbCmd = new OleDbCommand(SQL, myOleDbConn);
  4. Create a variable to receive the return value from the command. This variable is declared as type Object because that is the return type of the ExecuteScalar method of the OleDbCommand object.

    object Result;
  5. Call the ExecuteScalar method of the OleDbCommand object, and use the returned value. Note that you must cast the value to the correct type before using it because the returned type is Object. This is especially important if you need to use methods of a particular type that are not implemented by Object.

    Result = myOleDbCmd.ExecuteScalar() Value.Text += Result.ToString;

The following example shows how you would use the objects in the OleDb namespace to display the returned result in a Web Forms page.

Display a single query result from Microsoft Access

  1. Open Visual Studio, and then open the project created in the previous example.

  2. Add a new Web Form to the project and name it ExecuteScalar.aspx.

  3. Add a label to the form. Use the Properties window to change the ID of the label to Value.

  4. Switch to code view by pressing the F7 key or by selecting View, and then Code.

  5. Add the following using clause to the using clauses at the top of the file:

    using System.Data.OleDb;
  6. Scroll down to the Page_Load event handler and add the following code. This code assumes that a copy of the Northwind Microsoft Access sample database exists in the same directory as ExecuteScalar.aspx:

    string DbPath  = Server.MapPath("Northwind.mdb") string ConnStr string SQL ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; "  @"Data Source=" + DbPath +";" OleDbConnection MyOleDbConn = new OleDbConnection(ConnStr) MyOleDbConn.Open() tr  SQL = "SELECT Count(*) FROM products" OleDbCommand MyOleDbCommand = new OleDbCommand(SQL,MyOleDbConn) object Result Result = MyOleDbCommand.ExecuteScalar() Value.Text = "There are " + Result.ToString()  " products in the Northwind database."  finall  MyOleDbConn.Close() }
  7. Save the page and code-behind module, and then build the project.

  8. Test the page by right-clicking ExecuteScalar.aspx, selecting Browse With, and then selecting Microsoft Internet Explorer. The resulting screen should look like the following illustration.

    Click To expand
    Note 

    Although the previous example uses Microsoft Access to demonstrate the ability of the OleDbCommand object to connect to non–SQL Server databases, you should generally avoid using Access for ASP.NET applications. For initial prototyping and development, or for applications with minimal scalability requirements (up to 100 or so concurrent users, depending on the activity generated by each user), MSDE is the better choice.

    MSDE is a SQL Server–compatible database that is available with a number of Microsoft products, including SQL Server, Microsoft Office, and Visual Studio. A version of MSDE also comes with Visual Studio .NET, as well as with the .NET Framework SDK, and it’s installed with and used to run the SDK samples, if you choose to install them. (Please refer to Chapter 6 for important guidance about installing sample applications.) MSDE is built on the same database engine as SQL Server, but it’s tuned for approximately five concurrent users.

    The advantage of using MSDE is that all of your development tasks are then identical to developing against SQL Server, without the licensing expense of a full-blown SQL Server. (The license to use and distribute MSDE is included in the aforementioned products. Check the end-user license agreement to ensure that your use is within the terms of the agreement.) And if your application’s scalability needs to grow, you can simply move your database to SQL Server for increased performance and scalability, with no code or data changes required.

Using Stored Procedures

In addition to using SQL text queries, you can also use stored procedures as the basis of a SqlCommand or OleDbCommand object. This is as simple as setting the CommandText property of the object to the name of the stored procedure that you want to execute, and setting CommandType to CommandType.StoredProcedure. If you’re familiar with the execution of stored procedures under classic ADO, this isn’t so different from what you would have done with the ADO Command object.

Calling Stored Procedures with Parameters

Calling stored procedures with input or output parameters is a little more involved, but it’s still pretty straightforward. You simply create parameters (either SqlParameter or OleDbParameter objects); set the appropriate properties, such as ParameterName, Direction, type (SqlType or OleDbType), and Value; and then add the parameter object to the Parameters collection of the Command object. The following example shows the code required to execute the byroyalty stored procedure in the Pubs sample SQL Server database, and it returns a SqlDataReader object, which is then bound to an ASP.NET DataGrid control. (You’ll learn more about data binding later in this chapter.)

Read data using a stored procedure and SqlDataReader

  1. Open Visual Studio and then open the Chapter_09 project created earlier in the chapter.

  2. Add a new Web Form to the project. Name the form ExecuteReader.aspx.

  3. Drag a DataGrid from the toolbox and drop it onto the form. Use the Properties window to change the ID of the DataGrid to MyGrid.

  4. Switch to code view by pressing the F7 key or by selecting View, and then Code.

  5. Add the following using clause to the using clauses at the top of the file:

    using System.Data.SqlClient;
    
  6. Scroll down to the Page_Load event handler and add the following code:

    string ConnStr string SQL ConnStr = @"Data Source=(local)\VSdotNET;"  "database=pubs;integrated security=true" SqlConnection MySqlConn = new SqlConnection(ConnStr) MySqlConn.Open() tr  SQL = "byroyalty" SqlCommand MySqlCmd = new SqlCommand(SQL,MySqlConn) MySqlCmd.CommandType = CommandType.StoredProcedure SqlParameter MySqlParam = new SqlParameter("@ percentage", SqlDbType.Int) MySqlParam.Value = 40 MySqlCmd.Parameters.Add(MySqlParam) SqlDataReader Reader Reader = MySqlCmd.ExecuteReader() if (Reader.HasRows  MyGrid.DataSource=Reader MyGrid.DataBind()  els  Label Message = new Label() Message.Text = "No rows to display" Page.Controls.Add(Message)   finall  MySqlConn.Close() }
  7. Save the page and code-behind module.

  8. Build the project.

  9. Test the page by right-clicking ExecuteReader.aspx, selecting Browse With, and then selecting Microsoft Internet Explorer. The resulting screen should look like the following illustration.

    Click To expand

The previous example creates and opens a connection to the Pubs database, creates a SqlCommand object and sets its CommandType property to CommandType.StoredProcedure, and then creates a SqlParameter object, passing the parameter name and data type to the parameter’s constructor.

Important 

Unlike in ADO, when you specify a parameter name to ADO.NET you must use the exact name the stored procedure expects, including the at symbol (@).

The code then sets the value of the parameter, adds it to the Parameters collection of the SqlCommand object, and executes the command. The code then checks the datareader's HasRows property, and if the query returned one or more rows, the SqlDataReader object is then bound to a DataGrid control, which displays the results.


Team LiB
Previous Section Next Section


JavaScript EditorJavascript debugger     Javascript examples