SQLite数据访问类(.NETCF)
最近我在使用 SQLite 数据库开发 Windows Mobile 项目，顺便把 .NET Compact Framework 版的 Data Access Application Block (SQL Server Compact) 转成 SQLite 版，觉得挺好用，跟大家分享！
代码
using System;
using System.Data.Common;
using System.Data;
namespace System.Data.SQLite
{
/// <summary>
/// This class provides the foundation for simplified access to a database. It contains a number
/// of helper methods that make working with the database easier.
/// </summary>
public class SQLiteDatabase : IDisposable
{
private string connectionString;
private SQLiteConnection databaseConnection;
private SQLiteDatabase() { }
/// <summary>
/// The constructor.
/// </summary>
/// <param name="connectionString">
/// The connection string that will be used to connect to the database.
/// </param>
public SQLiteDatabase(string connectionString)
{
Guard.ArgumentNotNullOrEmptyString(connectionString, "connectionString");
this.connectionString = connectionString;
CreateConnection();
}
/// <summary>
/// Closes the current connection. You should call this method when you are completely
/// done using this database instance. Methods will fail after you've disposed of this
/// instance.
/// </summary>
public void Dispose()
{
if (databaseConnection != null)
{
databaseConnection.Close(); // .NET documents recommend closing instead of disposing connection
databaseConnection = null;
}
}
/// <summary>
/// Creates a new, unopened connection instance for this database.
/// </summary>
/// <returns>
/// An unopened <see cref="SQLiteConnection"/> for this database.
/// </returns>
/// <seealso cref="SQLiteConnection"/>
private void CreateConnection()
{
if (databaseConnection == null)
{
databaseConnection = new SQLiteConnection();
databaseConnection.ConnectionString = ConnectionString;
}
}
/// <summary>
/// Gets the connection string for this instance.
/// </summary>
public string ConnectionString
{
get { return connectionString; }
}
/// <summary>
/// Builds a value parameter name for the current database by ensuring there is an '@' at the
/// start of the name.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <returns>A correctly formated parameter name, which starts with an '@'.</returns>
public string BuildParameterName(string name)
{
Guard.ArgumentNotNullOrEmptyString(name, "name");
if (name[0] != '@')
return "@" + name;
else
return name;
}
/// <summary>
/// Creates a new parameter and sets the name of the parameter.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <returns>
/// A new <see cref="SQLiteParameter"/> instance of the correct type for this database.</returns>
/// <remarks>
/// The database will automatically add the correct prefix, like "@" for SQLite, to the
/// parameter name. In other words, you can just supply the name without a prefix.
/// </remarks>
public SQLiteParameter CreateParameter(string name)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.ParameterName = BuildParameterName(name);
return parameter;
}
/// <summary>
/// Creates a new parameter and sets the name of the parameter.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="value">
/// The value you want assigned to thsi parameter. A null value will be converted to
/// a <see cref="DBNull"/> value in the parameter.
/// </param>
/// <returns>
/// A new <see cref="SQLiteParameter"/> instance of the correct type for this database.</returns>
/// <remarks>
/// The database will automatically add the correct prefix, like "@" for SQLite, to the
/// parameter name. In other words, you can just supply the name without a prefix.
/// </remarks>
public SQLiteParameter CreateParameter(string name, object value)
{
SQLiteParameter param = CreateParameter(name);
param.Value = (value == null) ? DBNull.Value : value;
return param;
}
/// <summary>
/// Creates a new parameter and sets the name of the parameter.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="type">The type of the parameter.</param>
/// <param name="size">The size of this parameter.</param>
/// <param name="value">
/// The value you want assigned to this parameter. A null value will be converted to
/// a <see cref="DBNull"/> value in the parameter.
/// </param>
/// <returns>
/// A new <see cref="SQLiteParameter"/> instance of the correct type for this database.</returns>
/// <remarks>
/// The database will automatically add the correct prefix, like "@" for SQLite, to the
/// parameter name. In other words, you can just supply the name without a prefix.
/// </remarks>
public SQLiteParameter CreateParameter(string name, DbType type, int size, object value)
{
SQLiteParameter param = CreateParameter(name);
param.DbType = type;
param.Size = size;
param.Value = (value == null) ? DBNull.Value : value;
return param;
}
/// <summary>
/// Executes an SQL query with an optional set of parameters.
/// </summary>
/// <param name="command">The command to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>The number of rows affected.</returns>
public int ExecuteNonQuery(SQLiteCommand command, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(command, "Command");
int result;
SQLiteConnection connection = GetConnection();
PrepareCommand(command, connection, parameters);
result = command.ExecuteNonQuery();
return result;
}
/// <summary>
/// Executes an SQL query with an optional set of parameters.
/// </summary>
/// <param name="sqlCommand">The SQL statement to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>The number of rows affected.</returns>
public int ExecuteNonQuery(string sqlCommand, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(sqlCommand, "sqlCommand");
using (SQLiteCommand command = new SQLiteCommand())
{
command.CommandText = sqlCommand;
return ExecuteNonQuery(command, parameters);
}
}
/// <summary>
/// Execute a command and return a <see cref="SQLiteDataReader"/> that contains the rows
/// returned.
/// </summary>
/// <param name="command">The command to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>A <see cref="SQLiteDataReader"/> that contains the rows returned by the query.</returns>
public SQLiteDataReader ExecuteReader(SQLiteCommand command, params SQLiteParameter[] parameters)
{
SQLiteDataReader result;
Guard.ArgumentNotNull(command, "command");
SQLiteConnection connection = GetConnection();
PrepareCommand(command, connection, parameters);
result = command.ExecuteReader();
return result;
}
/// <summary>
/// Execute a command and return a <see cref="SQLiteDataReader"/> that contains the rows
/// returned.
/// </summary>
/// <param name="sqlCommand">The SQL query to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>A <see cref="SQLiteDataReader"/> that contains the rows returned by the query.</returns>
public SQLiteDataReader ExecuteReader(string sqlCommand, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNullOrEmptyString(sqlCommand, "sqlCommand");
using (SQLiteCommand command = new SQLiteCommand())
{
command.CommandText = sqlCommand;
return ExecuteReader(command, parameters);
}
}
/// <summary>
/// <para>
/// Executes the <paramref name="command"/> and returns the first column of the first
/// row in the result set returned by the query. Extra columns or rows are ignored.
/// </para>
/// </summary>
/// <param name="command">
/// <para>
/// The command that contains the query to execute.
/// </para>
/// </param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>
/// <para>
/// The first column of the first row in the result set.
/// </para>
/// </returns>
/// <seealso cref="ISQLiteCommand.ExecuteScalar"/>
public object ExecuteScalar(SQLiteCommand command, params SQLiteParameter[] parameters)
{
object result;
Guard.ArgumentNotNull(command, "command");
SQLiteConnection connection = GetConnection();
PrepareCommand(command, connection, parameters);
result = command.ExecuteScalar();
return result;
}
/// <summary>
/// Executes the <paramref name="command"/> and returns the first column of the first
/// row in the result set returned by the query. Extra columns or rows are ignored.
/// </summary>
/// <param name="sqlCommand">The SQL statement to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>
/// <para>
/// The first column of the first row in the result set.
/// </para>
/// </returns>
/// <seealso cref="ISQLiteCommand.ExecuteScalar"/>
public object ExecuteScalar(string sqlCommand, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(sqlCommand, "sqlCommand");
using (SQLiteCommand command = new SQLiteCommand())
{
command.CommandText = sqlCommand;
return ExecuteScalar(command, parameters);
}
}
/// <summary>
/// <para>Returns the shared connection, and opens it the first startTime.</para>
/// </summary>
/// <returns>The opened connection.</returns>
public SQLiteConnection GetConnection()
{
if (databaseConnection.State != ConnectionState.Open)
databaseConnection.Open();
return databaseConnection;
}
/// <summary>
/// Closes the shared connection.
/// </summary>
public void CloseConnection()
{
if (databaseConnection.State != ConnectionState.Closed)
{
databaseConnection.Close();
}
}
/// <summary>
/// <para>
/// Assigns a <paramref name="connection"/> to the <paramref name="command"/> and
/// discovers parameters if needed.
/// </para>
/// </summary>
/// <param name="command">The command that contains the query to prepare.</param>
/// <param name="connection">The connection to assign to the command.</param>
private static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection)
{
Guard.ArgumentNotNull(command, "command");
Guard.ArgumentNotNull(connection, "connection");
command.Connection = connection;
}
/// <summary>
/// Prepares a <see cref="SQLiteCommand"/> object for use. This involves setting the connection
/// and adding any parameters to the command.
/// </summary>
/// <param name="command">The command object you want prepared.</param>
/// <param name="connection">The connection to use with the command.</param>
/// <param name="parameters">Zero or more parameters to add to the command.</param>
private void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(command, "command");
Guard.ArgumentNotNull(connection, "connection");
command.Connection = connection;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
command.Parameters.Add(parameters[i]);
}
}
/// <summary>
/// Checks to see if a table exists in the open database.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns>true if the table exists, otherwise false.</returns>
public bool TableExists(string tableName)
{
Guard.ArgumentNotNullOrEmptyString(tableName, "tableName");
string sql = "SELECT name FROM sqlite_master WHERE name=@TableName";
SQLiteParameter param = CreateParameter("@TableName", DbType.String, 512, tableName);
SQLiteDataReader rdr = ExecuteReader(sql, param);
if (rdr.HasRows)
return true;
else
return false;
}
/// <summary>
/// This is a simple helper method that will convert a DBNull value into
/// a null value.
/// </summary>
/// <param name="value">The value you want to check for DBNull</param>
/// <returns>Null if <paramref name="value"/> is DBNull.Value, or <paramref name="value"/>.</returns>
public static object GetNullable(object value)
{
return (value is DBNull) ? null : value;
}
/// <summary>
/// Common guard clauses
/// </summary>
internal static class Guard
{
/// <summary>
/// Checks an argument to ensure it isn't null
/// </summary>
/// <param name="argumentValue">The argument value to check.</param>
/// <param name="argumentName">The name of the argument.</param>
public static void ArgumentNotNull(object argumentValue, string argumentName)
{
if (argumentValue == null)
throw new ArgumentNullException(argumentName);
}
/// <summary>
/// Checks a string argument to ensure it isn't null or empty
/// </summary>
/// <param name="argumentValue">The argument value to check.</param>
/// <param name="argumentName">The name of the argument.</param>
public static void ArgumentNotNullOrEmptyString(string argumentValue, string argumentName)
{
ArgumentNotNull(argumentValue, argumentName);
if (argumentValue.Trim().Length == 0)
throw new ArgumentException("The string cannot be empty.", argumentName);
}
}
}
}
using System.Data.Common;
using System.Data;
namespace System.Data.SQLite
{
/// <summary>
/// This class provides the foundation for simplified access to a database. It contains a number
/// of helper methods that make working with the database easier.
/// </summary>
public class SQLiteDatabase : IDisposable
{
private string connectionString;
private SQLiteConnection databaseConnection;
private SQLiteDatabase() { }
/// <summary>
/// The constructor.
/// </summary>
/// <param name="connectionString">
/// The connection string that will be used to connect to the database.
/// </param>
public SQLiteDatabase(string connectionString)
{
Guard.ArgumentNotNullOrEmptyString(connectionString, "connectionString");
this.connectionString = connectionString;
CreateConnection();
}
/// <summary>
/// Closes the current connection. You should call this method when you are completely
/// done using this database instance. Methods will fail after you've disposed of this
/// instance.
/// </summary>
public void Dispose()
{
if (databaseConnection != null)
{
databaseConnection.Close(); // .NET documents recommend closing instead of disposing connection
databaseConnection = null;
}
}
/// <summary>
/// Creates a new, unopened connection instance for this database.
/// </summary>
/// <returns>
/// An unopened <see cref="SQLiteConnection"/> for this database.
/// </returns>
/// <seealso cref="SQLiteConnection"/>
private void CreateConnection()
{
if (databaseConnection == null)
{
databaseConnection = new SQLiteConnection();
databaseConnection.ConnectionString = ConnectionString;
}
}
/// <summary>
/// Gets the connection string for this instance.
/// </summary>
public string ConnectionString
{
get { return connectionString; }
}
/// <summary>
/// Builds a value parameter name for the current database by ensuring there is an '@' at the
/// start of the name.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <returns>A correctly formated parameter name, which starts with an '@'.</returns>
public string BuildParameterName(string name)
{
Guard.ArgumentNotNullOrEmptyString(name, "name");
if (name[0] != '@')
return "@" + name;
else
return name;
}
/// <summary>
/// Creates a new parameter and sets the name of the parameter.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <returns>
/// A new <see cref="SQLiteParameter"/> instance of the correct type for this database.</returns>
/// <remarks>
/// The database will automatically add the correct prefix, like "@" for SQLite, to the
/// parameter name. In other words, you can just supply the name without a prefix.
/// </remarks>
public SQLiteParameter CreateParameter(string name)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.ParameterName = BuildParameterName(name);
return parameter;
}
/// <summary>
/// Creates a new parameter and sets the name of the parameter.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="value">
/// The value you want assigned to thsi parameter. A null value will be converted to
/// a <see cref="DBNull"/> value in the parameter.
/// </param>
/// <returns>
/// A new <see cref="SQLiteParameter"/> instance of the correct type for this database.</returns>
/// <remarks>
/// The database will automatically add the correct prefix, like "@" for SQLite, to the
/// parameter name. In other words, you can just supply the name without a prefix.
/// </remarks>
public SQLiteParameter CreateParameter(string name, object value)
{
SQLiteParameter param = CreateParameter(name);
param.Value = (value == null) ? DBNull.Value : value;
return param;
}
/// <summary>
/// Creates a new parameter and sets the name of the parameter.
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="type">The type of the parameter.</param>
/// <param name="size">The size of this parameter.</param>
/// <param name="value">
/// The value you want assigned to this parameter. A null value will be converted to
/// a <see cref="DBNull"/> value in the parameter.
/// </param>
/// <returns>
/// A new <see cref="SQLiteParameter"/> instance of the correct type for this database.</returns>
/// <remarks>
/// The database will automatically add the correct prefix, like "@" for SQLite, to the
/// parameter name. In other words, you can just supply the name without a prefix.
/// </remarks>
public SQLiteParameter CreateParameter(string name, DbType type, int size, object value)
{
SQLiteParameter param = CreateParameter(name);
param.DbType = type;
param.Size = size;
param.Value = (value == null) ? DBNull.Value : value;
return param;
}
/// <summary>
/// Executes an SQL query with an optional set of parameters.
/// </summary>
/// <param name="command">The command to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>The number of rows affected.</returns>
public int ExecuteNonQuery(SQLiteCommand command, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(command, "Command");
int result;
SQLiteConnection connection = GetConnection();
PrepareCommand(command, connection, parameters);
result = command.ExecuteNonQuery();
return result;
}
/// <summary>
/// Executes an SQL query with an optional set of parameters.
/// </summary>
/// <param name="sqlCommand">The SQL statement to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>The number of rows affected.</returns>
public int ExecuteNonQuery(string sqlCommand, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(sqlCommand, "sqlCommand");
using (SQLiteCommand command = new SQLiteCommand())
{
command.CommandText = sqlCommand;
return ExecuteNonQuery(command, parameters);
}
}
/// <summary>
/// Execute a command and return a <see cref="SQLiteDataReader"/> that contains the rows
/// returned.
/// </summary>
/// <param name="command">The command to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>A <see cref="SQLiteDataReader"/> that contains the rows returned by the query.</returns>
public SQLiteDataReader ExecuteReader(SQLiteCommand command, params SQLiteParameter[] parameters)
{
SQLiteDataReader result;
Guard.ArgumentNotNull(command, "command");
SQLiteConnection connection = GetConnection();
PrepareCommand(command, connection, parameters);
result = command.ExecuteReader();
return result;
}
/// <summary>
/// Execute a command and return a <see cref="SQLiteDataReader"/> that contains the rows
/// returned.
/// </summary>
/// <param name="sqlCommand">The SQL query to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>A <see cref="SQLiteDataReader"/> that contains the rows returned by the query.</returns>
public SQLiteDataReader ExecuteReader(string sqlCommand, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNullOrEmptyString(sqlCommand, "sqlCommand");
using (SQLiteCommand command = new SQLiteCommand())
{
command.CommandText = sqlCommand;
return ExecuteReader(command, parameters);
}
}
/// <summary>
/// <para>
/// Executes the <paramref name="command"/> and returns the first column of the first
/// row in the result set returned by the query. Extra columns or rows are ignored.
/// </para>
/// </summary>
/// <param name="command">
/// <para>
/// The command that contains the query to execute.
/// </para>
/// </param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>
/// <para>
/// The first column of the first row in the result set.
/// </para>
/// </returns>
/// <seealso cref="ISQLiteCommand.ExecuteScalar"/>
public object ExecuteScalar(SQLiteCommand command, params SQLiteParameter[] parameters)
{
object result;
Guard.ArgumentNotNull(command, "command");
SQLiteConnection connection = GetConnection();
PrepareCommand(command, connection, parameters);
result = command.ExecuteScalar();
return result;
}
/// <summary>
/// Executes the <paramref name="command"/> and returns the first column of the first
/// row in the result set returned by the query. Extra columns or rows are ignored.
/// </summary>
/// <param name="sqlCommand">The SQL statement to execute.</param>
/// <param name="parameters">Zero or more parameters for the query.</param>
/// <returns>
/// <para>
/// The first column of the first row in the result set.
/// </para>
/// </returns>
/// <seealso cref="ISQLiteCommand.ExecuteScalar"/>
public object ExecuteScalar(string sqlCommand, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(sqlCommand, "sqlCommand");
using (SQLiteCommand command = new SQLiteCommand())
{
command.CommandText = sqlCommand;
return ExecuteScalar(command, parameters);
}
}
/// <summary>
/// <para>Returns the shared connection, and opens it the first startTime.</para>
/// </summary>
/// <returns>The opened connection.</returns>
public SQLiteConnection GetConnection()
{
if (databaseConnection.State != ConnectionState.Open)
databaseConnection.Open();
return databaseConnection;
}
/// <summary>
/// Closes the shared connection.
/// </summary>
public void CloseConnection()
{
if (databaseConnection.State != ConnectionState.Closed)
{
databaseConnection.Close();
}
}
/// <summary>
/// <para>
/// Assigns a <paramref name="connection"/> to the <paramref name="command"/> and
/// discovers parameters if needed.
/// </para>
/// </summary>
/// <param name="command">The command that contains the query to prepare.</param>
/// <param name="connection">The connection to assign to the command.</param>
private static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection)
{
Guard.ArgumentNotNull(command, "command");
Guard.ArgumentNotNull(connection, "connection");
command.Connection = connection;
}
/// <summary>
/// Prepares a <see cref="SQLiteCommand"/> object for use. This involves setting the connection
/// and adding any parameters to the command.
/// </summary>
/// <param name="command">The command object you want prepared.</param>
/// <param name="connection">The connection to use with the command.</param>
/// <param name="parameters">Zero or more parameters to add to the command.</param>
private void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, params SQLiteParameter[] parameters)
{
Guard.ArgumentNotNull(command, "command");
Guard.ArgumentNotNull(connection, "connection");
command.Connection = connection;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
command.Parameters.Add(parameters[i]);
}
}
/// <summary>
/// Checks to see if a table exists in the open database.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns>true if the table exists, otherwise false.</returns>
public bool TableExists(string tableName)
{
Guard.ArgumentNotNullOrEmptyString(tableName, "tableName");
string sql = "SELECT name FROM sqlite_master WHERE name=@TableName";
SQLiteParameter param = CreateParameter("@TableName", DbType.String, 512, tableName);
SQLiteDataReader rdr = ExecuteReader(sql, param);
if (rdr.HasRows)
return true;
else
return false;
}
/// <summary>
/// This is a simple helper method that will convert a DBNull value into
/// a null value.
/// </summary>
/// <param name="value">The value you want to check for DBNull</param>
/// <returns>Null if <paramref name="value"/> is DBNull.Value, or <paramref name="value"/>.</returns>
public static object GetNullable(object value)
{
return (value is DBNull) ? null : value;
}
/// <summary>
/// Common guard clauses
/// </summary>
internal static class Guard
{
/// <summary>
/// Checks an argument to ensure it isn't null
/// </summary>
/// <param name="argumentValue">The argument value to check.</param>
/// <param name="argumentName">The name of the argument.</param>
public static void ArgumentNotNull(object argumentValue, string argumentName)
{
if (argumentValue == null)
throw new ArgumentNullException(argumentName);
}
/// <summary>
/// Checks a string argument to ensure it isn't null or empty
/// </summary>
/// <param name="argumentValue">The argument value to check.</param>
/// <param name="argumentName">The name of the argument.</param>
public static void ArgumentNotNullOrEmptyString(string argumentValue, string argumentName)
{
ArgumentNotNull(argumentValue, argumentName);
if (argumentValue.Trim().Length == 0)
throw new ArgumentException("The string cannot be empty.", argumentName);
}
}
}
}
这里下载源码：https://files.cnblogs.com/bobli/SQLiteDatabase_src.rar
作者：黎波
博客：http://bobli.cnblogs.com/
日期：2011年1月4日
