none
detecting that a sql server database exists. RRS feed

  • Question

  • PART 2

    I had someone help me with a way to detect is a sql server database exists. If it doesn't then create it and some tables.

    How ever after running it after completing some parts of my app. I have learned that it does some what work. When the database hasn't been created it works just fine. So when the app starts for the 1st time it creates the databases and tables. So here's the issue. Every time I run the app when the database and tables have all ready been created. It seems to want to create them again. Here's my code below.

    private void MapBase_Load(object sender, EventArgs e)
            {
                //Loads all the bms Files from the game directory
                //ChangedIt = false;
                btn_MapLocation.Enabled = false;
                catLabel.Text = catName;
                
                // this is for server based, not SQLEXPRESS,
                // try this for EXPRESS -> MSSQL$SQLEXPRESS
                var serviceName = "MSSQL$SQLEXPRESS";
                ServiceController controller = new ServiceController(serviceName);
    
                try
                {
    
                    if (controller.Status == ServiceControllerStatus.Running)
                    {
                        // Check to see if database exists
                        if (makeSQLdb())
                        {
                            MessageBox.Show("Database and tables created.", Application.ProductName);
                        }
                        else
                            this.Close();
                    }
                } 
                catch (Exception exc)
                {
                    // not found
                    MessageBox.Show(exc.Message + " Error SQL Server must be ruuning.", Application.ProductName);
                    this.Close();  return;              
                }
    
                loadDefaults();            
            }
    
            public bool makeSQLdb()
            {
    
                bool result = false;
    
                try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                    {
                        conn.Open();
    
                        string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper'";
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            int nRet = sqlCmd.ExecuteNonQuery();
    
                            if (nRet <= 0)
                            {
                                
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
    
                                conn.ChangeDatabase("mlhelper");
    
                                using (SqlCommand comm1 = new SqlCommand("CREATE TABLE games(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,gamename char(50), category char(10))", conn))
                                { comm1.ExecuteNonQuery(); }
    
                                using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,basename char(50),category char(10));", conn))
                                { command2.ExecuteNonQuery(); }
    
                                using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,mapname char(50),description char(500),filename char(13),filedate char(13),datedtime timestamp,baseid int,category char(50));", conn))
                                { command3.ExecuteNonQuery(); }
    
                                using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,cname char(50));", conn))
                                { command4.ExecuteNonQuery(); }
    
                                using (SqlCommand command5 = new SqlCommand("INSERT INTO category(cname) VALUES('jointops')", conn))
                                { command5.ExecuteNonQuery(); }
                                conn.Close();
                                result = true;
    
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Creating Error :" + ex.Message + "" + ex.StackTrace.ToString(), Application.ProductName);
                    this.Close();
                }
                            
                return result;
            }

    I just wish I had a way to test this code when I updated it. I couldn't until other parts of the code were updated. So anyone have an idea why the "nRet" doesn't seem to work.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software


    • Edited by Joesoft11a Monday, March 11, 2019 12:19 PM This covers from another post
    Monday, March 11, 2019 12:18 PM

Answers

  • Yes I understand that. The problem is that your DB is being created each time which means the database detection logic isn't working correctly. The code I originally posted was supposed to help deal with that but it doesn't seem it is. Put a breakpoint on the call to CheckDatabaseExists and run it when the DB exists. Step into that code and verify the results are coming back that it does exist. If it doesn't then the problem still lies in how you're querying for the table. If it does detect the DB then the rest of the logic you posted shouldn't execute (and you can step to verify) so that isn't where the problem lies. If it is creating the DB anyway then it isn't in this code posted so you are going to have to expand your review of your code to see where else it might be called from.

    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Joesoft11a Tuesday, March 12, 2019 6:53 PM
    Tuesday, March 12, 2019 5:36 PM
    Moderator

