C # Two ways to import an Excel data table into a SQL database

Recently used to write a WinForm program to use Excel file Import database, online search method, found the attempt can be used.

Method One:

Implementation in C # can efficiently import Excel data into SQL Server database, many people through the loop to splicing SQL, this is not only error-prone and inefficient, the best way is to use BCP, This is the System.Data.SqlClient.SqlBulkCopy class to implement.

    Using System;        Using System.Collections.Generic;        Using System.ComponentModel;        Using System.Data;        Using System.Drawing;        Using System.Linq;        Using System.Text;        Using System.Windows.Forms;                Using System.Data.OleDb;                namespace Exceltosql {public partial class Form1:form {public Form1 ()                {InitializeComponent (); } private void Button1_Click (object sender, EventArgs e) {//test, will Student in Excel is imported into SQL Server db_test, creating a string connstring = "Server = (local) If the data table in SQL does not exist." UID = sa; PWD = sa;                    Database = Db_test ";                       System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog (); if (FD. ShowDialog () = = DialogResult.OK) {transferdata (fd.      FileName, "student", connstring);              }} public void TransferData (string excelfile, String sheetname,                    String connectionString) {DataSet ds = new DataSet (); try {//Get all data string strconn = "Provider = Microsoft.                           jet.oledb.4.0; "+" Data source= "+ Excelfile +"; "+" Extended Properties = Excel 8.0; ";                        OleDbConnection conn = new OleDbConnection (strconn); Conn.                        Open ();                        String strexcel = "";                           OleDbDataAdapter mycommand = null; Strexcel = string.                        Format ("select * from [{0}$]", sheetname);                        mycommand = new OleDbDataAdapter (Strexcel, strconn);                                  Mycommand.fill (ds, SheetName); If the target table does not exist then create the first behavior column header of the Excel file, starting from the second line all are data records string strSQL = String.   Format ("If NOT EXISTS"-select * from sysobjects where name = ' {0} ') create table {0} (", sheetname); SheetName is the table name foreach (System.Data.DataColumn C in DS. Tables[0]. Columns) {strSQL + = string.                           Format ("[{0}] varchar (255),", c.columnname);                                     } strSQL = Strsql.trim (', ') + ")";                           using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection (connectionString)) {sqlconn.                               Open (); System.Data.SqlClient.SqlCommand command = sqlconn.                               CreateCommand ();                               Command.commandtext = strSQL; Command.                               ExecuteNonQuery (); Sqlconn.                        Close (); }//Import data with bcp in the//excel fileThe order of the columns must be the same as the column order of the data table, because the data is imported from the second row of data in the Excel file, regardless of the structure of the data table, anyway, the first column of data is inserted into the first column of the Data table field, the second column of data is inserted into the second column field of the datasheet, It does not, by itself, determine which field in the corresponding data table the data is to be inserted using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClie Nt. SqlBulkCopy (connectionString)) {bcp.                               Sqlrowscopied + = new System.Data.SqlClient.SqlRowsCopiedEventHandler (bcp_sqlrowscopied); Bcp. BatchSize = 100;//The number of rows per transmission bcp. Notifyafter = 100;//The number of line BCP for the progress hint. DestinationTableName = sheetname;//target table bcp. WriteToServer (ds.                        Tables[0]); }} catch (Exception ex) {Sys Tem. Windows.Forms.MessageBox.Show (ex.                       Message); }}//Progress shows void Bcp_sqlrowscopied (object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e) {this.                       Text = E.rowscopied.tostring (); This.                   Update ();     }              }           }

Method Two:

Convert the Excel file to a DataTable and then loop through the records into the database table, which allows the programmer to choose which column of data to import into the data table's field

    Using System;      Using System.Collections.Generic;      Using System.ComponentModel;      Using System.Data;      Using System.Drawing;      Using System.Linq;      Using System.Text;      Using System.Windows.Forms;      Using System.Data.OleDb;           Using System.Data.SqlClient;              namespace Exceltosql {public partial class Form2:form {public Form2 ()              {InitializeComponent ();              } datatable dt = new DataTable (); String connstring = "Server = (local); UID = sa; PWD = sa;              Database = Db_test ";                   SqlConnection Conn;  private void Button1_Click (object sender, EventArgs e) {System.Windows.Forms.OpenFileDialog                  FD = new OpenFileDialog (); if (FD. ShowDialog () = = DialogResult.OK) {string fileName = fd.                      FileName;               Bind (FileName);   }} private void Bind (string fileName) {string strconn = "Prov ider=microsoft.jet.oledb.4.0; "+" Data source= "+ FileName +"; "+" Extended P Roperties= ' Excel 8.0; Hdr=yes;                  Imex=1 ' ";                  OleDbDataAdapter da = new OleDbDataAdapter ("SELECT * from [student$]", strconn);                  DataSet ds = new DataSet (); try {da.                      Fill (DS); DT = ds.                      Tables[0];                  This.dataGridView1.DataSource = DT; } catch (Exception err) {MessageBox.Show ("Operation failed! "+ Err.                  ToString ()); }}//Insert the DATAGRIDVIEW1 record into the database private void button2_click (object sender, even                  Targs e) {conn = new SqlConnection (connstring); Conn.             Open ();     if (DataGridView1.Rows.Count > 0) {DataRow dr = null; for (int i = 0; i < dt. Rows.Count; i++) {dr = dt.                          Rows[i];                      Inserttosql (DR); } conn.                      Close (); MessageBox.Show ("Import succeeded!                  "); } else {MessageBox.Show ("No data!                  "); }} The column names in the private void Inserttosql (DataRow dr) {//excel table and the column names in the database must be Corresponds to String name = dr["Studentname"].                  ToString (); string sex = dr["Sex"].                  ToString (); String no = dr["Studentidno"].                  ToString (); String major = Dr["major"].                  ToString ();                           String sql = "INSERT into student values ('" + name + "', '" + Sex + "', '" + No + "', '" + Major + "')"; SqlCommand cmd = new SqlCommand (SQL, conn); Cmd.              ExecuteNonQuery ();  }          }      }

C # Two ways to import an Excel data table into a SQL database