如何创建“嵌入式"SQL 2008 数据库文件是否不存在? [英] How to create "embedded" SQL 2008 database file if it doesn't exist?

问题描述

我使用 C#、ADO.Net 和我在 Server Management Studio 中创建的嵌入式 MS SQL 2008 数据库文件(附加到 MS SQL 2008 Express)创建了一个数据库应用程序.有人可以指出我如何在缺少数据库文件的情况下以编程方式创建该文件的资源(例如在安装我的应用程序之后)?

I've created a database application using C#, ADO.Net and an embedded MS SQL 2008 database file (that attaches to MS SQL 2008 Express) which I created in Server Management Studio. Can someone point me to a resource that describes how I can programmatically create the database file if it is missing (like right after my application is installed)?

推荐答案

If it is me (when it is me...):

If it were me (when it is me...):

您并不特别想尝试通过复制和附加数据库文件来使它们工作 - 这有您可能想要的原因，但我认为这些是例外而不是规则.

You don't particularly want to be trying to make database files work by copying them and attaching them - there are reasons why you might want to but I believe these to be exceptions rather than rules.

相应地，您需要做的是编写数据库的创建脚本，即使用 SQL DDL 创建数据库和表以及架构中的所有其他内容.

Accordingly what you need to do is to script creation of the database i.e. to use SQL DDL to create the database and the tables and all the other stuff in your schema.

几乎所有您需要的就是对服务器实例的适当权限，然后是连接字符串(除了服务器/实例名称之外，您可能还可以构建该字符串).

Pretty much all you need to enable you to do this is appropriate rights to the server instance and then a connection string (which you can probably build apart from the server/instance name).

从这里:

1. 有数据库吗?如果不创建它.
2. 如果有数据库，它是正确的架构版本吗?如果太低，要么更新它，要么建议用户，然后根据您希望事情的运行方式优雅地退出.如果太高，请退出并建议需要更新版本的应用程序
3. 一切照旧，继续.

从代码角度:判断数据库是否存在的方法；使用版本表和版本号 0 创建标准空"数据库的方法；通过运行适当的 DDL 将架构升级到当前版本的方法(我们将我们的代码编码到 C# 中，因为它提供了更大的灵活性，但您同样可以按顺序运行 DDL 脚本).

From a code point of view: method to determine if a database exists; method to create an standard "empty" database with a version table and a version number of 0; methods to bring the schema up to the current version by running the appropriate DDL (we encode ours into C# because it provides more flexibility but you could equally run DDL scripts in sequence).

是否存在:

    public virtual bool Exists()
    {
        bool exists = false;

        string masterConnectionString = this.CreateConnectionString(this.Server, this.FailoverServer, "master");

        this.DBConnection.ConnectionString = masterConnectionString;
        this.DBConnection.Open();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.DBConnection;
            cmd.CommandText = "SELECT COUNT(name) FROM sysdatabases WHERE name = @DBName";
            cmd.Parameters.AddWithValue("@DBName", this.DBName);

            exists = (Convert.ToInt32(cmd.ExecuteScalar()) == 1);
        }
        finally
        {
            this.DBConnection.Close();
        }

        return exists;
    }

创建一个新的数据库:

    public virtual void CreateNew()
    {
        string createDDL = @"CREATE DATABASE [" + this.DBName + "]";

        this.BuildMasterConnectionString();

        this.DBConnection.Open();
        try
        {
            this.ExecuteSQLStmt(createDDL, this.DefaultSQLTimeout, null);
        }
        finally
        {
            this.DBConnection.Close();
        }

        createDDL = @"
                CREATE TABLE AAASchemaVersion 
                (
                    Version         int             NOT NULL,
                    DateCreated     datetime        NOT NULL,
                    Author          nvarchar(30)    NOT NULL,
                    Notes           nvarchar(MAX)   NULL 
                );

                ALTER TABLE AAASchemaVersion ADD CONSTRAINT PK_Version PRIMARY KEY CLUSTERED
                (
                    Version
                );

                INSERT INTO AAASchemaVersion
                    (Version, DateCreated, Author, Notes)
                VALUES
                    (0, GETDATE(), 'James Murphy', 'Empty Database')
            ";

        this.BuildConnectionString();
        this.ConnectionString += ";pooling=false";

        this.DBConnection.Open();
        try
        {
            this.ExecuteSQLStmt(createDDL, this.DefaultSQLTimeout, null);
        }
        catch (Exception ex)
        {
            throw new Exception("Exception while creating / initialising AAASchemaVersion", ex);
        }
        finally
        {
            this.DBConnection.Close();
        }
    }

更新代码有点复杂，但基本上运行如下:

The update code is a tad more complex but basically runs stuff like this:

CREATE TABLE AuditUser
(    
    ID                  int IDENTITY(1,1)   NOT NULL,
    UserSourceTypeID    tinyint             NOT NULL,
    DateCreated         smalldatetime       NOT NULL,
    UserName            nvarchar(100)       NOT NULL        
);
ALTER TABLE AuditUser
ADD CONSTRAINT
    PK_AuditUser PRIMARY KEY CLUSTERED
    (
        ID
    ),
    CONSTRAINT [FK_AuditUser_UserSourceType] FOREIGN KEY
    (
        UserSourceTypeID
    ) REFERENCES UserSourceType (
        ID
    );

每次更新都包含在一个事务中 - 这样如果更新失败，您应该让数据库保持已知的良好状态.

All wrapped up in a transaction per update - so that if the update fails you should leave the database is a known good state.

为什么要这样做(在代码中，这不是没有经过试验?)最终结果是您的应用程序正在与之对话的架构是您的应用程序希望与之对话的架构的高度置信度...正确的表格、正确的列(以正确的顺序，即正确的类型和正确的长度)等等，随着时间的推移，这种情况将继续如此.

Why do it this way (in code, which is not without its trials?) well the end result is a high degree of confidence that the schema your app is talking to is the schema your app expects to talk to... right tables, right columns (in the right order, that are the right type and the right length), etc, etc. and that this will continue to be the case over time.

抱歉，如果这有点长 - 但这是我非常热衷的......

Apologies if this is a bit long - but this is something I'm quite keen on...

这篇关于如何创建“嵌入式"SQL 2008 数据库文件是否不存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！