Asp.net访问SQLite
SQLite作为一个轻量级的数据库引擎，被给予了很高的评价，由其将它与MS Access做对比。
在.Net中使用SQLite，一般都推荐使用System.Data.SQLite。
因为符合ADO.NET的规范，所以使用方式，基本和 SqlClient, OleDb等原生的一致。
最主要的还是AIR是原生支持SQLite的。
为了方便使用，写了一个.Net访问SQLite的类。
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SQLite;
using System.Configuration;
namespace SqliteSample
{
public class DBHelperSQLite
{
public static string connectionString = "Data Source = "+
System.AppDomain.CurrentDomain.BaseDirectory + ConfigurationManager.AppSettings["ConnectionString"];
public DBHelperSQLite()
{
}
/// <summary>
/// 判断是否存在某表的某个字段
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>是否存在</returns>
public static bool Exists(string SQLString)
{
object obj = DBHelperSQLite.GetSingle(SQLString);
int result;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
result = 0;
}
else
{
result = int.Parse(obj.ToString());
}
if (result == 0)
{
return false;
}
else
{
return true;
}
}
/// <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 (SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
public static int ExecuteSqlByTime(string SQLString, int Time)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString,connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Time;
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch(SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
/// <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 (SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
public static object GetSingle(string SQLString,int Times)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SQLiteDataReader ExecuteReader(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using(SQLiteDataAdapter da = new SQLiteDataAdapter(SQLString,connection))
{
DataSet ds = new DataSet();
try
{
connection.Open();
da.Fill(ds,"ds");
return ds;
}
catch(SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
public static DataSet Query(string SQLSting, int StartIndex, int PageSize)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteDataAdapter da = new SQLiteDataAdapter(SQLSting, connection))
{
DataSet ds = new DataSet();
try
{
connection.Open();
da.Fill(ds, StartIndex, PageSize, "ds");
return ds;
}
catch (SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
public static DataSet Query(string SQLString,int Times)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteDataAdapter da = new SQLiteDataAdapter(SQLString, connection))
{
DataSet ds = new DataSet();
try
{
connection.Open();
da.SelectCommand.CommandTimeout = Times;
da.Fill(ds, "ds");
return ds;
}
catch (SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
}
}
