002 学习笔记--SqlLite

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

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

SqliteHelper==》

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

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;
        }
 
    }
}