Read Excel records and import SQL database, excel import SQL database

Read Excel records and import SQL database, excel import SQL database

Prepare. Import the Excel data to the database in the near future.

Reference namespace:

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

 

You can write a method to read an Excel document and return the 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 data to a database, you must create a table to store the data imported from Excel:

Next, you can use the SqlBulkCopy method to copy the database:

 

Try {string cs = ConfigurationManager. connectionStrings ["InsusSqlConnectionString"]. connectionString; using (SqlConnection sqlConn = new SqlConnection (cs) {string sqlQueryStatement = "SELECT [Material], [Plnt], [Level], [Item], [Component], [Object description] FROM [Sheet1 $] "; string virtualPath = "~ /App_Data/Book1.xlsx "; DataSet ds = ImportExcelToDataSet (virtualPath, sqlQueryStatement); DataTable dt = ds. tables [0]; sqlConn. open (); using (SqlBulkCopy sqlbc = new SqlBulkCopy (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) {throw ex ;}Source Code

Demo:

 

 

The following content is added at, November 17:

Use sqlDataReader for import, without too many tests, the speed and DataSet are not much faster:

Private OleDbDataReader ImportExcelToDataReader (string virtualPath, string sqlQueryStatement) {string excelConnectionString = DB. excelConnectionString (Server. mapPath (virtualPath); OleDbConnection oleConn = new OleDbConnection (excelConnectionString); OleDbCommand oleComm = new OleDbCommand (sqlQueryStatement, oleConn); oleConn. open (); return oleComm. executeReader ();}Source Code

SqlBulkCopy method:

 

String sqlQueryStatement = "SELECT [Material], [Plnt], [Level], [Item], [Component], [Object description] FROM [Sheet1 $]"; string virtualPath = "~ /App_Data/Book1.xlsx "; string cs = ConfigurationManager. connectionStrings ["InsusSqlConnectionString"]. connectionString; SqlConnection sqlConn = new SqlConnection (cs); OleDbDataReader reader = ImportExcelToDataReader (virtualPath, sqlQueryStatement); using (SqlBulkCopy bulkCopy = new SqlBulkCopy (sqlConn) {bulkCopy. destinationTableName = "BOM"; try {sqlConn. open (); bulkCopy. writeToServer (reader); R Esponse. Write ("data import successful. ") ;}Catch (Exception ex) {Console. WriteLine (ex. Message) ;}finally {reader. Close (); sqlConn. Close ();}}Source Code