SQLite简介及SQLite在.NET中的应用

在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里写好数据库操作类(写的比较简陋, 大家自行完善)

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

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
        /// <summary>
        ///     Default Constructor for SQLiteDatabase Class.
        /// </summary>
        public SQLiteDatabase()
        {
            dbConnection = "Data Source=recipes.s3db";
            cnn = new SQLiteConnection(dbConnection);
        }
 
        /// <summary>
        ///     Single Param Constructor for specifying the DB file.
        /// </summary>
        /// <param name="inputFile">The File containing the DB</param>
        public SQLiteDatabase(String inputFile)
        {
            dbConnection = String.Format("Data Source={0}", inputFile);
            cnn = new SQLiteConnection(dbConnection);
        }
 
        /// <summary>
        ///     Single Param Constructor for specifying advanced connection options.
        /// </summary>
        /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
        public SQLiteDatabase(Dictionary<String, String> connectionOpts)
        {
            String str = "";
            foreach (KeyValuePair<String, String> 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
 
        /// <summary>
        ///     Allows the programmer to run a query against the Database.
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <returns>A DataTable containing the result set.</returns>
        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<SQLiteParameter> 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;
        }
 
        /// <summary>
        ///     Allows the programmer to interact with the database for purposes other than a query.
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        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<SQLiteParameter> 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;
        }
 
        /// <summary>
        ///     暂时用不到
        ///     Allows the programmer to retrieve single items from the DB.
        /// </summary>
        /// <param name="sql">The query to run.</param>
        /// <returns>A string.</returns>
        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 "";
        }
 
        /// <summary>
        ///     Allows the programmer to easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> 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

100

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<SQLiteParameter>()
                {
                    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<SQLiteParameter>()
                {
                    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<SQLiteParameter>()
                {
                    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();
        }
    }
 
}