SqlBulkCopy and SQL Server trigger Issues

Question

SqlBulkCopy and SQL Server trigger Issues

asp.net c# sqlbulkcopy sql-server

Question

I have following code in my application. I use SqlBulkCopy to upload Excel data into a SQL Server table. Now, I have a trigger on that table. I have changed my existing code like this but I am getting an error.

The error is

Argument 1: cannot convert from 'System.Data.SqlClient.SqlConnection' to 'string'

I changed the code as

oSqlBulk = new SqlBulkCopy("cn", SqlBulkCopyOptions.FireTriggers);

Then I am getting an error

Format of the initialization string does not conform to specification starting at index 0.

Please check my code:

public static string ExecuteBulkCopy(string connectionString, string TableName, string Paths)
{
    SqlBulkCopy oSqlBulk = null;
    string error = null;

    OleDbConnection myExcelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " +
                       "Data Source=" + Paths +
                       ";Extended Properties=Excel 12.0;");

    try
    {
        myExcelConn.Open();

        // GET DATA FROM EXCEL SHEET.
        OleDbCommand objOleDB = new OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn);

        // READ THE DATA EXTRACTED FROM THE EXCEL FILE.
        OleDbDataReader objBulkReader = null;
        objBulkReader = objOleDB.ExecuteReader();

        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            // cn.Open();
            oSqlBulk = new SqlBulkCopy(cn,SqlBulkCopyOptions.FireTriggers);

            oSqlBulk.DestinationTableName = TableName; // TABLE NAME.
            oSqlBulk.WriteToServer(objBulkReader);
            // return 1;

            //call the overload that takes a connection in place of the connection string
            // return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
        }
    }
    catch (Exception ex)
    {
        error = ex.Message;
    }
    finally
    {
        oSqlBulk.Close();
        oSqlBulk = null;
        myExcelConn.Close();
        myExcelConn = null;
    }

    return error;
}

aniltc

Popular Answer

I have done following changes.It works now

 using (SqlConnection cn = new SqlConnection(connectionString))
                {

                    cn.Open();
                    oSqlBulk = new SqlBulkCopy(connectionString,SqlBulkCopyOptions.FireTriggers);

aniltc

View more on Stack Overflow

user6144226 · Accepted Answer · 12/6/2017 2:27:54 PM

If you check the valid constructors for SqlBulkCopy: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.sqlbulkcopy(v=vs.110).aspx

You'll notice that the overload using SqlBulkCopyOptions does not accept SqlConnection directly. Just the ConnectionString

Since SqlBulkCopy implements IDisposable

You should replace the SqlConnection entirely:

using (var oSqlBulk = new SqlBulkCopy(connectionString,SqlBulkCopyOptions.FireTriggers))
{
    oSqlBulk.DestinationTableName = TableName; 
    oSqlBulk.WriteToServer(objBulkReader);
}

SqlBulkCopy and SQL Server trigger Issues

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator