无法使用smo从c#执行脚本

I have developed a C# Windows application in which I need to create a database on client machine for which I have created a script file and installed SQL Server 2008 R2 on client machine.

我开发了一个c# Windows应用程序，我需要在客户端机器上创建一个数据库，我已经在客户端机器上创建了一个脚本文件并安装了SQL Server 2008 R2。

But when I execute script from my code it always shows an error:

但是当我执行代码中的脚本时，它总是显示一个错误:

Failed to connect database.

连接数据库失败。

I have added referenced the SMO assemblies from

我已经添加了对SMO程序集的引用

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

And my code is :

我的代码是:

string sqlConString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mydatabase;Data Source=(local)";
FileInfo file = new FileInfo(dbPath);

string script = file.OpenText().ReadToEnd();

SqlConnection con = new SqlConnection(sqlConString);

Server server = new Server(new ServerConnection(con));
server.ConnectionContext.ExecuteNonQuery(script);

file.OpenText().Close();

1 个解决方案

#1

How about something like this:

比如这样:

using (SqlConnection cnn = new SqlConnection(sqlConString))
{
    var cmds = File.ReadAllText(@"path\to\file").Split(new string[] { "GO" },
        StringSplitOptions.RemoveEmptyEntries);

    cnn.Open();
    foreach (var cmd in cmds)
    {
        using (SqlCommand cmd = new SqlCommand(cmd, cnn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

With this approach you get what you want, the script gets executed, but you don't have to use the slow and bloated SMO interface. Further, you're leveraging the using statement so you don't have to worry about unmanaged resources being left open.

通过这种方法，您可以得到您想要的东西，脚本将被执行，但是您不必使用缓慢而臃肿的SMO接口。此外，您正在利用using语句，因此不必担心未管理的资源被保留为打开状态。

Now, to address the failed to connect to database..., which I clearly missed the first time I read it, if that's happening that means this:

现在，为了解决连接数据库失败的问题……，很明显，我第一次读的时候就错过了，如果是这样的话，就意味着:

Initial Catalog=mydatabase;Data Source=(local)

is likely not right. And if it is, your server may not be accepting Windows Authentication.

可能是不正确的。如果是，服务器可能不接受Windows身份验证。

I don't know the setup of your local machine, but it's likely that the server is (local)\SQLEXPRESS.

我不知道本地机器的设置，但是很可能服务器是(本地)\SQLEXPRESS。

#1