SQLite 操作类
SQLite 操作类
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using System.Data.SQLite; 7 using System.IO; 8 using System.Data.Common; 9 10 namespace myAlarmSystem.myClass 11 { 12 /// <summary> 13 /// Sqlite数据库操作类 14 /// </summary> 15 public class mySqliteAPI 16 { 17 18 #region 构造函数 19 20 private string strConn = ""; 21 22 /// <summary> 23 /// 构造函数 24 /// </summary> 25 /// <param name="dbPath">SQLITE数据库文件路径</param> 26 public mySqliteAPI(string dbPath) 27 { 28 this.strConn = "Data Source=" + dbPath; 29 } 30 31 /// <summary> 32 /// 构造函数 33 /// </summary> 34 /// <param name="dbPath">数据库路径</param> 35 /// <param name="dbPwd">数据库密码</param> 36 public mySqliteAPI(string dbPath, string dbPwd) 37 { 38 this.strConn = "Data Source=" + dbPath + ";Password=" + dbPwd; 39 } 40 41 #endregion 42 43 #region 创建数据库及初始化数据库表 44 45 /// <summary> 46 /// 创建SQLITE数据库 47 /// </summary> 48 /// <param name="dbPath">要创建的SQLITE数据库文件路径</param> 49 public void CreateDB(string dbPath) 50 { 51 using (SQLiteConnection Conn = new SQLiteConnection("Data Source=" + dbPath)) 52 { 53 Conn.Open(); 54 using (SQLiteCommand Comm = new SQLiteCommand(Conn)) 55 { 56 Comm.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)"; 57 Comm.ExecuteNonQuery(); 58 Comm.CommandText = "DROP TABLE Demo"; 59 Comm.ExecuteNonQuery(); 60 } 61 Conn.ChangePassword("19880210");//创建加密的数据库 62 } 63 } 64 65 /// <summary> 66 /// 压缩清理SQLITE数据库 67 /// </summary> 68 public void PressDB() 69 { 70 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 71 { 72 Conn.Open(); 73 using (SQLiteCommand Comm = Conn.CreateCommand()) 74 { 75 Comm.CommandText = "VACUUM"; 76 Comm.ExecuteNonQuery(); 77 } 78 } 79 } 80 81 /// <summary> 82 /// 执行SQL语句,来自TXT文本 83 /// </summary> 84 /// <param name="txtPath"></param> 85 public void ExecuteTxtSQL(string dbPath, string sqlPath) 86 { 87 string sql = this.ReadSqlFromTxt(sqlPath); 88 if (sql.Length > 0) 89 { 90 using (SQLiteConnection Conn = new SQLiteConnection("Data Source=" + dbPath)) 91 { 92 Conn.Open(); 93 using (SQLiteCommand Comm = new SQLiteCommand(Conn)) 94 { 95 Comm.CommandText = sql; 96 Comm.ExecuteNonQuery(); 97 } 98 } 99 } 100 } 101 102 /// <summary> 103 /// 读取文本 104 /// </summary> 105 /// <param name="file">文件</param> 106 /// <returns></returns> 107 private string ReadSqlFromTxt(string file) 108 { 109 string data = ""; 110 try 111 { 112 using (StreamReader sr = new StreamReader(file)) 113 { 114 string line; 115 while ((line = sr.ReadLine()) != null) 116 { 117 data += line; 118 } 119 } 120 } 121 catch { return ""; } 122 return data; 123 } 124 125 #endregion 126 127 #region 增删改操作 128 129 /// <summary> 130 /// 增删改操作,返回受影响的行数 131 /// </summary> 132 /// <param name="sql">要执行增删改的SQL语句</param> 133 /// <param name="parameters">执行增删改语句所需要的参数,参数必须以他们在SQL语句中的顺序为准</param> 134 /// <returns></returns> 135 public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters) 136 { 137 int affectedRows = 0; 138 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 139 { 140 Conn.Open(); 141 using (DbTransaction transaction = Conn.BeginTransaction()) 142 { 143 using (SQLiteCommand Comm = new SQLiteCommand(Conn)) 144 { 145 Comm.CommandText = sql; 146 if (parameters != null) 147 { 148 Comm.Parameters.AddRange(parameters); 149 } 150 affectedRows = Comm.ExecuteNonQuery(); 151 } 152 transaction.Commit(); 153 } 154 } 155 return affectedRows; 156 } 157 158 /// <summary> 159 /// 增删改操作,返回执行成功与否 160 /// </summary> 161 /// <param name="sql">要执行增删改的SQL语句</param> 162 /// <returns></returns> 163 public bool ExecuteNonQuery(string sql) 164 { 165 try 166 { 167 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 168 { 169 Conn.Open(); 170 using (DbTransaction transaction = Conn.BeginTransaction()) 171 { 172 using (SQLiteCommand Comm = new SQLiteCommand(Conn)) 173 { 174 Comm.CommandText = sql; 175 Comm.ExecuteNonQuery(); 176 } 177 transaction.Commit(); 178 } 179 } 180 } 181 catch { return false; } 182 return true; 183 } 184 185 #endregion 186 187 #region 查询操作 188 189 /// <summary> 190 /// 执行一个查询语句,返回一个包含查询结果的datatable 191 /// </summary> 192 /// <param name="sql">要执行的查询语句</param> 193 /// <param name="parameters">要执行的SQL语句所需要的参数,参数必须是以他们在SQL语句中的顺序为准</param> 194 /// <returns></returns> 195 public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters) 196 { 197 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 198 { 199 using (SQLiteCommand Comm = new SQLiteCommand(sql, Conn)) 200 { 201 if (parameters != null) 202 { 203 Comm.Parameters.AddRange(parameters); 204 } 205 SQLiteDataAdapter adapter = new SQLiteDataAdapter(Comm); 206 DataTable data = new DataTable(); 207 adapter.Fill(data); 208 return data; 209 } 210 } 211 } 212 213 /// <summary> 214 /// 执行一个查询语句,返回一个包含查询结果的datatable 215 /// </summary> 216 /// <param name="sql">要执行的查询语句</param> 217 /// <param name="tableName">表名</param> 218 /// <returns></returns> 219 public DataTable ExecuteDataTable(string sql, string tableName) 220 { 221 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 222 { 223 using (SQLiteCommand Comm = new SQLiteCommand(sql, Conn)) 224 { 225 SQLiteDataAdapter adapter = new SQLiteDataAdapter(Comm); 226 DataSet data = new DataSet(); 227 adapter.Fill(data, tableName); 228 return data.Tables[tableName]; 229 } 230 } 231 } 232 233 public DataSet ExecuteDataSet(string sql) 234 { 235 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 236 { 237 using (SQLiteCommand Comm = new SQLiteCommand(sql, Conn)) 238 { 239 SQLiteDataAdapter adapter = new SQLiteDataAdapter(Comm); 240 DataSet data = new DataSet(); 241 adapter.Fill(data); 242 return data; 243 } 244 } 245 } 246 247 /// <summary> 248 /// 执行一个查询语句,返回一个包含查询结果的datatable,分页显示 249 /// </summary> 250 /// <param name="sql">要执行的查询语句</param> 251 /// <param name="startIndex">开始索引</param> 252 /// <param name="count">行数</param> 253 /// <param name="tableName">表名</param> 254 /// <returns></returns> 255 public DataTable ExecuteDataTable(string sql, int startIndex, int count, string tableName) 256 { 257 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 258 { 259 using (SQLiteCommand Comm = new SQLiteCommand(sql, Conn)) 260 { 261 SQLiteDataAdapter adapter = new SQLiteDataAdapter(Comm); 262 DataSet data = new DataSet(); 263 adapter.Fill(data, startIndex, count, tableName); 264 return data.Tables[tableName]; 265 } 266 } 267 } 268 269 /// <summary> 270 /// 执行查询语句 271 /// </summary> 272 /// <param name="sql">SQL语句</param> 273 /// <returns></returns> 274 public object ExecuteScalar(string sql) 275 { 276 using (SQLiteConnection Conn = new SQLiteConnection(strConn)) 277 { 278 Conn.Open(); 279 using (SQLiteCommand Comm = new SQLiteCommand(sql, Conn)) 280 { 281 return Comm.ExecuteScalar(); 282 } 283 } 284 } 285 286 #endregion 287 288 } 289 } 290