不使用SMO执行TSQL？

Question

我正在维护一个简单的DB工具，它在我们的CreateDatabase模型上执行EF，并使用SMO运行一些.sql脚本。

它目前使用：

var svrConnection = new ServerConnection(sqlConnection);
var server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(fullSqlScript);

是否有一种不使用SMO?在.Net中执行TSQL脚本的方法？

或者，是否有一种方法可以在应用程序中成功地使用SMO，而无需在运行SMO的服务器上安装？

任何对我有用的选择都不需要在盒子上安装，除了我的工具的程序集的x拷贝。它还必须保证脚本将以完全相同的方式运行，而无需进行额外的测试/验证。

脚本使用GO等，不能/不应该被分解--它们是由第三方工具(aspnet_regsql.exe)和手工生成的(但在这一点上已经过时了)，所以如果可以避免的话，我不想碰它们。

我很确定在对这个答复的评论中的建议是错误的，因为我确信GO不能直接用;代替。如果我错了，请告诉我:)

我正在努力解决这个问题：

无法加载文件或程序集“Microsoft.SqlServer.SqlClrProvider、Version=10.0.0.0、Culture=neutral、PublicKeyToken=89845dcd8080cc91”或其依赖项之一。系统找不到指定的文件。

这里提到的修复方法需要安装SMO，这需要其他软件包，这对我来说并不理想。

基于罗素·麦克卢尔的回答的最终解决方案

最后，我将使用sqlcmd.exe，因为为它编写的脚本(任何包含GO的脚本)在我自己的代码中通过SqlCommand.ExecuteNonQuery复制可能会做得太多。而且，由于它与SMO具有相同的依赖级别，并且需要一些工作才能以一种编程上干净的方式包装起来，所以我将继续使用SMO。

我提到的所有库和工具都是以下内容的一部分：

http://www.microsoft.com/download/en/details.aspx?id=16978

Answer 1

通过sqlcmd.exe运行T。

或者，我通常要做的是读取文件内容(我的sql文件通常是嵌入式资源)，然后将其传递给这个函数，将其拆分为ADO.NET可以接受的批处理：

