C# 中 SQLite 使用介绍

关于SQLite

         SQLite是一款轻型的嵌入式的遵守ACID的关系型数据库管理系统，诞生已有15个年头了。随着移动互联的发展，现在得到了更广泛的使用。

在使用SQLite之前，我们势必要先了解它一些“个性”的地方。下面是它的一些特点：

1、  自包含。SQLite很大层度上是独立的，他只需要非常小的外部库支持。任何程序能够访问磁盘就可以使用SQLite数据库。这使它适用于嵌入式设备，缺乏桌面计算机支持的基础设施。这也使得SQLite适用于不作任何修改就可运行在不同配置电脑上的程序。

2、  无服务器。大多数SQL数据库引擎被实现为一个单独的服务器进程。程序要访问数据库与服务器通信使用某种形式的进程间通信(通常是TCP / IP)，向服务器发送请求并接收返回结果。SQLite则不是这种工作方式。对于SQLite，想要访问数据库直接从磁盘上的对数据库文件执行读和写操作。没有中间的服务器进程。

3、  零配置。使用SQLite不需要“安装”。没有“设置”程序。没有服务器进程需要启动，停止，或配置。不需要管理员来创建一个新的数据库实例或访问权限分配给用户。SQLite不使用配置文件。

4、  支持事务。事务数据库的所有更改和查询表现出原子性、一致性、隔离性、持久性(ACID)。执行SQLite的事务操作时，要么完全执行，要么不执行，即使写入磁盘的操作被程序崩溃，断电等故障打断。

5、  开源。和前面的特点相比，这个似乎没有多大关系。之所以把它作为一个特点，是因为开源在很大层度上会成为我们选择一个解决方案的重要维度。

 

　　除了这些，SQLite还有着比同是开源的Mysql、PostgreSQL数据库更快的处理效率，更低的资源占用。看起来很“完美”的东西背后往往也有着致命的缺陷。SQLite的缺陷虽不能说致命，但也足以让你在选择的过程中说NO。如果你要求更精准的控制数据库访问，细粒度锁（SQLite只提供数据库级的锁定）以及很好的并发性（虽然可以手动实现并发，但是性能不高，也容易出现死锁），SQLite也许不适合你。另外，SQLite也不适合远程访问，虽然可以通过网络共享的方式运行，但是会存在文件锁定的问题，而且访问网络共享相关的延迟会导致性能的下降。

 

安装配置

         前面说过了，使用SQLite是超级简单的，无需安装，只需要在官网下载库文件添加引用即可。当然，还有很简单的一些配置。还有更简单的方法，使用Nuget添加SQLite库文件并自动配置，这简直是一键搞定啊。

首先，在Nuget中找到SQLite。这里我们选择第一个安装。

 

 

　　会自动安装如下库。

 

 

　　安装后会自动添加如下库的引用。

 

　　并自动添加如下配置。

<?xml version="1.0" encoding="utf-8"?> 2 <configuration> 3   <configSections> 4     <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --> 5     <section name="entityFramework" 6       type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 7       requirePermission="false"/> 8   </configSections> 9   10   <entityFramework>11     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">12       <parameters>13         <parameter value="v11.0"/>14       </parameters>15     </defaultConnectionFactory>16     <providers>17       <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>18       <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/>19     </providers>20   </entityFramework>21 <system.data>22     <DbProviderFactories>23     <remove invariant="System.Data.SQLite.EF6"/><add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6"24         description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"/></DbProviderFactories>25   </system.data></configuration>

 

