Building-a-Data-Access-Layer-with-a-UserDefined-Type


            File: App_Code\DBDataLayer.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;
public class DBDataLayer
{
    private static readonly string _connectionString;
    public List GetAll()
    {
        List results = new List();
        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand("SELECT Product FROM DBProducts", con);
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DBProduct newProduct = (DBProduct)reader["Product"];
                results.Add(newProduct);
            }
        }
        return results;
    }
    public void Insert(DBProduct productToAdd)
    {
        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand("INSERT DBProducts (Product) VALUES (@Product)", con);
        cmd.Parameters.Add("@Product", SqlDbType.Udt);
        cmd.Parameters["@Product"].UdtTypeName = "DBProduct";
        cmd.Parameters["@Product"].Value = productToAdd;
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }    
    static DBDataLayer()
    {
        _connectionString = WebConfigurationManager.ConnectionStrings["Products"].ConnectionString;
    }
}
File: ShowDBDataLayer.aspx
<%@ Page Language="C#" %>
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


    Show DBDataLayer


    
    
            id="grdProducts"
        DataSourceID="srcProducts"
        Runat="server" />
    
    
    Add Product
            id="dtlProduct"
        DataSourceID="srcProducts"
        DefaultMode="Insert"
        AutoGenerateInsertButton="true"
        AutoGenerateRows="false"
        Runat="server">
        
        
        
                   HeaderText="Box Office Totals" />
        
    
    

            id="srcProducts"
        TypeName="DBDataLayer"
        DataObjectTypeName="DBProduct"
        SelectMethod="GetAll"
        InsertMethod="Insert"
        Runat="server" />
ADO Net Database ASP.Net Tutorial
