How to deploy Windows app with SQL Server database?

Question

I have developed an application in Visual Studio 2012 with a SQL Server 2012 database. When I try to publish the application to test it, it works correctly on my machine (that includes the database in a SQL Server data folder), but when I move that published app to another machine it doesn't work.

I want to know the simplest way to deploy the project together with its database. I have seen a solution to integrate the SQL database with my application is to use a localdb but I don't understand the steps to use it. I need all the steps to deploy an application with a SQL Server 2012 database in order to install the application on another PC without having to install SQL Server 2012 on that PC.

Answer 1

It seems not too long ago I faced the same problem. I looked at SQL Server, MySQL, SQL Server Express and SQL Server Compact edition. I wanted a simple database for a standalone application. SQL Server Compact fit the bill for a standalone, self-contained database. SQLite is another excellent choice for a standalone database, but that is another answer. SQL Server Express pros/cons is covered in another answer already.

To deploy SQL Server Compact (CE) you would could either include the installer of SQLCE40Runtime_x86-ENU.exe or you include the needed directories and Dlls manually that the installer creates for you. For more deployment information see: https://msdn.microsoft.com/en-us/library/aa983326%28v=vs.140%29.aspx

The connection string I would use is

ConnectionString = "Data Source=" + System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location) + "\\DevEssai.sdf;Persist Security Info=False";

For additional ideas for a connection string see: http://www.connectionstrings.com/

Any choice you ultimate make, there are pros and cons to each choice that you will have to make. Either way it will require you to do some research to choose the best choice for your application. Don't be intimidated. Once you research it further, it is not as hard as you might think initially. It is simply a learning curve that everyone has to go through.

Answer 2

Your app doesn't work on the other machine because you deployed it with the same configuration on your local machine without a database.

• If you don't have SQL server on the machine you can use SQL Server Express (it's installed by default with Visual Studio unless you explicitly tell it not to do) and update web.config:

  <connectionStrings>
      <add name="testCon" 
           connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" 
           providerName="System.Data.SqlClient"/>
  </connectionStrings>
  

• Deploy database to server and change the connection string

  <connectionStrings>
      <add name="testCon" 
           providerName="System.Data.SqlClient" 
           connectionString="Data Source=Server_Name;Initial Catalog=DB_Name; User Id=User_Name;Password=Password;" /> 
  </connectionStrings>
  

Answer 3

I have created a simple conversion program to convert one of my SQL Server tables into a SQL Server Compact Edition table. I just created a Windows form with a single button that says "Convert". It will create the SQL Server CE database and then read each record from the SQL Server database table and write it to the equivalent table record in the SQL Server Compact database.

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.Sql;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Data.SQLite;

