请检查我的代码，以提供连接泄漏 [英] Please Review my Code that give Connection Leaking

问题描述

大家好，

我创建了一个网站，但有时多个用户同时访问该网站上的某些内容会导致Connection泄漏或出现错误.

我像这样使用:

protected void btn_add_cat_Click(object sender, EventArgs e)
    {
        c.setcon();
        string found = "select category from ctgry where category=''" + txt_cat_name.Text + "''";
        SqlDataAdapter ad1 = new SqlDataAdapter(found, c.getcon());
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1);
        if (ds1.Tables[0].Rows.Count == 0)
        {
            string s1 = "select max(cat_id) from ctgry";
            SqlDataAdapter ad = new SqlDataAdapter(s1, c.getcon());
            DataSet ds = new DataSet();
            ad.Fill(ds);

            if (ds.Tables[0].Rows[0][0] == DBNull.Value)
            {
                cat_no = "1";
            }
            else
            {
                cat_no = (System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) + 1).ToString();
            }

            string s = "insert into ctgry values(''" + cat_no + "'',''" + txt_cat_name.Text + "'')";
            cmd = new SqlCommand(s, c.getcon());
            cmd.ExecuteNonQuery();
}

因此，通过该查询内部的查询是否泄漏连接?
c.setcon()是我的Appcode函数，因为我设置了连接.而且我在Main以及子查询中都使用c.getcon()，所以我不确定错误的主要问题是不是?

请告诉我此查询在查询内是否泄漏了连接?正在使用连接池.什么是getCon和setCon函数?

