DataConnector | ComponentOne
ADO.NET provider for Snowflake / Batch Processing
In This Topic
    Batch Processing
    In This Topic

    The ADO.NET Provider for Snowflake facilitates the execution of multiple operations with bulk data through C1SnowflakeDataAdapter. The process can be improved by executing many smaller batch requests. The size of each batch can be controlled by setting the CSnowflakeDataAdapter's UpdateBatchSize property to a positive integer.

    Steps to perform batch processing:

    1. In C1SnowflakeCommand class objects, define the custom SQL statements.
    2. Set the UpdatedRowSource property of the C1SnowflakeCommand object to "UpdateRowSource.None".
    3. Assign the C1SnowflakeCommand class objects to the C1SnowflakeDataAdapter and add the parameters to the command.
    4. Invoke the C1SnowflakeDataAdapter's Update method, passing a DataSet or DataTable containing the changes.

    The provider translates all SQL queries in the batch into a single request. Below are examples of different operations with bulk data.

    Bulk Insert

    The following code example creates a simple bulk insert:

    C#
    Copy Code 
    public void BulkInsertInto()
   {            
       var sql = "Insert Into ORDER_DETAILS (ORDER_ID,PRODUCT_ID, UNIT_PRICE, DISCOUNT, STATUS_ID)
VALUES (1,40, 355,88,66),(1,40, 355,88,66),(1,40, 355,88,66),(1,40, 355,88,66),(1,40, 355,88,66);";
       using (var con = new C1SnowflakeConnection(connectionString))
       {
           con.Open();
           var command = con.CreateCommand();
           command.CommandText = sql;
           var result = command.ExecuteNonQuery();
           Console.WriteLine("Bulk insert successful! \n\n");
       }
   }

    Bulk Insert With Select

    Bulk insert with select allows you to efficiently insert data from one table into another using the result set of a SELECT query. The following code example creates a bulk insert with select:

    C#
    Copy Code 
    public void BulkInsertWithSelect()
  {     
      var sql = "INSERT INTO ORDER_DETAILS SELECT * FROM ORDER_TEST";    
      using (var con = new C1SnowflakeConnection(SnowflakeConnection))
      {
          con.Open();
          var command = con.CreateCommand();
          command.CommandText = sql;
          var result = command.ExecuteNonQuery();
          Console.WriteLine("Bulk insert successful! \n\n");
      }
  }

    Batch Insert

    The following code example creates a Batch Insert:

    C#
    Copy Code 
    public void BatchInsertDataAdapter()
  {
      using (C1SnowflakeConnection conn = new C1SnowflakeConnection(connectionString))
      {
          string sqlQuery = "Select * from ORDER_DETAILS";
          conn.Open();
     
          C1SnowflakeDataAdapter adapter = new C1SnowflakeDataAdapter(conn, sqlQuery);
          DataTable dataTable = new DataTable();
          adapter.Fill(dataTable);
          adapter.InsertCommand = new C1SnowflakeCommand(conn);
          adapter.InsertCommand.CommandText = "Insert Into ORDER_DETAILS
          (ORDER_ID,PRODUCT_ID, UNIT_PRICE, DISCOUNT,
          STATUS_ID) VALUES (@ORDER_ID,@PRODUCT_ID, @UNIT_PRICE, @DISCOUNT, @STATUS_ID)";
          adapter.InsertCommand.Parameters.Add("@ORDER_ID", "ORDER_ID");
          adapter.InsertCommand.Parameters.Add("@PRODUCT_ID", "PRODUCT_ID");
          adapter.InsertCommand.Parameters.Add("@UNIT_PRICE", "UNIT_PRICE");
          adapter.InsertCommand.Parameters.Add("@DISCOUNT", "DISCOUNT");
          adapter.InsertCommand.Parameters.Add("@STATUS_ID", "STATUS_ID");
          adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
          //Insert new row
          DataRow customerRow1 = dataTable.NewRow();
          customerRow1["ORDER_ID"] = 1;
          customerRow1["PRODUCT_ID"] = 2;
          customerRow1["UNIT_PRICE"] = 2;
          customerRow1["STATUS_ID"] = 2;
          customerRow1["DISCOUNT"] = 2;
          dataTable.Rows.Add(customerRow1);
          //Insert new row
          DataRow customerRow2 = dataTable.NewRow();
          customerRow2["ORDER_ID"] = 1;
          customerRow2["PRODUCT_ID"] = 2;
          customerRow2["UNIT_PRICE"] = 2;
          customerRow2["STATUS_ID"] = 2;
          customerRow2["DISCOUNT"] = 2;
          dataTable.Rows.Add(customerRow2);
          //Insert new row
          DataRow customerRow3 = dataTable.NewRow();
          customerRow3["ORDER_ID"] = 1;
          customerRow3["PRODUCT_ID"] = 2;
          customerRow3["UNIT_PRICE"] = 2;
          customerRow3["STATUS_ID"] = 2;
          customerRow3["DISCOUNT"] = 2;
          dataTable.Rows.Add(customerRow3);
          //Insert new row
          DataRow customerRow4 = dataTable.NewRow();
          customerRow4["ORDER_ID"] = 1;
          customerRow4["PRODUCT_ID"] = 2;
          customerRow4["UNIT_PRICE"] = 2;
          customerRow4["STATUS_ID"] = 2;
          customerRow4["DISCOUNT"] = 2;
          dataTable.Rows.Add(customerRow4);
          //Insert new row
          DataRow customerRow5 = dataTable.NewRow();
          customerRow5["ORDER_ID"] = 1;
          customerRow5["PRODUCT_ID"] = 2;
          customerRow5["UNIT_PRICE"] = 2;
          customerRow5["STATUS_ID"] = 2;
          customerRow5["DISCOUNT"] = 2;
          dataTable.Rows.Add(customerRow5);
          //Insert new row
          DataRow customerRow6 = dataTable.NewRow();
          customerRow6["ORDER_ID"] = 1;
          customerRow6["PRODUCT_ID"] = 2;
          customerRow6["UNIT_PRICE"] = 2;
          customerRow6["STATUS_ID"] = 2;
          customerRow6["DISCOUNT"] = 2;
          dataTable.Rows.Add(customerRow6);
          //Update database
          adapter.UpdateBatchSize = 2;
          var affectedRows = adapter.Update(dataTable);
          Console.WriteLine("Batch insert successful! \n\n");
      }
  }