Lesson 03: The SqlCommand Object(SqlCommand 对象中的方法)

Lesson 03: The SqlCommand Object

SqlCommand 对象

This lesson describes the SqlCommand object and how you use it to interact with a data base.  Here are the objectives of this lesson:

这节课描述了 SqlCommand 对象以及如何使用它与数据库交互。下面是本课的目标：

• Know what a command object is.
• Learn how to use the ExecuteReader method to query data.
• Learn how to use the ExecuteNonQuery method to insert and delete data.
• Learn how to use the ExecuteScalar method to return a single value.
• 知道什么是command对象
• 学习如何使用ExecuteReader方法查询数据
• 学习如何使用ExecuteNonQuery方法插入和删除对象
• 学习如何使用EXecuteScalar方法返回单一值

Introduction

介绍

A SqlCommand object allows you to specify what type of interaction you want to perform with a data base.  For example, you can do select, insert, modify, and delete commands on rows of data in a data base table.  The SqlCommand object can be used to support disconnected data management scenarios, but in this lesson we will only use the SqlCommand object alone.  A later lesson on the SqlDataAdapter will explain how to implement an application that uses disconnected data.  This lesson will also show you how to retrieve a single value from a data base, such as the number of records in a table.

SqlCommand 对象允许你指定在数据库上执行的操作的类型。比如，你能够对数据库中的行数据执行 select ， insert ， modify 以及 delete 命令。 SqlCommand 对象能被用来支持断开连接数据管理的情况，但是在这节课我们将只单独使用 SqlCommand 对象。后面关于 SqlDataAdapter 的课程将解释如何使用断开数据实现应用程序。这节课将同时展示如何从数据库中返回一个单独的值，比如表中记录的数量。

Creating a SqlCommand Object

创建 SqlCommand 对象

Similar to other C# objects, you instantiate a SqlCommand object via the new instance declaration, as follows:

与其他 C# 对象相似，通过 new 实例声明来实例化 SqlCommand 对象：

    SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

The line above is typical for instantiating a SqlCommand object.  It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object.  SqlCommand has a few overloads, which you will see in the examples of this tutorial.

上面一行是典型的实例化 SqlCommand 对象的代码。它使用一个 string 参数来保存你想要执行的命令以及一个关于 SqlConnection 对象的引用。 SqlCommand 具有重载形式，这些形式你将在以后的示例中看到。

Querying Data

查询数据

When using a SQL select command, you retrieve a data set for viewing.  To accomplish this with a SqlCommand object, you would use the ExecuteReader method, which returns a SqlDataReader object.  We'll discuss the SqlDataReader in a future lesson.  The example below shows how to use the SqlCommand object to obtain a SqlDataReader object:

当使用 SQL 的 select 命令，会得到一组数据集。为了和 SqlCommand 对象配合使用，你应该使用 ExecuteReader 方法，它返回一个 SqlDataReader 对象。我们将在后面的内容讨论 SqlDataReader 。下面的例子显示了如何使用 SqlCommand 对象来得到 SqlDataReader 对象：

// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();

In the example above, we instantiate a SqlCommand object, passing the command string and connection object to the constructor.  Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd. 

在上面的示例中，我们通过传递命令字符串核连接对象到构造函数的方式实体化了 SqlCommand 对象。然后我们通过 SqlCommand 对象 cmd 调用 ExecuteReader 方法得到了 SqlDataReader 对象。

This code is part of the ReadData method of Listing 1 in the Putting it All Together section later in this lesson.

这些代码是表 1 中 ReadData 方法的一部分，我们将在后面集中介绍。

Inserting Data

插入数据

To insert data into a data base, use the ExecuteNonQuery method of the SqlCommand object.  The following code shows how to insert data into a data base table:

要对数据库插入数据，使用 SqlCommand 对象的 ExecuteNonQuery 方法。下面的代码显示了如何向数据库表插入数据：

// prepare command string
  string insertString = @"
     insert into Categories
     (CategoryName, Description)
     values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
 
 // 1. Instantiate a new command with a query and connection
 SqlCommand cmd = new SqlCommand(insertString, conn);
 
 // 2. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

The SqlCommand instantiation is just a little different from what you've seen before, but it is basically the same.  Instead of a literal string as the first parameter of the SqlCommand constructor, we are using a variable, insertString.  The insertString variable is declared just above the SqlCommand declaration. 

