SQlite数据库

[csharp] view plain copy

print ?

1. 首先：添加配置<add key="SQLString" value="~\demo.db"/>   

首先：添加配置<add key="SQLString" value="~\demo.db"/> 

[csharp] view plain copy

print ?

1.    

[csharp] view plain copy

print ?

1. /************************************** 
2. * 作用：SQLLite Server操作实现 
3. **************************************/  
4.   
5. using System;  
6. using System.Collections;  
7. using System.Collections.Specialized;  
8. using System.Data;  
9. using System.Data.SQLite;//这个可以去网上下载  
10. using System.Configuration;  
11.   
12. public class SQLiteHelper  
13. {  
14.     //数据库连接字符串(web.config来配置)，可以动态更改SQLString支持多数据库.          
15.     public static string connectionString = "Data Source=" + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["SQLString"]);  
16.   
17.     public SQLiteHelper() { }  
18.  
19.     #region 公用方法  
20.   
21.     public static int GetMaxID(string FieldName, string TableName)  
22.     {  
23.         string strsql = "select max(" + FieldName + ")+1 from " + TableName;  
24.         object obj = GetSingle(strsql);  
25.         if (obj == null)  
26.         {  
27.             return 1;  
28.         }  
29.         else  
30.         {  
31.             return int.Parse(obj.ToString());  
32.         }  
33.     }  
34.   
35.     public static bool Exists(string strSql)  
36.     {  
37.         object obj = GetSingle(strSql);  
38.         int cmdresult;  
39.         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
40.         {  
41.             cmdresult = 0;  
42.         }  
43.         else  
44.         {  
45.             cmdresult = int.Parse(obj.ToString());  
46.         }  
47.         if (cmdresult == 0)  
48.         {  
49.             return false;  
50.         }  
51.         else  
52.         {  
53.             return true;  
54.         }  
55.     }  
56.   
57.     public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)  
58.     {  
59.         object obj = GetSingle(strSql, cmdParms);  
60.         int cmdresult;  
61.         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
62.         {  
63.             cmdresult = 0;  
64.         }  
65.         else  
66.         {  
67.             cmdresult = int.Parse(obj.ToString());  
68.         }  
69.         if (cmdresult == 0)  
70.         {  
71.             return false;  
72.         }  
73.         else  
74.         {  
75.             return true;  
76.         }  
77.     }  
78.  
79.     #endregion  
80.  
81.     #region  执行简单SQL语句  
82.   
83.     /// <summary>  
84.     /// 执行SQL语句，返回影响的记录数  
85.     /// </summary>  
86.     /// <param name="SQLString">SQL语句</param>  
87.     /// <returns>影响的记录数</returns>  
88.     public static int ExecuteSql(string SQLString)  
89.     {  
90.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
91.         {  
92.             using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))  
93.             {  
94.                 try  
95.                 {  
96.                     connection.Open();  
97.                     int rows = cmd.ExecuteNonQuery();  
98.                     return rows;  
99.                 }  
100.                 catch (System.Data.SQLite.SQLiteException E)  
101.                 {  
102.                     connection.Close();  
103.                     throw new Exception(E.Message);  
104.                 }  
105.             }  
106.         }  
107.     }  
108.   
109.     /// <summary>  
110.     /// 执行SQL语句，设置命令的执行等待时间  
111.     /// </summary>  
112.     /// <param name="SQLString"></param>  
113.     /// <param name="Times"></param>  
114.     /// <returns></returns>  
115.     public static int ExecuteSqlByTime(string SQLString, int Times)  
116.     {  
117.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
118.         {  
119.             using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))  
120.             {  
121.                 try  
122.                 {  
123.                     connection.Open();  
124.                     cmd.CommandTimeout = Times;  
125.                     int rows = cmd.ExecuteNonQuery();  
126.                     return rows;  
127.                 }  
128.                 catch (System.Data.SQLite.SQLiteException E)  
129.                 {  
130.                     connection.Close();  
131.                     throw new Exception(E.Message);  
132.                 }  
133.             }  
134.         }  
135.     }  
136.   
137.     /// <summary>  
138.     /// 执行多条SQL语句，实现数据库事务。  
139.     /// </summary>  
140.     /// <param name="SQLStringList">多条SQL语句</param>          
141.     public static void ExecuteSqlTran(ArrayList SQLStringList)  
142.     {  
143.         using (SQLiteConnection conn = new SQLiteConnection(connectionString))  
144.         {  
145.             conn.Open();  
146.             SQLiteCommand cmd = new SQLiteCommand();  
147.             cmd.Connection = conn;  
148.             SQLiteTransaction tx = conn.BeginTransaction();  
149.             cmd.Transaction = tx;  
150.             try  
151.             {  
152.                 for (int n = 0; n < SQLStringList.Count; n++)  
153.                 {  
154.                     string strsql = SQLStringList[n].ToString();  
155.                     if (strsql.Trim().Length > 1)  
156.                     {  
157.                         cmd.CommandText = strsql;  
158.                         cmd.ExecuteNonQuery();  
159.                     }  
160.                 }  
161.                 tx.Commit();  
162.             }  
163.             catch (System.Data.SQLite.SQLiteException E)  
164.             {  
165.                 tx.Rollback();  
166.                 throw new Exception(E.Message);  
167.             }  
168.         }  
169.     }  
170.   
171.     /// <summary>  
172.     /// 执行带一个存储过程参数的的SQL语句。  
173.     /// </summary>  
174.     /// <param name="SQLString">SQL语句</param>  
175.     /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>  
176.     /// <returns>影响的记录数</returns>  
177.     public static int ExecuteSql(string SQLString, string content)  
178.     {  
179.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
180.         {  
181.             SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);  
182.             SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);  
183.             myParameter.Value = content;  
184.             cmd.Parameters.Add(myParameter);  
185.             try  
186.             {  
187.                 connection.Open();  
188.                 int rows = cmd.ExecuteNonQuery();  
189.                 return rows;  
190.             }  
191.             catch (System.Data.SQLite.SQLiteException E)  
192.             {  
193.                 throw new Exception(E.Message);  
194.             }  
195.             finally  
196.             {  
197.                 cmd.Dispose();  
198.                 connection.Close();  
199.             }  
200.         }  
201.     }  
202.   
203.     /// <summary>  
204.     /// 执行带一个存储过程参数的的SQL语句。  
205.     /// </summary>  
206.     /// <param name="SQLString">SQL语句</param>  
207.     /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>  
208.     /// <returns>影响的记录数</returns>  
209.     public static object ExecuteSqlGet(string SQLString, string content)  
210.     {  
211.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
212.         {  
213.             SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);  
214.             SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);  
215.             myParameter.Value = content;  
216.             cmd.Parameters.Add(myParameter);  
217.             try  
218.             {  
219.                 connection.Open();  
220.                 object obj = cmd.ExecuteScalar();  
221.                 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
222.                 {  
223.                     return null;  
224.                 }  
225.                 else  
226.                 {  
227.                     return obj;  
228.                 }  
229.             }  
230.             catch (System.Data.SQLite.SQLiteException E)  
231.             {  
232.                 throw new Exception(E.Message);  
233.             }  
234.             finally  
235.             {  
236.                 cmd.Dispose();  
237.                 connection.Close();  
238.             }  
239.         }  
240.     }  
241.   
242.     /// <summary>  
243.     /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)  
244.     /// </summary>  
245.     /// <param name="strSQL">SQL语句</param>  
246.     /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>  
247.     /// <returns>影响的记录数</returns>  
248.     public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)  
249.     {  
250.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
251.         {  
252.             SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);  
253.             SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);  
254.             myParameter.Value = fs;  
255.             cmd.Parameters.Add(myParameter);  
256.             try  
257.             {  
258.                 connection.Open();  
259.                 int rows = cmd.ExecuteNonQuery();  
260.                 return rows;  
261.             }  
262.             catch (System.Data.SQLite.SQLiteException E)  
263.             {  
264.                 throw new Exception(E.Message);  
265.             }  
266.             finally  
267.             {  
268.                 cmd.Dispose();  
269.                 connection.Close();  
270.             }  
271.         }  
272.     }  
273.   
274.     /// <summary>  
275.     /// 执行一条计算查询结果语句，返回查询结果（object）。  
276.     /// </summary>  
277.     /// <param name="SQLString">计算查询结果语句</param>  
278.     /// <returns>查询结果（object）</returns>  
279.     public static object GetSingle(string SQLString)  
280.     {  
281.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
282.         {  
283.             using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))  
284.             {  
285.                 try  
286.                 {  
287.                     connection.Open();  
288.                     object obj = cmd.ExecuteScalar();  
289.                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
290.                     {  
291.                         return null;  
292.                     }  
293.                     else  
294.                     {  
295.                         return obj;  
296.                     }  
297.                 }  
298.                 catch (System.Data.SQLite.SQLiteException e)  
299.                 {  
300.                     connection.Close();  
301.                     throw new Exception(e.Message);  
302.                 }  
303.             }  
304.         }  
305.     }  
306.   
307.     /// <summary>  
308.     /// 执行查询语句，返回SQLiteDataReader(使用该方法切记要手工关闭SQLiteDataReader和连接)  
309.     /// </summary>  
310.     /// <param name="strSQL">查询语句</param>  
311.     /// <returns>SQLiteDataReader</returns>  
312.     public static SQLiteDataReader ExecuteReader(string strSQL)  
313.     {  
314.         SQLiteConnection connection = new SQLiteConnection(connectionString);  
315.         SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);  
316.         try  
317.         {  
318.             connection.Open();  
319.             SQLiteDataReader myReader = cmd.ExecuteReader();  
320.             return myReader;  
321.         }  
322.         catch (System.Data.SQLite.SQLiteException e)  
323.         {  
324.             throw new Exception(e.Message);  
325.         }  
326.         //finally //不能在此关闭，否则，返回的对象将无法使用  
327.         //{  
328.         //    cmd.Dispose();  
329.         //    connection.Close();  
330.         //}      
331.     }  
332.   
333.     /// <summary>  
334.     /// 执行查询语句，返回DataSet  
335.     /// </summary>  
336.     /// <param name="SQLString">查询语句</param>  
337.     /// <returns>DataSet</returns>  
338.     public static DataSet Query(string SQLString)  
339.     {  
340.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
341.         {  
342.             DataSet ds = new DataSet();  
343.             try  
344.             {  
345.                 connection.Open();  
346.                 SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);  
347.                 command.Fill(ds, "ds");  
348.             }  
349.             catch (System.Data.SQLite.SQLiteException ex)  
350.             {  
351.                 throw new Exception(ex.Message);  
352.             }  
353.             return ds;  
354.         }  
355.     }  
356.   
357.     public static DataSet Query(string SQLString, string TableName)  
358.     {  
359.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
360.         {  
361.             DataSet ds = new DataSet();  
362.             try  
363.             {  
364.                 connection.Open();  
365.                 SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);  
366.                 command.Fill(ds, TableName);  
367.             }  
368.             catch (System.Data.SQLite.SQLiteException ex)  
369.             {  
370.                 throw new Exception(ex.Message);  
371.             }  
372.             return ds;  
373.         }  
374.     }  
375.   
376.     /// <summary>  
377.     /// 执行查询语句，返回DataSet,设置命令的执行等待时间  
378.     /// </summary>  
379.     /// <param name="SQLString"></param>  
380.     /// <param name="Times"></param>  
381.     /// <returns></returns>  
382.     public static DataSet Query(string SQLString, int Times)  
383.     {  
384.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
385.         {  
386.             DataSet ds = new DataSet();  
387.             try  
388.             {  
389.                 connection.Open();  
390.                 SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);  
391.                 command.SelectCommand.CommandTimeout = Times;  
392.                 command.Fill(ds, "ds");  
393.             }  
394.             catch (System.Data.SQLite.SQLiteException ex)  
395.             {  
396.                 throw new Exception(ex.Message);  
397.             }  
398.             return ds;  
399.         }  
400.     }  
401.  
402.     #endregion  
403.  
404.     #region 执行带参数的SQL语句  
405.   
406.     /// <summary>  
407.     /// 执行SQL语句，返回影响的记录数  
408.     /// </summary>  
409.     /// <param name="SQLString">SQL语句</param>  
410.     /// <returns>影响的记录数</returns>  
411.     public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)  
412.     {  
413.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
414.         {  
415.             using (SQLiteCommand cmd = new SQLiteCommand())  
416.             {  
417.                 try  
418.                 {  
419.                     PrepareCommand(cmd, connection, null, SQLString, cmdParms);  
420.                     int rows = cmd.ExecuteNonQuery();  
421.                     cmd.Parameters.Clear();  
422.                     return rows;  
423.                 }  
424.                 catch (System.Data.SQLite.SQLiteException E)  
425.                 {  
426.                     throw new Exception(E.Message);  
427.                 }  
428.             }  
429.         }  
430.     }  
431.   
432.     /// <summary>  
433.     /// 执行多条SQL语句，实现数据库事务。  
434.     /// </summary>  
435.     /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>  
436.     public static void ExecuteSqlTran(Hashtable SQLStringList)  
437.     {  
438.         using (SQLiteConnection conn = new SQLiteConnection(connectionString))  
439.         {  
440.             conn.Open();  
441.             using (SQLiteTransaction trans = conn.BeginTransaction())  
442.             {  
443.                 SQLiteCommand cmd = new SQLiteCommand();  
444.                 try  
445.                 {  
446.                     //循环  
447.                     foreach (DictionaryEntry myDE in SQLStringList)  
448.                     {  
449.                         string cmdText = myDE.Key.ToString();  
450.                         SQLiteParameter[] cmdParms = (SQLiteParameter[]) myDE.Value;  
451.                         PrepareCommand(cmd, conn, trans, cmdText, cmdParms);  
452.                         int val = cmd.ExecuteNonQuery();  
453.                         cmd.Parameters.Clear();  
454.   
455.                         trans.Commit();  
456.                     }  
457.                 }  
458.                 catch  
459.                 {  
460.                     trans.Rollback();  
461.                     throw;  
462.                 }  
463.             }  
464.         }  
465.     }  
466.   
467.     /// <summary>  
468.     /// 执行一条计算查询结果语句，返回查询结果（object）。  
469.     /// </summary>  
470.     /// <param name="SQLString">计算查询结果语句</param>  
471.     /// <returns>查询结果（object）</returns>  
472.     public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)  
473.     {  
474.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
475.         {  
476.             using (SQLiteCommand cmd = new SQLiteCommand())  
477.             {  
478.                 try  
479.                 {  
480.                     PrepareCommand(cmd, connection, null, SQLString, cmdParms);  
481.                     object obj = cmd.ExecuteScalar();  
482.                     cmd.Parameters.Clear();  
483.                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
484.                     {  
485.                         return null;  
486.                     }  
487.                     else  
488.                     {  
489.                         return obj;  
490.                     }  
491.                 }  
492.                 catch (System.Data.SQLite.SQLiteException e)  
493.                 {  
494.                     throw new Exception(e.Message);  
495.                 }  
496.             }  
497.         }  
498.     }  
499.   
500.     /// <summary>  
501.     /// 执行查询语句，返回SQLiteDataReader (使用该方法切记要手工关闭SQLiteDataReader和连接)  
502.     /// </summary>  
503.     /// <param name="strSQL">查询语句</param>  
504.     /// <returns>SQLiteDataReader</returns>  
505.     public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)  
506.     {  
507.         SQLiteConnection connection = new SQLiteConnection(connectionString);  
508.         SQLiteCommand cmd = new SQLiteCommand();  
509.         try  
510.         {  
511.             PrepareCommand(cmd, connection, null, SQLString, cmdParms);  
512.             SQLiteDataReader myReader = cmd.ExecuteReader();  
513.             cmd.Parameters.Clear();  
514.             return myReader;  
515.         }  
516.         catch (System.Data.SQLite.SQLiteException e)  
517.         {  
518.             throw new Exception(e.Message);  
519.         }  
520.         //finally //不能在此关闭，否则，返回的对象将无法使用  
521.         //{  
522.         //    cmd.Dispose();  
523.         //    connection.Close();  
524.         //}      
525.   
526.     }  
527.   
528.     /// <summary>  
529.     /// 执行查询语句，返回DataSet  
530.     /// </summary>  
531.     /// <param name="SQLString">查询语句</param>  
532.     /// <returns>DataSet</returns>  
533.     public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)  
534.     {  
535.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
536.         {  
537.             SQLiteCommand cmd = new SQLiteCommand();  
538.             PrepareCommand(cmd, connection, null, SQLString, cmdParms);  
539.             using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))  
540.             {  
541.                 DataSet ds = new DataSet();  
542.                 try  
543.                 {  
544.                     da.Fill(ds, "ds");  
545.                     cmd.Parameters.Clear();  
546.                 }  
547.                 catch (System.Data.SQLite.SQLiteException ex)  
548.                 {  
549.                     throw new Exception(ex.Message);  
550.                 }  
551.                 return ds;  
552.             }  
553.         }  
554.     }  
555.   
556.     public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)  
557.     {  
558.         if (conn.State != ConnectionState.Open)  
559.             conn.Open();  
560.         cmd.Connection = conn;  
561.         cmd.CommandText = cmdText;  
562.         if (trans != null)  
563.             cmd.Transaction = trans;  
564.         cmd.CommandType = CommandType.Text;//cmdType;  
565.         if (cmdParms != null)  
566.         {  
567.   
568.   
569.             foreach (SQLiteParameter parameter in cmdParms)  
570.             {  
571.                 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&  
572.                     (parameter.Value == null))  
573.                 {  
574.                     parameter.Value = DBNull.Value;  
575.                 }  
576.                 cmd.Parameters.Add(parameter);  
577.             }  
578.         }  
579.     }  
580.  
581.     #endregion  
582.  
583.     #region 参数转换  
584.     /// <summary>  
585.     /// 放回一个SQLiteParameter  
586.     /// </summary>  
587.     /// <param name="name">参数名字</param>  
588.     /// <param name="type">参数类型</param>  
589.     /// <param name="size">参数大小</param>  
590.     /// <param name="value">参数值</param>  
591.     /// <returns>SQLiteParameter的值</returns>  
592.     public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, int size, object value)  
593.     {  
594.         SQLiteParameter parm = new SQLiteParameter(name, type, size);  
595.         parm.Value = value;  
596.         return parm;  
597.     }  
598.   
599.     public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, object value)  
600.     {  
601.         SQLiteParameter parm = new SQLiteParameter(name, type);  
602.         parm.Value = value;  
603.         return parm;  
604.     }  
605.  
606.     #endregion  
607. }  

