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);
            }
        }
    }
}

 

 

这里下载源码：https://files.cnblogs.com/bobli/SQLiteDatabase_src.rar

 

作者：黎波
博客：http://bobli.cnblogs.com/
日期：2011年1月4日