SqlCommand 的实例化过程与以前看到的有一些区别，但是基本一致。在构造函数的第一个字符串参数中是用的是插入字符串变量而不三字符串字面值。该变量在 SqlCommand 声明之前被声明了。

Notice the two apostrophes ('') in the insertString text for the word "doesn''t".  This is how you escape the apostrophe to get the string to populate column properly. 

注意在 insertString 文本中“ doesn’’t ”的两个单引号（ ’’ ）。这是将它转义为适当的单引号。

Another observation to make about the insert command is that we explicitly specified the columns CategoryName and Description.  The Categories table has a primary key field named CategoryID.  We left this out of the list because SQL Server will add this field itself.  Trying to add a value to a primary key field, such as CategoryID, will generate an exception.

另外一个需要注意的是我们显式指明了列： CategoryName 和 Description 。列表中有一个主键名为 CategoryID 。我们忽略这列因为 SQL Server 将自动添加此字段。试图对主键比如 CategoryID 添加值会产生异常。

To execute this command, we simply call the ExecuteNonQuery method on the SqlCommand instance, cmd.

为了执行此命令，我们简单的对 SqlCommand 实体 cmd 调用 ExecuteNonQuery 方法。

This code is part of the InsertData method of Listing 1 in the Putting it All Together section later in this lesson.

这段代码是表 1 中 InsertData 方法的一部分，我们将在后面集中介绍。

Updating Data

更新数据

The ExecuteNonQuery method is also used for updating data.  The following code shows how to update data:

ExecuteNonQuery 方法同样用来更新数据。下面的代码显示了如何更新数据：

// prepare command string
  string updateString = @"
     update Categories
     set CategoryName = 'Other'
     where CategoryName = 'Miscellaneous'";
 
 // 1. Instantiate a new command with command text only
 SqlCommand cmd = new SqlCommand(updateString);
 
 // 2. Set the Connection property
 cmd.Connection = conn;
 
 // 3. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

Again, we put the SQL command into a string variable, but this time we used a different SqlCommand constructor that takes only the command.  In step 2, we assign the SqlConnection object, conn, to the Connection property of the SqlCommand object, cmd. 

再一次，我们将 SQL 命令赋给字符串变量，但是这次我们使用了不同的 SqlCommand 构造函数，它只适用了命令。在第 2 步，将 SqlConnection 对象 conn 赋值给 SqlCommand 对象 cmd 的连接属性。

This could have been done with the same constructor used for the insert command, with two parameters.  It demonstrates that you can change the connection object assigned to a command at any time.

这同样能够用上面 insert 命令中使用两个参数的构造函数实现。它说明了你能够在任何时候改变赋值给命令对象的连接对象。

The ExecuteNonQuery method performs the update command.

ExecuteNonQuery 方法执行更新命令。

This code is part of the UpdateData method of Listing 1 in the Putting it All Together section later in this lesson.

这些代码是表 1UpdateData 方法的一部分。我们将在本课后面集中介绍。

Deleting Data

删除数据

You can also delete data using the ExecuteNonQuery method.  The following example shows how to delete a record from a data base with the ExecuteNonQuery method:

你同样能够使用 ExecuteNonQuery 方法删除数据。下面的例子说明了如何使用 EXecuteNonQuery 方法删除数据库中的记录。

// prepare command string
  string deleteString = @"
     delete from Categories
     where CategoryName = 'Other'";
 
 // 1. Instantiate a new command
 SqlCommand cmd = new SqlCommand();
 
 // 2. Set the CommandText property
 cmd.CommandText = deleteString;
 
 // 3. Set the Connection property
 cmd.Connection = conn;
 
 // 4. Call ExecuteNonQuery to send command
 cmd.ExecuteNonQuery();

This example uses the SqlCommand constructor with no parameters.  Instead, it explicity sets the CommandText and Connection properties of the SqlCommand object, cmd. 

这个示例使用了没有参数的 SqlCommand 构造函数。取而代之的是显式地设置了 CommandText 和 SqlCommand 对象的连接属性。

We could have also used either of the two previous SqlCommand constructor overloads, used for the insert or update command, with the same result.  This demonstrates that you can change both the command text and the connection object at any time. 

我们同样能够使用 SqlCommand 构造函数在前面的两个重载形式——用来插入或者更新命令——得到相同的结果。它说明了在任何时候既能够改变命令文本又能够改变连接对象。

The ExecuteNonQuery method call sends the command to the data base.

ExecuteNonQuery 方法调用将命令传递给数据库。

This code is part of the DeleteData method of Listing 1 in the Putting it All Together section later in this lesson.

这些代码是表 1 中 DeleteData 方法的一部分。我们将在后面的内容中集中介绍。

Getting Single values

得到单一值

Sometimes all you need from a data base is a single value, which could be a count, sum, average, or other aggregated value from a data set.  Performing an ExecuteReader and calculating the result in your code is not the most efficient way to do this.  The best choice is to let the data base perform the work and return just the single value you need.  The following example shows how to do this with the ExecuteScalar method:

某些时候你想从数据库中只取一个值，它可能是关于数据集的计数、和、平均值或者其他聚合数值。使用 ExecuteReader 方法并计算代码中的结果并不是做这些事情的有效方式。最好的选择就是让数据库能够执行并且只返回你所需要的单独的值。下面的示例说明了如何使用 ExecuteScalar 方法来实现：

// 1. Instantiate a new command
  SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
 
 // 2. Call ExecuteNonQuery to send command
 int count = (int)cmd.ExecuteScalar();

The query in the SqlCommand constructor obtains the count of all records from the Categories table.  This query will only return a single value.  The ExecuteScalar method in step 2 returns this value.  Since the return type of ExecuteScalar is type object, we use a cast operator to convert the value to int.

在 SqlComand 构造函数中的查询语句要求从 Categories 表中得到所有所有记录的计数。这些查询将致返回单独的值。在第 2 步中的 ExecuteScalar 方法返回这个值。因为 ExecuteScalar 方法返回类型是 object ，我们使用转换操作符将它转换为 int 。

This code is part of the GetNumberOfRecords method of Listing 1 in the Putting it All Together section later in this lesson.

这些代码在表 GetNumberOfRecords 方法的一部分，我们将在后面集中介绍它。

Putting it All Together

集中介绍

For simplicity, we showed snippets of code in previous sections to demonstrate the applicable techniques .  It is also useful to have an entire code listing to see how this code is used in a working program.  Listing 1 shows all of the code used in this example, along with a driver in the Main method to produce formatted output.

为了简单，我们在前面的小节中展示了一部分代码。它同样对于如何在工程程序中使用是有帮助的。表 1 显示了在这个例子所使用的所有代码，并通过 Main 方法中产生格式化的输出。

