Creating database and two tables linked by foreign key

Question

These commands do what I want, but I'm trying to find out the best approach for this. My main areas of concern are: error handling and disposal of resources. In regards to error handling, I'm catching the errors by wrapping the caller in a try catch. In what concerns to disposal of resources, is it enough to wrap my connection and command in using statements?

Also notice I'm using two different strings to create the DB and to create the table. I couldn't find an optimal approach that would do the whole thing in one command but I guess it's not absolutely necessary.

Bear in mind this is for training only, so I'm not concerned about hardcoding my SQL queries.

The caller:

try
{
    DatabaseManager.CheckDatabase();
    DatabaseManager.CreateTables();
}
catch (SqlException ex)
{
    Console.WriteLine(ex.Message);
}
finally
{
    DoSomethingElse();
}

DbManager:

internal class DatabaseManager
    {
        public static void CheckDatabase()
        {
            string chebkDbString = ConfigurationManager.AppSettings.Get("CheckDbString");

            using (var connection = new SqlConnection(chebkDbString))
            {
                using (var command = connection.CreateCommand())
                {
                    connection.Open();
                    command.CommandText =
                        @"IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'quizDb')
                           BEGIN
                             CREATE DATABASE quizDb;
                           END;
                         ";
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void CreateTables()
        {
            string chebkDbString = ConfigurationManager.AppSettings.Get("CreateTableString");

            using (var connection = new SqlConnection(chebkDbString))
            {
                using (var command = connection.CreateCommand())
                {
                    connection.Open();
                    command.CommandText =
                        @" IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'stack')
                        CREATE TABLE stack (
                          Id int IDENTITY(1,1) NOT NULL,
                          Name varchar(100) NOT NULL UNIQUE,
                          PRIMARY KEY (Id)
                         );
                      ";
                    command.ExecuteNonQuery();

                    command.CommandText =
                        @" IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'flashcard')
                        CREATE TABLE flashcard (
                          Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
                          Question varchar(30) NOT NULL,
                          Answer varchar(30) NOT NULL,
                          StackId int NOT NULL 
                            FOREIGN KEY 
                            REFERENCES stack(Id) 
                            ON DELETE CASCADE 
                            ON UPDATE CASCADE
                         );
                      ";
                    command.ExecuteNonQuery();
                }
            }
        }

Answer 1

In regards to error handling, I'm catching the errors by wrapping the caller in a try catch.

Seems good.

In what concerns to disposal of resources, is it enough to wrap my connection and command in using statements?

Yes, the using statement will dispose the resources for you once it's finished or exited; seems you had it done right.

Also notice I'm using two different strings to create the DB and to create the table. I couldn't find an optimal approach that would do the whole thing in one command but I guess it's not absolutely necessary.

Here is the code snippet.

public static void CheckDatabase()
    {
        string chebkDbString = ConfigurationManager.AppSettings.Get("CheckDbString");

        using (var connection = new SqlConnection(chebkDbString))
        {
            using (var command = connection.CreateCommand())
            {
                connection.Open();
                command.CommandText = @" IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'quizDb')
begin
CREATE DATABASE quizDb
end

use quizDb
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'stack')
begin
      CREATE TABLE stack (
        Id int IDENTITY(1,1) NOT NULL,
        Name varchar(100) NOT NULL UNIQUE,
        PRIMARY KEY (Id)
       )
end
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'flashcard')
begin
      CREATE TABLE flashcard (
        Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        Question varchar(30) NOT NULL,
        Answer varchar(30) NOT NULL,
        StackId int NOT NULL 
          FOREIGN KEY 
          REFERENCES stack(Id) 
          ON DELETE CASCADE 
          ON UPDATE CASCADE
                         )
end"
                command.ExecuteNonQuery();
            }
        }
    }

Now you only need to call DatabaseManager.CheckDatabase(); and it will do everything for you.