1

I have a datatable table which have multiple records. I want to Insert this datatable to ms-access table without using loop.

I want to insert multiple rows/records into ms-access database as whole. I don't want to insert records one by one.

using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString))
{
      var adap = new OleDbDataAdapter();
      adap.SelectCommand = new OleDbCommand ("select RollNo, SName, FName, DOB, [Section] from students", conn);
      var cb = new OleDbCommandBuilder(adap);
      cb.GetInsertCommand();
      cb.GetDeleteCommand();
      cb.GetUpdateCommand();
      conn.Open();
      adap.Update(table);   
 }

Loading of data from excel sheet to datatable. code is below,

using (OleDbConnection connExcel = new OleDbConnection(DatabaseObjects.ConnectionStringExcel))
        {
            string queryExcel = "select * from [" + sheetName + "$]";
            using (OleDbCommand commandExcel = new OleDbCommand(queryExcel, connExcel))
            {
                connExcel.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = commandExcel;
                adapter.Fill(dtSheetData);
            }
        }
  • 1
    Does this answer your question? [Insert entire DataTable into database at once instead of row by row?](https://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row) – June7 Sep 01 '20 at 17:28
  • Given link is work for SQL-Server. – Muhammad Rizwan Sep 01 '20 at 17:37
  • Would think the concept is same but I have read some discussion indicating not possible with Access. – June7 Sep 01 '20 at 18:40
  • is the data source and filling of the "from" table from the same database? If yes, then you can use a append query (insert query with select). If the table source is NOT from the same database, or you created the table from NOT the same data source? Then you have to loop row by row. The access UI is able to operate sql between two different data sources at the same time - but .net cannot. However, you don't mention the source table origin? if that is a ODBC database, then you can get JET/ACE to do this for you, but have to by-pass oleDB and use the JET/ACE (dao) object model directly. – Albert D. Kallal Sep 02 '20 at 03:36
  • @June7 concept is same but how we do it in Access.? – Muhammad Rizwan Sep 02 '20 at 10:54
  • Data source of `table` is from Excel sheet and `table` is filling the Access Table. @AlbertD.Kallal – Muhammad Rizwan Sep 02 '20 at 10:56
  • Well, as noted, it is possible to create a linked table in Access. This would allow a query from .net to import the excel sheet as a query. However, this requires one to create that linked table in Access. this would suggest that you have to adopt and launch a copy of Access as a COM automation object in .net. And that means you need in addition to JET/ACE, but also a copy of Access installed for this to work. And the free runtime does not support you creating a instance of Access in .net. You can get around this by launching access via shell(), and then using GetObject to get that instance. – Albert D. Kallal Sep 02 '20 at 16:02
  • so it is possible to use a JET/ACE query here. You also can specify the source table in the query and NOT use your already existing/loaded table in .net. But, with a current table loaded in .net? You have to use row by row copy. So, a ACE query is possible here, the trick would be to avoid having to use Access in addition to JET/ACE. This means you would not load the Excel sheet into a table. – Albert D. Kallal Sep 02 '20 at 16:05
  • I am working with ACE Oledb. – Muhammad Rizwan Sep 02 '20 at 16:10
  • This method is very complex. @AlbertD.Kallal – Muhammad Rizwan Sep 02 '20 at 16:19
  • with .net you are limited to one connection. You don't mention how you loading up the table from Excel, but you could try the steps here:https://www.c-sharpcorner.com/article/import-data-from-excel-to-access-using-ado-net/ – Albert D. Kallal Sep 02 '20 at 16:53
  • @AlbertD.Kallal i have added the code in question for loading datatable from excel sheet. – Muhammad Rizwan Sep 02 '20 at 16:57

0 Answers0