Inserting a List<> into SQL Server table

Question

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.

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 :

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

have you considered an ORM? With Linq to SQL or Entity Framework you could be up and running with this in half an hour. There's also nHibernate. — Andras Zoltan
– Andras Zoltan, Commented May 21, 2012 at 7:25

marc_s · Accepted Answer · 2012-05-21 07:27:55Z

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

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.

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

yeah yeah you are right...its MS SQL Server thanks for the help and correcting me there
How about passing a list as xml? That would solve your problem
@John: yes - you could send it as XML - but then you'll have to "shred" it inside SQL Server again and that also requires some effort and code ....
you could maybe use a Transaction, so in case something goes wrong it will rolleback
You can use SqlBulkCopy, learn.microsoft.com/en-us/dotnet/api/…

Nikhil Agrawal · Accepted Answer · 2012-05-21 07:31:32Z

0

let say lstroles is your 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.

edited May 21, 2012 at 7:31

answered May 21, 2012 at 7:25

Nikhil Agrawal

48.8k25 gold badges134 silver badges212 bronze badges

1 Comment

marc_s Over a year ago

You should NOT concatenate together your SQL statements! That's opening the barn door to SQL injection attacks, and it's also slower from a performance point of view (no reuse of the SQL Server execution plan!)

Collectives™ on Stack Overflow

Inserting a List<> into SQL Server table

2 Answers 2

5 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Linked

Related