最佳实践创建（点播）在C＃中的SQL Server 2008前preSS数据库？ [英] Best practice to create (on demand) SQL Server 2008 Express databases in C#?

问题描述

其目的是处理用户的数据（你可以打电话给他们的项目，文档，文件，或其他）在一个全新的SQL Server 2008前preSS数据库。该数据预计将占据超过4GB的空间要少得多可用以EX preSS版（这也是免费派发）。

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

例如，每个用户选择文件 - >新建命令的时候，一个新的空数据库将在指定位置创建。另外，类似的命令，文件 - >打开必须提供支持来检索数据库列表中选择一个用于打开。

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.

因此​​，下面的问题必须解决： 一）应用程序必须能够创建连接字符串和附加数据库到SQL Server 2008前preSS到code（C＃） 二）的应用程序必须能够检索（再次通过code）一种列表与所有可用的数据库，给用户一个机会来选择一个开

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前preSS数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！