/**************************************
* 作用：SQLLite Server操作实现
**************************************/

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SQLite;//这个可以去网上下载
using System.Configuration;

public class SQLiteHelper
{
    //数据库连接字符串(web.config来配置)，可以动态更改SQLString支持多数据库.        
    public static string connectionString = "Data Source=" + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["SQLString"]);

    public SQLiteHelper() { }

    #region 公用方法

    public static int GetMaxID(string FieldName, string TableName)
    {
        string strsql = "select max(" + FieldName + ")+1 from " + TableName;
        object obj = GetSingle(strsql);
        if (obj == null)
        {
            return 1;
        }
        else
        {
            return int.Parse(obj.ToString());
        }
    }

    public static bool Exists(string strSql)
    {
        object obj = GetSingle(strSql);
        int cmdresult;
        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString());
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
    {
        object obj = GetSingle(strSql, cmdParms);
        int cmdresult;
        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        {
            cmdresult = 0;
        }
        else
        {
            cmdresult = int.Parse(obj.ToString());
        }
        if (cmdresult == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    #endregion

    #region  执行简单SQL语句

    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string SQLString)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    connection.Close();
                    throw new Exception(E.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行SQL语句，设置命令的执行等待时间
    /// </summary>
    /// <param name="SQLString"></param>
    /// <param name="Times"></param>
    /// <returns></returns>
    public static int ExecuteSqlByTime(string SQLString, int Times)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    cmd.CommandTimeout = Times;
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    connection.Close();
                    throw new Exception(E.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>        
    public static void ExecuteSqlTran(ArrayList SQLStringList)
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = conn;
            SQLiteTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }
    }

    /// <summary>
    /// 执行带一个存储过程参数的的SQL语句。
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string SQLString, string content)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
            SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
            myParameter.Value = content;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                throw new Exception(E.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 执行带一个存储过程参数的的SQL语句。
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
    /// <returns>影响的记录数</returns>
    public static object ExecuteSqlGet(string SQLString, string content)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
            SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
            myParameter.Value = content;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                object obj = cmd.ExecuteScalar();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                throw new Exception(E.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
    /// </summary>
    /// <param name="strSQL">SQL语句</param>
    /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
            SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);
            myParameter.Value = fs;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (System.Data.SQLite.SQLiteException E)
            {
                throw new Exception(E.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string SQLString)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SQLite.SQLiteException e)
                {
                    connection.Close();
                    throw new Exception(e.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行查询语句，返回SQLiteDataReader(使用该方法切记要手工关闭SQLiteDataReader和连接)
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>SQLiteDataReader</returns>
    public static SQLiteDataReader ExecuteReader(string strSQL)
    {
        SQLiteConnection connection = new SQLiteConnection(connectionString);
        SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
        try
        {
            connection.Open();
            SQLiteDataReader myReader = cmd.ExecuteReader();
            return myReader;
        }
        catch (System.Data.SQLite.SQLiteException e)
        {
            throw new Exception(e.Message);
        }
        //finally //不能在此关闭，否则，返回的对象将无法使用
        //{
        //    cmd.Dispose();
        //    connection.Close();
        //}    
    }

    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string SQLString)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                command.Fill(ds, "ds");
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    public static DataSet Query(string SQLString, string TableName)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                command.Fill(ds, TableName);
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    /// <summary>
    /// 执行查询语句，返回DataSet,设置命令的执行等待时间
    /// </summary>
    /// <param name="SQLString"></param>
    /// <param name="Times"></param>
    /// <returns></returns>
    public static DataSet Query(string SQLString, int Times)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
                command.SelectCommand.CommandTimeout = Times;
                command.Fill(ds, "ds");
            }
            catch (System.Data.SQLite.SQLiteException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    #endregion

    #region 执行带参数的SQL语句

    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    throw new Exception(E.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
    public static void ExecuteSqlTran(Hashtable SQLStringList)
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand();
                try
                {
                    //循环
                    foreach (DictionaryEntry myDE in SQLStringList)
                    {
                        string cmdText = myDE.Key.ToString();
                        SQLiteParameter[] cmdParms = (SQLiteParameter[]) myDE.Value;
                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();

                        trans.Commit();
                    }
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SQLite.SQLiteException e)
                {
                    throw new Exception(e.Message);
                }
            }
        }
    }

    /// <summary>
    /// 执行查询语句，返回SQLiteDataReader (使用该方法切记要手工关闭SQLiteDataReader和连接)
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>SQLiteDataReader</returns>
    public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
    {
        SQLiteConnection connection = new SQLiteConnection(connectionString);
        SQLiteCommand cmd = new SQLiteCommand();
        try
        {
            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
            SQLiteDataReader myReader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return myReader;
        }
        catch (System.Data.SQLite.SQLiteException e)
        {
            throw new Exception(e.Message);
        }
        //finally //不能在此关闭，否则，返回的对象将无法使用
        //{
        //    cmd.Dispose();
        //    connection.Close();
        //}    

    }

    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
            using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SQLite.SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
    }

    public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        if (trans != null)
            cmd.Transaction = trans;
        cmd.CommandType = CommandType.Text;//cmdType;
        if (cmdParms != null)
        {


            foreach (SQLiteParameter parameter in cmdParms)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }
    }

    #endregion

    #region 参数转换
    /// <summary>
    /// 放回一个SQLiteParameter
    /// </summary>
    /// <param name="name">参数名字</param>
    /// <param name="type">参数类型</param>
    /// <param name="size">参数大小</param>
    /// <param name="value">参数值</param>
    /// <returns>SQLiteParameter的值</returns>
    public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, int size, object value)
    {
        SQLiteParameter parm = new SQLiteParameter(name, type, size);
        parm.Value = value;
        return parm;
    }

    public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, object value)
    {
        SQLiteParameter parm = new SQLiteParameter(name, type);
        parm.Value = value;
        return parm;
    }

    #endregion
}

 调用方法

