sql server 数据访问层 代码生成工具（一）

create procedure sp_Orders_ins
(
@OrderID int,
@CustomerID nchar(5),
@EmployeeID int,
@OrderDate datetime,
@RequiredDate datetime,
@ShippedDate datetime,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40),
@ShipAddress nvarchar(60),
@ShipCity nvarchar(15),
@ShipRegion nvarchar(15),
@ShipPostalCode nvarchar(10),
@ShipCountry nvarchar(15)
)
as
begin
  if exists(select * from [Orders] where )
  begin
    update [Orders] set
    [OrderID]=@OrderID,
    [CustomerID]=@CustomerID,
    [EmployeeID]=@EmployeeID,
    [OrderDate]=@OrderDate,
    [RequiredDate]=@RequiredDate,
    [ShippedDate]=@ShippedDate,
    [ShipVia]=@ShipVia,
    [Freight]=@Freight,
    [ShipName]=@ShipName,
    [ShipAddress]=@ShipAddress,
    [ShipCity]=@ShipCity,
    [ShipRegion]=@ShipRegion,
    [ShipPostalCode]=@ShipPostalCode,
    [ShipCountry]=@ShipCountry
    where


  end
  else
  begin
    insert into [Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],[ShipCountry])
    values(@OrderID,@CustomerID,@EmployeeID,@OrderDate,@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry)
  end
end

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;

namespace DAL
{
[Serializable]
public class Orders
{
public int _OrderID;
public string _CustomerID;
public int? _EmployeeID;
public DateTime? _OrderDate;
public DateTime? _RequiredDate;
public DateTime? _ShippedDate;
public int? _ShipVia;
public decimal? _Freight;
public string _ShipName;
public string _ShipAddress;
public string _ShipCity;
public string _ShipRegion;
public string _ShipPostalCode;
public string _ShipCountry;

#region 属性
public int OrderID
{
get { return _OrderID; }
set { _OrderID = value; } 
}
public string CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; } 
}
public int? EmployeeID
{
get { return _EmployeeID; }
set { _EmployeeID = value; } 
}
public DateTime? OrderDate
{
get { return _OrderDate; }
set { _OrderDate = value; } 
}
public DateTime? RequiredDate
{
get { return _RequiredDate; }
set { _RequiredDate = value; } 
}
public DateTime? ShippedDate
{
get { return _ShippedDate; }
set { _ShippedDate = value; } 
}
public int? ShipVia
{
get { return _ShipVia; }
set { _ShipVia = value; } 
}
public decimal? Freight
{
get { return _Freight; }
set { _Freight = value; } 
}
public string ShipName
{
get { return _ShipName; }
set { _ShipName = value; } 
}
public string ShipAddress
{
get { return _ShipAddress; }
set { _ShipAddress = value; } 
}
public string ShipCity
{
get { return _ShipCity; }
set { _ShipCity = value; } 
}
public string ShipRegion
{
get { return _ShipRegion; }
set { _ShipRegion = value; } 
}
public string ShipPostalCode
{
get { return _ShipPostalCode; }
set { _ShipPostalCode = value; } 
}
public string ShipCountry
{
get { return _ShipCountry; }
set { _ShipCountry = value; } 
}
#endregion
public Orders()
{}
public Orders(IDataReader dr)
{
#region 初始化
if(dr["OrderID"] != DBNull.Value)
_OrderID = (int)dr["OrderID"];

if(dr["CustomerID"] != DBNull.Value)
_CustomerID = (string)dr["CustomerID"];

if(dr["EmployeeID"] != DBNull.Value)
_EmployeeID = (int?)dr["EmployeeID"];

if(dr["OrderDate"] != DBNull.Value)
_OrderDate = (DateTime?)dr["OrderDate"];

if(dr["RequiredDate"] != DBNull.Value)
_RequiredDate = (DateTime?)dr["RequiredDate"];

if(dr["ShippedDate"] != DBNull.Value)
_ShippedDate = (DateTime?)dr["ShippedDate"];

if(dr["ShipVia"] != DBNull.Value)
_ShipVia = (int?)dr["ShipVia"];

if(dr["Freight"] != DBNull.Value)
_Freight = (decimal?)dr["Freight"];

if(dr["ShipName"] != DBNull.Value)
_ShipName = (string)dr["ShipName"];

if(dr["ShipAddress"] != DBNull.Value)
_ShipAddress = (string)dr["ShipAddress"];

if(dr["ShipCity"] != DBNull.Value)
_ShipCity = (string)dr["ShipCity"];

if(dr["ShipRegion"] != DBNull.Value)
_ShipRegion = (string)dr["ShipRegion"];

if(dr["ShipPostalCode"] != DBNull.Value)
_ShipPostalCode = (string)dr["ShipPostalCode"];

if(dr["ShipCountry"] != DBNull.Value)
_ShipCountry = (string)dr["ShipCountry"];

#endregion
}

public static int Insert(Orders a)
{
return 0;
}
public static IDataReader Select()
{
return null;
}
public static  IEnumerable<Orders> Each(IDataReader dr)
{
while(dr.Read()) { yield return new Orders(dr); } dr.Close();
}
public static Orders First(IDataReader dr)
{
Orders ret = null; if (dr.Read()) { ret = new Orders(dr); } dr.Close(); return ret;
}
}
}

create procedure "Ten Most Expensive Products" AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

public static IDataReader Ten Most Expensive Products()
{
IDataReader dr;
Database db = new Database();
DbCommand cmd = db.GetStoredProcCommand("Ten Most Expensive Products");
dr = db.ExecuteReader(cmd);
return dr;

}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;

namespace DAL
{
[Serializable]
public class Customer and Suppliers by City
{
public string _City;
public string _CompanyName;
public string _ContactName;
public string _Relationship;

#region 属性
public string City
{
get { return _City; }
set { _City = value; } 
}
public string CompanyName
{
get { return _CompanyName; }
set { _CompanyName = value; } 
}
public string ContactName
{
get { return _ContactName; }
set { _ContactName = value; } 
}
public string Relationship
{
get { return _Relationship; }
set { _Relationship = value; } 
}
#endregion
public Customer and Suppliers by City()
{}
public Customer and Suppliers by City(IDataReader dr)
{
#region 初始化
if(dr["City"] != DBNull.Value)
_City = (string)dr["City"];

if(dr["CompanyName"] != DBNull.Value)
_CompanyName = (string)dr["CompanyName"];

if(dr["ContactName"] != DBNull.Value)
_ContactName = (string)dr["ContactName"];

if(dr["Relationship"] != DBNull.Value)
_Relationship = (string)dr["Relationship"];

#endregion
}

public static int Insert(Customer and Suppliers by City a)
{
return 0;
}
public static IDataReader Select()
{
return null;
}
public static  IEnumerable<Customer and Suppliers by City> Each(IDataReader dr)
{
while(dr.Read()) { yield return new Customer and Suppliers by City(dr); } dr.Close();
}
public static Customer and Suppliers by City First(IDataReader dr)
{
Customer and Suppliers by City ret = null; if (dr.Read()) { ret = new Customer and Suppliers by City(dr); } dr.Close(); return ret;
}
}
}