就个人而言，您应该将上面的代码重新编写为:

 受保护的  void  btn_add_cat_Click(对象发​​件人，EventArgs e)
    {
        sqlConnection cn =新的sqlconnection(" );
        找到字符串 = "  + txt_cat_name.Text +  '";
        SqlDataAdapter ad1 = 新 SqlDataAdapter(found，cn);
        DataSet ds1 = 新 DataSet();
        ad1.Fill(ds1);
        如果(ds1.Tables [ 0 ].Rows.Count == 字符串 s1 = " ;
            SqlDataAdapter广告= 新 SqlDataAdapter(s1，cn);
            DataSet ds = 新 DataSet();
            ad.Fill(ds);
 
            如果(ds.Tables [ 0 ].行[ 0 ] [ 0 ] == DBNull.Value)
            {
                cat_no = " ;
            }
            其他
            {
                cat_no =(System.Convert.ToInt32(ds.Tables [ 0 ].Rows [ 0 ] [ 0 ])+  1 ).ToString();
            }
 
            字符串 s = "  + cat_no + "  + txt_cat_name.Text +  ')";
            cmd = 新 SqlCommand(s，cn);
            cmd.ExecuteNonQuery();
} 

我的另一个建议是将数据访问移到单独的类和事件类库中.您不应该在IMHO背后的代码中真正拥有这种东西.

UPDATE

如果您想以编写代码的方式运行代码，请使用它来建立连接.

 公共 静态 SqlConnection GetConnection()
        {
            尝试
            {
                con = 新 SqlConnection();
                con.ConnectionString =  @" ;
                con.Open();
                返回 con；
            }
            捕获(例外)
            {
                // 请考虑日志记录.
                // 请考虑抛出异常.失败的连接将在哪里处理?
            }
        } 

还要摆脱设置的连接.并不需要.

另外，如果您遇到数据访问问题，我也建议您使用Enterprise库5中的DataAccess块. http://entlib.codeplex.com/releases [^ ]使生活更干净！

在web.config文件中使用您的连接，而在appcode中不使用.以及您在setcon函数中编写了什么.并根据解决方案1每次实例化一个新的sql连接.

 <  配置 > ; 
  <  连接字符串 > 
    <  添加    ="   DataB" 连接字符串  数据源= .\ SQLEXPRESS;< br模式=" 保持  /> AttachDbFilename = | DataDirectory | MyDatabase.mdf;集成安全性= True;< br模式="   / > 
  <  /connectionstrings  > 
<  /configuration  >  

为防止打开连接，请在不使用时断开连接.

您可以使用using 或finally块来放置连接.以下代码可能对您有用，您也可以使用 SQLHelper [ DataSet resultSet = 新 DataSet(); 使用(SqlConnection sqlConnection = 新 SqlConnection(ConnectionString)) { 使用(SqlCommand sqlCommand = 新 SqlCommand(commandText，sqlConnection)) { 如果(参数！= 默认(IList< sqlparameter>)&&参数. ="code-keyword">> 0 ) { parameters.ToList().ForEach(p = > { sqlCommand.Parameters.Add(p); }); } SqlDataAdapter sqlDataAdapter = 新 SqlDataAdapter(); sqlDataAdapter.SelectCommand = sqlCommand; sqlDataAdapter.Fill(resultSet); sqlCommand.Parameters.Clear(); } } 返回 resultSet;

上面的函数最好返回您的数据集.

谢谢
-Amit Gajjar

Hello To all ,

i make a Website but some times when the multiple user access something from the website at sametime that gives Connection leaking or get Error.

i use like :

protected void btn_add_cat_Click(object sender, EventArgs e)
    {
        c.setcon();
        string found = "select category from ctgry where category=''" + txt_cat_name.Text + "''";
        SqlDataAdapter ad1 = new SqlDataAdapter(found, c.getcon());
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1);
        if (ds1.Tables[0].Rows.Count == 0)
        {
            string s1 = "select max(cat_id) from ctgry";
            SqlDataAdapter ad = new SqlDataAdapter(s1, c.getcon());
            DataSet ds = new DataSet();
            ad.Fill(ds);

            if (ds.Tables[0].Rows[0][0] == DBNull.Value)
            {
                cat_no = "1";
            }
            else
            {
                cat_no = (System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) + 1).ToString();
            }

            string s = "insert into ctgry values(''" + cat_no + "'',''" + txt_cat_name.Text + "'')";
            cmd = new SqlCommand(s, c.getcon());
            cmd.ExecuteNonQuery();
}

so, by this query inside query Leak the connection or not ?
c.setcon() is My Appcode function in that i set the connection. and i use c.getcon() in Main as well as subquery , so i m not sure the error main problem is that or not ?

Please Tell me this query inside query leak the COnnection or not ?

解决方案

For each connection you need to instantiate an new sql connection and not re-use a connection unless you are using connection pooling. What is the getCon and setCon functions?

Personally you should re-write your code above to:

protected void btn_add_cat_Click(object sender, EventArgs e)
    {
        sqlConnection cn = New sqlconnection("connection string");
        string found = "select category from ctgry where category='" + txt_cat_name.Text + "'";
        SqlDataAdapter ad1 = new SqlDataAdapter(found, cn );
        DataSet ds1 = new DataSet();
        ad1.Fill(ds1);
        if (ds1.Tables[0].Rows.Count == 0)
        {
            string s1 = "select max(cat_id) from ctgry";
            SqlDataAdapter ad = new SqlDataAdapter(s1, cn );
            DataSet ds = new DataSet();
            ad.Fill(ds);
 
            if (ds.Tables[0].Rows[0][0] == DBNull.Value)
            {
                cat_no = "1";
            }
            else
            {
                cat_no = (System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) + 1).ToString();
            }
 
            string s = "insert into ctgry values('" + cat_no + "','" + txt_cat_name.Text + "')";
            cmd = new SqlCommand(s, cn );
            cmd.ExecuteNonQuery();
}

My other suggestion would be to move the data access into a seperate class and event class library. You should not really have this sort of thing in the code behind IMHO.

UPDATE

use this to get your connection if you want to run your code the way you have written it.

public static SqlConnection GetConnection() 
        {
            try
            {
                con = new SqlConnection();
                con.ConnectionString = @"connection String";
                con.Open();
                return con;
            }
            catch (Exception ex)
            {
                //consider logging.
                //consider throwing exception. Where would a failed connection be handled?
            }          
        }

Also get rid of your set connection. Not needed.

Also if data access is an issue for you I would recommend the DataAccess blocks from Enterprise library 5. http://entlib.codeplex.com/releases[^] makes life a bit cleaner!!

use your connection in web.config file not in appcode. and what have you written in setcon function. and as per solution 1 each time instantiate an new sql connection.

<configuration>
  <connectionstrings>
    <add name="DataB" connectionstring="Data Source=.\SQLEXPRESS;<br mode=" hold=" />      AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;<br mode=" />
  </connectionstrings>
</configuration>

Hi,

To prevent open connection, destroy your connection when it is not in use.

you can use either using or finally block to dispose the connection. below code may be useful for you, you can also use SQLHelper[^] class.

DataSet resultSet = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection))
{
if (parameters != default(IList<sqlparameter>) && parameters.Count > 0)
                    {
                        parameters.ToList().ForEach(p =>
                        {
                            sqlCommand.Parameters.Add(p);
                        });
                    }

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = sqlCommand;
                    sqlDataAdapter.Fill(resultSet);
                    sqlCommand.Parameters.Clear();
}
}
return resultSet;

above function would be best to return your dataset.

Thanks
-Amit Gajjar

这篇关于请检查我的代码，以提供连接泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！