C#实现SQLite数据库文件的读写操作示例

读取SQLite数据库，就是读取一个路径\192.168.100.**\position\db.sqlite下的文件

<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>

</startup>

这个放在</configuration>里面去

我在winform 里的代码

public void jiazaiweizhi()

{

string sql = "SELECT * FROM messages";

string connStr = @"Data Source=" + @"\192.168.100.**\position\db.sqlite;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";

using (SQLiteConnection conn = new SQLiteConnection(connStr))

{

using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn))

{

DataSet ds = new DataSet();

adapter.Fill(ds);

DataTable dt = ds.Tables[0];

// MessageBox.Show(dt.Columns[1].ColumnName, "数据库信息");

textBox1.Text = "当前位置" + Environment.NewLine + dt.Rows[0][1].ToString();

}

}

}

.Net SQLite数据库驱动以及System.Data.SQLite.dll下载最新地址：

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

（电脑上可以搜索System.Data.SQLite.dll这个dll，加载进入项目之后 可以看它的版本）

SQLite管理工具下载地址：

http://www.cr173.com/soft/94247.html

App.config文件修改：

1. <?xml version="1.0"?>
2. <configuration>
3. <appSettings>
4. <!--FailIfMissing=false表示数据库不存在时就会自动创建-->
5. <add key="DbSQLite" value="data source=|DataDirectory|DB.db3;Pooling=true;FailIfMissing=false"/>
6. </appSettings>
7. </configuration>

备注：如果开发环境是4.0，而System.Data.Sqlite是比较低的版本，则可能会弹出错误信息“混合模式程序集是针对“v2.0.50727”版的运行时生成的，在没有配置其他信息的情况下，无法在 4.0 运行时中加载该程序集”，解决方法是在上面加上：

<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>

</startup>

这个放在</configuration>里面去

数据库读写助手SqliteHelper.cs

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Data;
6. using System.Data.Common;
7. using System.Data.SQLite;
8. 
   
9. 
   
10. public class SqliteHelper
11. {
12. public SqliteHelper()
13. {
14. //
15. //TODO: 在此处添加构造函数逻辑
16. //
17. }
18. 
    
19. private static SQLiteConnection GetConnection()
20. {
21. string connStr = System.Configuration.ConfigurationManager.AppSettings["DbSQLite"].ToString();
22. SQLiteConnection conn = new SQLiteConnection(connStr);
23. conn.Open();
24. return conn;
25. }
26. 
    
27. public static int ExecuteSql(string sql)
28. {
29. using (SQLiteConnection conn = GetConnection())
30. {
31. var cmd = new SQLiteCommand(sql, conn);
32. return cmd.ExecuteNonQuery();
33. }
34. }
35. 
    
36. public static int ExecuteScalar(string sql)
37. {
38. using (SQLiteConnection conn = GetConnection())
39. {
40. var cmd = new SQLiteCommand(sql, conn);
41. object o = cmd.ExecuteScalar();
42. return int.Parse(o.ToString());
43. }
44. }
45. public static SQLiteDataReader ExecuteReader(string sql)
46. {
47. SQLiteConnection conn = GetConnection();
48. var cmd = new SQLiteCommand(sql, conn);
49. SQLiteDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
50. return myReader;
51. }
52. public static DataSet ExecDataSet(string sql)
53. {
54. using (SQLiteConnection conn = GetConnection())
55. {
56. var cmd = new SQLiteCommand(sql, conn);
57. SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
58. DataSet ds = new DataSet();
59. da.Fill(ds);
60. 
    
61. return ds;
62. }
63. }
64. }

窗体中应用Form1.cs

1. //判断表是否存在，不存在则生成
2. int result = SqliteHelper.ExecuteScalar("SELECT COUNT(*) FROM sqlite_master where type='table' and name='tb'");
3. if (result == 0)
4. {
5. //创建表
6. SqliteHelper.ExecuteSql("create table [tb] (id integer PRIMARY KEY autoincrement, [name] varchar(20), [createDate] datetime default (datetime('now', 'localtime')))");
7. }
8. //插入一行数据
9. result = SqliteHelper.ExecuteSql("insert into [tb](name) values ('Luck')");
10. if(result > 0)
11. {
12. string msg = "";
13. //读取数据
14. SQLiteDataReader dr = SqliteHelper.ExecuteReader("select * from [tb]");
15. if (dr.Read())
16. {
17. msg += dr[0] + "," + dr[1] + "," + dr[2];
18. }
19. MessageBox.Show(msg);
20. }