Insert record on a remote SQL Server using SqlBulkCopy in ASP.NET MVC

Question

I have been managing supplier information stored in database on remote server from my ASP.NET MVC web application.

I can do normal CRUD operations successfully.

I have also a functionality in this web application to insert supplier information in database on remote server using SqlBulkCopy.

using (oSqlBulkCopy = new SqlBulkCopy(pConnectionString, SqlBulkCopyOptions.Default))
{
    string[] vColumns = { "vSupplierID", "vUniqueRegRefNumber", "vIsSupplierActive", "vSupplierInactiveReason", "vSupplierInactiveDate", "vIsAssociated", "vSupplierTypeCode", "vSupplierTypeName", "vSupplierSubTypeCode", "vSupplierSubTypeName", "vGovernmentTypeCode", "vGovernmentTypeName", "vIndustryClassificationCode", "vIndustryClassificationName", "vCountryOfOriginCode", "vCountryOfOriginName", "vLegalName", "vTradingName", "vIDTypeCode", "vIDTypeName", "vSAIDNumber", "vForeignIDNumber", "vForeignPassportNumber", "vWorkPermitNumber", "vSACompanyNumber", "vRegistrationDate", "vForeignCompanyRegNo", "vSATrustRegNo", "vForeignTrustRegistrationNumber", "vDateOperationStarted", "vHaveBankAccount", "vBusinessStatusCode", "vBusinessStatusName", "vBusinessStatusLastVerificationDate", "vCreatedDate", "vEditDate", "vBulkFileBackupName" };

    string vTableName = "KNO_DSM_RT_Supplier_Temporary";

    Mapping.MapColumns(oSqlBulkCopy, vTableName, vColumns);

    oSqlBulkCopy.BatchSize = 1;
    oSqlBulkCopy.WriteToServer(this);
}

On first attempt: the supplier information being inserted have been downloaded from a third party web service. I have following errors during insert/update supplier information

using SqlBulkCopy;

On 2nd attempt

Error occurred during Supplier Identification Import: A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable) Supplier Identification not imported for current supplier

On third attempt

Error occurred during Supplier Identification Import: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Supplier Identification not imported for current supplier

On fourth attempt

Error occurred during Supplier Identification Import: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Supplier Identification not imported for current supplier

Sometimes it generates

Remote server returned an error. Bad Request(400)

Please help...

Alexei - check Codidact · Accepted Answer · 2020-04-20 18:26:04Z

1

The fact that you receive various errors on subsequent is strange, but I notice something that kind of defeats the purpose of bulk insert:

oSqlBulkCopy.BatchSize = 1;

This property should normally have a much bigger value to actually reduce the roundtrips.

Not sure, but this might lead to trouble due to big amount of queries issued against the server.

answered Apr 20, 2020 at 18:26

Alexei - check Codidact

23.2k22 gold badges159 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert record on a remote SQL Server using SqlBulkCopy in ASP.NET MVC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related