首先,我的问题很复杂,我不太会详细解释它,所以我想在此先对不起。
好的,这是东西。我执行此查询
string count = "SELECT Count(*) FROM Student WHERE IntakeID = 'MYVALUE'"
它将返回10的数字。因此,我继续下面的代码:
SqlCommand cmd = new SqlCommand(count, conn);
int temp = Convert.ToInt32(cmd.ExecuteScalar().ToString());
for (int x = 0; x < temp; x++)
{
string query = "INSERT INTO Docket (DocketNo, StudentID) VALUES ('" + getUniqueKey() + "','(SELECT StudentID FROM Student WHERE IntakeID = 'MYVALUE')')
}
PS:getUniqueKey()是一种获取我生成的唯一密钥的方法。
这段代码在技术上是否正确?我想要的结果如下所示:
+-----------+-------------+
| DocketNo | StudentID |
+-----------+-------------+
| 18590394 | TP123456 |
| 09141563 | TP012457 |
| 58293495 | TP049185 |
+-----------+-------------+
如果您不清楚我的问题,我会尽力使其更清楚。对造成的不便表示歉意。
更新(ANSWER):在Paparazzi(谢谢!)的帮助下,我进行了修改,并提出了自己的解决方案。
string count = "SELECT StudentID FROM Student WHERE IntakeID = 'MYVALUE'"
SqlCommand cmd = new SqlCommand(count, conn);
string query = "INSERT INTO Docket (DocketNo, StudentID) VALUES ";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
var loop = true;
while (loop)
{
loop = rdr.Read();
if (!loop)
{
//When end of rows and no more data to be retrieve, it removes the last "," from the query.
char[] trimChar = { ',' };
string newQuery = query.TrimEnd(trimChar);
cmd.CommandText = newQuery;
}
else {
query += "('" + GetUniqueKey() + "','" + rdr.GetString(0) + "')";
query += ",";
}
}
}
cmd.ExecuteNonQuery();
conn.Close();
因此,实际上,当在SqlDataReader上循环时,查询将在后台生成类似以下内容的代码:
INSERT INTO Docket(DocketNo, StudentID) VALUES ('1562456','TP028800'), ('1465446','TP028801'),..........('4939104','TP028810'),
请注意,由于,查询末尾将出现“,”逗号query += ",";
。并且当SqlDataReader不返回更多行时,它将执行该if(!loop)
语句以从查询中删除最后一个“,”(逗号)。
如果您不清楚其+=
用途,请访问https://msdn.microsoft.com/zh-cn/library/sa7629ew.aspx,以了解更多信息。
更新2:如果您要管理大量数据,@ Paparazzi提供了一种效率更高,性能更佳的方法。在自己的帖子中查找他的代码。:)
string count = "SELECT StudentID FROM Student WHERE IntakeID = 'MYVALUE'"
SqlCommand cmd = new SqlCommand(count, conn);
string query = "INSERT INTO Docket (DocketNo, StudentID) VALUES ";
conn.Open();
bool first = true;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (first)
first = false;
else
query += ", "
query += "('" + getUniqueKey() + "', '" + rdr.GetString(0) + "')";
}
}
if (!first)
{
cmd.CommandText = query + ";";
cmd.ExecuteNonQuery();
}
conn.Close();
StringBuilder比字符串上的+ =快,
并且多个值限制为1000(我认为),但是比单个插入更有效。
因此,如果您可以得到多个值,则需要添加一个计数器并触发插入
string getID = "SELECT StudentID FROM Student WHERE IntakeID = 'MYVALUE'"
SqlCommand cmd = new SqlCommand(getID , conn);
string insert = "INSERT INTO Docket (DocketNo, StudentID) VALUES ";
Int32 count = 0;
StringBuiler sb = new StringBuiler();
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (count == 0)
{
sb.Clear();
sb.AppendLine(insert);
}
else
sb.Append(", ")
sb.Append("('" + getUniqueKey() + "', '" + rdr.GetString(0) + "')");
count++;
if(count > 800)
{
count = 0;
cmd.CommandText = sb.ToString() + ";";
cmd.ExecuteNonQuery();
// most likely need a separate cmd here has a open reader
// will leave that as an exercise for you
// could even go asynch if you want to get faster
// or you could just build up multiple inserts on sb
}
}
}
if (count > 0)
{
cmd.CommandText = sb.ToString() + ";";
cmd.ExecuteNonQuery();
}
conn.Close();
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句