Creating local database at run time with Visual Studio

Question

Beginner's question - please can I ask for advice on creating local database files programmatically at run time. I want to be able later to rename, delete them etc using Windows Explorer in the same way as for text and other files, and to copy them to other computers.

This is using Visual Studio Community 15 with C#, installed SQL server Data Tools 14.0.50616.0. The computer has Microsoft SQL Server 2014.

For an example I have removed the surplus parts of my program to leave the code below, which uses a Windows Form Application with 3 buttons (btnCreateDb, btnDeleteDb, and btnDoesDbExist) and a combobox cbxDb for the database name. It makes databases in an 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 state that they exist. However, if I delete the two files using Explorer, it throws an exception if an attempt is made to delete or to create the database; and btnDoesDbExist shows that it still exists.

Why does the database still appear to exist when the files have been deleted by Windows Explorer? The code under btnDoesDatabaseExist doesn't refer to the path of the files, so it must be seeing something else, but where? Is this a correct method for the user of the program to create, delete, and detect 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");
                }
            }
        }

    }
}

}

Thank you to all for replies, and your trouble is greatly appreciated.

I now understand that I'm using the wrong database so I've tried to use SQL Server Compact instead. Have uninstalled, downloaded again, and reinstalled SQL Server Compact including SP1. Have also downloaded and installed SQL Server Compact/SQLite Toolbox from https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1 . But Visual Studio has throughout shown an error when I type using System.Data.SqlServerCe . Also when I type SqlCeEngine or SqlCecommand, I assume for the same reason.

In Visual Studio, SQL Server Data Tools and SQL Server Compact & SQLite Toolbox are shown as installed products, but not SQL Server Compact. Do I need to install this into Visual Studio, and if so how is it done?

Answer 1

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

The program below appears to work, and is much simpler. Thanks to all for assistance.

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 let you delete the physical files of a database if that database is active - EVER. The only you would ever be able to do this is if the database is DETACHED ( as mentioned earlier )

So I suspect something your telling us isn't quite right ??

I would change your "check database exists" logic to be;

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

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