SQLite数据访问类(.NETCF)
发布时间：2020-12-12 20:26:22 所属栏目：百科 来源：网络整理
导读：using System; using System.Data; using System.Data.SQLite; namespace Util { /// summary /// SQLiteHelper SQLite数据库辅助类 /// /summary public class SQLiteHelper : IDisposable { #region 字段 private string connectionString; private SQLite
|
using System;
using System.Data; using System.Data.SQLite; namespace Util { /// <summary> /// SQLiteHelper SQLite数据库辅助类 /// </summary> public class SQLiteHelper : IDisposable { #region 字段 private string connectionString; private SQLiteConnection databaseConnection; #endregion #region 属性 /// Gets the connection string for this instance. public string ConnectionString get { return connectionString; } } #region 构造函数 public SQLiteHelper(){} public SQLiteHelper(string connectionString) if (connectionString == null) throw new ArgumentNullException("connectionString"); this.connectionString = connectionString; CreateConnection(); #region 方法 /// 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. public void Dispose() if (databaseConnection != null) databaseConnection.Close(); databaseConnection = null; /// Creates a new,unopened connection instance for this database. /// <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; /// <para>Returns the shared connection,and opens it the first startTime.</para> /// <returns>The opened connection.</returns> public SQLiteConnection GetConnection() if (databaseConnection.State != ConnectionState.Open) databaseConnection.Open(); return databaseConnection; /// Closes the shared connection. public void CloseConnection() if (databaseConnection.State != ConnectionState.Closed) /// Builds a value parameter name for the current database by ensuring there is an '@' at the /// start of the name. /// <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) if (name == null) throw new ArgumentNullException("name"); if (name.Trim().Length == 0) throw new ArgumentException("The string cannot be empty.","name"); if (name[0] != '@') return "@" + name; else return name; /// Creates a new parameter and sets the name of the parameter. /// 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; /// <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> public SQLiteParameter CreateParameter(string name,object value) SQLiteParameter param = CreateParameter(name); param.Value = (value == null) ? DBNull.Value : value; return param; /// <param name="type">The type of the parameter.</param> /// <param name="size">The size of this parameter.</param> /// The value you want assigned to this parameter. A null value will be converted to param.DbType = type; param.Size = size; /// Executes an SQL query with an optional set of parameters. /// <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) if (command == null) throw new ArgumentNullException("command"); int result; SQLiteConnection connection = GetConnection(); PrepareCommand(command,connection,parameters); result = command.ExecuteNonQuery(); return result; /// <param name="strSql">The SQL statement to execute.</param> public int ExecuteNonQuery(string strSql,sans-serif; font-size:14px; line-height:25px"> if (strSql == null) throw new ArgumentNullException("strSql"); using (SQLiteCommand command = new SQLiteCommand()) command.CommandText = strSql; return ExecuteNonQuery(command,sans-serif; font-size:14px; line-height:25px"> /// Execute a command and return a <see cref="SQLiteDataReader"/> that contains the rows /// returned. /// <returns>A <see cref="SQLiteDataReader"/> that contains the rows returned by the query.</returns> public SQLiteDataReader ExecuteReader(SQLiteCommand command,sans-serif; font-size:14px; line-height:25px"> SQLiteDataReader result; result = command.ExecuteReader(); /// <param name="strSql">The SQL query to execute.</param> public SQLiteDataReader ExecuteReader(string strSql,sans-serif; font-size:14px; line-height:25px"> if (strSql.Trim().Length == 0) return ExecuteReader(command,sans-serif; font-size:14px; line-height:25px"> /// <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> /// <param name="command"> /// The command that contains the query to execute. /// The first column of the first row in the result set. /// <seealso cref="ISQLiteCommand.ExecuteScalar"/> public object ExecuteScalar(SQLiteCommand command,sans-serif; font-size:14px; line-height:25px"> object result; result = command.ExecuteScalar(); public object ExecuteScalar(string strSql,sans-serif; font-size:14px; line-height:25px"> return ExecuteScalar(command,sans-serif; font-size:14px; line-height:25px"> /// Assigns a <paramref name="connection"/> to the <paramref name="command"/> and /// discovers parameters if needed. /// <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) if (connection == null) throw new ArgumentNullException("connection"); command.Connection = connection; /// Prepares a <see cref="SQLiteCommand"/> object for use. This involves setting the connection /// and adding any parameters to the command. /// <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,sans-serif; font-size:14px; line-height:25px"> if (parameters != null) for (int i = 0; i < parameters.Length; i++) command.Parameters.Add(parameters[i]); /// Checks to see if a table exists in the open database. /// <param name="tableName">Name of the table.</param> /// <returns>true if the table exists,otherwise false.</returns> public bool TableExists(string tableName) if (tableName == null) throw new ArgumentNullException("tableName"); if (tableName.Trim().Length == 0) string strSql = "SELECT name FROM sqlite_master WHERE name=@TableName"; SQLiteParameter param = CreateParameter("@TableName",DbType.String,512,tableName); SQLiteDataReader rdr = ExecuteReader(strSql,param); return rdr.HasRows; /// This is a simple helper method that will convert a DBNull value into /// a null value. /// <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; } 转自:http://www.cnblogs.com/bobli/archive/2011/01/04/1925893.html （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |