Search This Blog

Monday, April 8, 2013

How to work with multiple result sets using SqlDataReader?

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)
{
// get the connection
using (SqlConnection conn = new SqlConnection(_connStr))
{
// write the sql statement to execute
string sql = "SELECT AutoID, FirstName, LastName, Active FROM
PersonalDetail ORDER BY FirstName; " +
"SELECT AutoId, FileName FROM Files Order By FileName ASC";
// instantiate the command object to fire
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
// in case of multiple result set you will not be able to specify the
CommandBehavior
// if so you will not be able to get the next result set from the
reader
using (SqlDataReader reader = cmd.ExecuteReader())
{
// bind the first resultset
GridView1.DataSource = reader;
GridView1.DataBind();

reader.NextResult();

// bind the second resultset
GridView2.DataSource = reader;
GridView2.DataBind();
}
conn.Close();
}
}
}

No comments:

Post a Comment