打开和关闭每个查询的数据库连接 [英] Opening and closing database connection for each query

问题描述

我已经写了下面的code。
我开了一次一个查询数据库连接
我想执行另一个查询。
我已经写了下面的code。
但我认为这是一个错误
谁能帮我吗？

 公共无效检查（）
{
    尝试
    {
        OdbcConnection myOdbcConnection =新OdbcConnection（CON1）;
        的OdbcCommand myOdbcCommand = myOdbcConnection.CreateCommand（）;
        字符串sSQL =SELECT * FROM（选择按call_no递减从tbl_IThelpdesk为了tdate），其中ROWNUM = 1;该call_no列//最后一个记录
        myOdbcCommand.CommandText = sSQL;
        myOdbcConnection.Open（）;
        OdbcDataReader myOdbcDataReader = myOdbcCommand.ExecuteReader（）;
        如果（！myOdbcDataReader.Read（））
        {
            txtDate.Text = DateTime.Now.ToShortDateString（）;
            串strcallno = DateTime.Now.Year.ToString（D2）+ DateTime.Now.Month.ToString（D2）+ DateTime.Now.Day.ToString（D2）;
            txtcall.Text =ITHD+ strcallno +001;
            myOdbcConnection.Close（）;
            myOdbcDataReader.Close（）;
        }
        其他
        {
            今天日期时间= DateTime.Parse（DateTime.Now.ToShortDateString（））;
            如果（myOdbcDataReader [0]的ToString（）== today.ToString（））
            {
                myOdbcConnection.Close（）;
                myOdbcDataReader.Close（）;
                myOdbcConnection.Open（）;
                的OdbcCommand myOdbcCommand1 = myOdbcConnection.CreateCommand（）;
                字符串SQLmax =SELECT MAX（call_no）FROM TBL_IThelpdesk
                myOdbcCommand1.CommandText = SQLmax;                OdbcDataReader myOdbcDataReader1 = myOdbcCommand1.ExecuteReader（）;
                而（myOdbcDataReader1.Read（）！= FALSE）
                {
                    txtcall.Text = myOdbcDataReader1 [0]的ToString（）修剪（）。
                }
                myOdbcDataReader1.Close（）;
                myOdbcDataReader.Close（）;
                myOdbcConnection.Close（）;
            }
        }
    }
    赶上（例外五）
    {
        lblEmpty.Text = e.Message;
        lblEmpty.Visible = TRUE;
    }
}
 

解决方案

由于数据库连接使用一个游泳池，您不必维护多个查询相同的连接;相反，打开连接，当你需要它，并尽快将其关闭以释放资源。

请参阅：<一href=\"http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx\">http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx

另请参阅： C＃的SQLConnection汇集

请注意，你已经不使用使用（）{} 模式。鉴于 OdbcConnection 和类似的实施的IDisposable ，你应该将它们嵌入到一个使用，以便对它们进行处理，而无需等待垃圾回收器。

请参阅：<一href=\"http://msdn.microsoft.com/en-us/library/yh598w02.aspx\">http://msdn.microsoft.com/en-us/library/yh598w02.aspx

I have written following code. I have opened the database connection for once for one query I want to execute another query. I have written the code below. But i think there is a mistake Can anyone help me please?

public void check()
{
    try
    {
        OdbcConnection myOdbcConnection = new OdbcConnection(con1);
        OdbcCommand myOdbcCommand = myOdbcConnection.CreateCommand();
        String sSQL = "SELECT * FROM(select tdate from tbl_IThelpdesk order by call_no desc)where ROWNUM = 1"; //last record of the call_no column
        myOdbcCommand.CommandText = sSQL;
        myOdbcConnection.Open();
        OdbcDataReader myOdbcDataReader = myOdbcCommand.ExecuteReader();
        if (!myOdbcDataReader.Read())
        {
            txtDate.Text = DateTime.Now.ToShortDateString();
            string strcallno = DateTime.Now.Year.ToString("d2") + DateTime.Now.Month.ToString("d2") + DateTime.Now.Day.ToString("d2");
            txtcall.Text = "ITHD" + strcallno + "001";
            myOdbcConnection.Close();
            myOdbcDataReader.Close();
        }
        else
        {
            DateTime today = DateTime.Parse(DateTime.Now.ToShortDateString());
            if (myOdbcDataReader[0].ToString() == today.ToString())
            {
                myOdbcConnection.Close();
                myOdbcDataReader.Close();
                myOdbcConnection.Open();
                OdbcCommand myOdbcCommand1 = myOdbcConnection.CreateCommand();
                String SQLmax = "SELECT max(call_no) FROM TBL_IThelpdesk";
                myOdbcCommand1.CommandText = SQLmax;

                OdbcDataReader myOdbcDataReader1 = myOdbcCommand1.ExecuteReader();
                while (myOdbcDataReader1.Read() != false)
                {
                    txtcall.Text = myOdbcDataReader1[0].ToString().Trim();
                }
                myOdbcDataReader1.Close();
                myOdbcDataReader.Close();
                myOdbcConnection.Close(); 
            }
        }
    }
    catch (Exception e)
    {
        lblEmpty.Text = e.Message;
        lblEmpty.Visible = true;
    }
}

解决方案

Since database connections use a pool, you don't have to maintain the same connection for multiple queries; instead, open a connection when you need it, and close it as soon as possible to free up the resources.

See: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx
See also: C# SQLConnection pooling

Note that you've not used using() { } pattern. Given that OdbcConnection and similar types implement IDisposable, you should embed them into a using in order for them to be disposed without waiting the garbage collector.

See: http://msdn.microsoft.com/en-us/library/yh598w02.aspx

这篇关于打开和关闭每个查询的数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！