C# 将 List<> 插入 SQL Server 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10680910/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting a List<> into SQL Server table
提问by DoNNie_DarkO
I have an entity Reportwhose values I want to insert into a database table. The following attributes of Reporthave to be inserted:
我有一个实体Report，我想将其值插入到数据库表中。Report必须插入以下属性：
reportID - int
RoleID - int
Created_BY = SYSTEM(default)
CURRENT_TIMESTAMP
Now the problem is with the 2nd attribute. I have a report with the LIST<ROLES>attributes. ROLESis a well defined entity which has an IDand a NAME. From this list I have to extract every role and insert each role's ID into the table.
现在的问题是第二个属性。我有一份带有LIST<ROLES>属性的报告。ROLES是一个定义明确的实体，它具有ID和NAME。从这个列表中，我必须提取每个角色并将每个角色的 ID 插入表中。
So my query presently looks as below :
所以我的查询目前如下所示：
INSERT INTO REPORT_MARJORIE_ROLE(REPORT_ID, ROLE_ID, CREATED_BY, CREATED)
VALUES({0}, {1}, 'SYSTEM', CURRENT_TIMESTAMP)
The C# code from where I am parsing these values is as follows :
我解析这些值的 C# 代码如下：
try
{
StringBuilder _objSQL = new StringBuilder();
_objSQL.AppendFormat(Queries.Report.ReportQueries.ADD_NEW_ROLES, report.ID, "report.MarjorieRoles.Add(MarjorieRole"));
_objDBWriteConnection.ExecuteQuery(_objSQL.ToString());
_objDBWriteConnection.Commit();
_IsRolesAdded = true;
}
So please guide me how to add roles from C# function
所以请指导我如何从 C# 函数中添加角色
采纳答案by marc_s
I'm assuming you say SQL(structured query language) and you really mean Microsoft SQL Server(the actual database product) instead - right?
我假设您说的是SQL（结构化查询语言），而实际上是指 Microsoft SQL Server（实际的数据库产品）——对吗？
You cannot insert a whole list as a whole into SQL Server - you need to insert one row for each entry. This means, you need to call the INSERT statement multiple times.
您不能将整个列表作为一个整体插入 SQL Server - 您需要为每个条目插入一行。这意味着，您需要多次调用 INSERT 语句。
Do it like this:
像这样做：
// define the INSERT statement using **PARAMETERS**
string insertStmt = "INSERT INTO dbo.REPORT_MARJORIE_ROLE(REPORT_ID, ROLE_ID, CREATED_BY, CREATED) " +
"VALUES(@ReportID, @RoleID, 'SYSTEM', CURRENT_TIMESTAMP)";
// set up connection and command objects in ADO.NET
using(SqlConnection conn = new SqlConnection(-your-connection-string-here))
using(SqlCommand cmd = new SqlCommand(insertStmt, conn)
{
// define parameters - ReportID is the same for each execution, so set value here
cmd.Parameters.Add("@ReportID", SqlDbType.Int).Value = YourReportID;
cmd.Parameters.Add("@RoleID", SqlDbType.Int);
conn.Open();
// iterate over all RoleID's and execute the INSERT statement for each of them
foreach(int roleID in ListOfRoleIDs)
{
cmd.Parameters["@RoleID"].Value = roleID;
cmd.ExecuteNonQuery();
}
conn.Close();
}
回答by Nikhil Agrawal
let say lstrolesis your LIST<ROLES>.
让说lstroles是你的LIST<ROLES>。
lstroles.ForEach(Role =>
{
/* Your Insert Query like
INSERT INTO REPORT_MARJORIE_ROLE(REPORT_ID, ROLE_ID, CREATED_BY, CREATED)
VALUES(REPORT_ID, Role.ID, {0}, {1}, 'SYSTEM', CURRENT_TIMESTAMP);
Commit you query*\
});
On a personal note: Beware of SQL Injection.
个人注意事项：谨防 SQL 注入。
