public class AccessHelper
{
private AccessHelper() { }
private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, string commandText, OleDbParameter[] commandParameters, out bool mustCloseConnection)
{
if (null == command) throw new ArgumentNullException("command");
if (null == commandText || string.Empty == commandText) throw new ArgumentNullException("commandText");
if (ConnectionState.Closed == connection.State)
{
connection.Open();
mustCloseConnection = true;
}
else
{
mustCloseConnection = false;
}
command.Connection = connection;
command.CommandText = commandText;
if (null != transaction)
{
if (null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
command.Transaction = transaction;
}
command.CommandType = CommandType.Text;
if (null != commandParameters)
{
AttachParameters(command, commandParameters);
}
}
private static void AttachParameters(OleDbCommand command, OleDbParameter[] commandParameters)
{
if (null == command) throw new ArgumentNullException("command");
if (null != commandParameters)
{
foreach (OleDbParameter p in commandParameters)
{
if (null != p)
{
if ((ParameterDirection.Input == p.Direction || ParameterDirection.InputOutput == p.Direction) && (null == p.Value))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
public static int ExecuteNonQuery(string connectionstring, string commandText)
{
return ExecuteNonQuery(connectionstring, commandText, (OleDbParameter[])null);
}
public static int ExecuteNonQuery(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
return ExecuteNonQuery(connection, commandText, commandParameters);
}
}
public static int ExecuteNonQuery(OleDbConnection connection, string commandText)
{
return ExecuteNonQuery(connection, commandText, (OleDbParameter[])null);
}
public static int ExecuteNonQuery(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connection) throw new ArgumentNullException("connection");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
if (mustCloseConnection) connection.Close();
return retval;
}
public static int ExecuteNonQuery(OleDbTransaction transaction, string commandText)
{
return ExecuteNonQuery(transaction, commandText, (OleDbParameter[])null);
}
public static int ExecuteNonQuery(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
public static object ExecuteScalar(string connectionString, string commandText)
{
return ExecuteScalar(connectionString, commandText, (OleDbParameter[])null);
}
public static object ExecuteScalar(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString && string.Empty == connectionString) throw new ArgumentNullException("connectionString");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
return ExecuteScalar(connection, commandText, commandParameters);
}
}
public static object ExecuteScalar(OleDbConnection connection, string commandText)
{
return ExecuteScalar(connection, commandText, (OleDbParameter[])null);
}
public static object ExecuteScalar(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connection) throw new ArgumentNullException("connection");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
public static object ExecuteScalar(OleDbTransaction transaction, string commandText)
{
return ExecuteScalar(transaction, commandText, (OleDbParameter[])null);
}
public static object ExecuteScalar(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return retval;
}
private enum OleDbConnectionOwnership
{
/// <summary>
/// 连接对象是由SqlHelper管理的
/// </summary>
Internal,
/// <summary>
/// 连接对象是由调用者管理的
/// </summary>
External
}
private static OleDbDataReader ExecuteReader(OleDbConnection connection, OleDbTransaction transaction, string commandText, OleDbParameter[] commandParameters, OleDbConnectionOwnership connectionOwnership)
{
if (null == connection) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
OleDbCommand cmd = new OleDbCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandText, commandParameters, out mustCloseConnection);
OleDbDataReader dataReader;
if (OleDbConnectionOwnership.External == connectionOwnership)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool canClear = true;
foreach (OleDbParameter commandParameter in cmd.Parameters)
{
if (ParameterDirection.Input != commandParameter.Direction) canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection) connection.Close();
throw;
}
}
public static OleDbDataReader ExecuteReader(OleDbConnection connection, string commandText)
{
return ExecuteReader(connection, commandText, (OleDbParameter[])null);
}
public static OleDbDataReader ExecuteReader(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
return ExecuteReader(connection, (OleDbTransaction)null, commandText, commandParameters, OleDbConnectionOwnership.External);
}
public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, string commandText)
{
return ExecuteReader(transaction, commandText, (OleDbParameter[])null);
}
public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
return ExecuteReader(transaction.Connection, transaction, commandText, commandParameters, OleDbConnectionOwnership.External);
}
public static OleDbDataReader ExecuteReader(string connectionString, string commandText)
{
return ExecuteReader(connectionString, commandText, (OleDbParameter[])null);
}
public static OleDbDataReader ExecuteReader(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
OleDbConnection connection = null;
try
{
connection = new OleDbConnection(connectionString);
connection.Open();
return ExecuteReader(connection, (OleDbTransaction)null, commandText, commandParameters, OleDbConnectionOwnership.Internal);
}
catch
{
if (connection != null) connection.Close();
throw;
}
}
public static DataSet ExecuteDataset(string connectionString, string commandText)
{
return ExecuteDataset(connectionString, commandText, (OleDbParameter[])null);
}
public static DataSet ExecuteDataset(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
return ExecuteDataset(connectionString, commandText, commandParameters);
}
}
public static DataSet ExecuteDataset(OleDbConnection connection, string commandText)
{
return ExecuteDataset(connection, commandText, (OleDbParameter[])null);
}
public static DataSet ExecuteDataset(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connection) throw new ArgumentNullException("connection");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return ds;
}
}
public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText)
{
return ExecuteDataset(transaction, commandText, (OleDbParameter[])null);
}
public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
}
{
private AccessHelper() { }
private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, string commandText, OleDbParameter[] commandParameters, out bool mustCloseConnection)
{
if (null == command) throw new ArgumentNullException("command");
if (null == commandText || string.Empty == commandText) throw new ArgumentNullException("commandText");
if (ConnectionState.Closed == connection.State)
{
connection.Open();
mustCloseConnection = true;
}
else
{
mustCloseConnection = false;
}
command.Connection = connection;
command.CommandText = commandText;
if (null != transaction)
{
if (null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
command.Transaction = transaction;
}
command.CommandType = CommandType.Text;
if (null != commandParameters)
{
AttachParameters(command, commandParameters);
}
}
private static void AttachParameters(OleDbCommand command, OleDbParameter[] commandParameters)
{
if (null == command) throw new ArgumentNullException("command");
if (null != commandParameters)
{
foreach (OleDbParameter p in commandParameters)
{
if (null != p)
{
if ((ParameterDirection.Input == p.Direction || ParameterDirection.InputOutput == p.Direction) && (null == p.Value))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
public static int ExecuteNonQuery(string connectionstring, string commandText)
{
return ExecuteNonQuery(connectionstring, commandText, (OleDbParameter[])null);
}
public static int ExecuteNonQuery(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
return ExecuteNonQuery(connection, commandText, commandParameters);
}
}
public static int ExecuteNonQuery(OleDbConnection connection, string commandText)
{
return ExecuteNonQuery(connection, commandText, (OleDbParameter[])null);
}
public static int ExecuteNonQuery(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connection) throw new ArgumentNullException("connection");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
if (mustCloseConnection) connection.Close();
return retval;
}
public static int ExecuteNonQuery(OleDbTransaction transaction, string commandText)
{
return ExecuteNonQuery(transaction, commandText, (OleDbParameter[])null);
}
public static int ExecuteNonQuery(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
public static object ExecuteScalar(string connectionString, string commandText)
{
return ExecuteScalar(connectionString, commandText, (OleDbParameter[])null);
}
public static object ExecuteScalar(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString && string.Empty == connectionString) throw new ArgumentNullException("connectionString");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
return ExecuteScalar(connection, commandText, commandParameters);
}
}
public static object ExecuteScalar(OleDbConnection connection, string commandText)
{
return ExecuteScalar(connection, commandText, (OleDbParameter[])null);
}
public static object ExecuteScalar(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connection) throw new ArgumentNullException("connection");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
public static object ExecuteScalar(OleDbTransaction transaction, string commandText)
{
return ExecuteScalar(transaction, commandText, (OleDbParameter[])null);
}
public static object ExecuteScalar(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return retval;
}
private enum OleDbConnectionOwnership
{
/// <summary>
/// 连接对象是由SqlHelper管理的
/// </summary>
Internal,
/// <summary>
/// 连接对象是由调用者管理的
/// </summary>
External
}
private static OleDbDataReader ExecuteReader(OleDbConnection connection, OleDbTransaction transaction, string commandText, OleDbParameter[] commandParameters, OleDbConnectionOwnership connectionOwnership)
{
if (null == connection) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
OleDbCommand cmd = new OleDbCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandText, commandParameters, out mustCloseConnection);
OleDbDataReader dataReader;
if (OleDbConnectionOwnership.External == connectionOwnership)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool canClear = true;
foreach (OleDbParameter commandParameter in cmd.Parameters)
{
if (ParameterDirection.Input != commandParameter.Direction) canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection) connection.Close();
throw;
}
}
public static OleDbDataReader ExecuteReader(OleDbConnection connection, string commandText)
{
return ExecuteReader(connection, commandText, (OleDbParameter[])null);
}
public static OleDbDataReader ExecuteReader(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
return ExecuteReader(connection, (OleDbTransaction)null, commandText, commandParameters, OleDbConnectionOwnership.External);
}
public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, string commandText)
{
return ExecuteReader(transaction, commandText, (OleDbParameter[])null);
}
public static OleDbDataReader ExecuteReader(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
return ExecuteReader(transaction.Connection, transaction, commandText, commandParameters, OleDbConnectionOwnership.External);
}
public static OleDbDataReader ExecuteReader(string connectionString, string commandText)
{
return ExecuteReader(connectionString, commandText, (OleDbParameter[])null);
}
public static OleDbDataReader ExecuteReader(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
OleDbConnection connection = null;
try
{
connection = new OleDbConnection(connectionString);
connection.Open();
return ExecuteReader(connection, (OleDbTransaction)null, commandText, commandParameters, OleDbConnectionOwnership.Internal);
}
catch
{
if (connection != null) connection.Close();
throw;
}
}
public static DataSet ExecuteDataset(string connectionString, string commandText)
{
return ExecuteDataset(connectionString, commandText, (OleDbParameter[])null);
}
public static DataSet ExecuteDataset(string connectionString, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connectionString || string.Empty == connectionString) throw new ArgumentNullException("connectionString");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
return ExecuteDataset(connectionString, commandText, commandParameters);
}
}
public static DataSet ExecuteDataset(OleDbConnection connection, string commandText)
{
return ExecuteDataset(connection, commandText, (OleDbParameter[])null);
}
public static DataSet ExecuteDataset(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
{
if (null == connection) throw new ArgumentNullException("connection");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return ds;
}
}
public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText)
{
return ExecuteDataset(transaction, commandText, (OleDbParameter[])null);
}
public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
{
if (null == transaction) throw new ArgumentNullException("transaction");
if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
OleDbCommand cmd = new OleDbCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
}
本文使用Blog_Backup未注册版本导出，请到soft.pt42.com注册。