将List<>插入SQL Server表中

Question

我有一个实体Report，其中的值需要插入到数据库表中。需要插入Report的以下属性：

reportID - int
RoleID - int
Created_BY = SYSTEM(default)
CURRENT_TIMESTAMP

现在问题出在第二个属性上。我有一个报告，并包含 LIST<ROLES> 属性，其中ROLES是一个明确定义实体，它有一个ID和一个NAME。我需要从这个列表中提取每个角色并将每个角色的ID插入到表中。因此，我的查询目前如下所示：

INSERT INTO REPORT_MARJORIE_ROLE(REPORT_ID, ROLE_ID, CREATED_BY, CREATED)
VALUES({0}, {1}, 'SYSTEM', CURRENT_TIMESTAMP)

我正在解析这些值的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;
}

所以请指导我如何从C#函数中添加角色。

- DoNNie_DarkO

---

你考虑过使用ORM吗？使用Linq to SQL或Entity Framework，你可以在半小时内开始运行。还有nHibernate可供选择。 - Andras Zoltan

Answer 1

我假设你说的是SQL(结构化查询语言)，而实际上你指的是微软的SQL Server(一个数据库产品) - 是这样吗？

你无法将整个列表作为一个整体插入到SQL Server中，你需要为每个条目插入一行。这意味着你需要多次调用INSERT语句。

像这样执行：

// 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();
}      

- marc_s

---

是的，你说得对...它是MS SQL Server 感谢你的帮助并纠正我。 - DoNNie_DarkO

把列表作为XML传递怎么样？这将解决你的问题。 - mko

@John：是的 - 你可以将它作为XML发送 - 但是然后你还需要在SQL Server中再次“shred”它，这也需要一些努力和代码.... - marc_s

你可以使用事务，这样如果出现问题，它将回滚。 - WiiMaxx

Answer 2

假设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*\
   });

个人建议：注意防范SQL注入攻击。

- Nikhil Agrawal

---

你不应该将SQL语句拼接在一起！这会打开SQL注入攻击的大门，也会从性能角度变慢（因为无法重用SQL Server的执行计划！） - marc_s