[csharp] view plain copy

print ?

1. /// <summary>  
2. /// 判断用户是否存在  
3. /// </summary>  
4. /// <param name="name">用户名称</param>  
5. /// <returns></returns>  
6. public bool UserExists(string name)  
7. {  
8.     StringBuilder strSql = new StringBuilder();  
9.     strSql.Append("select count(*) n from Users");  
10.     strSql.Append(" where UserName=@UserName ");  
11.     SQLiteParameter[] parameters = {  
12.                 SQLiteHelper.MakeSQLiteParameter("@UserName", DbType.String,30,name)};  
13.   
14.     return SQLiteHelper.Exists(strSql.ToString(), parameters);  
15. }  
16.   
17. /// <summary>  
18. /// 增加一个用户  
19. /// </summary>  
20. /// <param name="name">用户名</param>  
21. /// <param name="pwd">密码</param>  
22. /// <returns></returns>  
23. public int CreateUser(string name, string pwd)  
24. {  
25.     int ret = 0;  
26.     if (!UserExists(name))  
27.     {  
28.         StringBuilder strSql = new StringBuilder();  
29.         strSql.Append("insert into t_UserGroup(");  
30.         strSql.Append("UserName,Password)");  
31.         strSql.Append(" values (");  
32.         strSql.Append("@UserName,@Password)");  
33.         SQLiteParameter[] parameters = {  
34.                 SQLiteHelper.MakeSQLiteParameter("@UserName", DbType.String,30,name),  
35.                 SQLiteHelper.MakeSQLiteParameter("@Password", DbType.String,128,pwd)  
36.                 };  
37.   
38.         if (SQLiteHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)  
39.         {  
40.             ret = 1;  
41.         }  
42.     }  
43.     else  
44.     {  
45.         ret = 2;  
46.     }  
47.     return ret;  
48. }  
49.   
50. /// <summary>  
51. /// 更新一条数据  
52. /// </summary>  
53. /// <param name="model">用户分组实体类</param>  
54. /// <returns></returns>  
55. public bool UpdateUser(int id, string name, string pwd)  
56. {  
57.     StringBuilder strSql = new StringBuilder();  
58.     strSql.Append("update Users set ");  
59.     strSql.Append("UserName=@UserName,");  
60.     strSql.Append("Password=@Password");  
61.     strSql.Append(" where UserID=@UserID");  
62.     SQLiteParameter[] parameters = {  
63.                 SQLiteHelper.MakeSQLiteParameter("@UserID", DbType.Int32,11,id),  
64.                 SQLiteHelper.MakeSQLiteParameter("@UserName", DbType.String,30,name),  
65.                 SQLiteHelper.MakeSQLiteParameter("@Password", DbType.String,128,pwd)};  
66.   
67.     if (SQLiteHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)  
68.     {  
69.         return true;  
70.     }  
71.     else  
72.     {  
73.         return false;  
74.     }  
75. }  
76.   
77. /// <summary>  
78. /// 删除用户  
79. /// </summary>  
80. /// <param name="ID">用户ID</param>  
81. /// <returns></returns>  
82. public int DeleteUser(int id)  
83. {  
84.     int ret = 0;  
85.     string strSql3 = "delete from Users where UserID=@UserID";  
86.     SQLiteParameter[] parameters = {  
87.                 SQLiteHelper.MakeSQLiteParameter("@UserID", DbType.Int32,4,id)};  
88.   
89.     if (SQLiteHelper.ExecuteSql(strSql3, parameters) >= 1)  
90.     {  
91.         ret = 1;  
92.     }  
93.     else  
94.     {  
95.         ret = 0;  
96.     }  
97.   
98.     return ret;  
99. }  
100.   
101. /// <summary>  
102. /// 获得用户分组数据列表  
103. /// </summary>  
104. /// <param name="strWhere">Where条件</param>  
105. /// <returns></returns>  
106. public DataSet GetUserList(string strWhere)  
107. {  
108.     StringBuilder strSql = new StringBuilder();  
109.     strSql.Append("select * FROM Users ");  
110.   
111.     if (strWhere.Trim() != "")  
112.     {  
113.         strSql.Append(" where " + strWhere);  
114.     }  
115.     strSql.Append(" order by UserID desc");  
116.   
117.     return SQLiteHelper.Query(strSql.ToString());  
118. }  

