c# - ASP.NET C# SQL Server 数据读取器

Question

我不是 ASP.NET 的 super 专家，但是我有一个必须用 ASP.NET C# 编写的 Web 应用程序，我在处理从数据库加载数据并将其放入的页面时遇到了问题成一张 table 。下面是我的代码。基本上，发生的事情是它会在尝试加载时间歇性地说“页面无法显示”。加载页面总是需要大约 10-20 秒，我知道我做错了什么。有人可以指出我的错误吗？

using (SqlConnection dbConn = new SqlConnection(strConnection))
{
    SqlDataAdapter dbAdapter = new SqlDataAdapter();
    SqlCommand dbCommand = new SqlCommand();
    dbConn.Open();
    // I tried the SET ARTITHABORT portion below based on other posts I found on SO - no better performance though
    dbCommand.Connection = dbConn;
    dbCommand.CommandText = "SET ARITHABORT ON";        
    dbCommand.ExecuteNonQuery();        
    dbCommand.CommandText = @"SELECT ID, NAME, PART_NUMBER, BARCODE, QOH, MINIMUM_QOH, LAST_PRICE FROM INVENTORY" + Session["table_extension"].ToString();
    dbCommand.Connection = dbConn;
    SqlDataReader dbReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);

    if (dbReader.HasRows)
    {
        strInventoryTable = @"<table id='inventoryTable' cellspacing='0' cellpadding='5' border='0' width='100%'>
                                <thead>
                                    <tr>
                                        <th>Name</th>
                                        <th>Part No.</th>
                                        <th>Barcode</th>
                                        <th>QOH</th>
                                        <th>Min. QOH</th>
                                        <th>Last Price</th>
                                        <th>Action</th>
                                    </tr>
                                </thead>
                                <tbody>";  
        string rowMarker = "even";
        while (dbReader.Read())
        {
            if (rowMarker == "even")
            {
                strInventoryTable += "<tr class='even clickable' onclick='location.href=\"edit_product.aspx?id=" + dbReader["ID"] + "\";'>";
                rowMarker = "odd";
            }
            else
            {
                strInventoryTable += "<tr class='odd clickable' onclick='location.href=\"edit_product.aspx?id=" + dbReader["ID"] + "\";'>";
                rowMarker = "even";
            }
            strInventoryTable += "<td>" + dbReader["NAME"] + "</td>";                        
            strInventoryTable += "<td class='tdCenter'>" + dbReader["PART_NUMBER"] + "</td>";
            strInventoryTable += "<td class='tdCenter'>" + dbReader["BARCODE"] + "</td>";
            strInventoryTable += "<td class='tdRight'>" + dbReader["QOH"] + "</td>";
            strInventoryTable += "<td class='tdRight'>" + dbReader["MINIMUM_QOH"] + "</td>";
            strInventoryTable += "<td class='tdRight'>" + globals.formatMoney(dbReader["LAST_PRICE"].ToString()) + "</td>";
            strInventoryTable += "<td class='tdCenter'><a href='edit_product.aspx?id=" + dbReader["ID"] + "'>Edit</a>&nbsp;&nbsp;";
            strInventoryTable += "<a href='delete_product.aspx?id=" + dbReader["ID"] + "'>Delete</a></td>";
            strInventoryTable += "</tr>";
        }
        strInventoryTable += "</tbody></table>";
    }
    else
    {
        strInventoryTable = "<p><strong><em>No inventory found in database</em></strong></p>";
    }
} 
inventoryTable.InnerHtml = strInventoryTable;

查询本身需要大约 0.00003 秒才能完成。所以我知道这不是查询，我只返回大约 2,400 行。

更新

尝试调试瓶颈所在...这是结果

查询开始:3/24/2016 9:06:06 AM

查询完成:3/24/2016 9:06:06 AM

数据读取器开始:2016 年 3 月 24 日上午 9:06:06

数据读取结束:3/24/2016 9:06:43 AM

所以我的问题肯定是在 while (dbReader.Read()) {} 循环中

Answer 1

我真的建议用 StringBuilder 替换所有的字符串连接。
所有这些字符串连接都在不断地在内存中创建和分配新字符串

出现间歇性错误显然是服务器压力过大的症状。

    // Initialize with a big internal buffer
    // It seems that you have a lot of data but I can't measure from here, you can
    StringBuilder sb = new StringBuilder(1024*1024);     
    if (dbReader.HasRows)
    {
        sb.Append(@"<table id='inventoryTable' cellspacing='0' cellpadding='5' border='0' width='100%'>
                                <thead>
                                    <tr>
                                        <th>Name</th>
                                        <th>Part No.</th>
                                        <th>Barcode</th>
                                        <th>QOH</th>
                                        <th>Min. QOH</th>
                                        <th>Last Price</th>
                                        <th>Action</th>
                                    </tr>
                                </thead>
                                <tbody>");  
        string rowMarker = "even";
        while (dbReader.Read())
        {
            if (rowMarker == "even")
            {
                sb.Append("<tr class='even clickable' onclick='location.href=\"edit_product.aspx?id=" + dbReader["ID"] + "\";'>");
                rowMarker = "odd";
            }
            else
            {
                sb.Append("<tr class='odd clickable' onclick='location.href=\"edit_product.aspx?id=" + dbReader["ID"] + "\";'>");
                rowMarker = "even";
            }
            sb.Append("<td>" + dbReader["NAME"] + "</td>");
            sb.Apppend("<td class='tdCenter'>" + dbReader["PART_NUMBER"] + "</td>");
            ......
        }
        sb.Append("</tbody></table>");
    }
    else
    {
        sb.Append("<p><strong><em>No inventory found in database</em></strong></p>");
    }
    inventoryTable.InnerHtml = sb.ToString();