.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Best practice on closing SQL CLR context connection in between calls

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :


In SQL CLR, best practice for queries is to use a context connection that reuses the already existing connection to the db.  There's also a restriction that you can only have one open context connection at a time per execution environment (i.e. per sql clr stored proc / function that's running; a nested sql clr function called from within another sql clr function has its own separate execution environment, hence has its own open context connection).


If I have a relatively long-running sql clr trigger:

  1. Is it best practice to create it in a singleton wrapper and keep it throughout execution, or to create a new SqlConnection for each use in a using statement? 
  2. Is it best practice to explicitly close the context connection after each call, or to leave it open and just have a check for whether it's already open before opening another one?   Note: If I create a new SqlConnection each time in a using statement and open it and leave it open, and then there's a nested using statement inside the main one, I can't open the connection or it will fail, so I must ALWAYS have a check for whether the connection is open before opening it, even though this looks odd because I just created it as a new SqlConnection)


View Complete Post

More Related Resource Links

Context Connection problem in SQLCLR

I am using context connection in the sqlclr (and there are reasons to do so). While a datareader is under execution that uses the context connection, I need another database read passing one of values being read from the datareader. Since I cann't use the context connection for this second read (as context connection is already in use), how can I create a second connection to be used with this second read. I cannot create the hardcoded connections string for the second connection as the database is dynamically selected. The database is same as used by the context connection. I can use the server=localhost though, but cannot hardcode the database name as the database can be different in different sqlclr proc call. However the database is same as used by the context connection. (If I somehow get the database name from the context connection then I can use it to create another connection, but I am expecting an even better solution)   Any solution to this problem? Will be very thankful.

Closing a WCF connection takes a long time when using SSL


I have a very puzzling problem closing WCF connections. We utilize several different protocols for different customers. The most common is net.tcp. We now have a customer that requires SSL encryption of their WCF calls. It does seem to work, but exhibits some strange behavior when we close the connection.

Closing the connection takes a long time. This could be caused by any number of problems, but the strange thing is that the more data that was transmitted on the connection, the longer it takes to close. Connections that get a small amount of data close reasonably fast, but if a large amount of data was transmitted, closing the connection can take as long as actually retrieving the data. It almost seems as if the data is being retransmitted back to the server for verification before the connection is closed.

We are using reliable sessions and Binary over HTTP protocol.

implications of not closing an open connection declared locally


 Hello All.


I was wondering about something.  I'm supporting a .NET C# application which has a number of database connections declared locally as IDbConnection with MS SQL Server and NHibernate.  These connections are declared locally and not explicitly closed after a specific operation is performed.  Are these connections automatically closed after the local method is executed, or could these connections be floating around somewhere?  We're having semaphore max out problems, and I'm trying to see if this may be the culprit.  I'm doubtful, as I believe these connections would automatically close, but thought I'd throw out hte possibility.






ControllerBase - connection string MVC best practice


I build my first project in MVC & EF.
In the both practices (MVC / EF) it's my first use, and I try understanding the best way to use them in my project.
I build a BaseController and restart the EF and the Log4Net on the constructor.

All other controller that I need use the DB I inherit from this BaseController.
Of course I want this application to be effective and built correctly.

how to write logs without opening and closing the Oracle DB Connection


I am developing logging application with asp.net & vb.net & Oracle

At present I'm using this mechanism to write logs

