C#使用sqlite

来源：互联网发布：网络最火的歌2017 编辑：程序博客网时间：2024/06/05 03:35

1、准备工作

下载数据库（http://www.sqlite.org/download.html）文件很小用C#开发不要用这个先了解下，
下载C# 对应的netframework 版本的DLL System.Data.SQLite（下载地址是http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki 自己选择版本）安装后提取出DLL
下载数据库开发可视工具SqliteDev387（破解版）GOOGLE搜。

2 .新建项目

1.创建DB数据格式文件。

2.编写DAL数据库访问程序

代码如下

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SQLite;using System.Data;using System.Data.Common;using System.Windows.Forms;namespace Utils{    public class SqliteHelper    {        private string connectionString = string.Empty;        private string pwd = null;        /// <summary>        /// 构造函数        /// </summary>        /// <param name="dbPath">SQLite数据库文件路径</param>        public SqliteHelper(string dbPath)        {            this.connectionString = "Data Source=" + dbPath;        }        public SqliteHelper(string dbPath, string pwd)            : this(dbPath)        {            this.pwd = pwd;            EncryptDB();        }        /// <summary>        /// 加密数据库        /// </summary>        public void EncryptDB()        {            using (SQLiteConnection con = new SQLiteConnection(connectionString))            {                con.SetPassword("pwd");//如果已经设置过密码想修改密码则Open前必须要原始密码                con.Open();                con.ChangePassword(pwd);            }        }        /// <summary>        ///         /// </summary>        /// <param name="TableName"></param>        /// <param name="Sql"></param>        /// <returns></returns>        public DataTable getTable(string TableName, string QueryTable_DDL,string CreateTable_DDL)        {            using (SQLiteConnection con = new SQLiteConnection(connectionString))            {                try                {                    con.SetPassword(pwd);                    con.Open();                     using (SQLiteCommand cmd = new SQLiteCommand(con))                    {                        cmd.CommandText = "SELECT count(1) FROM sqlite_master  WHERE type='table'   and name='" + TableName + "'";                        object rs = cmd.ExecuteScalar();                        if (rs != null)                        {                            if (Convert.ToInt32(rs) > 0)                            {                                goto Table;                                //SQLiteDataAdapter dap = new SQLiteDataAdapter(QueryTable_DDL, con);                                //DataTable dt = new DataTable();                                //dap.Fill(dt);                                //return dt;                            }                            ///创建表                            else                            {                                cmd.CommandText = CreateTable_DDL;                                cmd.ExecuteNonQuery();                                goto Table;                                //SQLiteDataAdapter dap = new SQLiteDataAdapter(QueryTable_DDL, con);                                //DataTable dt = new DataTable();                                //dap.Fill(dt);                                //return dt;                            }                        }                        else                            return null;                    Table:                        SQLiteDataAdapter dap = new SQLiteDataAdapter(QueryTable_DDL, con);                        DataTable dt = new DataTable();                        dap.Fill(dt);                        return dt;                    }                }                catch(Exception ex)                {                    MessageBox.Show(ex.Message);                    return null;                }            }                                }        public bool  ExecuteNonQuery(string Sql)        {            using (SQLiteConnection con = new SQLiteConnection(this.connectionString))            {                con.SetPassword(pwd);                con.Open();                 SQLiteTransaction trans = con.BeginTransaction();                using (SQLiteCommand cmd = new SQLiteCommand(con))                {                    cmd.Transaction = trans;                    cmd.CommandText = Sql;                    if (cmd.ExecuteNonQuery() > 0)                    {                        trans.Commit();                        return true;                    }                    else                    {                        trans.Rollback();                        return false;                    }                }            }         }        public object getScalar(string TableName)        {            using (SQLiteConnection con = new SQLiteConnection(this.connectionString))            {                con.SetPassword(pwd);                con.Open();                 using(SQLiteCommand cmd=new SQLiteCommand(con))                {                    cmd.CommandText = "SELECT count(1) FROM sqlite_master  WHERE type='table'   and name='"+TableName+"'";                   object rs=  cmd.ExecuteScalar();                   return rs;                }            }        }    }              }

下载地址：http://dl.dbank.com/c0ideqjlku