如何创建＆QUOT;嵌入式＆QUOT; SQL如果它不存在，2008年的数据库文件？ [英] How to create "embedded" SQL 2008 database file if it doesn't exist?

问题描述

我已经创建使用C＃，ADO.Net和嵌入式MS SQL 2008数据库文件的数据库应用程序（即连接到MS SQL 2008前preSS）我在Server Management Studio中创建的。有人能指出我描述我如何通过编程创建数据库文件，如果它丢失（如右侧安装我的应用程序之后）的资源？

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 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多少你需要让你做，这是适当的权限服务器实例，然后连接字符串（你也许可以从服务器/实例名建除外）。

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. 一切都因为它应该是矣。

从一个code点：方法来确定一个数据库存在;方法创建一个版本表和0版本号的标准的空的数据库;方法通过运行相应的DDL带来的架构上涨到目前的版本（我们EN code我们到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;
    }

创建一个新的数据库：

Create a new database:

    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();
        }
    }

更新code是一点点更复杂，但基本上运行的东西是这样的：

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.

为什么做这种方式（在code，这也不是没有试验？），以及最终的结果是一个高度的信心，你的应用程序是谈话的模式是您的应用希望交谈的架构...右表，右列（以正确的顺序，这是正确的类型和正确的长度），等等，等等，而这将继续一段时间的情况。

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...

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