SQL server data access layer code generation tool (1)

A self-written tool that can be used to reduce repetitive work and give full play to the programmer's ability to "copy-paste. If you have any shortcomings, please consult more.

 

1. After installation, open the interface as follows:

After selecting the "northwind" database, the tables, stored procedures, and views are displayed in the lower left corner,

2. Select the tab "table" and select the "Orders" table.

The generated insert update statement is as follows. Copy it to the query analyzer and modify it.

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

 

The generated Orders class is as follows:

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 attribute 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 ShipPost AlCode {get {return _ ShipPostalCode;} set {_ ShipPostalCode = value ;}} public string ShipCountry {get {return _ ShipCountry ;}set {_ ShipCountry = value ;}} # endregionpublic Orders () {} public Orders (IDataReader dr) {# region initializes if (dr ["OrderID"]! = DBNull. Value) _ OrderID = (int) dr ["OrderID"]; if (dr ["mermerid"]! = 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 ;}}}

 

3. Select the "Stored Procedure" tab and select "Ten Most Expensive Products" stored procedure.

Stored Procedure Code:

create procedure "Ten Most Expensive Products" ASSET ROWCOUNT 10SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPriceFROM ProductsORDER BY Products.UnitPrice DESC

　　

Generate the corresponding method:

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

4. Select the "View" tab and select the "Customer and Suppliers by City" view,

Generate the corresponding class as follows, and modify some parts by yourself

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 attribute public string City {get {return _ City;} set {_ City = value ;}} public string CompanyName {get {return _ Compan YName ;}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 initializes 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 ;}}}

Download and install idea