Easy Visual Studio C# database example using a local dynamically created database (Microsoft Sql Server Compact)

I wanted to be able to create and access a local sql database using c#. I wanted to avoid having to install any database instances on the machine on which this app could run. The application needs to be as self-contained as possible. I am also very used to the way data is accessed in php so this uses sql query strings passed to execute commands, and result sets that can be iterated through.

First, a reference needs to be added to SqlServerCE...

Then make sure the reference is added in code...

using System.Data.SqlServerCe;

Here is the full code...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlServerCe;  // add this to use sql server compact methods
using System.IO; // add this to use file.Delete,file.Exists and Path methods


namespace sql_server_compact_test_application
{
    public partial class Form1 : Form
    {
        private SqlCeEngine database;
        private SqlCeConnection conn;
        private SqlCeCommand cmd;
        string databaseFileName;

        public Form1()
        {
            InitializeComponent();

            /* I want the database file to be in the same folder as the executable so 
			   Path.GetDirectoryName(Application.ExecutablePath) is used to find that out. */

            databaseFileName = Path.GetDirectoryName(Application.ExecutablePath) + "\\testymctest.sdf";
            string connString = "DataSource=\"testymctest.sdf\"; Password=\"OIUhjso&1\"";
            databaseNameLabel.Text = "Database file: " + databaseFileName;

            if (File.Exists(databaseFileName))
            {
                infoLabel.Text = "Connected to existing file";
                conn = new SqlCeConnection(connString);
                cmd = new SqlCeCommand();
                cmd.Connection = conn;
            }
            else
            {
                infoLabel.Text = "Created database as it did not exist";

                database = new SqlCeEngine(connString);
                database.CreateDatabase();
                conn = new SqlCeConnection(connString);

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                string query = "create table testtable (id int not null identity(1,1) primary key,sausage nvarchar(50))";
                cmd = new SqlCeCommand(query,conn);

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (SqlCeException sqlexception)
                {
                    
                    MessageBox.Show("Caught sql exception: " + sqlexception.Message,"SQL Error",MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Caught exception: " + ex.Message,"Error", MessageBoxButtons.OK,MessageBoxIcon.Error);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        private void deleteDatabaseButton_Click(object sender, EventArgs e)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            File.Delete(databaseFileName);
            infoLabel.Text = 
				"Database deleted. You will need to restart the app.  " + 
				"The code could be modified to recreate the database after deletion, but hasn't yet";

        }

        private void getDataButton_Click(object sender, EventArgs e)
        {
            textBox2.ResetText();

            if (conn.State == ConnectionState.Closed) conn.Open();

            cmd.CommandText = "select * from testtable";

            SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            //SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None);

            int SausageIndex = results.GetOrdinal("sausage"); // get the column position of the field named 'sausage'

			
            if (results.ReadFirst()) textBox2.Text += results.GetString(SausageIndex) + "\r\n";
            else MessageBox.Show("There is no data to show yet"); 

			
            while (results.Read())
            {
                textBox2.Text += results.GetString(SausageIndex) + "\r\n";
            }


        }

        private void AddButton_Click(object sender, EventArgs e)
        {
            string sql = "insert into testtable (sausage) values (@sausage)";
            //try
            //{
            if (conn.State == ConnectionState.Closed) conn.Open();
            cmd.CommandText = sql;

            /* Note,  here I'm passing the user-supplied input using AddWithValue to avoid sql injection.  
               It could be included directly in the sql string above but this is safer */

            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@sausage", textBox1.Text);
            cmd.ExecuteNonQuery();
            //}
            //catch (SqlCeException sqlexception)
            //{
            //    MessageBox.Show("SQL Exception: " + sqlexception.Message, "SQL Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show("Exception: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            //}
            //finally
            //{
            conn.Close();
            //}

            /* I commented out the try/catch code that was in the example code where I got this from because 
			   I find that the way an app deals with uncaught exceptions actually provides more useful information 
			   than this... and the IDE stops at the code that caused the exception */
        }       
    }
}