C# 中使用 SQLite 数据库

SQLite 是一个轻量级别数据库，是遵守 ACID 的关系型数据库管理系统，它包含在一个相对小的 C 库中。它是 D.RichardHipp 建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百 K 的内存就够了。它能够支持 Windows/Linux/Unix 等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java 等，还有 ODBC 接口，同样比起 Mysql、PostgreSQL 这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。

SQLite 官网：http://www.sqlite.org/

项目中使用了类库，下载地址：http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

下载好后，在项目右键添加引用即可。

代码：

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

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

using System;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
 
namespace Blog.Data
{
 
    /// <summary>
    /// SQLiteHelper
    /// </summary>
    public class SQLiteHelper : IDisposable
    {
        #region 私有字段..
 
        private SQLiteConnection _connection = null;
        private SQLiteTransaction _transaction = null;
        private bool _transacted = false;
        private string _connectionString = String.Empty;
        private bool _disposed = false;
        private bool _autoCommit = false;
 
        #endregion
 
        #region 构造函数...
 
        /// <summary>
        /// 使用默认的连接字符串初始化 <see cref="SQLiteHelper"/> 类的新实例。
        /// </summary>
        public SQLiteHelper()
            : this(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)
        {
        }
 
        /// <summary>
        /// 初始化 SQLiteHelper
        /// </summary>
        /// <param name="connectionString">数据库连接字符串。</param>
        public SQLiteHelper(string connectionString)
        {
            _connectionString = connectionString;
            _connection = new SQLiteConnection(_connectionString);
            _connection.Commit += new SQLiteCommitHandler(Transaction_Commit);
            _connection.RollBack += new EventHandler(Transaction_RollBack);
        }
 
        /// <summary>
        /// SQLiteHelper 析构函数
        /// </summary>
        ~SQLiteHelper()
        {
            Dispose(false);
        }
 
        #endregion
 
        #region 方法...
 
        /// <summary>
        /// 打开数据库连接。
        /// </summary>
        private void Open()
        {
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }
        }
 
        /// <summary>
        /// 关闭数据库连接。
        /// </summary>
        private void Close()
        {
            if (_connection.State != ConnectionState.Closed)
            {
                if (_transacted && _autoCommit)
                {
                    Commit();
                }
                _connection.Close();
            }
        }
 
        /// <summary>
        /// 开始数据库事务。
        /// </summary>
        public void BeginTransaction()
        {
            _connection.BeginTransaction();
            _transacted = true;
        }
 
