Insert A list of objects into SQL Server table

Question

I want to insert a list of objects into sql server table. However, currently, I have to open and close the sql connection each time I insert a record row.

I just wonder if there is a way I can insert all the objects in the record list at one time? Here is the code snippet.

public void InsertDataToDb()
{
    string connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
    var records = GetRecords();

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand cmd =
            new SqlCommand(
                "INSERT INTO TableName (param1, param2, param3) VALUES (@param1, @param2, @param3)");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        foreach (var item in records)
        {
            cmd.Parameters.AddWithValue("@param1", item.param1);
            cmd.Parameters.AddWithValue("@param2", item.param2);
            cmd.Parameters.AddWithValue("@param3", item.param3);

            conn.Open();
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            conn.Close();
        }
    }
}

You could add multiple rows within one query. Just be sure the parameters are unique. — Jeroen van Langen, Oct 26 '15 at 21:16
An alternative is to create a stored procedure with a table-valued parameter — Giorgos Betsos, Oct 26 '15 at 21:18
@JeroenvanLangen But if I have 100 records in the list, how come I could add one by one? — catlovespurple, Oct 26 '15 at 21:20
I've found some info for you: http://stackoverflow.com/questions/2972974/how-should-i-multiple-insert-multiple-records — Jeroen van Langen, Oct 26 '15 at 21:23
You might want to take a peek at this article and consider not using AddWithValue in the future. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — Sean Lange, Oct 26 '15 at 21:30
@GiorgosBetsos you remind me to write a stored procedure. For my problem here, I think using store procedure is a little bit overkilled. SP is good for more complicated logic than my code. Here I just wanted to insert some records. — catlovespurple, Oct 26 '15 at 21:42
@catlovespurple on the contrary. Using stored procedure for all data manipulation is valid and a great strategy. It is the first step in truly separating the data layer. When you have pass through queries like this embedded in your application it requires changes to the application when a simple change to the insert is required. Don't think of a stored procedure as a tool only to be used when the logic is complicated. The best procedures are simple and don't try to recreate programming logic. — Sean Lange, Oct 27 '15 at 13:51

Hambone · Accepted Answer · 2015-10-26T21:46:12.373

17

I'm making assumptions about your datatypes (change them as you need, based on what the actual DbTypes are), but something like this should do it:

    public void InsertDataToDb()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["connection"].
            ConnectionString;
        var records = GetRecords();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            SqlCommand cmd =
                new SqlCommand(
                    "INSERT INTO TableName (param1, param2, param3) " +
                    " VALUES (@param1, @param2, @param3)");
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            cmd.Parameters.Add("@param1", DbType.String);
            cmd.Parameters.Add("@param2", DbType.String);
            cmd.Parameters.Add("@param3", DbType.String);

            foreach (var item in records)
            {
                cmd.Parameters[0].Value = item.param1;
                cmd.Parameters[1].Value = item.param2;
                cmd.Parameters[2].Value = item.param3;

                cmd.ExecuteNonQuery();
            }

            conn.Close();
        }
    }

I'd also recommend invoking a transaction so that all 100 inserts can be done as a single transaction.

-- EDIT --

Regarding the transaction, here is about how you would add it:

conn.Open();   // already there -- to show you where to start the transaction

SqlTransaction trans = conn.BeginTransaction();
string sql = "INSERT INTO TableName (param1, param2, param3) " +
    "VALUES (@param1, @param2, @param3)";

SqlCommand cmd = new SqlCommand(sql, conn, trans);

And then, before you close your connection (or after the last statement in the transaction, which can include selects, updates, whatever):

trans.Commit();

edited Oct 26 '15 at 21:46

answered Oct 26 '15 at 21:24

Hambone

15,600
8
46
69

1

Thanks for your answer. They are working well. I wonder the transaction you'd recommend is to write a T-Store Procedure to do all these work at one time? – catlovespurple Oct 26 '15 at 21:36
No, the transaction within ADO.net... I didn't compile/test this and didn't want to risk making a mistake on the transaction piece, but it's pretty easy. I'll fix it and show you what I mean. – Hambone Oct 26 '15 at 21:37
1

