将List 插入SQL Server表

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

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.

现在的问题是第二个属性。我有一个包含LIST 属性的报告。 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#函数添加角色

2 个解决方案

#1

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

#2

let say lstroles is your LIST<ROLES>.

让我们说lstroles是你的LIST 。

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注入。

#1