Lesson 1: Creating and Executing Command Objects in Visual Studio .NET

Encoder Denso QR Bar Code in Visual Studio .NET Lesson 1: Creating and Executing Command Objects

10. Double-click the Execute Sproc button to create the button-click event handler and switch to code view. For this handler, you will copy and reuse much of the code from the foregoing example. Typically, in an efficient production application, you would refactor similar code out into its own method but, for the sake of clarity in this demon stration, just copy the code you want to reuse into the additional button-click event handlers. The main difference between calling a standard SQL command and calling a stored procedure is that when calling a stored procedure you set the CommandType property to StoredProcedure and set the CommandText property to the name of the stored procedure. Then you call the ExecuteReader method just like execut ing a SQL statement and iterate through the reader exactly as before. 11. Add the following code to the ExecuteSprocButton_Click event handler:

ExecuteSprocCommand.CommandText = "Ten Most Expensive Products"

ExecuteSprocCommand.Connection.Open()

For i As Integer = 0 To reader.FieldCount - 1

results.Append(reader(i).ToString & vbTab)

results.Append(Environment.NewLine)

End While reader.Close()

ExecuteSprocCommand.Connection.Close()

ResultsTextBox.Text = results.ToString // C# System.Text.StringBuilder results = new System.Text.StringBuilder(); SqlCommand ExecuteSprocCommand = new SqlCommand(); ExecuteSprocCommand.Connection = NorthwindConnection;

ExecuteSprocCommand.CommandType = CommandType.StoredProcedure;

ExecuteSprocCommand.CommandText = "Ten Most Expensive Products";

ExecuteSprocCommand.Connection.Open();

6

SqlDataReader reader = ExecuteSprocCommand.ExecuteReader(); while (reader.Read()) {

for (int i = 0; i<reader.FieldCount;i++)

results.Append(reader[i].ToString() + "\t"); } results.Append(Environment.NewLine); }

reader.Close();

ExecuteSprocCommand.Connection.Close();

ResultsTextBox.Text = results.ToString();

Let s add some more functionality by writing some code to perform a catalog operation and execute a command that creates a new table in the database. The execution methods in the preceding table show that performing catalog opera tions requires the use of the ExecuteNonQuery method of the Command object. For this method, you do not use a data reader because no data will be returned by the command. After creating the table, simply inspect the database in Server Explorer and verify that the command executed successfully. 12. Double-click the Create Table button to create the button-click event handler and switch to code view. 13. Add the following code to the CreateTableButton_Click event handler:

' VB Dim CreateTableCommand As New SqlCommand CreateTableCommand.Connection = NorthwindConnection CreateTableCommand.CommandType = CommandType.Text CreateTableCommand.CommandText = "CREATE TABLE SalesPersons (" & _ "[SalesPersonID] [int] IDENTITY(1,1) NOT NULL, " & _ "[FirstName] [nvarchar](50) NULL, " & _ "[LastName] [nvarchar](50) NULL)" CreateTableCommand.Connection.Open()

CreateTableCommand.ExecuteNonQuery()

CreateTableCommand.Connection.Close()

// C# SqlCommand CreateTableCommand = new SqlCommand();

CreateTableCommand.Connection = NorthwindConnection;

CreateTableCommand.CommandType = CommandType.Text;

"[LastName] [nvarchar](50) NULL)";

CreateTableCommand.Connection.Open();

CreateTableCommand.ExecuteNonQuery();

CreateTableCommand.Connection.Close();

14. Run the application and click the Create Table button. Navigate to Server Explorer and refresh the Tables node of the Northwind Traders database. Verify the existence of the new SalesPersons table.

Command objects are used to execute SQL statements and stored procedures against a database. There are specific Command objects for each of the .NET Framework Data Pro viders. The CommandType property determines whether a command executes a SQL statement or stored procedure. Commands can also be used to perform catalog operations on a database. Commands can be executed asynchronously. DataReader objects are created when command execution returns tabular data.

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

Answers to these questions and explanations of why each choice is right or wrong are located in the Answers section at the end of the book.

1. What are the Command object property settings to execute a stored procedure (Choose all that apply.) A. CommandType = Text, CommandText = stored procedure name B. CommandType = Text, CommandText = SQL syntax to execute the stored procedure C. CommandType = StoredProcedure, CommandText = SQL syntax to execute the stored procedure D. CommandType = StoredProcedure, CommandText = stored procedure name