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 a value of 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 uses 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 be sure that a 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 how can check the existence of the database base objects in an SQLite database.

Run CSharp “Hello World!” program on Developer Command prompt

To start writing your first program without using the Visual Studio or other tools, you just need to open “Command Prompt”. Just type command on the search in Start menu either you are using Windows 7 or any higher.

image_thumb[10]_thumb[3][4]

Visual Studio installs few command line tools to do some operation using command tools and these can be invoked using the “Developer Command Prompt for VS2015”(For Visual Studio 2015). If someone has not installed Visual Studio then just install .NET Framework on the computer.

After this we are ready to create and compile our “Hello World!” program using the .NET Framework compilers.

Now create a file “hello.cs” using the “notepad”. We are going to write our code to print “Hello World!” on the screen for this example. “.cs” is the common extension for the C# file and “.vb” for Visual Basic. So now we going to create a C# program now so this file will contain C# code.

image_thumb[16]_thumb[1][4]

After running this command a prompt will appear with message “Do you want to create a new file?”. Click on “Yes” to create a new file.

In this we are going to create a class with a static method “Main”. In a Console application that would run on command prompt, CLR look for class named “Program” with a “Main” method to run the program.

image_thumb[8][3]

Now save this file and come back to the command prompt.

Now one of the tool “csc.exe” exists in the “Windows” directory in the .NET framework installation paths. It can be located at the path “C:\Windows\Microsoft.NET\Framework\{.NET Framework}”. Under the Framework folder you will find installation of .NET Framework multiple version. Below is the path of the latest one on my system. It is the “Visual C# Command Line Compiler” which compiles the C# code.

image_thumb[24][4]

Now we are going to compile the C# code then execute the program. After compiling the program C# compiler produce a executable file(.exe).

To compile the created C# code file, use the below command line on command prompt. Just pass the created “hello.cs” as parameter.

  1. csc hello.cs

image_thumb[29][4]

On windows we can run exe. Now run the hello.exe to view the result of our example program.

image_thumb[32][4]

That’s all done. We have created an example program run on the command prompt using the C# command line compiler.

Actually it transforms C# code into Microsoft Intermediate Language. This can either an exe or dynamic link library. We specify the assembly format to the csc.exe using the /target:library option. See below image for various target options:

image_thumb[37][4]

  Happy Coding!

What is the correct way to handle exceptions and UnobservedTaskException in the TPL?

Information from: A Task's exception(s) were not observed either by Waiting on the Task or accessing its Exception property. As a result, the unobserved exception was

If you create a Task, and you don't ever call task.Wait() or try to retrieve the result of a Task<T>, when the task is collected by the garbage collector, it will tear down your application during finalization. For details, see MSDN's page on Exception Handling in the TPL.

The best option here is to "handle" the exception. This can be done via a continuation - you can attach a continuation to the task, and log/swallow/etc the exception that occurs. This provides a clean way to log task exceptions, and can be written as a simple extension method, ie:

public static void LogExceptions(this Task task)
{
    task.ContinueWith( t =>
    {
         var aggException = t.Exception.Flatten();
         foreach(var exception in aggException.InnerExceptions)
             LogException(exception);
    }, 
    TaskContinuationOptions.OnlyOnFaulted);
}

With the above, you can prevent any task from tearing down the app, and logging it, via:
Task.Factory.StartNew( () => 
   { 
       // Do your work...
   }).LogExceptions();

Alternatively, you can subscribe to the TaskScheduler.UnobservedTaskException and handle it there. 

Continuation of the task could determine the status of the parent task whether it get faulted or completed to success. A continuation can find out if an exception was thrown by the antecedent Task by the antecedent task's exception property. The following code snippet will print the results of a NullReferenceException to the console.

