The problem I am having is that in the Web.Config, I have a connection string which db.database is using, this works fine, I am able to retrieve data from the database no problem, however the problem I am having is when I try to use SqlBulkCopy:
using (SqlBulkCopy bulk = new SqlBulkCopy(db.Database.Connection.ConnectionString))
{
bulk.DestinationTableName = datatable.TableName;
bulk.WriteToServer(datatable);
}
I am using the exact same connection that the entity framework is using but it is failing and I get the error message
Login failed for user 'DatabaseName'.
I can't seem to figure out why it's failing, I Googled around and it says it's an authentication issue on the server / database side, but why would it work for db.database and not when using SqlBulkCopy?
Please find the below it should help you:
try using a connection directly rather then entify framework.
string csDestination = "put here connection string to database";
using (SqlConnection destinationConnection = new SqlConnection(csDestination)) using (SqlBulkCopy bulk = new SqlBulkCopy(destinationConnection)) { bulk.DestinationTableName = datatable.TableName; bulk.WriteToServer(datatable); }