ADO.NET的记忆碎片（九）

这一篇是讲复杂更新数据，在讲复杂更新之前，我们要先将参数化查询，今天我猛然发现，参数化查询我真的之前没有提到过，
不过在上篇中有用到参数化查询，那我今天就正好把参数化的查询这一块内容整理出来，不要留下什么盲点。
参数化查询
假设一种场景，希望获得一位特定的用户订单信息，希望执行下面的SQL查询：

**select OrderID,CustomerID,OrderDate,EmployeeIDfrom Orders where CustomerID = @CustomerID

要在ADO.NET对象模型中执行一个参数化查询，需要向Command对象的Parameters集合中添加Parameter对象。
参考代码如下：

SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection("连接字符串");
cmd.CommandText = "select OrderID,CustomerID,OrderDate,EmployeeID from Orders where CustomerID = @CustomerID";
SqlParameter p;
p = new SqlParameter();
p.ParameterName = "@CustomerID";
p.Value = "ALFKI";
cmd.Parameters.Add(p);

**使用语法糖

p = cmd.Parameters.AddWithValue("@CustomerID", "ALFKI");

*显式设置数据类型
*有两种方法可以对参数的类型进行设置：设置SqlParameter对象的SqlDbType属性，或者在SqlParameter的构造函数中设置
*参考代码如下：
构造函数中设置

p = new SqlParameter("@CustomerID",SqlDbType.NVarChar,5);
p.Value = "ALFKI";
cmd.Parameters.Add(p);

设置SqlParameter对象的SqlDbType属性

p.SqlDbType = SqlDbType.NVarChar;
p.Size = 5;

*参数的方向
*在上面的类子中我们设置的参数都是输入参数，还可以使用参数从数据库中获取数据。即是：使用输出参数，返回结果，而且性能会更好
*假设使用输入参数根据ProductName来查询数据，并通过输出参数来返回Price和InStock的值，SQL语句如下：
*select @Price = Price,@InStock = InStock from Products where ProductName = @ProductName
*参考代码如下：

SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = new SqlConnection("连接字符串");
cmd2.CommandText = "select @Price = Price,@InStock = InStock from Products where ProductName = @ProductName";
SqlParameter price,instock,productname;
price = cmd.Parameters.Add("@Price",SqlDbType.Money);
price.Direction = ParameterDirection.Output;
instock = cmd.Parameters.Add("@InStock", SqlDbType.NVarChar,20);
instock.Direction = ParameterDirection.Output;
productname = cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar, 40);
productname.Value = "Chai";
cmd2.ExecuteNonQuery();
if (price.Value == DBNull.Value)
{
Console.WriteLine("No found named {0}", productname.Value);
}
else
{
Console.WriteLine("{0}--{1}", price.Value, instock.Value);
}

复杂更新：提交更新后刷新一行
在提交更新后刷新一行，因为有时间截列，可以保证数据不会被乱改，所以在提交更新后刷新一行是非常有必要的，
我们的解决方法就是用输出参数获取新行数据，使用SQL语句是：

update OrderDetailsWithTimestamp
set OrderID = @OrderIDNew, ProductID = @ProductIDNew, Quantity = @QuantityNew, Price = @PriceNew
where OrderID = @OrderIDOld and ProductID = @ProductIDOld and Timestamp = @TimestampOld
if @@ROWCOUNT <> 0 then
select @QuantityNew = Quantity,@PriceNew = Price, @TimestampNew = Timestamp
from OrderDetailsWithTimestamp
where OrderID = @OrderIDNew and ProductID=@ProductIDNew

参考代码如下：

cmd2.CommandText = "update OrderDetailsWithTimestamp" +
"set OrderID = @OrderIDNew, ProductID = @ProductIDNew, Quantity = @QuantityNew, Price = @PriceNew" +
"where OrderID = @OrderIDOld and ProductID = @ProductIDOld and Timestamp = @TimestampOld" +
"if @@ROWCOUNT <> 0 then" +
"select @QuantityNew = Quantity,@PriceNew = Price, @TimestampNew = Timestamp" +
"from OrderDetailsWithTimestamp" +
"where OrderID = @OrderIDNew and ProductID=@ProductIDNew";
SqlParameter QuantityNew, PriceNew, TimestampNew;

cmd2.Parameters.AddWithValue("@OrderIDNew", "OrderIDNew");
cmd2.Parameters.AddWithValue("@ProductIDNew", "ProductIDNew");
QuantityNew = cmd2.Parameters.AddWithValue("@QuantityNew", "QuantityNew");
QuantityNew.Direction = ParameterDirection.InputOutput;
PriceNew = cmd2.Parameters.AddWithValue("@PriceNew", "PriceNew");
PriceNew.Direction = ParameterDirection.InputOutput;
TimestampNew = cmd2.Parameters.AddWithValue("@TimestampNew", "TimestampNew");
TimestampNew.Direction = ParameterDirection.Output;
cmd2.Parameters.AddWithValue("@OrderIDOld", "OrderIDOld");
cmd2.Parameters.AddWithValue("@ProductIDOld", "ProductIDOld");
cmd2.Parameters.AddWithValue("@TimestampOld", "TimestampOld");

QuantityNew, PriceNew, TimestampNew这三个输出参数可以作为刷新数据来使用
复杂更新：一次插入多条数据，使用SqlBulkCopy对象
参考代码：

<summary>
向表插入数据
<summary>
<param name="table">在内存中的数据，即将要插入数据库的数据<param>
<param name="database">对应数据库的名称<param>
<param name="database">对应数据库的表名称<param>

public static void InsertTableData(DataTabletable, string database,stringtableName)
{
using (SqlBulkCopybcp = new SqlBulkCopy("Server=.;database=;user id=;Password=;connection reset=false;"))
{
bcp.BatchSize = 100;//每次传输的行数
bcp.DestinationTableName = tableName;//目标表
bcp.WriteToServer(table);
}
}