14

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();
        }
    }
}
Improve this question
8
  • You could add multiple rows within one query. Just be sure the parameters are unique. Commented Oct 26, 2015 at 21:16
  • An alternative is to create a stored procedure with a table-valued parameter Commented Oct 26, 2015 at 21:18
  • @JeroenvanLangen But if I have 100 records in the list, how come I could add one by one? Commented Oct 26, 2015 at 21:20
  • I've found some info for you: stackoverflow.com/questions/2972974/… Commented Oct 26, 2015 at 21:23
  • 1
    You might want to take a peek at this article and consider not using AddWithValue in the future. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… Commented Oct 26, 2015 at 21:30

3 Answers 3

Reset to default
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();
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

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?
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.
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.
@Hambone I still think there needs for each statement in the sql transaction, how come we dump all the data at one time?
@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 of the code using a transaction, see that only one transaction is created.
|
9

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);
}
Improve this answer

2 Comments

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.
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
-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();
    }
}
Improve this answer

3 Comments

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)
...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.
How what? I pointed out 3 issues of why this is not a good solution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.