How to list all database objects in Sqlite database

Execute the following query and you will see all the table definitions and view definition.

select *
from sqlite_master


Reference:
http://www.sqlite.org/faq.html#q7 

Detecting the parent Form from Component class

Component is a class that allows you to build non-visual control. For example, Timer class. By the problem is that it does not have a intuitive way to get the parent control.

Below is the code that will get the parent control reference and save the reference during the design time. Please take note the following code will work when you are dragging a control from the Toolbox onto the Form. For those Component that has already dropped on the Form before you added codes below, it will not work.

private ContainerControl _containerControl = null;
        public ContainerControl ContainerControl
        {
            get { return _containerControl; }
            set { _containerControl = value; }
        }

        public override ISite Site
        {
            get { return base.Site; }
            set
            {
                base.Site = value;
                if (value == null)
                {
                    return;
                }
                
                IDesignerHost host = value.GetService(typeof(IDesignerHost)) as IDesignerHost;
                if (host != null)
                {
                    IComponent componentHost = host.RootComponent;
                    if (componentHost is ContainerControl)
                    {
                        ContainerControl = componentHost as ContainerControl;
                    }
                }
            }
        }

Getting the Windows system information

In case you want to find out the Windows system information such as printer configuration or network configuration, you may have to learn the WMI (Windows Management Instrumentation).

.Net comes with System.Management.ManagementObjectSearcher class which helps in digging the system information.

To find out more about WMI, visit the following URL:

http://wilsonmar.com/1wmiwbem.htm

To download the tool on generating C# code, visit the following URL:

http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=8572

To see the example on how to use the WMI tool, visit the following URL:

http://www.c-sharpcorner.com/UploadFile/scottlysle/UsingWMI04052007222505PM/UsingWMI.aspx

Registering data provider at runtime

In .Net, you may either register new data provider in the app.config file or register the data provider programmatically.

static bool _has_reg_sqlite_provider = false;

        public static void RegisterSqliteProvider()
        {
            if (_has_reg_sqlite_provider)
            {
                return;
            }

            var dataSet = System.Configuration.ConfigurationManager.GetSection("system.data") as System.Data.DataSet;

            if ((dataSet != null)
                && (dataSet.Tables.Count > 0))
            {
                DataRow[] rows = dataSet.Tables[0].Select("InvariantName='System.Data.SQLite'");

                if ((rows == null)
                    || (rows.Length == 0))
                {
                    dataSet.Tables[0].Rows.Add("SQLite Data Provider",
                        "SQLite Data Provider",
                        "System.Data.SQLite",
                        "System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.74.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"
                    );

                    _has_reg_sqlite_provider = true;
                }
            }
        }

Extension method (3)

You may use extension method to increase the code readability too. For example, we often ensure that the object instance return by some function is not null before using.

CustomerClass customer = GetCustomerObject();

if (customer == null)
{
   // do this..
}
else
{
   // do that..
}

Wtih the extension method feature in C#, you may declare a new method call IsEmpty() which looks like this:

public static bool IsEmpty(this object obj)
{
  return (obj == null);
}

Now, you may do with your new IsEmpty() method:

CustomerClass customer = GetCustomerObject();

if (customer.IsEmpty())
{
   // do this..
}
else
{
   // do that..
}

Aside from improving the code readability, why we have to declare such IsEmpty() method instead of using "x == null"? This is what you are going to gain if you are using VS2010. When you type "." after the variable and follow by "Is", VS2010 will shortlist the methods that starts with "Is". Press Tab key to auto-complete the method name and you may move on to other code. This definitely will shorten the development time.

Note: you may rename this function to IsNull() or whatsoever.

Updating database structure

Problem:

How to patch the database structure when upgrading the program?

Solution:

1. Create a class using the 'plug and play' concept (please refers to this topic). This class contains all the SQL scripts to be executed.
2. Upon the application startup, check the database version and run all the necessary SQL script to update the database structure.

Class design:

1. DBUpdate class:

• This class stores the necessary patch for the given version.
• Attributes: db_version, release_datetime & sql_script.

2. DBUpdateList class:

• This class contains the DBUpdate object instance and it is responsible for executing all the patches.
• Attributes: release_datetime & DBUpdate class type (note: it's not required to instantiate this object or otherwise will take up some memory and slowing down the application startup).

Flow:

1) Get the last patch date/time that stores in the database.
2) Get the list of the patches that is newer than the value get from step 1.
3) Execute all the necessary patches against the database.
4) Get the newest date/time among the patches that has been executed.
5) Update the last patch date/time into the database.
The purpose of storing the last patch date/time is to avoid all the patches being run each time the application starts. So, the application startup performance will not be affected much.

Extension method (2)

In my previous article, I have created an extension method call StrToInt for String class. Now, the question is what if a string object instance is null value and it's calling the extension method? Will the program crash? The answer is NO.

Try this out:

string s = null;
int i = s.StrToInt();