使用DataReader读取两个表的记录，执行后还是返回一个表的记录

Question

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string sConnectionString = "server=.;database=fourm;trusted_connection=true";
        string sql = "select * from tbBoard ;select * from tbClass";       
        using (SqlConnection conn = new SqlConnection (sConnectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand (sql,conn))
            {
              using(SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
             { 
               if(dr.HasRows)
                {
                   System.Text.StringBuilder htmlStr = new System.Text.StringBuilder();
                   htmlStr.Append("<table border='3' cellPadding='5' cellSpacing='1' style='font-size:9pt;font:宋体'>");
                    htmlStr.Append("<tr style='background-color=#F0F0F0'>");
                    for (int i = 0; i < dr.FieldCount; i++ )
                    {  
                        htmlStr.Append(string.Format("<td><strong>{0}</strong></td>",dr.GetName(i)));
                    }
                    htmlStr.Append("</tr>");
                    while (dr.Read())
                    {
                        htmlStr.Append("<tr>");
                        for (int i = 0; i < dr.FieldCount;i++ )
                        {
                            if (!dr.IsDBNull(i))                            htmlStr.Append(string.Format("<td>{0}</td>",dr.GetValue(i)));
                        }
                    }
                   htmlStr.Append("</table>");
                   Response.Write(htmlStr);
               } while (dr.NextResult()) ;              }
            }
        }
    }   
}

Answer 1

using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Configuration; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; public partial class _Default : System.Web.UI.Page 
{ 
    protected void Page_Load(object sender, EventArgs e) 
    { 
        string sConnectionString = "server=.;database=fourm;trusted_connection=true"; 
      string sql = "select * from tbBoard ;select * from tbClass";      
        using (SqlConnection conn = new SqlConnection (sConnectionString)) 
        { 
            conn.Open(); 
            using (SqlCommand cmd = new SqlCommand (sql,conn)) 
            { 
              using(SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) 
            { 
              if(dr.HasRows) 
                { 
                  System.Text.StringBuilder htmlStr = new System.Text.StringBuilder();//把这个htmlstr的声明和初始化放到while循环的外边吧，在循环里边，读第二个记录集的时候又被初始化为空了。放到if(dr.HasRows)的上边就可以了。
                  htmlStr.Append(" <table border='3' cellPadding='5' cellSpacing='1' style='font-size:9pt;font:宋体'>"); 
                    htmlStr.Append(" <tr style='background-color=#F0F0F0'>"); 
                    for (int i = 0; i < dr.FieldCount; i++ ) 
                    {  
                        htmlStr.Append(string.Format(" <td> <strong>{0} </strong> </td>",dr.GetName(i))); 
                    } 
                    htmlStr.Append(" </tr>"); 
                    while (dr.Read()) 
                    { 
                        htmlStr.Append(" <tr>"); 
                        for (int i = 0; i < dr.FieldCount;i++ ) 
                        { 
                            if (!dr.IsDBNull(i))                            htmlStr.Append(string.Format(" <td>{0} </td>",dr.GetValue(i))); 
                        } 
                    } 
                  htmlStr.Append(" </table>"); 
                  Response.Write(htmlStr); 
              } while (dr.NextResult()) ;               } 
            } 
        } 
    }   
} 

Answer 2

把这个htmlstr的声明和初始化放到while循环的外边吧，在循环里边，读第二个记录集的时候又被初始化为空了。放到if(dr.HasRows)的上边就可以了

Answer 3

 string sql = "select * from tbBoard ;select * from tbClass";  
可这么写   厉害
学习了

Answer 4

呵呵~少了个do ....wile  循环，还是感谢大虾指点using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string sConnectionString = "server=.;database=fourm;trusted_connection=true";
        string sql = "select * from tbBoard ;select * from tbClass";        using (SqlConnection conn = new SqlConnection(sConnectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (dr.HasRows)
                    {
                        do
                        {
                            System.Text.StringBuilder htmlStr = new System.Text.StringBuilder();
                            htmlStr.Append("<table border='3' cellPadding='5' cellSpacing='1' style='font-size:9pt;font:宋体'>");
                            htmlStr.Append("<tr style='background-color=#F0F0F0'>");
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                htmlStr.Append(string.Format("<td><strong>{0}</strong></td>", dr.GetName(i)));
                            }
                            htmlStr.Append("</tr>");
                            while (dr.Read())
                            {
                                htmlStr.Append("<tr>");
                                for (int i = 0; i < dr.FieldCount; i++)
                                {
                                    if (!dr.IsDBNull(i))
                                        htmlStr.Append(string.Format("<td>{0}</td>", dr.GetValue(i)));
                                }
                            }
                            htmlStr.Append("</table>");
                            Response.Write(htmlStr);                        }
                        while (dr.NextResult());
                    }
                }
            }
        }
    }
}