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.

start example

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 constructor Dim mySqlCmd As New SqlCommand() 'Passing in a query Dim SQL As String = "SELECT au_id FROM authors" Dim mySqlCmd2 as New SqlCommand(SQL) 'Passing in a query and a connection Dim ConnStr As String ConnStr = "datasource=localhost\VSdotNET;database=pubs;" & _ "integratedsecurity=true" Dim mySqlConn As New SqlConnection(ConnStr) Dim SQL As String = "SELECT au_id FROM authors" Dim mySqlCmd3 as New SqlCommand(SQL, mySqlConn) 'Passing in a query, a connection, and a transaction Dim ConnStr As String ConnStr = "datasource=localhost\VSdotNET;database=pubs;" & _ "integratedsecurity=true" Dim mySqlConn As New SqlConnection(ConnStr) Dim mySqlTrans As SqlTransaction = mySqlConn.BeginTransaction() Dim SQL As String = "SELECT au_id FROM authors" Dim mySqlCmd4 as 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.

    mySqlCmd.CommandText = "SELECT au_id FROM authors" 'Assumes that mySqlConn has already been created and opened mySqlCmd.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 or 'DELETE query where that query type has been set using the 'CommandText propertymy SqlCmd.ExecuteNonQuery() 'Use ExecuteReader to execute a SELECT command and 'return a datareader Dim mySqlReader As SqlDataReader = mySqlCmd.ExecuteReader 'Use ExecuteScalar to execute a command and return the value of 'the first column of the first row. Any additional results 'are ignored. Dim Result As Object Result = mySqlCmd.ExecuteScalar 'Use ExecuteXmlReader to execute a SELECT command and  'fill a DataSet using the returned XmlReader Dim SQL As String SQL = "SELECT * FROM authors FOR XML AUTO, XMLDATA" Dim mySqlCmd as New SqlCommand(SQL, mySqlConn) Dim myDS As New DataSet() MyDS.ReadXml(mySqlCmd.ExecuteXmlReader(), _ XmlReadMode.Fragment)

  4. Make sure to close the connection when you’re finished with it.

end example

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 Basic .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

start example
  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 Imports clause to the top of the source file:

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

    Dim ConnStr As String ConnStr = "data source=(local)\VSdotNET; " & _    "database=pubs;integrated security=true" 'Create and open the connection Dim MySqlConn As New SqlConnection(ConnStr) MySqlConn.Open() Try    Dim SQL As String    SQL = "SELECT * FROM authors FOR XML AUTO, XMLDATA"        Dim mySqlCmd As New SqlCommand(SQL, MySqlConn)    Dim MyDS As New DataSet()    'Fill the dataset using the XML read from MSDE/SQL Server    MyDS.ReadXml(mySqlCmd.ExecuteXmlReader(), _                 XmlReadMode.Fragment)    XmlDisplay.DocumentContent = MyDS.GetXml() Finally    MySqlConn.Close() End Try 
  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

end example

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

<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 > <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" > <b> <xsl:value-of select='@au_id' /> </b> </td> <td nowrap="true" > <xsl:value-of select='@au_lname' /> </td> <td nowrap="true" > <xsl:value-of select='@au_fname' /> </td> <td nowrap="true" > <xsl:value-of select='@phone' /> </td> <td nowrap="true" > <xsl:value-of select='@address' /> </td> <td nowrap="true" > <xsl:value-of select='@city' /> </td> <td nowrap="true" > <xsl:value-of select='@state' /> </td> <td nowrap="true" > <xsl:value-of select='@zip' /> </td> <td nowrap="true" > <xsl:value-of select='@contract' /> </td> </tr> </xsl:for-each> </table> </xsl:template> </xsl:stylesheet>

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.

     

    Dim ConnStr As String ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" ConnStr &= "Data Source=<filepath>\northwind.mdb;" Dim myOleDbConn As 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.)

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

    Dim myOleDbCmd as 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.

    Dim Result As Object 
  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 &= CType(Result, String)

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

start example

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 Imports clause to the using clauses at the top of the file:

    Imports 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:

    Dim DbPath As String = Server.MapPath("Northwind.mdb") Dim ConnStr As String Dim SQL As String ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=" & DbPath & ";" Dim MyOleDbConn As New OleDbConnection(ConnStr) MyOleDbConn.Open() Try SQL = "SELECT Count(*) FROM products" Dim MyOleDbCommand As New OleDbCommand(SQL, MyOleDbConn) Dim Result As Object Result = MyOleDbCommand.ExecuteScalar() Value.Text = "There are " & Result.ToString() & _ " products in the Northwind database." Finally MyOleDbConn.Close() End Try

  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

end example

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 Basic .NET Standard, 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.)

start example

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 Imports clause to the using clauses at the top of the file:

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

    Dim ConnStr As String Dim SQL As String ConnStr="Data Source=(local)\VSdotNET; " & _ "database=pubs;integrated security=true" Dim MySqlConn As New SqlConnection(ConnStr) MySqlConn.Open() Try SQL = "byroyalty" Dim MySqlCmd As New SqlCommand(SQL,MySqlConn) MySqlCmd.CommandType = CommandType.StoredProcedure Dim MySqlParam As New SqlParameter("@percentage",SqlDbType.Int) MySqlParam.Value = 40 MySqlCmd.Parameters.Add(MySqlParam) Dim Reader As SqlDataReader Reader = MySqlCmd.ExecuteReader()    If Reader.HasRows Then    MyGrid.DataSource = Reader    MyGrid.DataBind()    Else       Dim Message As New Label()       Message.Text = "No rows to display"       Page.Controls.Add(Message)    End If Finally MySqlConn.Close() End Try

  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

end example

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.




Microsoft ASP. NET Programming with Microsoft Visual Basic. NET Version 2003 Step by Step
Microsoft ASP.NET Programming with Microsoft Visual Basic .NET Version 2003 Step By Step
ISBN: 0735619344
EAN: 2147483647
Year: 2005
Pages: 126

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