ASP.net中的SQL连接共享 [英] SQL Connection Sharing in ASP.net

问题描述

你好，我叫Vikash Gohil.
我已经使用ASP.net和SQL Server 2000开发了一个Web应用程序. 在我的应用程序中，我创建了一个类，并在该类中创建了SQL Connection变量.
然后在该类(即Sub New())的构造方法中，我分配连接字符串并打开连接.
然后，在每个网页上，我都在创建此类的实例.
所以我的问题是，在创建每个新实例时，会创建与数据库的新连接吗?
如果是，那么我该如何为一个用户创建一个连接(当他登录时注销，而在他注销时断开连接)?
我心中有这种困惑.
任何帮助将不胜感激.
在此先感谢.

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.

推荐答案

Vikash，
我不建议您将这种模型用于您的网站.
您只需要为特定的数据库操作打开连接，此后必须关闭连接，否则注定会遇到一些内存消耗问题和数据库锁定问题.
相反，我建议您使用以下结构:
1.创建/打开连接
2.对数据库做一些操作
3.之后立即关闭连接.

在c#中，有一种执行此类操作的模式:

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:

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

祝你好运.

Good luck.

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;
}

第一次打开连接，然后将连接对象保留在Application对象中，就可以对所有对象重用连接对象用户.

因此在上述senario中，您一次只能保持一个连接处于活动状态.

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.

这篇关于ASP.net中的SQL连接共享的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！