public static string[] ParseSqlStatementBatch(string sqlStatementBatch)
{
   // split the sql into seperate batches by dividing on the GO statement
   Regex sqlStatementBatchSplitter = new Regex(@"^\s*GO\s*\r?$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
   return sqlStatementBatchSplitter.Split(sqlStatementBatch);
}

下面是一个示例用法：

string[] sqlStatements = DataAccess.DatabaseWrapper.ParseSqlStatementBatch(FileContents);

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
   sqlConnection.Open();
   using (SqlCommand command = sqlConnection.CreateCommand())
   {
      command.CommandType = CommandType.Text;
      command.CommandTimeout = Registry.RegistryWrapper.GetSqlCommandTimeout();
      foreach (string sqlStatement in sqlStatements)
      {
         if (sqlStatement.Length > 0)
         {
            command.CommandText = sqlStatement;
            command.ExecuteNonQuery();

Answer 2

您可以使用ADO.NET，这样您就不必依赖sqlcmd.exe了。

下面是一个非常有用的类来处理您的脚本(使用GO、:setvar、$(MyVar)等)。

archive.html

几个月来，我一直在生产中使用一个稍微修改过的版本，没有问题。这是我的版本(不记得我更改了什么--可能添加了一些受支持的命令)。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace GR.WIX
{
    /// <summary>
    /// Supports running a SQLCmd Mode style statement such as the output from a VS 2008 Database Team Edition Database Project
    /// Only a limited subset of the SQLCmd mode syntax is supported. Other gotchas.
    ///
    ///
    ///
    /// Supported Commands:
    ///     GO (note GO [N] will only be executed once not N times)
    ///     :setvar
    ///     $(MyVar)
    ///     :on error exit
    ///     :on error resume (only for SQL Errors)
    ///     :on error ignore (only for SQL Errors)
    ///
    /// The Following SQLCMD Commands are recognized but ignored. They will not crash your script if encountered but will be skipped.
    ///     :ED
    ///     :Error
    ///     :!!
    ///     :Perftrace
    ///     :Quit
    ///     :Exit
    ///     :Help
    ///     :XML
    ///     :r
    ///     :ServerList
    ///     :Listvar
    ///
    /// The following SQLCMD pre-defined variables are pre-defined by this class just like they are by SQLCMD
    /// The only difference is SQLCMD actually used and/or updated these variable. This class simply has them predefined
    /// with much the same values as SQLCMD did. The class allows you to change ALL variables (unlike SQLCMD) where some are
    /// read only.
    ///     SQLCMDUSER ""
    ///     SQLCMDPASSWORD
    ///     SQLCMDSERVER {Server Name}
    ///     SQLCMDWORKSTATION {Computer Name}
    ///     SQLCMDLOGINTIMEOUT {Connection Timeout}
    ///     SQLCMDDBNAME {Database Name}
    ///     SQLCMDHEADERS "0"
    ///     SQLCMDCOLSEP " "
    ///     SQLCMDCOLWIDTH "0"
    ///     SQLCMDPACKETSIZE "4096"
    ///     SQLCMDERRORLEVEL "0"
    ///     SQLCMDMAXVARTYPEWIDTH "256"
    ///     SQLCMDMAXFIXEDTYPEWIDTH "0"
    ///     SQLCMDEDITOR "edit.com"
    ///     SQLCMDINI ""
    ///
    /// The following pre-defnined variables ARE used by the class and their values when set are not ignored
    ///     SQLCMDSTATTIMEOUT "0"
    ///   
    /// One Additional Variable is defined so that scripts could potentially detect they are running in this class instead
    /// of SQLCmd.
    ///     SQLCMDREAL "0"
    /// </summary>
    public class ExecuteSqlCmdMode
    {
        #region Fields
        private readonly Dictionary<string, string> variables;
        private readonly List<string> lockedVariables;
        private ErrorMode errorMode;
        private readonly SqlConnection connection;
        private readonly List<string> ignoredCommands;
        private bool allowVariableOverwrites;
        #endregion Fields

        #region Properties
        /// <summary>
        /// Gets or sets a value indicating whether to allow variable overwrites.
        /// If True then even though a variable is specified externally it may be overwritten by :SetVar in the script. If False then the reverse
        /// variables specified externally superscede :setvar.
        /// Default = false
        /// </summary>
        /// <value>true if allow variable overwrites; otherwise, false.</value>
        public bool AllowVariableOverwrites
        {
            get { return allowVariableOverwrites; }
            set { allowVariableOverwrites = value; }
        }
        #endregion Properties

        #region Constructor
        /// <summary>
        /// Initializes a new instance of the <see cref="ExecuteSqlCmdMode"/> class.
        /// </summary>
        /// <param name="sqlConnection">The SQL conn.</param>
        public ExecuteSqlCmdMode(SqlConnection sqlConnection)
        {
            // Check for legal values
            if (sqlConnection == null)
            {
                throw new Exception("connection cannot be null");
            }

            // Set connection variable from supplied SQLConnection.
            connection = sqlConnection;

            // Load up the script variables.
            variables = new Dictionary<string, string>();
            variables.Add("SQLCMDUSER", "");
            variables.Add("SQLCMDPASSWORD", "");
            variables.Add("SQLCMDSERVER", sqlConnection.DataSource);
            variables.Add("SQLCMDWORKSTATION", sqlConnection.WorkstationId);
            variables.Add("SQLCMDDBNAME", sqlConnection.Database);
            variables.Add("SQLCMDLOGINTIMEOUT", sqlConnection.ConnectionTimeout.ToString());
            variables.Add("SQLCMDSTATTIMEOUT", "0");
            variables.Add("SQLCMDHEADERS", "0");
            variables.Add("SQLCMDCOLSEP", "");
            variables.Add("SQLCMDCOLWIDTH", "0");
            variables.Add("SQLCMDPACKETSIZE", "4096");
            variables.Add("SQLCMDERRORLEVEL", "0");
            variables.Add("SQLCMDMAXVARTYPEWIDTH", "256");
            variables.Add("SQLCMDMAXFIXEDTYPEWIDTH", "0");
            variables.Add("SQLCMDEDITOR", "edit.com");
            variables.Add("SQLCMDINI", "");
            variables.Add("SQLCMDREAL", "0");

            // Setup pre-locked variables.
            lockedVariables = new List<string>();
            lockedVariables.Add("SQLCMDREAL");

            // Setup the list of commands to be ignored.
            ignoredCommands = new List<string>();
            ignoredCommands.Add(":ED");
            ignoredCommands.Add(":ERROR");
            ignoredCommands.Add(":!!");
            ignoredCommands.Add(":PERFTRACE");
            ignoredCommands.Add(":QUIT");
            ignoredCommands.Add(":EXIT");
            ignoredCommands.Add(":HELP");
            ignoredCommands.Add(":XML");
            //ignoredCommands.Add(":R");
            ignoredCommands.Add(":SERVERLIST");
            ignoredCommands.Add(":LISTVAR");

            // Some other misc values.
            errorMode = ErrorMode.ErrExit;
            allowVariableOverwrites = false;
        }

        #endregion Constructor

        /// <summary>
        /// Sets a variable in advance of script execution.
        /// </summary>
        /// <param name="variableName">Name of the variable.</param>
        /// <param name="variableValue">The variable value.</param>
        public void SetVariable(string variableName, string variableValue)
        {
            variableName = variableName.Trim().ToUpper();
            if (variableName.Length == 0  || variableName.Contains(" "))
            {
                throw new Exception(string.Format("Variable name {0} cannot be blank or contain spaces", variableName));
            }

            // See if we already have this variable
            if (variables.ContainsKey(variableName))
            {
                variables[variableName] = variableValue;
            }
            else
            {
                variables.Add(variableName, variableValue);

                if (!allowVariableOverwrites)
                {
                    lockedVariables.Add(variableName);
                }
            }
        }

        /// <summary>
        /// Executes the specified SQL script.
        /// </summary>
        /// <param name="scriptToExecute">The SQL script to execute.</param>
        public List<Exception> Execute(string scriptToExecute)
        {
            var exceptions = new List<Exception>();
            var queryBlock = new StringBuilder();

            connection.Open();


            var scriptLines = (scriptToExecute.Replace(Environment.NewLine, "\n") + "\nGO\n").Split('\n');

            // Loop each line in the script
            for (var i = 0; i < scriptLines.GetUpperBound(0); i++)
            {
                // Prepare a specially modified version of the line for checking for commands.
                var ucaseLine = scriptLines[i].Replace("\t", " ").Trim().ToUpper() + " ";

                // See if it's one of the commands to be ignored.
                if (ignoredCommands.Contains(ucaseLine.Split(' ')[0]))
                {
                    // Just ignore this line.
                }
                else if (ucaseLine.StartsWith("GO "))
                {
                    // We have a GO line (everything after GO on the line is ignored). Execute the block
                    // we have gathered so far.
                    ExecuteBlock(queryBlock, exceptions);
                    // After a GO command, we reset our query.
                    queryBlock = new StringBuilder();
                }
                else if (ucaseLine.StartsWith(":SETVAR "))
                {
                    // We have found a SetVar line. Add (or update) the variable and its value to our list.
                    SetVariableValue(scriptLines[i]);
                }
                else if (ucaseLine.StartsWith(":ON ERROR "))
                {
                    // Handle :on error.
                    HandleOnErrorCommand(i, ucaseLine);
                }
                else if (ucaseLine.StartsWith(":R "))
                {
                    // TODO: Handle this case.
                }
                else
                {
                    // Regular SQL Line to have variables replaced on then added to SQLCmd for execution.

                    // Replace variables with its value for the line (if any).
                    var noVariableVersion = ReplaceVariablesWithValue(scriptLines[i]);

                    // Add it to the current block of code to execute.
                    queryBlock.AppendLine(noVariableVersion);
                }
            }
            return exceptions;
        }

        private string ReplaceVariablesWithValue(string temp)
        {
            if (temp.Length > 4 && temp.Contains("$("))
            {
                // Loop each variable to check the line for.
                foreach (var keyPair in variables)
                {
                    var searchFor = string.Format("$({0})", keyPair.Key);
                    var begPos = temp.ToUpper().IndexOf(searchFor);
                    while (begPos >= 0)
                    {
                        // Make the variable substitution
                        var endPos = begPos + searchFor.Length;
                        temp = temp.Substring(0, begPos) + keyPair.Value + temp.Substring(endPos, temp.Length - endPos);

                        // Calculate a new begPos
                        begPos = temp.ToUpper().IndexOf(string.Format(searchFor));
                    }
                }
            }
            return temp;
        }

        private void ExecuteBlock(StringBuilder sqlCommand, List<Exception> exceptions)
        {
            try
            {
                if (sqlCommand.Length > 0)
                {
                    // Attempt the SQL command.
                    using (var sqlComm = new SqlCommand(sqlCommand.ToString(), connection))
                    {
                        sqlComm.CommandTimeout = 120;
                        sqlComm.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                if (errorMode != ErrorMode.ErrIgnore)
                {
                    throw new Exception("Error executing " + sqlCommand, ex);
                }

                exceptions.Add(new Exception("Error executing " + sqlCommand, ex));
            }
        }
        private void HandleOnErrorCommand(int i, string ucaseLine)
        {
            var temp = ucaseLine.Substring(10, ucaseLine.Length - 10).Trim();
            if (temp == "EXIT")
            {
                errorMode = ErrorMode.ErrExit;
            }
            else if (temp == "RESUME" || temp == "IGNORE")
            {
                errorMode = ErrorMode.ErrIgnore;
            }
            else
            {
                throw new Exception(string.Format("Unknown On Error mode '{0}' on line {1}", temp, i));
            }
        }

        private void SetVariableValue(string scriptLine)
        {
            var temp = scriptLine.Trim().Substring(8, scriptLine.Trim().Length - 8);
            var begPos = temp.IndexOf(" ");

            var varName = temp.Substring(0, begPos).Trim().ToUpper();
            var varValue = temp.Substring(begPos + 1, temp.Length - begPos - 1).Trim();
            if (varValue.StartsWith("\"") && varValue.EndsWith("\""))
            {
                varValue = varValue.Substring(1, varValue.Length - 2);
            }
            else
            {
                throw new Exception(string.Format("Improperly formatted :SetVar on the following line {0}.", scriptLine));
            }

            if (variables.ContainsKey(varName))
            {
                if (!lockedVariables.Contains(varName))
                {
                    variables[varName] = varValue;
                }
            }
            else
            {
                variables.Add(varName, varValue);
            }
        }
    }

    /// <summary>
    /// Legal values for the error mode
    /// Error mode controls what happens when a SQL Error occurs
    /// </summary>
    public enum ErrorMode
    {
        ErrExit,
        ErrIgnore
    }
}

像这样使用它：

/// <summary>
        /// Executes the SQL script.
        /// </summary>
        /// <param name="serverName">Name of the server.</param>
        /// <param name="scriptPath">The path of the script to execute.</param>
        /// <param name="variables">The variables.</param>
        /// <returns></returns>
        private static void ExecuteSqlScript(string serverName, string scriptPath, Dictionary<string, string> variables)
        {
            using (var connection = new SqlConnection(string.Format(SqlConnectionFormat, serverName)))
            {
                var mode = new ExecuteSqlCmdMode(connection);

                // Add variables.
                foreach (var variable in variables)
                {
                    mode.SetVariable(variable.Key, variable.Value);
                }

                mode.Execute(FileToString(scriptPath));
            }
        }

Answer 3

以下是一些可能有帮助的代码。我将其用于构建/部署系统。

public void ExecuteScript(string scriptPath, SqlTransaction trans)
{
    var batch = new StringBuilder();
    var script = File.ReadAllLines(scriptPath);

    for (int i = 0; i < script.Length; i++)
    {
        if (script[i].Trim().StartsWith("GO", StringComparison.CurrentCultureIgnoreCase))
        {
            // If a line starts with a GO it means that a batch should be executed (Sql Server doesn't understand GO).
            ExecuteBatch(batch.ToString(), trans);
            batch = new StringBuilder();
        }
        else
            batch.AppendLine(script[i]);
    }
    // make sure we execute the last batch (it might not end with GO).
    ExecuteBatch(batch.ToString(), trans);        
}

private static void ExecuteBatch(string batch, SqlTransaction trans)
{
    batch = batch.Trim();
    if (batch == "") return;
    var cmd = new SqlCommand(batch, trans.Connection, trans);
    cmd.CommandTimeout = 0;
    cmd.ExecuteNonQuery();
}

注意:这并不完全是原始代码(我有一些您可能不关心的额外内容)，而且我没有测试上面的更改。