如何创建“嵌入式”应用程序SQL 2008 数据库文件如果不存在?

发布于 2024-08-11 00:57:25 字数 172 浏览 7 评论 0原文

我使用 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)?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

鲜肉鲜肉永远不皱 2024-08-18 00:57:25

如果是我(当是我时......):

您并不特别想尝试通过复制和附加它们来使数据库文件工作 - 您可能有原因想要这样做,但我相信这些都是例外而不是规则。

因此,您需要做的是编写数据库创建脚本,即使用 SQL DDL 来创建数据库和表以及模式中的所有其他内容。

几乎您需要执行此操作的是对服务器实例的适当权限,然后是连接字符串(您可以将其与服务器/实例名称分开构建)。

从这里:

  1. 有数据库吗?如果没有创建它。
  2. 如果有数据库,它的架构版本是否正确?如果太低,请更新它或建议用户并根据您希望事情如何工作的方式优雅地退出。如果太高,请退出并建议需要应用程序的更新版本
  3. 一切都应如此,继续。

从代码上看:判断数据库是否存在的方法;方法创建一个标准的“空”数据库,其版本表和版本号为0;方法通过运行适当的 DDL 将架构升级到当前版本(我们将我们的 DDL 编码为 C#,因为它提供了更大的灵活性,但您同样可以按顺序运行 DDL 脚本)。

它是否存在:

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

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

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

每次更新都包含在一个事务中 - 因此,如果更新失败,您应该让数据库处于已知的良好状态。

为什么要这样做(在代码中,这并非没有经过试验?)最终的结果是高度确信您的应用程序正在对话的模式是您的应用程序期望与之对话的模式......正确的表,正确的列(以正确的顺序,即正确的类型和正确的长度)等等,并且随着时间的推移,这种情况将继续存在。

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

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.

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

From here:

  1. Is there a database? If not create it.
  2. If there is a database, is it the right schema version? If too low either update it or advise the user and back out gracefully depending on how you want things too work. If too high just back out and advise that an updated version of the application is required
  3. All is as it should be, carry on.

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

Does it exist:

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

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

兰花执着 2024-08-18 00:57:25

如果“嵌入式 MS SQL”是“Microsoft SQL Server Compact 3.5”:

using (SqlCeEngine sqlCeEngine = new SqlCeEngine(connectionString))
  sqlCeEngine.CreateDatabase();

If the "embedded MS SQL" is a "Microsoft SQL Server Compact 3.5":

using (SqlCeEngine sqlCeEngine = new SqlCeEngine(connectionString))
  sqlCeEngine.CreateDatabase();
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文