在C＃和MS SQL Server中将布尔类型传递给位参数类型 [英] Passing a boolean type into a bit parameter type in C# and MS SQL Server

问题描述

我有一个C＃方法，它接受一个clientId（int）和hasPaid（boolean）来表示该客户端是否付款。 MS SQL Server存储过程期望@HasPaid参数的BIT值（1或0），但是该方法期望hasPaid的 boolean 类型（真/假）。 ADO.NET代码是否可以将SQL Server的 boolean 转换为 bit 类型，还是我需要将 hasPaid 的值转换为 1或0 ？

I have a C# method that accepts a clientId (int) and hasPaid (boolean) that represents if the client has paid or not. The MS SQL Server stored procedure expects a BIT value (1 or 0) for the @HasPaid parameter yet the method expects a boolean type (true/false) for hasPaid. Will the ADO.NET code take care of converting the boolean to a bit type for SQL Server or do I need to convert the value of hasPaid into a 1 or 0?

public void UpdateClient(int clientId, bool hasPaid)
{
    using (SqlConnection conn = new SqlConnection(this.myConnectionString))
    {
        using (SqlCommand sqlCommand = new SqlCommand("uspUpdatePaymentStatus", conn))
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@ClientID", clientId);
            sqlCommand.Parameters.AddWithValue("@HasPaid", hasPaid);
            sqlCommand.Connection.Open();
            var rowsAffected = sqlCommand.ExecuteNonQuery();
        }
    }
}

推荐答案

使用SQL参数时，我发现 AddWithValue 的类型的自动检测功能不太可靠。我发现最好只调用 Add 一个显式设置类型， Add 也会返回它从函数调用，这样您就可以随后调用 .Value 。

When working with SQL parameters I find AddWithValue's auto detection feature of the type too unreliable. I find it better to just call Add a explicitly set the type, Add also returns the new parameter it creates from the function call so you can just call .Value on it afterward.

public void UpdateClient(int clientId, bool hasPaid)
{
    using (SqlConnection conn = new SqlConnection(this.myConnectionString))
    {
        using (SqlCommand sqlCommand = new SqlCommand("uspUpdatePaymentStatus", conn))
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@ClientID", SqlDbType.Int).Value = clientId;
            sqlCommand.Parameters.Add("@HasPaid", SqlDbType.Bit).Value = hasPaid;
            sqlCommand.Connection.Open();
            var rowsAffected = sqlCommand.ExecuteNonQuery();
        }
    }
}

使用正确的类型是双重的在使用存储过程并且期望使用特定类型时很重要，我只是养成了始终以这种方式进行操作的习惯。

Using the correct type is doubly important when using stored procedures and it is expecting a specific type, I just got in to the habit of always doing it this way.

这篇关于在C＃和MS SQL Server中将布尔类型传递给位参数类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！