Insert multiple rows to SQL from DataSet using LINQ

Question

I am trying to use LINQPad to query an MS Access table using DataSet, and wanted to insert the query result to a SQL table.

This is how I got the data from the MS Access table:

string connectionString = ("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\Temp\\temp.mdb;");
OdbcConnection myconnection = new OdbcConnection(connectionString);
OdbcDataAdapter myadapter = new OdbcDataAdapter("SELECT * FROM Name", myconnection);
DataSet myCustomersDS = new DataSet();
myadapter.Fill(myCustomersDS, "Name");

Now In LINQPad, I wanted to insert all records from the dataset myCustomersDS to a SQL table. I tried to query the dataset in LINQPad this way and it gives me the right result:

Connection.Open();
var toInsert = from b in myCustomersDS.Tables["Name"].AsEnumerable()
           select b;
toInsert.Dump();
Connection.Close();

I tried various versions of these commands, but always get errors related to the type of the DataSet not matching with the table type.

Name.InsertOnSubmit(toInsert);
SubmitChanges();

Thank you, Steven

Jeff Mercado · Accepted Answer · 2013-10-31 21:25:36Z

2

When inserting data into a table using LINQ Table objects, you need to map out the data you want to add to objects of the table's row type.

If you had a table called Name, you need to create an instance of a Name object, fill in the values to insert, then insert it.

If inserting a single row, use InsertOnSubmit(). Otherwise if you have a collection of objects to insert, use InsertAllOnSubmit().

e.g.,

// insert a single item
Name.InsertOnSubmit(new Name
{
    Name = "Bob",
    Title = "Janitor",
});

// inserting multiple items
var toInsert =
    from row in myCustomerDS.Tables["Name"].AsEnumerable()
    select new Name
    {
        Name = row.Field<string>("Name"),
        Title = row.Field<string>("Title"),
    };
Name.InsertAllOnSubmit(toInsert);

SubmitChanges();

answered Oct 31, 2013 at 21:25

Jeff Mercado

136k34 gold badges267 silver badges282 bronze badges

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

16 Comments

Steven Over a year ago

Thank you Jeff. My Name table has 67 fields. Do I have to really enumerate all the fields? I wanted to insert all fields that are in the dataset myCustomersDS, except one field called TIME_STAMP. Thanks, Steven (PS: how come I cannot insert new line in this comment? - it submits instead)

Jeff Mercado Over a year ago

When you're mapping out the values to insert, you don't have to map all fields, just the ones you're interested in. You don't need to add the TIME_STAMP field if you don't want it.

Steven Over a year ago

Hi Jeff, I am interested in importing all the fields from the dataset, since this is a profile transfer from another institute with exactly same data structure. So there are 67 fields. I thought there might be an easier way to specify all fields. But then I guess I would have a problem excluding the TIME_STAMP field.

Jeff Mercado Over a year ago

67 fields is a lot. Unfortunately there isn't really an easier way to do this. Reflection is an option however, just beware of any risks involved with that. It would be safer to perform the copying manually.

Steven Over a year ago

Ok, I will write down all the columns. In the example you gave me you wrote InsertAllOnSubmit. I also have multiple rows. So this example would take care of multiple rows? Does it mean that the Name object will have multiple rows, or when you call InsertAllOnSubmit, it will run a loop for each row and Name has 1 row but is called multiple times in the loop? It is not that clear for me where is the loop in the code. And what is Reflection, and how can that help in this case? Thank you, Steven

|

Frank · Accepted Answer · 2013-12-11 13:32:08Z

1

There is a context driver for MS Access in LinqPad, you can download it here Microsoft Access Data Context Driver download the .lpx-file, in LinqPad, click on Add connection, then click on 'view more drivers...' then click on 'browse' and select the .lpx-file the driver is added on the top list-box MS Access Data Context Driver in LinqPad

answered Dec 11, 2013 at 13:32

Frank

2491 silver badge6 bronze badges

Collectives™ on Stack Overflow

Insert multiple rows to SQL from DataSet using LINQ

2 Answers 2

16 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related