CODEWITHSUNDEEP
c#sqlsql-serverconnection-stringsqlconnection
HydraCc
HydraCc

Reputation: 41

Is overutilisation of Sql Connections in C# a problem?

Throughout the program which I am currently working on, I realized that whenever I need to access to SQL Server, I just type a queryString and execute my query using SqlCommand and SqlConnection in C#. At a certain point during the program, I even opened a connection and ran a query in a "for loop".

Is it unhealthy for the program to constantly open-close connections?

***I am not very familiar with the terminology, therefore you might be having some problems understanding what I am talking about:

Is doing this very frequently may cause any problem?:

string queryString = "Some SQL Query";
public void(){
   SqlConnection con = new Connection(conString);
   SqlCommand cmd = new SqlCommand(queryString,con);
   cmd.Parameters.AddWithValue("@SomeParam",someValue);
   con.Open();
   cmd.ExecuteNonQuery();
   con.Close();

}

I use this template almost every class I create,(usually to get,update,insert data from/into a datatable). Well, is it harmful?

Upvotes: 0

Views: 250

Answers (1)

SMor
SMor

Reputation: 2872

The short answer is yes - it is inefficient to constantly open and close connections. Opening an actual connection is a very expensive process and managing a connection for the lifetime of its need (which usually is the lifetime of the application or process using it) is fraught with errors.

That is why connection pooling was introduced a long time ago. There is a layer beneath your application that will manage the physical opening/closing of connections in a more efficient way. This also helps prevent the chances that an open connection is lost and continues to stay open (which causes lots of problems). By default pooling is enabled so you don't need to do anything to use it.

With pooling - you write code to open a connection and use it for the duration of a particular section of code and then close it. If the connection pool has an open but unused connection, it will reuse it rather than open a new one. When you close the connection, that simply returns the connection to the pool and makes it available to the next open attempt. You should also get familiar with the c# using statement.

Upvotes: 3

Related Questions