CODEWITHSUNDEEP
c#sql-server
jurajvt
jurajvt

Reputation: 144

Get context connection and use it as a connection in other place

I am working in an environment in which enterprise host application is calling client applications with implemented COM interface via ProgID. This is the way how programmers of host application allow to enhance application with plugins.

Host app <-> COM interface <-> Client app

The client app can use methods from provided COM interface.

One of that method is RunSqlQuery(rawSqlQuery). It has possibilities to fully communicate with SQL within the scope of a logged-in user of the host application (based on connection authentication type it could be Windows or SQL user).

What I am looking for is a way to get a current connection context which was started from the host application and use it as a connection for DbContext.

Why?

  1. I want to use some ORM for data binding, parameters binding, change tracker, etc.
  2. I can use my own explicit SQL connection for it, but with my own SQL connection, I don't reach local temporary tables, which are created within the host application on its start. Host application heavily rely on stored procedures, which in turn operates with that mentioned local temporary tables
  3. In the case of using explicit SQL connection (main SQL connection holds host application), I need to manage connection credentials in the connection string (when it is not allowed SQL server authentication mixed mode)

For clarity, where is the problem, something similar is well known in the SQL CLR world:

In SQL CLR UDF there is possibility to use context connection=true as a connection string for instance of SqlConnection (more here).

In the link above they wrote:

The connection is in a different database session than the stored procedure or function, it may have different SET options, it is in a separate transaction, it does not see your temporary tables, and so on.

And in other places in that link:

You probably want the stored procedure or function to execute in the context of that connection, along with its transaction, SET options, and so on. This is called the context connection.

Upvotes: 12

Views: 1513

Answers (1)

Mahri Ilmedova
Mahri Ilmedova

Reputation: 340

If you work in asp.net core you just need to configure the connectionstring in your appsettings.json file. Your connectionstring settings are usually shown in the host. If you cannot find it just ask the online consultant about your connection string. This is how I solved my problem with connection when I had quite similar problems with connection

appsettings.json

"ConnectionStrings": { DefaultConnection": "Here_is_the_connectionstringSettings_that_is_given_by_your_host"}

Upvotes: 3

Related Questions