The Command: Executing SQL Statements


The Command object allows you to directly execute SQL statements such as INSERT, SELECT, UPDATE, and DELETE against a data source for reading, writing, and updating a database. You can also use the Command object to execute stored procedures. The SqlCommand, OleDbCommand, and OdbcCommand classes represent the Command objects in the Sql, OleDb, and ODBC data providers, respectively.

Table 4-7 describes some of the more important properties of the Command class for the Sql data provider. The CommandText listed in this table can contain either a SQL statement or a stored procedure name. The CommandType determines which one of these forms the CommandText takes.

Table 4-7: The Command Properties

PROPERTY

DESCRIPTION

CommandText

Could be a SQL statement, a stored procedure, or a database table name depending on the CommandType

CommandTimeout

Wait time before terminating the execute command

CommandType

An enumeration of values Text, StoredProcedure, or TableDirect

Connection

A Connection representing the ActiveConnection

DesignTimeVisible

Indicates whether the command should be visible at design-time

Parameters

A collection of parameters (SqlParameterCollection)

Transaction

A transaction

UpdateRowSource

Represents how command results are applied to a DataRow when used by the Update method

Listing 4-7 creates a SqlCommand object, sets its properties, and then reads back its properties to display them. You'll see the rest of the properties—such as Transaction—in the related sections of this chapter.

Listing 4-7: Setting and Getting SqlCommand Properties

start example
 ' Connection and SQL strings     Dim ConnectionString As String = "Integrated Security=SSPI;" & _              "Initial Catalog=Northwind;Data Source=MCB;"     Dim SQL As String = "SELECT * FROM Orders"     ' Create connection object     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     ' Create command object     Dim cmd As SqlCommand = New SqlCommand()     cmd.Connection = conn     cmd.CommandText = SQL     cmd.CommandTimeout = 30     cmd.CommandType = CommandType.Text     ' Open connection     conn.Open()     ' Read Command properties     Dim str As String     str = "Connection String: " + cmd.Connection.ConnectionString.ToString()     str = str + " , SQL Statement :" + cmd.CommandText     str = str + " , Timeout :" + cmd.CommandTimeout.ToString()     str = str + " , CommandTyoe:" + cmd.CommandType.ToString()     MessageBox.Show(str)     ' close connection     conn.Close()     'Dispose  conn.Dispose() 
end example

Creating a Command Object

There are a number of ways to construct a Command object. You can create a Command and set its connection and SQL string, or you can create a Command by passing the connection string and SQL string as parameters of the Command constructor.

The following examples show you three different ways to create a Command object. This code constructs a connection and a SQL string:

 ' Connection and SQL strings Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;" ' Create connection object Dim conn As SqlConnection = New SqlConnection(ConnectionString) Dim SQL As String = "SELECT * FROM Orders" 

Now create a SqlCommand object using a constructor with no arguments. Later you set SqlCommand's Connection and CommandText properties to connect to a Connection and set the SQL statement, which this command will be executing:

 ' Create command object Dim cmd1 As SqlCommand = New SqlCommand() cmd1.Connection = conn cmd1.CommandText = SQL cmd1.CommandTimeout = 30 cmd1.CommandType = CommandType.Text 

In the second form, you create a SqlCommand object by directly passing a SQL query and the SqlConnection object as the first and second arguments:

 // Create command object Dim cmd2 As SqlCommand = New SqlCommand(SQL, conn) 

The third way of creating a Command object is to create a command by just passing a SQL query as the argument and setting its Connection property later:

 // Create command object Dim cmd3 As SqlCommand = New SqlCommand(SQL) cmd3.Connection = conn 

Listing 4-8 shows you how to connect to the Northwind SQL Server database, read all the records from the Orders table, and output the first and second field's data to the console. The new things you'll notice in this code are ExecuteReader and SqlDataReader. A SqlDataReader is a DataReader class, and ExecuteReader fills data from a data source to the DataReader based on the SQL query. (We discuss DataReader classes in the next section.)

Listing 4-8: Using SqlCommand to Read Data from a Database

