codesmith oracle 存储过程,CodeSmith模板（生成SQL脚本和数据）

Name:产生数据库的SQL脚本

Author: Jack.zhou

Description:

--%>

TargetLanguage="T-SQL" Src="" Inherits="" Debug="False"

Description="Template description here."

ResponseEncoding="UTF-8"%>

Type="SchemaExplorer.DatabaseSchema" Default="" Optional="False"

Category="Context" Description="" OnChanged="" Editor=""

EditorBase="" Serializer="" %>

Default="NewDB" Optional="False" Category="Context" Description=""

OnChanged="" Editor="" EditorBase="" Serializer=""

%>

Type="System.Boolean" Default="False" Optional="False"

Category="Context" Description="" OnChanged="" Editor=""

EditorBase="" Serializer="" %>

%>

%>

%>

%>

%>

Use master

Go

--region

判断数据库是否存在，如果存在，那么删除这个数据库

IF Exists(Select '' From sysdatabases Where

name='')

Begin

Drop Database

[]

End

Go

--endregion

--region

创建数据库

Create Database

[]

Go

Use []

Go

--endregion

--region 创建自定义类型

UserDefineTypeCollectionudtc=new UserDefineTypeCollection(this.SourceDB);

foreach(UserDefineType udt in udtc.UserDefineTypes)

{

//Response.WriteLine(udt.ToString());

}

%>

Go

--endregion

