C# 内嵌数据库 SQLite

 最近，看到一个软件，软件是使用的内嵌数据库。我对这个东西没有实践过，今天突然想亲手做一做！。关于SQLIte的资料我就不多说了，网上都有。我自己也整理了一部分，基本上可以对SQLite有个全面的了解了。我这里就不废话了，直接上我自己的代码。

1：首先要先下载一个SQLite的C#开发的支持的东西。下载地址：sqllite下载地址：tp://sourceforge.net/projects/sqlite-dotnet2/files/SQLite%20for%20ADO.NET%202.0/1.0.66.0/SQLite-1.0.66.0-setup.exe/download

点击下载后，安装就行。(注意：安装的时候最好不要开着vs2008）

2：打开vs2008，新建一个winform应用程序

3： 打开“服务器资源管理器” 在这里右击“数据连接”---->添加连接   ------》更改  -----》更改数据源为：“SQLLite Database FIle“----->确定 ---->Browse选择你的数据库，（注意选择的文件一定是以后缀“.db”结束的，如果你还没建库，那么你就随便建一个以后缀为"db"结束的文件，放到你项目的bin目录的debug中（很重要，程序发布的路径））

 

我从网上当的一个SQLite helper 根本就用不成，经过我的修补终于可以用了。

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
namespace DAL
{
    public class SQLiteHelper
    {
        /// <summary>
        /// 获得连接对象
        /// </summary>
        /// <returns></returns>
        public static SQLiteConnection GetSQLiteConnection()
        {
            SQLiteConnection conn=new SQLiteConnection(@"data source=E:\SQliteTest\SQliteTest\bin\Debug\JxyDb.db");
            conn.SetPassword("123456");//数据库密码
            return conn;
        }
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
            if (p != null)
            {
                foreach (SQLiteParameter parm in p)
                {
                    cmd.Parameters.AddWithValue(parm.ParameterName, parm.Value);
                }
            }
        }
        public static DataSet ExecuteDataset(string cmdText, params object[] p)
        {
            DataSet ds = new DataSet();
            SQLiteCommand command = new SQLiteCommand();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                PrepareCommand(command, connection, cmdText, p);
                SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                da.Fill(ds);
            }
            return ds;
        }
        public static DataRow ExecuteDataRow(string cmdText, params object[] p)
        {
            DataSet ds = ExecuteDataset(cmdText, p);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0];
            return null;
        }
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="cmdText">a</param>
        /// <param name="commandParameters">传入的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string cmdText, params object[] p)
        {
            SQLiteCommand command = new SQLiteCommand();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                PrepareCommand(command, connection, cmdText, p);
                return command.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 返回SqlDataReader对象
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters">传入的参数</param>
        /// <returns></returns>
        public static SQLiteDataReader ExecuteReader(string cmdText, params object[] p)
        {
            SQLiteCommand command = new SQLiteCommand();
            SQLiteConnection connection = GetSQLiteConnection();
            try
            {
                PrepareCommand(command, connection, cmdText, p);
                SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch
            {
                connection.Close();
                throw;
            }
        }
        /// <summary>
        /// 返回结果集中的第一行第一列，忽略其他行或列
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters">传入的参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, params object[] p)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                PrepareCommand(cmd, connection, cmdText, p);
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="recordCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="cmdText"></param>
        /// <param name="countText"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p)
        {
            if (recordCount < 0)
                recordCount = int.Parse(ExecuteScalar(countText, p).ToString());
            DataSet ds = new DataSet();
            SQLiteCommand command = new SQLiteCommand();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                PrepareCommand(command, connection, cmdText, p);
                SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
            }
            return ds;
        }

        /**/
        /// <summary>
        /// 放回一个SQLiteParameter
        /// </summary>
        /// <param name="name">参数名字</param>
        /// <param name="type">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns>SQLiteParameter的值</returns>
        public static  SQLiteParameter CreateSqliteParameter(string name, DbType type, int size, object value)
        {
            SQLiteParameter parm = new SQLiteParameter(name, type, size);
            parm.Value = value;
            return parm;
        }

    }
} 

Winform的测试界面和代码如下。

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace SQliteTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string Sqlstr = "INSERT INTO [Users] VALUES(@username,@mobile)";
            SQLiteParameter[] canshu = new SQLiteParameter[]{
                        DAL.SQLiteHelper.CreateSqliteParameter("@username",DbType.String,50,this.textBox1.Text),
                        DAL.SQLiteHelper.CreateSqliteParameter("@mobile",DbType.String,50,this.textBox2.Text)
                      };
            DAL.SQLiteHelper.ExecuteNonQuery(Sqlstr, canshu);
            Bangding();

        }

        public void Bangding()
        {
            SQLiteParameter[] canshu1 = new SQLiteParameter[] { DAL.SQLiteHelper.CreateSqliteParameter("@id", DbType.Int32, 4, 1) };
            string Sqlstr = "select * from users";
            this.dataGridView1.DataSource = DAL.SQLiteHelper.ExecuteDataset(Sqlstr, canshu1).Tables[0];
            
        }

    }
}

 项目文件

 

 

 

 

没有做性能测试，应该问题不大。 紧紧做入门，保存下来以便自己日后会用到。