SQL超时异常

问题描述

为什么我得到这个例外在我的code？我重新启动服务器，更改端口等，但没有什么工作。

Why do I get this exception in my code? I restarted the server, changed ports, etc, but nothing is working.

怎么了？

DataTable dt = new DataTable();

SqlConnection con = new SqlConnection("server=localhost;user=armin;password=root;");
con.Open();

SqlCommand result = new SqlCommand(
    "SELECT userid FROM KDDData.dbo.userprofile order by userid", con);

SqlDataReader reader = result.ExecuteReader();
dt.Load(reader);

List<string> userids = new List<string>(dt.Rows.Count);

foreach (DataRow item in dt.Rows)
{
    userids.Add(item.ItemArray[0].ToString().Trim());
}

con.Close();

con = new SqlConnection("server=localhost;user=armin;password=root;");
con.Open();

foreach (string user in userids)
{
    DataTable temp = new DataTable();
    SqlCommand result1 = new SqlCommand(
    "select itemid from KDDTrain.dbo.train where userid=" + user, con);

    SqlDataReader reader1 = result1.ExecuteReader();

    if (!reader1.HasRows)
    {
        continue;
    }

    temp.Load(reader1);
}

第一个查询工作正常，但第二次没有。正如你所看到的我甚至用一些其他的的SqlConnection ，但它仍然无法正常工作。

The first query works fine, but the second doesn't. As you can see I even use some other SqlConnection but it still doesn't work.

请注意：我正在使用的数据库有ATLEAST 100畅想记录，想到的可能是，这将是一个问题。

Note:The database i'm working with has atleast 100 milion records,thought may be this would be a problem.

推荐答案

东西看起来不正确的连接字符串
我总是看到服务器=本地主机;用户=阿明;密码=根在连接字符串的MySQL不使用Sql Server的地方，而不是我会使用数据来源=（本地）;集成安全性= SSPI或SqlServer的实例名称。你肯定第一个查询的工作？

Something doesn't look right in your connection string
I always seen "server=localhost; user=armin;password=root" in connections strings for MySql not for SqlServer where instead I will use "Data Source=(LOCAL);Integrated Security=SSPI" or the INSTANCE name of SqlServer. Are you sure that the first query works?.

不过，我认为你应该使用using语句相应的

However I think you should use the appropriate using statement

DataTable dt = new DataTable();
using(SqlConnection con = new SqlConnection("server=localhost;user=armin;password=root;"))
{
    using(SqlCommand result = new SqlCommand(
            "SELECT userid FROM KDDData.dbo.userprofile order by userid", con))
    {
        con.Open();
        using(SqlDataReader reader = result.ExecuteReader())
        {
           dt.Load(reader);
           List<string> userids = new List<string>(dt.Rows.Count);
           foreach (DataRow item in dt.Rows)
           {
              userids.Add(item.ItemArray[0].ToString().Trim());
           }
        }
        DataTable temp = new DataTable();
        foreach (string user in userids)
        {
            using(SqlCommand result1 = new SqlCommand(
            "select itemid from KDDTrain.dbo.train where userid=" + user, con))
            {
                using(SqlDataReader reader1 = result1.ExecuteReader())
                {
                    if (!reader1.HasRows)   continue;
                    temp.Load(reader1);
                }
            }
        }
   }

这篇关于SQL超时异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！