SQLite的使用 

         上面的准备工作已经就绪。接下来开始使用SQLite。这里不对如何创建数据库作介绍，在实际工作中，我一般会选择使用Navicat for SQLite来创建数据库，方便快捷。如果有编码创建数据库要求，自行问度娘。如果要使用Linq，别忘了添加System.Data.Linq的引用。

    /// <summary>  2     /// SQLite数据库帮助类  3     /// </summary>  4     public class SQLiteHelper  5     {  6         /// <summary>  7         /// 数据库路径  8         /// </summary>  9         private static readonly string m_DataSource = ConfigurationManager.AppSettings["Test"]; 10  11         /// <summary> 12         /// ConnectionString样例：Data Source=Test.db;Pooling=true;FailIfMissing=false 13         /// </summary> 14         private static readonly string m_ConnectionString; 15  16         /// <summary> 17         /// 静态构造函数，初始化连接字符串，检查数据库连接 18         /// </summary> 19         static SQLiteHelper() 20         { 21             try 22             { 23                 SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder 24                 { 25                     Version = 3, 26                     Pooling = true, 27                     FailIfMissing = false, 28                     DataSource = m_DataSource 29                 }; 30                 m_ConnectionString = connectionStringBuilder.ConnectionString; 31                 using (SQLiteConnection conn = new SQLiteConnection(m_ConnectionString)) 32                 { 33                     conn.Open(); 34                 } 35             } 36             catch { } 37         } 38  39         #region basic method 40  41         /// <summary> 42         /// 获得连接对象 43         /// </summary> 44         /// <returns></returns> 45         private static SQLiteConnection GetSQLiteConnection() 46         { 47             return new SQLiteConnection(m_ConnectionString); 48         } 49  50         /// <summary> 51         /// 预备命令 52         /// </summary> 53         /// <param name="cmd"></param> 54         /// <param name="conn"></param> 55         /// <param name="cmdText"></param> 56         /// <param name="commandParameters"></param> 57         private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] commandParameters) 58         { 59             if (conn.State != ConnectionState.Open) 60                 conn.Open(); 61             cmd.Parameters.Clear(); 62             cmd.Connection = conn; 63             cmd.CommandText = cmdText; 64             cmd.CommandType = CommandType.Text; 65             cmd.CommandTimeout = 30; 66             if (commandParameters != null) 67             { 68                 foreach (object parm in commandParameters) 69                     cmd.Parameters.AddWithValue(string.Empty, parm); 70  71                 //for (int i = 0; i < p.Length; i++) 72                 //    cmd.Parameters[i].Value = p[i]; 73             } 74         } 75  76         /// <summary> 77         /// 返回受影响的行数 78         /// </summary> 79         /// <param name="cmdText">执行语句</param> 80         /// <param name="commandParameters">传入的参数</param> 81         /// <returns>返回受影响行数</returns> 82         public static int ExecuteNonQuery(string cmdText, params object[] commandParameters) 83         { 84             SQLiteCommand command = new SQLiteCommand(); 85             using (SQLiteConnection connection = GetSQLiteConnection()) 86             { 87                 PrepareCommand(command, connection, cmdText, commandParameters); 88                 return command.ExecuteNonQuery(); 89             } 90         } 91  92         /// <summary> 93         /// 返回表集合 94         /// </summary> 95         /// <param name="cmdText">执行语句</param> 96         /// <param name="commandParameters">传入的参数</param> 97         /// <returns>返回DataSet</returns> 98         public static DataSet ExecuteDataset(string cmdText, params object[] commandParameters) 99         {100             DataSet ds = new DataSet();101             SQLiteCommand command = new SQLiteCommand();102             using (SQLiteConnection connection = GetSQLiteConnection())103             {104                 PrepareCommand(command, connection, cmdText, commandParameters);105                 SQLiteDataAdapter da = new SQLiteDataAdapter(command);106                 da.Fill(ds);107             }108             return ds;109         }110 111         /// <summary>112         /// 返回SqlDataReader对象113         /// </summary>114         /// <param name="cmdText">执行语句</param>115         /// <param name="commandParameters">传入的参数</param>116         /// <returns>返回SQLiteDataReader</returns>117         public static SQLiteDataReader ExecuteReader(string cmdText, params object[] commandParameters)118         {119             SQLiteCommand command = new SQLiteCommand();120             SQLiteConnection connection = GetSQLiteConnection();121             try122             {123                 PrepareCommand(command, connection, cmdText, commandParameters);124                 SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);125                 return reader;126             }127             catch128             {129                 connection.Close();130                 throw;131             }132         }133 134         /// <summary>135         /// 返回表第一行136         /// </summary>137         /// <param name="cmdText">执行语句</param>138         /// <param name="commandParameters">传入的参数</param>139         /// <returns>返回：第一行</returns>140         public static DataRow ExecuteDataRow(string cmdText, params object[] commandParameters)141         {142             DataSet ds = ExecuteDataset(cmdText, commandParameters);143             if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)144                 return ds.Tables[0].Rows[0];145             return null;146         }147 148         /// <summary>149         /// 返回结果集中的第一行第一列，忽略其他行或列150         /// </summary>151         /// <param name="cmdText">执行语句</param>152         /// <param name="commandParameters">传入的参数</param>153         /// <returns>返回：第一行第一列</returns>154         public static object ExecuteScalar(string cmdText, params object[] commandParameters)155         {156             SQLiteCommand cmd = new SQLiteCommand();157             using (SQLiteConnection connection = GetSQLiteConnection())158             {159                 PrepareCommand(cmd, connection, cmdText, commandParameters);160                 return cmd.ExecuteScalar();161             }162         }163 164         #endregion165 166 167         #region advanced method168 169         /// <summary>170         /// 获取表所有数据171         /// </summary>172         /// <typeparam name="T">实体类型</typeparam>173         /// <param name="tableName">表名</param>174         /// <returns>表所有数据</returns>175         public static List<T> GetTableData<T>(string tableName) where T : class176         {177             List<T> dataList = new List<T>();178             try179             {180                 using (SqliteDataContext context = new SqliteDataContext(new SQLiteConnection(m_ConnectionString)))181                 {182                     string sql = "select * from " + tableName;183                     dataList = context.ExecuteQuery<T>(sql).ToList();184                     context.SubmitChanges();185                 }186             }187             catch { }188             return dataList;189         }190 191         /// <summary>192         /// 获取表数据193         /// </summary>194         /// <typeparam name="T">实体类型</typeparam>195         /// <param name="cmdText">sql语句</param>196         /// <param name="parameter">参数</param>197         /// <returns>表所有数据</returns>198         public static List<T> GetTableData<T>(string cmdText, params object[] parameter) where T : class199         {200             List<T> dataList = new List<T>();201             try202             {203                 using (SqliteDataContext context = new SqliteDataContext(new SQLiteConnection(m_ConnectionString)))204                 {205                     dataList = context.ExecuteQuery<T>(cmdText, parameter).ToList();206                 }207             }208             catch {}209             return dataList;210         }211 212         /// <summary>213         /// 插入表数据214         /// </summary>215         /// <typeparam name="T">数据类型</typeparam>216         /// <param name="tableName">表名</param>217         /// <param name="dataList">数据集合</param>218         /// <returns>true或false</returns>219         public static bool BatchInsert<T>(string tableName, List<T> dataList)220         {221             try222             {223                 if (dataList != null && dataList.Count > 0)224                 {225                     var temp = dataList[0];226                     PropertyInfo[] propertyInfos = temp.GetType().GetProperties();227                     List<string> propertyStrs = new List<string>();228                     string propertyStr = "";229                     foreach (var propertyInfo in propertyInfos)230                     {231                         propertyStrs.Add(propertyInfo.Name);232                         propertyStr = propertyStr + "@" + propertyInfo.Name + ",";233                     }234                     propertyStr = propertyStr.Remove(propertyStr.Length - 1);235 236                     using (SQLiteConnection conn = new SQLiteConnection(m_ConnectionString))237                     {238                         using (SQLiteCommand command = new SQLiteCommand(conn))239                         {240                             command.Connection.Open();241                             using (SQLiteTransaction transaction = conn.BeginTransaction())242                             {243                                 command.Transaction = transaction;244                                 command.CommandText = "insert into " + tableName + " values(" + propertyStr + ")";245                                 foreach (var needInsertData in dataList)246                                 {247                                     command.Parameters.Clear();248                                     for (int i = 0; i < propertyStrs.Count; i++)249                                     {250                                         command.Parameters.AddWithValue("@" + propertyStrs[i], propertyInfos[i].GetValue(needInsertData, null));251                                     }252                                     command.ExecuteNonQuery();253                                 }254                                 transaction.Commit();255                             }256                         }257                     }258                 }259             }260             catch (Exception ex)261             {262                 return false;263             }264             return true;265         }266 267         /// <summary>268         /// 删除表数据269         /// </summary>270         /// <param name="tableName">表名</param>271         /// <returns>true或false</returns>272         public static bool DeleteTableData(string tableName)273         {274             try275             {276                 using (SQLiteConnection conn = new SQLiteConnection(m_ConnectionString))277                 {278                     using (SQLiteCommand command = new SQLiteCommand(conn))279                     {280                         command.Connection.Open();281                         command.CommandText = "delete from " + tableName;282                         command.ExecuteNonQuery();283                     }284                 }285             }286             catch (Exception ex)287             {288                 return false;289             }290             return true;291         }292 293         #endregion294     }

 

 

 

　　如果看了上面的Code，会发现查询表数据方法是使用的Linq。这种方式相比遍历IDataReader要高效得多。只是目前.NET只支持查询，如果想要实现增删改需要第三方库支持。常见的有Dblinq 、linqconnect、linq2db和ado.net sqlite data provider等。不过使用事务批量插入数据也非常的快。

 

写在后面

　　SQLite作为一款轻型高效的嵌入式数据库，适合简单高性能低并发的应用，在移动端使用将会是广泛的，如果是高通信量的Web站点，SQLite是不合适的。

　　据@InkFx指出，也看了一些大数据案例，证实SQLite对高负载支持也很好（未亲自测试）。当然，这种高负载也会受制于运行时的文件系统。