使用SQL预准备语句在C#中绑定结果 [英] Bind Results in C# using SQL prepared statements

问题描述

使用此:

SqlConnection myConnection = new SqlConnection("Data Source=.\\SERVER;Initial Catalog=DB;Integrated Security=True;TrustServerCertificate=True;User Instance=False");
myConnection.Open();

SqlCommand myCommand = new SqlCommand("SELECT BusinessName FROM Businessess WHERE BusinessID = @Param2", myConnection);

SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.Int, 4);
myParam2.Value = 1;
myCommand.Parameters.Add(myParam2);

MessageBox.Show(myCommand); //How do I bind results to show as string?

如何将准备好的语句的结果绑定到变量，以便可以操纵它们?

How do I bind the results of a prepared statement to a variable so that I may manipulate them?

推荐答案

尝试如下:

using (SqlConnection myConnection = new SqlConnection("Data Source=.\\SERVER;Initial Catalog=DB;Integrated Security=True;TrustServerCertificate=True;User Instance=False"))
using (SqlCommand myCommand = myConnection.CreateCommand())
{
    myConnection.Open();
    myCommand.CommandText = "SELECT BusinessName FROM Businessess WHERE BusinessID = @Param2";
    myCommand.Parameters.AddWithValue("@Param2", myParam2);
    using (SqlDataReader reader = myCommand.ExecuteReader())
    {
        if (reader.Read())
        {
            string businessName = reader.GetString(reader.GetOrdinal("BusinessName"));
            MessageBox.Show(businessName);
        }
        else
        {
            MessageBox.Show(string.Format("Sorry, no business found with id = {0}", myParam2));
        }
    }
}

注意事项:

• 使用语句将一次性资源包装起来，以确保即使在有例外的情况下也能正确处理
• 传递给sql命令的参数的简化
• 在命令上调用ExecuteReader以便检索允许您读取返回的结果集的对象.

• disposable resources are wrapped in using statements to ensure proper disposal even in case of exceptions
• simplification of the parameter passed to the sql command
• call the ExecuteReader on the command in order to retrieve an object allowing you to read the returned resultset.

这篇关于使用SQL预准备语句在C#中绑定结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！