如何以编程方式创建 localdb .mdf? [英] how does one programmatically create a localdb .mdf?

问题描述

如何以编程方式创建 localdb .mdf?

how does one programmatically create a localdb .mdf?

可接受的解决方案排除visual studio、ssms、aspnet_regsql.

acceptable solutions exclude visual studio, ssms, aspnet_regsql.

对解决方案的天真尝试可能如下所示:

a naive stab at a solution might look like this:

static void Main(string[] args)
{
    using (var con = new SqlConnection(@"Integrated Security=SSPI;Data Source=(LocalDb)v11.0;AttachDbFilename=test.mdf"))
    {
        con.Open();
        using (var cmd = new SqlCommand("CREATE DATABASE test", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
    }
}

但当然，这在 SqlConnection.Open 中失败并出现错误

but of course, this fails in SqlConnection.Open with the error

尝试为文件 test.mdf 附加自动命名的数据库失败.存在同名数据库，或无法打开指定文件，或位于 UNC 共享.

An attempt to attach an auto-named database for file test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

如果指定的 .mdf 不存在，您将无法连接到数据库.

You cannot connect to a database if the specified .mdf doesn't exist.

那么……你如何创建一个?

So... how do you create one?

推荐答案

必须拼凑 Stackoverflow 和伟大的 SQL Server 2012 Express LocalDB 入门 文章来自@AaronBertrand

Had to piece together several answers from Stackoverflow and the great Getting Started with SQL Server 2012 Express LocalDB article from @AaronBertrand

代码假定安装了 Dapper.NET:

Code assumes Dapper.NET is installed:

PM> 安装包 Dapper

程序化创建:

var dbServerName = "SERVER_NAME";
var dbName = "DATABASE_NAME";

var infoResult = SqlLocalDbCommand($"info {dbServerName}");

var needsCreated = infoResult?.Trim().EndsWith($""{dbServerName}" doesn't exist!");

if (needsCreated.GetValueOrDefault(false))
{
    var createResult = SqlLocalDbCommand($"create {dbServerName} -s");

    var success = createResult?.Trim().EndsWith($""{dbServerName}" started.");

    if (false == success)
    {
        var msg = $"Failed to create database:{Environment.NewLine}{createResult}"
        throw new ApplicationException(msg);
    }

    var master = $@"Server=(localdb){dbServerName};Integrated Security=True;"
    using (var conn = new SqlConnection(master))
    {
        var result = conn.Execute($"CREATE DATABASE {dbName}");
    }

  var @new = $@"Server=(localdb){dbServerName};Integrated Security=True;Database={dbName}"
    using (var conn = new SqlConnection(@new))
    {
        //verify i can access my new database
        var tables = conn.Query($"SELECT * FROM {dbName}.INFORMATION_SCHEMA.Tables");
    }
}

Helper(感谢 T30):

/// <summary>
///     Executes a command against SqlLocalDB
/// </summary>
/// <remarks></remarks>
/// <param name="arguments">The arguments to pass to SqlLocalDB.exe</param>
/// <returns></returns>
/// <exception cref="System.ApplicationException">Error returned from process</exception>
private static string SqlLocalDbCommand(string arguments)
{
    var process = new Process
    {
        StartInfo =
        {
            FileName = "SqlLocalDB",
            Arguments = arguments,
            UseShellExecute = false,
            RedirectStandardOutput = true,
            RedirectStandardError = true
        }
    };

    process.Start();
    //* Read the output (or the error)
    var output = process.StandardOutput.ReadToEnd();
    Console.WriteLine(output);
    var err = process.StandardError.ReadToEnd();
    Console.WriteLine(err);
    process.WaitForExit();

    if (err.Exists()) throw new ApplicationException(err); //Is LocalDB installed?

    return output;
}

请注意，使用此解决方案，您将看不到 mdf 文件，我确定它们存在于某个用户文件夹中，但关键是您将通过连接字符串进行连接

Note that with this solution you won't see the mdf files, i'm sure they exist in some user folder but the key take away is that you'll connect by the connection string

(localdb)SERVER_NAME;Integrated Security=True;Database=DATABASE_NAME

这篇关于如何以编程方式创建 localdb .mdf?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！