Explain in detail the method of inserting the data in DataTable into the database once by C
- 2021-12-04 10:55:27
- OfStack
The actual situation now is as follows:
The customer has a punch-in machine, and all the information of employees' punch-in is stored in the Access database of the punch-in machine. Now the customer has introduced a new management system, which needs to synchronize the punch-in data in the Access database to the SQL Server database. Due to the long time, more than 400,000 pieces of data have been accumulated.
Software functions:
Select the Access database file, fill in the IP address of the target SQL Server database, and start synchronization.
Implementation method:
1. First, store the data to be imported in Access database into DataTable
Database connection string in 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>
Method of encapsulating and reading Access database data into 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, conn))
{
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 the target Access database to Datatable
string sql = "select Id,Time from checkinout";
DataTable dt = AcHelper.OleGetDataTable(sql, @"F:\project\tiantiankaixing\admin.mdb");
2. Method of encapsulating batch insertion 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";// Table name of the table to insert
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.ColumnMappings.Add("ID", "ID");// Mapped field name DataTable Column name , Database Corresponding column name
bulkCopy.ColumnMappings.Add("TIME", "TIME");
bulkCopy.WriteToServer(dt);
System.Windows.Forms.MessageBox.Show(" Successful insertion ");
}
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 DataTable into the database
Attachment: Simple use of SqlBulkCopy
public void Test()
{
string connectionString = "server=tiantiankaixing;database= New Database ;trusted_connection=sspi";
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.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 Read source data
SqlCommand commandSourceData = new SqlCommand("select * from student", sourceConnection);
SqlDataReader reader =commandSourceData.ExecuteReader();
// For testing, take the data from 1 Batch insertion of tables into another 1 Table
// Certainly not in real life
using (SqlConnection destinationConnection =new SqlConnection(connectionString))
{
destinationConnection.Open();
// Create 1 A SQlBulkCopy Object
// Specify the target table name
// Specify the number of rows to insert
// Specify the corresponding mapping
using (SqlBulkCopy bulkCopy =new SqlBulkCopy(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();
}
}
}
}
}