C#下Sqlite数据库连接类

首先要引用

System.Data.SQLite.dll  分x86和x64版本

 

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SQLite;namespace UrlSubmitCore{ public class DBServiceLite { //internal static void CheckDataBase() //{ // string strDataBase = CMM.GetProductApplicationDataPath + "//datainfo.db"; // string strDataBaseBack = CMM.GetAppPath + "//datainfo.db"; // if (!System.IO.File.Exists(strDataBase) && System.IO.File.Exists(strDataBaseBack)) // { // System.IO.File.Copy(strDataBaseBack, strDataBase); // } //} /// <summary> /// 获得连接对象 /// </summary> /// <returns></returns> public static SQLiteConnection GetSQLiteConnection() { return new SQLiteConnection("Data Source=" + CMM.GetAppPath + "//datainfo.db"); } static SQLiteConnection m_Connection = null; private static SQLiteConnection GetConnection { get { if (m_Connection == null) m_Connection = new SQLiteConnection("Data Source=" + CMM.GetAppPath + "//datainfo.db"); if (m_Connection != null && m_Connection.State == ConnectionState.Closed) { m_Connection.Open(); } return m_Connection; } } private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Parameters.Clear(); cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 30; if (p != null) { foreach (object parm in p) cmd.Parameters.AddWithValue(string.Empty, parm); //for (int i = 0; i < p.Length; i++) // cmd.Parameters[i].Value = p[i]; } } public static DataSet ExecuteDataset(string cmdText, params object[] p) { DataSet ds = new DataSet(); SQLiteCommand command = new SQLiteCommand(); using (SQLiteConnection connection = GetSQLiteConnection()) { PrepareCommand(command, connection, cmdText, p); SQLiteDataAdapter da = new SQLiteDataAdapter(command); da.Fill(ds); } return ds; } public static bool FillTableInDataSet(DataSet mySet, string strSql, string strTabName) { bool result = false; SQLiteDataAdapter adapter = null; try { if (mySet.Tables.Contains(strTabName)) mySet.Tables.Remove(strTabName); SQLiteConnection conn = GetConnection; adapter = new SQLiteDataAdapter(strSql, conn); adapter.Fill(mySet, strTabName); result = true; } catch (Exception ex) { } finally { if (adapter != null) adapter.Dispose(); } return result; } public static DataRow ExecuteDataRow(string cmdText, params object[] p) { DataSet ds = ExecuteDataset(cmdText, p); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) return ds.Tables[0].Rows[0]; return null; } /// <summary> /// 返回受影响的行数 /// </summary> /// <param name="cmdText">a</param> /// <param name="commandParameters">传入的参数</param> /// <returns></returns> public static int ExecuteNonQuery(string cmdText, params object[] p) { SQLiteCommand command = new SQLiteCommand(); using (SQLiteConnection connection = GetSQLiteConnection()) { PrepareCommand(command, connection, cmdText, p); return command.ExecuteNonQuery(); } } /// <summary> /// 返回SqlDataReader对象 /// </summary> /// <param name="cmdText"></param> /// <param name="commandParameters">传入的参数</param> /// <returns></returns> public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p) { SQLiteCommand command = new SQLiteCommand(); SQLiteConnection connection = GetSQLiteConnection(); try { PrepareCommand(command, connection, cmdText, p); SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); return reader; } catch { connection.Close(); throw; } } /// <summary> /// 返回结果集中的第一行第一列，忽略其他行或列 /// </summary> /// <param name="cmdText"></param> /// <param name="commandParameters">传入的参数</param> /// <returns></returns> public static object ExecuteScalar(string cmdText, params object[] p) { SQLiteCommand cmd = new SQLiteCommand(); using (SQLiteConnection connection = GetSQLiteConnection()) { PrepareCommand(cmd, connection, cmdText, p); return cmd.ExecuteScalar(); } } /// <summary> /// 分页 /// </summary> /// <param name="recordCount"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="cmdText"></param> /// <param name="countText"></param> /// <param name="p"></param> /// <returns></returns> public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p) { if (recordCount < 0) recordCount = int.Parse(ExecuteScalar(countText, p).ToString()); DataSet ds = new DataSet(); SQLiteCommand command = new SQLiteCommand(); using (SQLiteConnection connection = GetSQLiteConnection()) { PrepareCommand(command, connection, cmdText, p); SQLiteDataAdapter da = new SQLiteDataAdapter(command); da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result"); } return ds; } public static bool ExecuteSql(string _sql) { bool result; SQLiteConnection Conn = null; SQLiteCommand cmd = null; try { Conn = GetConnection; cmd = new SQLiteCommand(_sql, Conn); cmd.ExecuteNonQuery(); result = true; } catch { result = false; } finally { cmd.Dispose(); } return result; } public static bool ExecuteSqlWithParameters(string _sql, string[] arrName, string[] arrValue) { bool result; SQLiteConnection Conn = null; SQLiteCommand cmd = null; try { Conn = GetConnection; cmd = new SQLiteCommand(_sql, Conn); for (int i = 0; i < arrName.Length; i++) { SQLiteParameter parameter = new SQLiteParameter(arrName[i]); parameter.Value = arrValue[i]; cmd.Parameters.Add(parameter); } cmd.ExecuteNonQuery(); result = true; } catch { result = false; } finally { cmd.Dispose(); } return result; } #region GetOneValue public static bool GetOneValue(string sSql, out string sValue) { bool result; sValue = null; SQLiteConnection Conn = null; SQLiteCommand cmd = null; try { Conn = GetConnection; cmd = new SQLiteCommand(sSql, Conn); sValue = Convert.ToString(cmd.ExecuteScalar()); result = true; } catch { result = false; } finally { cmd.Dispose(); } return result; } #endregion #region GetRecordCount public static bool GetRecordCount(string sSql, out int iValue) { bool result; SQLiteConnection Conn = null; SQLiteCommand cmd = null; iValue = 0; try { Conn = GetConnection; cmd = new SQLiteCommand(sSql, Conn); iValue = Convert.ToInt32(cmd.ExecuteScalar()); result = true; } catch { result = false; } finally { cmd.Dispose(); } return result; } #endregion }}