Detailed explanation of C# sample code for inserting data in DataTable into the database at one time

This article mainly introduces the detailed C# method of inserting the data in the DataTable into the data library at one time. It has certain reference value. Those who are interested can learn more.

The actual situation now is this:

The customer has a clock-in machine, and all employee clock-in information is stored in the Access database of the clock-in machine. Now the customer has introduced a new management system , it is necessary 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 then start synchronization.

Implementation method:

1. First store the data to be imported from the Access database into the DataTable

Database connection in the configuration fileString

<connectionStrings>
  <add name="oleConStr" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="/>
  
  <add name="sqlConStr" connectionString ="server=tiantiankaixing;database=新建数据库;trusted_connection=sspi"/>
 </connectionStrings>

Encapsulate the method of 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 into Datatable

string sql = "select Id,Time from checkinout";
DataTable dt = AcHelper.OleGetDataTable(sql, @"F:\project\tiantiankaixing\admin.mdb");

2. Encapsulate batch insertion Data SQL Server data method

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";//要插入的表的表名
            bulkCopy.BatchSize = dt.Rows.Count;
            bulkCopy.ColumnMappings.Add("ID", "ID");//映射字段名 DataTable列名 ,数据库 对应的列名 
            bulkCopy.ColumnMappings.Add("TIME", "TIME");
            
            bulkCopy.WriteToServer(dt);
            System.Windows.Forms.MessageBox.Show("插入成功");
          }
          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);

to insert all the data in the DataTable into the database

Attachment: Simple usage of SqlBulkCopy

public void Test()
    {
      string connectionString = "server=tiantiankaixing;database=新建数据库;trusted_connection=sspi";
      
      using (SqlConnection sourceConnection =
            new SqlConnection(connectionString))
      {
        sourceConnection.Open();
        //获取读取的表总行数
        SqlCommand commandRowCount = new SqlCommand("select count(*) from student",sourceConnection);
        long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
        

        //使用SqlDataReader读取源数据
        SqlCommand commandSourceData = new SqlCommand("select * from student", sourceConnection);
        SqlDataReader reader =commandSourceData.ExecuteReader();

        //测试用，把数据从一个表批量插入到另一个表
        //现实生活中肯定不会
        using (SqlConnection destinationConnection =new SqlConnection(connectionString))
        {
          destinationConnection.Open();

          //创建一个SQlBulkCopy对象
          //指定目标表名
          //指定要插入的行数
          //指定对应的映射
          using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destinationConnection))
          {
            bulkCopy.DestinationTableName ="test";
            bulkCopy.BatchSize = 1;
            bulkCopy.ColumnMappings.Add("数据源列名","目标列名");

            try
            {
              
              bulkCopy.WriteToServer(reader);
            }
            catch (Exception ex)
            {
              Console.WriteLine(ex.Message);
            }
            finally
            {
              reader.Close();
            }
          }
        }


      }
    }

The above is the detailed content of Detailed explanation of C# sample code for inserting data in DataTable into the database at one time. For more information, please follow other related articles on the PHP Chinese website!