SQLite使用
首先从https://www.sqlite.org/download.html下载
下载完成后，将文件全部解压到D:\sqlite
运行sqlite3.exe查看是否正常
下载可视化工具 https://github.com/sqlitebrowser/sqlitebrowser/releases
利用可视化工具创建表结构
准备工作完成，接下来就是C#操作SQLite
在NuGet包管理工具中添加所需的dll
在config文件中配置数据库连接字符串
<connectionStrings> <add name="sqliteConn" connectionString="Data Source=..\..\dataBase\dbAreaInfo.db;Pooling=true;FailIfMissing=false" providerName="System.Data.SQLite" /> </connectionStrings>
接下来是一个简单的SQLiteHelper
1 public class SQLiteHelper 2 { 3 private static string connString = System.Configuration.ConfigurationManager.ConnectionStrings["sqliteConn"].ToString(); 4 private static SQLiteConnection conn; 5 6 static SQLiteHelper() 7 { 8 conn = new SQLiteConnection(connString); 9 } 10 11 12 /// <summary> 13 /// 查询数据,返回DataSet 14 /// </summary> 15 /// <param name="sql"></param> 16 /// <returns></returns> 17 public static DataSet QuerySql(string sql) 18 { 19 DataSet ds = new DataSet(); 20 try 21 { 22 using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn)) 23 { 24 conn.Open(); 25 adapter.Fill(ds); 26 conn.Close(); 27 } 28 } 29 catch { } 30 return ds; 31 } 32 33 /// <summary> 34 /// 执行单条SQL语句 35 /// </summary> 36 /// <param name="sql"></param> 37 /// <returns></returns> 38 public static bool ExecuteSql(string sql) 39 { 40 bool result = false; 41 try 42 { 43 using (SQLiteCommand cmd = new SQLiteCommand(conn)) 44 { 45 conn.Open(); 46 cmd.CommandText = sql; 47 result = cmd.ExecuteNonQuery() > 0; 48 conn.Close(); 49 } 50 } 51 catch { } 52 return result; 53 } 54 55 /// <summary> 56 /// 事务执行多条SQL语句 57 /// </summary> 58 /// <param name="lstSql"></param> 59 /// <returns></returns> 60 public static bool ExecuteSql(List<string> lstSql) 61 { 62 bool result = false; 63 try 64 { 65 using (SQLiteCommand cmd = new SQLiteCommand(conn)) 66 { 67 conn.Open(); 68 DbTransaction trans = conn.BeginTransaction(); 69 foreach (var sql in lstSql) 70 { 71 cmd.CommandText = sql; 72 cmd.ExecuteNonQuery(); 73 } 74 trans.Commit(); 75 result = true; 76 conn.Close(); 77 } 78 } 79 catch { } 80 return result; 81 } 82 }
两个简单的调用例子
查询
1 /// <summary> 2 /// 获取区域名称 3 /// </summary> 4 /// <param name="areaCode">区域编号</param> 5 /// <returns></returns> 6 public string GetAreaName(string areaCode) 7 { 8 string result = string.Empty; ; 9 string sql = "select AreaName from AreaInfo where AreaCode='" + areaCode + "'"; 10 DataSet ds = SQLiteHelper.QuerySql(sql); 11 if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 1) 12 { 13 result = ds.Tables[0].Rows[0]["AreaName"].ToString(); 14 } 15 return result; 16 }
删除和新增
1 public void InsertAreaSource() 2 { 3 string sql = "delete from AreaInfo"; 4 SQLiteHelper.ExecuteSql(sql); 5 6 AreaInfo areaInfo = new AreaInfo(); 7 List<Area> lstArea = areaInfo.GetAreaInfoFromWeb(); 8 List<string> lstSql = new List<string>(); 9 foreach (Area item in lstArea) 10 { 11 sql = "INSERT INTO AreaInfo(AreaCode,AreaName) VALUES('" + item.AreaCode + "','" + item.AreaName + "')"; 12 lstSql.Add(sql); 13 } 14 SQLiteHelper.ExecuteSql(lstSql); 15 }
