C#封装的Sqlite访问类实例

using system;
using system.collections;
using system.collections.specialized;
using system.data;
using system.configuration;
using system.data.sqlite;
namespace dal
{
 internal abstract class dbhelpersqlite
 {  
  public static string connectionstring = "data source=" + appdomain.currentdomain.basedirectory + @"dataleaf.db;version=3;";
  public dbhelpersqlite()
  {
  }
  #region 公用方法
  public static int getmaxid(string fieldname, string tablename)
  {
   string strsql = "select max(" + fieldname + ")+1 from " + tablename;
   object obj = getsingle(strsql);
   if (obj == null)
   {
    return 1;
   }
   else
   {
    return int.parse(obj.tostring());
   }
  }
  public static bool exists(string strsql)
  {
   object obj = getsingle(strsql);
   int cmdresult;
   if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
   {
    cmdresult = 0;
   }
   else
   {
    cmdresult = int.parse(obj.tostring());
   }
   if (cmdresult == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
  public static bool exists(string strsql, params sqliteparameter[] cmdparms)
  {
   object obj = getsingle(strsql, cmdparms);
   int cmdresult;
   if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
   {
    cmdresult = 0;
   }
   else
   {
    cmdresult = int.parse(obj.tostring());
   }
   if (cmdresult == 0)
   {
    return false;
   }
   else
   {
    return true;
   }
  }
  #endregion
  #region 执行简单sql语句
  /// <summary>
  /// 执行sql语句，返回影响的记录数
  /// </summary>
  /// <param name="sqlstring">sql语句</param>
  /// <returns>影响的记录数</returns>
  public static int executesql(string sqlstring)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    using (sqlitecommand cmd = new sqlitecommand(sqlstring, connection))
    {
     try
     {
      connection.open();
      int rows = cmd.executenonquery();
      return rows;
     }
     catch (system.data.sqlite.sqliteexception e)
     {
      connection.close();
      throw new exception(e.message);
     }
    }
   }
  }
  /// <summary>
  /// 执行多条sql语句，实现数据库事务。
  /// </summary>
  /// <param name="sqlstringlist">多条sql语句</param>  
  public static void executesqltran(arraylist sqlstringlist)
  {
   using (sqliteconnection conn = new sqliteconnection(connectionstring))
   {
    conn.open();
    sqlitecommand cmd = new sqlitecommand();
    cmd.connection = conn;
    sqlitetransaction tx = conn.begintransaction();
    cmd.transaction = tx;
    try
    {
     for (int n = 0; n < sqlstringlist.count; n++)
     {
      string strsql = sqlstringlist[n].tostring();
      if (strsql.trim().length > 1)
      {
       cmd.commandtext = strsql;
       cmd.executenonquery();
      }
     }
     tx.commit();
    }
    catch (system.data.sqlite.sqliteexception e)
    {
     tx.rollback();
     throw new exception(e.message);
    }
   }
  }
  /// <summary>
  /// 执行带一个存储过程参数的的sql语句。
  /// </summary>
  /// <param name="sqlstring">sql语句</param>
  /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
  /// <returns>影响的记录数</returns>
  public static int executesql(string sqlstring, string content)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    sqlitecommand cmd = new sqlitecommand(sqlstring, connection);
    sqliteparameter myparameter = new sqliteparameter("@content", dbtype.string);
    myparameter.value = content;
    cmd.parameters.add(myparameter);
    try
    {
     connection.open();
     int rows = cmd.executenonquery();
     return rows;
    }
    catch (system.data.sqlite.sqliteexception e)
    {
     throw new exception(e.message);
    }
    finally
    {
     cmd.dispose();
     connection.close();
    }
   }
  }
  /// <summary>
  /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
  /// </summary>
  /// <param name="strsql">sql语句</param>
  /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
  /// <returns>影响的记录数</returns>
  public static int executesqlinsertimg(string strsql, byte[] fs)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    sqlitecommand cmd = new sqlitecommand(strsql, connection);
    sqliteparameter myparameter = new sqliteparameter("@fs", dbtype.binary);
    myparameter.value = fs;
    cmd.parameters.add(myparameter);
    try
    {
     connection.open();
     int rows = cmd.executenonquery();
     return rows;
    }
    catch (system.data.sqlite.sqliteexception e)
    {
     throw new exception(e.message);
    }
    finally
    {
     cmd.dispose();
     connection.close();
    }
   }
  }
  /// <summary>
  /// 执行一条计算查询结果语句，返回查询结果（object）。
  /// </summary>
  /// <param name="sqlstring">计算查询结果语句</param>
  /// <returns>查询结果（object）</returns>
  public static object getsingle(string sqlstring)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    using (sqlitecommand cmd = new sqlitecommand(sqlstring, connection))
    {
     try
     {
      connection.open();
      object obj = cmd.executescalar();
      if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
      {
       return null;
      }
      else
      {
       return obj;
      }
     }
     catch (system.data.sqlite.sqliteexception e)
     {
      connection.close();
      throw new exception(e.message);
     }
    }
   }
  }
  /// <summary>
  /// 执行查询语句，返回sqlitedatareader
  /// </summary>
  /// <param name="strsql">查询语句</param>
  /// <returns>sqlitedatareader</returns>
  public static sqlitedatareader executereader(string strsql)
  {
   sqliteconnection connection = new sqliteconnection(connectionstring);
   sqlitecommand cmd = new sqlitecommand(strsql, connection);
   try
   {
    connection.open();
    sqlitedatareader myreader = cmd.executereader();
    return myreader;
   }
   catch (system.data.sqlite.sqliteexception e)
   {
    throw new exception(e.message);
   }
  }
  /// <summary>
  /// 执行查询语句，返回dataset
  /// </summary>
  /// <param name="sqlstring">查询语句</param>
  /// <returns>dataset</returns>
  public static dataset query(string sqlstring)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    dataset ds = new dataset();
    try
    {
     connection.open();
     sqlitedataadapter command = new sqlitedataadapter(sqlstring, connection);
     command.fill(ds, "ds");
    }
    catch (system.data.sqlite.sqliteexception ex)
    {
     throw new exception(ex.message);
    }
    return ds;
   }
  }
  #endregion
  #region 执行带参数的sql语句
  /// <summary>
  /// 执行sql语句，返回影响的记录数
  /// </summary>
  /// <param name="sqlstring">sql语句</param>
  /// <returns>影响的记录数</returns>
  public static int executesql(string sqlstring, params sqliteparameter[] cmdparms)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    using (sqlitecommand cmd = new sqlitecommand())
    {
     try
     {
      preparecommand(cmd, connection, null, sqlstring, cmdparms);
      int rows = cmd.executenonquery();
      cmd.parameters.clear();
      return rows;
     }
     catch (system.data.sqlite.sqliteexception e)
     {
      throw new exception(e.message);
     }
    }
   }
  }
  /// <summary>
  /// 执行多条sql语句，实现数据库事务。
  /// </summary>
  /// <param name="sqlstringlist">sql语句的哈希表（key为sql语句，value是该语句的sqliteparameter[]）</param>
  public static void executesqltran(hashtable sqlstringlist)
  {
   using (sqliteconnection conn = new sqliteconnection(connectionstring))
   {
    conn.open();
    using (sqlitetransaction trans = conn.begintransaction())
    {
     sqlitecommand cmd = new sqlitecommand();
     try
     {
      //循环
      foreach (dictionaryentry myde in sqlstringlist)
      {
       string cmdtext = myde.key.tostring();
       sqliteparameter[] cmdparms = (sqliteparameter[])myde.value;
       preparecommand(cmd, conn, trans, cmdtext, cmdparms);
       int val = cmd.executenonquery();
       cmd.parameters.clear();
       trans.commit();
      }
     }
     catch
     {
      trans.rollback();
      throw;
     }
    }
   }
  }
  /// <summary>
  /// 执行一条计算查询结果语句，返回查询结果（object）。
  /// </summary>
  /// <param name="sqlstring">计算查询结果语句</param>
  /// <returns>查询结果（object）</returns>
  public static object getsingle(string sqlstring, params sqliteparameter[] cmdparms)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    using (sqlitecommand cmd = new sqlitecommand())
    {
     try
     {
      preparecommand(cmd, connection, null, sqlstring, cmdparms);
      object obj = cmd.executescalar();
      cmd.parameters.clear();
      if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
      {
       return null;
      }
      else
      {
       return obj;
      }
     }
     catch (system.data.sqlite.sqliteexception e)
     {
      throw new exception(e.message);
     }
    }
   }
  }
  /// <summary>
  /// 执行查询语句，返回sqlitedatareader
  /// </summary>
  /// <param name="strsql">查询语句</param>
  /// <returns>sqlitedatareader</returns>
  public static sqlitedatareader executereader(string sqlstring, params sqliteparameter[] cmdparms)
  {
   sqliteconnection connection = new sqliteconnection(connectionstring);
   sqlitecommand cmd = new sqlitecommand();
   try
   {
    preparecommand(cmd, connection, null, sqlstring, cmdparms);
    sqlitedatareader myreader = cmd.executereader();
    cmd.parameters.clear();
    return myreader;
   }
   catch (system.data.sqlite.sqliteexception e)
   {
    throw new exception(e.message);
   }
  }
  /// <summary>
  /// 执行查询语句，返回dataset
  /// </summary>
  /// <param name="sqlstring">查询语句</param>
  /// <returns>dataset</returns>
  public static dataset query(string sqlstring, params sqliteparameter[] cmdparms)
  {
   using (sqliteconnection connection = new sqliteconnection(connectionstring))
   {
    sqlitecommand cmd = new sqlitecommand();
    preparecommand(cmd, connection, null, sqlstring, cmdparms);
    using (sqlitedataadapter da = new sqlitedataadapter(cmd))
    {
     dataset ds = new dataset();
     try
     {
      da.fill(ds, "ds");
      cmd.parameters.clear();
     }
     catch (system.data.sqlite.sqliteexception ex)
     {
      throw new exception(ex.message);
     }
     return ds;
    }
   }
  }
  private static void preparecommand(sqlitecommand cmd, sqliteconnection conn, sqlitetransaction trans, string cmdtext, sqliteparameter[] cmdparms)
  {
   if (conn.state != connectionstate.open)
    conn.open();
   cmd.connection = conn;
   cmd.commandtext = cmdtext;
   if (trans != null)
    cmd.transaction = trans;
   cmd.commandtype = commandtype.text;//cmdtype;
   if (cmdparms != null)
   {
    foreach (sqliteparameter parm in cmdparms)
     cmd.parameters.add(parm);
   }
  }
  #endregion
 }
}