刚学用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" %>
第三步是声明使用的变量
<%@ Property Name=" SourceDatabase" Type="SchemaExplorer.TableSchema" Default="" Optional="False" Category="Context" Description="the name of SourceDatabase" OnChanged="" Editor="" EditorBase="" Serializer="" %>
使用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>中
下面是代码:
<script runat ="template">
public string GetSqlParameterStatement(ColumnSchema column)
{
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 %>
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 runat ="template">
public string GetSqlParameterStatement(ColumnSchema column)
{
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>
我们使用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;谢谢