Author:水如烟
因为要创建数库,所以项目中需要大量的SQL脚本。
脚本的书写
一可以使用模板方式,使用时替换相应参数就可以了。
二是在代码中直接输写。我采用这种方法。
为了代码阅读稍为方便些,并使输出语句格式尽可能的规范,用这个类来帮助输写:
Public Class SQLHelper
Public Shared Sub AppendLine( _
ByRef b As System.Text.StringBuilder, _
ByVal value As String , _
ByVal numTab As Integer , _
ByVal withComa As Boolean , _
ByVal ParamArray paras As String ())
b.AppendLine(GetString(value, numTab, withComa, paras))
End Sub
Public Shared Sub Append( _
ByRef b As System.Text.StringBuilder, _
ByVal value As String , _
ByVal numTab As Integer , _
ByVal withComa As Boolean , _
ByVal ParamArray paras As String ())
b.Append(GetString(value, numTab, withComa, paras))
End Sub
Private Shared Function GetString( _
ByVal value As String , _
ByVal numTab As Integer , _
ByVal withComa As Boolean , _
ByVal ParamArray paras As String ()) As String
Return String .Concat( New String (ControlChars.Tab, numTab), String .Format(value, paras), IIf (withComa, " , " , "" ))
End Function
End Class
End Namespace
例如:
Dim b As New System.Text.StringBuilder
SQLHelper.AppendLine(b, " USE master " , 0 , False )
SQLHelper.AppendLine(b, " ; " , 0 , False )
SQLHelper.AppendLine(b, " IF EXISTS " , 0 , False )
SQLHelper.AppendLine(b, " ( " , 0 , False )
SQLHelper.AppendLine(b, " SELECT * " , 1 , False )
SQLHelper.AppendLine(b, " FROM sys.databases " , 1 , False )
SQLHelper.AppendLine(b, " WHERE name = N'{0}' " , 1 , False , Me .Name)
SQLHelper.AppendLine(b, " ) " , 0 , False )
SQLHelper.AppendLine(b, " DROP DATABASE {0} " , 0 , False , Me .Name)
SQLHelper.AppendLine(b, " ; " , 0 , False )
SQLHelper.AppendLine(b, " CREATE DATABASE {0} " , 0 , False , Me .Name)
SQLHelper.AppendLine(b, " ; " , 0 , False )
Return b.ToString
End Function
输出结果就是:
;
IF EXISTS
(
SELECT *
FROM sys.databases
WHERE name = N ' Sql9Works '
)
DROP DATABASE Sql9Works
;
CREATE DATABASE Sql9Works
;
在DbCommand中执行SQL脚本
DbCommand(以下简称cm)是支持执行SQL脚本的。比如可以使用上面的,
cm.CommandText = GetCreateCatalogOverridesString()
cm.ExecuteNonQuery()
稍为注意的是:
不支持查询器脚本里的"GO"。GO是查询器特有的。所以要把GO用";"来替代。
但须注意,";"并不能代替"GO"的功能。"GO"切断了上下块的联系,而";"则不能。
在语句块中定义了变量的时候,有必要区分这个差别了。如:
DECLARE @object_id int
DECLARE @Sql nvarchar ( MAX )
SET @table = N ' [Base].[职员] '
SELECT @object_id = object_id
FROM sys.objects
WHERE parent_object_id =
(
SELECT object_id
FROM sys.objects
WHERE object_id = OBJECT_ID ( @table )
AND type in (N ' U ' )
)
AND type in (N ' PK ' )
IF @object_id > 0
BEGIN
SET @Sql = N ' ALTER TABLE ' + @table + N ' DROP CONSTRAINT ' + OBJECT_name ( @object_id )
EXEC sys.sp_executesql @Sql
END
SET @Sql = N ' ALTER TABLE [Base].[职员] ADD CONSTRAINT PK_Base_职员 PRIMARY KEY (职员ID) '
EXEC sys.sp_executesql @Sql
GO
DECLARE @Table nvarchar ( 50 )
DECLARE @object_id int
DECLARE @Sql nvarchar ( MAX )
SET @table = N ' [Base].[部门] '
SELECT @object_id = object_id
FROM sys.objects
WHERE parent_object_id =
(
SELECT object_id
FROM sys.objects
WHERE object_id = OBJECT_ID ( @table )
AND type in (N ' U ' )
)
AND type in (N ' PK ' )
IF @object_id > 0
BEGIN
SET @Sql = N ' ALTER TABLE ' + @table + N ' DROP CONSTRAINT ' + OBJECT_name ( @object_id )
EXEC sys.sp_executesql @Sql
END
SET @Sql = N ' ALTER TABLE [Base].[部门] ADD CONSTRAINT PK_Base_部门 PRIMARY KEY (部门ID) '
EXEC sys.sp_executesql @Sql
这在查询器中运行是正常的,而用;代替GO后,则异常的了。
DECLARE @object_id int
DECLARE @Sql nvarchar ( MAX )
SET @table = N ' [Base].[职员] '
SELECT @object_id = object_id
FROM sys.objects
WHERE parent_object_id =
(
SELECT object_id
FROM sys.objects
WHERE object_id = OBJECT_ID ( @table )
AND type in (N ' U ' )
)
AND type in (N ' PK ' )
IF @object_id > 0
BEGIN
SET @Sql = N ' ALTER TABLE ' + @table + N ' DROP CONSTRAINT ' + OBJECT_name ( @object_id )
EXEC sys.sp_executesql @Sql
END
SET @Sql = N ' ALTER TABLE [Base].[职员] ADD CONSTRAINT PK_Base_职员 PRIMARY KEY (职员ID) '
EXEC sys.sp_executesql @Sql
;
DECLARE @Table nvarchar ( 50 )
DECLARE @object_id int
DECLARE @Sql nvarchar ( MAX )
SET @table = N ' [Base].[部门] '
SELECT @object_id = object_id
FROM sys.objects
WHERE parent_object_id =
(
SELECT object_id
FROM sys.objects
WHERE object_id = OBJECT_ID ( @table )
AND type in (N ' U ' )
)
AND type in (N ' PK ' )
IF @object_id > 0
BEGIN
SET @Sql = N ' ALTER TABLE ' + @table + N ' DROP CONSTRAINT ' + OBJECT_name ( @object_id )
EXEC sys.sp_executesql @Sql
END
SET @Sql = N ' ALTER TABLE [Base].[部门] ADD CONSTRAINT PK_Base_部门 PRIMARY KEY (部门ID) '
EXEC sys.sp_executesql @Sql
错误信息就是:
消息 134,级别 15,状态 1,第 29 行
变量名 '@Table' 已声明。变量名在查询批次或存储过程内部必须唯一。
消息 134,级别 15,状态 1,第 30 行
变量名 '@object_id' 已声明。变量名在查询批次或存储过程内部必须唯一。
消息 134,级别 15,状态 1,第 32 行
变量名 '@Sql' 已声明。变量名在查询批次或存储过程内部必须唯一。
这种情形,要拆分,分次赋值给cm执行就可以了。
CREATE DATABASE后的CREATE TABLE
先看这个脚本,它赋给cm.CommandText后是可以执行的。
;
IF EXISTS
(
SELECT *
FROM sys.databases
WHERE name = N ' Sql9Demo '
)
DROP DATABASE Sql9Demo
;
CREATE DATABASE Sql9Demo
;
USE Sql9Demo
;
IF NOT EXISTS
(
SELECT *
FROM sys.schemas
WHERE name = N ' Base '
)
EXEC sys.sp_executesql N ' CREATE SCHEMA [Base] AUTHORIZATION [dbo] '
;
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID (N ' [Base].[职员] ' )
AND type in (N ' U ' )
)
DROP TABLE [ Base ] . [ 职员 ]
;
CREATE TABLE [ Base ] . [ 职员 ]
(
[ 职员ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ 姓名 ] [ nvarchar ] ( 50 ) NOT NULL ,
[ 性别 ] [ bit ]
)
;
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID (N ' [Base].[部门] ' )
AND type in (N ' U ' )
)
DROP TABLE [ Base ] . [ 部门 ]
;
CREATE TABLE [ Base ] . [ 部门 ]
(
[ 部门ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ 部门 ] [ nvarchar ] ( MAX ) NOT NULL
)
;
注意的是,这条语句可能正常运行,也可能发生异常。
在服务器没有Sql9Demo数据库时执行会发生异常。如果已存在,上面语句实现删除了重建,不会异常。
发生异常的情形,原因在于创建后紧跟着使用了USE Sql9Demo,
错误信息如下:
消息 911,级别 16,状态 1,第 13 行
在 sysdatabases 中找不到数据库 'Sql9Demo' 所对应的条目。没有找到具有该名称的条目。请确保正确地输入了该名称。
如果在查询器中,修改Use上的“;”为“GO”,那就正常了。
在cm中,为避免异常,要单独创建数据库。之后对数据库的操作,定向方式方法有,一是重新生成ConnectionString,这种方法比较麻烦;二是在每个语句首加"USE Sql9Demo;";三是利用DbConnection.ChangeDatabase("Sql9Demo")方法。我利用第三种方法。留意的是ChangeDatabase方法是在Open状态下才有效。
Dim mResult As Boolean = False
Try
Using cm As Data.Common.DbCommand = GetCommand(sql)
Try
cm.Connection.Open()
cm.Connection.ChangeDatabase( " Master " )
cm.ExecuteNonQuery()
cm.Connection.Close()
mResult = True
Catch ex As Exception
Console.WriteLine(ex.ToString)
Finally
cm.Connection.Dispose()
End Try
End Using
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try
Return mResult
End Function
Private Function ExecuteCurrentSql( ByVal sql As String ) As Boolean
Dim mResult As Boolean = False
Try
Using cm As Data.Common.DbCommand = GetCommand(sql)
Try
cm.Connection.Open()
cm.Connection.ChangeDatabase( Me .Catalog.Name)
cm.ExecuteNonQuery()
cm.Connection.Close()
mResult = True
Catch ex As Exception
Console.WriteLine(ex.ToString)
Finally
cm.Connection.Dispose()
End Try
End Using
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try
Return mResult
End Function