C#使用sqlite

1、准备工作

  1. 下载数据库(http://www.sqlite.org/download.html)文件很小用C#开发不要用这个先了解下,
  2. 下载C# 对应的netframework 版本的DLL System.Data.SQLite(下载地址是http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki 自己选择版本)安装后提取出DLL
  3. 下载数据库开发可视工具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;
                }
            }
        }
    }
}