SQL Connection Sharing in ASP.net

Question

0.00/5 (No votes)

See more:

SQL-server-2000

, +

Hello, this is Vikash Gohil.
I have developed a web application using ASP.net and SQL Server 2000.
In my application I have created a class and in that class I have created SQL Connection variable.
Then in Constructor for that class ie, Sub New(), I am assigning the connection string and opening the connection.
Then on each web page I am creating the instance of this class.
So my question is, on creating each new instance will it create a new connection to the Database?
If yes, then how can I do that one connection is created for one user, when he logs on and disconnected when he logouts?
Had this confusion in my mind.
Any help would be greatly appreciated.
Thanks In Advance.

Posted 2-Dec-10 0:37am

VikashGohil

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ed Guzman · Answer 1 · 2010-12-02T00:56:00

Vikash,
I would not recommend you to use this model for your website.
You need the connection open only for a particular database operation(s), after that you have to close the connection, otherwise you are doomed to some memory consumption problems and database lock issues.
Instead, I would recommend you the following construct:
1. create/open connection
2. do some operations against the database
3. close the connection immediately after.

In c# there is a pattern for doing such things:

C#

using (SqlConnection con= new SqlConnection("connection string"))
{
   con.Open();
   //do what you need here
}

Good luck.

Praveen Patidar · Answer 2 · 2011-11-04T00:16:00

public class Connectionclass
{
	
	SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[" Educational"]);

    public bool opencon()
    {
    bool retval=false;
        try
        {
        if (con.State==ConnectionState.Open)
        {
        con.Close();

        }
            con.Open();
            retval=true;
        }
        catch(Exception ex)
        {
            throw(ex);
        }
        return retval;
            
    
    }

	public bool closecon()
    {
 bool retval=false;
        try
        {
            if(con.State==ConnectionState.Closed)
            {
            retval=true;
            }
            con.Close();
            retval=true;

        }
    catch(Exception ex)

        {  
            throw(ex);
        }
        return retval;
    }
    
    public int ExecuteQuries (string sql)
 
    {
    int value=0;
        try

        {
        con.Open();
        SqlCommand cmd=new SqlCommand(sql,con);
        value=cmd.ExecuteNonQuery();
        
        }
    catch(Exception ex)
        {
            throw(ex);
        }
        finally
        {
        con.Close();
        }
    return value;
    
    
    
    
    }
    
    public int result( string sql)
    {
    int value=0;
        try
        {
            con.Open();
            SqlCommand cmd=new SqlCommand(sql, con);
            value=Convert.ToInt16(cmd.ExecuteScalar());
        
        }
    catch(Exception ex)
        {
        throw(ex);
        }
        finally
        {
        con.Close();
        }
        return value;
    }
    public int ExecuteNonQuries(SqlCommand cmd)
    {
        int i = 0;
        try
        {
            opencon();
            cmd.Connection = con;
            i = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
            {
                throw(ex);
        }
        

            finally
        {
            con.Close();
        }
        return i;
    }

    public DataSet retdata(SqlCommand cmd, DataSet ds)
    {
        try
        {
            opencon();
            cmd.Connection=con;
            SqlDataAdapter  adp =new SqlDataAdapter(cmd);
            adp.Fill(ds);
        }
    catch( Exception ex)
        {    
   
              throw(ex) ;   
    }
    finally
        {
        con.Close();
        
        }
        return ds;
    }

     public Boolean FillGrid(ref GridView gv,string sql)
    {
        bool value = false;
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            con.Open();
            da.Fill(ds);
            gv.DataSource = ds;
            gv.DataBind();
            value = true;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
        return value;
    }
     public bool FillDropDownList(string sql, ref DropDownList ddl, string TextField, string ValueField)
     {
         bool value = false;
         try
         {
             SqlDataAdapter da = new SqlDataAdapter(sql, con);
             DataSet ds = new DataSet();
             con.Open();
             da.Fill(ds);
             ddl.DataSource = ds;
             ddl.DataTextField = TextField;
             ddl.DataValueField = ValueField;
             ddl.DataBind();
             ddl.Items.Add("-SELECT-");
             ddl.Items.FindByText("-SELECT-").Selected = true;
             value = true;
         }
         catch (Exception ex)
         {
             throw (ex);
         }
         finally
         {
             con.Close();
         }
         return value;
}

koolprasad2003 · Answer 3 · 2011-11-04T00:20:00

Solution 3

Just Open connection for first time and then keep connection object in Application object and you can reuse connection object for all users.

so in above senario you can keep only one connection alive at a time.

Posted 4-Nov-11 0:20am

koolprasad2003

SQL Connection Sharing in ASP.net

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0