Dynamically connect to server and create database when app runs

Question

I want my app to connect to server and create database on whatever pc it runs. I have code, but it works only on my computer and not others, when i run it on other computers it won't connect to that computers server and won't create database

This is my code, I have splash screen form and I want, when app runs, to check if database exists on that pc and if don't to create it. What my problem is, is that my code doesn't catch server name and just shows message "Bad server name"

public partial class Splash : Form
    {
        public Splash()
        {
            InitializeComponent();              
            Properties.Settings.Default.Server = "(local)";
            timer.Enabled = true;
        }

        private void CheckDatabase()
        {
            try
            {
                using (SqlConnection conn = Helper.ConnectionToServer())
                {
                    if (Properties.Settings.Default.Server != string.Empty)
                    {
                        using (SqlCommand cmd = new SqlCommand(
                            "IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDb') SELECT 1 ELSE SELECT 0",
                            conn))
                        {
                            conn.Open();
                            int value = (int)cmd.ExecuteScalar();
                            conn.Close();

                            if (value != 1)
                            {
                                MessageBox.Show("Database doesn't exist");
                                timer.Enabled = false;
                                CreateDatabase();

                            }

                        }
                    }
                    else
                    {
                        timer.Enabled = true;
                    }
                }
            }
            catch
            {
                MessageBox.Show("Bad server name");

            }
        }
        private void CreateDatabase()
        {
            string serverStr = "CREATE DATABASE [MyDb]";
            string databaseStr = @"
CREATE TABLE [dbo].[Worker] (
    [FirstName] NVARCHAR (50) NOT NULL,
    [Password] NVARCHAR (50) NOT NULL,
    [LastName] NVARCHAR (50) NOT NULL,

    PRIMARY KEY CLUSTERED ([Password] ASC)
);

CREATE TABLE [dbo].[Article] (
    [ItemName]     NVARCHAR (50) NOT NULL,
    [Barcode]  NVARCHAR (50) NOT NULL,
    [Price]   MONEY NOT NULL,

    CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED ([Barcode] ASC)
);";
            using (SqlConnection conn = Helper.ConnectionToServer())
            {
                try
                {
                    using (SqlCommand comm = new SqlCommand(serverStr, conn))
                    {
                        conn.Open();
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                }
                catch
                {
                    MessageBox.Show("Bad server name");

                }

            }
            using (SqlConnection conn = Helper.ConnectionToDatabase())
            {
                using (SqlCommand comm = new SqlCommand(databaseStr, conn))
                {
                    conn.Open();
                    comm.ExecuteNonQuery();
                    conn.Close();
                    timer.Enabled = true;
                }
            }
        }

I have a helper class also that I use whenever I use sql commands

 class Helper
    {
        public static SqlConnection ConnectionToDatabase()
        {
            string con = string.Format(@"Data Source=DESKTOP-RFJ38NM;Initial Catalog=MyDb;Integrated Security=True", Properties.Settings.Default.Server);
            return new SqlConnection(con);
        }
        public static SqlConnection ConnectionToServer()
        {
            string con = string.Format(@"Data Source=DESKTOP-RFJ38NM;Integrated Security=True", Properties.Settings.Default.Server);
            return new SqlConnection(con);
        }

    }

Answer 1

The following logic creates a new database, your two tables. Up front I did this not in a splash form but a normal form where there is one section I felt should be asynchronous which is for obtaining the default SQL-Server instance name using SmoApplication where you would add a reference to it, in my case the dll is found here, many be different on your computer

C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

In the form there is a single button and a click event for the button. When clicked the code transfers to a separate class where I mashed up your code.

using System;
using System.Windows.Forms;

namespace StackOverFlow
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private async void button1_Click(object sender, EventArgs e)
        {
            var ops = await Operations.Create();

            if (ops.FoundServerInstanceName)
            {
                if (ops.CheckDatabase())
                {
                    MessageBox.Show("Ready to work with tables!!!");
                }
                else
                {
                    if (ops.HasException)
                    {
                        MessageBox.Show($"Encountered the following issue(s)\n{ops.ExceptionMessage}");
                    }
                    else
                    {
                        MessageBox.Show("Failed");
                    }
                }
            }
        }
    }
}

At the top of Operations class (below) there are several properties

• one for remembering exceptions thrown
• One for indicating if the database exists
• A property indicating the default instance name for SQL-Server was located.
• The connection string for creating the new database
• A connection string for using the new database