start example
 ' Connection and SQL strings Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;" Dim SQL As String = "SELECT * FROM Orders" ' Create connection object Dim conn As SqlConnection = New SqlConnection(ConnectionString) ' Create command object Dim cmd As SqlCommand = New SqlCommand(SQL) cmd.Connection = conn ' Open connection conn.Open() ' Call command's ExecuteReader Dim reader As SqlDataReader = cmd.ExecuteReader() While reader.Read()   If Not reader.IsDBNull(0) Then    Console.Write("OrderID:" + reader.GetInt32(0).ToString())    Console.Write(" ,")    Console.WriteLine("Customer:" + reader.GetString(1).ToString())  End If End While ' close reader and connection reader.Close() conn.Close() 'Dispose conn.Dispose() 
end example

The output of Listing 4-8 will list records from the Customers table to the console.

Creating and Using OleDbCommand

Like the SqlCommand object, you create OleDb and ODBC Command objects by using the OleDbCommand and OdbcCommand classes. You can pass the same arguments as discussed previously. The only difference is the connection string. For example, Listing 4-9 uses OleDbCommand and OleDbConnection to connect to a SQL Server database. As you can see, the only changes are the class prefixes and the connection string. Similarly, you can use the OdbcCommand object.

Listing 4-9: Using OleCommand to Access an Access Database

start example
 ' Connection and SQL strings     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=c:\\Northwind.mdb"     Dim SQL As String = "SELECT * FROM Orders"     ' Create connection object     Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)     ' Create command object     Dim cmd As OleDbCommand = New OleDbCommand(SQL)     cmd.Connection = conn     ' Open connection     conn.Open()     ' Call command's ExecuteReader     Dim reader As OleDbDataReader = cmd.ExecuteReader()     While reader.Read()       Console.Write("OrderID:" + reader.GetInt32(0).ToString())       Console.Write(" ,")       Console.WriteLine("Customer:" + reader.GetString(1).ToString())     End While     ' close reader and connection     reader.Close()     conn.Close() conn.Dispose() 
end example

Using the CommandType Enumeration

The CommandType enumeration decides what type of object a command will be executed as. The CommandType enumeration can have any of the three values defined in Table 4-8.

Table 4-8: The CommandType Enumeration Members

MEMBERS

DESCRIPTION

StoredProcedure

The name of the stored procedure.

TableDirect

The CommandText property should be set to the table name, and all rows and columns in the table will be returned.

Text

A SQL text command.

As you can see from Table 4-8, you can call a stored procedure, use TableDirect, or execute a SQL command. We present these options individually in the following sections.

Calling a Stored Procedure

Calling stored procedures using the Command object is similar to executing a SQL query. This section gives you a quick overview of how to execute stored procedures. (We cover stored procedures in more detail in Chapter 11.)

You need to set the CommandType property of a Command object before calling a stored procedure. By default, the CommandType property is Text. If you want to call a stored procedure, you need to set the CommandType to StoredProcedure and the CommandText to the stored procedure name. After that you can call the ExecuteReader method or other methods. You can also pass parameters to the procedure by setting parameter values in the Command and then calling ExecuteReader on the Command object. Alternatively, you can pass a procedure name as a string when creating a Command object. Listing 4-10 shows the settings of the CommandType and CommandText properties of SqlCommand. As you can see, it calls an existing SQL Server Northwind database stored procedure, Sales By Year.

Listing 4-10: Calling a Stored Procedure Using SqlCommand

start example
 ' Create a SqlCommand with stored procedure as string Dim cmd As SqlCommand = New SqlCommand("Sales By Year", conn) ' set Command's CommandType as StoredProcedure cmd.CommandType = CommandType.StoredProcedure 
end example

Note

Executing stored procedures can be helpful in improving the performance of an application in multiuser and Web applications because a stored procedure executes on the server itself.

The Northwind database in SQL Server contains a few stored procedures. One is called Sales By Year (see Listing 4-11).

Listing 4-11: The Sales By Year Stored Procedure in the Northwind Database

start example
 SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID =  "Order Subtotals".OrderID WHERE Orders.ShippedDate  BETWEEN @Beginning_Date AND @Ending_Date 
end example

