CODEWITHSUNDEEP
c#sqlsql-serverlinq-to-sqlstored-procedures
frenchie
frenchie

Reputation: 51947

converting linq to sql to stored procedure for bulk insert

I have a L2S query that looks like this:

using (MyDC TheDC = new MyDC())
{
   foreach (MyObject TheObject in TheListOfMyObjects)
   {
      DBTable TheTable = new DBTable();

      TheTable.Prop1 = TheObject.Prop1;
      TheTable.Prop2 = TheObject.Prop2; 
      // only 2 properties, an int and a string

      TheDC.DBTables.InsertOnSubmit(TheTable);
   }
   TheDC.SubmitChanges();
}

How could I change this to a stored procedure that does a bulk insert of the list? I found this article that talks about using a dataset and sqlbulkcopy classes; is this the best way to do it?

Thank you for your suggestions and feedback.

Upvotes: 2

Views: 1469

Answers (3)

Sal Zaki
Sal Zaki

Reputation: 41

The best option is not to use InsertOnSubmit in your loop. Try the following.

using (MyDC TheDC = new MyDC())
{
  List<DBTable> TheTables = new List<DBTable>();
  foreach (MyObject TheObject in TheListOfMyObjects)
  {
    DBTable TheTable= new DBTable();  
    TheTable.Prop1 = TheObject.Prop1;
    TheTable.Prop2 = TheObject.Prop2; 
    // only 2 properties, an int and a string
    TheTables.Add(TheTable);
  }
  TheDC.DBTables.InsertAllOnSubmit(TheTables);
  TheDC.SubmitChanges();
}

Hope this helps.

Upvotes: 0

Arion
Arion

Reputation: 31239

Maybe something like this:

void Main()
{
    //Your list of objects
    List<MyObject> TheListOfMyObjects=new List<MyObject>();

    var dt=new DataTable();
    dt.Columns.Add("Prop1",typeof(int));
    dt.Columns.Add("Prop2",typeof(string));
    foreach (var TheObject in TheListOfMyObjects)
    {
        dt.Rows.Add(TheObject.Prop1,TheObject.Prop2);
    }
    InsertWithBulk(dt,"YourConnnectionString","MyObject");
}
private void InsertWithBulk(DataTable dt,string connectionString,string tableName)
{
    using (SqlConnection destinationConnection =new SqlConnection(connectionString))
    {
        destinationConnection.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
        {
            bulkCopy.DestinationTableName =tableName;

            try
            {
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                //Exception from the bulk copy
            }
        }
    }
}

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88074

Looks good to me.

Quite frankly I'd drop L2S entirely because of it's general horrible performance but you may have an app that is too far along to do that.

Upvotes: 0

Related Questions