sql2000使用实例:
DBManager dBManager = new DBManager(DataProvider.SqlServer, "Data Source=.;Initial Catalog=China_GYM_Lottery;User ID=sa;pwd=sa");
dBManager.Open();
dBManager.ExecuteNonQuery("select * from LotteryHistory");
dBManager.Close();
,呵呵数据操作就变的这么简单,拿出来与大家分享.
数据库操作类,超强(老外写的,WAlottery彩票软件基于此开发)
using System;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
namespace FrameworkServer
{
public enum DataProvider
{
Oracle,SqlServer,OleDb,Odbc
}
public enum ApplicationType
{
WindowsForm,WebForm
}
#region 接口
public interface IDBManager
{
DataProvider ProviderType
{
get;
set;
}
string ConnectionString
{
get;
set;
}
IDbConnection Connection
{
get;
}
IDbTransaction Transaction
{
get;
}
IDataReader DataReader
{
get;
}
IDbCommand Command
{
get;
}
IDbDataParameter[] Parameters
{
get;
}
void Open();
void BeginTransaction();
void CommitTransaction();
void CreateParameters(int paramsCount);
void AddParameters(int index, string paramName, object objValue);
IDataReader ExecuteReader(CommandType commandType, string commandText);
DataSet ExecuteDataSet(CommandType commandType, string commandText);
//DataTable ExecuteDataTable(CommandType commandType, string commandText,string tableName);
object ExecuteScalar(CommandType commandType, string commandText);
int ExecuteNonQuery(CommandType commandType,string commandText);
void CloseReader();
void Close();
void Dispose();
}
#endregion
#region DBManagerFactory
public sealed class DBManagerFactory
{
private DBManagerFactory(){}
public static IDbConnection GetConnection(DataProvider providerType)
{
IDbConnection iDbConnection = null;
switch (providerType)
{
case DataProvider.SqlServer:
iDbConnection = new SqlConnection();
break;
case DataProvider.OleDb:
iDbConnection = new OleDbConnection();
break;
case DataProvider.Odbc:
iDbConnection = new OdbcConnection();
break;
case DataProvider.Oracle:
iDbConnection = new OracleConnection();
break;
default:
return null;
}
return iDbConnection;
}
public static IDbCommand GetCommand(DataProvider providerType)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlCommand();
case DataProvider.OleDb:
return new OleDbCommand();
case DataProvider.Odbc:
return new OdbcCommand();
case DataProvider.Oracle:
return new OracleCommand();
default:
return null;
}
}
public static IDbDataAdapter GetDataAdapter(DataProvider providerType)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlDataAdapter();
case DataProvider.OleDb:
return new OleDbDataAdapter();
case DataProvider.Odbc:
return new OdbcDataAdapter();
case DataProvider.Oracle:
return new OracleDataAdapter();
default:
return null;
}
}
public static IDbTransaction GetTransaction(DataProvider providerType)
{
IDbConnection iDbConnection =GetConnection(providerType);
IDbTransaction iDbTransaction =iDbConnection.BeginTransaction();
return iDbTransaction;
}
public static IDataParameter GetParameter(DataProvider providerType)
{
IDataParameter iDataParameter = null;
switch (providerType)
{
case DataProvider.SqlServer:
iDataParameter = new SqlParameter();
break;
case DataProvider.OleDb:
iDataParameter = new OleDbParameter();
break;
case DataProvider.Odbc:
iDataParameter = new OdbcParameter();
break;
case DataProvider.Oracle:
iDataParameter = new OracleParameter();
break;
}
return iDataParameter;
}
public static IDbDataParameter[] GetParameters(DataProvider providerType,int paramsCount)
{
IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
switch (providerType)
{
case DataProvider.SqlServer:
for (int i = 0; i < paramsCount;++i)
{
idbParams[i] = new SqlParameter();
}
break;
case DataProvider.OleDb:
for (int i = 0; i < paramsCount;++i)
{
idbParams[i] = new OleDbParameter();
}
break;
case DataProvider.Odbc:
for (int i = 0; i < paramsCount;++i)
{
idbParams[i] = new OdbcParameter();
}
break;
case DataProvider.Oracle:
for (int i = 0; i <paramsCount; ++i)
{
idbParams[i] = new OracleParameter();
}
break;
default:
idbParams = null;
break;
}
return idbParams;
}
}
#endregion
#region DataBase操作方法
public sealed class DBManager: IDBManager,IDisposable
{
private IDbConnection idbConnection;
private IDataReader idataReader;
private IDbCommand idbCommand;
private DataProvider providerType;
private IDbTransaction idbTransaction =null;
private IDbDataParameter[]idbParameters =null;
private string strConnection;
public DBManager()
{
}
/// <summary>
/// 从web.config中读取DATAPROVIDERTYPE字段指出数据库的类型
/// 从web.config中读取SQLSERVERCONNECTIONSTRING字段指出数据库的连接字符串
/// </summary>
public DBManager(ApplicationType AT)
{
if (AT==ApplicationType.WebForm)
{
string dataProviderType = System.Configuration.ConfigurationSettings.AppSettings["DATAPROVIDERTYPE"].ToString();
if (dataProviderType != "" || dataProviderType != string.Empty)
{
if (dataProviderType.ToLower() == "access")
{
this.providerType = DataProvider.OleDb;
this.strConnection = System.Configuration.ConfigurationSettings.AppSettings["ACCESSCONNECTIONSTRING"].ToString();
}
if (dataProviderType.ToLower() == "sqlserver")
{
this.providerType = DataProvider.SqlServer;
this.strConnection = System.Configuration.ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString();
}
if (dataProviderType.ToLower() == "odbc")
{
this.providerType = DataProvider.Odbc;
this.strConnection = System.Configuration.ConfigurationSettings.AppSettings["ODBCCONNECTIONSTRING"].ToString();
}
if (dataProviderType.ToLower() == "oracle")
{
this.providerType = DataProvider.Oracle;
this.strConnection = System.Configuration.ConfigurationSettings.AppSettings["ORCALECONNECTIONSTRING"].ToString();
}
}
}//End if
else if (AT==ApplicationType.WindowsForm)
{
}//End else if
}
public DBManager(DataProvider providerType)
{
this.providerType = providerType;
}
public DBManager(DataProvider providerType, string connectionString)
{
this.providerType = providerType;
this.strConnection = connectionString;
}
public IDbConnection Connection
{
get
{
return idbConnection;
}
}
public IDataReader DataReader
{
get
{
return idataReader;
}
set
{
idataReader = value;
}
}
public DataProvider ProviderType
{
get
{
return providerType;
}
set
{
providerType = value;
}
}
public string ConnectionString
{
get
{
return strConnection;
}
set
{
strConnection = value;
}
}
public IDbCommand Command
{
get
{
return idbCommand;
}
}
public IDbTransaction Transaction
{
get
{
return idbTransaction;
}
}
public IDbDataParameter[] Parameters
{
get
{
return idbParameters;
}
}
public void Open()
{
try
{
idbConnection = DBManagerFactory.GetConnection(this.providerType);
idbConnection.ConnectionString = this.ConnectionString;
if (idbConnection.State != ConnectionState.Open)
{
idbConnection.Open();
}
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
}
catch
{
throw;
}
}
public void Close()
{
if (idbConnection.State !=ConnectionState.Closed)
idbConnection.Close();
}
public void Dispose()
{
GC.SuppressFinalize(this);
this.Close();
this.idbCommand = null;
this.idbTransaction = null;
this.idbConnection = null;
}
public void BeginTransaction()
{
try
{
if (this.idbTransaction == null)
{
idbTransaction = DBManagerFactory.GetTransaction(this.ProviderType);
}
this.idbCommand.Transaction = idbTransaction;
}
catch
{
throw;
}
}
public void CommitTransaction()
{
if (this.idbTransaction != null)
{
this.idbTransaction.Commit();
}
idbTransaction = null;
}
public void CreateParameters(int paramsCount)
{
idbParameters = new IDbDataParameter[paramsCount];
idbParameters =DBManagerFactory.GetParameters(this.ProviderType,paramsCount);
}
public void AddParameters(int index, string paramName, object objValue)
{
if (index < idbParameters.Length)
{
idbParameters[index].ParameterName = paramName;
idbParameters[index].Value = objValue;
}
}
public void AddParameters(int index, string paramName, int size, ParameterDirection parDirection)
{
if (index < idbParameters.Length)
{
idbParameters[index].Size = size;
idbParameters[index].ParameterName = paramName;
idbParameters[index].Direction = parDirection;
}
}
private void AttachParameters(IDbCommand command,IDbDataParameter[]commandParameters)
{
foreach (IDbDataParameter idbParameter in commandParameters)
{
if ((idbParameter.Direction == ParameterDirection.InputOutput)&&(idbParameter.Value == null))
{
idbParameter.Value = DBNull.Value;
}
command.Parameters.Add(idbParameter);
}
}
private void PrepareCommand(IDbCommand command, IDbConnection connection,IDbTransaction transaction, CommandType commandType, string commandText,IDbDataParameter[] commandParameters)
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
}
public IDataReader ExecuteReader(CommandType commandType, string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
idbCommand.Connection = this.Connection;
PrepareCommand(idbCommand, this.Connection, this.Transaction,
commandType, commandText, this.Parameters);
this.DataReader = idbCommand.ExecuteReader(CommandBehavior.CloseConnection);
idbCommand.Parameters.Clear();
return this.DataReader;
}
catch
{
throw;
}
}
public IDataReader ExecuteReader(string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
idbCommand.Connection = this.Connection;
PrepareCommand(idbCommand, this.Connection, this.Transaction,
CommandType.Text,
commandText, this.Parameters);
this.DataReader = idbCommand.ExecuteReader(CommandBehavior.CloseConnection);
idbCommand.Parameters.Clear();
return this.DataReader;
}
catch
{
throw;
}
}
public void CloseReader()
{
if (this.DataReader != null)
this.DataReader.Close();
}
public int ExecuteNonQuery(CommandType commandType, string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
int returnValue = idbCommand.ExecuteNonQuery();
idbCommand.Parameters.Clear();
return returnValue;
}
catch
{
throw;
}
}
public int ExecuteNonQuery( string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction, CommandType.Text, commandText, this.Parameters);
int returnValue = idbCommand.ExecuteNonQuery();
idbCommand.Parameters.Clear();
return returnValue;
}
catch
{
throw;
}
}
public object ExecuteScalar(CommandType commandType, string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction,
commandType,
commandText, this.Parameters);
object returnValue = idbCommand.ExecuteScalar();
idbCommand.Parameters.Clear();
return returnValue;
}
catch
{
throw;
}
}
public object ExecuteScalar(string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction,
CommandType.Text,
commandText, this.Parameters);
object returnValue = idbCommand.ExecuteScalar();
idbCommand.Parameters.Clear();
return returnValue;
}
catch
{
throw;
}
}
public DataSet ExecuteDataSet(CommandType commandType, string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
dataAdapter.SelectCommand = idbCommand;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
idbCommand.Parameters.Clear();
return dataSet;
}
catch
{
throw;
}
}
public DataSet ExecuteDataSet(string commandText)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction, CommandType.Text, commandText, this.Parameters);
IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
dataAdapter.SelectCommand = idbCommand;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
idbCommand.Parameters.Clear();
return dataSet;
}
catch
{
throw;
}
}
public DataTable ExecuteDataTable(CommandType commandType, string commandText, string tableName)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
dataAdapter.SelectCommand = idbCommand;
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
dataAdapter.Fill(ds);
dataTable = ds.Tables[0];
dataTable.TableName = tableName;
idbCommand.Parameters.Clear();
return dataTable;
}
catch
{
throw;
}
}
public DataTable ExecuteDataTable(string commandText, string tableName)
{
try
{
this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType);
PrepareCommand(idbCommand, this.Connection, this.Transaction, CommandType.Text, commandText, this.Parameters);
IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
dataAdapter.SelectCommand = idbCommand;
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
dataAdapter.Fill(ds);
dataTable = ds.Tables[0];
dataTable.TableName = tableName;
idbCommand.Parameters.Clear();
return dataTable;
}
catch
{
throw;
}
}
}
#endregion
}