Method1 Connect (
Conn.open ()
Close () & Dispose ()
Method2 WriteLogs (
Call Connect ()
If flag=True
                Write logs into Oracle Database
                Write logs into Text File

The problem with above procedure is, I'm using "Method2 WriteLogs" more than 2000 locations in my program. So each time it's opening & closing the DB connections. It's slows the system.
I want to use Method2 without using the Method1
 Please advice me

Error "Could not find server 'System' in sys.servers" with non-context connection in CLR function


When trying to open a non-context connection in a CLR scalar valued function I am receiving the Exception:

Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers

I encounter this problem on two different SQL Server 2005 servers. The error seems strange to me since I am not using linked servers.

Here is an extract from my function:



class LimitSystemFunctions
  public static<

EF 4.1 connection string code first and object context


I have a question about using  an existing sql express(from a web host) with entity framework 4.1) and using an existing database  

Does this map directly using the following method. I posted this question in the msdn site and I really did not get a very good response so I hope someone helps out. my question Is what is the best way to map to an existing database on a web host    

  http://blogs.msdn.com/b/adonet/archive/2010/12/06/ef-feature-ctp5-code-first-walkthrough.aspx here is using a sql  default constructor

http://msdn.microsoft.com/en-us/library/bb896291.aspx you have to pre-configure entity framework here ....

I was wondering is this the way you do it using objectcontext

Public class  Blog context: Db context

public Blogcontext (Db connection connection)


Creating Sites/Lists/List Items under Current User Context in SharePoint By Using SPUserToken

Usually we do this by performing the action under RunWithElevatedPrivileges method and updating the listitem using SPListItem.SystemUpdate() method (see here). but this approach has its own flaws like (RunWithElevatedPrivileges will run under system account, we cannot use SystemUpdate for SPSite,SPWeb,SPList, since it runs under system we will "CreatedBy" by as SystemAccount).

Using JQuery to perform Ajax calls in ASP.NET MVC

The muscle behind the actual asynchronous calls comes from JavaScript. I looked around at a bunch of existing JavaScript libraries and settled on JQuery because of the way it leverages existing CSS knowledge. The three things that the library should do easily are:

SQL Connection String

We can learn or know how can we connect the database from our .net. This will help for freshers or .NET beginners!

abstract away the source of the connection string using a class with a static property.

ASP.NET provides a configuration system we can use to keep our applications flexible at runtime. In this article we will examine some tips and best practices for using the configuration system for the best results.

The element of a web.config file is a place to store connection strings, server names, file paths, and other miscellaneous settings needed by an application to perform work. The items inside appSettings are items that need to be configurable depending upon the environment, for instance, any database connection strings will change as you move your application from a testing and staging server into production.

Let's abstract away the source of the connection string using a class with a static property.

connection string question from a newbie


when publishing  in the webmatrix dialogue it asks for "destination connection string" for my "database.mdf"

I was not sure what it was so I pasted this from my web.config file :

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;User Instance=True

it all published ok, but wont run, so I guess it has somthing to do with this connection string or some kind of configuration issue.

My question is this, What should go in the field for "Destination connection string"

localhost is not set up to establish a connection on port 51624 with this computer


I'm trying to run asp.net locally, but getting the error

localhost is not set up to establish a connection on port 51624 with this computer

How can I fix it?

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.


I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

Unable to make connection to database?


Hy guys. I try to connect my database for so long so i wrote two type of codes. Let's discuse first on.

I have database called PhoneDirectory.

It have only one table named Residents.

I have button wiht ID="Button1".

I want when i click this button to see all of my residents. 

I want to see the result in DetailsView and to be able to edin, delete and add new resident.

First try with SqlDataSource so i wrote this:

<asp:SqlDataSource ID="Resident Details" runat="server" ProviderName="System.Data.SqlClient" ConnectionStrings="<%$ ConnectionStrings:ThisIsTheConnection%>" 
              SelectCommand = "SELECT * FROM Resident "

Need Oracle Data Provider .CS File for Oracle 10g Database connection !



I need a 'Wrapper.cs' file which takes care of the Database connection ( Oracle 10g) where

i can just call the method with my SQL Query


Gridview1.DataSource = SampleWrapper.ExecuteDatatable("THE SQL QUERY");


Plz Post the link if there is any open source !    

Intermittant error: "A connection attempt failed because the connected party did not properly respon



I'm having a doozy of a problem, and can't find a solution.  I have a WFC Web Service installed to a pair of servers, and accessed through a load balancer.  I am accessing the service from an ASP.NET client app, also via WCF.  The Client is not load balanced.

I'm getting an intermittant error:

(Inner Most Exception) System.Net.Sockets.SocketException:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond x.x.x.x:80

(Middle Exception) System.Net.WebException: Unable to connect to the remote server

(Outer Exception) System.ServiceModel.EndpointNotFoundException:  Could not connect to http://<MYSite>/<MYApp>/<MYService>.svc. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond x.x.x.x:80.

I can reproduce this error with about 20% reliability if I execute the exact same page request on the client from 2 browsers at the same time.  When the problem occurs, the successful p

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend