如何使用C＃代码创建SQL server 2016 localdb？ [英] How to create SQL server 2016 localdb using C# code?
问题描述
安装了SQL Server 2016 LocalDB，并创建并运行了实例'FFw1a'：
C：\Program Files \ Microsoft SQL Server \ 130' \\ Tools \ Binn> sqllocaldb i Ffw1a
名称：FFw1a
版本： 13 。 0 . 2151 。 0
分享名称：
所有者：MinKyung \\ \\ cice.dong
自动创建：否
状态：正在运行
开始时间：2017/3/3 16 ： 48 ： 38
管道名称：np：\\.\pipe \LOCALDB＃E10BE0E6 \ tsql\query
我想通过下面的C＃代码创建一个本地db文件：
private void CreateDatabase（）
{
系统。 Data.SqlClient.SqlConnection tmpConn;
string sqlCreateDBQuery;
tmpConn = new SqlConnection（）;
tmpConn.ConnectionString = @ 数据源=（LocalDB）\ FFw1a; AttachDbFilename = 'D：\\\\TestDB_Data.mdf'; Integrated Security = True; Connect Timeout = 30; Encrypt = False;
sqlCreateDBQuery = CREATE DATABASE TestDB ON PRIMARY +
@ （NAME = TestDB_Data，FILENAME ='D：\y \\\\TestDB_Data.mdf'，SIZE = 2MB，FILEGROWTH = 10％） +
@ LOG ON（NAME = TestDB_Log ，FILENAME ='D：\y \\\\TestDB_Log.ldf'，SIZE = 1MB，FILEGROWTH = 10％）;
SqlCommand myCommand = new SqlCommand（sqlCreateDBQuery，tmpConn）;
尝试
{
tmpConn.Open（）;
myCommand.ExecuteNonQuery（）;
MessageBox.Show（ 数据库已成功创建！，< span class =code-string> Create database，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
catch （System.Exception ex）
{
MessageBox.Show（ex.ToString（），< span class =code-string> Create database，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
最后
{
tmpConn.Close（）;
}
return ;
}
但它会导致'tmpConn.Open（）'出现异常，并且消息为：
尝试附加文件D：\y \\\\TestDB_Data.mdf的自动命名数据库失败。存在具有相同名称的数据库，或者无法打开指定的文件，或者它位于UNC共享上。
如何更改连接字符串和sqlCreateDBQuery以创建一个数据库文件成功了吗？
我尝试过：
< pre lang =c＃> private void CreateDatabase（）
{
System .Data.SqlClient.SqlConnection tmpConn;
string sqlCreateDBQuery;
tmpConn = new SqlConnection（）;
tmpConn.ConnectionString = @ 数据源=（LocalDB）\ FFw1a; AttachDbFilename = 'D：\\\\TestDB_Data.mdf'; Integrated Security = True; Connect Timeout = 30; Encrypt = False;
sqlCreateDBQuery = CREATE DATABASE TestDB ON PRIMARY +
@ （NAME = TestDB_Data，FILENAME ='D：\y \\\\TestDB_Data.mdf'，SIZE = 2MB，FILEGROWTH = 10％） +
@ LOG ON（NAME = TestDB_Log ，FILENAME ='D：\y \\\\TestDB_Log.ldf'，SIZE = 1MB，FILEGROWTH = 10％）;
SqlCommand myCommand = new SqlCommand（sqlCreateDBQuery，tmpConn）;
尝试
{
tmpConn.Open（）;
myCommand.ExecuteNonQuery（）;
MessageBox.Show（ 数据库已成功创建！，< span class =code-string> Create database，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
catch （System.Exception ex）
{
MessageBox.Show（ex.ToString（），< span class =code-string> Create database，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
最后
{
tmpConn.Close（）;
}
return ;
}
查看错误消息：
尝试为文件D：\y \\\\TestDB_Data.mdf附加自动命名的数据库失败。存在具有相同名称的数据库，或者无法打开指定的文件，或者它位于UNC共享上。
非常明确！
要么：
1）SQL服务器中已经有一个同名的DB。检查数据库的SQL当前持有什么，你不能附加一个与你不知道你想要的名称相同的数据库。
2）检查文件和文件夹的访问权限包含它的树：SQL服务器不在您的用户ID下运行，因此它需要足够的SQL权限才能使用它。
3）D：驱动器在哪里？如果它不是物理上在同一台计算机上，则SQL无法连接到它。
和其他2件事BTW：
1）从不硬代码连接字符串。这意味着您必须在开发和生产之间更改代码 - 这意味着除了实时生产数据库之外，您无法测试发布软件，这是一个非常非常糟糕的主意。配置文件是可行的方法！
2）不要养成附加数据库的习惯 - 这是一种特殊的开发模式，只能在Express版本中使用 - 生产数据库不会能够使用它（并且它比普通连接慢，并且它必须专门为该文件启动一个新的SQL实例）。
SQL Server 2016 LocalDB installed, and instance 'FFw1a' is created and running:
C:\Program Files\Microsoft SQL Server\130\Tools\Binn>sqllocaldb i Ffw1a
Name: FFw1a
Version: 13.0.2151.0
Share name:
Owner: MinKyung\icey.dong
Auto Create: No
Status: Running
Start Time: 2017/3/3 16:48:38
Pipe name: np:\\.\pipe\LOCALDB#E10BE0E6\tsql\query
I want to create a local db file by below C# code:
private void CreateDatabase()
{
System.Data.SqlClient.SqlConnection tmpConn;
string sqlCreateDBQuery;
tmpConn = new SqlConnection();
tmpConn.ConnectionString = @"Data Source=(LocalDB)\FFw1a;AttachDbFilename='D:\y\2\TestDB_Data.mdf'; Integrated Security=True;Connect Timeout=30;Encrypt=False";
sqlCreateDBQuery = "CREATE DATABASE TestDB ON PRIMARY "+
@"(NAME = TestDB_Data, FILENAME = 'D:\y\2\TestDB_Data.mdf', SIZE = 2MB, FILEGROWTH = 10%) "+
@"LOG ON (NAME = TestDB_Log, FILENAME = 'D:\y\2\TestDB_Log.ldf', SIZE = 1MB, FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
try
{
tmpConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("Database has been created successfully!", "Create Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}
return;
}
But it cause exception on 'tmpConn.Open()', and the message is:
An attempt to attach an auto-named database for file D:\y\2\TestDB_Data.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
How can I change the connection string and sqlCreateDBQuery to create a database file successfully?
What I have tried:
private void CreateDatabase()
{
System.Data.SqlClient.SqlConnection tmpConn;
string sqlCreateDBQuery;
tmpConn = new SqlConnection();
tmpConn.ConnectionString = @"Data Source=(LocalDB)\FFw1a;AttachDbFilename='D:\y\2\TestDB_Data.mdf'; Integrated Security=True;Connect Timeout=30;Encrypt=False";
sqlCreateDBQuery = "CREATE DATABASE TestDB ON PRIMARY "+
@"(NAME = TestDB_Data, FILENAME = 'D:\y\2\TestDB_Data.mdf', SIZE = 2MB, FILEGROWTH = 10%) "+
@"LOG ON (NAME = TestDB_Log, FILENAME = 'D:\y\2\TestDB_Log.ldf', SIZE = 1MB, FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
try
{
tmpConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("Database has been created successfully!", "Create Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}
return;
}Look at the error message:
An attempt to attach an auto-named database for file D:\y\2\TestDB_Data.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It's pretty explicit!
Either:
1) There is a DB with the same name already in SQL server. Check what DB's SQL is holding at the moment, you cannot attach a DB with the same name as it wouldn't know which one you meant.
2) Check the access permissions on the file and the folder tree containing it: SQL server does not run under your user ID, so it needs sufficient permissions for SQL to use it.
3) Where is the "D:" drive? If it isn't physically on the same computer, SQL cannot attach to it.
And 2 other things BTW:
1) Never hard code connection strings. It means you have to change the code between development and production - which means you can't test the release software except against the live production DB and that is a very, very bad idea. Config files are the way to go!
2) Don't get into the habit of attaching DBs - it's a special mode for development, and is only available in the Express editions - the production DB will not be able to use it (and it's slower than a "normal" connection as well as it has to spin up a new instance of SQL specifically for the file).
这篇关于如何使用C＃代码创建SQL server 2016 localdb？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
