1
\$\begingroup\$

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();
                }
            }
        }
\$\endgroup\$
1
  • 1
    \$\begingroup\$ So what exactly is your concern? \$\endgroup\$ Jan 25, 2022 at 12:06

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ I'm not sure if this works. Are you sure that use quizDb work if you create a database just before you issue the use command without committing the previous changes? \$\endgroup\$ Jan 26, 2022 at 7:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.