sqlhelper 数据库帮助操作类

数据库帮助类

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using System.Data.SQLite;
  7 using System.Data;
  8 
  9 namespace SqliteDemo
 10 {
 11     /// <summary>
 12     /// SQLite 操作类
 13     /// </summary>
 14     class SqLiteHelper
 15     {
 16 
 17         /// <summary>
 18         /// 数据库连接定义
 19         /// </summary>
 20         private SQLiteConnection dbConnection;
 21 
 22         /// <summary>
 23         /// SQL命令定义
 24         /// </summary>
 25         private SQLiteCommand dbCommand;
 26 
 27         /// <summary>
 28         /// 数据读取定义
 29         /// </summary>
 30         private SQLiteDataReader dataReader;
 31 
 32         /// <summary>
 33         /// 数据读取
 34         /// </summary>
 35         private SQLiteDataAdapter dataAdapter;
 36 
 37         /// <summary>
 38         /// 构造函数
 39         /// </summary>
 40         /// <param name="connectionString">连接SQLite库字符串</param>
 41         public SqLiteHelper(string connectionString)
 42         {
 43             try
 44             {
 45                 dbConnection = new SQLiteConnection(connectionString);
 46                 dbConnection.Open();
 47             }
 48             catch (Exception e)
 49             {
 50                 Log(e.ToString());
 51             }
 52         }
 53         /// <summary>
 54         /// 执行SQL命令
 55         /// </summary>
 56         /// <returns>The query.</returns>
 57         /// <param name="queryString">SQL命令字符串</param>
 58         public SQLiteDataReader ExecuteQuery(string queryString)
 59         {
 60             try
 61             {
 62                 dbCommand = dbConnection.CreateCommand();
 63                 dbCommand.CommandText = queryString;
 64                 dataReader = dbCommand.ExecuteReader();
 65             }
 66             catch (Exception e)
 67             {
 68                 Log(e.Message);
 69             }
 70 
 71             return dataReader;
 72         }
 73 
 74     public DataTable GetDataTable(string tableName)
 75         {
 76             string queryString = "SELECT * FROM " + tableName;
 77             DataTable dt = new DataTable();
 78             try
 79             {
 80                 dbCommand = dbConnection.CreateCommand();
 81                 dbCommand.CommandText = queryString;
 82                 SQLiteDataAdapter ad = new SQLiteDataAdapter(dbCommand);
 83                 ad.Fill(dt);
 84             }
 85             catch (Exception e)
 86             {
 87                 Log(e.Message);
 88             }
 89             return dt;
 90         }
 91 
 92 
 93         /// <summary>
 94         /// 关闭数据库连接
 95         /// </summary>
 96         public void CloseConnection()
 97         {
 98             //销毁Commend
 99             if (dbCommand != null)
100             {
101                 dbCommand.Cancel();
102             }
103             dbCommand = null;
104             //销毁Reader
105             if (dataReader != null)
106             {
107                 dataReader.Close();
108             }
109             dataReader = null;
110             //销毁Connection
111             if (dbConnection != null)
112             {
113                 dbConnection.Close();
114             }
115             dbConnection = null;
116 
117         }
118 
119         /// <summary>
120         /// 读取整张数据表
121         /// </summary>
122         /// <returns>The full table.</returns>
123         /// <param name="tableName">数据表名称</param>
124         public SQLiteDataReader ReadFullTable(string tableName)
125         {
126             string queryString = "SELECT * FROM " + tableName;
127             return ExecuteQuery(queryString);
128         }
129 
130 
131         /// <summary>
132         /// 向指定数据表中插入数据
133         /// </summary>
134         /// <returns>The values.</returns>
135         /// <param name="tableName">数据表名称</param>
136         /// <param name="values">插入的数值</param>
137         public SQLiteDataReader InsertValues(string tableName, string[] values)
138         {
139             //获取数据表中字段数目
140             int fieldCount = ReadFullTable(tableName).FieldCount;
141             //当插入的数据长度不等于字段数目时引发异常
142             if (values.Length != fieldCount)
143             {
144                 throw new SQLiteException("values.Length!=fieldCount");
145             }
146 
147             string queryString = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
148             for (int i = 1; i < values.Length; i++)
149             {
150                 queryString += ", " + "'" + values[i] + "'";
151             }
152             queryString += " )";
153             return ExecuteQuery(queryString);
154         }
155 
156         /// <summary>
157         /// 更新指定数据表内的数据
158         /// </summary>
159         /// <returns>The values.</returns>
160         /// <param name="tableName">数据表名称</param>
161         /// <param name="colNames">字段名</param>
162         /// <param name="colValues">字段名对应的数据</param>
163         /// <param name="key">关键字</param>
164         /// <param name="value">关键字对应的值</param>
165         /// <param name="operation">运算符：=,<,>,...，默认“=”</param>
166         public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation = "=")
167         {
168             //当字段名称和字段数值不对应时引发异常
169             if (colNames.Length != colValues.Length)
170             {
171                 throw new SQLiteException("colNames.Length!=colValues.Length");
172             }
173 
174             string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";
175             for (int i = 1; i < colValues.Length; i++)
176             {
177                 queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";
178             }
179             queryString += " WHERE " + key + operation + "'" + value + "'";
180             return ExecuteQuery(queryString);
181         }
182 
183         /// <summary>
184         /// 删除指定数据表内的数据
185         /// </summary>
186         /// <returns>The values.</returns>
187         /// <param name="tableName">数据表名称</param>
188         /// <param name="colNames">字段名</param>
189         /// <param name="colValues">字段名对应的数据</param>
190         public SQLiteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations)
191         {
192             //当字段名称和字段数值不对应时引发异常
193             if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
194             {
195                 throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
196             }
197 
198             string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
199             for (int i = 1; i < colValues.Length; i++)
200             {
201                 queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'";
202             }
203             return ExecuteQuery(queryString);
204         }
205 
206         /// <summary>
207         /// 删除指定数据表内的数据
208         /// </summary>
209         /// <returns>The values.</returns>
210         /// <param name="tableName">数据表名称</param>
211         /// <param name="colNames">字段名</param>
212         /// <param name="colValues">字段名对应的数据</param>
213         public SQLiteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations)
214         {
215             //当字段名称和字段数值不对应时引发异常
216             if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
217             {
218                 throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
219             }
220 
221             string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
222             for (int i = 1; i < colValues.Length; i++)
223             {
224                 queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
225             }
226             return ExecuteQuery(queryString);
227         }
228 
229 
230         /// <summary>
231         /// 创建数据表
232         /// </summary> +
233         /// <returns>The table.</returns>
234         /// <param name="tableName">数据表名</param>
235         /// <param name="colNames">字段名</param>
236         /// <param name="colTypes">字段名类型</param>
237         public SQLiteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)
238         {
239             string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];
240             for (int i = 1; i < colNames.Length; i++)
241             {
242                 queryString += ", " + colNames[i] + " " + colTypes[i];
243             }
244             queryString += "  ) ";
245             return ExecuteQuery(queryString);
246         }
247 
248         /// <summary>
249         /// Reads the table.
250         /// </summary>
251         /// <returns>The table.</returns>
252         /// <param name="tableName">Table name.</param>
253         /// <param name="items">Items.</param>
254         /// <param name="colNames">Col names.</param>
255         /// <param name="operations">Operations.</param>
256         /// <param name="colValues">Col values.</param>
257         public SQLiteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
258         {
259             string queryString = "SELECT " + items[0];
260             for (int i = 1; i < items.Length; i++)
261             {
262                 queryString += ", " + items[i];
263             }
264             queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];
265             for (int i = 0; i < colNames.Length; i++)
266             {
267                 queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";
268             }
269             return ExecuteQuery(queryString);
270         }
271 
272         /// <summary>
273         /// 本类log
274         /// </summary>
275         /// <param name="s"></param>
276         static void Log(string s)
277         {
278             Console.WriteLine("class SqLiteHelper:::" + s);
279         }
280     }
281 }

