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, Oct 14 '19 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, Oct 14 '19 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, Oct 14 '19 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, Oct 14 '19 at 23:07

Chris F Carroll · Answer 1 · 2019-10-14T23:01:54.207

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 '19 at 23:01

answered Oct 14 '19 at 22:06

Chris F Carroll

11,146
3
53
61

In that this function requires me passing List values, this is not the direction I wanted to go. – tmptplayer Oct 14 '19 at 22:18
Take some time to explore Dapper. It's really not a great idea to build your own ADO.NET helper library. – David Browne - Microsoft Oct 14 '19 at 22:38
To do a bulk insert, what do you want to pass in? – Chris F Carroll Oct 14 '19 at 22:38
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. – tmptplayer Oct 14 '19 at 22:58
But to bulk insert you'd pass a string and an *array* of parameters. Which gets awkward to handle when untyped – Chris F Carroll Oct 14 '19 at 23:01
The way I have it working now is that I receive the List – tmptplayer Oct 14 '19 at 23:05

Code Efficient Multiple-Entry Parameteric SQL Insert: C#

1 Answers1