关于codesmith的一点使用--自动生成数据库中所有表的存储过程使用模板

最新推荐文章于 2022-11-04 15:46:09 发布

置顶 bendan2008

最新推荐文章于 2022-11-04 15:46:09 发布

阅读量1.2k

点赞数

文章标签：数据库存储 extension assembly string database

本文链接：https://blog.csdn.net/bendan2008/article/details/3943325

版权

刚学用codesmith不久,错误在所难免,有错误欢迎指正:共同进步;

当我们在写程序时会为了经常写同样的代码或类似的代码而烦恼,当然现在不在烦恼了,像codesmith这一类的软件很多了,我经常使用是这档软件,使用起来还挺上手;

下面我介绍一下一个关于生成数据库中全部表的SQL更新语句的使用例子,比照这个例子大家可以实现类似的SELECT,INSERT等使用例子;

下面开始编写模板:

使用语言C#,codesmith版本5.0.5,数据库为:SQL SERVER2000;

<%@ Property Name=" SourceDatabase" Type="SchemaExplorer.TableSchema" Default="" Optional="False" Category="Context" Description="the name of SourceDatabase" OnChanged="" Editor="" EditorBase="" Serializer="" %>

在与数据库进行交互时,我们使用到了一个CodeSmith自代的组件SchemaExplorer,利用这个组件我们可以访问数据库的数据表,存储过程,视图等,并可以得到相应的数据类型,标识列,列的(字段)名称等信息.

下面是生成整个表的存储过程,虽然网上有很多的例子,CODESMITH英文帮助中也有,但很少生成所有表的存储过程:今天我们要活用codesmith中给我们例子:

第一步指明使用的语言和生成的目标语言.

<%@ CodeTemplate Language=" C#" TargetLanguage="T-SQL" Src="" Inherits="" Debug="False" LinePragmas="True" Description="List database tables" %>

这上面的这些菏都能看懂吧:代码模板使用语言C#,生成T-SQL语句,其他的不说了......自己看,一看就明白

第二步就是加载要使用访问数据库的组件SchemaExploer(注意codesmith中严格区分大小写),并声明其使用的命名空间.

<%@ Assembly Name=" SchemaExplorer" %>
<%@ Import Namespace=" SchemaExplorer" %>

第三步是声明使用的变量

使用for结构:

<% for (int i=0;i<SourceDatabase.Database .Tables .Count ;i++)%>
         <%{%>

    <%=SourceDatabase.Database .Tables [i].Name   %>

CREATE PROCEDURE dbo.Update<%=SourceDatabase.Database .Tables [i].Name %>
<%for (int j=0;j<SourceDatabase.Database .Tables [i].Columns.Count ;j++)%>
        <%{%>
  <%=GetSqlParameterStatement(SourceDatabase.Database .Tables [i].Columns [j])%>
        <%}%>
  UPDATA [<%=SourceDatabase.Database .Tables [i].Name %>] set
         <% for (int w=0;w<SourceDatabase.Database .Tables [i].NonForeignKeyColumns .Count ;w++)%>
      <%{%>
      [<%=SourceDatabase.Database .Tables [i].NonForeignKeyColumns [w].Name %>]=@<%=SourceDatabase.Database .Tables [i].NonForeignKeyColumns [w].Name %>

       <%if(w<SourceDatabase.Database .Tables[i].NonForeignKeyColumns .Count -1)%>
        <%{%>,<%}%>

      <%}%>
      where
      <%for(int z=0;z<SourceDatabase.Database .Tables [i].PrimaryKey .MemberColumns .Count ;z++)%>
           <%{%>
                 <%if(z>0){%> AND <%}%>
       [<%=SourceDatabase.Database .Tables [i].PrimaryKey .MemberColumns[z].Name %>]=@<%=SourceDatabase.Database .Tables [i].PrimaryKey .MemberColumns [z].Name %>
      <%-- [<%=SourceDatabase.Database .Tables [i].PrimaryKey .MemberColumns [z].Name %>]=@<%=SourceDatabase.Database .Tables [i].PrimaryKey .MemberColumns [z].Name %> --%>
      <%}%>
<%}%>

