asp.net下进行数据库安装与初始化化的例子 使用到了一个sql脚本文件,里面有创建表以及导入数据的部分,于是有朋友问是如何实现的,这里就简单介绍下。
一开始,判断表存在并删除表的代码是我自己写的,基本做法如下:
private System.Text.StringBuilder sBuilder=new System.Text.StringBuilder();
private void tt()
{
try
{
SqlConnection conn=new SqlConnection(txtConn.Text.Trim());
string sql="select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 and name not in ('dtproperties')";
SqlDataAdapter adapter=new SqlDataAdapter(sql,conn);
SqlCommandBuilder sb=new SqlCommandBuilder(adapter);
DataTable dt=new DataTable();
adapter.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
CreateDropTableScript(dt.Rows[i][0].ToString());
}
txtResult.Text=sBuilder.ToString();
dt.Dispose();
conn.Close();
}
catch (Exception ex)
{
}
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
private void CreateDropTableScript(string tblName)
{
Output("if exists (select * from dbo.sysobjects where id = object_id(N'["+tblName+"]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)");
Output("drop table ["+tblName+"]");
Output("GO");
}
![](/Images/OutliningIndicators/None.gif)
private void Output(string s)
{
sBuilder.Append(s);
sBuilder.Append(Environment.NewLine);
}
创建表和生成insert语句的部分是使用了Mygeneration的模板CreateTable.zenus和SQL_DataInserts.zenus.
后来为了省事,就改了CreateTable.zenus模板,在Create Table之前加入如下代码:
if exists (select * from dbo.sysobjects where id = object_id(N'[<%=table.Name%>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [<%=table.Name%>]
GO
修改后完整的Template Code代码如下:
<%
Public Class GeneratedTemplate
Inherits DotNetScriptTemplate
![](/Images/OutliningIndicators/InBlock.gif)
Public Sub New(context As ZeusContext)
MyBase.New(context)
End Sub
![](/Images/OutliningIndicators/InBlock.gif)
'---------------------------------------------------
' Render() is where you want to write your logic
'---------------------------------------------------
Public Overrides Sub Render
Dim db as IDatabase
Dim table As ITable
Dim col as IColumn
Dim IsFirst As Boolean
Dim FK As IForeignKey
Dim fkeys As New HashTable
Dim PKIndex As IIndex
db = MyMeta.Databases(input.Item("cmbDatabase"))
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Creating basic table structur.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For Each table in db.Tables
%>
if exists (select * from dbo.sysobjects where id = object_id(N'[<%=table.Name%>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [<%=table.Name%>]
GO
CREATE TABLE [<%=table.Name%>] (
<%
IsFirst = True
output.incTab()
For Each Col in table.Columns
If IsFirst = False Then
output.write( "," )
output.writeln("")
End If
output.autotab( CreateColumnScript( col ) )
IsFirst = False
Next
output.decTab()
%>
)
![](/Images/OutliningIndicators/InBlock.gif)
GO
![](/Images/OutliningIndicators/InBlock.gif)
<%
PKIndex = Nothing
For Each FK In table.ForeignKeys
If Not fKeys.ContainsKey( FK.Name ) Then
fKeys.Add( FK.Name, FK )
End If
If PKIndex Is Nothing AndAlso FK.PrimaryTable.Name = table.Name Then
PKIndex = table.Indexes( FK.PrimaryKeyName )
End If
Next
![](/Images/OutliningIndicators/InBlock.gif)
If Not PKIndex Is Nothing Then
%>
ALTER TABLE [<%=table.Name%>] WITH NOCHECK ADD
CONSTRAINT [<%=PKIndex.Name%>] PRIMARY KEY <%If PKIndex.Clustered Then output.write("CLUSTERED") %>
(
<%
IsFirst = True
For Each col In PKIndex.Columns
If IsFirst = False Then
output.write(",")
output.writeln("")
End If
output.autotab("[" + col.Name + "]")
IsFirst = False
Next
%>
) ON [PRIMARY]
GO
<%
End If
Next
![](/Images/OutliningIndicators/InBlock.gif)
For Each FK In fKeys.Values
%>
ALTER TABLE [<%=FK.ForeignTable.Name%>] ADD
CONSTRAINT [<%=FK.Name%>] FOREIGN KEY
(
<%
IsFirst = True
For Each col In FK.ForeignColumns
If IsFirst = False Then
output.write(",")
output.writeln("")
End If
output.autotab("[" + col.Name + "]")
IsFirst = False
Next
%>
) REFERENCES [<%=FK.PrimaryTable.Name%>] (
<%
IsFirst = True
For Each col In FK.PrimaryColumns
If IsFirst = False Then
output.write(",")
output.writeln("")
End If
output.autotab("[" + col.Name + "]")
IsFirst = False
Next
%>
) <%
If FK.DeleteRule <> "NO ACTION" Then
output.write( " ON DELETE " + FK.DeleteRule)
End If
If FK.UpdateRule <> "NO ACTION" Then
output.write( " ON UPDATE " + FK.UpdateRule)
End If
%>
GO
<%
Next
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
End Sub
![](/Images/OutliningIndicators/InBlock.gif)
Private Function CreateColumnScript( ByVal col As IColumn ) As String
Dim retStr As String
![](/Images/OutliningIndicators/InBlock.gif)
retStr = "[" + col.Name + "] " + col.DataTypeNameComplete
![](/Images/OutliningIndicators/InBlock.gif)
If col.IsAutoKey Then
retStr += " IDENTITY ( " + col.AutoKeySeed.ToString() + "," + col.AutoKeyIncrement.ToString() + " )"
End If
![](/Images/OutliningIndicators/InBlock.gif)
If col.HasDefault Then
retStr += " DEFAULT (" + col.Default + ")"
End If
![](/Images/OutliningIndicators/InBlock.gif)
If col.IsNullable Then
retStr += " NULL"
Else
retStr += " NOT NULL"
End If
![](/Images/OutliningIndicators/InBlock.gif)
Return retStr
End Function
![](/Images/OutliningIndicators/InBlock.gif)
End Class
%>
当然生成insert语句的时候对Image字段无法处理。
既然Mygeneration可以很好地完成我要的工作(项目中需要做数据库自动安装部分,就是开头提到的那个demo,当然还需要做很多完善的工作),我暂时也就懒得写自己的代码了,等有时间的时候也许我会重新写下的。
注:安装Mygeneration后,默认没有上面所提到的两个模板,你需要到mygeneration在线模板库下载,你可以通过浏览网页找到所需的模板,然后保存到指定目录,当然下面的方法也许更让你觉得方便很多:
download Templates from Mygeneration Online Template library
也许有朋友有更好的实现方法,希望能拿出来大家分享,谢谢!
一开始,判断表存在并删除表的代码是我自己写的,基本做法如下:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
创建表和生成insert语句的部分是使用了Mygeneration的模板CreateTable.zenus和SQL_DataInserts.zenus.
后来为了省事,就改了CreateTable.zenus模板,在Create Table之前加入如下代码:
if exists (select * from dbo.sysobjects where id = object_id(N'[<%=table.Name%>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [<%=table.Name%>]
GO
修改后完整的Template Code代码如下:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
当然生成insert语句的时候对Image字段无法处理。
既然Mygeneration可以很好地完成我要的工作(项目中需要做数据库自动安装部分,就是开头提到的那个demo,当然还需要做很多完善的工作),我暂时也就懒得写自己的代码了,等有时间的时候也许我会重新写下的。
注:安装Mygeneration后,默认没有上面所提到的两个模板,你需要到mygeneration在线模板库下载,你可以通过浏览网页找到所需的模板,然后保存到指定目录,当然下面的方法也许更让你觉得方便很多:
download Templates from Mygeneration Online Template library
也许有朋友有更好的实现方法,希望能拿出来大家分享,谢谢!