Sandip's Programming Zen

An attempt to share tech/coding experiences

A Basic SQLite DAL class in C#

leave a comment »

 

You have to first download ADO.Net provider for SQLite from here and use it’s reference in your project.

The class has enough functions for basic DAL operations but you are free to enhance it as per your need.

public static class SqliteDal
{
    public static string cnstr = "Data Source =" + "yourdb.db3";

    /// <summary>
    /// Returns datatbale for given sql query.
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable getData(string sql)
    {
        SQLiteConnection cn = new SQLiteConnection(cnstr);
        cn.Open();

        try
        {
            SQLiteCommand cm = new SQLiteCommand(sql, cn);
            SQLiteDataReader dr = cm.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);
            cn.Close();

            return dt;
        }
        catch (Exception ex)
        {
            cn.Close();
            throw ex;
        }
    }

    /// <summary>
    /// Returns count of executed insert, update, delete statement.
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int execNQ(string sql)
    {
        SQLiteConnection cn = new SQLiteConnection(cnstr);
        cn.Open();
        SQLiteCommand cm = new SQLiteCommand(sql, cn);
        int rows;
        rows = cm.ExecuteNonQuery();
        cn.Close();
        return rows;
    }

    /// <summary>
    /// Returns scalar for given sql query.
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static object execSC(string sql)
    {
        SQLiteConnection cn = new SQLiteConnection(cnstr);
        cn.Open();
        SQLiteCommand cm = new SQLiteCommand(sql, cn);
        object rows;
        rows = cm.ExecuteScalar();
        cn.Close();
        if (rows == null)
        {
            rows = 0;
        }
        return rows;
    }

}

Advertisements

Written by Sandip

April 6, 2010 at 8:51 am

Posted in .Net

Tagged with ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: