ADO.NET的记忆碎片（八）

向数据库中提交数据
使用参数化SqlCommand提交更新
提交新行
可以执行参数化Insert查询，向表中添加新行。
例如需要执行下面的Insert的SQL语句：
insert into order(OrderID,ProductID,Quantity,UnitPrice)
values(@OrderID,@ProductID,@Quantity,@UnitPrice)
在C#中使用实例：

SqlConnection cn = new SqlConnection("连接字符串");
string strSQL = "insert into order(OrderID,ProductID,Quantity,UnitPrice)"
+" values(@OrderID,@ProductID,@Quantity,@UnitPrice)";
DataRow rowToInsert;//新行的数据就是在rowToInsert里面,这里没有数据，需要自己另行添加
SqlCommand cmdInsert = new SqlCommand(strSQL,cn);
cmdInsert.Parameters.AddWithValue("@OrderID",rowToInsert["OrderID"]);
cmdInsert.Parameters.AddWithValue("@ProductID", rowToInsert["ProductID"]);
cmdInsert.Parameters.AddWithValue("@Quantity", rowToInsert["Quantity"]);
cmdInsert.Parameters.AddWithValue("@UnitPrice", rowToInsert["UnitPrice"]);
try {
int count = cmdInsert.ExecuteNonQuery();
if (count == 1)
{
rowToInsert.AcceptChanges();
Console.WriteLine("Query success,Affected 1 row!");
}
else if (count == 0)
{
Console.WriteLine("Query failed,Affected 0 row!");
}
else
{
Console.WriteLine("Query Affected {0} rows!",count);
}
}catch(Exception ex) {
Console.WriteLine("Query failed:{0}",ex.Message);
}

更新现有行
可以执行参数化Update查询，向表中修改现有行。
例如需要执行下面的Update的SQL语句：
update order set OrderID=@OrderID_New,ProductID=@ProductID_New,Quantity=@Quantity_New,UnitPrice=@UnitPrice_New
where OrderID=@OrderID_Old andProductID=@ProductID_Old and Quantity=@Quantity_Old and UnitPrice=@UnitPrice_Old
在C#中使用实例：

string strSQL1 = "update order set OrderID=@OrderID_New,ProductID=@ProductID_New,Quantity=@Quantity_New,UnitPrice=@UnitPrice_New"
+" where OrderID=@OrderID_Old and ProductID=@ProductID_Old and Quantity=@Quantity_Old andUnitPrice=@UnitPrice_Old";
DataRow rowToUpdate;//修改行的数据就是在rowToUpdate里面,这里没有数据，需要自己另行设置
SqlCommand cmdUpdate = new SqlCommand(strSQL1, cn);
cmdUpdate.Parameters.AddWithValue("@OrderID_New", rowToUpdate["OrderID"]);
cmdUpdate.Parameters.AddWithValue("@ProductID_New", rowToUpdate["ProductID"]);
cmdUpdate.Parameters.AddWithValue("@Quantity_New", rowToUpdate["Quantity"]);
cmdUpdate.Parameters.AddWithValue("@UnitPrice_New", rowToUpdate["UnitPrice"]);
cmdUpdate.Parameters.AddWithValue("@OrderID_Old", rowToUpdate["OrderID",DataRowVersion.Original]);
cmdUpdate.Parameters.AddWithValue("@ProductID_Old", rowToUpdate["ProductID", DataRowVersion.Original]);
cmdUpdate.Parameters.AddWithValue("@Quantity_Old", rowToUpdate["Quantity", DataRowVersion.Original]);
cmdUpdate.Parameters.AddWithValue("@UnitPrice_Old", rowToUpdate["UnitPrice", DataRowVersion.Original]);
try
{
int count = cmdUpdate.ExecuteNonQuery();
if (count == 1)
{
rowToUpdate.AcceptChanges();
Console.WriteLine("Query success,Affected 1 row!");
}
else if (count == 0)
{
Console.WriteLine("Query failed,Affected 0 row!");
}
else
{
Console.WriteLine("Query Affected {0} rows!", count);
}
}
catch (Exception ex)
{
Console.WriteLine("Query failed:{0}", ex.Message);
}

删除现有行
可以执行参数化Dalete查询，向表中删除现有行。
例如需要执行下面的Update的SQL语句：
delete order where OrderID=@OrderID and ProductID=@ProductID and Quantity=@Quantity andUnitPrice=@UnitPrice
在C#中使用实例：

