自己实现的数据库访问帮助类库，DataBaseAccessHelper。
支持MySQL、SQL Server，Oracle等数据库类型；
支持多种数据库访问方式；
支持单行记录中某个字段、整行记录及数据集的查询操作；
支持SQL命令执行操作（DML、DDL）；
支持存储过程；
支持异步读取数据库记录；
库中各个类说明：
DataBaseAccess：执行数据库访问的核心功能类；
AccessType：数据库访问方式，枚举类型；
DatabaseDefinitions：包含一些常量的定义；
DatabaseParam：数据库参数；
DatabaseAsyncState：异步执行数据查询时的状态；
AccessType：
/// <summary>
/// 数据库访问类型
/// </summary>
public enum AccessType
{
MySQLClient,
MSSQLClient,
OracleDirect,
OracleTNS,
MySQLODBC,
MSSQLODBC,
MSOracleODBC,
OracleODBC,
IBMDataDB2,
DB2ODBC
}
DatabaseDefinitions：
/// <summary>
/// 定义数据库相关常量
/// 数据库类型常量
/// 数据库端口常量等
/// </summary>
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：
/// <summary>
/// 数据库参数
/// </summary>
public class DatabaseParam
{
/// <summary>
/// 数据库类型
/// 1：MySQL
/// 2：SQL Server
/// 3：Oracle
/// 4：Sybase
/// 5：DB2
/// </summary>
public int DBType { get; set; }
/// <summary>
/// 数据库服务器地址
/// </summary>
public string DBHost { get; set; }
/// <summary>
/// 端口
/// </summary>
public int DBPort { get; set; }
/// <summary>
/// 数据库名
/// </summary>
public string DBName { get; set; }
/// <summary>
/// 登录用户名
/// </summary>
public string DBUser { get; set; }
/// <summary>
/// 登录密码
/// </summary>
public string DBPassword { get; set; }
/// <summary>
/// 默认数据库参数
/// 数据库类型：MySQL
/// 端口：3306
/// 其他为空
/// </summary>
public DatabaseParam()
{
DBType = 1;
DBHost = "";
DBPort = 3306;
DBName = "";
DBUser = "";
DBPassword = "";
}
/// <summary>
/// 指定数据库类型、主机、端口、数据库名、登录名和密码
/// </summary>
/// <param name="db_type">数据库类型</param>
/// <param name="db_host">主机</param>
/// <param name="db_port">端口</param>
/// <param name="db_name">数据库名</param>
/// <param name="db_user">登录名</param>
/// <param name="db_pass">密码</param>
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;
}
/// <summary>
/// 指定数据库类型、主机、数据库名、登录名和密码,端口根据数据库类型默认指定
/// </summary>
/// <param name="db_type">数据库类型</param>
/// <param name="db_host">主机</param>
/// <param name="db_name">数据库名</param>
/// <param name="db_user">登录名</param>
/// <param name="db_pass">密码</param>
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;
}
/// <summary>
/// 指定参数数组
/// </summary>
/// <param name="db_params">
/// 0：数据库类型
/// 1：数据库服务器地址
/// 2：端口
/// 3：数据库名
/// 4：登录名
/// 5：密码
/// </param>
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：
/// <summary>
/// 异步执行状态
/// </summary>
public class DatabaseAsyncState
{
/// <summary>
/// IDbCommand对象
/// </summary>
public IDbCommand DbCommand { get; set; }
/// <summary>
/// IDataReader对象
/// </summary>
public IDataReader DataReader { get; set; }
}
DataBaseAccess：
/// <summary>
/// 访问数据库，提供连接、查询、执行SQL等数据库访问功能
/// author:Charley
/// date:2012/4/30
/// </summary>
public class DataBaseAccess
{
private DatabaseParam m_databaseparam;
/// <summary>
/// 获取或设置数据库参数
/// </summary>
public DatabaseParam DatabaseParam
{
get { return m_databaseparam; }
set { m_databaseparam = value; }
}
private string m_errormsg;
/// <summary>
/// 获取内部操作操作错误信息，得到上一步操作的错误消息
/// </summary>
public string ErrorMsg
{
get { return m_errormsg; }
}
private string m_connectionstring;
/// <summary>
/// 获取或设置数据库连接字符串
/// </summary>
public string ConnectionString
{
get { return m_connectionstring; }
set { m_connectionstring = value; }
}
private AccessType m_accessType;
/// <summary>
/// 设置数据库访问方式
/// </summary>
public AccessType AccessType
{
set { m_accessType = value; }
}
private string m_providername;
/// <summary>
/// 设置数据提供者名称,用于ODBC连接
/// </summary>
public string ProviderName
{
//get { return m_providername; }
set { m_providername = value; }
}
private int m_commandtimeout;
/// <summary>
/// 设置命令执行的超时时间，0：不限制，即采用数据库默认值
/// </summary>
public int CommandTimeout
{
set { m_commandtimeout = value; }
}
private int m_connectiontimeout;
/// <summary>
/// 设置数据库连接超时时间，0：不限制，即采用数据库默认值
/// </summary>
public int ConnectionTimeout
{
set { m_connectiontimeout = value; }
}
/// <summary>
///使用默认值构造
/// </summary>
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;
}
/// <summary>
/// 指定数据库参数构造
/// </summary>
/// <param name="database_param">数据库参数</param>
public DataBaseAccess(DatabaseParam database_param)
: this()
{
m_databaseparam = database_param;
}
/// <summary>
/// 指定数据库参数及数据库访问类型构造
/// </summary>
/// <param name="database_param">数据库参数</param>
/// <param name="access_type">数据库访问类型</param>
public DataBaseAccess(DatabaseParam database_param, AccessType access_type)
: this()
{
m_databaseparam = database_param;
m_accessType = access_type;
}
/// <summary>
/// 清除内部错误消息
/// </summary>
public void ClearMessage()
{
m_errormsg = string.Empty;
}
/// <summary>
/// 测试数据库连接
/// 连接成功：true
/// 连接失败：False
/// </summary>
/// <returns></returns>
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;
}
/// <summary>
/// 获取记录行数
/// 返回 -1 指示操作有错误
/// </summary>
/// <param name="str_sql">查询语句</param>
/// <returns></returns>
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;
}
/// <summary>
/// 获取指定列的值
/// 返回 DATABASE_RETURN_ERROR 指示操作有错误
/// </summary>
/// <param name="str_sql">查询语句</param>
/// <param name="column_name">列名</param>
/// <returns></returns>
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;
}
/// <summary>
/// 获取第一条记录
/// 返回 null 指示操作有错误
/// </summary>
/// <param name="str_sql">查询语句</param>
/// <returns></returns>
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];
}
/// <summary>
/// 获取数据集
/// 返回 null 指示操作有错误
/// </summary>
/// <param name="str_sql">查询语句</param>
/// <returns></returns>
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;
}
/// <summary>
/// 执行SQL命令，返回受影响的行数
/// 返回 -2 指示操作有错误
/// 返回 -1 指示执行DDL命令
/// </summary>
/// <param name="str_sql">SQL命令</param>
/// <returns></returns>
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;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <typeparam name="T">DataParameter参数类型，实现IDataParameter接口</typeparam>
/// <param name="proc_name">存储过程名称</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public bool ExecuteProcedure<T>(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;
}
/// <summary>
/// 执行SQL命令，返回数据读取器DataReader
/// </summary>
/// <param name="str_sql">SQL命令</param>
/// <returns></returns>
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;
}
/// <summary>
/// 异步执行Sql查询
/// </summary>
/// <param name="str_sql">sql查询</param>
/// <param name="callback">回调函数，包含DatabaseAsyncState</param>
/// <returns></returns>
public bool BeginExecuteReader(string str_sql, Action<object> callback)
{
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);
DatabaseAsyncState async_state = new DatabaseAsyncState();
async_state.DbCommand = obj_cmd;
async_state.DataReader = obj_dr;
System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(callback));
thread.Start(async_state);
return true;
}
catch (Exception ex)
{
m_errormsg = ex.ToString();
return false;
}
}
/// <summary>
/// 试图取消IDbCommand的执行
/// </summary>
/// <param name="command">ICommand对象</param>
public void Cancel(IDbCommand command)
{
command.Cancel();
}
/// <summary>
/// 根据数据库访问方式构造数据库连接字符串
/// </summary>
public void BuildConnectionString()
{
switch (m_accessType)
{
case AccessType.MySQLClient:
m_connectionstring = "Server=" + m_databaseparam.DBHost + ";Port=" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword + ";";
if (m_commandtimeout > 0)
{
m_connectionstring += "Default command timeout=" + m_commandtimeout.ToString() + ";";
}
if (m_connectiontimeout > 0)
{
m_connectionstring += "Connection timeout=" + m_connectiontimeout + ";";
}
break;
case AccessType.MSSQLClient:
m_connectionstring = "Data Source=" + m_databaseparam.DBHost + "," + m_databaseparam.DBPort + ";Initial Catalog=" + m_databaseparam.DBName + ";User ID=" + m_databaseparam.DBUser + ";Password=" + m_databaseparam.DBPassword + ";";
break;
case AccessType.OracleDirect:
m_connectionstring = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + m_databaseparam.DBHost + ")(PORT=" + m_databaseparam.DBPort + ")))(CONNECT_DATA=(SERVICE_NAME=" + m_databaseparam.DBName + ")));User Id=" + m_databaseparam.DBUser + ";Password=" + m_databaseparam.DBPassword + ";";
break;
case AccessType.OracleTNS:
m_connectionstring = "Data Source=" + m_databaseparam.DBName + ";User Id=" + m_databaseparam.DBUser + ";Password=" + m_databaseparam.DBPassword + ";";
break;
case AccessType.IBMDataDB2:
m_connectionstring = "Server=" + m_databaseparam.DBHost + ":" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName + ";userid=" + m_databaseparam.DBUser + ";password=" + m_databaseparam.DBPassword+";";
break;
case AccessType.MySQLODBC:
m_connectionstring = "Driver={" + m_providername + "};Server=" + m_databaseparam.DBHost + ";Port=" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword + ";";
break;
case AccessType.MSSQLODBC:
m_connectionstring = "Driver={" + m_providername + "};Server=" + m_databaseparam.DBHost + ";Port=" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword + ";";
break;
case AccessType.MSOracleODBC:
m_connectionstring = "Driver={" + m_providername + "};Server=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword + ";";
break;
case AccessType.OracleODBC:
m_connectionstring = "Driver={" + m_providername + "};Server=" + m_databaseparam.DBHost + ";Dbq=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword + ";";
break;
case AccessType.DB2ODBC:
m_connectionstring = "DRIVER={"+m_providername+"};UID=" + m_databaseparam.DBUser + ";PWD=" + m_databaseparam.DBPassword + ";PROTOCOL=TCPIP;HOSTNAME=" + m_databaseparam.DBHost + ";DATABASE=" + m_databaseparam.DBName + ";PORT=" + m_databaseparam.DBPort + ";";
break;
default:
m_errormsg = "Access type not support.";
break;
}
}
private IDbConnection GetDBConnection()
{
switch (m_accessType)
{
case AccessType.MySQLClient:
MySqlConnection mysql_connection = new MySqlConnection();
return mysql_connection;
case AccessType.MSSQLClient:
SqlConnection mssql_connection = new SqlConnection();
return mssql_connection;
case AccessType.OracleDirect:
case AccessType.OracleTNS:
OracleConnection oracle_connection = new OracleConnection();
return oracle_connection;
case AccessType.IBMDataDB2:
DB2Connection db2_connection = new DB2Connection();
return db2_connection;
case AccessType.MySQLODBC:
case AccessType.MSSQLODBC:
case AccessType.OracleODBC:
case AccessType.MSOracleODBC:
case AccessType.DB2ODBC:
OdbcConnection odbc_connection = new OdbcConnection();
return odbc_connection;
default:
m_errormsg = "Access type not support.";
return null;
}
}
private IDataAdapter GetDataAdapter(string str_sql)
{
switch (m_accessType)
{
case AccessType.MySQLClient:
MySqlConnection mysql_connection = new MySqlConnection(m_connectionstring);
MySqlDataAdapter mysql_dapt = new MySqlDataAdapter(str_sql, mysql_connection);
return mysql_dapt;
case AccessType.MSSQLClient:
SqlConnection mssql_connection = new SqlConnection(m_connectionstring);
SqlDataAdapter mssql_dapt = new SqlDataAdapter(str_sql, mssql_connection);
return mssql_dapt;
case AccessType.OracleDirect:
case AccessType.OracleTNS:
OracleConnection oracle_connection = new OracleConnection(m_connectionstring);
OracleDataAdapter oracle_dapt = new OracleDataAdapter(str_sql, oracle_connection);
return oracle_dapt;
case AccessType.IBMDataDB2:
DB2Connection db2_connection = new DB2Connection(m_connectionstring);
DB2DataAdapter db2_dapt = new DB2DataAdapter(str_sql, db2_connection);
return db2_dapt;
case AccessType.MySQLODBC:
case AccessType.MSSQLODBC:
case AccessType.OracleODBC:
case AccessType.MSOracleODBC:
case AccessType.DB2ODBC:
OdbcConnection odbc_connection = new OdbcConnection(m_connectionstring);
OdbcDataAdapter odbc_dapt = new OdbcDataAdapter(str_sql, odbc_connection);
return odbc_dapt;
default:
m_errormsg = "Access type not support.";
return null;
}
}
private IDbCommand GetDBCommand()
{
switch (m_accessType)
{
case AccessType.MySQLClient:
MySqlCommand mysql_cmd = new MySqlCommand();
return mysql_cmd;
case AccessType.MSSQLClient:
SqlCommand mssql_cmd = new SqlCommand();
return mssql_cmd;
case AccessType.OracleDirect:
case AccessType.OracleTNS:
OracleCommand oracle_cmd = new OracleCommand();
return oracle_cmd;
case AccessType.IBMDataDB2:
DB2Command db2_cmd = new DB2Command();
return db2_cmd;
case AccessType.MySQLODBC:
case AccessType.MSSQLODBC:
case AccessType.OracleODBC:
case AccessType.MSOracleODBC:
case AccessType.DB2ODBC:
OdbcCommand odbc_cmd = new OdbcCommand();
return odbc_cmd;
default:
m_errormsg = "Access type not support.";
return null;
}
}
}
使用示例：
DatabaseParam database_param = new DatabaseParam();
database_param.DBType = 1;
database_param.DBHost = txt_serverName.Text;
database_param.DBPort = int.Parse(txt_serverPort.Text);
database_param.DBName = txt_dbName.Text;
database_param.DBUser = txt_loginName.Text;
database_param.DBPassword = txt_loginPwd.Password;
App.G_VMCDatabaseHelper = new DataBaseAccess(database_param,AccessType.MySQLClient);
App.G_VMCDatabaseHelper.ClearMessage();
if (App.G_VMCDatabaseHelper.TestConnection())
{
}
else
{
App.G_LogOperator.WriteOperationLog("WLogin->Login", "Connect to database fail.\r\n" + App.G_VMCDatabaseHelper.ErrorMsg);
ShowErrorMessageBox("Connect to database fail.");
return;
}