最近准备用的数据访问类，欢迎拍砖！
//====================================================================
// Copyright (C) 2008 allen. All rights reserved.
//====================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;
namespace BinaryIntellect.DataAccess
{
public class DatabaseHelper:IDisposable
{
#region members
private string strConnectionString;
private DbConnection objConnection;
private DbCommand objCommand;
private DbProviderFactory objFactory = null;
#endregion
#region constructor
public DatabaseHelper(string connectionstring,Providers provider)
{
strConnectionString = connectionstring;
switch (provider)
{
case Providers.SqlServer:
objFactory = SqlClientFactory.Instance;
break;
case Providers.OleDb:
objFactory = OleDbFactory.Instance;
break;
case Providers.Oracle:
objFactory = OracleClientFactory.Instance;
break;
case Providers.ODBC:
objFactory = OdbcFactory.Instance;
break;
case Providers.ConfigDefined:
string providername=ConfigurationManager.ConnectionStrings["connectionstring"].ProviderName;
switch (providername)
{
case "System.Data.SqlClient":
objFactory = SqlClientFactory.Instance;
break;
case "System.Data.OleDb":
objFactory = OleDbFactory.Instance;
break;
case "System.Data.OracleClient":
objFactory = OracleClientFactory.Instance;
break;
case "System.Data.Odbc":
objFactory = OdbcFactory.Instance;
break;
}
break;
}
objConnection = objFactory.CreateConnection();
objCommand = objFactory.CreateCommand();
objConnection.ConnectionString = strConnectionString;
objCommand.Connection = objConnection;
}
public DatabaseHelper(Providers provider):this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,provider)
{
}
public DatabaseHelper(string connectionstring): this(connectionstring, Providers.SqlServer)
{
}
public DatabaseHelper():this(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString,Providers.ConfigDefined)
{
}
#endregion
#region AddParameter
public int AddParameter(string name,object value)
{
DbParameter p = objFactory.CreateParameter();
p.ParameterName = name;
p.Value=value;
return objCommand.Parameters.Add(p);
}
public int AddParameter(DbParameter parameter)
{
return objCommand.Parameters.Add(parameter);
}
public int AddParameter(DbParameter[] dbParam)
{
if (dbParam == null)
{
return 0;
}
try
{
foreach (DbParameter param in dbParam)
{
objCommand.Parameters.Add(param);
}
return dbParam.Length;
}
catch
{
return 0;
}
}
#endregion
public DbCommand Command
{
get
{
return objCommand;
}
}
#region Transaction Operation
public void BeginTransaction()
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
objCommand.Transaction = objConnection.BeginTransaction();
}
public void CommitTransaction()
{
objCommand.Transaction.Commit();
objConnection.Close();
}
public void RollbackTransaction()
{
objCommand.Transaction.Rollback();
objConnection.Close();
}
#endregion
#region ExecuteNonQuery
public int ExecuteNonQuery(string query)
{
return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
}
public int ExecuteNonQuery(string query,CommandType commandtype)
{
return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
}
public int ExecuteNonQuery(string query,ConnectionState connectionstate)
{
return ExecuteNonQuery(query,CommandType.Text,connectionstate);
}
public int ExecuteNonQuery(string query,CommandType commandtype, ConnectionState connectionstate)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
int i=-1;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
i = objCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//throw ex;
}
finally
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
{
objConnection.Close();
}
}
return i;
}
#endregion
#region Executescalar
public object ExecuteScalar(string query)
{
return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
}
public object ExecuteScalar(string query,CommandType commandtype)
{
return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
}
public object ExecuteScalar(string query, ConnectionState connectionstate)
{
return ExecuteScalar(query, CommandType.Text, connectionstate);
}
public object ExecuteScalar(string query,CommandType commandtype, ConnectionState connectionstate)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
object o = null;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
o = objCommand.ExecuteScalar();
}
catch (Exception ex)
{
//throw ex;
}
finally
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
{
objConnection.Close();
}
}
return o;
}
#endregion
#region ExecuteReader
public DbDataReader ExecuteReader(string query)
{
return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
}
public DbDataReader ExecuteReader(string query,CommandType commandtype)
{
return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
}
public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
{
return ExecuteReader(query, CommandType.Text, connectionstate);
}
public DbDataReader ExecuteReader(string query,CommandType commandtype, ConnectionState connectionstate)
{
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
DbDataReader reader=null;
try
{
if (objConnection.State == System.Data.ConnectionState.Closed)
{
objConnection.Open();
}
if (connectionstate == ConnectionState.CloseOnExit)
{
reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
reader = objCommand.ExecuteReader();
}
}
catch (Exception ex)
{
//throw ex;
}
finally
{
objCommand.Parameters.Clear();
}
return reader;
}
#endregion
#region ExecuteDataSet
public DataSet ExecuteDataSet(string query)
{
return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
}
public DataSet ExecuteDataSet(string query,CommandType commandtype)
{
return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
}
public DataSet ExecuteDataSet(string query,ConnectionState connectionstate)
{
return ExecuteDataSet(query, CommandType.Text, connectionstate);
}
public DataSet ExecuteDataSet(string query,CommandType commandtype, ConnectionState connectionstate)
{
DbDataAdapter adapter = objFactory.CreateDataAdapter();
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
adapter.SelectCommand = objCommand;
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
HandleExceptions(ex);
}
finally
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
{
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
}
}
return ds;
}
#endregion
#region ExecuteDataTable
public DataTable ExecuteDataTable(string query)
{
return ExecuteDataTable(query, CommandType.Text, ConnectionState.CloseOnExit);
}
public DataTable ExecuteDataTable(string query, CommandType commandtype)
{
return ExecuteDataTable(query, commandtype, ConnectionState.CloseOnExit);
}
public DataTable ExecuteDataTable(string query, ConnectionState connectionstate)
{
return ExecuteDataTable(query, CommandType.Text, connectionstate);
}
public DataTable ExecuteDataTable(string query, CommandType commandtype, ConnectionState connectionstate)
{
DbDataAdapter adapter = objFactory.CreateDataAdapter();
objCommand.CommandText = query;
objCommand.CommandType = commandtype;
adapter.SelectCommand = objCommand;
DataSet ds = new DataSet();
DataTable table = null;
try
{
adapter.Fill(ds);
table = ds.Tables[0].Copy();
ds.Dispose();
}
catch (Exception ex)
{
//throw ex;
}
finally
{
objCommand.Parameters.Clear();
if (connectionstate == ConnectionState.CloseOnExit)
{
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
}
}
return table;
}
#endregion
#region ExecuteSqlTransaction
public bool ExecuteSqlTranByHash(Hashtable SQLStringList)
{
BeginTransaction();
try
{
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
DbParameter[] cmdParms = (DbParameter[])myDE.Value;
foreach (DbParameter p in cmdParms)
{
AddParameter(p);
}
int val = ExecuteNonQuery(cmdText, ConnectionState.KeepOpen);
objCommand.Parameters.Clear();
}
CommitTransaction();
return true;
}
catch
{
RollbackTransaction();
}
return false;
}
public bool ExecuteSqlTranByList(ArrayList SQLStringList)
{
BeginTransaction();
try
{
foreach (string s in SQLStringList)
{
string cmdText = s;
int val = ExecuteNonQuery(cmdText, ConnectionState.KeepOpen);
objCommand.Parameters.Clear();
}
CommitTransaction();
return true;
}
catch
{
RollbackTransaction();
}
return false;
}
#endregion
public void Dispose()
{
objConnection.Close();
objConnection.Dispose();
objCommand.Dispose();
}
}
public enum Providers
{
SqlServer, OleDb, Oracle, ODBC, ConfigDefined
}
public enum ConnectionState
{
KeepOpen, CloseOnExit
}
}
请大家多拍砖
就像有人提出的Enterprise Library，我对这个库的使用也存在一定的疑虑，到底性能如何
欢迎大家也对Enterprise Library的使用提出一些建议
