【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。

4. SqlParameter要不要指定Size参数