All replies

  • Hello,

    Use the following to test if the database exists. the var databaseExists would be then used to create if false.

    using (var cn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
    {
        using (var cmd = new SqlCommand() {Connection = cn, CommandText = "SELECT DB_ID(@dbName)"})
        {
            cmd.Parameters.AddWithValue("@dbName", "mlhelper");
            cn.Open();
            var databaseExists = cmd.ExecuteScalar()  is DBNull ?  false : true;
            Console.WriteLine(databaseExists);
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 11, 2019 2:05 PM
    Moderator
  • I cannot possibly recommend that you look for SQLExpress as a service and then close the app. This is not maintainable and makes an assumption that the SQL instance is using that name which is in no way required. Simply try to open the DB connection via SqlConnection. If it fails then either the network name is invalid, the service is offline or the DB doesn't exist. Handle this with a simple try-catch and tell the user the error. If you want to elaborate on "start the SQL service instance" or whatever than you can but doing all this checking up from is not useful. SqlConnection will already handle it.

    As for the ExecuteNonQuery call, it returns the # of rows impacted, not the results of the query. ExecuteNonQuery (as the name implies) is for non-query commands like INSERT/UPDATE/DELETE. If you want to know if a query has any results then either use ExecuteScalar (if it returns a single value) or ExecuteReader/FillDataSet to check for results. 

    I would modify your query to use EXISTS instead. Perhaps something like this.

    public bool DoesTableExist ( SqlConnection connection, string tableName )
    {
       using (var cmd = new SqlCommand("SELECT CASE WHEN EXISTS (SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper') THEN 1 ELSE 0 END"))
       {
          //If this fails with cast exception then use int and compare to 0
          return (bool)cmd.ExecuteScalar();
       };
    }


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, March 11, 2019 2:14 PM
    Moderator
  • Mike, Thanks. Your code detects a table. The tables only get created when the database doesn't exist. I only need to to detect the database. and a way to a apply it my my code.

    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Monday, March 11, 2019 9:36 PM
  • I get some of what your saying. Can you give me an idea about how to add this to my code without confusing me. I'll have to go back and see who posted the code for the "nRet" code.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Monday, March 11, 2019 9:38 PM
  • Hi  Joesoft11a,

    Thank you for posting here.

    For your question, you want to check if the database exists.

    You could refer to the following code, the bold code is modified.

    public bool makeSQLdb()
            {
    
                bool result = false;
    
                try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=Example;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;"))
                    {
                        conn.Open();
                        string databasename = "mlhelper";
                        string cmddb = String.Format("SELECT * FROM master.dbo.sysdatabases where name = '{0}'",databasename);
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            bool s = CheckDatabaseExists(databasename);
                            if (s == false)
                            {
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
    
                                conn.ChangeDatabase("mlhelper");
    
                                using (SqlCommand comm1 = new SqlCommand("CREATE TABLE games(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,gamename char(50), category char(10))", conn))
                                { comm1.ExecuteNonQuery(); }
    
                                using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,basename char(50),category char(10));", conn))
                                { command2.ExecuteNonQuery(); }
    
                                using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,mapname char(50),description char(500),filename char(13),filedate char(13),datedtime timestamp,baseid int,category char(50));", conn))
                                { command3.ExecuteNonQuery(); }
    
                                using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,cname char(50));", conn))
                                { command4.ExecuteNonQuery(); }
    
                                using (SqlCommand command5 = new SqlCommand("INSERT INTO category(cname) VALUES('jointops')", conn))
                                { command5.ExecuteNonQuery(); }
                                conn.Close();
                                result = true;
                            }
                            else
                            {
                                MessageBox.Show(string.Format("the database  {0} has existed",databasename));
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Creating Error :" + ex.Message + "" + ex.StackTrace.ToString(), Application.ProductName);
                    this.Close();
                }
    
                return result;
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                bool s = makeSQLdb();
        
            }
            private static bool CheckDatabaseExists( string databaseName)
            {
                string sqlCreateDBQuery;
                bool result = false;
    
                try
                {
                    SqlConnection tmpConn = new SqlConnection(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=Example;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;");
    
                    sqlCreateDBQuery = string.Format("SELECT database_id from sys.databases WHERE Name  = '{0}'", databaseName);
            
                   using (tmpConn)
                    {
                        using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
                        {
                            tmpConn.Open();
    
                            object resultObj = sqlCmd.ExecuteScalar();
    
                            int databaseID = 0;
    
                            if (resultObj != null)
                            {
                                int.TryParse(resultObj.ToString(), out databaseID);
                            }
    
                            tmpConn.Close();
    
                            result = (databaseID > 0);
                        }
                    }
                }
                catch (Exception ex)
                {
                    result = false;
                }
    
                return result;
            }
    

    Result:

    Best Regards,

    Jack.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 12, 2019 5:16 AM
    Moderator
  • Thanks Jack. I still have the same issue. It's not detecting that the database exists so it keeps wanting to create it again. So I'm confused.

    private void MapBase_Load(object sender, EventArgs e)
            {
                //Loads all the bms Files from the game directory
                //ChangedIt = false;
                btn_MapLocation.Enabled = false;
                catLabel.Text = catName;
                
                // this is for server based, not SQLEXPRESS,
                // try this for EXPRESS -> MSSQL$SQLEXPRESS
                var serviceName = "MSSQL$SQLEXPRESS";
                ServiceController controller = new ServiceController(serviceName);
    
                try
                {
                    bool s = makeSQLdb();
    
                    if (controller.Status == ServiceControllerStatus.Running)
                    {
                        // Check to see if database exists
                        if (s)
                        {
                            MessageBox.Show("Database and tables created.", Application.ProductName);
                        }
                        else
                            this.Close();
                    }
                } 
                catch (Exception exc)
                {
                    // not found
                    MessageBox.Show(exc.Message + " Error SQL Server must be ruuning.", Application.ProductName);
                    this.Close();  return;              
                }
    
                loadDefaults();            
            }
    
            public bool makeSQLdb()
            {
    
                bool result = false;
    
                try
                {
                    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                    {
                        conn.Open();
                        string databasename = "mlhelper";
                        string cmddb = String.Format("SELECT * FROM master.dbo.sysdatabases where name = '{0}'", databasename);
    
    
                        //string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper'";
    
                        using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
                        {
                            //Create the database 1st.
                            //int nRet = sqlCmd.ExecuteNonQuery();
    
                            bool s = CheckDatabaseExists(databasename);
                            if (s == false)
                            {
                                
                                using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
                                    command.ExecuteNonQuery();
    
                                conn.ChangeDatabase("mlhelper");
    
                                using (SqlCommand comm1 = new SqlCommand("CREATE TABLE games(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,gamename char(50), category char(10))", conn))
                                { comm1.ExecuteNonQuery(); }
    
                                using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,basename char(50),category char(10));", conn))
                                { command2.ExecuteNonQuery(); }
    
                                using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,mapname char(50),description char(500),filename char(13),filedate char(13),datedtime timestamp,baseid int,category char(50));", conn))
                                { command3.ExecuteNonQuery(); }
    
                                using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,cname char(50));", conn))
                                { command4.ExecuteNonQuery(); }
    
                                using (SqlCommand command5 = new SqlCommand("INSERT INTO category(cname) VALUES('jointops')", conn))
                                { command5.ExecuteNonQuery(); }
                                conn.Close();
                                result = true;
    
                            }
                            else
                            { MessageBox.Show(string.Format("the database  {0} does exist", databasename)); }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Creating Error :" + ex.Message + "" + ex.StackTrace.ToString(), Application.ProductName);
                    this.Close();
                }
                            
                return result;
            }
    
            private static bool CheckDatabaseExists(string databaseName)
            {
                string sqlCreateDBQuery;
                bool result = false;
    
                try
                {
                    SqlConnection tmpConn = new SqlConnection(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=Example;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;");
    
                    sqlCreateDBQuery = string.Format("SELECT database_id from sys.databases WHERE Name  = '{0}'", databaseName);
    
                    using (tmpConn)
                    {
                        using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
                        {
                            tmpConn.Open();
    
                            object resultObj = sqlCmd.ExecuteScalar();
    
                            int databaseID = 0;
    
                            if (resultObj != null)
                            {
                                int.TryParse(resultObj.ToString(), out databaseID);
                            }
    
                            tmpConn.Close();
    
                            result = (databaseID > 0);
                        }
                    }
                }
                catch (Exception ex)
                {
                    result = false;
                }
    
                return result;
            }
            
    

    • Edited by Joesoft11a Tuesday, March 12, 2019 1:05 PM pasted code in
    Tuesday, March 12, 2019 1:03 PM
  • As I mentioned before, ExecuteNonQuery doesn't do that. Did you change the code to use ExecuteScalar like I recommended and it still didn't work? Please post the updated code around detecting the DB.

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, March 12, 2019 1:35 PM
    Moderator
  • I did, I posted all of it above this post. Your changes were added in and your new method.

    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Tuesday, March 12, 2019 1:45 PM
  • I get some of what your saying. Can you give me an idea about how to add this to my code without confusing me. I'll have to go back and see who posted the code for the "nRet" code.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Here is a basic approach, in a data class, in the constructor check if the database exists, if not create it.

    using System;
    using System.Data.SqlClient;
    
    namespace YourNamespace
    {
        public class DataOperations
        {
            public DataOperations()
            {
                if (NeedToCreateDatabase())
                {
                    CreateDatabase();
                }
            }
            public void CreateDatabase()
            {
                // code to create database
            }
    
            private bool NeedToCreateDatabase()
            {
                bool databaseExists = false;
                using (var cn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
                {
                    using (var cmd = new SqlCommand() { Connection = cn, CommandText = "SELECT DB_ID(@dbName)" })
                    {
                        cmd.Parameters.AddWithValue("@dbName", "mlhelper");
                        cn.Open();
                        databaseExists = cmd.ExecuteScalar() is DBNull ? false : true;
                    }
                }
    
                return databaseExists;
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 12, 2019 1:51 PM
    Moderator
  • BTW Why did I go against how others who replied in regards to detecting if a database exists.

    using my suggestion you don't need to set the catalog to master as shown below, I have selected a database other than master and still get the results I was after that yes the database exists.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 12, 2019 2:00 PM
    Moderator
  • Just a comment:

    It is not always possible to detect whether a database exists or not. Cause the view any database permission could be removed.

    Thus the easiest approach is as already posted:

    - Open SQL connection to your database.

    - Open a table in your database.

    - If any of the above things fail, create your database.

    Caveat: You may get errors on all levels. So when you cannot create the database, examine the error. Maybe the database could not be created, cause it already exists (e.g. file overwrite error).

    Caveat: Even when you create a database, it is not guaranteed in a large environment, that you'll keep the db_owner.

    Thus: What is you ultimate goal here?


    Tuesday, March 12, 2019 2:09 PM
  • Sorry, I use threaded conversations so if you respond to someone else then it doesn't appear "above" my posts. I've found where you responded to someone else with the updated code.

    I still disagree with your checking of whether SQL Express exists and I wonder if that is causing the problem. But you should be able to step through your code and verify all this is working correctly. If this is intermittent then that would complicate things but step through the code and find out where the actual call is failing. If it is when you select from the system view then that would be an indicating that query is wrong. However if it is failing with service detection then that is a different problem entirely. You need to add in more error detection logic to narrow down where it is failing.

    As already mentioned permissions come into play as well. Because you're trying to create a DB at startup the runner of the code would need DBA permissions to create the database and then dbo permissions to create the tables. Generally an app wouldn't have this kind of permission.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, March 12, 2019 3:00 PM
    Moderator
  • Mike, sorry I guess there is some what of a miss under standing. If you check the code I'm not trying to detect sql server. Just checking to make sure it's running. If the user using my app and what's to use this option. He has to have sql server running. That's what that 1st part of code does. It just makes sure. As for making the databases and tables. I all ready fix all that or You or someone help me fix all of that. Creating the database and tables is not the issue. OK. With me so far.

    The issue is that when the database is created the app should see that and not rerun the create database and tables again. It should only do that one time. The issue is that I can't get it to stop creating the database and tables again when the app runs again and again. Does that help clear it up with you. If the app runs for the 1st time. It checks to see of Sql server is running. If it is running it checks to see if the database exists. If not it creates the database and tables. It should only create the database and tables one time only and only once. After the database and tables have been created and the users runs this part of the app again. It will error out because it's trying to create the database and tables again when it shouldn't.

    Hope that helps.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Tuesday, March 12, 2019 3:15 PM
  • Yes I understand that. The problem is that your DB is being created each time which means the database detection logic isn't working correctly. The code I originally posted was supposed to help deal with that but it doesn't seem it is. Put a breakpoint on the call to CheckDatabaseExists and run it when the DB exists. Step into that code and verify the results are coming back that it does exist. If it doesn't then the problem still lies in how you're querying for the table. If it does detect the DB then the rest of the logic you posted shouldn't execute (and you can step to verify) so that isn't where the problem lies. If it is creating the DB anyway then it isn't in this code posted so you are going to have to expand your review of your code to see where else it might be called from.

    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Joesoft11a Tuesday, March 12, 2019 6:53 PM
    Tuesday, March 12, 2019 5:36 PM
    Moderator
  • I wanted to say sorry to everyone. I didn't know that all the code that was given above wasn't all changed to see my sql server. After putting breaks in all over the place in my code. I fount the issue.

     SqlConnection tmpConn = new SqlConnection(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=Example;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;");
    

    The above code was never changed and I didn't think to go over all the code and make sure it was right. The above code should have been like this.

    SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;")
    This is why the code was returning a false result. No wonder I was having a hard time. It all works. Thank you everyone for your patience.  


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Tuesday, March 12, 2019 6:53 PM