SQLite in ASP.NET Web Forms

What is SQLite?

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite is a must have tool for manipulating database for small to mid size projects, there by cutting the cost of maintaining an extra database server. Its very fast and has less percentage of crashing also.
Next, download an SQLite Manager from which we can create and manipulate a sample database and its objects. For that I used the SQLite Manager add on for Firefox. Feel free to use any other tools.
Now we are all set to use the SQLite database in our ASP.NET application.

Steps to follow

1. Create a Sample Database called Northwind.sqlite using the firefix addon.
2. Add a table named Customers to it with some fields and some dummy data to it.
3. Create an empty website in .NET 3.5 using Visual Studio
4. Create an App_Data folder in it and place the Northwind.sqlite to it
5. Add to the empty website as reference, the dll

using System.Data.SQLite

6. Create a default page in your application and place a gridview in that

<asp:GridView ID="CustomersGrid" runat="server">
</asp:GridView>

7. At code behind, add reference to System.Data.SQLite;
8. Write the code to get data from Customers Table here.

private void BindCustomerGrid()
{
    string commandText = "SELECT * FROM Customers";
    string path_to_db = System.IO.Path.Combine(Server.MapPath("."), "App_Data", "Northwind.sqlite3");
    string connectionString = String.Format("Data Source={0};Version=3;", path_to_db);
    DataTable dataTable = new DataTable();
    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        using (SQLiteCommand cmd = new SQLiteCommand(commandText, connection))
        {
            cmd.CommandType = CommandType.Text;
            using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd))
            {
                adapter.Fill(dataTable);
            }
        }
    }
    CustomersGrid.DataSource = dataTable;
    CustomersGrid.DataBind();
}

Thats all to it. Happy Coding!

Related Posts :