this.SourceDB.Tables){%>

--region

判断数据表[]是否存在，如果存在，那么删除这个数据表

If Exists(Select '' From sysobjects Where

name='')

Begin

Drop Table

[]

End

Go

--endregion

this.SourceDB.Tables){%>

--region

创建数据表

Create Table []

(

i=0;i

,

)

Go

--endregion

--region 添加数据

use New

Go

this.SourceDB.Tables){%>

DBCC CheckIdent ('[

%>].[

%>]', reseed, 1)

Set Identity_Insert

[].[]

On

foreach(DataRow dr in dt.Rows){%>

Insert Into

[].[]()

Values()

Set Identity_Insert

[].[]

Off 

Go

--endregion

this.SourceDB.Tables){%>

--region

为[]添加主键约束

Alter Table [] Add Constraint

Primary

Key ()

Go

--endregion

this.SourceDB.Tables){%>

--region

为[]添加外键约束

table.ForeignKeys){%>

Alter Table [] Add Constraint

Foreign Key

Go

--endregion

this.SourceDB.Tables){%>

--region

为[]添加其他约束

table.Columns){%>

Go

--endregion

this.SourceDB.Tables){%>

--region

为[]添加索引

table.Indexes){%>

Create Index

[] On

[]

()

Go

--endregion

this.SourceDB.Views){%>

--region

创建视图

Go

--endregion

this.SourceDB.Commands){%>

--region

创建存储过程

Go

--endregion

--region 创建触发器

this.SourceDB.Tables){%>

TriggerCollection(table);%>

triggers.Triggers){%>

--region

创建触发器

--endregion

Go

--endregion

#region 生成数据库结构

public string GetIndexOption(IndexSchema index)

{

string s="With ";

if(int.Parse(index.ExtendedProperties["CS_OrigFillFactor"].Value.ToString())!=0)

{

s+="FillFactor="+int.Parse(index.ExtendedProperties["CS_OrigFillFactor"].Value.ToString())+",";

}

if(bool.Parse(index.ExtendedProperties["CS_PadIndex"].Value.ToString()))

{

s+="PAD_INDEX,";

}

if(bool.Parse(index.ExtendedProperties["CS_IgnoreDupKey"].Value.ToString()))

{

s+="IGNORE_DUP_KEY,";

}

if(bool.Parse(index.ExtendedProperties["CS_DropExist"].Value.ToString()))

{

s+="DROP_EXISTING,";

}

if(s.Length==5)

{

return "";

}

return s.Substring(0,s.Length-1);

}

public string GetIndexColumn(IndexSchema index)

{

string s="";

foreach(MemberColumnSchema column in

index.MemberColumns)

{

if((int)column.ExtendedProperties["CS_IsDescending"].Value==0)

{

s+=column.Name+",";

}

else

{

s+=column.Name+"

Desc,";

}

}

if(s=="")

{

return s;

}

return s.Substring(0,s.Length-1);

}

public string GetIndexType(IndexSchema index)

{

if(index.IsClustered==true

&& index.IsPrimaryKey==true

&& index.IsUnique==true)

{

return "";

}

else if(index.IsClustered==false

&& index.IsPrimaryKey==false

&& index.IsUnique==false)

{

return "NonClustered";

}

else if(index.IsClustered==false

&& index.IsPrimaryKey==false

&& index.IsUnique==true)

{

return "Unique";

}

return "";

}

public string GetColumn(ColumnSchema column)

{

string s="";

s+="["+column.Name+"] "+column.NativeType+"

";

if(IsId(column))

{

s+="identity("+column.ExtendedProperties["CS_IdentitySeed"].Value+","+column.ExtendedProperties["CS_IdentityIncrement"].Value+")

";

}

if(HasLength(column))

{

s+="("+column.Size+") ";

}

if(column.AllowDBNull)

{

s+="null ";

}

else

{

s+="not null";

}

return s;

}

public bool IsId(ColumnSchema column)

{

return

(bool)column.ExtendedProperties["CS_IsIdentity"].Value;

}

public bool HasLength(ColumnSchema column)

{

switch(column.NativeType)

{

case "char":

case "varchar":

case "nchar":

case "nvarchar":

return

true;

default:

return

false;

}

}

public string GetPkString(TableSchema table)

{

string s="";

foreach(MemberColumnSchema column in

table.PrimaryKey.MemberColumns)

{

s+="["+column.Name+"]"+",";

}

return s.Substring(0,s.Length-1);

}

public string GetFKString(TableKeySchema key)

{

string s="(";

foreach(MemberColumnSchema column in

key.ForeignKeyMemberColumns)

{

s+="["+column.Name+"],";

}

s=s.Substring(0,s.Length-1);

s+=")";

s+=" References

["+key.PrimaryKeyTable.Name+"](";

foreach(MemberColumnSchema column in

key.PrimaryKeyMemberColumns)

{

s+="["+column.Name+"],";

}

s=s.Substring(0,s.Length-1);

s+=")";

return s;

}

public string GetOtherConstrain(ColumnSchema column)

{

string s="";

int i=1;

while(true)

{

if(column.ExtendedProperties["CS_Constraint"+i+"Name"]==null)

{

break;

}

if(column.ExtendedProperties["CS_Constraint"+i+"Type"].Value.ToString().ToUpper().Trim()=="DEFAULT")

{

s+="Alter

Table ["+column.Table.Name+"] Add Constraint

"+column.ExtendedProperties["CS_Constraint"+i+"Name"].Value+"

Default

"+column.ExtendedProperties["CS_Constraint"+i+"Definition"].Value.ToString()+"

For ["+column.Name+"]\r\n";

}

else

{

s+="Alter

Table ["+column.Table.Name+"] Add Constraint

"+column.ExtendedProperties["CS_Constraint"+i+"Name"].Value+" Check

"+column.ExtendedProperties["CS_Constraint"+i+"Definition"].Value.ToString()+"\r\n";

}

i++;

}

if(s=="")

{

return null;

}

return s;

}

#endregion

#region 产生数据

public bool HasIdentity(TableSchema table)

{

foreach(ColumnSchema column in

table.Columns)

{

if((bool)column.ExtendedProperties["CS_IsIdentity"].Value)

{

return

true;

}

}

return false;

}

public string GetData(DataRow dr,TableSchema table)

{

string s="";

foreach(ColumnSchema column in

table.Columns)

{

if(dr[column.Name] is

System.DBNull)

{

s+="null,";

}

else

{

if(IsNumeric(column))

{

if(column.SystemType.ToString()!="System.Boolean")

{

s+=FormatString(dr[column.Name].ToString())+",";

}

else

{

s+=

(((bool)dr[column.Name])==true?1:0) +",";

}

}

else

{

if(column.SystemType.ToString()!="System.Byte[]")

{

s+="'"+FormatString(dr[column.Name].ToString())+"',";

}

else

{

byte[]

bs=(byte[])dr[column.Name];

string

x="0x";

foreach(byte

b in bs)

{

x+=b.ToString("X");

}

s+=""+x+",";

}

}

}

}

if(s.Length==0)

{

return s;

}

return s.Substring(0,s.Length-1);

}

public string FormatString(string s)

{

return s.Replace("'","''");

}

public string GetTableColumnList(TableSchema table)

{

string s="";

foreach(ColumnSchema column in

table.Columns)

{

s+="["+column.Name+"],";

}

if(s.Length==0)

{

return "";

}

return s.Substring(0,s.Length-1);

}

public bool IsNumeric(ColumnSchema column)

{

switch(column.NativeType.ToLower())

{

case "int":

case "smallint":

case "bigint":

case "float":

case "decimal":

case "money":

case "numeric":

case "real":

case "smallmoney":

case "tinyint":

case "bit":

return

true;

default:

return

false;

}

}

#endregion