Generic Code to use any SQLite DB with C#

How use SQLite Database with C# and System.Data.SQLite using SharpDevelop


Original by Zhanshan Dong

/*
 * Original by Zhanshan Dong
 * A little modified by HACKPRO TM.
 * Created: 22/05/2010
 * Hour: 10:20 p.m.
 * 
 */

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SQLite;
using System.IO;

public class ConnSQLite {
 
 private SQLiteConnection Conn;
 private SQLiteCommand Cmd;
 private bool isConnect = false;

 public ConnSQLite(string dbName, string connection) {
  
  bool hasDBFile = File.Exists(dbName);
  
  if (hasDBFile == true) {
   ConnectDB(connection);
   Cmd = CreateCMD();
  } else {
   isConnect = false;
  }
  
 }

 ~ConnSQLite() {
  
  if (isConnect == true) {
   Cmd.Dispose();
   Conn.Close();
  }
  
 }

 private void ConnectDB(string connection) {
  
  Conn = new SQLiteConnection(connection);
  Conn.Open();
  
  if (Conn.State.ToString() == "Open") {
   isConnect = true;
  } else {
   isConnect = false;
  }
  
 }

 private SQLiteCommand CreateCMD() {
  
  SQLiteCommand cmd = new SQLiteCommand();
  return Conn.CreateCommand();
  
 }

 private void disposeCMD(SQLiteCommand cmd) {
  
  cmd.Dispose();
  
 }
 
 public bool IsConnect() {
  
  return isConnect;
  
 }

 public void ExecuteNonQuerySQL(string SQL) {
  
  Cmd.CommandText = SQL;
  Cmd.ExecuteNonQuery();
  
 }

 public SQLiteDataReader ExecuteQuerySQL(string SQL, SQLiteCommand cmd) {
  
  cmd.CommandText = SQL;
  return cmd.ExecuteReader();
  
 }
 
 public DataTable RecordSet(string SQL) {
  
  SQLiteDataAdapter da = new SQLiteDataAdapter(SQL, Conn);
  DataSet ds = new DataSet();
  DataTable dt = new DataTable();
  ds.Reset();
  da.Fill(ds);
  dt = ds.Tables[0];
  return dt;
  
 }
 
 public double MAXId(string SQL) {
  
  SQLiteCommand cmd = CreateCMD();
  SQLiteDataReader dbReader = ExecuteQuerySQL(SQL, cmd);
  double rowVal = 0;
  
  if (dbReader.HasRows == true) {
   while (dbReader.Read()) {
    rowVal = dbReader.GetDouble(0);
   }
   
  } else {
   rowVal = 0;
  }
  
  dbReader.Close();
  cmd.Dispose();
  return rowVal;
  
 }

 public int getRowCount(string SQL) {
  
  SQLiteCommand cmd = CreateCMD();
  SQLiteDataReader dbReader = ExecuteQuerySQL(SQL, cmd);
  int rowCount = 0;
  
  if (dbReader.HasRows) {
   dbReader.Read();
   rowCount = dbReader.GetInt32(0);
  }
  
  dbReader.Close();
  cmd.Dispose();
  return rowCount;
  
 }

}