CODEWITHSUNDEEP
c#sql-serverdatatablesqlcommand
coding
coding

Reputation: 63

Is this the fastest way to add multiple rows to a SQL Server table

I have a general question that I have not found any answer to. I think it is important to ask this question so I know how to structure the whole application so I am on the right track from the very beginning.

I believe it is practical/efficient to add Multiple rows in one Query command. The below code adds 3 rows with 5 values in 5 columns at once.

See this image also of the table:

Image of DataTable

My question now is. This table will later have 100,000 columns where I am thinking of for example adding 10 rows at a time. Now imagine that this cmdString will be EXTREMELY long.

My question simply is. Is this okay or is this the wrong way to go here when I add so much information in one query? (Is this the fastest way to do it or should I do the below code in another way?)

Thank you!

void addMultipleRowWithValuesSQL()
{
    String cmdString = "INSERT INTO DateTimes(DateTime,F1,F2,G1,G2) " + 
                       "VALUES" +
                           "('201005011715','1',2,'3','4')," +
                           "('201005011730','5',6,'7','8')," +
                           "('201005011745','9',10,'11','12');";
 
    using (SqlConnection conn = new SqlConnection(GetConnectionString()))
    {
        using (SqlCommand comm = new SqlCommand(cmdString))
        {
            try
            {
                comm.Connection = conn;

                conn.Open();
                int i = comm.ExecuteNonQuery();

                if (i != 0) 
                {
                    MessageBox.Show(i + "Rows Added"); 
                }
            }
            catch (SqlException ex) 
            {
                 MessageBox.Show(ex.ToString()); 
            }
        }
    }
}

static private string GetConnectionString()
{
    return "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\andre\\source\\repos\\TestDatabaseCreation\\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30";
}

Upvotes: 1

Views: 4253

Answers (2)

coding
coding

Reputation: 63

I was looking at this link that also showed of how to add multiple rows in one transaction. https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching

My question is if this is a valid and good way to do batch insert?
Or does this code also involve enormous amount of parsing like in my original post, if I would add for example 10,000 rows with VALUES for example 200 columns in one transaction?

Just to have a confirmation on that. The code do work to mention.

    void addMultipleRowWithValuesSQL()
    {
        List<string> dbOperations = new List<string>();
        dbOperations.Add("INSERT INTO DateTimes(DateTime, F1, F2, G1, G2) VALUES('201005011800', '11', '22', '33', '44');");
        dbOperations.Add("INSERT INTO DateTimes(DateTime, F1, F2, G1, G2) VALUES('201005011815', '55', '66', '77', '88');");
        dbOperations.Add("INSERT INTO DateTimes(DateTime, F1, F2, G1, G2) VALUES('201005011830', '99', '100', '101', '102');");

        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            SqlTransaction transaction = conn.BeginTransaction();

            foreach (string commandString in dbOperations)
            {
                SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
                cmd.ExecuteNonQuery();
            }
            transaction.Commit();
        }
    }

Upvotes: 1

Usama Safi
Usama Safi

Reputation: 171

They call it Bulk Insert, this link here will get you to a nice example that can explain. Bulk Insert In SQL Server From C#

Upvotes: 2

Related Questions