我有一个大的 SQL 文本文件, 有很多 SQL 命令可以创建表格、 列等 。

示例行:

IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N xcal_views ) AND OBJECTPROPERTY(id, N IsUserTable ) = 1) 
   CREATE TABLE xcal_views (lid INT NOT NULL);
GO

IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N xcal_views_actors ) AND OBJECTPROPERTY(id, N IsUserTable ) = 1) 
   CREATE TABLE xcal_views_actors (lid INT NOT NULL);
GO

IF NOT EXISTS ( SELECT * FROM dbo.syscolumns, dbo.sysobjects WHERE [dbo].[syscolumns].[name] =  xlactor  AND [dbo].[sysobjects].[id] = [dbo].[syscolumns].[id] AND [dbo].[sysobjects].[id] = object_id(N xcal_views_actors ) AND OBJECTPROPERTY([dbo].[sysobjects].[id], N IsUserTable ) = 1 ) 
   ALTER TABLE [dbo].[xcal_views_actors] ADD xlactor INT NULL;
GO

IF NOT EXISTS ( SELECT * FROM dbo.syscolumns, dbo.sysobjects WHERE [dbo].[syscolumns].[name] =  lparentid  AND [dbo].[sysobjects].[id] = [dbo].[syscolumns].[id] AND [dbo].[sysobjects].[id] = object_id(N xcal_views_actors ) AND OBJECTPROPERTY([dbo].[sysobjects].[id], N IsUserTable ) = 1 ) 
   ALTER TABLE [dbo].[xcal_views_actors] ADD lparentid INT NULL;
GO

IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE parent_obj = (SELECT id FROM dbo.sysobjects WHERE id = object_id(N xcal_views_actors ) AND OBJECTPROPERTY(id, N IsUserTable ) = 1) AND OBJECTPROPERTY(id, N IsPrimaryKey ) = 1) 
   ALTER TABLE [dbo].[xcal_views_actors] 
   ADD CONSTRAINT [CT_00000501] PRIMARY KEY CLUSTERED (lid ASC);
GO

IF NOT EXISTS ( SELECT * FROM [sys].[indexes] i INNER JOIN [sys].[objects] o ON o.object_id = i.object_id AND o.name =  xcal_views_actors  WHERE i.name =  parent_id  ) 
   CREATE INDEX parent_id ON xcal_views_actors (lparentid ASC)
GO

每个命令之间我有一个额外的线条 < code> GO 来分隔命令 。

如果我从 SQL 服务器管理工作室运行整个 < code> patch.sql 文件,所有命令都会执行并正常运行。

在.NET中,我阅读了整个文本文件,然后将其与 GO 分开,然后根据数据库执行每个 SQL 命令。

奇怪的是,有些命令没有被执行,我找不到原因。

这是执行此任务的方法 :

private static void patchDatabase(string connection, string sqlfile)
{
  var defaultEncoding = Encoding.Default;
  using (FileStream fs = File.OpenRead(sqlfile))
  {
    defaultEncoding = TextFileEncodingDetector.DetectTextFileEncoding(fs, defaultEncoding, 1024);
  }
  //Console.WriteLine(string.Format("File {0} using encoding: {1}",sqlfile, defaultEncoding));

  var dbPatch = new StreamReader(sqlfile, defaultEncoding);
  string sqlPatch = dbPatch.ReadToEnd();
  dbPatch.Close();
  string[] stringSeparators = new[] {"GO"};
  string[] sqlPatches = sqlPatch.Split(stringSeparators, StringSplitOptions.None);

  if (connection != null && sqlPatch.Length > 0)
  {
    Console.WriteLine(string.Format("Executing {0} statements from {1}", sqlPatches.Length, sqlfile));

    using (var cnn = new SqlConnection(connection))
    {
      cnn.Open();
      foreach (var sql in sqlPatches)
      {
        if (String.IsNullOrEmpty(sql))
          continue; // Not a real sql statement, use next

        using (var cmd = new SqlCommand(sql, cnn))
        {
          try
          {
            cmd.CommandTimeout = 120;
            cmd.ExecuteNonQuery();
            //int results = cmd.ExecuteNonQuery();
            //if (results < 1)
            //  Console.WriteLine(String.Format("Failed:
Result: {0}
{1}",results, sql));
          }
          catch (Exception ex)
          {
            Console.WriteLine("Execution error!

" + sql + "


" + ex);
          }
        }
      }
      cnn.Close();
    }

  }
}

看起来像我的功能滑水...

我目前的文本文件有大约6000多条线。

知道我做错了什么吗?