使用For循环使用C#编码在Sql Server中插入数据

本文关键字：Server Sql 插入 数据 编码 循环 For 使用 | 更新日期: 2023-09-27 17:57:36

我知道我的问题很奇怪，但我想使用C#使用For循环在Sql Server中插入数据。我有如下数据：

Insert into tblQuestions(Question, Description, Image, TopicId) values('Urogenital Diaphragm is formed by A/E', NULL, NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Colle''s fascia', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Sphincter urethra', NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Perineal membrane', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Deep perineal muslces', NULL, 0)

Insert into tblQuestions(Question, Description, Image, TopicId) values('The intricately and prodigiously looped system of veins and arteries that lie on the surface of the epididymis is known as:', NULL, NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Choroid plexus', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Tuberal plexus', NULL, 0)
Insert into tblOptions(Option, QId, Answer) values('Pampiniform plexus', NULL, 1)
Insert into tblOptions(Option, QId, Answer) values('Pectiniform septum', NULL, 0)


这样，我就有了许多数据。第一行是问题，接下来的4行包含该问题的选项。插入第一个问题id后，它将生成问题id，该问题id将在所有4个选项中使用，而不是NULL。

请帮我处理这件事。我正在寻找一些想法或建议。我把所有这些数据都保存在记事本文件中。

使用For循环使用C#编码在Sql Server中插入数据

作为一种替代方案，如果您能够创建一个存储过程，您可以声明一个变量，并在插入后捕获新创建的ID，然后在随后的每个insert语句中使用它？

DECLARE @QuestionId int
Insert into tblQuestions(Question, Description, Image, TopicId) values('Urogenital Diaphragm is formed by A/E', NULL, NULL, 1)
SELECT @QuestionId = SCOPE_IDENTITY();
Insert into tblOptions(Option, QId, Answer) values('Colle''s fascia', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Sphincter urethra', @QuestionId, 1)
Insert into tblOptions(Option, QId, Answer) values('Perineal membrane', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Deep perineal muslces', @QuestionId, 0)

Insert into tblQuestions(Question, Description, Image, TopicId) values('The intricately and prodigiously looped system of veins and arteries that lie on the surface of the epididymis is known as:', NULL, NULL, 1)
SELECT @QuestionId = SCOPE_IDENTITY();
Insert into tblOptions(Option, QId, Answer) values('Choroid plexus', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Tuberal plexus', @QuestionId, 0)
Insert into tblOptions(Option, QId, Answer) values('Pampiniform plexus', @QuestionId, 1)
Insert into tblOptions(Option, QId, Answer) values('Pectiniform septum', @QuestionId, 0)

我要告诉你一些将改变你生活的事情。：）好吧，不是真的，但它会让写查询等时更容易。

点击此处下载数据访问块。

然后将其添加到您的项目资源中并包含。

您将键入的唯一代码插入到表格等中是：

//Using:
using Microsoft.ApplicationBlocks.Data;
//Setting connection
private static string _connection = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
//sending info to a stored proc (Which is better) in my opinion:
SqlHelper.ExecuteNonQuery(_connection, "usp_AddUpdateTargets", week1target, week2target, week3target, week4target, UserID, TerritoryID);

这就是它的全部。很明显，你必须得到你的变量等等。

希望这能有所帮助。

从查询性能的角度来看，调用数据库插入单行是不好的。我建议在for循环中将查询连接为带有分隔符"；"的查询字符串，并在最后一次执行整个insert语句。例如：

字符串查询；

For循环（singleInsertQuery）{query=query+"；"+singleInsertQuery}

执行（查询）；

相关文章：