Database Operations Using C#: Table Operations

In this article, I am going to explain how you can create a database table, using C# code -- but the main aim here is to run a SQL script, using C# code. Now, one can argue, why do we have to do this, when we have the stored procedure doing the same thing for us? Well, there are several reasons why it’s really flexible to do it. If you have a system with versioning, you can use this code to apply the different versions. Now, we will start by just focusing on how to create a table but later on, we will see how powerful this code can be, though which we can achieve more.

Below is the Main class which will have the caller’s code. In the constants, I have defined the connection strings. An object of the ExecuteSql is created, which will help us to run our script. Note that this will only run the script on the master db. Next is creating the object for the schema writer. Here lies the code, which will load the script to create a table.

class Program
{
    private static ExecuteSql _executeMaster;

    private static string _connectionString =
        "Data Source=.;Integrated Security=SSPI;Initial Catalog=testme;Application Name=Test;Connection Timeout=300";

    private static DatabaseSchemaCreator databaseSchemaCreator;
    private static string _masterDatabaseName = "master";

    static void Main(string[] args)
    {
        _executeMaster = new ExecuteSql(openConnectionToMaster);
        databaseSchemaCreator = new DatabaseSchemaCreator(_executeMaster);
        databaseSchemaCreator.ApplyScript(@"C:\Users\Documents\Visual Studio 2015\Projects\DB\", "testme");
        Console.WriteLine("done");
        Console.ReadLine();
    }


    private static SqlConnection openConnectionToMaster()
    {
        var connectionStringBuilder = new SqlConnectionStringBuilder(_connectionString);
        connectionStringBuilder.InitialCatalog = _masterDatabaseName;
        var conn = new SqlConnection(connectionStringBuilder.ConnectionString);
        conn.Open();
        return conn;
    }

}

In the apply script path, there is a path to the script, meaning that the script lies in a file, so it can be anything; it can be a create table or drop table script. Given below is also the Db script with the DB name on the top and as I mentioned earlier, we will be running our script on the master db. We need to mention which db, we want to point to; else our table will be created on the master. 

databaseSchemaCreator = new DatabaseSchemaCreator(_executeMaster);
databaseSchemaCreator.ApplyScript(@"C:\Users\Documents\Visual Studio 2015\Projects\DB\", "testme");

USE [$(DBNAME)]


CREATE TABLE [Jobss](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Type] [nvarchar](max) NULL,
    [UserName] nvarchar(500) NOT NULL,
    [AssignedNode] [nvarchar](max) NULL,
    [Status] [nvarchar](max) NULL
 CONSTRAINT [PK_Jobss] PRIMARY KEY CLUSTERED
(
    [Id] ASC
))

This is the class which can be used for multiple purposes. Now, imagine, if you have a system where you have a new version released every month or so and it uses db as well. How will you manage the DB changes? How will your update know which scripts are new and which are already installed? The best solution is to name all your scripts with a number and save the last number you executed. Thus, in our case, this applies to the script, which will load all the script from the local file system and check in the DB the number of the scripts which are already installed and start running the new scripts in the right order.

public class DatabaseSchemaCreator
    {
        private readonly ExecuteSql _executeSql;

        public DatabaseSchemaCreator( ExecuteSql executeSql)
        {
            _executeSql = executeSql;
        }

        public void ApplyScript(string releasesPath, string databaseName)
        {
            if (!Directory.Exists(releasesPath)) return;

            var scriptsDirectoryInfo = new DirectoryInfo(releasesPath);
            var scriptFiles = scriptsDirectoryInfo.GetFiles("*.sql", SearchOption.TopDirectoryOnly);


            foreach (var scriptFile in scriptFiles)
            {
                var sql = File.ReadAllText(scriptFile.FullName);
                sql = ReplaceScriptTags(sql, databaseName);
                _executeSql.ExecuteNonQuery(sql);
            }
        }


        private string ReplaceScriptTags(string script, string name)
        {
            script = script.Replace("$(DBNAME)", name);
            return script;
        }


    }

This is the helper class, which will run SQL on the provided connection. Note, there is no transient error handling implemented here. In my later articles, I will try to explain with the help of the same code, using the transient handling. The reason to handle these errors is to be able to be run the code on Cloud. With the use of transient error handling, proper retry logic will be in place. 

public class ExecuteSql
    {
        private readonly Func<SqlConnection> _openConnection;

        public ExecuteSql(Func<SqlConnection> openConnection)
        {
            _openConnection = openConnection;
        }

        public void ExecuteCustom(Action<SqlConnection> action)
        {
            using (var connection = _openConnection())
            {
                action(connection);
            }
        }

        public int ExecuteScalar(string sql, int timeout = 30, IDictionary<string, object> parameters = null)
        {
            parameters = parameters ?? new Dictionary<string, object>();

            var result = 0;

            using (var connection = _openConnection())
            {
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;

                        foreach (var parameter in parameters)
                        {
                            command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                        }
                        command.CommandTimeout = timeout;

                        {
                            command.CommandText = sql;
                            command.CommandType = CommandType.Text;
                            result = (int) (command.ExecuteScalar() ?? default(int));
                        }

                        transaction.Commit();
                    }
                }
            }

            return result;
        }

        public void ExecuteTransactionlessNonQuery(string sql, int timeout = 30)
        {
            using (var connection = _openConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = timeout;
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
            }
        }

        public void ExecuteNonQuery(string sql, int timeout = 30, IDictionary<string, object> parameters = null)
        {
            parameters = parameters ?? new Dictionary<string, object>();

            using (var connection = _openConnection())
            {
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandTimeout = timeout;

                        {
                            command.Parameters.Clear();
                            foreach (var parameter in parameters)
                            {
                                if (sql.Contains(parameter.Key))
                                {
                                    command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                                }
                            }
                            command.CommandText = sql;
                            command.CommandType = CommandType.Text;
                            command.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                }
            }
        }


    }

Do comment, if you need any help.