C# 在 SQL Server 中以编程方式创建数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9015142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a database programmatically in SQL Server
提问by Pascal
How can I create a database programmatically and what is the minimum information I need to do this?
我如何以编程方式创建数据库，我需要做的最少信息是什么？
Pleaseno "SQL Server Management Object API " suggestions.
请不要“SQL Server 管理对象 API”建议。
采纳答案by Glenn
Create database 'Databasename'
回答by jeroenh
You can either use the SQL Server Management Object API(see task "creating, altering and removing databases"):
您可以使用SQL Server 管理对象 API（请参阅任务“创建、更改和删除数据库”）：
var srv = new Server();
var db = new Database(srv, "mydb");
db.Create();
Information on how to get started is here. During SQL server installation you need to install the client SDK, the SMO assemblies are in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
关于如何开始的信息在这里。SQL server安装过程中需要安装客户端SDK，SMO程序集在C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
Or if you don't want the dependency on these assemblies, you can also simply run DDL statements using ADO.Net (e.g. see this question):
或者，如果您不想依赖这些程序集，您也可以简单地使用 ADO.Net 运行 DDL 语句（例如，请参阅此问题）：
using (var connection = new SqlConnection(myConnectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "CREATE DATABASE mydb";
command.ExecuteNonQuery();
}
Obviously you need a correct connection string: known sql server instance and a user with CREATE DATABASE permission.
显然，您需要一个正确的连接字符串：已知的 sql server 实例和具有 CREATE DATABASE 权限的用户。
回答by MartinStettner
You need to open a connection to the server, i.e. you need a server and instance name.
您需要打开与服务器的连接，即您需要一个服务器和实例名称。
You also need the proper access rights to create a database, so you might need some user name and password depending on the authentication settings on the server.
您还需要适当的访问权限来创建数据库，因此您可能需要一些用户名和密码，具体取决于服务器上的身份验证设置。
From the server name and authentication information you can construct a connection stringand open a connection.
根据服务器名称和身份验证信息，您可以构建连接字符串并打开连接。
Then you can use the CREATE DATABASESQL command (see here on MSDN). The only needed parameter for this command is a database name.
然后您可以使用CREATE DATABASESQL 命令（参见MSDN 上的此处）。此命令唯一需要的参数是数据库名称。
回答by David Brabant
You need connection information: server, possibly instance, a user having create database rights on that server/instance and the corresponding password. Then you can use SMO for creating the database. Here is a small PowerShell example that you can very easily "translate" to C#, for example:
您需要连接信息：服务器，可能是实例，在该服务器/实例上具有创建数据库权限的用户以及相应的密码。然后您可以使用 SMO 来创建数据库。这是一个小的 PowerShell 示例，您可以非常轻松地将其“翻译”为 C#，例如：
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$s = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
# Instantiate the database object and add the filegroups
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($s, $DatabaseName)
$primaryFG = New-Object Microsoft.SqlServer.Management.Smo.FileGroup($db, 'PRIMARY')
$db.FileGroups.Add($primaryFG)
# Create Data file
$syslogname = $DatabaseName + '_SysData'
$dbdsysfile = New-Object Microsoft.SqlServer.Management.Smo.DataFile($primaryFG, $syslogname)
$primaryFG.Files.Add($dbdsysfile)
$dbdsysfile.FileName = $s.MasterDBPath + '\' + $syslogname + '.mdf'
$dbdsysfile.Size = [double](5.0 * 1024.0)
$dbdsysfile.GrowthType = 'KB'
$dbdsysfile.Growth = 10000.0
$dbdsysfile.IsPrimaryFile = 'True'
# Create Log file
$loglogname = $DatabaseName + '_Log'
$dblfile = New-Object Microsoft.SqlServer.Management.Smo.LogFile($db, $loglogname)
$db.LogFiles.Add($dblfile)
$dblfile.FileName = $s.MasterDBLogPath + '\' + $loglogname + '.ldf'
$dblfile.Size = [double](10.0 * 1024.0)
$dblfile.GrowthType = 'KB'
$dblfile.Growth = 10000.0
# Create database with READ_COMMITTED_SNAPSHOT isolation level.
# Other options can be set on $db object before calling Create.
$db.IsReadCommittedSnapshotOn = $true
$db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple
$db.Create()
回答by Dave Poole
Assuming you have the rights to fire off a CREATE DATABASE statement you can do so as you would any other query.
假设您有权触发 CREATE DATABASE 语句，您可以像执行任何其他查询一样执行此操作。
I should stress that being able to do so requires quite high privileges on the server and this would be restricted to DBAs in QA and Production environments.
我要强调的是，能够这样做需要在服务器上具有相当高的权限，这将仅限于 QA 和生产环境中的 DBA。
For that reason I would make sure that your connection uses Windows Integrated Security. That way when the appropriate DBA runs your application the app will function as requested.
出于这个原因，我会确保您的连接使用 Windows 集成安全性。这样，当适当的 DBA 运行您的应用程序时，应用程序将按要求运行。
Once you have created your database you will also need to fire off the T-SQL to create logins and create users. I'm taking it as obvious that CREATE TABLE/VIEW statements will be needed.
创建数据库后，您还需要触发 T-SQL 以创建登录名和创建用户。我认为显然需要 CREATE TABLE/VIEW 语句。
回答by nawfal
From the creators:
来自创作者：
// your connection string
string connectionString = "Server=(local)\netsdk;uid=sa;pwd=;database=master";
// your query:
var query = GetDbCreationQuery();
var conn = new SqlConnection(connectionString);
var command = new SqlCommand(query, conn);
try
{
conn.Open();
command.ExecuteNonQuery();
MessageBox.Show("Database is created successfully", "MyProgram",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if ((conn.State == ConnectionState.Open))
{
conn.Close();
}
}
To create in default location with default settings, just:
要使用默认设置在默认位置创建，只需：
static string GetDbCreationQuery()
{
// your db name
string dbName = "MyDatabase";
// db creation query
string query = "CREATE DATABASE " + dbName + ";";
return query;
}
Or, to create it in a specific location:
或者，在特定位置创建它：
static string GetDbCreationQuery()
{
// your db name
string dbName = "MyDatabase";
// path to your db files:
// ensure that the directory exists and you have read write permission.
string[] files = { Path.Combine(Application.StartupPath, dbName + ".mdf"),
Path.Combine(Application.StartupPath, dbName + ".ldf") };
// db creation query:
// note that the data file and log file have different logical names
string query = "CREATE DATABASE " + dbName +
" ON PRIMARY" +
" (NAME = " + dbName + "_data," +
" FILENAME = '" + files[0] + "'," +
" SIZE = 3MB," +
" MAXSIZE = 10MB," +
" FILEGROWTH = 10%)" +
" LOG ON" +
" (NAME = " + dbName + "_log," +
" FILENAME = '" + files[1] + "'," +
" SIZE = 1MB," +
" MAXSIZE = 5MB," +
" FILEGROWTH = 10%)" +
";";
return query;
}
Even in case the execution fails, give it another try. The db files might have got created.
即使执行失败，也请再试一次。db 文件可能已创建。
