winform中读写SQLite数据库例子(转)

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

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

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>

 

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