How to populate SQL Server database with list of list of objects instead of looping

Question

I have data coming from an Excel spreadsheet, I cannot change how this comes in. Is there a solution for adding IList<IList<Object>> values to a SQL Server database instead of looping as I am reaching limit with currently 5k rows.

I was also informed that I shouldn't use injection, so any alternatives are welcomed.

public static void Load_Table()
{
    // This function on_click populates the datagrid named JumpTable
    // this.JumpTable.ItemsSource = null; // Clears the current datagrid before getting new data
    // Pulls in the 2d table from the client sheet
    IList<IList<Object>> client_sheet = Get(SetCredentials(), "$placeholder", "Client!A2:AY");
    DBFunctions db = new DBFunctions();
    db.postDBTable("DELETE FROM Client");

    foreach (var row in client_sheet)
    {
        string exe = "INSERT INTO Client ([Tracker ID],[Request ID]) VALUES('" + row[0].ToString() + "','" + row[1].ToString() + "')";
        db.postDBTable(exe);
    }
}

Database functions

public SqlConnection getDBConnection()
{
    // --------------< Function: Opens the connection to our database >-------------- \\
    string connectionString = Properties.Settings.Default.connection_string; // Gets the connection source from properties
    SqlConnection dbConnection = new SqlConnection(connectionString); // Creates the connection based off our source
    if (dbConnection.State != ConnectionState.Open) dbConnection.Open(); // If it's not already open then open the connection

    return dbConnection;
}
public DataTable getDBTable(string sqlText)
{
    // --------------< Function: Gets the table from our database >-------------- \\
    SqlConnection dbConnection = getDBConnection();

    DataTable table = new DataTable();
    SqlDataAdapter adapter = new SqlDataAdapter(sqlText, dbConnection);adapter.Fill(table);

    return table;
}

public void postDBTable(string sqlText)
{
    // --------------< Function: Post data to our database >-------------- \\
    SqlConnection dbConnection = getDBConnection();

    SqlCommand cmd = new SqlCommand(sqlText, dbConnection);cmd.ExecuteNonQuery();
}

Before anything, you should really be fixing that injection problem. You need to parametrise those queries. — Thom A
– Thom A ♦, Commented Jul 10, 2019 at 15:09
parametrise? I was going off of a tutorial that used this method, could you explain. — xchrisbradley
– xchrisbradley, Commented Jul 10, 2019 at 15:10
"Bind list of list of objects to sql server database"? What exactly do you mean by this? You don't "bind" anything to a database. — mm8
– mm8, Commented Jul 10, 2019 at 15:11
SqlCommand.Parameters Property @ChristopherBradley . If the tutorial is "recommending" you inject valeus int your queries, my honest opinion is you find a different (and better) tutorial. — Thom A
– Thom A ♦, Commented Jul 10, 2019 at 15:15

RubberChickenLeader · Accepted Answer · 2019-07-10 18:29:16Z

1

I have worked with lots of bulk data loads in the past. There are two main ways to avoid individual inserts with SQL Server, a list of values inserted at one time or using bulk insert.

the first option to use a list of values is like this:

INSERT INTO Foo (Bar,Baz)
VALUES ('bar','baz'),
       ('anotherbar','anotherbaz')

In c# you would loop through your list and build the values content, however doing this with out a sql injection vulnerability is difficult.

The second option is to use bulk insert with SQL Bulk copy and a datatable. Before getting to the code below you would build a DataTable that holds all your data then use SqlBulkCopy to insert rows using Sql functionality that is optimized for inserting large amounts of data.

using (var bulk = new SqlBulkCopy(con)
{
    //bulk mapping is a SqlBulkCopyColumnMapping[] and is not necessaraly needed if the DataTable matches your destination table exactly
    foreach (var i in bulkMapping)
    {
        bulk.ColumnMappings.Add(i);
    }

    bulk.DestinationTableName = "MyTable";

    bulk.BulkCopyTimeout = 600;
    bulk.BatchSize = 5000;

    bulk.WriteToServer(someDataTable);
}

These are the two framework included methods. There are other libraries that can help. Dapper is one but I am not sure how it handles inserts on the back end. Entity framework is another but it does single inserts so it is just moving the problem from your code to some one else's.

answered Jul 10, 2019 at 18:29

RubberChickenLeader

1,0119 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xchrisbradley Over a year ago

Your second option was a valid solution, I will also explore SSIS it seems like there are differences of opinions on this subject.

Collectives™ on Stack Overflow

How to populate SQL Server database with list of list of objects instead of looping

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related