none
SQL Server database creation failure using c# console application RRS feed

  • Question

  • C# console application fails to create database through SQL Server on my local machine, however, no errors or exceptions are thrown!

    In case if the DATABASE already cexists, I'm able to create and update table information!


    • Edited by atomV13 Monday, March 9, 2020 12:25 PM
    Monday, March 9, 2020 12:24 PM

All replies

  • Hello,

    Please provide relevant code and the full SQL in code block (second button in the toolbar to the right). 

    Also makes sure the user account performing these operations have permissions to perform these operations.

    Edit: here is a simple example which can be folded into one line if need be and used in a Command object..

    IF  NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'DBNAME')
    BEGIN
        CREATE DATABASE [DBNAME]
    	PRINT 'Just created'
    END ELSE BEGIN
    	PRINT 'Already exists'
    END;
    
    Still proper permissions a reeded.

    Last option (with proper permissions) is SMO

    https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/creating-altering-and-removing-databases?view=sql-server-ver15


    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 9, 2020 1:10 PM
    Moderator
  • string connectionString = "Data Source=[localServerName];Integrated Security=SSPI;";

    //databaseName = "DB_TCMB_Currencies";

     public class SqlServerDatabaseCreator
        {
    
            /*
             * 
             */
            public void Creator(List<Dictionary<string, string>> currenciesList, string connectionString)
            {
                if (DatabaseExists(connectionString)) //create dataabse if it doesnt exists
                {
                    SaveCurrentCurrencyRates(currenciesList, connectionString);
                }
                else {
                    CreateDatabase(connectionString);
    
                    SaveCurrentCurrencyRates(currenciesList, connectionString);
    
                }
            }
    
            /*
             * DatabaseExists() checks whether database exists, returns true if yes, 
             * and false otherwise
             */
            private static bool DatabaseExists(string connectionString)
            {
                using (var sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
    
                    using (var sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandText = $"SELECT db_id('{Globals.databaseName}')";
    
                        return sqlCommand.ExecuteScalar() != DBNull.Value;
                    }
                }
            }
    
            /*
             * CreateDatabase() creates database and a currecies table with no values
             */
            private static void CreateDatabase(string connectionString)
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    string queryingString =
                        $"CREATE DATABASE {Globals.databaseName};" +
                           $"CREATE TABLE {Globals.databaseName}.dbo.Currency " +
                           "( currency_id  INT NOT NULL IDENTITY PRIMARY KEY," +
                           "currency_code VARCHAR(10) NOT NULL ," +
                           "currency_name VARCHAR(20) NOT NULL," +
                           "forex_buying VARCHAR(20) NOT NULL," +
                           "forex_selling VARCHAR(20) NOT NULL," +
                           "banknote_buying VARCHAR(20) NOT NULL," +
                           "banknote_selling VARCHAR(20) NOT NULL,)";
                    using (SqlCommand sqlCommand = new SqlCommand(queryingString, sqlConnection)) 
                    {
                        try
                        {
                            sqlConnection.Open();
                            sqlCommand.ExecuteNonQuery();
                        }
                        catch (Exception e) 
                        {
                            Console.WriteLine("Error: " + e.Message);
                        }
                    }
                }
            }
    
            /*
             * 
             */
            private static void SaveCurrentCurrencyRates(List<Dictionary<string, string>> currenciesList, string connectionString)
            {
                string sqlValues = "";
                for (int i = 0; i < currenciesList.Count; i++) 
                {
                    sqlValues += " (";
                    
                    int count = 0;
                    foreach (KeyValuePair<string, string> item in currenciesList[i])
                    {
                        if (count == currenciesList[i].Count-1)
                        {
                            sqlValues += $"'{item.Value}'";
                        }
                        else 
                        {
                            sqlValues += $"'{item.Value}', ";
                        }
                        count++;
                    }
                    if (i == currenciesList.Count - 1) {
                        sqlValues += ")";
                    }else
                        sqlValues += "), ";
                }
    
                using (var sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (var sqlCommand = sqlConnection.CreateCommand())
                    {
                        try
                        {
                            sqlCommand.CommandText = $"TRUNCATE TABLE {Globals.databaseName}.dbo.Currency;" +
                                $"INSERT INTO {Globals.databaseName}.dbo.Currency VALUES {sqlValues};";
    
                            sqlCommand.ExecuteNonQuery();
                        }
                        catch (Exception e) 
                        {
                            Console.WriteLine("Error: " + e.Message);
                            sqlCommand.CommandText = $"CREATE TABLE {Globals.databaseName}.dbo.Currency " +
                           $"( currency_id INT NOT NULL IDENTITY PRIMARY KEY," +
                           $"currency_code VARCHAR(10) NOT NULL ," +
                           $"currency_name VARCHAR(20) NOT NULL," +
                           $"   forex_buying VARCHAR(20) NOT NULL," +
                           $"  forex_selling VARCHAR(20) NOT NULL," +
                           $" banknote_buying VARCHAR(20) NOT NULL," +
                           $"banknote_selling VARCHAR(20) NOT NULL,)" +
                           $"TRUNCATE TABLE {Globals.databaseName}.dbo.Currency;" +
                           $"INSERT INTO {Globals.databaseName}.dbo.Currency VALUES {sqlValues};";
    
                            sqlCommand.ExecuteNonQuery();
                        }
                    }
                    sqlConnection.Close();
                }
            }
        }
    CreteDatabase() function executes, but the database is not created as a result, therefore, 
    SaveCurrentCurrencyRates() fucntion fails to create a table.

    Tuesday, March 10, 2020 10:36 AM
  • If the user does not have permissions (which I indicated in my first reply) SQL-Server may reject the create database with no errors raised. So you must ensure that the user/or yourself have permissions outside of SSMS to create the database.

    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 10, 2020 10:57 AM
    Moderator
  • I hacked up your code (and took out Globals) to test it out, see if this helps. Just change the connection string and only call CreateDatabase method.

    SqlServerDatabaseCreator.CreateDatabase();


    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 10, 2020 3:15 PM
    Moderator
  • Hi atomV13,

    Have you tried the SMO mentioned by Karen?

    I created the database and tables very simply with the following code.

               Server srv = new Server();
                Database db = null;
                DatabaseCollection databaseCollection = srv.Databases;
                if (!databaseCollection.Contains("Test_SMO_Database"))
                {
                    db = new Database(srv, "Test_SMO_Database");
                    db.Create(false);
                }
                db = srv.Databases["Test_SMO_Database"];
                string sql = $"CREATE TABLE dbo.Currency " +
                       "( currency_id  INT NOT NULL IDENTITY PRIMARY KEY," +
                       "currency_code VARCHAR(10) NOT NULL ," +
                       "currency_name VARCHAR(20) NOT NULL," +
                       "forex_buying VARCHAR(20) NOT NULL," +
                       "forex_selling VARCHAR(20) NOT NULL," +
                       "banknote_buying VARCHAR(20) NOT NULL," +
                       "banknote_selling VARCHAR(20) NOT NULL,)";
                db.ExecuteNonQuery(sql);
    

    This way will connect to the local default instance of SQL Server.

    If you want to connect to other instances, you can also connect like this.

    Server srv1 = new Server("<server_location>");
    srv1.ConnectionContext.LoginSecure = false;
    srv1.ConnectionContext.Login = "<username>";
    srv1.ConnectionContext.Password = "<password>";
    srv1.ConnectionContext.Connect();
    Database sourceDb = srv1.Databases["<database_name>"];
    Table sourceTbl = sourceDb.Tables["<table_name>"];

    Best Regards,

    Timon


    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.

    Thursday, March 19, 2020 6:18 AM