Task task1 = Task.Factory.StartNew (() => { throw null; });
Task task2 = task1.ContinueWith (ant => Console.Write(ant.Exception());

If task1 throws an exception and this exception is not captured/queried by the continuation then it is considered unhandled and the application get halted. With continuations it is enough to establish the result of the task via the Status property.
asyncTask.ContinueWith(task =>
{
    // Check task status.
    switch (task.Status)
    {
        // Handle any exceptions to prevent UnobservedTaskException.             
        case TaskStatus.RanToCompletion:
            if (asyncTask.Result)
            {
                // Do stuff...
            }
            break;
        case TaskStatus.Faulted:
            if (task.Exception != null)
                mainForm.progressRightLabelText = task.Exception.InnerException.Message;
            else
                mainForm.progressRightLabelText = "Operation failed!";
        default:
            break;
    }
}


If you don't use continuations you either have to wait on the task in a try/catch block or query a task's Result in a try/catch block. See below code snippet:
int x = 0;
Task task = Task.Factory.StartNew (() => 7 / x);
try
{
    task.Wait();
    // OR.
    int result = task.Result;
}
catch (AggregateException aggEx)
{
    Console.WriteLine(aggEx.InnerException.Message);
}

How to close a MessageBox after several seconds?

Related to: Close a MessageBox after several seconds.
Scenario:
I need to create a message box or Popup which must be visible for specified time and close automatically after then.

Solution:
There is not inbuilt feature in .net framework to do this. You need to create your custom logic that message box close after a specific time. To do this create a custom message box class which have a timer which close the raised pop message when elapsed time equal to specified time to make it visible to the user.
Here is the custom class:

public class AutoClosingMessageBox {
    System.Threading.Timer _timeoutTimer;
    string _caption;
    AutoClosingMessageBox(string text, string caption, int timeout) {
        _caption = caption;
        _timeoutTimer = new System.Threading.Timer(OnTimerElapsed,
            null, timeout, System.Threading.Timeout.Infinite);
        MessageBox.Show(text, caption);
    }
    public static void Show(string text, string caption, int timeout) {
        new AutoClosingMessageBox(text, caption, timeout);
    }
    void OnTimerElapsed(object state) {
        IntPtr mbWnd = FindWindow(null, _caption);
        if(mbWnd != IntPtr.Zero)
            SendMessage(mbWnd, WM_CLOSE, IntPtr.Zero, IntPtr.Zero);
        _timeoutTimer.Dispose();
    }
    const int WM_CLOSE = 0x0010;
    [System.Runtime.InteropServices.DllImport("user32.dll", SetLastError = true)]
    static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
    [System.Runtime.InteropServices.DllImport("user32.dll", CharSet = System.Runtime.InteropServices.CharSet.Auto)]
    static extern IntPtr SendMessage(IntPtr hWnd, UInt32 Msg, IntPtr wParam, IntPtr lParam);
}

It can be called as below:

AutoClosingMessageBox.Show("Text", "Caption", 1000);
 

How to prevent selection change in drop down from key press when UltraComboEditor’s DropdownStyle set to DropDownList?

Scenario:
Current requirement is that user can select option from drop down from mouse selection only, but whenever use type anything in the drop down it automatically raise control’s ComboBox.SelectionChangeCommitted event with first match that satisfies auto complete condition of the control. As required we need
ignore the key press to raise the ComboBox.SelectionChangeCommitted event of the combo box and it only allow selection only on mouse.

Solution:

 

To do this, I set the control’s DropdownStyle set to DropDownList, but no work correctly. Along this I have to suppress combo box’s key press by handling KeyDown event as below.

LibraryListDropdown libDropdown = null;
public Form1()
{
    InitializeComponent();
    libDropdown = new LibraryListDropdown();
    this.libDropdown.DropDownStyle = DropDownStyle.DropDownList;
    libDropdown.Name = "cmbObjectType";
    libDropdown.SelectionChangeCommitted += new EventHandler(libDropdown_SelectionChangeCommitted);
    libDropdown.KeyDown += libDropdown_KeyDown;
    this.Controls.Add(libDropdown);
    libDropdown.PopulateLibraries();
}

void libDropdown_KeyDown(object sender, KeyEventArgs e)
{
    e.SuppressKeyPress = true;
}

private void libDropdown_SelectionChangeCommitted(object sender, EventArgs e)
{
    
}

What is the use of AsyncCallback in Client server programs and GUI improvements? why should we use it?

When the async method finishes processing, the AsyncCallback method is automatically called, where post processing statements can be executed. With this technique there is no need to poll or wait for the asyn thread to complete.

This is some explanation on Aynsc Call back usage:

Callback Model: The callback model requires that we specify a method to callback on and include any state that we need in the callback method to complete the call. The callback model can be seen in the following example:

static byte[] buffer = new byte[100];

static void TestCallbackAPM()
{
    string filename = System.IO.Path.Combine (System.Environment.CurrentDirectory, "mfc71.pdb");

    FileStream strm = new FileStream(filename,
        FileMode.Open, FileAccess.Read, FileShare.Read, 1024,
        FileOptions.Asynchronous);

    // Make the asynchronous call
    IAsyncResult result = strm.BeginRead(buffer, 0, buffer.Length,
        new AsyncCallback(CompleteRead), strm);
}

In this model, we are creating a new AsyncCallback delegate, specifying a method to call (on another thread) when the operation is complete. In addition, we are specifying some object that we might need as the state of the call. For this example, we are sending the stream object in because we will need to call EndRead and close the stream.

The method that we create to be called at the end of the call would look somewhat like this:

static void CompleteRead(IAsyncResult result)
{
    Console.WriteLine("Read Completed");

    FileStream strm = (FileStream) result.AsyncState;

    // Finished, so we can call EndRead and it will return without blocking
    int numBytes = strm.EndRead(result);

    // Don't forget to close the stream
    strm.Close();

    Console.WriteLine("Read {0} Bytes", numBytes);
    Console.WriteLine(BitConverter.ToString(buffer));
}

Other techniques are wait-until-done and pollback

Wait-Until-Done Model The wait-until-done model allows you to start the asynchronous call and perform other work. Once the other work is done, you can attempt to end the call and it will block until the asynchronous call is complete.

// Make the asynchronous call
strm.Read(buffer, 0, buffer.Length);
IAsyncResult result = strm.BeginRead(buffer, 0, buffer.Length, null, null);

// Do some work here while you wait

// Calling EndRead will block until the Async work is complete
int numBytes = strm.EndRead(result);

Or you can use wait handles.

result.AsyncWaitHandle.WaitOne();

Polling Model The polling method is similar, with the exception that the code will poll the IAsyncResult to see whether it has completed.

// Make the asynchronous call
IAsyncResult result = strm.BeginRead(buffer, 0, buffer.Length, null, null);

// Poll testing to see if complete
while (!result.IsCompleted)
{
    // Do more work here if the call isn't complete
    Thread.Sleep(100);
}

IN clause With Linq To Sql

There is no direct equivalent in LINQ. Instead you can use contains () or any other trick to implement them. Here's an example that uses Conains ().:

String [] s = new String [5];
s [0] = "34";
s [1] = "12";
s [2] = "55";
s [3] = "4";
s [4] = "61";
 
var = RESULT1 from the day context.TableName
                        where s.Contains (d.fieldname)
                        select d;

Eg:

int[] productList = new int[] { 1, 2, 3, 4 };   

var myProducts = from p in db.Products

                 where productList.Contains(p.ProductID)

                select p;

Start with the Order (pretend my order is ID=44):

AdventureWorks.DB db=new DB();

var itemQuery = from orders in db.SalesOrder

              where orders.SalesOrderID == 44

              select orders.ProductID;

Next we need to get the products, but only those that are in the cart. We do this by using our first query, inside the second:

var myProducts = from p in db.Products

                where itemQuery.Contains(p.ProductID)

                select p;