在IIS上发布好了WCF之后，我一直在努力寻找除了XML外最简单的数据库。虽然对SQLite早有耳闻，今天听说android和ios里用的都是sqlite，今天来尝尝鲜
在上有各种平台的版本，找到你需要的平台。如下
然后在平台下的各种版本中选一个，我们选32位.NET4.0的bundle版本：这里大家可以看到一个是bundle的，另一个是不带bundle的；bundle的表示System.Data.SQLite.dll里混合了SQLite.Interop.dll。
我们这里下载bundle混合版本的（如果下的是另一个版本，在项目中添加SQLite.Interop.dll会出错，如果不添加SQLite.Interop.dll也会保存）。
还有一项准备工作，下载一个工具：sqlitespy，用来操作sqlite数据库的。
工具都准备好了，就可以开始了
1. 用spy来创建个数据库
DROP TABLE [BOOK];CREATE TABLE [Book]([ID] INTEGER NOT NULL PRIMARY KEY autoincrement,[BookName] VARCHAR(50) NOT NULL,[Price] REAL NOT NULL);
2.在vs里写好数据库操作类(写的比较简陋, 大家自行完善)
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SQLite;namespace SQLiteDemo{ public class SQLiteDatabase { String dbConnection; SQLiteConnection cnn; #region ctor /// /// Default Constructor for SQLiteDatabase Class. /// public SQLiteDatabase() { dbConnection = "Data Source=recipes.s3db"; cnn = new SQLiteConnection(dbConnection); } /// /// Single Param Constructor for specifying the DB file. /// /// The File containing the DB public SQLiteDatabase(String inputFile) { dbConnection = String.Format("Data Source={0}", inputFile); cnn = new SQLiteConnection(dbConnection); } /// /// Single Param Constructor for specifying advanced connection options. /// /// A dictionary containing all desired options and their values public SQLiteDatabase(Dictionary connectionOpts) { String str = ""; foreach (KeyValuePair row in connectionOpts) { str += String.Format("{0}={1}; ", row.Key, row.Value); } str = str.Trim().Substring(0, str.Length - 1); dbConnection = str; cnn = new SQLiteConnection(dbConnection); } #endregion /// /// Allows the programmer to run a query against the Database. /// /// The SQL to run /// A DataTable containing the result set. public DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); try { SQLiteConnection cnn = new SQLiteConnection(dbConnection); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; SQLiteDataReader reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close(); } catch (Exception e) { throw new Exception(e.Message); } return dt; } public DataTable GetDataTable(string sql, IList cmdparams) { DataTable dt = new DataTable(); try { SQLiteConnection cnn = new SQLiteConnection(dbConnection); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; mycommand.Parameters.AddRange(cmdparams.ToArray()); mycommand.CommandTimeout = 180; SQLiteDataReader reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close(); } catch (Exception e) { throw new Exception(e.Message); } return dt; } /// /// Allows the programmer to interact with the database for purposes other than a query. /// /// The SQL to be run. /// An Integer containing the number of rows updated. public bool ExecuteNonQuery(string sql) { bool successState = false; cnn.Open(); using (SQLiteTransaction mytrans = cnn.BeginTransaction()) { SQLiteCommand mycommand = new SQLiteCommand(sql, cnn); try { mycommand.CommandTimeout = 180; mycommand.ExecuteNonQuery(); mytrans.Commit(); successState = true; cnn.Close(); } catch (Exception e) { mytrans.Rollback(); } finally { mycommand.Dispose(); cnn.Close(); } } return successState; } public bool ExecuteNonQuery(string sql, IList cmdparams) { bool successState = false; cnn.Open(); using (SQLiteTransaction mytrans = cnn.BeginTransaction()) { SQLiteCommand mycommand = new SQLiteCommand(sql, cnn, mytrans); try { mycommand.Parameters.AddRange(cmdparams.ToArray()); mycommand.CommandTimeout = 180; mycommand.ExecuteNonQuery(); mytrans.Commit(); successState = true; cnn.Close(); } catch (Exception e) { mytrans.Rollback(); throw e; } finally { mycommand.Dispose(); cnn.Close(); } } return successState; } /// /// 暂时用不到 /// Allows the programmer to retrieve single items from the DB. /// /// The query to run. /// A string. public string ExecuteScalar(string sql) { cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; object value = mycommand.ExecuteScalar(); cnn.Close(); if (value != null) { return value.ToString(); } return ""; } /// /// Allows the programmer to easily update rows in the DB. /// /// The table to update. /// A dictionary containing Column names and their new values. /// The where clause for the update statement. /// A boolean true or false to signify success or failure. public bool Update(String tableName, Dictionary data, String where) { String vals = ""; Boolean returnCode = true; if (data.Count >= 1) { foreach (KeyValuePair val in data) { vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString()); } vals = vals.Substring(0, vals.Length - 1); } try { this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where)); } catch { returnCode = false; } return returnCode; } }}
3. 写好dal, 这里有个提示, id可以自增, 但是一定要插入null
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SQLite;namespace SQLiteDemo{ public class BookDAL { SQLiteDatabase sqlExcute = new SQLiteDatabase(); public bool Create(Book book) { try { var sql = "insert into Book values(@ID,@BookName,@Price);"; var cmdparams = new List () { new SQLiteParameter("ID", null), new SQLiteParameter("BookName", book.BookName), new SQLiteParameter("Price", book.Price) }; return sqlExcute.ExecuteNonQuery(sql, cmdparams); } catch (Exception e) { //Do any logging operation here if necessary throw e; return false; } } public bool Update(Book book) { try { var sql = "update Book set BookName=@BookName,Price=@Price where ID=@ID;"; var cmdparams = new List () { new SQLiteParameter("ID", book.ID), new SQLiteParameter("BookName", book.BookName), new SQLiteParameter("Price", book.Price) }; return sqlExcute.ExecuteNonQuery(sql, cmdparams); } catch (Exception) { //Do any logging operation here if necessary return false; } } public bool Delete(int ID) { try { using (SQLiteConnection conn = new SQLiteConnection("Data Source=e:\\test.db3")) { conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "delete from Book where ID=@ID;"; cmd.Parameters.Add(new SQLiteParameter("ID", ID)); int i = cmd.ExecuteNonQuery(); return i == 1; } } catch (Exception) { //Do any logging operation here if necessary return false; } } public Book GetbyID(int ID) { try { var sql = "select * from Book where ID=@ID;"; var cmdparams = new List () { new SQLiteParameter("ID", ID) }; var dt = sqlExcute.GetDataTable(sql, cmdparams); if (dt.Rows.Count > 0) { Book book = new Book(); book.ID = int.Parse(dt.Rows[0]["ID"].ToString()); book.BookName = dt.Rows[0]["BookName"].ToString(); book.Price = decimal.Parse(dt.Rows[0]["Price"].ToString()); return book; } else return null; } catch (Exception) { //Do any logging operation here if necessary return null; } } }}
4. 在console里写调用
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SQLite;namespace SQLiteDemo{ class Program { static void Main(string[] args) { BookDAL BookDAL = new SQLiteDemo.BookDAL(); Book book = new Book(); book.BookName = "第一本书"; book.Price = 10.0m; BookDAL.Create(book); book.BookName = "第二本书"; book.Price = 13.0m; BookDAL.Create(book); book = BookDAL.GetbyID(2); Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price); book.Price = 11.1m; BookDAL.Update(book); book = BookDAL.GetbyID(2); Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price); book = BookDAL.GetbyID(1); Console.WriteLine(book.ID + " " + book.BookName + " " + book.Price); Console.Read(); } }}
5.出来实例
注意:
如果客户端调用出错，可能是数据库的位置错误，因为这里是相对bin/debug下的位置，最好放个固定的位置。
参考: