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)
sql");
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