SQLite是一个开源、免费的小型的Embeddable RDBMS(关系型数据库)，用C实现，内存占用较小，支持绝大数的SQL92标准，现在已变得越来越流行，它的体积很小，被广泛应用于各种不同类型的应用中。SQLite已经是世界上布署得最广泛的SQL数据库引擎，被用在无以计数的桌面电脑应用中，还有消费电子设备中，如移动电话、掌上电脑和MP3播放器等。
SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它 占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多 程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度 比他们都快。SQLite第一个Alpha版本诞生于2000年5月. 至今已经有10个年头，SQLite也迎来了一个版本 SQLite 3已经发布。
详细简介：http://baike.baidu.com/view/19310.htm
要使用sqlite保存数据，则需要用到SQLite操作驱动的dll，可以在官网下载，安装完成后，引用安装目录下的System.Data.SQLite.dll文件，
可以在我百度网盘下载：sqlite-netFx40-setup-bundle-x64-2010-1.0.96.0.exe
你也可以直接在项目中安装sqlite，项目会自动添加引用
安装完成后，你会发现。app.config中会自动生成配置代码
1
<?
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"
type
="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
requirePermission
="false"
/>
6
<
section
name
="dataConfiguration"
type
="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=null"
/>
7
</
configSections
>
8
<
system.data
>
9
<
DbProviderFactories
>
10
<
remove
invariant
="System.Data.SQLite.EF6"
/>
11
<
add
name
="SQLite Data Provider (Entity Framework 6)"
invariant
="System.Data.SQLite.EF6"
description
=".NET Framework Data Provider for SQLite (Entity Framework 6)"
type
="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"
/>
12
</
DbProviderFactories
>
13
</
system.data
>
14
<
entityFramework
>
15
<
defaultConnectionFactory
type
="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"
/>
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
<
connectionStrings
>
22
<
add
name
="sqlite"
connectionString
="Data Source=|DataDirectory|document.db;Pooling=true;FailIfMissing=false"
23
providerName
="System.Data.SQLite"
/>
24
</
connectionStrings
>
25
</
configuration
>
这些步骤完成后，就可以操作sqlite数据库。如果你链接数据库的时候。提示：
未能加载文件或程序集“System.Data.SQLite, Version=1.0.66.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139”或它的某一个依赖项。试图加载格式不正确的程序。
这是因为目标平台不匹配， 原因是SQLite下载的平台类型不对造成的，因为你下载的sqlite驱动分x86和x64位，你可以把项目平台改成匹配的，右键项目属性：
前期准备完成，在编码前，当然少不了一个工具，来对数据库进行管理。创建库、表、执行SQL语句操作，
比如：SQLite Expert Personal 3，Navicat for SQLite等等，
SQLite Expert Personal 3界面，
我这里使用Navicat for SQLite来管理sqlite，
打开Navicat for SQLite，单击连接，如图：
连接成功后，创建表，添加数据。
我这里创建一个表：document.db，并添加简单的数据用于测试
接下来可以在vs中编码，如果用过sql server，那么sqlite就没什么难的
使用原生态的ADO.NET访问SQLite
原生态的访问，就是说直接用connection和command这些对象打开数据库，然后打开连接，进行数据的操作。
在App.config中配置connectionStrings
1
<
connectionStrings
>
2
<
add
name
="sqlite"
connectionString
="Data Source=|DataDirectory|document.db;Pooling=true;FailIfMissing=false"
3
providerName
="System.Data.SQLite"
/>
4
</
connectionStrings
>
上面的connectionstring配置节的db就是SQLite的数据库文件，将它放在Web应用的App_Data目录，|DataDirectory|就代表这个目录的位置，后面的就是文件名。 剩下的就是我们使用企业库访问SQL Server是一样的了。
到这里。其实有一个盲区。就是App_Data，是web应用中才有，但winform中是没有的。在winform中DataDirectory被程序弄成了apppath/bin/debug目录，所以，此时。你需要把document.db赋值到debug目录下面。
现在来测试是否可以成功访问数据库并读取数据
1
string sql = "SELECT * FROM userInfo";
2//string conStr = "D:/sqlliteDb/document.db"; 3string connStr = @"Data Source=" + @"D:sqlliteDbdocument.db;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
4using (SQLiteConnection conn = new SQLiteConnection(connStr))
5 {
6//conn.Open(); 7using (SQLiteDataAdapter ap = new SQLiteDataAdapter(sql, conn))
8 {
9 DataSet ds = new DataSet();
10 ap.Fill(ds);
1112 DataTable dt = ds.Tables[0];
13 }
14 }
设置一个断点，发现已经得到sqlite中的数据
如果想读取app.config中的数据库连接字符串
1
string config = System.Configuration.ConfigurationManager.ConnectionStrings["sqlite"].ConnectionString;
2using (SQLiteConnection conn = new SQLiteConnection(config))
3 {
4 conn.Open();
5//DbCommand comm = conn.CreateCommand();
6//comm.CommandText = "select * from userinfo";
7//comm.CommandType = CommandType.Text;
8 9//conn.Open();10using (SQLiteDataAdapter ap = new SQLiteDataAdapter(sql, conn))
11 {
12 DataSet ds = new DataSet();
13 ap.Fill(ds);
1415 DataTable dt = ds.Tables[0];
16 }
17 }
使用SQLite.NET访问SQLite
SQLite.NET也是一个数据访问组件，其中的System.Data.SQLite 就好像是.NET自带的System.Data.SqlClient一样。里面包含了connection、command等数据访问的常用对象，只是他们前面都有一个前缀sqlite。
回到之前的app.config。当用NuGet程序包安装sqlite后。里面自动生成了如下代码
1
<
system.data
>
2
<
DbProviderFactories
>
3
<
remove
invariant
="System.Data.SQLite.EF6"
/>
4
<
add
name
="SQLite Data Provider (Entity Framework 6)"
invariant
="System.Data.SQLite.EF6"
description
=".NET Framework Data Provider for SQLite (Entity Framework 6)"
type
="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"
/>
5
</
DbProviderFactories
>
6
</
system.data
>
即：添加一个DbProviderFactory的创建源，在代码中就可以使用DbProviderFactory类来创建SQLite的数据访问对象。
1 DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite.EF6");
2using (DbConnection conn = fact.CreateConnection())
3 {
4 conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["sqlite"].ConnectionString;
5 conn.Open();
6 DbCommand comm = conn.CreateCommand();
7 comm.CommandText = "select * from userInfo";
8 comm.CommandType = CommandType.Text;
9using (IDataReader reader = comm.ExecuteReader())
10 {
11while (reader.Read())
12 {
13string dd = reader["name"].ToString();
14 }
15 }
16 }
同样测试看结果：
可以用SQLiteConnection.CreateFile("D:/d.db");直接创建一个数据库文件
网上找了一个sqlite帮助类，有需要的可以看下
1
///
<summary>
2
///
SQLiteHelper is a utility class similar to "SQLHelper" in MS
3
///
Data Access Application Block and follows similar pattern.
4
///
</summary>
5
public
class
SQLiteHelper
6
{
7
///
<summary>
8
///
Creates a new
<see cref="SQLiteHelper"/>
instance. The ctor is marked private since all members are static.
9
///
</summary>
10
private
SQLiteHelper()
11
{
12
}
13
///
<summary>
14
///
Creates the command.
15
///
</summary>
16
///
<param name="connection">
Connection.
</param>
17
///
<param name="commandText">
Command text.
</param>
18
///
<param name="commandParameters">
Command parameters.
</param>
19
///
<returns>
SQLite Command
</returns>
20
public
static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
21 {
22 SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
23if (commandParameters.Length > 0)
24 {
25foreach (SQLiteParameter parm in commandParameters)
26 cmd.Parameters.Add(parm);
27 }
28return cmd;
29 }
30 31///<summary> 32/// Creates the command.
33///</summary> 34///<param name="connectionString">Connection string.</param> 35///<param name="commandText">Command text.</param> 36///<param name="commandParameters">Command parameters.</param> 37///<returns>SQLite Command</returns> 38publicstatic SQLiteCommand CreateCommand(string connectionString, string commandText, params SQLiteParameter[] commandParameters)
39 {
40 SQLiteConnection cn = new SQLiteConnection(connectionString);
41 42 SQLiteCommand cmd = new SQLiteCommand(commandText, cn);
43 44if (commandParameters.Length > 0)
45 {
46foreach (SQLiteParameter parm in commandParameters)
47 cmd.Parameters.Add(parm);
48 }
49return cmd;
50 }
51///<summary> 52/// Creates the parameter.
53///</summary> 54///<param name="parameterName">Name of the parameter.</param> 55///<param name="parameterType">Parameter type.</param> 56///<param name="parameterValue">Parameter value.</param> 57///<returns>SQLiteParameter</returns> 58publicstatic SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
59 {
60 SQLiteParameter parameter = new SQLiteParameter();
61 parameter.DbType = parameterType;
62 parameter.ParameterName = parameterName;
63 parameter.Value = parameterValue;
64return parameter;
65 }
66 67///<summary> 68/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
69///</summary> 70///<param name="connectionString">SQLite Connection string</param> 71///<param name="commandText">SQL Statement with embedded "@param" style parameter names</param> 72///<param name="paramList">object[] array of parameter values</param> 73///<returns></returns> 74publicstatic DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
75 {
76 SQLiteConnection cn = new SQLiteConnection(connectionString);
77 SQLiteCommand cmd = cn.CreateCommand();
78 79 80 cmd.CommandText = commandText;
81if (paramList != null)
82 {
83 AttachParameters(cmd, commandText, paramList);
84 }
85 DataSet ds = new DataSet();
86if (cn.State == ConnectionState.Closed)
87 cn.Open();
88 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
89 da.Fill(ds);
90 da.Dispose();
91 cmd.Dispose();
92 cn.Close();
93return ds;
94 }
95///<summary> 96/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
97///</summary> 98///<param name="cn">Connection.</param> 99///<param name="commandText">Command text.</param>100///<param name="paramList">Param list.</param>101///<returns></returns>102publicstatic DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
103 {
104105 SQLiteCommand cmd = cn.CreateCommand();
106107108 cmd.CommandText = commandText;
109if (paramList != null)
110 {
111 AttachParameters(cmd, commandText, paramList);
112 }
113 DataSet ds = new DataSet();
114if (cn.State == ConnectionState.Closed)
115 cn.Open();
116 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
117 da.Fill(ds);
118 da.Dispose();
119 cmd.Dispose();
120 cn.Close();
121return ds;
122 }
123///<summary>124/// Executes the dataset from a populated Command object.
125///</summary>126///<param name="cmd">Fully populated SQLiteCommand</param>127///<returns>DataSet</returns>128publicstatic DataSet ExecuteDataset(SQLiteCommand cmd)
129 {
130if (cmd.Connection.State == ConnectionState.Closed)
131 cmd.Connection.Open();
132 DataSet ds = new DataSet();
133 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
134 da.Fill(ds);
135 da.Dispose();
136 cmd.Connection.Close();
137 cmd.Dispose();
138return ds;
139 }
140141///<summary>142/// Executes the dataset in a SQLite Transaction
143///</summary>144///<param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>145///<param name="commandText">Command text.</param>146///<param name="commandParameters">Sqlite Command parameters.</param>147///<returns>DataSet</returns>148///<remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>149publicstatic DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)
150 {
151152if (transaction == null) thrownew ArgumentNullException("transaction");
153if (transaction != null && transaction.Connection == null) thrownew ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
154 IDbCommand cmd = transaction.Connection.CreateCommand();
155 cmd.CommandText = commandText;
156foreach (SQLiteParameter parm in commandParameters)
157 {
158 cmd.Parameters.Add(parm);
159 }
160if (transaction.Connection.State == ConnectionState.Closed)
161 transaction.Connection.Open();
162 DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
163return ds;
164 }
165166///<summary>167/// Executes the dataset with Transaction and object array of parameter values.
168///</summary>169///<param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>170///<param name="commandText">Command text.</param>171///<param name="commandParameters">object[] array of parameter values.</param>172///<returns>DataSet</returns>173///<remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>174publicstatic DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, object[] commandParameters)
175 {
176177if (transaction == null) thrownew ArgumentNullException("transaction");
178if (transaction != null && transaction.Connection == null) thrownew ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
179 IDbCommand cmd = transaction.Connection.CreateCommand();
180 cmd.CommandText = commandText;
181 AttachParameters((SQLiteCommand)cmd, cmd.CommandText, commandParameters);
182if (transaction.Connection.State == ConnectionState.Closed)
183 transaction.Connection.Open();
184185 DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
186return ds;
187 }
188189#region UpdateDataset
190///<summary>191/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
192///</summary>193///<remarks>194/// e.g.:
195/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
196///</remarks>197///<param name="insertCommand">A valid SQL statement to insert new records into the data source</param>198///<param name="deleteCommand">A valid SQL statement to delete records from the data source</param>199///<param name="updateCommand">A valid SQL statement used to update records in the data source</param>200///<param name="dataSet">The DataSet used to update the data source</param>201///<param name="tableName">The DataTable used to update the data source.</param>202publicstaticvoid UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
203 {
204if (insertCommand == null) thrownew ArgumentNullException("insertCommand");
205if (deleteCommand == null) thrownew ArgumentNullException("deleteCommand");
206if (updateCommand == null) thrownew ArgumentNullException("updateCommand");
207if (tableName == null || tableName.Length == 0) thrownew ArgumentNullException("tableName");
208209// Create a SQLiteDataAdapter, and dispose of it after we are done210using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
211 {
212// Set the data adapter commands213 dataAdapter.UpdateCommand = updateCommand;
214 dataAdapter.InsertCommand = insertCommand;
215 dataAdapter.DeleteCommand = deleteCommand;
216217// Update the dataset changes in the data source218 dataAdapter.Update(dataSet, tableName);
219220// Commit all the changes made to the DataSet221 dataSet.AcceptChanges();
222 }
223 }
224#endregion225226227228229///<summary>230/// ShortCut method to return IDataReader
231/// NOTE: You should explicitly close the Command.connection you passed in as
232/// well as call Dispose on the Command after reader is closed.
233/// We do this because IDataReader has no underlying Connection Property.
234///</summary>235///<param name="cmd">SQLiteCommand Object</param>236///<param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>237///<param name="paramList">object[] array of parameter values</param>238///<returns>IDataReader</returns>239publicstatic IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
240 {
241if (cmd.Connection == null)
242thrownew ArgumentException("Command must have live connection attached.", "cmd");
243 cmd.CommandText = commandText;
244 AttachParameters(cmd, commandText, paramList);
245if (cmd.Connection.State == ConnectionState.Closed)
246 cmd.Connection.Open();
247 IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
248return rdr;
249 }
250251///<summary>252/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
253///</summary>254///<param name="connectionString">SQLite Connection String</param>255///<param name="commandText">Sql Statement with embedded "@param" style parameters</param>256///<param name="paramList">object[] array of parameter values</param>257///<returns></returns>258publicstaticint ExecuteNonQuery(string connectionString, string commandText, paramsobject[] paramList)
259 {
260 SQLiteConnection cn = new SQLiteConnection(connectionString);
261 SQLiteCommand cmd = cn.CreateCommand();
262 cmd.CommandText = commandText;
263 AttachParameters(cmd, commandText, paramList);
264if (cn.State == ConnectionState.Closed)
265 cn.Open();
266int result = cmd.ExecuteNonQuery();
267 cmd.Dispose();
268 cn.Close();
269270return result;
271 }
272273274275publicstaticint ExecuteNonQuery(SQLiteConnection cn, string commandText, paramsobject[] paramList)
276 {
277278 SQLiteCommand cmd = cn.CreateCommand();
279 cmd.CommandText = commandText;
280 AttachParameters(cmd, commandText, paramList);
281if (cn.State == ConnectionState.Closed)
282 cn.Open();
283int result = cmd.ExecuteNonQuery();
284 cmd.Dispose();
285 cn.Close();
286287return result;
288 }
289290///<summary>291/// Executes non-query sql Statment with Transaction
292///</summary>293///<param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>294///<param name="commandText">Command text.</param>295///<param name="paramList">Param list.</param>296///<returns>Integer</returns>297///<remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>298publicstaticint ExecuteNonQuery(SQLiteTransaction transaction, string commandText, paramsobject[] paramList)
299 {
300if (transaction == null) thrownew ArgumentNullException("transaction");
301if (transaction != null && transaction.Connection == null) thrownew ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
302 IDbCommand cmd = transaction.Connection.CreateCommand();
303 cmd.CommandText = commandText;
304 AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
305if (transaction.Connection.State == ConnectionState.Closed)
306 transaction.Connection.Open();
307int result = cmd.ExecuteNonQuery();
308 cmd.Dispose();
309return result;
310 }
311312313///<summary>314/// Executes the non query.
315///</summary>316///<param name="cmd">CMD.</param>317///<returns></returns>318publicstaticint ExecuteNonQuery(IDbCommand cmd)
319 {
320if (cmd.Connection.State == ConnectionState.Closed)
321 cmd.Connection.Open();
322int result = cmd.ExecuteNonQuery();
323 cmd.Connection.Close();
324 cmd.Dispose();
325return result;
326 }
327328///<summary>329/// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
330///</summary>331///<param name="connectionString">SQLite Connection String</param>332///<param name="commandText">SQL statment with embedded "@param" style parameters</param>333///<param name="paramList">object[] array of param values</param>334///<returns></returns>335publicstaticobject ExecuteScalar(string connectionString, string commandText, paramsobject[] paramList)
336 {
337 SQLiteConnection cn = new SQLiteConnection(connectionString);
338 SQLiteCommand cmd = cn.CreateCommand();
339 cmd.CommandText = commandText;
340 AttachParameters(cmd, commandText, paramList);
341if (cn.State == ConnectionState.Closed)
342 cn.Open();
343object result = cmd.ExecuteScalar();
344 cmd.Dispose();
345 cn.Close();
346347return result;
348 }
349350///<summary>351/// Execute XmlReader with complete Command
352///</summary>353///<param name="command">SQLite Command</param>354///<returns>XmlReader</returns>355publicstatic XmlReader ExecuteXmlReader(IDbCommand command)
356 { // open the connection if necessary, but make sure we
357// know to close it when we?re done.358if (command.Connection.State != ConnectionState.Open)
359 {
360 command.Connection.Open();
361 }
362363// get a data adapter 364 SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command);
365 DataSet ds = new DataSet();
366// fill the data set, and return the schema information367 da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
368 da.Fill(ds);
369// convert our dataset to XML370 StringReader stream = new StringReader(ds.GetXml());
371 command.Connection.Close();
372// convert our stream of text to an XmlReader373returnnew XmlTextReader(stream);
374 }
375376377378///<summary>379/// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection.
380///</summary>381///<param name="commandText">Sql Statement with "@param" style embedded parameters</param>382///<param name="paramList">object[] array of parameter values</param>383///<returns>SQLiteParameterCollection</returns>384///<remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks>385privatestatic SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, paramsobject[] paramList)
386 {
387if (paramList == null || paramList.Length == 0) returnnull;
388389 SQLiteParameterCollection coll = cmd.Parameters;
390string parmString = commandText.Substring(commandText.IndexOf("@"));
391// pre-process the string so always at least 1 space after a comma.392 parmString = parmString.Replace(",", " ,");
393// get the named parameters into a match collection394string pattern = @"(@)S*(.*?)b";
395 Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
396 MatchCollection mc = ex.Matches(parmString);
397string[] paramNames = newstring[mc.Count];
398int i = 0;
399foreach (Match m in mc)
400 {
401 paramNames[i] = m.Value;
402 i++;
403 }
404405// now let‘s type the parameters406int j = 0;
407 Type t = null;
408foreach (object o in paramList)
409 {
410 t = o.GetType();
411412 SQLiteParameter parm = new SQLiteParameter();
413switch (t.ToString())
414 {
415416case ("DBNull"):
417case ("Char"):
418case ("SByte"):
419case ("UInt16"):
420case ("UInt32"):
421case ("UInt64"):
422thrownew SystemException("Invalid data type");
423424425case ("System.String"):
426 parm.DbType = DbType.String;
427 parm.ParameterName = paramNames[j];
428 parm.Value = (string)paramList[j];
429 coll.Add(parm);
430break;
431432case ("System.Byte[]"):
433 parm.DbType = DbType.Binary;
434 parm.ParameterName = paramNames[j];
435 parm.Value = (byte[])paramList[j];
436 coll.Add(parm);
437break;
438439case ("System.Int32"):
440 parm.DbType = DbType.Int32;
441 parm.ParameterName = paramNames[j];
442 parm.Value = (int)paramList[j];
443 coll.Add(parm);
444break;
445446case ("System.Boolean"):
447 parm.DbType = DbType.Boolean;
448 parm.ParameterName = paramNames[j];
449 parm.Value = (bool)paramList[j];
450 coll.Add(parm);
451break;
452453case ("System.DateTime"):
454 parm.DbType = DbType.DateTime;
455 parm.ParameterName = paramNames[j];
456 parm.Value = Convert.ToDateTime(paramList[j]);
457 coll.Add(parm);
458break;
459460case ("System.Double"):
461 parm.DbType = DbType.Double;
462 parm.ParameterName = paramNames[j];
463 parm.Value = Convert.ToDouble(paramList[j]);
464 coll.Add(parm);
465break;
466467case ("System.Decimal"):
468 parm.DbType = DbType.Decimal;
469 parm.ParameterName = paramNames[j];
470 parm.Value = Convert.ToDecimal(paramList[j]);
471break;
472473case ("System.Guid"):
474 parm.DbType = DbType.Guid;
475 parm.ParameterName = paramNames[j];
476 parm.Value = (System.Guid)(paramList[j]);
477break;
478479case ("System.Object"):
480481 parm.DbType = DbType.Object;
482 parm.ParameterName = paramNames[j];
483 parm.Value = paramList[j];
484 coll.Add(parm);
485break;
486487default:
488thrownew SystemException("Value is of unknown data type");
489490 } // end switch491492 j++;
493 }
494return coll;
495 }
496497///<summary>498/// Executes non query typed params from a DataRow
499///</summary>500///<param name="command">Command.</param>501///<param name="dataRow">Data row.</param>502///<returns>Integer result code</returns>503publicstaticint ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)
504 {
505int retVal = 0;
506507// If the row has values, the store procedure parameters must be initialized508if (dataRow != null && dataRow.ItemArray.Length > 0)
509 {
510// Set the parameters values511 AssignParameterValues(command.Parameters, dataRow);
512513 retVal = ExecuteNonQuery(command);
514 }
515else516 {
517 retVal = ExecuteNonQuery(command);
518 }
519520return retVal;
521 }
522523///<summary>524/// This method assigns dataRow column values to an IDataParameterCollection
525///</summary>526///<param name="commandParameters">The IDataParameterCollection to be assigned values</param>527///<param name="dataRow">The dataRow used to hold the command‘s parameter values</param>528///<exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>529protectedinternalstaticvoid AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow)
530 {
531if (commandParameters == null || dataRow == null)
532 {
533// Do nothing if we get no data534return;
535 }
536537 DataColumnCollection columns = dataRow.Table.Columns;
538539int i = 0;
540// Set the parameters values541foreach (IDataParameter commandParameter in commandParameters)
542 {
543// Check the parameter name544if (commandParameter.ParameterName == null ||
545 commandParameter.ParameterName.Length <= 1)
546thrownew InvalidOperationException(string.Format(
547"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: ‘{1}‘.",
548 i, commandParameter.ParameterName));
549550if (columns.Contains(commandParameter.ParameterName))
551 commandParameter.Value = dataRow[commandParameter.ParameterName];
552elseif (columns.Contains(commandParameter.ParameterName.Substring(1)))
553 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
554555 i++;
556 }
557 }
558559///<summary>560/// This method assigns dataRow column values to an array of IDataParameters
561///</summary>562///<param name="commandParameters">Array of IDataParameters to be assigned values</param>563///<param name="dataRow">The dataRow used to hold the stored procedure‘s parameter values</param>564///<exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>565protectedvoid AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow)
566 {
567if ((commandParameters == null) || (dataRow == null))
568 {
569// Do nothing if we get no data570return;
571 }
572573 DataColumnCollection columns = dataRow.Table.Columns;
574575int i = 0;
576// Set the parameters values577foreach (IDataParameter commandParameter in commandParameters)
578 {
579// Check the parameter name580if (commandParameter.ParameterName == null ||
581 commandParameter.ParameterName.Length <= 1)
582thrownew InvalidOperationException(string.Format(
583"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: ‘{1}‘.",
584 i, commandParameter.ParameterName));
585586if (columns.Contains(commandParameter.ParameterName))
587 commandParameter.Value = dataRow[commandParameter.ParameterName];
588elseif (columns.Contains(commandParameter.ParameterName.Substring(1)))
589 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
590591 i++;
592 }
593 }
594595///<summary>596/// This method assigns an array of values to an array of IDataParameters
597///</summary>598///<param name="commandParameters">Array of IDataParameters to be assigned values</param>599///<param name="parameterValues">Array of objects holding the values to be assigned</param>600///<exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception>601protectedvoid AssignParameterValues(IDataParameter[] commandParameters, paramsobject[] parameterValues)
602 {
603if ((commandParameters == null) || (parameterValues == null))
604 {
605// Do nothing if we get no data606return;
607 }
608609// We must have the same number of values as we pave parameters to put them in610if (commandParameters.Length != parameterValues.Length)
611 {
612thrownew ArgumentException("Parameter count does not match Parameter Value count.");
613 }
614615// Iterate through the IDataParameters, assigning the values from the corresponding position in the
616// value array617for (int i = 0, j = commandParameters.Length, k = 0; i < j; i++)
618 {
619if (commandParameters[i].Direction != ParameterDirection.ReturnValue)
620 {
621// If the current array value derives from IDataParameter, then assign its Value property622if (parameterValues[k] is IDataParameter)
623 {
624 IDataParameter paramInstance;
625 paramInstance = (IDataParameter)parameterValues[k];
626if (paramInstance.Direction == ParameterDirection.ReturnValue)
627 {
628 paramInstance = (IDataParameter)parameterValues[++k];
629 }
630if (paramInstance.Value == null)
631 {
632 commandParameters[i].Value = DBNull.Value;
633 }
634else635 {
636 commandParameters[i].Value = paramInstance.Value;
637 }
638 }
639elseif (parameterValues[k] == null)
640 {
641 commandParameters[i].Value = DBNull.Value;
642 }
643else644 {
645 commandParameters[i].Value = parameterValues[k];
646 }
647 k++;
648 }
649 }
650 }
651 }
其他资料：
http://www.cnblogs.com/virusswb/archive/2010/09/17/SQLite1.html
http://blog.csdn.net/heqichanggg/article/details/5784839
http://www.cnblogs.com/luxiaoxun/p/3784729.html
http://www.cnblogs.com/xugang/archive/2011/04/19/2020713.html
原文：http://www.cnblogs.com/nsky/p/4479850.html
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！