c# - System.Data.dll 中发生“System.Data.SqlClient.SqlException”

Question

我尝试使用 c# 在 Visualstudio 中使用 Sql，但它抛出 System.Data.dll 中发生的“System.Data.SqlClient.SqlException”类型的未处理异常

 comm = new SqlCommand("INSERT INTO HafizwalaTable(DistrictName, TownName, FarmerName, " +
 "Area,  VarietyOfCrop, SowingDate, VisitDate, PestPopulation1, " + 
 "PestPopulation2, PestPopulation3, PestPopulation4, PestPopulation5, " + 
 "PestPopulation6, PestPopulation7, PestPopulation8, PestPopulation9, " + 
 "PestPopulation10, PestPopulation11, PestPopulation12, PesticideUsed, " + 
 "PesticideSprayDate, PesticideDosage, CLCV, PlantHeight) " + 
 "VALUES @p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11, " +
 "@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24)", con);

comm.Parameters.AddWithValue("@p1",FileReaderDataArray[RowNo, 0]);

comm.Parameters.AddWithValue("@p2",FileReaderDataArray[RowNo, 1]);

comm.Parameters.AddWithValue("@p3",FileReaderDataArray[RowNo, 2]);

comm.Parameters.AddWithValue("@p4", FileReaderDataArray[RowNo, 3]);

comm.Parameters.AddWithValue("@p5", FileReaderDataArray[RowNo, 4]);

comm.Parameters.AddWithValue("@p6", FileReaderDataArray[RowNo, 5]);

comm.Parameters.AddWithValue("@p7", FileReaderDataArray[RowNo, 6]);

comm.Parameters.AddWithValue("@p8", FileReaderDataArray[RowNo, 7]);

comm.Parameters.AddWithValue("@p9", FileReaderDataArray[RowNo, 8]);

comm.Parameters.AddWithValue("@p10", FileReaderDataArray[RowNo, 9]);

comm.Parameters.AddWithValue("@p11", FileReaderDataArray[RowNo, 10]);

comm.Parameters.AddWithValue("@p12", FileReaderDataArray[RowNo, 11]);

comm.Parameters.AddWithValue("@p13", FileReaderDataArray[RowNo, 12]);

comm.Parameters.AddWithValue("@p14", FileReaderDataArray[RowNo, 13]);

comm.Parameters.AddWithValue("@p15", FileReaderDataArray[RowNo, 14]);

comm.Parameters.AddWithValue("@p16", FileReaderDataArray[RowNo, 15]);

comm.Parameters.AddWithValue("@p17", FileReaderDataArray[RowNo, 16]);

comm.Parameters.AddWithValue("@p18", FileReaderDataArray[RowNo, 17]);

comm.Parameters.AddWithValue("@p19", FileReaderDataArray[RowNo, 18]);

comm.Parameters.AddWithValue("@p20", FileReaderDataArray[RowNo, 19]);

comm.Parameters.AddWithValue("@p21", FileReaderDataArray[RowNo, 20]);

comm.Parameters.AddWithValue("@p22", FileReaderDataArray[RowNo, 21]);

comm.Parameters.AddWithValue("@p23", FileReaderDataArray[RowNo, 22]);

comm.Parameters.AddWithValue("@p24", FileReaderDataArray[RowNo, 23]);


comm.ExecuteNonQuery();

此外，所有列的数据类型均为 nvarchar

Answer 1

您应该使用参数化查询来避免这种困惑。像这样的事情

try
{

    string cmdText = "INSERT INTO HafizwalaTable(DistrictName, " + 
        "TownName, FarmerName, Area, VarietyOfCrop, SowingDate, VisitDate, PestPopulation1, " + 
        "PestPopulation2, PestPopulation3, PestPopulation4, PestPopulation5, " + 
        "PestPopulation6, PestPopulation7, PestPopulation8, PestPopulation9, " + 
        "PestPopulation10, PestPopulation11, PestPopulation12, PesticideUsed, " + 
        "PesticideSprayDate, PesticideDosage, CLCV, PlantHeight) " + 
        "VALUES(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14, "
        "@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24)";

    using(SqlConnection con = new SqlConnection(GetConnectionString())
    using(SqlCommand comm = new SqlCommand(cmdText, con);
    {
        comm.Parameters.AddWithValue("@p1",FileReaderDataArray[RowNo, 0]);
        comm.Parameters.AddWithValue("@p2",FileReaderDataArray[RowNo, 1]);
        comm.Parameters.AddWithValue("@p3",FileReaderDataArray[RowNo, 2]);

        ..... and so on, add the other parameters. all 24 if I have counted them well

        comm.ExecuteNonQuery();
     }
 }
 catch(Exception ex)
 {
     MessageBox.Show(ex.Message);
 }

您应该使用参数化查询来避免解析值时出现问题(如果一个或多个 FileReaderDataArray 字符串包含单引号会发生什么？)并避免 Sql Injection 出现问题，最后你会得到一个更干净的命令字符串，而不会出现字符串连接所需的困惑引用