multiple SqlConnections for one feature: bad idea?

Question

I got an asp.net gridview connected to my sql database. When Inserting a new record or updating a record im doing some serverside checks and then either update/insert a record or do nothing. right now i got 2 methods CheckArtistExists and CheckSongExists which are both using a SqlConnection Object e.g.

public bool CheckSongExists(string _title, int _artistId)
    {
        int cnt = -1;
        using (SqlConnection con = new SqlConnection(CS))
        {
            //check if song already is exists in DB
            SqlCommand cmd = new SqlCommand("Select Count(ID) from tblSong WHERE Title = @newTitle AND ArtistId = @newArtistId;", con);
            cmd.Parameters.AddWithValue(@"newTitle", _title);
            cmd.Parameters.AddWithValue(@"newArtistId", _artistId);
            con.Open();
            cnt = (int)cmd.ExecuteScalar();
            // if cnt ==1 song exists in DB, of cnt == 0 song doesnt exist
            if(cnt == 1)
            { return true; }
            else
            { return false; }
        }
    }

So for the Update function in the gridview i need to establish 3 SqlConnections (at max) one to check for the artist(if artist doesnt exist i have to insert a record to tblArtist first) then a check if the song exists(only if artist exists) and finally if song doesnt exist I have to insert a new record.

I know database connections are valuable resources thats why i put them in a using block. So im not quite sure if its good style to use 3 SqlConnection objects to update/insert. Can you please tell me if my code is ok or if i should rather use another approach for this problem.

thank you

score 1 · Answer 1 · edited May 23 '17 at 12:11

ADO.NET internally manages the underlying Connections to the DBMS in the ADO-NET Connection-Pool:

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

So obviously there's no reason to avoid creating,opening or closing connections since actually they aren't created, opened and closed at all. This is "only" a flag for the connection pool to know when a connection can be reused or not. But it's a very important flag, because if a connection is "in use"(the connection pool assumes), a new physical connection must be openend to the DBMS what is very expensive.

So you're gaining no performance improvement if you "reuse" connections but the opposite.

Create, open(in case of Connections), use, close and dispose them where you need them(f.e. in a method)
use the using-statement to dispose and close(in case of Connections) implicitely

So yes, it's absolutely fine to use one connection per method since you are not using a physical connection at all if connection-pooling is enabled (default).

Another question is if you could improve your approach. You could create a stored-procedure which checks existence and updates or inserts accordingly.

Solutions for INSERT OR UPDATE on SQL Server

multiple SqlConnections for one feature: bad idea?

1 Answers1