/// <summary>
/// 判断用户是否存在
/// </summary>
/// <param name="name">用户名称</param>
/// <returns></returns>
public bool UserExists(string name)
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("select count(*) n from Users");
    strSql.Append(" where UserName=@UserName ");
    SQLiteParameter[] parameters = {
                SQLiteHelper.MakeSQLiteParameter("@UserName", DbType.String,30,name)};

    return SQLiteHelper.Exists(strSql.ToString(), parameters);
}

/// <summary>
/// 增加一个用户
/// </summary>
/// <param name="name">用户名</param>
/// <param name="pwd">密码</param>
/// <returns></returns>
public int CreateUser(string name, string pwd)
{
    int ret = 0;
    if (!UserExists(name))
    {
        StringBuilder strSql = new StringBuilder();
        strSql.Append("insert into t_UserGroup(");
        strSql.Append("UserName,Password)");
        strSql.Append(" values (");
        strSql.Append("@UserName,@Password)");
        SQLiteParameter[] parameters = {
                SQLiteHelper.MakeSQLiteParameter("@UserName", DbType.String,30,name),
                SQLiteHelper.MakeSQLiteParameter("@Password", DbType.String,128,pwd)
                };

        if (SQLiteHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)
        {
            ret = 1;
        }
    }
    else
    {
        ret = 2;
    }
    return ret;
}

