SqlBulkCopy and SQL Server trigger Issues

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;
}

Without using SqlBulkCopyOptions.FireTriggers , everything works but when I add this, I am getting error — atc, Dec 06 '17 at 14:07

user6144226 · Accepted Answer · 2017-12-06T14:27:54.583

1

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);
}

edited Dec 06 '17 at 14:27

answered Dec 06 '17 at 14:14

user6144226

613
1
8
15

Should I change my connection string ? – atc Dec 06 '17 at 14:17
@aniltc - essenitally you'll either need to use the overload with transaction(start one) or drop the using that creates the connection – user6144226 Dec 06 '17 at 14:23

score 0 · Answer 2 · answered Dec 06 '17 at 14:26

0

I have done following changes.It works now

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

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

answered Dec 06 '17 at 14:26

atc

621
8
31

But notice that *you don't use* `cn` after opening it, so it's entirely pointless to have that extra `SqlConnection` object at all. – Damien_The_Unbeliever Dec 06 '17 at 14:29

SqlBulkCopy and SQL Server trigger Issues

2 Answers2