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 }}