读取SQLite数据库,就是读取一个路径\192.168.100.**positiondb.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.**positiondb.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文件修改:
- <?xml version="1.0"?>
- <configuration>
- <appSettings>
- <!--FailIfMissing=false表示数据库不存在时就会自动创建-->
- <add key="DbSQLite" value="data source=|DataDirectory|DB.db3;Pooling=true;FailIfMissing=false"/>
- </appSettings>
- </configuration>
备注:如果开发环境是4.0,而System.Data.Sqlite是比较低的版本,则可能会弹出错误信息“混合模式程序集是针对“v2.0.50727”版的运行时生成的,在没有配置其他信息的情况下,无法在 4.0 运行时中加载该程序集”,解决方法是在上面加上:
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>
这个放在</configuration>里面去
数据库读写助手SqliteHelper.cs
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data;
- using System.Data.Common;
- using System.Data.SQLite;
- public class SqliteHelper
- {
- public SqliteHelper()
- {
- //
- //TODO: 在此处添加构造函数逻辑
- //
- }
- private static SQLiteConnection GetConnection()
- {
- string connStr = System.Configuration.ConfigurationManager.AppSettings["DbSQLite"].ToString();
- SQLiteConnection conn = new SQLiteConnection(connStr);
- conn.Open();
- return conn;
- }
- public static int ExecuteSql(string sql)
- {
- using (SQLiteConnection conn = GetConnection())
- {
- var cmd = new SQLiteCommand(sql, conn);
- return cmd.ExecuteNonQuery();
- }
- }
- public static int ExecuteScalar(string sql)
- {
- using (SQLiteConnection conn = GetConnection())
- {
- var cmd = new SQLiteCommand(sql, conn);
- object o = cmd.ExecuteScalar();
- return int.Parse(o.ToString());
- }
- }
- public static SQLiteDataReader ExecuteReader(string sql)
- {
- SQLiteConnection conn = GetConnection();
- var cmd = new SQLiteCommand(sql, conn);
- SQLiteDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- return myReader;
- }
- public static DataSet ExecDataSet(string sql)
- {
- using (SQLiteConnection conn = GetConnection())
- {
- var cmd = new SQLiteCommand(sql, conn);
- SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
- DataSet ds = new DataSet();
- da.Fill(ds);
- return ds;
- }
- }
- }
窗体中应用Form1.cs
-
- //判断表是否存在,不存在则生成
- int result = SqliteHelper.ExecuteScalar("SELECT COUNT(*) FROM sqlite_master where type='table' and name='tb'");
- if (result == 0)
- {
- //创建表
- SqliteHelper.ExecuteSql("create table [tb] (id integer PRIMARY KEY autoincrement, [name] varchar(20), [createDate] datetime default (datetime('now', 'localtime')))");
- }
- //插入一行数据
- result = SqliteHelper.ExecuteSql("insert into [tb](name) values ('Luck')");
- if(result > 0)
- {
- string msg = "";
- //读取数据
- SQLiteDataReader dr = SqliteHelper.ExecuteReader("select * from [tb]");
- if (dr.Read())
- {
- msg += dr[0] + "," + dr[1] + "," + dr[2];
- }
- MessageBox.Show(msg);
- }