string strSQL2 = "delete order"
+ " where OrderID=@OrderID and ProductID=@ProductID and Quantity=@Quantity andUnitPrice=@UnitPrice";
DataRow rowToDelete;//修改行的数据就是在rowToUpdate里面,这里没有数据，需要自己另行设置
SqlCommand cmdDelete = new SqlCommand(strSQL2, cn);
cmdDelete.Parameters.AddWithValue("@OrderID", rowToDelete["OrderID", DataRowVersion.Original]);
cmdDelete.Parameters.AddWithValue("@ProductID", rowToDelete["ProductID", DataRowVersion.Original]);
cmdDelete.Parameters.AddWithValue("@Quantity", rowToDelete["Quantity", DataRowVersion.Original]);
cmdDelete.Parameters.AddWithValue("@UnitPrice", rowToDelete["UnitPrice", DataRowVersion.Original]);
try
{
int count = cmdDelete.ExecuteNonQuery();
if (count == 1)
{
rowToDelete.AcceptChanges();
Console.WriteLine("Query success,Affected 1 row!");
}
else if (count == 0)
{
Console.WriteLine("Query failed,Affected 0 row!");
}
else
{
Console.WriteLine("Query Affected {0} rows!", count);
}
}
catch (Exception ex)
{
Console.WriteLine("Query failed:{0}", ex.Message);
}

使用SqlDataAdapter对象提交跟新
手动配置SqlDataAdapter对象
SqlDataAdapter对象公开了4个包含Command对象的属性：
SelectCommand属性是用来填充DataTable的
UpdateCommand，InsertCommand，DeleteCommand属性是用来提交挂起更改数据的
SqlDataAdapter的Update方法非常灵活。可以提供DataSet、DataSet与表名称、DataTable或DataRow数组。
无论如何调用SqlDataAdapter的Update方法，都会尝试通过适当的Command来提交挂起的更改。
使用实例：

SqlDataAdapter da = new SqlDataAdapter();
SqlParameterCollection pc;
SqlParameter p;
//设置InsertCommand属性
da.InsertCommand = new SqlCommand(strSQL, cn);
pc = da.InsertCommand.Parameters;
pc.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
pc.Add("@ProductID", SqlDbType.Int, 0, "ProductID");
pc.Add("@Quantity", SqlDbType.SmallInt, 0, "Quantity");
pc.Add("@UnitPrice", SqlDbType.Int, 0, "UnitPrice");

//设置UpdateCommand属性
da.UpdateCommand = new SqlCommand(strSQL1, cn);
pc = da.UpdateCommand.Parameters;
pc.Add("@OrderID_New", SqlDbType.Int, 0, "OrderID");
pc.Add("@ProductID_New", SqlDbType.Int, 0, "ProductID");
pc.Add("@Quantity_New", SqlDbType.SmallInt, 0, "Quantity");
pc.Add("@UnitPrice_New", SqlDbType.Int, 0, "UnitPrice");
p = pc.Add("@OrderID_Old", SqlDbType.Int, 0, "OrderID");
p.SourceVersion = DataRowVersion.Original;
p = pc.Add("@ProductID_Old", SqlDbType.Int, 0, "ProductID");
p.SourceVersion = DataRowVersion.Original;
p = pc.Add("@Quantity_Old", SqlDbType.SmallInt, 0, "Quantity");
p.SourceVersion = DataRowVersion.Original;
p = pc.Add("@UnitPrice_Old", SqlDbType.Int, 0, "UnitPrice");
p.SourceVersion = DataRowVersion.Original;

//设置DaleteCommand属性
da.UpdateCommand = new SqlCommand(strSQL1, cn);
pc = da.DeleteCommand.Parameters;
p = pc.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
p.SourceVersion = DataRowVersion.Original;
p = pc.Add("@ProductID", SqlDbType.Int, 0, "ProductID");
p.SourceVersion = DataRowVersion.Original;
p = pc.Add("@Quantity", SqlDbType.SmallInt, 0, "Quantity");
p.SourceVersion = DataRowVersion.Original;
p = pc.Add("@UnitPrice", SqlDbType.Int, 0, "UnitPrice");
p.SourceVersion = DataRowVersion.Original;

在设置了这一新逻辑之后，只需要调用SqlDataAdapter的update方法，datatable作为参数提供，就可以对datatable中的更改，提交给数据库