Code Efficient Multiple-Entry Parameteric SQL Insert: C#

Question

I have a series of objects which each have their own helper functions to prepare them for an SQL Insert. When originally coded, this was accomplished by ordinary string manipulation to generate the SQL Command Text. The string would then get passed to a generic SQL Execute module which does the database communication. I am now converting these methods to use parameters for best practice employment. My Select Query, method looks like this:

public static DataTable ExecuteSelectSQLQuery(string SQLString,
        List<SqlParameter> myParameters)
    {

        string fullSQLString = "";
        fullSQLString = "USE " + dbName + "\n" + SQLString;
        string myConnString = ConnectionStringHelper.GetConnectionString();
        Console.WriteLine("Connecting to SQL Server for Parameterized Query...");
        SqlConnection connection = new SqlConnection(myConnString);
        SqlDataAdapter myAdapter = new SqlDataAdapter(SQLString, connection);
        foreach (SqlParameter para in myParameters)
            myAdapter.SelectCommand.Parameters.Add(para);

        DataSet mySet = new DataSet();
        myAdapter.Fill(mySet, "myTable");
        DataTable dt = mySet.Tables["myTable"];
        Console.WriteLine("Connection Closed.  Operation Complete.");
        return dt;
    }

I am now seeking to parameterize the Insert query in the same fashion. At times, I will want to upload a few hundred records at a time, so my initial method just built the SQL accordingly. When I seek to parameterize the query, it seems like the preferred bulk processing technique is to run a loop within the connection, as shown here. I want my execute function to be object agnostic; I'm happy to submit strings and parameters, but I don't want to be looping through my data source within the SQL Execute method.

Short of calling a new single insert function for each row of data, is there a best-practice way of efficiently passing multiple rows of parameterized data?

If you already have the datatable, it is a small matter to perform a bulk copy. Take a peek at SqlBulkCopy — John Cappelletti
– John Cappelletti, Commented Oct 14, 2019 at 22:01
I'm currently only using the data table as a conduit out of the query... maybe the best answer is to create a DataTable to do the bulk copy, but I was hoping there was an easier way. — tmptplayer
– tmptplayer, Commented Oct 14, 2019 at 22:21
BulkCopy is very performant. For example, the first time I loaded the treasury rates (tens of thousands of records), It completed so fast, that I assumed it failed. It did not. The only trick is the datatable has to match the structure of the receiving table. This is one reason why I use staging tables. — John Cappelletti
– John Cappelletti, Commented Oct 14, 2019 at 22:25
Does BulkCopy need to be a complete match to the receiving table, or can it miss fields? Ie - destination table has fields (A, B, C)... with an SQL Insert I'd say INSERT INTO Table(A, C) Values ... that would still function in BulkCopy right? — tmptplayer
– tmptplayer, Commented Oct 14, 2019 at 23:07

Chris F Carroll · Accepted Answer · 2019-10-14 23:01:54Z

0

Yes. The very simplest way is to use Dapper which can reduce your insert to:

static int Inserts<T>(string sqlInsertString, IEnumerable<T> values)
{
    using(var connection= 
           new SqlConnection( ConnectionStringHelper.GetConnectionString() ))
    {
        return connection.Execute(sqlInsertString, values);
    }
}

static int Inserts(string sqlInsertString, DataTable values)
{
    return Inserts(sqlInsertString, values.Select();
}

public class MyType
{
    public int Field1 {get;set;}
    public string Field2 {get;set;}
    public string Etc__ {get;set;}
}

If you aren't familiar with using nuget packages, there's a walkthrough here https://learn.microsoft.com/en-us/nuget/quickstart/install-and-use-a-package-in-visual-studio

Dapper makes working with a SQL database much easier.

edited Oct 14, 2019 at 23:01

answered Oct 14, 2019 at 22:06

Chris F Carroll

12.6k4 gold badges62 silver badges74 bronze badges

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

6 Comments

tmptplayer Over a year ago

In that this function requires me passing List<MyType> values, this is not the direction I wanted to go.

David Browne - Microsoft Over a year ago

Take some time to explore Dapper. It's really not a great idea to build your own ADO.NET helper library.

Chris F Carroll Over a year ago

To do a bulk insert, what do you want to pass in?

tmptplayer Over a year ago

In an ideal world, a string and a list of parameters. What I don't want is to have to create a new function for every different type of table that I want to update because the Object that I would have to pass into the query would look different.

Chris F Carroll Over a year ago

But to bulk insert you'd pass a string and an array of parameters. Which gets awkward to handle when untyped

|

Collectives™ on Stack Overflow

Code Efficient Multiple-Entry Parameteric SQL Insert: C#

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related