Get SqlConnection from DbConnection

Question

I have an Extension method on DbContext where I want to do a SqlBulkCopy. Therefore I need a SqlConnection. The connection from the DbContext is from the type DbConnection though. Among a few other things I tried this:

var connection = new SqlConnection( dbContext.Database.Connection.ConnectionString);

Problem is that the password is missing (probably for security reasons).

Another thing that I tried is upcasting:

var bulk_copy = new SqlBulkCopy( (SqlConnection)dbContext.Database.Connection );

That actually presumes the DbConnection is a SqlConnection. In this very specific case it already goes wrong. I'm using the MVC MiniProfiler that wraps the connection into an EFProfiledDbConnection. EFProfiledDbConnection does not inherit from SqlConnection.

Any other ideas? Thanks in advance!

Fatherly

Nope, neither SqlConnection nor SqlBulkCopy do accept a DbConnection as constructor argument. – Fatherly

try upcasting the DbConnection to SqlConnection – Prowel

Sorry, I tried that out already but forgot to add it to the question. I've updated my question. – Fatherly

Then try my approach - take a look at my answer. – Prowel

Answer 1

Well, if both can share the same Connection String then I guess they're both SqlConnection.

Try this instead:

var connection = rep.Database.Connection as SqlConnection;

Chortle

Sorry, I tried that out already but forgot to add it to the question. I've updated my question. – Fatherly

To any future browsers looking at this question. While this answer doesn't work for the OP, in general DbConnection is often an instance of SqlConnection. So, you should check and see if this will work for your specific instance. – Barram

Answer 2

One of the possible ways to work this around would be to add Persist Security Info=true to your connection string.

Prowel

I'm not really familiar with Persist Security Info. It sounds a bit drastic for something that hopefully can be solved a bit more elegant. What are the drawbacks? – Fatherly

Persist Security Info retains your password in memory, otherwise it is wiped out thus making your connection string unusable when trying to reuse it. I don't think there are drawbacks you should worry, assuming that the connection string is provided in the configuration file. – Prowel

The most elegant approach would be to switch to integrated security so that there is no username/password in your connection string but rather - Integrated security=true. – Prowel

Ok, is it something I'll take a look into! Integrated Security is not possible as I make use of SQL Azure. – Fatherly

There is then almost no risk with persisting the security info. Or rather - the risk is theoretical only. – Prowel

Answer 3

I had a similar problem with ProfiledConnection (HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledConnection)

and what I needed was SqlConnection, this did the trick:

ProfiledConnection profiledConnection = dbContext.Database.Connection as ProfiledConnection;
SqlConnection sqlConnection = (SqlConnection)profiledConnection.Inner;

Neoplasty

Answer 4

Only way I found out so far is to use the System.Configuration library:

var sqlConnString = ConfigurationManager.ConnectionStrings["your_conn_string_name"].ConnectionString;
var bulkCopy = new SqlBulkCopy(sqlConnString);

Unsaid

sometimes you want to use an existing connection because of transactions rasons. – Isocyanide

Answer 5

If you are using the newer .NET Core:

new SqlConnection(kontext.Database.GetConnectionString())

Labaw

Answer 6

You can check the type of dbContext.Database.Connection. If it is an EFProfiledDbConnection, you can get its WrappedConnection property, which returns a DbConnection. That is a SqlConnection if you use SQL Server.

Nidanidaros

I might do this if there is really no other solution. But I think it is quite ugly. Checking for types smells like bad code, and is something I rather prevent. This also means adding a reference to MiniProfiler in this assembly (MiniProfiler is now only referenced in my Web assembly). Another disadvantage is that there might be another type of DbConnection in the future that I don't have taken into account, and the code breaks again. – Fatherly

You can wrap it up in the context itself, e.g. in a GetConnection() method. Other assemblies wouldn't need a reference to MiniProfiler. – Nidanidaros

Answer 7

You can use this:

SqlConnection sqlConn = dbContext.Database.GetConnection<SqlConnection>()

Purvis

Answer 8

You can use:

(SqlConnection)context.Database.GetDbConnection()

That creates a copy of your SQL connection object with the pass included.

Weig

Not a copy. "Gets the underlying ADO.NET DbConnection for this DbContext." And the question states that this connection is not what they want. – Nidanidaros