在 C# 中创建(按需)SQL Server 2008 Express 数据库的最佳实践?

Best practice to create (on demand) SQL Server 2008 Express databases in C#?(在 C# 中创建(按需)SQL Server 2008 Express 数据库的最佳实践?)

问题描述

目的是在全新的 SQL Server 2008 Express 数据库中处理用户的数据(您可以称它们为项目、文档、文件或其他任何内容).预计数据占用的空间比 Express 版本(也可免费分发)提供的 4GB 空间小得多.

The purpose is to handle the user's data (you can call them project, document, file, or whatever) in a brand new SQL Server 2008 Express database. The data are expected to occupy much less space than the 4GB available with the express edition (which is also free to distribute).

例如，每次用户选择 File->New 命令时，都会在指定位置创建一个新的空数据库.另一方面，类似的命令 File->Open 必须支持检索数据库列表以选择打开一个.

E.g., each time the user selects File->New command, a new empty database will be created at the specified location. On the other hand, a similar command, File->Open must provide support to retrieve the list of the databases to select one for opening.

因此，必须解决以下问题:a) 应用程序必须能够创建连接字符串并通过代码 (C#) 将数据库附加到 SQL Server 2008 Expressb) 应用程序必须能够(再次通过代码)检索包含所有可用数据库的列表，以便用户有机会选择打开一个.

So, the following issues must be resolved: a) The application must be able to create the connection string and attach the database to SQL Server 2008 Express through code (C#) b) The application must be able to retrieve (again through code) a list with all the available databases, to give the user a chance to select one to open.

我认为在资源中有一个模板数据库并将其复制到用户指定的位置会很有帮助.

I think it would be helpful to have a template database in resources and copy it in the location specified by the user.

您认为这是一个可行的解决方案吗?你有什么建议吗?

Do you think it is a working solution? Do you have any suggestions?

推荐答案

您可以使用 Sql Server 管理对象 (SMO) 做很多事情:

There's lots you can do with Sql Server Management Objects (SMO):

// Add a reference to Microsoft.SqlServer.Smo
// Add a reference to Microsoft.SqlServer.ConnectionInfo
// Add a reference to Microsoft.SqlServer.SqlEnum

using Microsoft.SqlServer.Management.Smo;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;

public class SqlServerController
{

    private Server m_server = null;

    public SqlServerController(string server)
    {
        m_server = new Server(server);
    }

    public void AttachDatabase(string database, StringCollection files,
        AttachOptions options)
    {
        m_server.AttachDatabase(database, files, options);
    }

    public void AddBackupDevice(string name)
    {
        BackupDevice device = new BackupDevice(m_server, name);
        m_server.BackupDevices.Add(device);
    }

    public string GetServerVersion(string serverName)
    {
        return m_server.PingSqlServerVersion(serverName).ToString();
    }

    public int CountActiveConnections(string database)
    {
        return m_server.GetActiveDBConnectionCount(database);
    }

    public void DeleteDatabase(string database)
    {
        m_server.KillDatabase(database);
    }

    public void DetachDatabase(string database, bool updateStatistics, 
        bool removeFullTextIndex)
    {
        m_server.DetachDatabase(database, updateStatistics, removeFullTextIndex);
    }

    public void CreateDatabase(string database)
    {
        Database db = new Database(m_server, database);
        db.Create();
    }

    public void CreateTable(string database, string table, 
        List<Column> columnList, List<Index> indexList)
    {
        Database db = m_server.Databases[database];
        Table newTable = new Table(db, table);

        foreach (Column column in columnList)
            newTable.Columns.Add(column);

        if (indexList != null)
        {
            foreach (Index index in indexList)
                newTable.Indexes.Add(index);
        }

        newTable.Create();

    }

    public Column CreateColumn(string name, DataType type, string @default,
        bool isIdentity, bool nullable)
    {
        Column column = new Column();

        column.DataType = type;
        column.Default = @default;
        column.Identity = isIdentity;
        column.Nullable = nullable;

        return column;
    }

    public Index CreateIndex(string name, bool isClustered, IndexKeyType type,
      string[] columnNameList)
    {

        Index index = new Index();

        index.Name = name;
        index.IndexKeyType = type;
        index.IsClustered = isClustered;

        foreach (string columnName in columnNameList)
            index.IndexedColumns.Add(new IndexedColumn(index, columnName));

        return index;
    }

}

这篇关于在 C# 中创建(按需)SQL Server 2008 Express 数据库的最佳实践?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！