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;
}
Accepted Answer
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);
}
Popular Answer
I have done following changes.It works now
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
oSqlBulk = new SqlBulkCopy(connectionString,SqlBulkCopyOptions.FireTriggers);