[asp.net]ExecuteNonQuery() 打破循环并且不在 SQL 数据库中插入数据

标签: Asp.net SQLServer
发布时间: 2017/3/26 2:56:46
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

这里是试图输入数组中存储的数据的代码。数组包含一样也有,不需要添加到数据库中的空单元格的数据。问题是代码不引发任何异常或错误,但它并不太在数据库中插入任何数据 !请帮助...在此先感谢

public void saveDb(string[,] timeTableId,string[,] start_time,string[,] end_time,string[,] subject_id,string[,] day,string[,] faculty_id)
{
    SqlConnection con;
    SqlCommand cmd;
    con = new SqlConnection("Data Source=.;Initial Catalog=AIS;Integrated Security=True");
    con.Open();
    for (int i = 0; i < 8; i++)
    {
        for (int j = 1; j <= 7; j++)
        {
            if (subject_id[i, j].Length != 0 && subject_id[i, j] != null)
            {
                cmd = new SqlCommand("INSERT INTO TIMETABLE VALUES('" + subject_id[i, j] + "','" + day[i, j] + "','" + start_time[i, j] + "','" + end_time[i, j] + "','" + subject_id[i, j] + "','" + faculty_id[i, j] + "')", con);
                cmd.ExecuteNonQuery();
            }
            else
            { 
            }
        }
    }
    con.Close();
}

解决方法 1:

好吧,我阐述了......

  1. 使用参数化查询-第一第二避免 SQL 注入,#1 漏洞在那里在互联网上,以避免出现问题我为此字符串或日期需要多少的单引号或双引号? ,这样的东西 — — 走如果你使用正确的类型参数和第三,提高性能 — — 你参数定义一次,重新使用他们多次 (和 SQL Server 还会创建一条 SQL 语句的执行计划和重用的 !)

  2. 使用 * * using(....) { .... } 尤其是阻止所有的一次性类- SqlConnectionSqlCommandSqlDataReader -确保适当和立即处置的不需要的对象。

  3. 始终显式定义列表中您要插入到表中的列的不只是依靠当前的表结构和顺序的列-明确的说你在做什么 !

一切的一切,你的方法真的应该看这样的事情︰

public void saveDb(string[,] timeTableId,string[,] start_time,string[,] end_time,string[,] subject_id,string[,] day,string[,] faculty_id)
{
    // define connection string - typically should come from a .config file
    string connectionString = "Data Source=.;Initial Catalog=AIS;Integrated Security=True";

    // define the SQL query - with *parameters* - and also: explicitly NAME the columns in your target table!
    // also: did you really want to insert the subject_id twice?
    string insertQry = "INSERT INTO dbo.TIMETABLE (col1, col2, col3, ....) " + 
                       " VALUES(@subject_id, @day, @start_time, @end_time, @subject_id, @faculty_id)";

    // set up your connection and command    
    // you didn't tell us what datatypes those are - maybe you need to adapt those to your situation!
    using (SqlConnection con = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(insertQry, con))
    {
        // define your parameters once, before the loop
        cmd.Parameters.Add("@subject_id", SqlDbType.Int);
        cmd.Parameters.Add("@day", SqlDbType.DateTime);
        cmd.Parameters.Add("@start_time", SqlDbType.Time);
        cmd.Parameters.Add("@end_time", SqlDbType.Time);
        cmd.Parameters.Add("@faculty_id", SqlDbType.Int);

        con.Open();

        // now start the for loops, and set the parameter values        
        for (int i = 0; i < 8; i++)
        {
            for (int j = 1; j <= 7; j++)
            {
                // not sure what these checks should be - left them "as is"
                if (subject_id[i, j].Length != 0 && subject_id[i, j] != null)
                {
                     // set the parameter values
                     cmd.Parameters["@subject_id"].Value = subject_id[i, j];
                     cmd.Parameters["@day"].Value = day[i, j];
                     cmd.Parameters["@start_time"].Value = start_time[i, j];
                     cmd.Parameters["@end_time"].Value = end_time[i, j];
                     cmd.Parameters["@faculty_id"].Value = faculty_id[i, j];

                     // execute query to insert data                     
                     cmd.ExecuteNonQuery();
                }    
            }
        }

        con.Close();
    }
}
赞助商