Returning Multiple Resultsets : DataReader « ADO.net Database « ASP.NET Tutorial

Home
ASP.NET Tutorial
1.ASP.Net Instroduction
2.Language Basics
3.ASP.net Controls
4.HTML Controls
5.Page Lifecycle
6.Response
7.Collections
8.Validation
9.Development
10.File Directory
11.Sessions
12.Cookie
13.Cache
14.Custom Controls
15.Profile
16.Configuration
17.LINQ
18.ADO.net Database
19.Data Binding
20.Ajax
21.Authentication Authorization
22.I18N
23.Mobile
24.WebPart
25.XML
ASP.NET Tutorial » ADO.net Database » DataReader 
18.24.5.Returning Multiple Resultsets
File: App_Code\DataLayer1.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;

public class DataLayer1
{
    private static readonly string _connectionString;

    public class ProductCategory
    {
        private int _id;
        private string _name;        public int Id
        {
            get return _id; }
            set _id = value; }
        }

        public string Name
        {
            get return _name; }
            set _name = value; }
        }
    }

    public class Product
    {
        private string _title;
        private int _categoryId;

        public string Title
        {
            get return _title; }
            set _title = value; }
        }

        public int CategoryId
        {
            get return _categoryId; }
            set _categoryId = value; }
        }
    }

    public static void GetProductData(List<DataLayer1.ProductCategory> productCategories, List<DataLayer1.Product> products)
    {
        string commandText = "SELECT Id,Name FROM ProductCategories;SELECT Title, CategoryId FROM Products";
        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand(commandText, con);
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DataLayer1.ProductCategory newCategory = new DataLayer1.ProductCategory();
                newCategory.Id = (int)reader["Id"];
                newCategory.Name = (string)reader["Name"];
                productCategories.Add(newCategory);
            }

            reader.NextResult();

            while (reader.Read())
            {
                DataLayer1.Product newProduct = new DataLayer1.Product();
                newProduct.Title = (string)reader["Title"];
                newProduct.CategoryId = (int)reader["CategoryID"];
                products.Add(newProduct);
            }
        }
    }

    static DataLayer1()
    {
        _connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
    }
}

            
File: Web.config

<configuration>
  <connectionStrings>
    <add name="Products" 
         connectionString="Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|MyDatabase.mdf;Integrated Security=True;User Instance=True" />
  </connectionStrings>
</configuration>

File: ShowDataLayer1.aspx

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    void Page_Load()
    {
        List<DataLayer1.ProductCategory> categories = new List<DataLayer1.ProductCategory>();
        List<DataLayer1.Product> products = new List<DataLayer1.Product>();
        DataLayer1.GetProductData(categories, products);

        grdCategories.DataSource = categories;
        grdCategories.DataBind();
        grdProducts.DataSource = products;
        grdProducts.DataBind();
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show DataLayer1</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <h1>Product Categories</h1>
    <asp:GridView
        id="grdCategories"
        Runat="server" />

    <h1>Products</h1>
    <asp:GridView
        id="grdProducts"
        Runat="server" />

    </div>
    </form>
</body>
</html>
18.24.DataReader
18.24.1.Using the DataReader Object
18.24.2.Iterating Through A DataReader
18.24.3.Create DataReader object from SqlCommand
18.24.4.List Binding DataReader
18.24.5.Returning Multiple Resultsets
18.24.6.Working with Multiple Active Resultsets
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.