Example code in C # inserting data from a DataTable into a database at a time

This article mainly introduces the method of C # to insert data from DataTable into database at once, which has certain reference value and is interested to understand.

Now the actual situation is this:

Customer has a punch machine, the information of the employee clock-out is stored in the Access database of the punch machine, and now the customer introduces a new management system, which needs to synchronize the data in the Access database to the SQL Server database, and the data accumulates 40多万条 because of the time is relatively long.

Software Features:

Select the Access database file, fill in the IP address of the destination SQL Server database, and start the synchronization.

Implementation method:

1. First put the data in the Access database into a DataTable

Database connection strings in the configuration file

<connectionStrings>  <add name= "oleconstr" connectionstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "/>    <add name=" sqlconstr "connectionString =" server=tiantiankaixing;database= new database; trusted_ Connection=sspi "/> </connectionStrings>

Encapsulates the method of reading Access database data into a DataTable

public static string oleconstr = configurationmanager.connectionstrings["Oleconstr"]. ConnectionString; public static DataTable olegetdatatable (String sql, String FilePath)    {            string a = Oleconstr + FilePath;      using (OleDbConnection conn = new OleDbConnection (a))      {        using (OleDbDataAdapter da = new OleDbDataAdapter (SQL, CO nn)        {          try          {            Conn. Open ();            DataTable dt = new DataTable ();            Da. Fill (DT);            return dt;          }          catch (Exception ex)          {            throw ex;          }          Finally          {            if (conn. state = = ConnectionState.Open)              Conn. Close ();}}}    

Read target Access database to DataTable

String sql = "Select Id,time from CheckInOut";D atatable dt = achelper.olegetdatatable (sql, @ "F:\project\tiantiankaixing\ Admin.mdb ");

2. How to encapsulate BULK INSERT data SQL Server data

public static void Datatabletosqlserver (DataTable dt,string connectstring)    {      string connectionString = ConnectString;      using (SqlConnection destinationconnection = new SqlConnection (connectionString))      {        Destinationconnection.open ();        using (SqlBulkCopy bulkcopy = new SqlBulkCopy (destinationconnection))        {          try          {            Bulkcopy.destinationtablename = "CheckInOut";//the table name of the table to be inserted            bulkcopy.batchsize = dt. Rows.Count;            BULKCOPY.COLUMNMAPPINGS.ADD ("id", "id");//Map Field name DataTable column name, database corresponding column name             bulkCopy.ColumnMappings.Add ("Time", " Time ");                        Bulkcopy.writetoserver (DT);            System.Windows.Forms.MessageBox.Show ("Insert succeeded");          }          catch (Exception ex)          {            Console.WriteLine (ex. Message);          }          Finally          {                      }        }      }    }

3. Call the Datatabletosqlserver () method

String Localcon = "Server=tiantiankaixing;database=test;trusted_connection=sspi"; Entity.datatabletosqlserver (DT, Localcon);

You can insert all the data in the DataTable into the database

Attached: SqlBulkCopy's simple use method

public void Test () {String connectionString = "server=tiantiankaixing;database= new database; Trusted_connection=sspi"; using (SqlConnection sourceconnection = new SqlConnection (connectionString)) {Sourceconn Ection.        Open ();        Gets the total number of table rows read SqlCommand Commandrowcount = new SqlCommand ("SELECT count (*) from student", sourceconnection);                Long Countstart = System.Convert.ToInt32 (Commandrowcount.executescalar ());        Use SqlDataReader to read the source data SqlCommand Commandsourcedata = new SqlCommand ("SELECT * from Student", sourceconnection);        SqlDataReader reader =commandsourcedata.executereader (); testing, bulk inserting data from one table to another table//real life will certainly not use (SqlConnection destinationconnection =new SqlConnection (ConnectionS          Tring)) {Destinationconnection.open (); Create a SqlBulkCopy object//Specify the target table name//Specify the number of rows to insert//specify the corresponding mapping using (SqlBulkCopy bulkcopy =new Sql BulkCopy (Destinationconnection)) {bulkcopy.destinationtablename = "test";            Bulkcopy.batchsize = 1;            BULKCOPY.COLUMNMAPPINGS.ADD ("Data source column name", "Target column name");            try {bulkcopy.writetoserver (reader); } catch (Exception ex) {Console.WriteLine (ex.            Message); } finally {reader.            Close (); }          }        }      }    }