How to create SQL server 2016 localdb using C# code?
-
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?
-
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?
Change the file names in the CREATE DATABASE statement so they use different file names to the one in your original connection string. You use AttachDbFilename='D:\y\2\TestDB_Data.mdf' in the connection string, then try to create a database file of the same name.
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Change the file names in the CREATE DATABASE statement so they use different file names to the one in your original connection string. You use AttachDbFilename='D:\y\2\TestDB_Data.mdf' in the connection string, then try to create a database file of the same name.
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Tried, but also failed. The exception is caused on 'tmpConn.Open()', not create databse anymore.
tmpConn.ConnectionString = @"Data Source=(LocalDB)\FFw1a;AttachDbFilename='D:\y\2\TestDB_Data.mdf'; Integrated Security=True;Connect Timeout=30;Encrypt=False";
remove this