Listing 1.  SqlConnection Demo

 using System;
 using System.Data;
 using System.Data.SqlClient;
 
 ///<summary>
 /// Demonstrates how to work with SqlCommand objects
 ///</summary>
 class SqlCommandDemo
 {
     SqlConnection conn;
 
     public SqlCommandDemo()
     {
         // Instantiate the connection
         conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
     }
 
     // call methods that demo SqlCommand capabilities
     static void Main()
     {
         SqlCommandDemo scd = new SqlCommandDemo();
 
         Console.WriteLine();
         Console.WriteLine("Categories Before Insert");
         Console.WriteLine("------------------------");
 
         // use ExecuteReader method
         scd.ReadData();
 
         // use ExecuteNonQuery method for Insert
         scd.InsertData();
         Console.WriteLine();
         Console.WriteLine("Categories After Insert");
         Console.WriteLine("------------------------------");
 
        scd.ReadData();
 
         // use ExecuteNonQuery method for Update
         scd.UpdateData();
 
         Console.WriteLine();
         Console.WriteLine("Categories After Update");
         Console.WriteLine("------------------------------");
 
         scd.ReadData();
 
         // use ExecuteNonQuery method for Delete
         scd.DeleteData();
 
         Console.WriteLine();
         Console.WriteLine("Categories After Delete");
         Console.WriteLine("------------------------------");
 
         scd.ReadData();
 
         // use ExecuteScalar method
         int numberOfRecords = scd.GetNumberOfRecords();
 
         Console.WriteLine();
         Console.WriteLine("Number of Records: {0}", numberOfRecords);
     }
 
     ///<summary>
     /// use ExecuteReader method
     ///</summary>
     public void ReadData()
     {
        SqlDataReader rdr = null;
 
         try
         {
             // Open the connection
             conn.Open();
 
             // 1. Instantiate a new command with a query and connection
             SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
 
             // 2. Call Execute reader to get query results
             rdr = cmd.ExecuteReader();
 
             // print the CategoryName of each record
             while (rdr.Read())
             {
                 Console.WriteLine(rdr[0]);
             }
         }
         finally
         {
             // close the reader
             if (rdr != null)
             {
                 rdr.Close();
             }
 
             // Close the connection
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 
     ///<summary>
     /// use ExecuteNonQuery method for Insert
     ///</summary>
     public void InsertData()
     {
         try
         {
             // Open the connection
             conn.Open();
 
             // prepare command string
             string insertString = @"
                 insert into Categories
                 (CategoryName, Description)
                 values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
 
             // 1. Instantiate a new command with a query and connection
             SqlCommand cmd = new SqlCommand(insertString, conn);
 
             // 2. Call ExecuteNonQuery to send command
             cmd.ExecuteNonQuery();
         }
         finally
         {
             // Close the connection
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 
     ///<summary>
     /// use ExecuteNonQuery method for Update
     ///</summary>
     public void UpdateData()
     {
         try
         {
             // Open the connection
             conn.Open();
 
             // prepare command string
             string updateString = @"
                 update Categories
                 set CategoryName = 'Other'
                 where CategoryName = 'Miscellaneous'";
 
             // 1. Instantiate a new command with command text only
             SqlCommand cmd = new SqlCommand(updateString);
 
             // 2. Set the Connection property
             cmd.Connection = conn;
 
             // 3. Call ExecuteNonQuery to send command
             cmd.ExecuteNonQuery();
        }
         finally
         {
             // Close the connection
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 
     ///<summary>
     /// use ExecuteNonQuery method for Delete
     ///</summary>
     public void DeleteData()
     {
         try
         {
             // Open the connection
             conn.Open();
 
             // prepare command string
             string deleteString = @"
                 delete from Categories
                 where CategoryName = 'Other'";
 
             // 1. Instantiate a new command
             SqlCommand cmd = new SqlCommand();
 
             // 2. Set the CommandText property
             cmd.CommandText = deleteString;
 
             // 3. Set the Connection property
             cmd.Connection = conn;
 
             // 4. Call ExecuteNonQuery to send command
             cmd.ExecuteNonQuery();
         }
         finally
         {
             // Close the connection
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 
     ///<summary>
     /// use ExecuteScalar method
     ///</summary>
     ///<returns>number of records</returns>
     public int GetNumberOfRecords()
     {
         int count = -1;
 
         try
         {
             // Open the connection
             conn.Open();
 
             // 1. Instantiate a new command
             SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
 
             // 2. Call ExecuteNonQuery to send command
             count = (int)cmd.ExecuteScalar();
         }
         finally
         {
            // Close the connection
             if (conn != null)
             {
                 conn.Close();
             }
         }
         return count;
     }
 }

In Listing 1, the SqlConnection object is instantiated in the SqlCommandDemo structure.  This is okay because the object itself will be cleaned up when the CLR garbage collector executes.  What is important is that we close the connection when we are done using it.  This program opens the connection in a try block and closes it in a finally block in each method.

在表 1 中， SqlConnection 对象在 SqlCommandDemo 结构中被实体化。这是可以的，因为当 CLR 垃圾回收器执行的时候对象本身会被清除。重要的是在我们做完了工作之后要关闭连接。此程序在每一个方法中打开在一个 try 语句块的连接，并且在 finally 语句块中关闭它。

The ReadData method displays the contents of the CategoryName column of the Categories table.  We use it several times in the Main method to show the current status of the Categories table, which changes after each of the insert, update, and delete commands.  Because of this, it is convenient to reuse to show you the effects after each method call.

ReadData 方法现实 Categories 表中的 CategoryName 列的内容。我们在 Main 方法中使用它许多次来现实 Categorie 表的当前状态，它在每一个 insert 、 update 和 delete 命令之后都会改变。因为这样，它能够在每一个函数被调用之后重用来查看效果。

Summary

总结

A SqlCommand object allows you to query and send commands to a data base.  It has methods that are specialized for different commands.  The ExecuteReader method returns a SqlDataReader object for viewing the results of a select query.  For insert, update, and delete SQL commands, you use the ExecuteNonQuery method.  If you only need a single aggregate value from a query, the ExecuteScalar is the best choice.

SqlCommand 对象允许你擦许并对数据库传送命令。它含有针对不同的命令而特定的方法。 ExecuteReader 方法返回 SqlDataReader 对象来现实查询的结果。对于 insert ， update 以及 delete 这些 SQL 命令，使用 ExecuteNonQuery 方法。如果你只需要查询的单独聚集值， ExecuteScalar 方法是最好的选择。

I hope you enjoyed this lesson and welcome you to the next one in this series, Lesson 04:  Reading Data with the SqlDataReader.