如何在c#中传递值给sql命令

本文关键字：sql 命令 | 更新日期: 2023-09-27 18:06:15

我有一个SQL命令:

string keyProcesses = "SELECT distinct STUFF((SELECT ', '+ cn.name  from WMCCMCategories cn  INNER JOIN CategorySets uc ON uc.categoryId = cn.categoryID INNER JOIN KeyProcesses u ON u.categorySetId = uc.setId  INNER JOIN Companies c ON c.companyId = u.companyId  WHERE c.companyName = @companyName  ORDER BY cn.name FOR XML PATH('')), 1, 1, '') AS listStr FROM WMCCMCategories cnn Group by cnn.name";

我有:

public SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = commandText; //where commandText is another SQL statement
sqlCmd.Parameters.Clear();

然后执行connection:

string connectionString = ConfigurationSettings.AppSettings["connectionString"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    try
    {
        connection.Open();
        SqlDataAdapter sdr = new SqlDataAdapter(sqlCmd.CommandText, connection);
        DataTable dt=new DataTable();
        sdr.Fill(dt)

之后，我想移动到下一个SQL命令并为该命令添加参数:

sqlCmd.CommandText = keyProcesses;
sqlCmd.Parameters.AddWithValue("@companyName", compnyName);
SqlDataAdapter sdr1 = new SqlDataAdapter(sqlCmd.CommandText, connection);
DataTable dtb1 = new DataTable();
sdr1.Fill(dtb1);

但是它没有执行这个SqlCommand。我已经确保SQL键进程可以使用预先添加的修复参数正常运行。compnName不为空。这里唯一值得怀疑的是如何向sql参数添加值。我不知道我使用它的方式有什么问题?

编辑:为了便于调查问题，我更新了整个代码:

string connectionString = ConfigurationSettings.AppSettings["connectionString"];
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                try
                {
                    connection.Open();
                    SqlDataAdapter sdr = new SqlDataAdapter(sqlCmd.CommandText, connection);
                    DataTable dt=new DataTable();
                    sdr.Fill(dt);
                        //To store the values of company names:
                    List<CompanyModel> companies = new List<CompanyModel>();

                        for(int i = 0; i < dt.Rows.Count; i++)
                        {
                            companies.Add(new CompanyModel 
                            { 
                                compnSN = i + 1,
                                compnName =  dt.Rows[i]["companyName"].ToString(),
                                compnAddress =  dt.Rows[i]["address"].ToString()
                            });
                        }
                        companyRepeater.DataSource = companies;
                        companyRepeater.DataBind();
                        string comName = dt.Rows[0]["companyName"].ToString();
                        var names = companies.Select(c => c.compnName);
                        string[] arr = names.ToArray();
                        foreach (string compnyName in arr)
                        {
                            //To write names to file for debugging purpose
                            using (StreamWriter _testData = new StreamWriter(Server.MapPath("~/File.txt"), true))
                            {
                                _testData.WriteLine(compnyName); // Write the file.
                            }

                              //Get KeyProcesses
                              sqlCmd.CommandText = keyProcesses;
                              //sqlCmd.Parameters.AddWithValue("@companyName", compnyName);
                              sqlCmd.Parameters.Add("@companyName", SqlDbType.NVarChar);
                              sqlCmd.Parameters["@companyName"].Value = compnyName;
                             // SqlDataAdapter sdr1 = new SqlDataAdapter(sqlCmd.CommandText, connection);
                              DataTable dtb1 = new DataTable();
                              //sdr1.Fill(dtb1);
                              using (var reader = sqlCmd.ExecuteReader())
                              {
                                  dtb1.Load(reader);
                              }
                              companies.Add(new CompanyModel
                              {
                                  compnKeyProcesses = dtb1.Rows[0][0].ToString()
                              });

如何在c#中传递值给sql命令

坦白地说，这里根本不需要适配器。这只会增加混乱。删除:

sqlCmd.CommandText = keyProcesses;
sqlCmd.Parameters.AddWithValue("@companyName", compnyName);
using(var reader = sqlCmd.ExecuteReader()) {
    dtb1.Load(reader);
}

sqlCmd.Parameters。Add("@companyName SqlDbType.NVarchar (1000);//或任何类型的数据库列有

sqlCmd.Parameters("@companyName")。Value = companyname;

这是一个更安全的方式，可能会帮助

相关文章：