SQLite之C#连接SQLite，
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://www.sqlite.org/
详细简介：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";
3 string connStr = @"Data Source=" + @"D:\sqlliteDb\document.db;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
4 using (SQLiteConnection conn = new SQLiteConnection(connStr))
5 {
6 //conn.Open();
7 using (SQLiteDataAdapter ap = new SQLiteDataAdapter(sql, conn))
8 {
9 DataSet ds = new DataSet();
10 ap.Fill(ds);
11
12 DataTable dt = ds.Tables[0];
13 }
14 }
设置一个断点，发现已经得到sqlite中的数据
如果想读取app.config中的数据库连接字符串
1 string config = System.Configuration.ConfigurationManager.ConnectionStrings["sqlite"].ConnectionString;
2 using (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();
10 using (SQLiteDataAdapter ap = new SQLiteDataAdapter(sql, conn))
11 {
12 DataSet ds = new DataSet();
13 ap.Fill(ds);
14
15 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");
2 using (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;
9 using (IDataReader reader = comm.ExecuteReader())
10 {
11 while (reader.Read())
12 {
13 string 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);
23 if (commandParameters.Length >
0)
24 {
25 foreach (SQLiteParameter parm
in commandParameters)
26 cmd.Parameters.Add(parm);
27 }
28 return 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>
38 public static 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
44 if (commandParameters.Length >
0)
45 {
46 foreach (SQLiteParameter parm
in commandParameters)
47 cmd.Parameters.Add(parm);
48 }
49 return 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>
58 public static 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;
64 return 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>
74 public static 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;
81 if (paramList !=
null)
82 {
83 AttachParameters(cmd, commandText, paramList);
84 }
85 DataSet ds =
new DataSet();
86 if (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();
93 return 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>
102 public static DataSet ExecuteDataSet(SQLiteConnection cn,
string commandText,
object[] paramList)
103 {
104
105 SQLiteCommand cmd =
cn.CreateCommand();
106
107
108 cmd.CommandText =
commandText;
109 if (paramList !=
null)
110 {
111 AttachParameters(cmd, commandText, paramList);
112 }
113 DataSet ds =
new DataSet();
114 if (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();
121 return 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>
128 public static DataSet ExecuteDataset(SQLiteCommand cmd)
129 {
130 if (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();
138 return ds;
139 }
140
141 /// <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>
149 public static DataSet ExecuteDataset(SQLiteTransaction transaction,
string commandText,
params SQLiteParameter[] commandParameters)
150 {
151
152 if (transaction ==
null)
throw new ArgumentNullException(
"transaction");
153 if (transaction !=
null && transaction.Connection ==
null)
throw new ArgumentException(
"The transaction was rolled back or committed, please provide an open transaction.",
"transaction");
154 IDbCommand cmd =
transaction.Connection.CreateCommand();
155 cmd.CommandText =
commandText;
156 foreach (SQLiteParameter parm
in commandParameters)
157 {
158 cmd.Parameters.Add(parm);
159 }
160 if (transaction.Connection.State ==
ConnectionState.Closed)
161 transaction.Connection.Open();
162 DataSet ds =
ExecuteDataset((SQLiteCommand)cmd);
163 return ds;
164 }
165
166 /// <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>
174 public static DataSet ExecuteDataset(SQLiteTransaction transaction,
string commandText,
object[] commandParameters)
175 {
176
177 if (transaction ==
null)
throw new ArgumentNullException(
"transaction");
178 if (transaction !=
null && transaction.Connection ==
null)
throw new 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);
182 if (transaction.Connection.State ==
ConnectionState.Closed)
183 transaction.Connection.Open();
184
185 DataSet ds =
ExecuteDataset((SQLiteCommand)cmd);
186 return ds;
187 }
188
189 #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>
202 public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet,
string tableName)
203 {
204 if (insertCommand ==
null)
throw new ArgumentNullException(
"insertCommand");
205 if (deleteCommand ==
null)
throw new ArgumentNullException(
"deleteCommand");
206 if (updateCommand ==
null)
throw new ArgumentNullException(
"updateCommand");
207 if (tableName ==
null || tableName.Length ==
0)
throw new ArgumentNullException(
"tableName");
208
209 // Create a SQLiteDataAdapter, and dispose of it after we are done
210 using (SQLiteDataAdapter dataAdapter =
new SQLiteDataAdapter())
211 {
212 // Set the data adapter commands
213 dataAdapter.UpdateCommand =
updateCommand;
214 dataAdapter.InsertCommand =
insertCommand;
215 dataAdapter.DeleteCommand =
deleteCommand;
216
217 // Update the dataset changes in the data source
218 dataAdapter.Update(dataSet, tableName);
219
220 // Commit all the changes made to the DataSet
221 dataSet.AcceptChanges();
222 }
223 }
224 #endregion
225
226
227
228
229 /// <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>
239 public static IDataReader ExecuteReader(SQLiteCommand cmd,
string commandText,
object[] paramList)
240 {
241 if (cmd.Connection ==
null)
242 throw new ArgumentException(
"Command must have live connection attached.",
"cmd");
243 cmd.CommandText =
commandText;
244 AttachParameters(cmd, commandText, paramList);
245 if (cmd.Connection.State ==
ConnectionState.Closed)
246 cmd.Connection.Open();
247 IDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
248 return rdr;
249 }
250
251 /// <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>
258 public static int ExecuteNonQuery(
string connectionString,
string commandText,
params object[] paramList)
259 {
260 SQLiteConnection cn =
new SQLiteConnection(connectionString);
261 SQLiteCommand cmd =
cn.CreateCommand();
262 cmd.CommandText =
commandText;
263 AttachParameters(cmd, commandText, paramList);
264 if (cn.State ==
ConnectionState.Closed)
265 cn.Open();
266 int result =
cmd.ExecuteNonQuery();
267 cmd.Dispose();
268 cn.Close();
269
270 return result;
271 }
272
273
274
275 public static int ExecuteNonQuery(SQLiteConnection cn,
string commandText,
params object[] paramList)
276 {
277
278 SQLiteCommand cmd =
cn.CreateCommand();
279 cmd.CommandText =
commandText;
280 AttachParameters(cmd, commandText, paramList);
281 if (cn.State ==
ConnectionState.Closed)
282 cn.Open();
283 int result =
cmd.ExecuteNonQuery();
284 cmd.Dispose();
285 cn.Close();
286
287 return result;
288 }
289
290 /// <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>
298 public static int ExecuteNonQuery(SQLiteTransaction transaction,
string commandText,
params object[] paramList)
299 {
300 if (transaction ==
null)
throw new ArgumentNullException(
"transaction");
301 if (transaction !=
null && transaction.Connection ==
null)
throw new 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);
305 if (transaction.Connection.State ==
ConnectionState.Closed)
306 transaction.Connection.Open();
307 int result =
cmd.ExecuteNonQuery();
308 cmd.Dispose();
309 return result;
310 }
311
312
313 /// <summary>
314 /// Executes the non query.
315 /// </summary>
316 /// <param name="cmd">CMD.</param>
317 /// <returns></returns>
318 public static int ExecuteNonQuery(IDbCommand cmd)
319 {
320 if (cmd.Connection.State ==
ConnectionState.Closed)
321 cmd.Connection.Open();
322 int result =
cmd.ExecuteNonQuery();
323 cmd.Connection.Close();
324 cmd.Dispose();
325 return result;
326 }
327
328 /// <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>
335 public static object ExecuteScalar(
string connectionString,
string commandText,
params object[] paramList)
336 {
337 SQLiteConnection cn =
new SQLiteConnection(connectionString);
338 SQLiteCommand cmd =
cn.CreateCommand();
339 cmd.CommandText =
commandText;
340 AttachParameters(cmd, commandText, paramList);
341 if (cn.State ==
ConnectionState.Closed)
342 cn.Open();
343 object result =
cmd.ExecuteScalar();
344 cmd.Dispose();
345 cn.Close();
346
347 return result;
348 }
349
350 /// <summary>
351 /// Execute XmlReader with complete Command
352 /// </summary>
353 /// <param name="command">SQLite Command</param>
354 /// <returns>XmlReader</returns>
355 public static XmlReader ExecuteXmlReader(IDbCommand command)
356 {
// open the connection if necessary, but make sure we
357 // know to close it when we�re done.
358 if (command.Connection.State !=
ConnectionState.Open)
359 {
360 command.Connection.Open();
361 }
362
363 // 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 information
367 da.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
368 da.Fill(ds);
369 // convert our dataset to XML
370 StringReader stream =
new StringReader(ds.GetXml());
371 command.Connection.Close();
372 // convert our stream of text to an XmlReader
373 return new XmlTextReader(stream);
374 }
375
376
377
378 /// <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>
385 private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd,
string commandText,
params object[] paramList)
386 {
387 if (paramList ==
null || paramList.Length ==
0)
return null;
388
389 SQLiteParameterCollection coll =
cmd.Parameters;
390 string 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 collection
394 string pattern =
@"(@)\S*(.*?)\b";
395 Regex ex =
new Regex(pattern, RegexOptions.IgnoreCase);
396 MatchCollection mc =
ex.Matches(parmString);
397 string[] paramNames =
new string[mc.Count];
398 int i =
0;
399 foreach (Match m
in mc)
400 {
401 paramNames[i] =
m.Value;
402 i++
;
403 }
404
405 // now let's type the parameters
406 int j =
0;
407 Type t =
null;
408 foreach (
object o
in paramList)
409 {
410 t =
o.GetType();
411
412 SQLiteParameter parm =
new SQLiteParameter();
413 switch (t.ToString())
414 {
415
416 case (
"DBNull"):
417 case (
"Char"):
418 case (
"SByte"):
419 case (
"UInt16"):
420 case (
"UInt32"):
421 case (
"UInt64"):
422 throw new SystemException(
"Invalid data type");
423
424
425 case (
"System.String"):
426 parm.DbType =
DbType.String;
427 parm.ParameterName =
paramNames[j];
428 parm.Value = (
string)paramList[j];
429 coll.Add(parm);
430 break;
431
432 case (
"System.Byte[]"):
433 parm.DbType =
DbType.Binary;
434 parm.ParameterName =
paramNames[j];
435 parm.Value = (
byte[])paramList[j];
436 coll.Add(parm);
437 break;
438
439 case (
"System.Int32"):
440 parm.DbType =
DbType.Int32;
441 parm.ParameterName =
paramNames[j];
442 parm.Value = (
int)paramList[j];
443 coll.Add(parm);
444 break;
445
446 case (
"System.Boolean"):
447 parm.DbType =
DbType.Boolean;
448 parm.ParameterName =
paramNames[j];
449 parm.Value = (
bool)paramList[j];
450 coll.Add(parm);
451 break;
452
453 case (
"System.DateTime"):
454 parm.DbType =
DbType.DateTime;
455 parm.ParameterName =
paramNames[j];
456 parm.Value =
Convert.ToDateTime(paramList[j]);
457 coll.Add(parm);
458 break;
459
460 case (
"System.Double"):
461 parm.DbType =
DbType.Double;
462 parm.ParameterName =
paramNames[j];
463 parm.Value =
Convert.ToDouble(paramList[j]);
464 coll.Add(parm);
465 break;
466
467 case (
"System.Decimal"):
468 parm.DbType =
DbType.Decimal;
469 parm.ParameterName =
paramNames[j];
470 parm.Value =
Convert.ToDecimal(paramList[j]);
471 break;
472
473 case (
"System.Guid"):
474 parm.DbType =
DbType.Guid;
475 parm.ParameterName =
paramNames[j];
476 parm.Value =
(System.Guid)(paramList[j]);
477 break;
478
479 case (
"System.Object"):
480
481 parm.DbType =
DbType.Object;
482 parm.ParameterName =
paramNames[j];
483 parm.Value =
paramList[j];
484 coll.Add(parm);
485 break;
486
487 default:
488 throw new SystemException(
"Value is of unknown data type");
489
490 }
// end switch
491
492 j++
;
493 }
494 return coll;
495 }
496
497 /// <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>
503 public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)
504 {
505 int retVal =
0;
506
507 // If the row has values, the store procedure parameters must be initialized
508 if (dataRow !=
null && dataRow.ItemArray.Length >
0)
509 {
510 // Set the parameters values
511 AssignParameterValues(command.Parameters, dataRow);
512
513 retVal =
ExecuteNonQuery(command);
514 }
515 else
516 {
517 retVal =
ExecuteNonQuery(command);
518 }
519
520 return retVal;
521 }
522
523 /// <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>
529 protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow)
530 {
531 if (commandParameters ==
null || dataRow ==
null)
532 {
533 // Do nothing if we get no data
534 return;
535 }
536
537 DataColumnCollection columns =
dataRow.Table.Columns;
538
539 int i =
0;
540 // Set the parameters values
541 foreach (IDataParameter commandParameter
in commandParameters)
542 {
543 // Check the parameter name
544 if (commandParameter.ParameterName ==
null ||
545 commandParameter.ParameterName.Length <=
1)
546 throw new 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));
549
550 if (columns.Contains(commandParameter.ParameterName))
551 commandParameter.Value =
dataRow[commandParameter.ParameterName];
552 else if (columns.Contains(commandParameter.ParameterName.Substring(
1)))
553 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(
1)];
554
555 i++
;
556 }
557 }
558
559 /// <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>
565 protected void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow)
566 {
567 if ((commandParameters ==
null) || (dataRow ==
null))
568 {
569 // Do nothing if we get no data
570 return;
571 }
572
573 DataColumnCollection columns =
dataRow.Table.Columns;
574
575 int i =
0;
576 // Set the parameters values
577 foreach (IDataParameter commandParameter
in commandParameters)
578 {
579 // Check the parameter name
580 if (commandParameter.ParameterName ==
null ||
581 commandParameter.ParameterName.Length <=
1)
582 throw new 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));
585
586 if (columns.Contains(commandParameter.ParameterName))
587 commandParameter.Value =
dataRow[commandParameter.ParameterName];
588 else if (columns.Contains(commandParameter.ParameterName.Substring(
1)))
589 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(
1)];
590
591 i++
;
592 }
593 }
594
595 /// <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>
601 protected void AssignParameterValues(IDataParameter[] commandParameters,
params object[] parameterValues)
602 {
603 if ((commandParameters ==
null) || (parameterValues ==
null))
604 {
605 // Do nothing if we get no data
606 return;
607 }
608
609 // We must have the same number of values as we pave parameters to put them in
610 if (commandParameters.Length !=
parameterValues.Length)
611 {
612 throw new ArgumentException(
"Parameter count does not match Parameter Value count.");
613 }
614
615 // Iterate through the IDataParameters, assigning the values from the corresponding position in the
616 // value array
617 for (
int i =
0, j = commandParameters.Length, k =
0; i < j; i++
)
618 {
619 if (commandParameters[i].Direction !=
ParameterDirection.ReturnValue)
620 {
621 // If the current array value derives from IDataParameter, then assign its Value property
622 if (parameterValues[k]
is IDataParameter)
623 {
624 IDataParameter paramInstance;
625 paramInstance =
(IDataParameter)parameterValues[k];
626 if (paramInstance.Direction ==
ParameterDirection.ReturnValue)
627 {
628 paramInstance = (IDataParameter)parameterValues[++
k];
629 }
630 if (paramInstance.Value ==
null)
631 {
632 commandParameters[i].Value =
DBNull.Value;
633 }
634 else
635 {
636 commandParameters[i].Value =
paramInstance.Value;
637 }
638 }
639 else if (parameterValues[k] ==
null)
640 {
641 commandParameters[i].Value =
DBNull.Value;
642 }
643 else
644 {
645 commandParameters[i].Value =
parameterValues[k];
646 }
647 k++
;
648 }
649 }
650 }
651 }
View Code
其他资料：
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.htsjk.com/SQLite/10456.html
www.htsjk.Com
true
http://www.htsjk.com/SQLite/10456.html
NewsArticle
SQLite之C#连接SQLite， SQLite是一个开源、免费的小型的Embeddable RDBMS(关系型数据库)，用C实现，内存占用较小，支持绝大数的SQL92标准，现在已变得越来越流行，它的体积很小，被广泛应用...
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。