Bulk upload strategy for SQL server

.net c# sql sqlbulkcopy sql-server

Question

I'm using the following function to transfer data from a CSV file to a SQL table.

Is there a more effective approach?

Right now, a connection that hangs for a lengthy period worries me. hence need to be decreased

   public bool SaveProxyBulkUploadData(List<AddServerPError> saveBulkUploadData)
        {
            try
            {
                foreach (AddServerPError addServetData in saveBulkUploadData)
                {
                    DbCommand dbCmd = CitiScriptExecutionDB.GetStoredProcCommand("USP_HS_InsertProxyBulkUploadData");
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@groupid", DbType.String, addServetData.GroupId);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@proxyname", DbType.String, addServetData.ProxyName);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@proxytype", DbType.String, addServetData.ProxyType);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@sa_spoc_dl", DbType.String, addServetData.SA_SPOC_DL);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@assignmentgroup", DbType.String, addServetData.AssignmentGroup);
                    CitiScriptExecutionDB.AddInParameter(dbCmd, "@createdby", DbType.String, addServetData.CreatedBy);
                    CitiScriptExecutionDB.ExecuteNonQuery(dbCmd);
                }

                return true;
            }
            catch (Exception ex)
            {
                string msg = ex.Message + ex.StackTrace;
                throw;
            }
        }

Stored Method

CREATE PROCEDURE USP_HS_InsertBulkUploadData   
 @groupid  int,  
 @hostname varchar(50),   
 @type1 varchar(50),  
 @type2 varchar(50),  
 @createdby varchar(50)       
AS        
BEGIN       

 Insert into [dbo].[EUCUsecaseGroupInputParam]    
 (    
  GroupId,  
  HostName,    
  Type1,    
  Type2,     
  CreatedBy,     
  CreatedDate,     
  UpdatedBy,    
  UpdatedDate,    
  IsActive    
  )        
 Values(   
  @groupId,  
  @hostname,    
  @type1,    
  @type2,    
  @createdby,    
  GetDate(),    
  null,    
  null,    
  1    
  )       
END 
1
0
7/19/2018 8:10:11 AM

Expert Answer

Here, your best option is probablySqlBulkCopy it effectively sends raw TDS at the server.SqlBulkCopy has two different inputs:

  • DataTable
  • IDataReader

You then have the following three choices:

  • changing yourList<AddServerPError> to aDataTable manually
  • utilize a device likeFastMember to acquire anIDataReader more than your current list (an example of this is at the bottom of the project page: https://github.com/mgravell/fast-member) - on nuget: 50-50
  • Delete your current list and read the CSV directly as an alternative.IDataReader - "LumenWorks" existsCsvReader that has consistently had success with; the closest nuget link I can find is https://www.nuget.org/packages/LumenWorksCsvReader/ (albeit it has been expanded from the original).
2
7/19/2018 8:48:09 AM

Popular Answer

You could think about utilizing the free source software ETL Cinchoo to do mass CSV file uploads to databases.

Choice 1:

Put the CSV file directly into the database.

string connectionstring = @"#YOUR DB ConnectionString#";
using (SqlBulkCopy bcp = new SqlBulkCopy(connectionstring))
{
    using (var p = new ChoCSVReader("#YOUR CSV FILE#"))
    {
        bcp.DestinationTableName = "#TABLENAME#";
        bcp.EnableStreaming = true;
        bcp.BatchSize = 10000;
        bcp.BulkCopyTimeout = 0;
        bcp.NotifyAfter = 100;
        bcp.SqlRowsCopied += delegate (object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine(e.RowsCopied.ToString("#,##0") + " rows copied.");
        };
        bcp.WriteToServer(p.AsDataReader());
    }
}

Option 2:

If the CSV load has already been completed and is output asList<AddServerPError> you may still upload them to the database as shown below.

List<AddServerPError> objs = # Your input objects #;

string connectionstring = @"#YOUR DB ConnectionString#";
using (SqlBulkCopy bcp = new SqlBulkCopy(connectionstring))
{
    bcp.DestinationTableName = "#TABLENAME#";
    bcp.EnableStreaming = true;
    bcp.BatchSize = 10000;
    bcp.BulkCopyTimeout = 0;
    bcp.NotifyAfter = 100;
    bcp.SqlRowsCopied += delegate (object sender, SqlRowsCopiedEventArgs e)
    {
        Console.WriteLine(e.RowsCopied.ToString("#,##0") + " rows copied.");
    };
    bcp.WriteToServer(objs.AsDataReader());
}


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow