CODEWITHSUNDEEP
c#web-servicessql-server-2008
bebosh
bebosh

Reputation: 863

Calling SQL Server more than one at same time and same connection

I created global static class, and it has a SqlConnection as in code below:

public class Globals
{
    public static SqlConnection SqlDbConn;

    public static OpenConnection(String ConnString)
    {
        SqlDbConn = new SqlConnection(ConnString);
        SqlDbConn.Open();              
    }
}

And then created web service, and it have more one function.

My question is: how can I use the same connection (SqlDbConn) in all functions in the same time? In other words, there are multi-user calling this web service at the same time, and each function in web service using the same SqlDbConn, this is true way? If not why ?

Finally, in web service I used this constructor to initial the connection:

public class Service1 : System.Web.Services.WebService
{
    private static bool Initialized = false;

    static string ConnString= @"Data Source=192.168.1.1\SQLEXPRESS;UID=sa;PWD=0000;Initial Catalog=DBName; MultipleActiveResultSets=true";

    public Service1()
    {
        // This method called each function call
        if (!Initialized )
        {
            Globals.OpenConnection(ConnString);
            Initialized = true;
        }
    }
}

Upvotes: 0

Views: 879

Answers (3)

Steve
Steve

Reputation: 216313

Your code could be very wrong in presence of threads and in any case it is a poor copy of the Connection Pooling infrastructure that already takes care of reusing in an efficient way a physical connection.

However you don't need many changes to have a better approach

public class Globals
{
    // Keep global just the connection string 
    // (albeit you could simply pass it every time)
    public static string ConnString {get;set;};

    // Change the OpenConnection method to return the SqlConnection created 
    public static SqlConnection OpenConnection()
    {
        SqlConnection cnn = new SqlConnection(ConnString);
        cnn.Open();              
        return cnn;
    }
}

.....

public class Service1 : System.Web.Services.WebService
{
    private static bool Initialized = false;
    public Service1()
    {
        // This method called each function call
        if (!Initialized )
        {
            // Don't hard code the connectionstring details, read it from CONFIG
            // See ConfigurationManager class....
            string connectionString = ReadConnectionStringFromYourConfigFile()
            Globals.ConnString = connectionString;
            Initialized = true;
        }
    }

    // An usage example of your Globals
    public int GetDatabaseValue()
    {
         using(SqlConnection cn = Globals.OpenConnection())
         {
            .....
         } // Here the connection will be closed and disposed also in case of exceptions...
    }
}

Upvotes: 0

marc_s
marc_s

Reputation: 755033

The generally accepted Best Practice for dealing with connections and commands on a "raw" ADO.NET level is to always create those objects as needed, as late as possible, and free them again as soon as possible. ADO.NET already has a very sophisticated connection pooling mechanism built-in - no point in trying to outsmart that well-proven mechanism.

In order to ensure proper disposal of the objects, it is also generally accepted to put them into using(...) { ... } blocks - something like this:

// define query as a string
string query = "SELECT ..... FROM ... WHERE ......";

// create connection and command in "using" blocks
using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmd = new SqlCommand(conn, query))
{
    // set up e.g. parameters for your SqlCommand

    // open the connection, execute the query, close the connnection again
    conn.Open();

    // for a SELECT, use ExecuteReader and handle the reader (or use SqlDataAdapter to fill a DataTable)
    // for UPDATE, INSERT, DELETE just use a ExecuteNonQuery() to run the command
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
           // handle the data from the reader
        }

        // close reader
        rdr.Close();
    }

    conn.Close();
}

Upvotes: 2

Sievajet
Sievajet

Reputation: 3523

You should create a new SqlConnection instance on every call to the database and Dispose it when youre done. SqlConnection has a pooling mechanism that create and reuses connections for you. Handling every call on a single connection object can possibly throw session busy exceptions on async operations

Upvotes: 3

Related Questions