的ExecuteNonQuery（）打破循环，不插入SQL数据库中的数据 [英] ExecuteNonQuery() breaks the loop and doesn't insert data in SQL database
问题描述
这里的code它试图进入存储在阵列中的数据。所述阵列包含数据以及有它不必被添加到数据库中的空单元。问题是code未抛出任何异常或错误，但它不是在数据库中插入任何数据呢！请帮助...在此先感谢
公共无效saveDb（字符串[，] timeTableId，字符串[，] START_TIME，字符串[，] END_TIME，字符串[，] subject_id，字符串[，]天，字符串[，] faculty_id）
{
SqlConnection的CON;
CMD的SqlCommand;
CON =新的SqlConnection（数据源=;初始目录= AIS;集成安全性=真）;
con.Open（）;
的for（int i = 0; I＆LT; 8;我++）
{
对于（INT J = 1; J＆LT; = 7; J ++）
{
如果（subject_id [I，J]。长度= 0＆安培;！＆安培;！subject_id [I，J] = NULL）
{
CMD =新的SqlCommand（INSERT INTO时间表VALUES（'+ subject_id [I，J] +，+天[I，J] +，+ START_TIME [I，J] +'， '+ END_TIME [I，J] +，+ subject_id [I，J] +，+ faculty_id [I，J] +'），CON）;
cmd.ExecuteNonQuery（）;
}
其他
{
}
}
}
con.Close（）;
}
好吧，我阐述....
-
使用参数化查询 - 第一个以避免SQL注入，排名第一的漏洞在那里在互联网上，第二个以避免问题的多少单或双引号，我需要此字符串或日期的和类似的东西 - 如果你使用正确的类型参数走了，第三，以提高性能 - 定义参数的一次后，再使用它们多次（和SQL服务器也将创建一个执行计划的一个SQL语句，并重复使用！）
-
使用**
使用（....）{...}块一次性全部类别 - 尤其是的SqlConnection，的SqlCommand，SqlDataReader的- 确保不需要的对象适当的，立即处置。 -
的总是的显式定义要插入到表中的列清单 - 不要仅仅依靠当前的列表结构和秩序 - 明确地说你在做什么！
所有的一切，你的方法确实应该是这个样子：
公共无效saveDb（字符串[，] timeTableId，字符串[，] START_TIME，字符串[，] END_TIME，字符串[，] subject_id，字符串[，]天，字符串[，] faculty_id）
{
//定义连接字符串 - 通常应该来自一个config文件
字符串的connectionString =数据源=;初始目录= AIS;集成安全性=真; //定义SQL查询 - 带*参数* - 也：明确名称与目标表中的列！
//也：你真的要插入的subject_id两次？
字符串insertQry =INSERT INTO dbo.TIMETABLE（COL1，COL2，COL3，....）+
VALUES（@subject_id，@day，@start_time，@end_time，@subject_id，@faculty_id）; //设置您的连接和命令
//你没有告诉我们什么数据类型那些是 - 也许你需要去适应那些你的情况！
使用（SqlConnection的CON =新的SqlConnection（的connectionString））
使用（CMD的SqlCommand =新的SqlCommand（insertQry，CON））
{
//定义你的参数一次，在循环之前
cmd.Parameters.Add（@ subject_id，SqlDbType.Int）;
cmd.Parameters.Add（@日，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（）; //现在开始进行循环，并设置参数值
的for（int i = 0; I＆LT; 8;我++）
{
对于（INT J = 1; J＆LT; = 7; J ++）
{
//不知道这些检查应该是什么 - 给他们留下原样
如果（subject_id [I，J]。长度= 0＆安培;！＆安培;！subject_id [I，J] = NULL）
{
//设置的参数值
cmd.Parameters [@ subject_id]值= subject_id [I，J]。
cmd.Parameters [@日]值=天[I，J]。
cmd.Parameters [@ START_TIME]值= START_TIME [I，J]。
cmd.Parameters [@ END_TIME]值= END_TIME [I，J]。
cmd.Parameters [@ faculty_id]值= faculty_id [I，J]。 //执行查询，插入数据
cmd.ExecuteNonQuery（）;
}
}
} con.Close（）;
}
}
Here's the code which tries to enter the data stored in the arrays. The arrays contain data as well as there are empty cells which need not be added into the database. The issue is the code is not throwing any exception or error but it isn't inserting any data in the database too! Please help... Thanks in advance
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();
}
OK, I'm elaborating....
use parametrized queries - first to avoid SQL injection, the #1 vulnerability out there on the internet, second to avoid issues with how many single or double quotes do I need for this string or date? and stuff like that - gone if you use properly typed parameters, and third to improve performance - define your parameter once, re-use them multiple times (and SQL Server will also create one SQL statement with an execution plan and reuse that!)
use **
using(....) { .... }blocks for all disposable classes - especiallySqlConnection,SqlCommand,SqlDataReader- to ensure proper and immediate disposal of unneeded objects.always explicitly define the list of columns of a table you're inserting into - don't just rely on the current table structure and order of columns - explicitly say what you're doing!
All in all, your method should really look something like this:
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();
}
}
这篇关于的ExecuteNonQuery（）打破循环，不插入SQL数据库中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
