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