一个C#实现的数据库访问帮助类DatabaseHelper C#

自己实现的数据库访问帮助类库，DataBaseAccessHelper。

支持MySQL、SQL Server，Oracle等数据库类型；
支持多种数据库访问方式；
支持单行记录中某个字段、整行记录及数据集的查询操作；
支持SQL命令执行操作（DML、DDL）；
支持存储过程；
支持异步读取数据库记录；

库中各个类说明：
DataBaseAccess：执行数据库访问的核心功能类；
AccessType：数据库访问方式，枚举类型；
DatabaseDefinitions：包含一些常量的定义；
DatabaseParam：数据库参数；
DatabaseAsyncState：异步执行数据查询时的状态；

AccessType：

/// /// 数据库访问类型 /// public enum AccessType { MySQLClient, MSSQLClient, OracleDirect, OracleTNS, MySQLODBC, MSSQLODBC, MSOracleODBC, OracleODBC, IBMDataDB2, DB2ODBC }

DatabaseDefinitions：

/// /// 定义数据库相关常量 /// 数据库类型常量 /// 数据库端口常量等 /// public class DatabaseDefinitions { public const int DATABASE_TYPE_MYSQL = 1; public const int DATABASE_TYPE_MSSQL = 2; public const int DATABASE_TYPE_ORACLE = 3; public const int DATABASE_TYPE_SYBASE = 4; public const int DATABASE_TYPE_DB2 = 5; public const int DATABASE_DEFAULT_PORT_MYSQL = 3306; public const int DATABASE_DEFAULT_PORT_MSSQL = 1433; public const int DATABASE_DEFAULT_PORT_ORACLE = 1521; public const int DATABASE_DEFAULT_PORT_SYBASE = 5000; public const int DATABASE_DEFAULT_PORT_DB2 = 50000; public const string DATABASE_RETURN_ERROR = "\0x07\0x07\0x07\0x07\0x07"; }

DatabaseParam：

/// /// 数据库参数 /// public class DatabaseParam { /// /// 数据库类型 /// 1：MySQL /// 2：SQL Server /// 3：Oracle /// 4：Sybase /// 5：DB2 /// public int DBType { get; set; } /// /// 数据库服务器地址 /// public string DBHost { get; set; } /// /// 端口 /// public int DBPort { get; set; } /// /// 数据库名 /// public string DBName { get; set; } /// /// 登录用户名 /// public string DBUser { get; set; } /// /// 登录密码 /// public string DBPassword { get; set; }/// /// 默认数据库参数 /// 数据库类型：MySQL /// 端口：3306 /// 其他为空 /// public DatabaseParam() { DBType = 1; DBHost = ""; DBPort = 3306; DBName = ""; DBUser = ""; DBPassword = ""; } /// /// 指定数据库类型、主机、端口、数据库名、登录名和密码 /// /// 数据库类型 /// 主机 /// 端口 /// 数据库名 /// 登录名 /// 密码 public DatabaseParam(int db_type, string db_host, int db_port, string db_name, string db_user, string db_pass) { DBType = db_type; DBHost = db_host; DBPort = db_port; DBName = db_name; DBUser = db_user; DBPassword = db_pass; } /// /// 指定数据库类型、主机、数据库名、登录名和密码,端口根据数据库类型默认指定 /// /// 数据库类型 /// 主机 /// 数据库名 /// 【一个C#实现的数据库访问帮助类DatabaseHelper】登录名 /// 密码 public DatabaseParam(int db_type, string db_host, string db_name, string db_user, string db_pass) { DBType = db_type; DBHost = db_host; switch (db_type) { case 1: DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MYSQL; break; case 2: DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MSSQL; break; case 3: DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_ORACLE; break; case 4: DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_SYBASE; break; case 5: DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_DB2; break; default: DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MYSQL; break; } DBName = db_name; DBUser = db_user; DBPassword = db_pass; } /// /// 指定参数数组 /// /// /// 0：数据库类型 /// 1：数据库服务器地址 /// 2：端口 /// 3：数据库名 /// 4：登录名 /// 5：密码 /// public DatabaseParam(string[] db_params) { int db_type, db_port; DBType = int.TryParse(db_params[0], out db_type) == true ? db_type : 1; DBHost = db_params[1]; DBPort = int.TryParse(db_params[2], out db_port) == true ? db_type : 3306; DBName = db_params[3]; DBUser = db_params[4]; DBPassword = db_params[5]; } }

