Creating a local database at runtime using Visual Studio

Question

Beginner question - please, I can ask for advice on creating local database files programmatically at runtime. I want to be able to rename, delete them, etc. later. Using Windows Explorer, just like for text and other files, and copy them to other computers.

This is using Visual Studio Community 15 with C # installed by SQL Server Data Tools 14.0.50616.0. Microsoft SQL Server 2014 is installed on the computer.

As an example, I removed the extra parts of my program to leave the code below, which uses a Windows Form application with three buttons ( btnCreateDb , btnDeleteDb and btnDoesDbExist ) and a cbxDb drop-down list for the database name. It creates databases in the existing folder C:\DbTemp .

It will apparently create and delete a new database and make files, for example mydb1.mdf and mydb1.ldf in the folder, and indicate that they exist. However, if I delete two files using Explorer, it throws an exception if an attempt is made to delete or create a database; and btnDoesDbExist shows that it still exists.

Why does the database still exist when files were deleted by Windows Explorer? The code under btnDoesDatabaseExist does not apply to the file path, so it should see something else, but where? Is this the right way for a program user to create, delete, and discover these databases?

 using System; using System.Data; using System.Windows.Forms; //my additions using System.Data.SqlClient; namespace DataProg15 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } public static string form1ConnectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; Integrated Security = True; Connect Timeout = 30; "; private string form1DatabasePath = "C:\\DbTemp"; private void btnCreateDb_Click(object sender, EventArgs e) { string nameToCreate = cbxDb.Text; SqlConnection myConn = new SqlConnection(form1ConnectionString); string str = "CREATE DATABASE " +nameToCreate+ " ON PRIMARY " + "(NAME = " +nameToCreate+ "_Data, " + "FILENAME = '" +form1DatabasePath+ "\\" +nameToCreate+ ".mdf', " + "SIZE = 4MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME = " +nameToCreate+ "_Log, " + "FILENAME = '" +form1DatabasePath+ "\\" +nameToCreate+ ".ldf', " + "SIZE = 1MB, " + "MAXSIZE = 5MB, " + "FILEGROWTH = 10%)"; SqlCommand myCommand = new SqlCommand(str, myConn); try { myConn.Open(); myCommand.ExecuteNonQuery(); MessageBox.Show("DataBase '" + nameToCreate + "' was created successfully"); } catch (System.Exception ex) { MessageBox.Show("Exception in CreateDatabase " + ex.ToString(), "Exception in CreateDatabase", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (myConn.State == ConnectionState.Open) { myConn.Close(); } } } private void btnDeleteDb_Click(object sender, EventArgs e) { string nameToDelete = cbxDb.Text; string myConnectionString = form1ConnectionString + "AttachDBFileName = " + form1DatabasePath + "\\" + nameToDelete + ".mdf "; string str = "USE MASTER DROP DATABASE " + nameToDelete; SqlConnection myConn = new SqlConnection(myConnectionString); SqlCommand myCommand = new SqlCommand(str, myConn); myConn.Open(); try { myCommand.ExecuteNonQuery(); MessageBox.Show("DataBase '" + nameToDelete + "' was deleted successfully"); } catch (System.Exception ex) { MessageBox.Show(ex.ToString(), "Exception in DeleteDatabase '" +nameToDelete+ "'", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { if (myConn.State == ConnectionState.Open) { myConn.Close(); } } } private void btnDoesDbExist_Click(object sender, EventArgs e) { string nameToTest = cbxDb.Text; using (var connection = new SqlConnection(form1ConnectionString)) { using (var command = new SqlCommand(string.Format( "SELECT db_id('" +nameToTest+ "')", nameToTest), connection)) { connection.Open(); if ((command.ExecuteScalar() != DBNull.Value)) { MessageBox.Show("DataBase '" +nameToTest+ "' exists"); } else { MessageBox.Show("Database '" +nameToTest+ "' does not exist"); } } } } } 

}

Thanks to everyone for the answers, and your problem is very appreciated.

Now I understand that I am using the wrong database, so I tried to use SQL Server Compact instead. Uninstall, download again, and reinstall SQL Server Compact , including SP1 . We downloaded and installed the SQL Server Compact/SQLite Toolbox from https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1 . But Visual Studio has been displaying input errors all the time using System.Data.SqlServerCe . Also, when I type SqlCeEngine or SqlCecommand , I guess for the same reason.

In Visual Studio, SQL Server Data Tools and SQL Server Compact & SQLite Toolbox appear as installed products, but not SQL Server Compact . I need to install this in Visual Studio, and if so, how to do it?

Answer 1

In Solution Explorer under References , check that System.Data.SqlServerCe listed. If not, right-click on the References button, then Add Reference → Browse and select the System.Data.SqlServerCe.dll file, possibly in C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop . System.Data.SqlServerCe should now display under References .

The program below works and is much simpler. Thank you all for your help.

 using System; using System.Data; using System.Windows.Forms; //my additions using System.Data.SqlServerCe; using System.IO; namespace DataProg15 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } public static string form1DatabasePath = "C:\\DbTemp\\dbtemp1.sdf"; public static string form1ConnectionString = "Data Source = " +form1DatabasePath; private void btnCreateDb_Click(object sender, EventArgs e) { SqlCeEngine engine = new SqlCeEngine(form1ConnectionString); try { engine.CreateDatabase(); MessageBox.Show("DataBase '" +form1DatabasePath+ "' was created successfully"); } catch (System.Exception ex) { MessageBox.Show("Exception in CreateDatabase " + ex.ToString(), "Exception in CreateDatabase", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { engine.Dispose(); } } private void btnDeleteDb_Click(object sender, EventArgs e) { if (File.Exists(form1DatabasePath)) { try { File.Delete(form1DatabasePath); MessageBox.Show("DataBase '" +form1DatabasePath+ "' was deleted successfully"); } catch (System.Exception ex) { MessageBox.Show(ex.ToString(), "Exception in DeleteDatabase '" +form1DatabasePath+ "'", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } private void btnDoesDbExist_Click(object sender, EventArgs e) { if (File.Exists(form1DatabasePath)) { MessageBox.Show("DataBase '" +form1DatabasePath+ "' exists"); } else { MessageBox.Show("DataBase '" +form1DatabasePath+ "' does not exist"); } } } } 

Answer 2

SQL Server will not allow you to delete physical database files if this database is active - EVER. The only thing you could do is if the database is DETACHED (as mentioned earlier)

So, I suspect that you are not telling us quite right?

I would change the logic "check database exists":

select * from sys.databases, where name = 'yourdatabasename'

I would run this query anyway when you deleted your database, just to find out what it returns.