Read Excel records and import SQL database

To prepare, you need to import Excel data into the database in the near period.

Reference namespaces:

using System.Configuration; using System.Data; using System.Data.OleDb; using System.Data.SqlClient;

You can write a method that reads a method of an Excel document and returns a DataSet dataset:

 private  DataSet importexceltodataset ( String  virtualpath, string   sqlquerystatement) { string  excelconnectionstring = DB.        Excelconnectionstring (Server.MapPath (virtualpath)); OleDbConnection dc  = new   OleDbConnection        (excelconnectionstring); OleDbDataAdapter da  = new          OleDbDataAdapter (Sqlquerystatement, DC);        DataSet ds  = new   DataSet (); Da.        Fill (DS);     return   DS; }

Source Code

To import a database, in a database, you need to create a table to store the data imported by Excel:

Next, you can make the SqlBulkCopy method replicate the database:

Try        {            stringCS = configurationmanager.connectionstrings["insussqlconnectionstring"].            ConnectionString; using(SqlConnection sqlconn =NewSqlConnection (CS)) {                stringSqlquerystatement ="SELECT [material],[plnt],[level],[item],[component],[object description] from [sheet1$]"; stringVirtualPath ="~/app_data/book1.xlsx"; DataSet DS=Importexceltodataset (virtualpath, sqlquerystatement); DataTable DT= ds. tables[0];                Sqlconn.open (); using(SqlBulkCopy SQLBC =NewSqlBulkCopy (sqlconn)) {SQLBC. DestinationTableName="BOM"; SQLBC. Columnmappings.add ("Material","Material"); SQLBC. Columnmappings.add ("plnt","plnt"); SQLBC. Columnmappings.add (" Level"," Level"); SQLBC. Columnmappings.add ("Item","Item"); SQLBC. Columnmappings.add ("Component","Component"); SQLBC. Columnmappings.add ("Object Description","Object Description"); SQLBC.                    WriteToServer (DT); Response.Write ("Data Import Successful! "); }            }        }        Catch(Exception ex) {Throwex; }

Source Code

Demonstrate:

Read Excel records and import SQL database