DatabaseAsyncState：

/// /// 异步执行状态 /// public class DatabaseAsyncState { /// /// IDbCommand对象 /// public IDbCommand DbCommand { get; set; } /// /// IDataReader对象 /// public IDataReader DataReader { get; set; } }

DataBaseAccess：

/// /// 访问数据库，提供连接、查询、执行SQL等数据库访问功能 /// author:Charley /// date:2012/4/30 /// public class DataBaseAccess { private DatabaseParam m_databaseparam; /// /// 获取或设置数据库参数 /// public DatabaseParam DatabaseParam { get { return m_databaseparam; } set { m_databaseparam = value; } } private string m_errormsg; /// /// 获取内部操作操作错误信息，得到上一步操作的错误消息 /// public string ErrorMsg { get { return m_errormsg; } } private string m_connectionstring; /// /// 获取或设置数据库连接字符串 /// public string ConnectionString { get { return m_connectionstring; } set { m_connectionstring = value; } } private AccessType m_accessType; /// /// 设置数据库访问方式 /// public AccessType AccessType { set { m_accessType = value; } }private string m_providername; /// /// 设置数据提供者名称,用于ODBC连接 /// public string ProviderName { //get { return m_providername; } set { m_providername = value; } } private int m_commandtimeout; /// /// 设置命令执行的超时时间，0：不限制，即采用数据库默认值 /// public int CommandTimeout { set { m_commandtimeout = value; } } private int m_connectiontimeout; /// /// 设置数据库连接超时时间，0：不限制，即采用数据库默认值 /// public int ConnectionTimeout { set { m_connectiontimeout = value; } } /// ///使用默认值构造 /// public DataBaseAccess() { m_databaseparam = new DatabaseParam(); m_accessType = AccessType.MySQLClient; m_errormsg = string.Empty; m_connectionstring = string.Empty; m_providername = string.Empty; m_commandtimeout = 0; m_connectiontimeout = 0; } /// /// 指定数据库参数构造 /// /// 数据库参数 public DataBaseAccess(DatabaseParam database_param) : this() { m_databaseparam = database_param; } /// /// 指定数据库参数及数据库访问类型构造 /// /// 数据库参数 /// 数据库访问类型 public DataBaseAccess(DatabaseParam database_param, AccessType access_type) : this() { m_databaseparam = database_param; m_accessType = access_type; } /// /// 清除内部错误消息 /// public void ClearMessage() { m_errormsg = string.Empty; } /// /// 测试数据库连接 /// 连接成功：true /// 连接失败：False /// /// public bool TestConnection() { bool b_return = false; if (m_connectionstring.Equals(string.Empty)) { BuildConnectionString(); } IDbConnection obj_connection = GetDBConnection(); obj_connection.ConnectionString = m_connectionstring; try { obj_connection.Open(); b_return = true; } catch (Exception ex) { m_errormsg = ex.ToString(); } finally { if (obj_connection.State == ConnectionState.Open) { obj_connection.Close(); } } return b_return; } /// /// 获取记录行数 /// 返回 -1 指示操作有错误 /// /// 查询语句 /// public int GetRecordNum(string str_sql) { DataSet obj_ds = GetDataSet(str_sql); if (obj_ds == null || obj_ds.Tables.Count <= 0) { return -1; } return obj_ds.Tables[0].Rows.Count; } /// /// 获取指定列的值 /// 返回 DATABASE_RETURN_ERROR 指示操作有错误 /// /// 查询语句 /// 列名 /// public string GetStringValue(string column_name, string str_sql) { string s_return = string.Empty; DataRow obj_dr = GetFirstRecord(str_sql); if (obj_dr == null) { s_return = DatabaseDefinitions.DATABASE_RETURN_ERROR; return s_return; } try { s_return = obj_dr[column_name].ToString(); } catch (Exception ex) { m_errormsg = ex.ToString(); s_return = DatabaseDefinitions.DATABASE_RETURN_ERROR; return s_return; } return s_return; } /// /// 获取第一条记录 /// 返回 null 指示操作有错误 /// /// 查询语句 /// public DataRow GetFirstRecord(string str_sql) { DataSet obj_ds = GetDataSet(str_sql); if (obj_ds == null || obj_ds.Tables.Count <= 0) { return null; } int count = obj_ds.Tables[0].Rows.Count; if (count == 0) { m_errormsg = "No Record."; return null; } return obj_ds.Tables[0].Rows[0]; } /// /// 获取数据集 /// 返回 null 指示操作有错误 /// /// 查询语句 /// public DataSet GetDataSet(string str_sql) { DataSet obj_ds = new DataSet(); if (m_connectionstring.Equals(string.Empty)) { BuildConnectionString(); } IDataAdapter obj_dapt = GetDataAdapter(str_sql); try { obj_dapt.Fill(obj_ds); if (obj_ds.Tables.Count <= 0) { m_errormsg = "No table."; obj_ds = null; } } catch (Exception ex) { m_errormsg = ex.ToString(); obj_ds = null; } return obj_ds; }/// /// 执行SQL命令，返回受影响的行数 /// 返回 -2 指示操作有错误 /// 返回 -1 指示执行DDL命令 /// /// SQL命令 /// public int ExecuteCommand(string str_sql) { int i_return = -2; if (m_connectionstring.Equals(string.Empty)) { BuildConnectionString(); } IDbConnection obj_con = GetDBConnection(); obj_con.ConnectionString = m_connectionstring; IDbCommand obj_cmd = GetDBCommand(); try { obj_con.Open(); obj_cmd.Connection = obj_con; obj_cmd.CommandText = str_sql; return obj_cmd.ExecuteNonQuery(); } catch (Exception ex) { m_errormsg = ex.ToString(); i_return = -2; } finally { if (obj_con.State == ConnectionState.Open) { obj_con.Close(); } } return i_return; } /// ///执行存储过程 /// /// DataParameter参数类型，实现IDataParameter接口 /// 存储过程名称 /// 参数列表 /// public bool ExecuteProcedure(string proc_name, ref T[] parameters) where T : IDataParameter { bool b_return = false; if (m_connectionstring.Equals(string.Empty)) { BuildConnectionString(); } IDbConnection obj_con = GetDBConnection(); obj_con.ConnectionString = m_connectionstring; try { obj_con.Open(); IDbCommand obj_cmd = GetDBCommand(); obj_cmd.Connection = obj_con; obj_cmd.CommandType = CommandType.StoredProcedure; obj_cmd.CommandText = proc_name; foreach (T parameter in parameters) { obj_cmd.Parameters.Add(parameter); } obj_cmd.ExecuteNonQuery(); b_return = true; } catch (Exception ex) { m_errormsg = ex.ToString(); b_return = false; } finally { if (obj_con.State == ConnectionState.Open) { obj_con.Close(); } } return b_return; } /// /// 执行SQL命令，返回数据读取器DataReader /// /// SQL命令 /// public IDataReader ExecuteReader(string str_sql) { IDataReader obj_dr; if (m_connectionstring.Equals(string.Empty)) { BuildConnectionString(); } IDbConnection obj_con = GetDBConnection(); obj_con.ConnectionString = m_connectionstring; try { obj_con.Open(); IDbCommand obj_cmd = GetDBCommand(); obj_cmd.Connection = obj_con; obj_cmd.CommandText = str_sql; obj_dr = obj_cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { m_errormsg = ex.ToString(); obj_dr = null; } return obj_dr; } /// /// 异步执行Sql查询 /// /// sql查询 /// 回调函数，包含DatabaseAsyncState /// public bool BeginExecuteReader(string str_sql, Action