C#実装データベースアクセスヘルプクラスDatabaseHelper
27889 ワード
独自に実装されたデータベース・アクセス・ヘルプ・クラス・ライブラリ、DataBaseAccessHelper.
MySQL、SQL Server、Oracleなどのデータベースタイプをサポートする.
複数のデータベースアクセス方式をサポートする.
単行レコードのフィールド、行全体のレコード、データセットのクエリー操作をサポートします.
SQLコマンド実行操作(DML、DDL)をサポートする.
ストレージ・プロシージャをサポートします.
非同期でデータベースレコードを読み込むことをサポートします.
ライブラリ内の各クラスの説明:
DataBaseAccess:データベースアクセスを実行するコア機能クラス;
AccessType:データベースアクセス方式、列挙タイプ;
DatabaseDefinitions:定数の定義が含まれています.
DatabaseParam:データベース・パラメータ;
DatabaseAsyncState:データクエリを非同期で実行したときの状態;
AccessType:
DatabaseDefinitions:
DatabaseParam:
DatabaseAsyncState:
DataBaseAccess:
使用例:
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;
}
///
/// 、 、 、 ,
///
///
///
///
///
///
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 使用例:
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
" + App.G_VMCDatabaseHelper.ErrorMsg);
ShowErrorMessageBox("Connect to database fail.");
return;
}