【C# SQL】SqlCommand
1. SqlCommand带参数用法
SqlConnection关联数据库连接str,SqlCommand关联connection和commandText。前者Open/Close，后者ExcecuteReader/ExecuteNonQuery。
private static void UpdateDemographics(Int32 customerID, string demoXml, string connectionString)
{
string commandText = "UPDATE Sales.Store SET Demographics = @demographics " + "WHERE CustomerID = @ID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
//创建parameter对象。指明参数类型、长度
SqlParameter parameter1 = new SqlParameter("@demographics", System.Data.SqlDbType.NChar, 10);
//给parameter赋值
parameter1.Value = demoXml;
//将parameter添加到command.Parameters
command.Parameters.Add(parameter1);
//先添加再赋值
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID; //parameter可用索引访问
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
2. SqlCommand不带参数用法
private static void UpdateDemographics(Int32 customerID, string demoXml, string connectionString)
{
string commandText = string.Format("UPDATE Sales.Store SET Demographics = {0} " + "WHERE CustomerID = {1};", demoXml, customerID);
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
3.效率
1的效率高于2。
command.Parameters.Add(parameter1);效率高于 command.Parameters.AddWithValue("@demographics", demoXml);。但是前者要通过SqlParameter parameter1 = new SqlParameter("@demographics", System.Data.SqlDbType.NChar, 10);指定好参数的类型。所以最好使用Add而不是AddWithValue。