namespace SampleConversion
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnConvert_Click(object sender, EventArgs e)
        {
            string cmd = "";
            int count = 0;

            create_SQLCE_database(); // Create the SQL Server CE database file and a table within it

            string SQLconnectionString = "server=(local); database=PTHData; Trusted_Connection=True;"; // open PTHData.mdf
            string SQLCEconnectionString = "Data Source=" + Application.StartupPath + "\\pthData.sdf;Persist Security Info=False"; // open PTHDATA.sdf

            // open the input and output database
            SqlCeConnection SQLCEconnection = new SqlCeConnection(SQLCEconnectionString);

            try
            {
                SQLCEconnection.Open();
            }
            catch (SqlCeException ex)
            {
                string errorMessages = "A SQL Server CE exception occurred on open.\n" + ex.Message;
                MessageBox.Show(errorMessages, "Convert");
                return;
            }
            SqlConnection SQLconnection = new SqlConnection(SQLconnectionString);
            try
            {
                SQLconnection.Open();
            }
            catch (SqlException ex)
            {
                string errorMessages = "A SQL exception occurred on open.\n" + ex.Message;
                MessageBox.Show( errorMessages, "Convert");
                return;
            }

            //Databases are not open, time to convert
            SqlCommand cmdread = new SqlCommand();
            cmdread.Connection = SQLconnection;
            cmdread.CommandText = "Select * from USTimeZones";
            SqlDataReader drread = null;

            SqlCeCommand cmdwrite = new SqlCeCommand();
            cmdwrite.Connection = SQLCEconnection;

            try
            {
                drread = cmdread.ExecuteReader();
                while (drread.Read())
                {
                    drread["timezone"].ToString();
                    cmd = "Insert into USTimeZones values ('" + drread["state"].ToString() + "','" +
                        drread["city"].ToString() + "','" + drread["county"].ToString() + "','" +
                        drread["timezone"].ToString() + "','" + drread["timetype"].ToString() + "','" +
                        drread["latitude"].ToString() + "','" + drread["longitude"].ToString() + "')";
                    cmdwrite.CommandText = cmd;
                    try
                    {
                        cmdwrite.ExecuteNonQuery();
                        count++;
                    }
                    catch (SqlCeException ex)
                    {
                        string errorMessages = "A SQL exception occurred on writing the SQL Server CE record.\n" + ex.Message;
                        MessageBox.Show(errorMessages, "Convert");
                        SQLCEconnection.Close();
                        SQLconnection.Close();
                        return;
                    }

                }
            }
            catch (SqlException ex)
            {
                string errorMessages = "A SQL exception occurred reading records.\n" + ex.Message;
                MessageBox.Show(errorMessages, "Convert");
            }
            catch (Exception ex)
            {
                string errorMessages = "A General exception occurred reading records.\n" + ex.Message;
                MessageBox.Show(errorMessages, "Convert");
            }

            MessageBox.Show("Records written: " + count.ToString(), "Conversion complete");
            drread.Close();
            SQLconnection.Close();
            SQLCEconnection.Close();
        }

        private void create_SQLCE_database()
        {
            string connectionString = "Data Source=" + Application.StartupPath + "\\pthData.sdf;Persist Security Info=False";

            try
            {
                SqlCeEngine en = new SqlCeEngine(connectionString);
                en.CreateDatabase();
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show("Unable to create the SQL Server CE pthData database\n" + ex.Message, "Create SQL Server CE file/database error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Unable to create the SQL Server CE pthData database\n" + ex.Message, "Create SQL Server CE file/database error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
            }

            // file created, now create tables
            SqlCeConnection cn = new SqlCeConnection(connectionString);
            if (cn.State == ConnectionState.Closed)
                cn.Open();

            SqlCeCommand cmd;
            string commandString = "Create table USTimeZones\n";

            // create USTimeZones file
            commandString = "Create table USTimeZones\r\n";
            commandString += "(state nvarchar(30), city nvarchar(100), county nvarchar(50), timezone nvarchar(10), ";
            commandString += "timetype int, latitude nvarchar(10), longitude nvarchar(10),  ";
            commandString += "PRIMARY KEY(state, city, county, timezone, timetype))";
            cmd = new SqlCeCommand(commandString, cn);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message + "\n Command string: " + commandString, "Error creating USTimeZoness", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error creating USTimeZones", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
            }

            cn.Close();
        }

    private void btnSQLiteConvert_Click(object sender, EventArgs e)
    {
        string cmd = "";
        int count = 0;

        create_SQLite_database(); // Create the SQLite database file and a table within it

        string SQLconnectionString = "server=(local); database=PTHData; Trusted_Connection=True;"; // open PTHData.mdf
        string SQLiteconnectionString = "Data Source=" + Application.StartupPath + "\\pthData.sqlite;Version=3;";

        // open the input and output database
        SQLiteConnection SQLiteconnection = new SQLiteConnection(SQLiteconnectionString);

        try
        {
            SQLiteconnection.Open();
        }
        catch (SQLiteException ex)
        {
            string errorMessages = "A SQLite exception occurred on open.\n" + ex.Message;
            MessageBox.Show(errorMessages, "Convert");
            return;
        }

        SqlConnection SQLconnection = new SqlConnection(SQLconnectionString);

        try
        {
            SQLconnection.Open();
        }
        catch (SqlException ex)
        {
            string errorMessages = "A SQL exception occurred on open.\n" + ex.Message;
            MessageBox.Show(errorMessages, "Convert");
            return;
        }

        //Databases are not open, time to convert
        SqlCommand cmdread = new SqlCommand();
        cmdread.Connection = SQLconnection;
        cmdread.CommandText = "Select * from USTimeZones";

        SqlDataReader drread = null;

        SQLiteCommand cmdwrite = new SQLiteCommand();
        cmdwrite.Connection = SQLiteconnection;

        try
        {
            drread = cmdread.ExecuteReader();

            while (drread.Read())
            {
                drread["timezone"].ToString();
                cmd = "Insert into USTimeZones values ('" + drread["state"].ToString() + "','" +
                    drread["city"].ToString() + "','" + drread["county"].ToString() + "','" +
                    drread["timezone"].ToString() + "','" + drread["timetype"].ToString() + "','" +
                    drread["latitude"].ToString() + "','" + drread["longitude"].ToString() + "')";
                cmdwrite.CommandText = cmd;

                try
                {
                    cmdwrite.ExecuteNonQuery();
                    count++;
                }
                catch (SQLiteException ex)
                {
                    string errorMessages = "An SQL exception occurred on writing the SQLite record.\n" + ex.Message;
                    MessageBox.Show(errorMessages, "Convert");
                    SQLiteconnection.Close();
                    SQLconnection.Close();
                    return;
                }

            }
        }
        catch (SqlException ex)
        {
            string errorMessages = "A SQL exception occurred reading records.\n" + ex.Message;
            MessageBox.Show(errorMessages, "Convert");
        }
        catch (Exception ex)
        {
            string errorMessages = "A General exception occurred reading records.\n" + ex.Message;
            MessageBox.Show(errorMessages, "Convert");
        }

        MessageBox.Show("Records written: " + count.ToString(), "Conversion complete");
        drread.Close();
        SQLconnection.Close();
        SQLiteconnection.Close();
    }

    private void create_SQLite_database()
    {
        string connectionString = "Data Source=" + Application.StartupPath + "\\pthData.sqlite;Version=3;";

        try
        {
            SQLiteConnection.CreateFile("pthData.sqlite");
        }
        catch (SQLiteException ex)
        {
            MessageBox.Show("Unable to create the SQLite database\n" + ex.Message + "\nConnection string: " + connectionString, "Create SQLite file/database error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
        }
        catch (Exception ex)
        {
            MessageBox.Show("Unable to create the SQLitedatabase\n" + ex.Message + "\nConnection string: " + connectionString, "Create SQLite file/database error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
        }
        // file created, now create tables
        SQLiteConnection cn = new SQLiteConnection(connectionString);
        if (cn.State == ConnectionState.Closed)
            cn.Open();

        SQLiteCommand cmd;
        string commandString = "Create table if not exists USTimeZones\n";

        // create time zones file
        commandString += "(state nvarchar(30), city nvarchar(100), county nvarchar(50), timezone nvarchar(10), ";
        commandString += "timetype int, latitude nvarchar(10), longitude nvarchar(10),  ";
        commandString += "PRIMARY KEY(state, city, county, timezone, timetype))";

        cmd = new SQLiteCommand(commandString, cn);

        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (SQLiteException sqlexception)
        {
            MessageBox.Show(sqlexception.Message + "\n Command string: " + commandString, "Error creating USTimeZones", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message + "\n Command string: " + commandString, "Error creating USTimeZoness", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
        }
    }
}