SQLite- Check If Table Exists

Introduction 

In this article, you will learn to check if a table exists in the SQLite database or not.

How to check if a table exists in the database 

You execute the below command to check whether a table exists or not in the SQLite database:

SELECT count(*) FROM sqlite_master WHERE type='table' AND name='tableName';

It will return value either 0 or greater than 0. If the table doesn’t exist then the result will be 0 otherwise 1. 

You can use this query in any programming language to the existence of the table in the database. For an example below .NET code block will check the existence of the table in the SQLite database:

string sqliteDBFile = string.Format("{0}\\{1}", "D:\\Logs", "Log.db");
string tableName = "LogTable";
string columnName = "CreatedOn";
if (!File.Exists(sqliteLogDBFile))
{
    using (SQLiteConnection con = new SQLiteConnection(string.Format("data source={0}", sqliteLogDBFile)))
    {
       if (CheckIfTableExists(con, Constants.SQLiteLogTableName))
       {
            if (!CheckIfColumnExists(con, tableName, columnName))
            {
                        
            }
        }
    }
}

private bool CheckIfTableExists(SQLiteConnection conn, string tableName)
{
    if (conn.State == System.Data.ConnectionState.Closed)
        conn.Open();

    using (SQLiteCommand cmd = new SQLiteCommand(conn))
    {
        cmd.CommandText = $"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{tableName}';";
        object result = cmd.ExecuteScalar();
        int resultCount = Convert.ToInt32(result);
        if (resultCount > 0)
            return true;

    }
    return false;
}

You can also check that that if a column exists in the table or not using the below line of code which using the table metadata and then check the column name for the match.

private bool CheckIfColumnExists(SQLiteConnection conn, string tableName, string columnName)
{
    if (conn.State == System.Data.ConnectionState.Closed)
        conn.Open();

    using (SQLiteCommand cmd = new SQLiteCommand(conn))
    {
        cmd.CommandText = string.Format("PRAGMA table_info({0})", tableName);

        var reader = cmd.ExecuteReader();
        int nameIndex = reader.GetOrdinal("Name");
        while (reader.Read())
        {
            if (reader.GetString(nameIndex).Equals(columnName))
            {
                return true;
            }
        }
    }
    return false;
}

Checking a table exists or not before creating a new table 

Checking a table before creating or dropping a table is the common use case. To run the query without failing it with a fail-safe check. In SQLite, you can sure that table should be created in the database if it does not exist using the below query:

CREATE TABLE IF NOT EXISTS <table_name>> (column1_name <datatype>,....)

Conclusion 

You have learned that how can check the existence of the database base objects in an SQLite database.

NDepend - Dependency Graph Navigating Coupling Graph

Introduction

This is another post followed by NDepend - Improved Dependency Graph Feature. In this article, you will discover more about the Dependency Graph features e.g. focus on a node (double click a node in graph) or coupling graph (double click an edge in the graph). 

Navigation in Dependency Graph

Again, we going to use the same OrchardCore project used in the previous article on NDepend Dependency Graph. Open the project and then navigate to the Dependency Graph Zoom in a bit either using the mouse wheel or button in the toolbar.

Focusing on a node

Select a node in the graph and double click on it. Now you will see that node will be in the center of the diagram and it is in the view of focus. Now you can see the element and paths of the selected node.

Transitioning to coupling graph

Coupling is related to the dependency between the modules. You can see the Dependency Graph by double click an edge in the graph. It is a great feature to go through the different dependencies path for the module node, you selected.  

Here is the visualization of the navigation in the Dependency Graph 

Conclusion

NDepend Dependency Graph has a smooth transition between the different dependencies and it is good that it is navigating to the coupling graph just by clicking on the edges.

NDepend - Improved Dependency Graph Feature

Introduction

This is another post related to the NDepend tool and Code Quality for .NET Core application follow by this. 

NDepend makes .NET code beautiful by measuring quality with metrics, generate diagrams, and enforce decisions with code rules, right in Visual Studio.

I have used this tool for analyzing the .NET Core project and it is a great and intuitive tool to work with. 

The new version NDepend  v2020.1.0 is available with the latest features e.g.  Dependency Graph Completely Rebuilt

What is new with the Dependency Graph

I have also used the Dependency Graph in the previous version of the NDepend. It was much useful to identify the dependency between the different assemblies in the solution.

This time the NDepend team improvise this feature and restructured to analyze large project architecture. Below features make it easy to analyze using the dependency graph.

New navigation system

you can drag and drop from Visual Studio solution explorer, Expand/Collapse parent elements, Search elements in graphs by name with the search windows, also provided Undo / Redo feature.

New layout options

• Group-By Assemblies, Namespaces, Types, Clusters Filters to show or hide
• Box size proportional to element size, Edge width proportional to the coupling strength
• Color conventions instantly identify caller/callee elements
• Complex graph simplified with Clusters
• Export to SVG vector format or PNG bitmap format.

Let's explorer that how can we generate Dependency Graph by taking an example of the OrchardCore application which contains more than 143 projects. 

Open the application in the Visual Studio and create an NDepend project by attached to the Visual Studio solution.

Filter and select the project that you want to analyze.

Now a dialog box will open with options, View NDepend Dashboard, Show NDepend Interactive Dependency Graph. Click on the "Show NDepend Interactive Dependency Graph" button to see the dependency graph.

After a few seconds windows will open which is shown in the image below. It has the relationship between the projects and their dependencies.

You can also zoom in or out of the dependency graph to see a group of objects. See the below screenshot to know the zoom feature. you can move and see the project elements.

Conclusion

I genuinely think NDepend is very powerful and very easy to use for analyzing application architecture. The NDepend team and Patrick are very active and we can expect a lot of good improvements in future versions which help to create quality software.