This stored procedure takes two parameters, Beginning_Date and Ending_Date. The procedure selects all of the orders between these two dates. It also performs a join with the Order Subtotals from the Order Subtotal view, which calculates the subtotals of each. If you want to execute this stored procedure in ADO.NET, you just create a Command object of type StoredProcedure and then call the Command object's ExecuteReader method. You then cycle through the results in the reader that you're looking for from your stored procedure. Listing 4-12 executes a stored procedure that selects all the orders in July and displays their order IDs.

Listing 4-12: Executing and Reading the Results of a Stored Procedure

start example
 Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;"     Dim SQL As String = "SELECT * FROM Orders"     ' Create connection object     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     ' Create a SqlCommand with stored procedure as string     Dim cmd As SqlCommand = New SqlCommand("Sales By Year", conn)     cmd.Connection = conn     ' set Command's CommandType as StoredProcedure     cmd.CommandType = CommandType.StoredProcedure     ' Create a SqlParameter and add a parameter     Dim parm1 As SqlParameter = cmd.Parameters.Add("@Beginning_Date", _     SqlDbType.DateTime, 20)     parm1.Value = "7/1/1996"     Dim parm2 As SqlParameter = cmd.Parameters.Add("@Ending_Date", _     SqlDbType.DateTime, 20)     parm2.Value = "7/31/1996"     ' Open connection     conn.Open()     ' Call ExecuteReader to execute the stored procedure     Dim reader As SqlDataReader = cmd.ExecuteReader()     Dim orderlist As String = ""     ' Read data from the reader     While reader.Read()       Dim result As String = reader("OrderID").ToString()       orderlist += result + " "     End While     ' close the connection and reader     reader.Close()     conn.Close()     conn.Dispose()     ' Print data on the console     Console.WriteLine("Orders in July")     Console.WriteLine("===============")     Console.WriteLine(orderlist) 
end example

If you wanted to look at the subtotals along with the orders, you'd just add a DataReader index for dereferencing the subtotal and concatenate with the order ID. Listing 4-13 shows the new DataReader loop.

Listing 4-13: Adding the Subtotal Listing to the Output of the Stored Procedure Results

start example
 While reader.Read()  Dim nextID As String = reader("OrderID").ToString()  Dim nextSubtotal As String = reader("Subtotal").ToString()  orderlist += nextID + ", " + nextSubtotal + ", " End While 
end example

The result of replacing this line of code in Listing 4-13 gives output that returns order IDs and subtotals in the month of July in the Northwind database.

Using TableDirect

You can also use the TableDirect CommandType to read information directly from a table. You need to make two changes in the example to execute a table by setting TableDirect. First, you need to set Command's CommandText property to the table name; second, you need to set the CommandType property to CommandType.TableDirect.

The following code reads the Customers table and sets the CommandType property to CommandType.TableDirect:

 cmd.CommandText = "Customers" cmd.CommandType = CommandType.TableDirect 

Listing 4-14 reads information from the Customers table by setting the TableDirect method and displaying it on the console.

Listing 4-14: Using TableDirect to Read a Table

start example
 ' Create a Connection Object     Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _      "Data Source=c:\\Northwind.mdb"     Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)     Dim cmd As OleDbCommand = New OleDbCommand()     cmd.Connection = conn     cmd.CommandText = "Customers"     cmd.CommandType = CommandType.TableDirect     conn.Open()     Dim reader As OleDbDataReader = cmd.ExecuteReader()     Console.WriteLine("Customer Id, Contact Name, Company Name")     Console.WriteLine("=======================================")     While reader.Read()       Console.Write(reader("CustomerID").ToString())       Console.Write(", " + reader("ContactName").ToString())       Console.WriteLine(", " + reader("CompanyName").ToString())     End While     ' release objects     reader.Close()     conn.Close() conn.Dispose() 
end example

Executing a Command

You just saw the ExecuteReader method, which reads data from a data source and fills the DataReader object depending on the data provider. Besides ExecuteReader, the Command object defines three more execute methods. These methods are ExecuteNonQuery, ExecuteScalar, and ExecuteXmlReader . The ExecuteReader method produces a DataReader, which is the solution for streaming data through ADO.NET. (We discuss the DataReader in more details in "The DataReader: Walking through the Data.")

