|
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>
数据库读写助手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);
}
|
|
|