在这其中使用到一个函数,写在<script></script>中

下面是代码:

{
string param="@"+column.Name +"   "+column.NativeType ;
switch(column.DataType )
{
  case DbType.Decimal:
  {
   param+="("+column.Precision +","+column.Scale +")";
   //param+="("+column.Precision ;
   break;
   }
  default:
   {
    if(column.Size >0)
    {
     param+="("+column.Size +")";
     }
    break;

  }
}

return param;
}

</script>

下面是整个的完整代码:::::::::::::::::::::::::::::

<%@ CodeTemplate Language=" C#" TargetLanguage="T-SQL" Src="" Inherits="" Debug="False" LinePragmas="True" Description="List database tables" %>
<%@ Property Name=" SourceDatabase" Type="SchemaExplorer.TableSchema" Default="" Optional="False" Category="Context" Description="the name of SourceDatabase" OnChanged="" Editor="" EditorBase="" Serializer="" %>

<%@ Assembly Name=" SchemaExplorer" %>
<%@ Import Namespace=" SchemaExplorer" %>

<% for (int i=0;i<SourceDatabase.Database .Tables .Count ;i++)%>
         <%{%>

    <%=SourceDatabase.Database .Tables [i].Name   %>

我们使用Nothwind作为例子:生成的存储过程如下::

    Categories
CREATE PROCEDURE dbo.UpdateCategories
  @CategoryID   int(4)
  @CategoryName   nvarchar(15)
  @Description   ntext(16)
  @Picture   image(16)
  UPDATA [Categories] set
      [CategoryID]=@CategoryID
               ,
      [CategoryName]=@CategoryName
               ,
      [Description]=@Description
               ,
      [Picture]=@Picture
,
      where

       [CategoryID]=@CategoryID
    CustomerCustomerDemo
CREATE PROCEDURE dbo.UpdateCustomerCustomerDemo
  @CustomerID   nchar(5)
  @CustomerTypeID   nchar(10)
  UPDATA [CustomerCustomerDemo] set
      where

       [CustomerID]=@CustomerID
                  AND
       [CustomerTypeID]=@CustomerTypeID
    CustomerDemographics
CREATE PROCEDURE dbo.UpdateCustomerDemographics
  @CustomerTypeID   nchar(10)
  @CustomerDesc   ntext(16)
  UPDATA [CustomerDemographics] set
      [CustomerTypeID]=@CustomerTypeID
               ,
      [CustomerDesc]=@CustomerDesc
,
      where

       [CustomerTypeID]=@CustomerTypeID
    Customers
CREATE PROCEDURE dbo.UpdateCustomers
  @CustomerID   nchar(5)
  @CompanyName   nvarchar(40)
  @ContactName   nvarchar(30)
  @ContactTitle   nvarchar(30)
  @Address   nvarchar(60)
  @City   nvarchar(15)
  @Region   nvarchar(15)
  @PostalCode   nvarchar(10)
  @Country   nvarchar(15)
  @Phone   nvarchar(24)
  @Fax   nvarchar(24)
  UPDATA [Customers] set
      [CustomerID]=@CustomerID
               ,
      [CompanyName]=@CompanyName
               ,
      [ContactName]=@ContactName
               ,
      [ContactTitle]=@ContactTitle
               ,
      [Address]=@Address
               ,
      [City]=@City
               ,
      [Region]=@Region
               ,
      [PostalCode]=@PostalCode
               ,
      [Country]=@Country
               ,
      [Phone]=@Phone
               ,
      [Fax]=@Fax
,
      where

       [CustomerID]=@CustomerID
    Employees
CREATE PROCEDURE dbo.UpdateEmployees
  @EmployeeID   int(4)
  @LastName   nvarchar(20)
  @FirstName   nvarchar(10)
  @Title   nvarchar(30)
  @TitleOfCourtesy   nvarchar(25)
  @BirthDate   datetime(8)
  @HireDate   datetime(8)
  @Address   nvarchar(60)
  @City   nvarchar(15)
  @Region   nvarchar(15)
  @PostalCode   nvarchar(10)
  @Country   nvarchar(15)
  @HomePhone   nvarchar(24)
  @Extension   nvarchar(4)
  @Photo   image(16)
  @Notes   ntext(16)
  @ReportsTo   int(4)
  @PhotoPath   nvarchar(255)
  UPDATA [Employees] set
      [EmployeeID]=@EmployeeID
               ,
      [LastName]=@LastName
               ,
      [FirstName]=@FirstName
               ,
      [Title]=@Title
               ,
      [TitleOfCourtesy]=@TitleOfCourtesy
               ,
      [BirthDate]=@BirthDate
               ,
      [HireDate]=@HireDate
               ,
      [Address]=@Address
               ,
      [City]=@City
               ,
      [Region]=@Region
               ,
      [PostalCode]=@PostalCode
               ,
      [Country]=@Country
               ,
      [HomePhone]=@HomePhone
               ,
      [Extension]=@Extension
               ,
      [Photo]=@Photo
               ,
      [Notes]=@Notes
               ,
      [PhotoPath]=@PhotoPath
,
      where

       [EmployeeID]=@EmployeeID
    EmployeeTerritories
CREATE PROCEDURE dbo.UpdateEmployeeTerritories
  @EmployeeID   int(4)
  @TerritoryID   nvarchar(20)
  UPDATA [EmployeeTerritories] set
      where

       [EmployeeID]=@EmployeeID
                  AND
       [TerritoryID]=@TerritoryID
    Order Details
CREATE PROCEDURE dbo.UpdateOrder Details
  @OrderID   int(4)
  @ProductID   int(4)
  @UnitPrice   money(8)
  @Quantity   smallint(2)
  @Discount   real(4)
  UPDATA [Order Details] set
      [UnitPrice]=@UnitPrice
               ,
      [Quantity]=@Quantity
               ,
      [Discount]=@Discount
,
      where

       [OrderID]=@OrderID
                  AND
       [ProductID]=@ProductID
    Orders
CREATE PROCEDURE dbo.UpdateOrders
  @OrderID   int(4)
  @CustomerID   nchar(5)
  @EmployeeID   int(4)
  @OrderDate   datetime(8)
  @RequiredDate   datetime(8)
  @ShippedDate   datetime(8)
  @ShipVia   int(4)
  @Freight   money(8)
  @ShipName   nvarchar(40)
  @ShipAddress   nvarchar(60)
  @ShipCity   nvarchar(15)
  @ShipRegion   nvarchar(15)
  @ShipPostalCode   nvarchar(10)
  @ShipCountry   nvarchar(15)
  @TestID   decimal(18,0)
  UPDATA [Orders] set
      [OrderID]=@OrderID
               ,
      [OrderDate]=@OrderDate
               ,
      [RequiredDate]=@RequiredDate
               ,
      [ShippedDate]=@ShippedDate
               ,
      [Freight]=@Freight
               ,
      [ShipName]=@ShipName
               ,
      [ShipAddress]=@ShipAddress
               ,
      [ShipCity]=@ShipCity
               ,
      [ShipRegion]=@ShipRegion
               ,
      [ShipPostalCode]=@ShipPostalCode
               ,
      [ShipCountry]=@ShipCountry
               ,
      [TestID]=@TestID
,
      where

       [OrderID]=@OrderID
    Products
CREATE PROCEDURE dbo.UpdateProducts
  @ProductID   int(4)
  @ProductName   nvarchar(40)
  @SupplierID   int(4)
  @CategoryID   int(4)
  @QuantityPerUnit   nvarchar(20)
  @UnitPrice   money(8)
  @UnitsInStock   smallint(2)
  @UnitsOnOrder   smallint(2)
  @ReorderLevel   smallint(2)
  @Discontinued   bit(1)
  UPDATA [Products] set
      [ProductID]=@ProductID
               ,
      [ProductName]=@ProductName
               ,
      [QuantityPerUnit]=@QuantityPerUnit
               ,
      [UnitPrice]=@UnitPrice
               ,
      [UnitsInStock]=@UnitsInStock
               ,
      [UnitsOnOrder]=@UnitsOnOrder
               ,
      [ReorderLevel]=@ReorderLevel
               ,
      [Discontinued]=@Discontinued
,
      where

       [ProductID]=@ProductID
    Region
CREATE PROCEDURE dbo.UpdateRegion
  @RegionID   int(4)
  @RegionDescription   nchar(50)
  UPDATA [Region] set
      [RegionID]=@RegionID
               ,
      [RegionDescription]=@RegionDescription
,
      where

       [RegionID]=@RegionID
    Shippers
CREATE PROCEDURE dbo.UpdateShippers
  @ShipperID   int(4)
  @CompanyName   nvarchar(40)
  @Phone   nvarchar(24)
  UPDATA [Shippers] set
      [ShipperID]=@ShipperID
               ,
      [CompanyName]=@CompanyName
               ,
      [Phone]=@Phone
,
      where

       [ShipperID]=@ShipperID
    Suppliers
CREATE PROCEDURE dbo.UpdateSuppliers
  @SupplierID   int(4)
  @CompanyName   nvarchar(40)
  @ContactName   nvarchar(30)
  @ContactTitle   nvarchar(30)
  @Address   nvarchar(60)
  @City   nvarchar(15)
  @Region   nvarchar(15)
  @PostalCode   nvarchar(10)
  @Country   nvarchar(15)
  @Phone   nvarchar(24)
  @Fax   nvarchar(24)
  @HomePage   ntext(16)
  UPDATA [Suppliers] set
      [SupplierID]=@SupplierID
               ,
      [CompanyName]=@CompanyName
               ,
      [ContactName]=@ContactName
               ,
      [ContactTitle]=@ContactTitle
               ,
      [Address]=@Address
               ,
      [City]=@City
               ,
      [Region]=@Region
               ,
      [PostalCode]=@PostalCode
               ,
      [Country]=@Country
               ,
      [Phone]=@Phone
               ,
      [Fax]=@Fax
               ,
      [HomePage]=@HomePage
,
      where

       [SupplierID]=@SupplierID
    Territories
CREATE PROCEDURE dbo.UpdateTerritories
  @TerritoryID   nvarchar(20)
  @TerritoryDescription   nchar(50)
  @RegionID   int(4)
  UPDATA [Territories] set
      [TerritoryID]=@TerritoryID
               ,
      [TerritoryDescription]=@TerritoryDescription
,
      where

       [TerritoryID]=@TerritoryID

这其中还是有些不足之处的,在使用生成的代码时,要在稍微改动一下供使用;还有整个的代码没有加注,希望大家耐心一点,还是很明白的,以后我在整理一下,在发吧!今天就写到!

我的金山到期了,有哪位仁兄有2009的通行证各密码;我邮箱:hanbquan@126.com;谢谢

bendan2008

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于codesmith的一点使用--自动生成数据库中所有表的存储过程使用模板

刚学用codesmith不久,错误在所难免,有错误欢迎指正:共同进步;当我们在写程序时会为了经常写同样的代码或类似的代码而烦恼,当然现在不在烦恼了,像codesmith这一类的软件很多了,我经常使用是这档软件,使用起来还挺上手;下面我介绍一下一个关于生成数据库中全部表的SQL更新语句的使用例子,比照这个例子大家可以实现类似的SELECT,INSERT等使用例子;下面开始编写模板:使
复制链接

扫一扫