        /// <summary>
        /// 开始数据库事务。
        /// </summary>
        /// <param name="isolationLevel">事务锁级别。</param>
        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            _connection.BeginTransaction(isolationLevel);
            _transacted = true;
        }
 
        /// <summary>
        /// 提交当前挂起的事务。
        /// </summary>
        public void Commit()
        {
            if (_transacted)
            {
                _transaction.Commit();
                _transacted = false;
            }
        }
 
        /// <summary>
        /// 回滚当前挂起的事务。
        /// </summary>
        public void Rollback()
        {
            if (_transacted)
            {
                _transaction.Rollback();
                _transacted = false;
            }
        }
 
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL 语句</param>
        /// <returns>返回受影响的行数。</returns>
        public int ExecuteNonQuery(string commandText)
        {
            int result = 0;
            Open();
            using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText))
            {
                result = sqliteCommand.ExecuteNonQuery();
            }
            Close();
            return result;
        }
 
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="commandText">SQL 语句</param>
        /// <param name="parmeters">要添加的 <see cref="SQLiteParameter"/> 值。</param>
        /// <returns>返回受影响的行数。</returns>
        public int ExecuteNonQuery(string commandText, SQLiteParameter[] parmeters)
        {
            int result = 0;
            Open();
            using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText))
            {
                sqliteCommand.Parameters.AddRange(parmeters);
                result = sqliteCommand.ExecuteNonQuery();
            }
            Close();
            return result;
        }
 
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="commandText">用于查询的 Sql 语句。</param>
        /// <returns>结果集中第一行的第一列；如果结果集为空，则为空引用。返回的最大字符数为 2033 个字符。</returns>
        public object ExecuteScalar(string commandText)
        {
            object result = null;
            Open();
            using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText))
            {
                result = sqliteCommand.ExecuteScalar();
            }
            Close();
            return result;
        }
 
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="commandText">用于查询的 Sql 语句。</param>
        /// <param name="parmeters">要添加的 <see cref="SQLiteParameter"/> 值。</param>
        /// <returns>结果集中第一行的第一列；如果结果集为空，则为空引用。返回的最大字符数为 2033 个字符。</returns>
        public object ExecuteScalar(string commandText, SQLiteParameter[] parmeters)
        {
            object result = null;
            Open();
            using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText))
            {
                sqliteCommand.Parameters.AddRange(parmeters);
                result = sqliteCommand.ExecuteScalar();
            }
            Close();
            return result;
        }
 
        /// <summary>
        /// 查询数据并返回一个 <see cref="DataSet"/>。
        /// </summary>
        /// <param name="commandText">用于查询的 Sql 语句。</param>
        /// <returns>返回一个 <see cref="DataSet"/> 对象。</returns>
        public DataSet GetDataSet(string commandText)
        {
            return GetDataSet(commandText, string.Empty);
        }
 
        /// <summary>
        /// 查询数据并返回一个 <see cref="DataSet"/>。
        /// </summary>
        /// <param name="commandText">用于查询的 Sql 语句。</param>
        /// <param name="tableName">用于表映射的源表的名称。</param>
        /// <returns>返回一个 <see cref="DataSet"/> 对象。</returns>
        public DataSet GetDataSet(string commandText, string tableName)
        {
            DataSet dataSet = new DataSet();
            Open();
            using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText, _connection))
            {
                using (SQLiteDataAdapter sqlAdapter = new SQLiteDataAdapter(sqliteCommand))
                {
                    if (string.Empty.Equals(tableName))
                    {
                        sqlAdapter.Fill(dataSet);
                    }
                    else
                    {
                        sqlAdapter.Fill(dataSet, tableName);
                    }
                }
            }
            Close();
            return dataSet;
        }
 
        /// <summary>
        /// 查询数据并返回一个 <see cref="DataSet"/>。
        /// </summary>
        /// <param name="commandText">用于查询的 Sql 语句。</param>
        /// <param name="sqlCommand"><see cref="SQLiteCommand"/> 对象。</param>
        /// <returns>返回一个 <see cref="DataSet"/> 对象。</returns>
        public DataSet GetDataSet(string commandText, out SQLiteCommand sqlCommand)
        {
            return GetDataSet(commandText, string.Empty, out sqlCommand);
        }
 
        /// <summary>
        /// 查询数据并返回一个 <see cref="DataSet"/>。
        /// </summary>
        /// <param name="commandText">用于查询的 Sql 语句。</param>
        /// <param name="tableName">用于表映射的源表的名称。</param>
        /// <param name="sqlCommand"><see cref="SQLiteCommand"/> 对象。</param>
        /// <returns>返回一个 <see cref="DataSet"/> 对象。</returns>
        public DataSet GetDataSet(string commandText, string tableName, out SQLiteCommand sqlCommand)
        {
            DataSet dataSet = new DataSet();
            Open();
            SQLiteCommand sqliteCommand = new SQLiteCommand(commandText, _connection);
            using (SQLiteDataAdapter sqlAdapter = new SQLiteDataAdapter(sqliteCommand))
            {
                sqlAdapter.Fill(dataSet);
            }
            sqlCommand = sqliteCommand;
            Close();
            return dataSet;
        }
 
        /// <summary>
        /// 为具有指定 <see cref="DataTable"/> 名称的 <see cref="DataSet"/> 更新数据。
        /// </summary>
        /// <param name="dataSet">用于更新数据源的 <see cref="DataSet"/>。</param>
        /// <param name="sqlCommand">指定的一个 <see cref="SQLiteCommand"/> 对象。</param>
        /// <returns><see cref="DataSet"/> 中成功更新的行数。</returns>
        public int Update(DataSet dataSet, ref SQLiteCommand sqlCommand)
        {
            return Update(dataSet, string.Empty, ref sqlCommand);
        }
 
        /// <summary>
        /// 为具有指定 <see cref="DataTable"/> 名称的 <see cref="DataSet"/> 更新数据。
        /// </summary>
        /// <param name="dataSet">用于更新数据源的 <see cref="DataSet"/>。</param>
        /// <param name="tableName">用于表映射的源表的名称。</param>
        /// <param name="sqlCommand">指定的一个 <see cref="SQLiteCommand"/> 对象。</param>
        /// <returns><see cref="DataSet"/> 中成功更新的行数。</returns>
        public int Update(DataSet dataSet, string tableName, ref SQLiteCommand sqlCommand)
        {
            int result = 0;
            Open();
            using (SQLiteDataAdapter sqlAdapter = new SQLiteDataAdapter(sqlCommand))
            {
                using (SQLiteCommandBuilder sqlCommandBuilder = new SQLiteCommandBuilder(sqlAdapter))
                {
                    if (string.Empty.Equals(tableName))
                    {
                        result = sqlAdapter.Update(dataSet);
                    }
                    else
                    {
                        result = sqlAdapter.Update(dataSet, tableName);
                    }
                }
            }
            Close();
            return result;
        }
 
        /// <summary>
        /// 释放该实例的托管资源。
        /// </summary>
        public virtual void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
 
        /// <summary>
        /// 释放非托管资源。
        /// </summary>
        /// <param name="disposing"></param>
        protected void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    // 定义释放非托管资源
                }
                _disposed = true;
            }
        }
 
        #endregion
 
        #region 属性...
 
        /// <summary>
        /// 获取数据库连接字符串。
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return _connectionString;
            }
        }
        /// <summary>
        /// 设置是否自动提交事务。
        /// </summary>
        public bool AutoCommit
        {
            get
            {
                return _autoCommit;
            }
            set
            {
                _autoCommit = value;
            }
        }
 
        #endregion
 
        #region 事件...
 
        /// <summary>
        /// 事务回滚事件。
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void Transaction_RollBack(object sender, EventArgs e)
        {
            _transacted = false;
        }
 
        /// <summary>
        /// 事务提交事件。
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void Transaction_Commit(object sender, CommitEventArgs e)
        {
            _transacted = false;
        }
 
        #endregion
    }
}