c# - SqlCommand.ExecuteReader 并越过第一行

Question

我是 ASP.NET 的新手，作为一项自选任务，我要创建一个简单的博客。快完成了，但我遇到了一个问题。正如您从代码中看到的那样，我首先使用查询从我的 SQL 服务器数据库中返回博客条目的数量。然后，for 循环从所有行中读取标题、日期和文本内容，并创建一个将它们加载到的 html div。除一件事外效果很好:ExecuteScalar() 仅读取第一行，每次迭代都会返回相同的值，从而导致所有博客条目都相同。

我需要一种方法让读者在每次迭代时继续前进。我考虑过将 SQLbulkcopy 制作到另一个表中，然后我可以从 for 循环中删除第一行，但如果要在每个 Page_Load 上完成，这似乎会大大降低页面速度。

string SQLreadTitle = "Select BLOG.BlogTitle from BLOG order by BlogID DESC";
string SQLreadDate = "Select BLOG.BlogDate from BLOG order by BlogID DESC";
string SQLreadText = "Select BLOG.BlogText from BLOG order by BlogID DESC";
string SQLcountIDs = "Select count(BlogID) from BLOG";

protected void Page_Load(object sender, EventArgs e)
{

    int i;
    SqlConnection con = new SqlConnection(cnString);
    con.Open();

    SqlCommand countIDs = new SqlCommand(SQLcountIDs, con);
    int count = Convert.ToInt32(countIDs.ExecuteScalar());



    for (i = 0; i < count; i++)
    {
        SqlCommand readTitle = new SqlCommand(SQLreadTitle, con);
        string titleString = readTitle.ExecuteScalar().ToString();
        SqlCommand readDate = new SqlCommand(SQLreadDate, con);
        string dateString = readDate.ExecuteScalar().ToString();
        SqlCommand readText = new SqlCommand(SQLreadText, con);
        string textString = readText.ExecuteScalar().ToString();



        System.Web.UI.HtmlControls.HtmlGenericControl dynDiv =
        new System.Web.UI.HtmlControls.HtmlGenericControl("DIV");
        dynDiv.ID = "BlogPost";
        dynDiv.InnerHtml = "<div id=\"BlogTitle\">" + 
            titleString + "</div><br /><div id=\"BlogDate\">"
            + dateString + "</div><br /><br /><div id=\"BlogText\">" 
            + textString + "</div>";
        Label1.Controls.Add(dynDiv);


    }
    con.Close();
}

提前致谢!

Answer 1

你让自己的生活变得太艰难了。您应该有一个 select 语句，然后读取返回的每一行。

    string SQL = "Select BLOG.BlogTitle, BLOG.BlogDate, BLOG.BlogText from BLOG order by BlogID DESC";
    // Positions of the columns you are reading
    const int TITLE_ORDINAL = 0;
    const int DATE_ORDINAL = 1;
    const int TEXT_ORDINAL = 2;

    protected void Page_Load(object sender, EventArgs e)
    {
        using (var con = new SqlConnection(cnString))
        {
            con.Open();
            try
            {
                using (var oCommand = new SqlCommand(SQL, con))
                {
                    using (var oReader = oCommand.ExecuteReader())
                    {
                        while (oReader.Read())
                        {
                            var dynDiv = new System.Web.UI.HtmlControls.HtmlGenericControl("DIV");
                            dynDiv.ID = "BlogPost";
                            dynDiv.InnerHtml = "<div id=\"BlogTitle\">" +
                                oReader.GetString(TITLE_ORDINAL) + "</div><br /><div id=\"BlogDate\">"
                                + oReader.GetString(DATE_ORDINAL) + "</div><br /><br /><div id=\"BlogText\">"
                                + oReader.GetString(TEXT_ORDINAL) + "</div>";
                            Label1.Controls.Add(dynDiv);
                        }
                    }
                }
            } finally {
                con.Close();
            }
        }
    }