Per your update, just as a side note, you can wrap the transaction in a `using` statement, its a easy way to ensure that the transaction gets rolled back if you leave the scope without calling `Commit()`. However because the connection is being closed anyway it does not really matter. – Scott Chamberlain Oct 26 '15 at 21:56
@Hambone I still think there needs for each statement in the sql transaction, how come we dump all the data at one time? – catlovespurple Oct 26 '15 at 22:06
@ScottChamberlain I agree. I would wrap both SqlTransaction and SqlCommand in a using statement. – catlovespurple Oct 26 '15 at 22:07
1

@catlovespurple There will be 100 requests but all of those requests will be in a single transaction so if the process fails they all get rolled back and you don't get half inserted and half not. [Here is a full example](https://gist.github.com/leftler/b6a81300f24a8fea1ce3) of the code using a transaction, see that only one transaction is created. – Scott Chamberlain Oct 26 '15 at 22:09
@catlovespurple -- you don't necessarily dump all of the data at once, but a single transaction can definitely take thousands of actions. It's very database-friendly, which is an often-overlooked element of programming. All of Scott's comments are dead-on, as well. – Hambone Oct 27 '15 at 01:04
@ScottChamberlain thanks for your full code. It's helpful and pretty clear. – catlovespurple Oct 27 '15 at 21:20
what problem if i move cmd.ExecuteNonQuery(); below of foreach loop? – Hien Nguyen May 03 '19 at 04:18
@HienNguyen -- if you do that, it will only execute the very last insert. – Hambone May 03 '19 at 11:41

Steve · Answer 2 · 2015-10-26T21:40:15.447

8

You could use an ORM like Dapper

With this library you could write something like this

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    conn.Execute(@"INSERT INTO TableName (param1, param2, param3) 
                   VALUES (@param1, @param2, @param3)", records);
}

edited Oct 26 '15 at 21:40

answered Oct 26 '15 at 21:25

Steve

213,761
22
232
286

Dapper is a good library and it think it's a little bit overkill for my project. Thanks for your answer, if I have larger/more db involved project, I will consider Dapper. – catlovespurple Oct 26 '15 at 21:37
It is up to you. I have used the direct ADO net approach thousands of times, so I think I understand your stance. But from my point of view, writing all that stuff is a big waste of time in particular with little projects – Steve Oct 26 '15 at 21:44

score -1 · Answer 3 · edited May 23 '17 at 12:16

-1

See this answer

But if you wanted to update yours, then do this

public void InsertDataToDb()
{
    string connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
    var records = GetRecords();

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        StringBuilder nonQuery = new StringBuilder();
        foreach(var item in records)
        {
            nonQuery.AppendFormat("INSERT INTO TableName (param1, param2, param3) VALUES ({0}, {1}, {2});",
                item.param1,
                item.param2,
                item.param3);
        }

        SqlCommand cmd = new SqlCommand(nonQuery.ToString());

        cmd.CommandType = CommandType.Text;

        cmd.Connection = conn;

        conn.Open();

        cmd.ExecuteNonQuery();

        cmd.Parameters.Clear();

        conn.Close();
    }
}

edited May 23 '17 at 12:16

Community

1
1

answered Oct 26 '15 at 21:28

bdparrish

3,216
3
37
58

Making the query un-parameterized is not good solution. You are introducing security holes and will now need to deal with things like proper formatting of dates and putting quotes around text. The correct way is to do the same concept but with parameters (See [Hambone's answer](http://stackoverflow.com/a/33355911/80274)) – Scott Chamberlain Oct 26 '15 at 21:30
...how? does it solve the problem that he is asking? are you saying this because of security on sql injection? ...and I upvoted Hambone's answer as it came at the same time i wrote mine. – bdparrish Oct 26 '15 at 21:33
How what? I pointed out 3 issues of why this is not a good solution. – Scott Chamberlain Oct 26 '15 at 21:34

Insert A list of objects into SQL Server table

3 Answers3