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();
            }
          }
        }


      }
    }

Related articles: