Correct Use Of Try Catch For The Sql Connection In C#

Is this code correct in means of Try/Catch? I need to value whether the action is done or not to inform the user about the result, so I will then get the bool value to know if conn

Solution 1:

Your method can actually have 3 outcomes:

1. The table was created successfully (method returns true)
2. The table already exists (method returns false)
3. There was an error trying to create the table (exception is thrown)

So you should handle the exception outside of this method and you should not blindly catch all Exceptions. You should only handle the SqlException so that other exceptions will not be handled. Also you should be logging the exception somewhere as a good practice.

For more information on the SqlExceptionhttps://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception(v=vs.110).aspx

publicstaticboolCreateSQLDatabaseTable()
{
    var connString = "Server=localhost\\SQLEXPRESS;Integrated Security = SSPI; database = MyDB";
    string cmdText = "SELECT count(*) as Exist from INFORMATION_SCHEMA.TABLES where table_name =@Product";

    using (var sqlConnection = new SqlConnection(connString))
    {
        using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
        {
            sqlCmd.Parameters.Add("@Product", System.Data.SqlDbType.NVarChar).Value = "Product";
            sqlConnection.Open();
            sqlCmd.ExecuteScalar();
            if ((int)sqlCmd.ExecuteScalar() != 1)
            {
                using (SqlCommand command = new SqlCommand("CREATE TABLE Product (Id INT, UserId TEXT, CreationDate TEXT, Name TEXT)", sqlConnection))
                {
                    command.ExecuteNonQuery();
                    returntrue;
                }
            }
        }
    }

    returnfalse;
}

publicstaticvoidMain()
{
    try
    {
        bool wasCreated = CreateSQLDatabaseTable();
    }
    catch (SqlException ex)
    {
        // Handle the SQL Exception as you wish
        Console.WriteLine(ex.ToString());
    }
}

Solution 2:

I would solve such a problem through a single query that does the exists check itself and returns whether it was created or not.

publicstaticboolCreateSQLDatabaseTable() {
        var connString = "...";
        conststring tableName = "Product";
        string cmdText = $"IF EXISTS (SELECT TOP(1) 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = DB_NAME() AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '{tableName}' AND TABLE_TYPE = 'BASE TABLE') BEGIN\r\n" +
                         $"  SELECT CAST(0 AS BIT) AS CreatedNow\r\n" +
                         $"END ELSE BEGIN\r\n" +
                         $"  CREATE TABLE dbo.{tableName}(Id INT, CreatorId INT, CreationDate DATETIME, CreationDateUtc DATETIME, Name NVARCHAR)\r\n" +
                         $"  SELECT CAST(1 AS BIT) AS CreatedNow\r\n" +
                         $"END";

        using (var sqlConnection = new SqlConnection(connString)) {
            using (var sqlCmd = new SqlCommand(cmdText, sqlConnection)) {
                sqlConnection.Open();
                return (bool)sqlCmd.ExecuteScalar();
            }
        }
    }