使用DBConnection超时

Question

我正在使用C#创建一个简单的应用程序，它将在1分钟后反复检查数据库。我使用线程使它更有风度，减少资源。这些线程将只在一次同步执行一个函数。

我的问题是

异常未被处理: DBConnection 超时过期了。在操作完成或服务器没有响应之前经过的超时时间。

我知道我与数据库的连接超过了时间限制。那么，如何解决这个问题呢？

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using GDEX.Master;
using GDEX.DataAccess;
using System.Globalization;
using System.IO;
using System.Threading;

namespace SMPPTransfer
{
    public partial class frmDBTS : Form
    {
        string updateProbRecord = string.Empty;
        string selectProbRecord = string.Empty;
        string selectProbStat = string.Empty;
        string updateStat = string.Empty;
        string selectAssignTo = string.Empty;
        string CheckPODStatus = string.Empty;

        DataTable dtStat = null;
        DataTable dtPOD = null;
        DataTable dtAssignNo = null;

        bool stopThreads = false;
        AutoResetEvent blockThread1 = new AutoResetEvent(true);
        AutoResetEvent blockThread2 = new AutoResetEvent(false);

        delegate void SetTextCallback(string text);
        GDexSqlSvConnection dbCon;

        public frmDBTS()
        {
            InitializeComponent();
            String connSQLSvr = "Data Source=<my IP>;Initial Catalog=<database>;User ID=<username>;Password=<pwd>;";
            dbCon = new GDexSqlSvConnection(connSQLSvr);

            Thread thread1 = new Thread(new ThreadStart(UpdateProbRec));
            Thread thread2 = new Thread(new ThreadStart(UpdateProbStat));

            thread1.Start();
            thread2.Start();
        }

        private void UpdateProbRec()
        {
            while (stopThreads == false)
            {
                blockThread1.WaitOne();

                SetText1("Entered Thread 1");
                SetText2("Out Thread 2");                

                //Get POD status
                CheckPODStatus = "select top 100 * from (select cn"
                                 + " FROM gdexpdb.dbo.prob_record where solve = 'N' and status!='S') A "
                                 + " join (select cn, cn_date"
                                 + " from gdexpdb.dbo.pod_data where type='rts' or type = 'pod' or type = 'm_pod' or type = 'm_rts') B "
                                 + "on A.CN=B.cn";

                dtPOD = dbCon.ExecuteQueryAndGetDataTable(CheckPODStatus); //Problem occur from here and only for this function only

                DateTime cnDate;
                string cnDateCon;
                for (int iii = 0; iii < dtPOD.Rows.Count; iii++)
                {
                    cnDate = (DateTime)dtPOD.Rows[iii][2];

                    cnDateCon = cnDate.ToString("yyyy-MM-dd");

                    updateProbRecord = "update gdexpdb.dbo.prob_record set solve='Y', solve_date='" + cnDateCon + "', status='S' "
                                        + "where cn='" + dtPOD.Rows[iii][0] + "'";
                    dbCon.ExecuteNonQuery(updateProbRecord);
                }

                dtPOD.Clear();
                Thread.Sleep(30000);
                blockThread2.Set();
            }
        }

        private void UpdateProbStat()
        {
            while (stopThreads == false)
            {
                blockThread1.WaitOne();

                SetText2("Entered Thread 2");
                SetText1("Out Thread 1");

                selectProbStat = "select username from gdexpdb.dbo.prob_stat";

                dtStat = dbCon.ExecuteQueryAndGetDataTable(selectProbStat);
                int[] userNo = new int[dtStat.Rows.Count];

                for (int x = 0; x < dtStat.Rows.Count; x++)
                {
                    selectAssignTo = "select count(*) as assignNo from gdexpdb.dbo.prob_record where assign_to='" + dtStat.Rows[x][0]+ "'";
                    dtAssignNo = dbCon.ExecuteQueryAndGetDataTable(selectAssignTo);

                    updateStat = "update gdexpdb.dbo.prob_stat set stat=" + dtAssignNo.Rows[0][0] + "where username='" + dtStat.Rows[x][0] + "'";
                    dbCon.ExecuteNonQuery(updateStat);
                }

                dtStat.Clear();
                dtAssignNo.Clear();
                Thread.Sleep(100000);
                blockThread1.Set();
            }
        }

        private void SetText1(string text)
        {
            // InvokeRequired required compares the thread ID of the
            // calling thread to the thread ID of the creating thread.
            // If these threads are different, it returns true.
            if (this.TextThread1.InvokeRequired)
            {
                SetTextCallback d = new SetTextCallback(SetText1);
                this.Invoke(d, new object[] { text });
            }
            else
            {
                this.TextThread1.Text = text;
            }
        }

        private void SetText2(string text)
        {
            // InvokeRequired required compares the thread ID of the
            // calling thread to the thread ID of the creating thread.
            // If these threads are different, it returns true.
            if (this.TextThread2.InvokeRequired)
            {
                SetTextCallback d = new SetTextCallback(SetText2);
                this.Invoke(d, new object[] { text });
            }
            else
            {
                this.TextThread2.Text = text;
            }
        }
    }
}

Answer 1

在GDexSqlSvConnection类中-函数ExecuteQueryAndGetDataTable，ExecuteNonQuery和其他.将CommandTimeout属性设置为用于查询数据库的数据库命令实例。

例如：

MyCommand.CommandTimeout = 120; // 2 Minutes Timeout

Answer 2

可以使用Connect Timeout属性在连接字符串中指定更长的连接超时。将其设置为0意味着它不会超时。

Connect Timeout=0

参考：连接字符串选项

虽然，这将使您通过当前的问题，您真正需要做的是修复您的查询。

简单的选择和更新不应导致数据库超时.

要加快这些查询的速度：

1. 在表上创建适当的索引
2. 对表进行分区，如果表非常大，则使用不同的文件组。
3. 检查分配给数据库服务器的资源。

Answer 3

超时可以是你的DbConnection上的一个DbConnection或者DbCommand上的一个CommandTimeout --通常是最后一个。