Sandip's Programming Zen

An attempt to share tech/coding experiences

Posts Tagged ‘SQLite

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 ,

SQLite Administrator: Great SQLite Windows client

leave a comment »

I have tried out few commercial and freeware SQLite client apps but none is better than SQLite Administrator, It’s simple and easy to use and all the core feature works flawlessly. I have used it couple of times before and this time I had simply forgotten the name and looked all over my PC to find out this program but since this is not an installable software (directly launched from exe) it’s difficult to locate because when you need it you simple download it and launch it from there and forget it thereafter. But this time I decided to write this post to make it searchable for me and of course others 😉

Written by Sandip

April 6, 2010 at 3:44 am

Posted in productivity, Softwares

Tagged with