002 学习笔记--SqlLite

第一步：使用Nuget安装System.Data.SQLite

第二步：使用SqlLite可视化工具创建数据库、表--略

SqliteHelper==》

using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace DBHelper
{
    public class SqliteHelper
    {
        //private string ConStr = "Data Source=C:\\ZDPACS_DJ\\DB\\SinglePacs.sqlite";
        //string conStr = "Data Source=D:\\SinglePacs.sqlite";
        public static string ConStr { get; set; }
        //定义一个用于保存静态变量的实例
        private static SqliteHelper instance = null;
        //定义一个保证线程同步的标识
        private static readonly object locker = new object();
        //构造函数为私有，使外界不能创建该类的实例
        private SqliteHelper() { }
        public static SqliteHelper Instance(string conStr)
        {
            if (instance == null)
            {
                lock (locker)
                {
                    if (instance == null)
                    {
                        instance = new SqliteHelper();
                        ConStr = conStr;
                    }
                }
            }
            return instance;
        }

        /// <summary>
        /// 执行非查询的sql语句，返回受影响的行数
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="paramters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, params SQLiteParameter[] paramters)
        {
            using (SQLiteConnection con = new SQLiteConnection(ConStr))
            {
                try
                {
                    con.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(con))
                    {
                        cmd.CommandText = cmdText;
                        if (paramters != null)
                        {
                            //SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
                            //myParameter.Value = paramters;
                            //cmd.Parameters.Add(paramters);
                            //多个参数
                            foreach (SQLiteParameter sp in paramters)
                            {
                                cmd.Parameters.Add(sp);
                            }
                        }

                        return cmd.ExecuteNonQuery();
                    }
                }
                catch (SQLiteException ex)
                {
                    //_log.E(ex);
                }
                return -1;
            }
        }

        /// <summary>
        /// 执行非查询的sql语句，返回第一行第一列的值
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConStr))
            {
                try
                {
                    conn.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        cmd.CommandText = cmdText;
                        cmd.Parameters.AddRange(parameters);
                        return cmd.ExecuteScalar();
                    }
                }
                catch (SQLiteException ex)
                {
                    //_log.E(ex);
                }
                return null;
            }
        }

        /// <summary>
        /// 执行查询语句，返回查询到的结果
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConStr))
            {
                try
                {
                    conn.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandText = cmdText;
                        cmd.Parameters.AddRange(parameters);
                        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                        da.Fill(dt);
                        return dt;
                    }
                }
                catch (SQLiteException ex)
                {
                    //_log.E(ex);
                }
                return null;
            }
        }

        /// <summary>
        /// 执行查询语句，返回查询到的结果
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public DataTable GetDataTableNoParam(string cmdText)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConStr))
            {
                try
                {
                    conn.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandText = cmdText;
                        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                        da.Fill(dt);
                        return dt;
                    }
                }
                catch (SQLiteException ex)
                {
                    //_log.E(ex);

                }
                return null;
            }
        }

        /// <summary>
        /// 执行事务，如果出现异常则回滚
        /// </summary>
        /// <param name="models"></param>
        /// <returns></returns>
        public bool ExecTransaction(List<TransModel> models)
        {
            using (SQLiteConnection con = new SQLiteConnection(ConStr))
            {
                try
                {
                    con.Open();
                    using (SQLiteTransaction trans = con.BeginTransaction())
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand(con))
                        {
                            cmd.Transaction = trans;
                            try
                            {
                                foreach (var model in models)
                                {
                                    cmd.CommandText = model.CmdText;
                                    if (model.Paras != null)
                                        cmd.Parameters.AddRange(model.Paras);
                                    cmd.ExecuteNonQuery();
                                }
                                trans.Commit();
                                return true;
                            }
                            catch (SQLiteException ex)
                            {
                                trans.Rollback();
                                //_log.E(ex);
                            }
                        }
                    }
                }
                catch (SQLiteException ex)
                {
                    // _log.E(ex);
                }
            }
            return false;
        }

    }

    public class TransModel
    {
        public string CmdText { get; set; }
        public SQLiteParameter[] Paras { get; set; }
    }
}

应用示例：

using DBHelper;
using System;
using System.Windows.Forms;

namespace SqlLiteDemo
{
    public partial class SqlLiteForm : Form
    {
        private string ConnString = "Data Source = " + Application.StartupPath + "\\SqlLiteDemo.db3";
        //string conStr = "Data Source=D:\\SinglePacs.sqlite";

        public SqlLiteForm()
        {
            InitializeComponent();
        }

        private void SqlLiteForm_Load(object sender, EventArgs e)
        {
            this.dgvList.AutoGenerateColumns = false;
            string sql = "select * from UserInfo";

            //var list = SqliteHelper.Instance(ConnString).GetDataTableNoParam(sql);
            var list = SqliteHelper.Instance(ConnString).GetDataTable(sql);
            this.dgvList.DataSource = list;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                string sql = @"insert into [UserInfo] ([UserId],[UserName],[PassWord],[NickName]) values ("
                    + "'" + this.txtUserId.Text + "',"
                    + "'" + this.txtUserName.Text + "',"
                    + "'" + this.txtPwd.Text + "',"
                    + "'" + this.txtNickName.Text + "')";

                int count = SqliteHelper.Instance(ConnString).ExecuteNonQuery(sql);
                MessageBox.Show("新增成功：" + count);
                //UserId,UserName,PassWord,NickName
            }
            catch (Exception ex)
            {
            }
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            string sql = "delete from UserInfo where UserId=='1'";
            int count = SqliteHelper.Instance(ConnString).ExecuteNonQuery(sql);

            MessageBox.Show("删除成功:" + count);
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string sql = string.Format(@"update [UserInfo] set [PassWord] = '001' where [PassWord] ='{0}'", 1);

            int count = SqliteHelper.Instance(ConnString).ExecuteNonQuery(sql);
            MessageBox.Show("修改成功：" + count);
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            string sql = "select * from UserInfo";

            var list = SqliteHelper.Instance(ConnString).GetDataTableNoParam(sql);
            this.dgvList.DataSource = list;
        }

    }
}