插入列表＆LT;＆GT;到SQL Server表 [英] Inserting a List<> into SQL Server table

问题描述

我有一个实体报告我要插入到数据库表中的值。的报告下面的属性必须被插入的：

I have an entity Report whose values I want to insert into a database table. The following attributes of Report have to be inserted:

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

现在的问题是与第二属性。我与 LIST℃的报告;角色与GT; 属性。 角色是它有一个 ID 和一个定义良好的实体名称。从这个名单上有提取每一个角色，并插入每个角色的ID到表中。

Now the problem is with the 2nd attribute. I have a report with the LIST<ROLES> attributes. ROLES is a well defined entity which has an ID and a NAME. From this list I have to extract every role and insert each role's ID into the table.

所以我的查询presently看起来如下：

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)

从那里我解析这些值的C＃code是如下：

The C# code from where I am parsing these values is as follows :

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＃功能添加角色

So please guide me how to add roles from C# function

推荐答案

的我假设你说的 SQL （结构化查询语言），你真的意味着微软的 SQL Server的（实际的数据库产品），而不是 - 右的

I'm assuming you say SQL (structured query language) and you really mean Microsoft SQL Server (the actual database product) instead - right?

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

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.

做这样的：

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

这篇关于插入列表＆LT;＆GT;到SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！