如何以编程方式创建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和出色的

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屋！