0

I don't really speak English. I would appreciate it if you could understand my situation. Is there a way to process multiple queries in batches using bulkcopy?

Current using Query Method :

private string SetQueryData()
{
   StringBuilder query = new StringBuilder();

   query.Append("IF EXISTS (SELECT DgpDate FROM TestBulkCopy WHERE DgpDate = '20210125' AND DgpCode = 'C01')");
   query.Append("UPDATE TestBulkCopy SET Active_Qty = 20");
   query.Append("WHERE DgpDate = '20210125' AND DgpCode = 'C01'");
   query.Append("ELSE INSERT INTO TestBulkCopy(CorpId, DgpDate , DgpCode, DgpName, Active_Qty)");
   query.Append("VALUES('10', '20210125', 'C01', 'TEST1', '20')");

   return query.ToString();
}

Current using bulkcopy

private void WriteLargeBulkQuery()
{
    using (SqlConnection sourceConnection = new SqlConnection(m_ConnectionString))
    {
         sourceConnection.Open();
         SqlCommand commandSourceData = new SqlCommand();
         commandSourceData.Connection = sourceConnection;
         commandSourceData.CommandType = CommandType.Text;

         commandSourceData.CommandText = SetQueryData();
         SqlDataReader reader = commandSourceData.ExecuteReader();

         using (SqlBulkCopy bulkCopy = new SqlBulkCopy(m_ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
         {
             bulkCopy.DestinationTableName = "TestBulkCopy";
             bulkCopy.BatchSize = 1000;
             bulkCopy.WriteToServer(reader);
         }
         reader.Close();
    }
}

I want to batch-process at once in bulkcopy by putting multiple query statements in a string array. I know that I lack. However, it was too difficult to solve, so I would like to seek help from programmers around the world.

Maicto
  • 1
  • Side note : are you aware that [C# provides multiline verbatim strings](https://stackoverflow.com/a/1100265/588868)(and thus would avoid using the stringbuilder as you did. – Steve B Jan 25 '21 at 14:53
  • In short: I guess you can't! You need one query and one destination table per call. If you want to populate multiple tables, you will heve to execute one bulk copy per table. However, this can be achieved by putting the corresponding info into a data table and loop over it. If you want to populate only one table, you should handle this within the query either by UNION / UNION ALL or by modifying the query at all. – Tyron78 Jan 25 '21 at 15:44

0 Answers0