我如何通过编程方式创建数据库,需要哪些最少的信息才能实现?
请不要提供 "SQL Server Management Object API" 的建议。
我如何通过编程方式创建数据库,需要哪些最少的信息才能实现?
请不要提供 "SQL Server Management Object API" 的建议。
你可以使用 SQL Server Management Object API(参见任务“创建、修改和删除数据库”):
var srv = new Server();
var db = new Database(srv, "mydb");
db.Create();
如何入门信息请点击这里。在安装 SQL Server 时,你需要安装客户端 SDK,SMO 组件位于 C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies。
或者,如果不想依赖这些组件,也可以使用 ADO.Net 运行 DDL 语句(例如参考此问题):
using (var connection = new SqlConnection(myConnectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "CREATE DATABASE mydb";
command.ExecuteNonQuery();
}
显然,您需要正确的连接字符串:已知的 SQL Server 实例和具有 CREATE DATABASE 权限的用户。
Create database 'Databasename'
来自创建者的说法:
// 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();
}
}
要使用默认设置在默认位置创建,只需执行以下操作:
static string GetDbCreationQuery()
{
// your db name
string dbName = "MyDatabase";
// db creation query
string query = "CREATE DATABASE " + dbName + ";";
return query;
}
或者,将其创建在特定位置:
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;
}
您需要连接信息:服务器,可能是实例,一个在该服务器/实例上具有创建数据库权限的用户以及相应的密码。然后,您可以使用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()
你需要打开到服务器的连接,即你需要一个服务器和实例名称。
你还需要适当的访问权限来创建数据库,所以根据服务器上的身份验证设置,你可能需要一些用户名和密码。
从服务器名称和身份验证信息中,你可以构造一个连接字符串并打开连接。
然后你可以使用CREATE DATABASE
SQL命令(参见MSDN这里)。该命令的唯一必需参数是一个数据库名称。
Integrated Security = true;
- user1234433222