The method Initialize gets the instance name for SQL-Server. Followed by checking if the database exists then if not we create the database and tables.

To build this I'm using VS2015 where I take advantage of string interpolation in messages and also setting properties.

Hopefully this is of some use with the current task.

using Microsoft.SqlServer.Management.Smo;
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace StackOverFlow
{

    public class Operations
    {
        string mExceptionMessage;
        public string ExceptionMessage { get { return mExceptionMessage; } }
        public bool HasException { get; set; }

        bool _databaseExists = false;

        bool mFoundServerInstanceName;
        public bool FoundServerInstanceName { get { return mFoundServerInstanceName; } }

        static string databaseServer;
        static string masterDefaultCatalog = "Master";
        string _masterConnectionString = $"Data Source={databaseServer};Initial Catalog={masterDefaultCatalog};Integrated Security=True";
        string MasterConnectionString
        {
            get
            {
                return _masterConnectionString;
            }
            set
            {
                _masterConnectionString = value;
            }
        }

        static string DefaultCatalog = "MyDb";
        string _ConnectionString = $"Data Source={databaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True";
        string ConnectionString
        {
            get
            {
                return _ConnectionString;
            }
            set
            {
                _ConnectionString = value;
            }
        }
        public static async Task<Operations> Create()
        {
            var ops = new Operations();
            await ops.Initialize();
            return ops;
        }
        async Task<bool> Initialize()
        {
            return await GetDefaultInstanceAsync();
        }
        public async Task<bool> GetDefaultInstanceAsync()
        {
            var serverInstanceTable = new DataTable();
            try
            {
                await Task.Run(() => { serverInstanceTable = SmoApplication.EnumAvailableSqlServers(true); });
                if (serverInstanceTable != null)
                {
                    databaseServer = serverInstanceTable.Rows[0].Field<string>("name");
                    mFoundServerInstanceName = true;
                }
                else
                {
                    mFoundServerInstanceName = false;
                }

                return true;

            }
            catch (Exception ex)
            {
                mExceptionMessage = ex.Message;
                HasException = true;
                return false;
            }

        }
        /// <summary>
        /// Determine if the database exists
        /// </summary>
        /// <returns></returns>
        public bool CheckDatabase()
        {

            try
            {
                using (SqlConnection conn = new SqlConnection() { ConnectionString = MasterConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand($"IF EXISTS (SELECT name FROM sys.databases WHERE name = '{DefaultCatalog}') SELECT 1 ELSE SELECT 0", conn))
                    {
                        conn.Open();
                        int value = (int)cmd.ExecuteScalar();
                        conn.Close();

                        if (value != 1)
                        {
                            CreateDatabase();
                            _databaseExists = true;
                        }
                        else
                        {
                            _databaseExists = true;
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                mExceptionMessage = ex.Message;
                _databaseExists = false;
                HasException = true;
            }

            return _databaseExists;

        }
        /// <summary>
        /// create the database and two tables
        /// </summary>
        /// <returns></returns>
        bool CreateDatabase()
        {
            string tableCreateScript = $@"
            USE [{DefaultCatalog}]
            CREATE TABLE [dbo].[Article](
                [ItemName] [nvarchar](50) NOT NULL,
                [Barcode] [nvarchar](50) NOT NULL,
                [Price] [money] NOT NULL,
             CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED 
            (
                [Barcode] ASC
            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
            ) ON [PRIMARY];
            CREATE TABLE [dbo].[Worker](
                [FirstName] [nvarchar](50) NOT NULL,
                [Password] [nvarchar](50) NOT NULL,
                [LastName] [nvarchar](50) NOT NULL,
            PRIMARY KEY CLUSTERED 
            (
                [Password] ASC
            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
            ) ON [PRIMARY];";

            using (SqlConnection conn = new SqlConnection() { ConnectionString = MasterConnectionString })
            {
                try
                {
                    using (SqlCommand comm = new SqlCommand($"CREATE DATABASE [{DefaultCatalog}];", conn))
                    {
                        conn.Open();

                        comm.ExecuteNonQuery();

                        comm.CommandText = tableCreateScript;
                        comm.ExecuteNonQuery();

                        return true;

                    }
                }
                catch (Exception ex)
                {
                    mExceptionMessage = ex.Message;
                    HasException = true;
                    return false;
                }

            }

        }
    }
}