View Code

控制台调用

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Threading.Tasks;
 6 
 7 namespace ConsoleApplication3
 8 {
 9     class Program
10     {
11         private static SqLiteHelper sql;
12         static void Main(string[] args)
13         {
14             sql = new SqLiteHelper(@"data source=E:620000S00000_20160707Data.db");
15 
16             //读取整张表
17             SQLiteDataReader reader = sql.ReadFullTable("SYS_Statistics");
18 
19             while (reader.Read())
20             {
21                 //读取表名
22                 string tableName = reader.GetString(reader.GetOrdinal("TableName"));
23                 //读取表的数据
24                 int Rowcount = reader.GetInt32(reader.GetOrdinal("DataStatistics"));
25                 Console.WriteLine(tableName+Rowcount);
26             }
27 
28             Console.Read();
29         }
30     }
31 }

View Code

相关阅读:
MySQL 中随机抽样：order by rand limit 的替代方案
 mysql下distinct和group by区别对比
 MVC中实现多按钮提交（转）
js的逻辑 OR 运算符- ||
js 实现键盘记录兼容FireFox和IE
jquery扩展
 sp_executesql的用法
 MVC中，视图的Layout使用
 MVC4的过滤器
 MVC中的Repository模式
原文地址：https://www.cnblogs.com/555556J/p/11081266.html