The ExecuteNonQuery method allows you to execute a SQL statement or a Command object with the CommandText property and a SQL statement without using a DataSet. This method doesn't take any parameters and returns the number of rows affected by the execute operation.

For example, you could have an UPDATE, INSERT, or DELETE statement in your CommandText and then call ExecuteNonQuery to execute it directly on your database.

Listing 4-15 shows how to insert a row into the Northwind database using the ExecuteNonQuery method. You can even use UPDATE and DELETE SQL queries to update and delete data from a database. (We use these statements in later examples.) In this example, you create an INSERT query and call ExecuteNonQuery.

Listing 4-15: Adding Records to a Table Using the INSERT SQL Statement

start example
 ' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _     "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString)  open an existing Connection to the Database and Create a ' Command Object with it: conn.Open() Dim cmd As SqlCommand = New SqlCommand("Customers", conn) ' Assign the SQL Insert statement we want to execute to the CommandText cmd.CommandText = "INSERT INTO Customers" & _  "(Address, City, CompanyName, ContactName, CustomerID)" & _   "VALUES ('111 Broad St.', 'NY', 'Xerox', 'Fred Biggles', 1400)" ' Call ExecuteNonQuery on the Command Object to execute insert Dim res As Integer res = cmd.ExecuteNonQuery() Console.WriteLine("Affected Rows :" + res.ToString()) ' release objects conn.Close() conn.Dispose() 
end example

Note

If you're deleting, inserting, and updating data with known parameters, using Execute methods is faster than using DataAdapter's Update method.

ExecuteScalar is a handy method for using a SQL statement that retrieves a single value. A good example of this is retrieving the number of rows from a database. Listing 4-16 retrieves the total number of rows from the Customers table. Then you assign the SQL command for getting the row count in Customers to the Command object, and you call ExecuteScalar to retrieve the counter.

Listing 4-16: Using the ExecuteScalar Method to Retrieve a Single Value

start example
    ' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;"     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     ' open an existing Connection to the Database and Create a     ' Command Object with it:     conn.Open()     Dim cmd As SqlCommand = New SqlCommand("Customers", conn)     ' Assign the SQL Insert statement we want to execute to the CommandText     cmd.CommandText = "SELECT Count(*) FROM Customers"     ' Call ExecuteNonQuery on the Command Object to execute insert     Dim res As Integer     res = cmd.ExecuteScalar()     Console.WriteLine("Total Rows :" + res.ToString())     ' release objects     conn.Close() conn.Dispose() 
end example

If you run Listings 4-17, it will display only the total row number on the console.

Listing 4-17: DataReader Reads Data from a SQL Server Database

start example
 ' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _     "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) Dim SQL As String = "SELECT * FROM Customers" ' open a connection conn.Open() Dim cmd As SqlCommand = New SqlCommand(SQL, conn) ' Call ExecuteNonQuery on the Command Object to execute insert Dim res As Integer ' Call ExecuteReader to return a DataReader Dim reader As SqlDataReader = cmd.ExecuteReader() Console.WriteLine("Customer ID, Contact Name," & _ "Contact Title, Address") Console.WriteLine("===================================") While reader.Read()  If Not reader.IsDBNull(0) Then    Console.Write(reader("CustomerID").ToString() + ", ")   Console.Write(reader("ContactName").ToString() + ", ")   Console.Write(reader("ContactTitle").ToString() + ", ")    Console.WriteLine(reader("Address").ToString() + ", ") End If End While Console.WriteLine("Affected Records: " & _ 'reader.RecordsAffected.ToString()) ' release objects conn.Close() conn.Dispose() 
end example

Using Other Command Methods

The Cancel method of a SqlCommand object tries to cancel an execute operation. If there's no execute operation or the Cancel method fails to cancel the execution, nothing happens.

The CreateParameter method creates a SqlParameter object.

The Prepare method creates a prepared version of the command. It doesn't affect the operation if CommandType is TableDirect. Before calling this method, you need to specify the data type of each parameter in the statement to be prepared.

The ResetCommandTimeout method resets the command timeout value to the default value, which is 30 seconds.

We use these methods in later examples.




Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214

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