SQLite 帮助类

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194

public static class SqliteHelper {     /// <summary>        /// 获得连接对象       /// </summary>        /// <returns>SQLiteConnection</returns>           public static SQLiteConnection GetSQLiteConnection()     {         //Sqlite数据库地址<br>                    string str = AppDomain.CurrentDomain.BaseDirectory;         var con = new SQLiteConnection("Data Source=" + str + "DataBass\\InfoServiceDbB.db");         return con;     }       /// <summary>     /// 准备操作命令参数     /// </summary>     /// <param name="cmd">SQLiteCommand</param>     /// <param name="conn">SQLiteConnection</param>     /// <param name="cmdText">Sql命令文本</param>     /// <param name="data">参数数组</param>     private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary<String, String> data)     {         if (conn.State != ConnectionState.Open)             conn.Open();         cmd.Parameters.Clear();         cmd.Connection = conn;         cmd.CommandText = cmdText;         cmd.CommandType = CommandType.Text;         cmd.CommandTimeout = 30;         if (data != null && data.Count >= 1)         {             foreach (KeyValuePair<String, String> val in data)             {                 cmd.Parameters.AddWithValue(val.Key, val.Value);             }         }     }       /// <summary>     /// 查询，返回DataSet     /// </summary>     /// <param name="cmdText">Sql命令文本</param>     /// <param name="data">参数数组</param>     /// <returns>DataSet</returns>     public static DataSet ExecuteDataset(string cmdText, Dictionary<string, string> data)     {         var ds = new DataSet();         using (SQLiteConnection connection = GetSQLiteConnection())         {             var command = new SQLiteCommand();             PrepareCommand(command, connection, cmdText, data);             var da = new SQLiteDataAdapter(command);             da.Fill(ds);         }         return ds;     }       /// <summary>     /// 查询，返回DataTable     /// </summary>     /// <param name="cmdText">Sql命令文本</param>     /// <param name="data">参数数组</param>     /// <returns>DataTable</returns>     public static DataTable ExecuteDataTable(string cmdText, Dictionary<string, string> data)     {         var dt = new DataTable();         using (SQLiteConnection connection = GetSQLiteConnection())         {             var command = new SQLiteCommand();             PrepareCommand(command, connection, cmdText, data);             SQLiteDataReader reader = command.ExecuteReader();             dt.Load(reader);         }         return dt;     }       /// <summary>     /// 返回一行数据     /// </summary>     /// <param name="cmdText">Sql命令文本</param>     /// <param name="data">参数数组</param>     /// <returns>DataRow</returns>     public static DataRow ExecuteDataRow(string cmdText, Dictionary<string, string> data)     {         DataSet ds = ExecuteDataset(cmdText, data);         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">Sql命令文本</param>     /// <param name="data">传入的参数</param>     /// <returns>返回受影响的行数</returns>     public static int ExecuteNonQuery(string cmdText, Dictionary<string, string> data)     {         using (SQLiteConnection connection = GetSQLiteConnection())         {             var command = new SQLiteCommand();             PrepareCommand(command, connection, cmdText, data);             return command.ExecuteNonQuery();         }     }       /// <summary>     /// 返回SqlDataReader对象     /// </summary>     /// <param name="cmdText">Sql命令文本</param>     /// <param name="data">传入的参数</param>     /// <returns>SQLiteDataReader</returns>     public static SQLiteDataReader ExecuteReader(string cmdText, Dictionary<string, string> data)     {         var command = new SQLiteCommand();         SQLiteConnection connection = GetSQLiteConnection();         try         {             PrepareCommand(command, connection, cmdText, data);             SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);             return reader;         }         catch         {             connection.Close();             command.Dispose();             throw;         }     }       /// <summary>     /// 返回结果集中的第一行第一列，忽略其他行或列     /// </summary>     /// <param name="cmdText">Sql命令文本</param>     /// <param name="data">传入的参数</param>     /// <returns>object</returns>     public static object ExecuteScalar(string cmdText, Dictionary<string, string> data)     {         using (SQLiteConnection connection = GetSQLiteConnection())         {             var cmd = new SQLiteCommand();             PrepareCommand(cmd, connection, cmdText, data);             return cmd.ExecuteScalar();         }     }       /// <summary>     /// 分页查询     /// </summary>     /// <param name="recordCount">总记录数</param>     /// <param name="pageIndex">页牵引</param>     /// <param name="pageSize">页大小</param>     /// <param name="cmdText">Sql命令文本</param>     /// <param name="countText">查询总记录数的Sql文本</param>     /// <param name="data">命令参数</param>     /// <returns>DataSet</returns>     public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary<string, string> data)     {         if (recordCount < 0)             recordCount = int.Parse(ExecuteScalar(countText, data).ToString());         var ds = new DataSet();         using (SQLiteConnection connection = GetSQLiteConnection())         {             var command = new SQLiteCommand();             PrepareCommand(command, connection, cmdText, data);             var da = new SQLiteDataAdapter(command);             da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");         }         return ds;     }       /// <summary>     /// 清除空间     /// </summary>     public static void ResetDataBass()     {         using (SQLiteConnection conn = GetSQLiteConnection())         {             var cmd = new SQLiteCommand();               if (conn.State != ConnectionState.Open)                 conn.Open();             cmd.Parameters.Clear();             cmd.Connection = conn;             cmd.CommandText = "vacuum";             cmd.CommandType = CommandType.Text;             cmd.CommandTimeout = 30;             cmd.ExecuteNonQuery();         }     }   }