(三)关于SQL脚本在DbCommand里的执行

 Author：水如烟 

因为要创建数库,所以项目中需要大量的SQL脚本。

脚本的书写

一可以使用模板方式，使用时替换相应参数就可以了。

二是在代码中直接输写。我采用这种方法。

为了代码阅读稍为方便些，并使输出语句格式尽可能的规范，用这个类来帮助输写：

Namespace  LzmTW.DatabaseBuilder
     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

例如：

         Friend   Overrides   Function  GetCreateCatalogOverridesString()  As   String
             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

输出结果就是：

USE  master
;
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   @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   @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   @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
;

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后是可以执行的。

USE  master
;
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状态下才有效。

         Private   Function  ExecuteMasterSql( 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( " 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