This chapter is from the book
This chapter is from the book
This chapter is from the book
Managed Commands
Managed Commands represent SQL syntax to be executed on the data store. Managed Commands can be simple SELECT statements or complex, parameterized commands.
Once a connection to a data store is established, you can retrieve, update, or insert data. One way of accomplishing this is to use a Managed Command. This is the most direct way to execute a SQL statement on a data store.
As with the Managed Connection object, there are both OleDb and SQL versions of the Managed Command—OleDbCommand and SqlCommand.
The Managed Command is similar to the classic ADO command object. In its simplest form, you create a Managed Command with the SQL statement and the connection (as either a ConnectionString or a Managed Connection object) as its parameters. In Listing 3.3 you create a SqlCommand to execute a simple SELECT statement against the Northwind database.
Note
For the bulk of this chapter I will be showing samples using the SQL Managed Provider. You can use the OleDb Managed Provider by changing the Managed Provider classes from SqlWidget to OleDbWidget. For example, in Listing 3.3 you could use the OleDbCommand class in replacement of the SqlCommand class. Remember that the OleDb Managed Provider uses the System.Data. OleDb namespace instead of the System.Data.SqlClient namespace.
Listing 3.3 Creating a SqlCommand Object
[VB]
01: <%@ Page Language="VB" %>
02: <%@ Import Namespace="System.Data.SqlClient" %>
03: <script runat="server">
04: Sub Page_Load(Sender As Object, E As EventArgs)
05: Dim myConnection As SqlConnection
06: Dim myCommand As SqlCommand
07: myConnection = New SqlConnection("server=localhost;
database=Northwind; uid=sa; pwd=;")
08: myCommand = New SqlCommand("SELECT * FROM Customers", myConnection)
09: End Sub
10: </script>
[C#]
01: <%@ Page Language="C#" %>
02: <%@ Import Namespace="System.Data.SqlClient" %>
03: <script runat="server">
04: void Page_Load(Object sender, EventArgs e){
05: SqlConnection myConnection;
06: SqlCommand myCommand;
07: myConnection = new SqlConnection("server=localhost; database=Northwind; uid=sa; pwd=;");
08: myCommand = new SqlCommand("SELECT * FROM Customers", myConnection);
09: }
10: </script>
[VB & C#]
11: <html>
12: <form runat="server" method="post">
13: <body>
14: <asp:DataGrid runat="server" id="myDataGrid" />
15: </form>
16: </body>
17: </html>
In Listing 3.3 you create a Web Form that uses the SQL Managed Provider to create a SqlConnection and a SqlCommand. To execute the SqlCommand on the database you call one of the provided execute methods.
ExecuteNonQuery—Executes a SQL statement that does not return any records.
ExecuteReader—Returns a DataReader object.
ExecuteScalar—Executes the SQL statement and returns the first column of the first row.
ExecuteStream (SQL Managed Provider only)—Executes a SQL statement and returns the results as an XML stream.
The only thing you're missing before calling one of the execute methods is an object to hold the results that are returned. For this example you will use the ExecuteReader() method and return the results as a DataReader object (SqlDataReader or OleDbDataReader). Once you have a DataReader object you can work with the data. For now you will bind the DataReader to a DataGrid server control. In Listing 3.4 you will use the ExecuteReader() method to return the results of the command execution into a DataReader object.
Listing 3.4 Executing a SqlCommand Object and Returning the Results in a DataReader
[VB]
01: <%@ Page Language="VB" %>
02: <%@ Import Namespace="System.Data.SqlClient" %>
03: <script runat="server">
04: Sub Page_Load(Sender As Object, E As EventArgs)
05: Dim myConnection As SqlConnection
06: Dim myCommand As SqlCommand
07: myConnection = New SqlConnection("server=localhost;
database=Northwind; uid=sa; pwd=;")
08: myCommand = New SqlCommand("SELECT * FROM Customers", myConnection)
09: myConnection.Open()
10: Dim myDataReader As SqlDataReader = myCommand.ExecuteReader()
11: myDataGrid.DataSource = myDataReader
12: myDataGrid.DataBind()
13: myConnection.Close()
14: End Sub
15: </script>
[C#]
01: <%@ Page Language="C#" %>
02: <%@ Import Namespace="System.Data.SqlClient" %>
03: <script runat="server">
04: void Page_Load(Object sender, EventArgs e){
05: SqlConnection myConnection;
06: SqlCommand myCommand;
07: myConnection = new SqlConnection("server=localhost;
database=Northwind; uid=sa; pwd=;");
08: myCommand = new SqlCommand("SELECT * FROM Customers", myConnection);
09: myConnection.Open();
10: SqlDataReader myDataReader = myCommand.ExecuteReader();
11: myDataGrid.DataSource = myDataReader;
12: myDataGrid.DataBind();
13: myConnection.Close();
14: }
15: </script>
[VB & C#]
16: <html>
17: <form runat="server" method="post">
18: <body>
19: <asp:DataGrid runat="server" id="myDataGrid" />
20: </form>
21: </body>
22: </html>
In Listing 3.4 you extend the code in Listing 3.3 to execute the SqlCommand object using the ExecuteReader() method. Since this method returns the results in a DataReader object, you first create an instance of the SqlDataReader class on line 10, and set it to the returned result of the SqlCommand.ExecuteReader() method. You will notice that I create an instance of the DataReader and assign it to the results of the Execute() on the same line. This is just another way of constructing an object in .NET, rather than doing the same thing across two lines of code.
Once the command has executed and the DataReader has been created, you bind the DataReader to a DataGrid server control (line 11) by setting the DataSource property of the DataGrid as the DataReader. Once the DataSource property is set you call the DataBind() method of the DataGrid to bind the command results to the output of the DataGrid.
Figure 3.2 shows the ASP.NET Web Form with the results from the Managed Command execution in a DataGrid.
){kind=link}
Figure 3.2 Using the Managed Providers you can create a connection to a database, execute a command, and display the results on an ASP.NET Web Form.