C# 操作SQLite数据库

最新推荐文章于 2024-03-21 17:01:55 发布

weixin_34110749

最新推荐文章于 2024-03-21 17:01:55 发布

阅读量157

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/hjes/p/11059949.html

版权

注意需要引用System.Data.SQLite.dll文件，附上文件下载地址：https://files.cnblogs.com/files/hjes/System.Data.SQLite.rar ，此文件为.NET 2.0 86位版本,运行程序，需要将运行平台改成x86模式方可正常运行，需要其它版本可去官网下载。

话不多说，直接上码

  1 class SQLiteDB
  2     {
  3 
  4         /// <summary>
  5         /// 创建数据库文件
  6         /// </summary>
  7         /// <param name="fileName">文件名</param>
  8         public static void CreateDBFile(string fileName)
  9         {
 10             string path = Environment.CurrentDirectory + @"/Data/";
 11             if (!Directory.Exists(path))
 12             {
 13                 Directory.CreateDirectory(path);
 14             }
 15             string databaseFileName = path + fileName;
 16             if (!File.Exists(databaseFileName))
 17             {
 18                 SQLiteConnection.CreateFile(databaseFileName);
 19             }
 20         }
 21         /// <summary>
 22         /// 删除数据库
 23         /// </summary>
 24         /// <param name="fileName">文件名</param>
 25         public static void DeleteDBFile(string fileName)
 26         {
 27             string path = Environment.CurrentDirectory + @"/Data/";
 28             if (File.Exists(path))
 29             {
 30                 File.Delete(path);
 31             }
 32         }
 33 
 34         /// <summary>
 35         /// 生成连接字符串
 36         /// </summary>
 37         /// <returns></returns>
 38         private static string CreateConnectionString()
 39         {
 40             SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
 41             connectionString.DataSource = @"data/data.db";//此处文件名可以使用变量表示
 42 
 43             string conStr = connectionString.ToString();
 44             return conStr;
 45         }
 46 
 47         static SQLiteConnection m_dbConnection;
 48         /// <summary>
 49         /// 连接到数据库
 50         /// </summary>
 51         /// <returns></returns>
 52         private static SQLiteConnection dbConnection()
 53         {
 54             m_dbConnection = new SQLiteConnection(CreateConnectionString());
 55 
 56             m_dbConnection.Open();
 57 
 58             return m_dbConnection;
 59         }
 60 
 61         /// <summary>
 62         /// 在指定数据库中创建一个table
 63         /// </summary>
 64         /// <param name="sql">sql语言，如：create table highscores (name varchar(20), score int)</param>
 65         /// <returns></returns>
 66         public static bool CreateTable(string sql)
 67         {
 68             try
 69             {
 70                 SQLiteCommand command = new SQLiteCommand(sql, dbConnection());
 71                 command.ExecuteNonQuery();
 72                 return true;
 73             }
 74             catch (Exception ex)
 75             {
 76                 _ErrorLog.Insert("ExecuteNonQuery(" + sql + ")Err:" + ex);
 77                 return false;
 78             }
 79             finally
 80             {
 81                 closeConn();
 82             }
 83 
 84         }
 85 
 86         /// <summary>
 87         /// 在指定数据库中删除一个table
 88         /// </summary>
 89         /// <param name="tablename">表名称</param>
 90         /// <returns></returns>
 91         public static bool DeleteTable(string tablename)
 92         {
 93             try
 94             {
 95                 SQLiteCommand cmd = new SQLiteCommand("DROP TABLE IF EXISTS " + tablename, dbConnection());
 96                 cmd.ExecuteNonQuery();
 97                 return true;
 98             }
 99             catch (Exception ex)
100             {
101                 _ErrorLog.Insert("ExecuteNonQuery(DROP TABLE IF EXISTS " + tablename + ")Err:" + ex);
102                 return false;
103             }
104             finally
105             {
106                 closeConn();
107             }
108         }
109 
110         /// <summary>
111         /// 在指定表中添加列
112         /// </summary>
113         /// <param name="tablename">表名</param>
114         /// <param name="columnname">列名</param>
115         /// <param name="ctype">列的数值类型</param>
116         /// <returns></returns>
117         public static bool AddColumn(string tablename, string columnname,string ctype)
118         {
119             try
120             {
121                 SQLiteCommand cmd = new SQLiteCommand("ALTER TABLE " + tablename + " ADD COLUMN " + columnname + " " + ctype, dbConnection());
122                 cmd.ExecuteNonQuery();
123                 return true;
124             }
125             catch (Exception ex)
126             {
127                 _ErrorLog.Insert("ExecuteNonQuery(ALTER TABLE " + tablename + " ADD COLUMN " + columnname + " " + ctype +")Err:" + ex);
128                 return false;
129             }
130             finally
131             {
132                 closeConn();
133             }
134         }
135 
136         /// <summary>
137         /// 执行增删改查操作
138         /// </summary>
139         /// <param name="sql">查询语言</param>
140         /// <returns></returns>
141         public static int ExecuteNonQuery(string sql)
142         {
143             try
144             {
145                 SQLiteCommand cmd;
146                 cmd = new SQLiteCommand(sql, dbConnection());
147                 cmd.ExecuteNonQuery().ToString();
148                 return 1;
149             }
150             catch (Exception ex)
151             {
152                 _ErrorLog.Insert("ExecuteNonQuery(" + sql + ")Err:" + ex);
153                 return 0;
154             }
155             finally
156             {
157                 closeConn();
158             }
159         }
160 
161         /// <summary>
162         /// 返回一条记录查询
163         /// </summary>
164         /// <param name="sql">sql查询语言</param>
165         /// <returns>返回字符串数组</returns>
166         public static string[] SqlRow(string sql)
167         {
168             try
169             {
170                 SQLiteCommand sqlcmd = new SQLiteCommand(sql, dbConnection());//sql语句
171                 SQLiteDataReader reader = sqlcmd.ExecuteReader();
172                 if (!reader.Read())
173                 {
174                     return null;
175                 }
176                 string[] Row = new string[reader.FieldCount];
177                 for (int i = 0; i < reader.FieldCount; i++)
178                 {
179                     Row[i] = (reader[i].ToString());
180                 }
181                 reader.Close();
182                 return Row;
183             }
184             catch (Exception ex)
185             {
186                 _ErrorLog.Insert("SqlRow(" + sql + ")Err:" + ex);
187                 return null;
188             }
189             finally
190             {
191                 closeConn();
192             }
193         }
194 
195         /// <summary>
196         /// 唯一结果查询
197         /// </summary>
198         /// <param name="sql">sql查询语言</param>
199         /// <returns>返回一个字符串</returns>
200         public static string sqlone(string sql)
201         {
202 
203             try
204             {
205                 SQLiteCommand sqlcmd = new SQLiteCommand(sql, dbConnection());//sql语句
206                 return sqlcmd.ExecuteScalar().ToString();
207             }
208             catch
209             {
210                 return "";
211             }
212             finally
213             {
214                 closeConn();
215             }
216         }
217 
218         /// <summary>
219         /// 获取一列数据
220         /// </summary>
221         /// <param name="sql">单列查询</param>
222         /// <param name="count">返回结果数量</param>
223         /// <returns>返回一个数组</returns>
224         public static List<string> sqlcolumn(string sql)
225         {
226             try
227             {
228                 List<string> Column = new List<string>();
229                 SQLiteCommand sqlcmd = new SQLiteCommand(sql, dbConnection());//sql语句
230                 SQLiteDataReader reader = sqlcmd.ExecuteReader();
231                 while (reader.Read())
232                 {
233                     Column.Add(reader[0].ToString());
234                 }
235                 reader.Close();
236                 return Column;
237             }
238             catch (Exception ex)
239             {
240                 _ErrorLog.Insert("sqlcolumn(" + sql + ")Err:" + ex);
241                 return null;
242             }
243             finally
244             {
245                 closeConn();
246             }
247         }
248 
249         /// <summary>
250         /// 返回记录集查询
251         /// </summary>
252         /// <param name="sql">sql查询语言</param>
253         /// <returns>返回查询结果集</returns>
254         public static DataTable SqlTable(string sql)
255         {
256             try
257             {
258                 SQLiteCommand sqlcmd = new SQLiteCommand(sql, dbConnection());//sql语句
259                 sqlcmd.CommandTimeout = 120;
260                 SQLiteDataReader reader = sqlcmd.ExecuteReader();
261                 DataTable dt = new DataTable();
262                 if (reader != null)
263                 {
264                     dt.Load(reader, LoadOption.PreserveChanges, null);
265                 }
266                 return dt;
267             }
268             catch (Exception ex)
269             {
270                 _ErrorLog.Insert("SqlReader(" + sql + ")Err:" + ex);
271                 return null;
272             }
273             finally
274             {
275                 closeConn();
276             }
277         }
278         /// <summary>
279         /// 关闭数据库连接
280         /// </summary>
281         public static void closeConn()
282         {
283             try
284             {
285                 if (m_dbConnection.State == ConnectionState.Open)
286                     m_dbConnection.Close();
287                 else if (m_dbConnection.State == ConnectionState.Broken)
288                 {
289                     m_dbConnection.Close();
290                 }
291             }
292             catch (Exception ex)
293             {
294                 _ErrorLog.Insert("closeConnErr:" + ex);
295             }
296         }
297     }

