C# 操作SQLite数据库
注意需要引用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 }
