右code检索从SQL Server数据库中的数据 [英] Right code to retrieve data from sql server database

问题描述

我在数据库连接的一些问题，并想知道如果我有什么错我的code。请查阅。这个问题涉及：<一href=\"http://stackoverflow.com/questions/4547070/switch-between-databases-use-two-databases-simultaneously\">Switch数据库之间，使用两个数据库同时问题。

I have some problems in database connection and wonder if I have something wrong in my code. Please review. This question is related: Switch between databases, use two databases simultaneously question.

cs="Data Source=mywebsite.com;Initial Catalog=database;User Id=root;Password=toor;Connect Timeout=10;Pooling='true';"

using (SqlConnection cnn = new SqlConnection(WebConfigurationManager.ConnectionStrings["cs"].ConnectionString))
{
    using (SqlCommand cmmnd = new SqlCommand("", cnn))
    {
        try
        {
            cnn.Open();

            #region Header & Description
            cmmnd.Parameters.Add("@CatID", SqlDbType.Int).Value = catId;
            cmmnd.CommandText = "SELECT UpperID, Title, Description FROM Categories WHERE CatID=@CatID;";

            string mainCat = String.Empty, rootCat = String.Empty;

            using (SqlDataReader rdr = cmmnd.ExecuteReader())
            {
                if (rdr.Read())
                {
                    mainCat = rdr["Title"].ToString();
                    upperId = Convert.ToInt32(rdr["UpperID"]);
                    description = rdr["Title"];
                }
                else { Response.Redirect("/", false); }
            }

            if (upperId > 0) //If upper category exists add its name
            {
                cmmnd.Parameters["@CatID"].Value = upperId;
                cmmnd.CommandText = "SELECT Title FROM Categories WHERE CatID=@CatID;";
                using (SqlDataReader rdr = cmmnd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        rootCat = "<a href='x.aspx'>" + rdr["Title"] + "</a> &raquo; ";
                    }
                }
            }
            #endregion

            #region Sub-Categories
            if (upperId == 0) //show only at root categories
            {
                cmmnd.Parameters["@CatID"].Value = catId;
                cmmnd.CommandText = "SELECT Count(CatID) FROM Categories WHERE UpperID=@CatID;";

                if (Convert.ToInt32(cmmnd.ExecuteScalar()) > 0)
                {
                    cmmnd.CommandText = "SELECT CatID, Title FROM Categories WHERE UpperID=@CatID ORDER BY Title;";

                    using (SqlDataReader rdr = cmmnd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            subcat.InnerHtml += "<a href='x.aspx'>" + rdr["Title"].ToString().ToLower() + "</a>\n";
                            description += rdr["Title"] + ", ";
                        }
                    }
                }
            }
            #endregion
        }
        catch (Exception ex) { HasanG.LogException(ex, Request.RawUrl, HttpContext.Current); Response.Redirect("/", false); }
        finally { cnn.Close(); }
    }
}

我收到的随机错误是：

The random errors I'm receiving are:

• 发送请求到服务器时出现了传输级错误。 （provider：TCP提供程序，error：0 - 一个现有的连接被强行关闭远程主机）


• 建立SQL Server的连接时发生网络相关的或特定于实例的错误。服务器未找到或无法访问。验证实例名称是否正确，以及SQL Server配置为允许远程连接。 （provider：命名管道提供程序，error：40 - 无法打开到SQL Server的连接）


• 超时过期。之前从池中获取连接超时时间已过。出现这种情况可能是因为所有池连接使用，最大池大小达到了。


• 无法打开数据库DB登录所请求。登录失败。用户登录失败'根'。

推荐答案

这里没有真正的问题。

There's no real issues here.

您不需要多余的最后{cnn.close（）; }作为所使用的子句会照顾你们。然而改变它将会有确切的影响为零。

You don't need the extraneous finally { cnn.close(); } as the using clause will take care of that for you. However changing it will have exactly zero impact.

另一件事是，我会把尝试..赶上外界所使用的子句的一个重定向。但同样，我不认为这会影响到被调用的Dispose。

Another thing is that I would put the try .. catch outside of the using clause with a redirect. But, again, I don't think that would affect the dispose from being called.

，你会得到连接池中的错误很有趣（超时过期），如果你总是正确处理您的连接，因为你已经证明。

It's interesting that you would get connection pool errors (timeout expired) if you are always properly disposing of your connections, as you've shown.

这留给我们的只有一个真正的解决办法：更换托管提供商。他们要么超载的DB服务器unusability或在自己的网络设置（网卡，交换机，路由器等），一些硬件元素的点差和丢弃数据包。

Which leaves us with only one real solution: switch hosting providers. They have either overloaded their DB server to the point of unusability or some hardware element in their network setup (nic, switch, router, etc) is bad and dropping packets.

这篇关于右code检索从SQL Server数据库中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！