Geek asks and answers

Insert List <> into SQL Server Table

I have an object Reportwhose values ​​I want to insert into the database table. The following attributes Reportmust be inserted:

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

Now the problem is with the second attribute. I have a report with attributes LIST<ROLES>. ROLES- This is a clearly defined object that has IDa NAME. From this list I have to extract each role and insert each role identifier into the table.

So my query looks like this:

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

The C # code 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;
}

So please tell me how to add roles from a C # function

+5
source share
2

, SQL ( ), Microsoft SQL Server ( ) - right?

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 and execute the INSERT statement for each of them
    foreach(int roleID in ListOfRoleIDs)
    {
      cmd.Parameters["@RoleID"].Value = roleID;
      cmd.ExecuteNonQuery();
    }

    conn.Close();
}
+10

, 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.

0

More articles:


All Articles