SQLite 操作类

最新推荐文章于 2022-04-22 10:52:20 发布
AnnieLin1990
最新推荐文章于 2022-04-22 10:52:20 发布
阅读量137
点赞数
文章标签：数据库
原文链接：http://www.cnblogs.com/CodingMouse/p/3330303.html
版权
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