/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="model">用户分组实体类</param>
/// <returns></returns>
public bool UpdateUser(int id, string name, string pwd)
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("update Users set ");
    strSql.Append("UserName=@UserName,");
    strSql.Append("Password=@Password");
    strSql.Append(" where UserID=@UserID");
    SQLiteParameter[] parameters = {
                SQLiteHelper.MakeSQLiteParameter("@UserID", DbType.Int32,11,id),
                SQLiteHelper.MakeSQLiteParameter("@UserName", DbType.String,30,name),
                SQLiteHelper.MakeSQLiteParameter("@Password", DbType.String,128,pwd)};

    if (SQLiteHelper.ExecuteSql(strSql.ToString(), parameters) >= 1)
    {
        return true;
    }
    else
    {
        return false;
    }
}

/// <summary>
/// 删除用户
/// </summary>
/// <param name="ID">用户ID</param>
/// <returns></returns>
public int DeleteUser(int id)
{
    int ret = 0;
    string strSql3 = "delete from Users where UserID=@UserID";
    SQLiteParameter[] parameters = {
                SQLiteHelper.MakeSQLiteParameter("@UserID", DbType.Int32,4,id)};

    if (SQLiteHelper.ExecuteSql(strSql3, parameters) >= 1)
    {
        ret = 1;
    }
    else
    {
        ret = 0;
    }

    return ret;
}

/// <summary>
/// 获得用户分组数据列表
/// </summary>
/// <param name="strWhere">Where条件</param>
/// <returns></returns>
public DataSet GetUserList(string strWhere)
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("select * FROM Users ");

    if (strWhere.Trim() != "")
    {
        strSql.Append(" where " + strWhere);
    }
    strSql.Append(" order by UserID desc");

    return SQLiteHelper.Query(strSql.ToString());
}

 

 

注意事项

1. @@IDENTITY LAST_INSERT_ROWID()
2. SELECT cn = COUNT(*) FROM ... SELECT COUNT(*) cn FROM ...
3. LIMIT startIndex,itemCn 这儿的startIndex是从0开始的，而ROW_NUMBER()是从1开始的
4. sqlite中没有SELECT TOP,用LIMIT即可
5. SQLite自增字段，如果在事务中插入数据失败，并不会占用增长后的id，而sql server中对应的id将无效
6. SQLite中没有GETDATE日期函数，在类中加入参数如下DbType.DateTime,DateTime.Now.ToString("s")
7. SQLite支持REPLACE INTO语法,sql server 2008中支持merge to