SqlCommand
2008-05-06 15:24
|
使用SqlCommand对象
执行动态SQL语句和存储过程是应用程序所需的两种最常见的数据库活动。动态SQL语句是指从客户端应用程序发送到数据库服务器时由数据库服务器读取并执行的SQL语句。在数据库接收这些SQL语句时，首先对它们进行解析以确保语法正确，然后数据库引擎创建一个访问计划——主要确定处理该SQL语句的最佳方法——然后执行该语句。与经常用于执行SQL DML操作(如创建表)或数据访问操作(执行ad hoc查询)的动态SQL语句不同，存储过程通常用于执行预先定义的查询和数据库更新操作。存储过程形成了大多数数据库应用程序的主干。动态SQL语句和存储过程之间的主要区别是，存储过程一般是在执行应用程序之前创建的，并驻留在数据库中。这使得存储过程的性能远远超过了动态SQL语句，因为解析SQL语句和创建数据访问计划的工作早已经被完成。值得注意的是，使用由SqlCommandBuilder类创建的动态SQL语句可以将ADO.NET DataSet中的数据变化传回到数据库中，或者可以使用存储过程将它们写回到数据库。然而，您并不一定要使用DataSet和DataAdapter来更新数据库。在不需要DataSet提供的数据绑定和定位功能的情况下，Command对象可以提供更简单更有效的方法来更新数据库。在接下来的部分，您将会看到如何使用SqlCommand对象来执行ad hoc查询、如何执行SQL DDL语句为目标数据库构建一个表，以及两个使用存储过程的示例。第一个存储过程示例将参数传递到一个存储过程中，第二个示例执行一个提供返回值的存储过程。
表6-3列出了SqlCommand对象和OleDbCommand对象支持的所有不同的SQL命令的执行方法。
表6-3 SqlCommand SQL语句执行方法
6.7.1 执行动态SQL语句
动态SQL为使用数据库提供了极为灵活的机制。动态SQL允许您执行ad hoc查询并返回活动查询的结果，以及执行SQL DDL语句创建数据库对象。下面的SQLCommandNonQuery子程序提供了一个示例示范了如何对ADO.NET SqlCommand对象使用动态SQL来检查表是否存在，如果不存在，则有条件地创建一个表：
Private Sub SQLCommandNonQuery(cn As SqlConnection)
Dim sSQL As String = ""
Dim cmd As New SqlCommand(sSQL, cn)
Try
' First drop the table
sSQL = "IF EXISTS " _
& "(SELECT * FROM dbo.sysobjects WHERE id = " _
& "object_id(N’[Department]’) " _
& "AND OBJECTPROPERTY(id, N’IsUserTable’) = 1) " _
& "DROP TABLE [department]"
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
' Then create the table
sSQL = "CREATE TABLE Department " _
& "(DepartmentID Int NOT NULL, " _
& "DepartmentName Char(25), PRIMARY KEY(DepartmentID))"
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
Catch e As Exception
MsgBox(e.Message)
End Try
End Sub
在这个SQLCommandNonQuery子程序的第一部分，您可以看到，SQL Server连接对象被当作一个参数传递。并且使用sSQL变量包含动态SQL语句，实例化一个名为cmd的SqlCommand对象实例。在该示例中，SqlCommand对象cmd的构造函数使用了两个参数——第一个是包含要执行的SQL语句的字符串，第二个是提供目标数据库服务器连接的SqlConnection对象。这里的sSQL字符串最初为空。接下来设置一个Try-Catch结构来执行SQL命令。Try-Catch中的第一个活动为sSQL变量赋值了一个SQL语句，它检查是否存在department表。在这个SQL语句中，您可以看到有一个SELECT语句查询SQL Server的sysobjects表来确定是否存在一个名为Department的User Table。如果发现Department表，则执行DROP TABLE语句从目标数据库中删除这个表。如果没有发现Department表，则不采取进一步的行动。为了真正执行SQL语句，则将sSQL变量中的值赋给cmd对象的CommandText特性，然后使用SqlCommand对象cmd的ExecuteNonQuery方法将该命令发送到SQL Server系统。ExecuteNonQuery方法用于执行一个不返回结果集或特定返回值的SQL语句。
在初次使用DROP TABLE SQL命令之后，按照相同的顺序执行Create Table命令。首先为sSQL变量赋值SQL CREATE TABLE语句，该语句创建了一个由两个列组成的名为Department的表。第一列是名为DepartmentID的整型数据类型，它也是主键，第二列是名为DepartmentName的25个字符的数据类型。接下来将sSQL变量中的值复制到cmd对象的CommandText特性中，并调用ExecuteNonQuery方法执行CREATE TABLE SQL语句。接下来成功执行ExecuteNonQuery方法，Department Table位于sDB变量先前确定的数据库中。
如果Try代码段中包含的任何操作发生错误，则执行Catch部分的代码，并给出一个消息框，显示异常情况的文本。
6.7.2 执行参数化的SQL语句
除了执行动态SQL语句之外，您还可以使用SqlCommand对象执行存储过程和参数化的SQL语句。动态SQL和预定的SQL之间的主要区别是，在运行动态SQL语句之前必须对它进行解析并创建一个访问计划(类似于SQL Server等一些数据库系统的处理方法非常灵活，而且它们也确实保存动态语句一段时间。然后在接下来执行该语句的时候使用已有的访问计划。虽然如此，这依赖于数据库活动，而且使用动态SQL也不能保证该计划立即可用)。您可以将预定的SQL语句当作存储过程和动态SQL的混合。与存储过程一样，它们在运行时可以接受不同的参数值。与动态SQL一样，它们在数据库中并不稳定。在应用程序执行该SQL语句时将解析SQL语句并创建访问计划。然而，与动态SQL不同，在初次制定预定的SQL语句时，解析并创建访问计划的过程只执行一次。接下来执行的语句利用了已有的访问计划。该访问计划通常保留在过程缓存器中，直到该连接被终止。以下示例显示了如何使用ADO.NET SqlCommand对象创建和执行预定的SQL语句：
Private Sub SQLCommandPreparedSQL(cn As SqlConnection)
' Set up the Command object's parameter types
Dim cmd As New SqlCommand("INSERT INTO department VALUES" & _
"(@DepartmentID, @DepartmentName)", cn)
Dim parmDepartmentID = _
New SqlParameter("@DepartmentID", SqlDbType.Int)
parmDepartmentID.Direction = ParameterDirection.Input
Dim parmDepartmentName = _
New SqlParameter("@DepartmentName", SqlDbType.Char, 25)
parmDepartmentName.Direction = ParameterDirection.Input
' Add the parameter objects to the cmd Parameter’s collection
cmd.Parameters.Add(parmDepartmentID)
cmd.Parameters.Add(parmDepartmentName)
Try
cmd.Prepare()
' Execute the prepared SQL statement to insert 10 rows
Dim i As Integer
For i = 0 To 10
parmDepartmentID.Value = i
parmDepartmentName.Value = "New Department " & CStr(i)
cmd.ExecuteNonQuery()
Next
Catch e As Exception
MsgBox(e.Message)
End Try
End Sub
您可以看到，CommandPrepareSQL子程序的顶部引入了一个名为cn的SqlConnection对象，接下来创建了一个名为cmd的新SqlCommand对象。在该示例中，构造函数使用了两个参数。第一个参数用于为cmd对象赋值一个SQL语句。它可以是一个SQL语句或存储过程的名称。这里的SQL语句是向Department表中添加两列值的INSERT语句。
该示例中需要注意的要点是SQL语句中使用的参数标记的格式。参数标记用于指示预定SQL语句中可替换的字符。在运行时，将使用SqlCommand对象的Parameters集合中提供的实际值来替换这些参数。与使用问号(?)指示可替换参数的ADO不同，SqlCommand对象要求所有参数标记以@符号开头。该示例显示了两个参数标记：@DepartmentID和@DepartmentName。SqlCommand构造函数的第二个参数将SqlCommand对象cmd与前面传入的SqlConnection对象cn结合在一起。
接下来创建了两个SqlParameter对象。第一个参数对象parmDepartmentID用于提供第一个参数标记(@DepartmentID)的值。同样，第二个参数对象parmDepartmentName提供第二个可替换参数(@DepartmentName)使用的值。该子程序中使用的代码示例显示了要被传递到SqlParameter构造函数中的3个参数。第一个参数提供了参数名。这里需要确保提供给SqlParameter对象中构造函数的名称与预定SQL语句的参数标记中使用的名称匹配。第二个参数被传递到SqlParameter构造函数的重载版本中，它指定了该参数的数据类型。
这里的Direction特性被设置为使用ParameterDirection.Input枚举类型进行输入。表6-4列出了SqlParameter Direction特性可用的枚举类型。
表6-4 SqlParameterDirection枚举
在创建SqlParameter对象之后，接下来将它们添加到SqlCommand对象的Parameters集合中。您可以看到，前面清单中使用SqlCommand对象的Parameters集合的Add方法将parmDepartmentID和parmDepartmentName SqlParameter对象添加到SqlCommand对象cmd中。SqlParameter对象的添加顺序并不重要。接下来，在Try-Catch代码段中使用Prepare语句指定该语句。注意，Prepare方法是在描述了所有参数特性之后才执行的。
接下来使用For-Next循环向新建的Department表中添加10行。在For-Next循环中，每个参数对象的Value特性被赋值为一个新的数据值。为了简化起见，parmDepartmentID参数被赋值为变量i中包含的循环计数器的值，而parmDepartmentName参数被赋值为一个包含“New Department”和循环计数器的当前值字符串。最后，SqlCommand对象的ExecuteNonQuery方法用于执行该SQL语句。在本示例中使用了ExecuteNonQuery，因为该示例使用了不返回任何值的SQL活动查询。从SQL Server的角度看，运行ExecuteNonQuery方法会导致该服务器使用sp_execute命令真正执行插入操作。
如果在任何操作过程中发生了错误，则执行Catch部分的代码，并给出一个消息框，显示异常情况的文本。
6.7.3 执行带返回值的存储过程
存储过程是大多数数据库应用程序的核心。除了它们的性能优势以外，存储过程作为一种机制还可以对它提供的预定界面的数据访问进行限制。类似于预定的SQL语句，由于存储过程是在使用之前进行编译，因此它能获得更强的性能。这允许数据库放弃通常所需的解析步骤，跳过创建访问计划的需求。存储过程是大多数数据库应用程序的实际工作设备，它们几乎总是用于数据库的插入、更新和删除操作，并检索单个值和结果集。在接下来的示例中，您将会看到如何使用SqlCommand对象执行SQL Server存储过程。在第一个示例之后，您将会看到如何执行存储过程来接受一个输入参数并返回一个标量值。
以下清单显示了创建CostDiff存储过程(将被添加到AdventureWorks数据库示例中)所需的T-SQL源代码。通过使用SQL Server Management Studio执行该代码，可以创建这个存储过程：
CREATE PROCEDURE CostDiff
@ProductID int
AS
DECLARE @CostDiff money
SELECT CostDiff = (ListPrice - StandardCost)
FROM Production.Product WHERE ProductID = @ProductID
RETURN @CostDiff
在这个清单中，您可以看到CostDiff存储过程接受了一个输入参数。该参数是一个用于标识ProductID的Integer值。CostDiff存储过程返回AdventureWorks数据库的Production.Product表中ProductID的成本差额。成本差额是通过检索ListPrice数并从StandardCost列的值中减去该值计算得到的。然后将这个值赋给@CostDiff变量，该变量由存储过程作为一个标量值返回。在AdventureWorks数据库中创建了示例存储过程之后，您的ADO.NET应用程序可以对它进行调用。以下示例显示了如何在VB.NET中使用SqlCommand类来执行CostDiff存储过程并检索它返回的标量值：
Private Sub SQLCommandSPScalar(cn As SqlConnection)
' Create the command object and set the SQL statement
Dim cmd As New SqlCommand("CostDiff", cn)
cmd.CommandType = CommandType.StoredProcedure
' Create the parameter
cmd.Parameters.Add("@ProductID", SqlDbType.Int)
cmd.Parameters("@ProductID").Direction = _
ParameterDirection.Input
cmd.Parameters("@ProductID").Value = 1
Try
Dim nCostDiff As Decimal
nCostDiff = cmd.ExecuteScalar()
' Put to textbox on displayed form
txtMid.Text = nCostDiff
Catch e As Exception
MsgBox(e.Message)
End Try
End Sub
您可以看到，该程序的开始部分引入了SqlConnection对象cn，接下来创建了名为cmd的SqlCommand对象。在该示例中，SqlCommand对象的构造函数使用了两个参数。第一个参数是接受将要执行的命令的字符串。它可以是SQL语句或存储过程的名称。您可以看到该示例中使用了CostDiff存储过程的名称。第二个参数用于SqlConnection对象(用于连接到目标数据库)的名称。在创建了SqlCommand对象cmd之后，将它的CommandType特性设置为CommandType.StoredProcedure，表示将要执行一个存储过程。这个CommandType特性可以接受表6-5中所示的任何值。
表6-5 CommandType值
在将SqlCommand对象的CommandType特性设置为CommandType.StoredProcedure之后，使用SqlParameter对象向创建的CostDiff存储过程提供输入值。您可以使用SqlParameter类的构造函数或执行SqlCommand对象的Parameters集合的Add方法来创建SqlParameter对象。在该示例中，该参数是使用SqlCommand对象的Parameters集合的Add方法创建的。提供给Add方法的第一个参数是包含该参数名称的字符串，在这里是“@ProductID”。此外还要注意，SqlParameter对象使用的可替换参数必须以@符号开始。第二个参数使用了SqlDbType.Int枚举，表示该参数包含一个Integer值。接下来的代码行将Direction特性设置为值ParameterDirection.Input，表示这是个输入参数。最后，将SqlParameter对象的Value特性设置为1，并将它传递到CostDiff存储过程。
接下来的代码部分建立了一个执行CostDiff存储过程的Try-Catch代码段。在这个Try-Catch代码段中要注意的重点是，SqlCommand对象cmd的ExecuteScalar方法用于执行CostDiff存储过程并将返回值赋给nCostDiff变量。然后将nCostDiff变量的内容赋给一个名为txtMid的文本框，该文本框是在该项目的Windows窗体上定义的。与前面的示例一样，如果存储过程失败，则向终端用户弹出一个消息框，显示错误文本。
|
2658
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
