sql server 資料訪問層 代碼產生工具（一）

自己寫的一個小工具，用它以減少重複勞動，充分發揮程式員“複製－粘貼”的能力。有不足之處請多多請教。

 

１.安裝之後開啟介面如下：

選擇“northwind”資料庫後，左下會出現各個表、預存程序、以及視圖，

２.選擇選項卡“表”，選擇“Orders” 表， 右邊會出現

產生的插入更新語句如下，把它複製到查詢分析器中修改一下即可用

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))asbegin  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)  endend

 

產生的Orders類如下

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; } }#endregionpublic 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;}}}

 

３.選擇選項卡“預存程序”，選擇“Ten Most Expensive Products” 預存程序，右邊會出現

預存程序代碼：

create procedure "Ten Most Expensive Products" ASSET ROWCOUNT 10SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPriceFROM ProductsORDER 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;}

４.選擇選項卡“視圖”，選擇“Customer and Suppliers by City”視圖，

產生對應的類如下，有些部分要自己修改

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; } }#endregionpublic 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;}}}

下載　安裝　http://cid-37232e9db6217cbf.office.live.com/self.aspx/.Public/%e4%b8%8b%e8%bd%bd/SP^_100509.rar