CODEWITHSUNDEEP
c#sqlsql-server
catlovespurple
catlovespurple

Reputation: 690

Insert A list of objects into SQL Server table

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

Upvotes: 14

Views: 44457

Answers (3)

Hambone
Hambone

Reputation: 16377

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

Upvotes: 17

Steve
Steve

Reputation: 216273

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

Upvotes: 9

bdparrish
bdparrish

Reputation: 2764

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

Upvotes: -1

Related Questions