常用的日志代码，喜欢的可以拿去

 1     public class writetolog
 2     {
 3         /// <summary>
 4         /// 写入到文件，文件名file_name，内容log_txt
 5         /// </summary>
 6         /// <returns></returns>
 7         public bool writetofile(string txt,string file_name)
 8         {
 9             FileInfo fi = new FileInfo(file_name);
10             if (!Directory.Exists(fi.DirectoryName))
11             {
12                 Directory.CreateDirectory(fi.DirectoryName);
13             }
14             txt = DateTime.Now.ToString("HH:mm:ss") + txt;
15                 try
16                 {
17                     using (FileStream sw = new FileStream(file_name, FileMode.Append, FileAccess.Write))
18                         if (File.Exists(file_name))
19                         {
20                             StreamWriter fs = new StreamWriter(sw);
21                             // 为文件添加一些文本内容
22                             fs.WriteLine(txt);
23                             fs.Close();
24                             return true;
25                         }
26                         else
27                         {
28                             using (StreamWriter fs = new StreamWriter(sw))
29                             {
30                                 fs.WriteLine(txt);
31                                 fs.Close();
32                                 return true;
33                             }
34                         }
35                 }
36                 catch
37                 {
38                     return false;
39                 }
40             }
41         }
42     /// <summary>
43     /// 记录错误日志
44     /// </summary>
45     public class _ErrorLog
46     {
47         public static void Insert(string x)
48         {
49             string err_name = "Syslog\\Err_log" + DateTime.Now.Date.ToString("yyyy-MM-dd") + ".txt";
50             writetolog flog = new writetolog();
51             flog.writetofile(x,err_name);
52         }
53     }
54     /// <summary>
55     /// 记录操作日志
56     /// </summary>
57     public class _ActionLog
58     {
59         public static void Insert(string x)
60         {
61             string act_name = "Syslog\\Act_log" + DateTime.Now.Date.ToString("yyyy-MM-dd") + ".txt";
62             writetolog flog = new writetolog();
63             flog.writetofile(x,act_name);
64         }
65     }