程序博客网 > 西岐网络 彭永辉

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

来源：互联网 发布：西岐网络 彭永辉 编辑：程序博客网 时间：2024/06/14 11:19

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

支持MySQL、SQL Server，Oracle等数据库类型；

支持多种数据库访问方式；

支持单行记录中某个字段、整行记录及数据集的查询操作；

支持SQL命令执行操作（DML、DDL）；

支持存储过程；

支持异步读取数据库记录；

库中各个类说明：

DataBaseAccess：执行数据库访问的核心功能类；

AccessType：数据库访问方式，枚举类型；

DatabaseDefinitions：包含一些常量的定义；

DatabaseParam：数据库参数；

DatabaseAsyncState：异步执行数据查询时的状态；

AccessType：

[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 数据库访问类型
  3. /// </summary>
  4. public enum AccessType
  5. {
  6. MySQLClient,
  7. MSSQLClient,
  8. OracleDirect,
  9. OracleTNS,
  10. MySQLODBC,
  11. MSSQLODBC,
  12. MSOracleODBC,
  13. OracleODBC,
  14. IBMDataDB2,
  15. DB2ODBC
  16. }
[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 数据库访问类型
  3. /// </summary>
  4. public enum AccessType
  5. {
  6. MySQLClient,
  7. MSSQLClient,
  8. OracleDirect,
  9. OracleTNS,
  10. MySQLODBC,
  11. MSSQLODBC,
  12. MSOracleODBC,
  13. OracleODBC,
  14. IBMDataDB2,
  15. DB2ODBC
  16. }


DatabaseDefinitions：

[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 定义数据库相关常量
  3. /// 数据库类型常量
  4. /// 数据库端口常量等
  5. /// </summary>
  6. public class DatabaseDefinitions
  7. {
  8. public constint DATABASE_TYPE_MYSQL = 1;
  9. public constint DATABASE_TYPE_MSSQL = 2;
  10. public constint DATABASE_TYPE_ORACLE = 3;
  11. public constint DATABASE_TYPE_SYBASE = 4;
  12. public constint DATABASE_TYPE_DB2 = 5;
  14. public constint DATABASE_DEFAULT_PORT_MYSQL = 3306;
  15. public constint DATABASE_DEFAULT_PORT_MSSQL = 1433;
  16. public constint DATABASE_DEFAULT_PORT_ORACLE = 1521;
  17. public constint DATABASE_DEFAULT_PORT_SYBASE = 5000;
  18. public constint DATABASE_DEFAULT_PORT_DB2 = 50000;
  20. public conststring DATABASE_RETURN_ERROR = "\0x07\0x07\0x07\0x07\0x07";
  21. }
[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 定义数据库相关常量
  3. /// 数据库类型常量
  4. /// 数据库端口常量等
  5. /// </summary>
  6. public class DatabaseDefinitions
  7. {
  8. public constint DATABASE_TYPE_MYSQL = 1;
  9. public constint DATABASE_TYPE_MSSQL = 2;
  10. public constint DATABASE_TYPE_ORACLE = 3;
  11. public constint DATABASE_TYPE_SYBASE = 4;
  12. public constint DATABASE_TYPE_DB2 = 5;
  14. public constint DATABASE_DEFAULT_PORT_MYSQL = 3306;
  15. public constint DATABASE_DEFAULT_PORT_MSSQL = 1433;
  16. public constint DATABASE_DEFAULT_PORT_ORACLE = 1521;
  17. public constint DATABASE_DEFAULT_PORT_SYBASE = 5000;
  18. public constint DATABASE_DEFAULT_PORT_DB2 = 50000;
  20. public conststring DATABASE_RETURN_ERROR = "\0x07\0x07\0x07\0x07\0x07";
  21. }


DatabaseParam：

[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 数据库参数
  3. /// </summary>
  4. public class DatabaseParam
  5. {
  6. /// <summary>
  7. /// 数据库类型
  8. /// 1：MySQL
  9. /// 2：SQL Server
  10. /// 3：Oracle
  11. /// 4：Sybase
  12. /// 5：DB2
  13. /// </summary>
  14. public int DBType {get; set; }
  15. /// <summary>
  16. /// 数据库服务器地址
  17. /// </summary>
  18. public string DBHost {get; set; }
  19. /// <summary>
  20. /// 端口
  21. /// </summary>
  22. public int DBPort {get; set; }
  23. /// <summary>
  24. /// 数据库名
  25. /// </summary>
  26. public string DBName {get; set; }
  27. /// <summary>
  28. /// 登录用户名
  29. /// </summary>
  30. public string DBUser {get; set; }
  31. /// <summary>
  32. /// 登录密码
  33. /// </summary>
  34. public string DBPassword {get; set; }
  36. /// <summary>
  37. /// 默认数据库参数
  38. /// 数据库类型：MySQL
  39. /// 端口：3306
  40. /// 其他为空
  41. /// </summary>
  42. public DatabaseParam()
  43. {
  44. DBType = 1;
  45. DBHost = "";
  46. DBPort = 3306;
  47. DBName = "";
  48. DBUser = "";
  49. DBPassword = "";
  50. }
  51. /// <summary>
  52. /// 指定数据库类型、主机、端口、数据库名、登录名和密码
  53. /// </summary>
  54. /// <param name="db_type">数据库类型</param>
  55. /// <param name="db_host">主机</param>
  56. /// <param name="db_port">端口</param>
  57. /// <param name="db_name">数据库名</param>
  58. /// <param name="db_user">登录名</param>
  59. /// <param name="db_pass">密码</param>
  60. public DatabaseParam(int db_type,string db_host, int db_port,string db_name, string db_user,string db_pass)
  61. {
  62. DBType = db_type;
  63. DBHost = db_host;
  64. DBPort = db_port;
  65. DBName = db_name;
  66. DBUser = db_user;
  67. DBPassword = db_pass;
  68. }
  69. /// <summary>
  70. /// 指定数据库类型、主机、数据库名、登录名和密码,端口根据数据库类型默认指定
  71. /// </summary>
  72. /// <param name="db_type">数据库类型</param>
  73. /// <param name="db_host">主机</param>
  74. /// <param name="db_name">数据库名</param>
  75. /// <param name="db_user">登录名</param>
  76. /// <param name="db_pass">密码</param>
  77. public DatabaseParam(int db_type,string db_host, string db_name,string db_user, string db_pass)
  78. {
  79. DBType = db_type;
  80. DBHost = db_host;
  81. switch (db_type)
  82. {
  83. case 1:
  84. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MYSQL;
  85. break;
  86. case 2:
  87. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MSSQL;
  88. break;
  89. case 3:
  90. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_ORACLE;
  91. break;
  92. case 4:
  93. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_SYBASE;
  94. break;
  95. case 5:
  96. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_DB2;
  97. break;
  98. default:
  99. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MYSQL;
  100. break;
  101. }
  102. DBName = db_name;
  103. DBUser = db_user;
  104. DBPassword = db_pass;
  105. }
  106. /// <summary>
  107. /// 指定参数数组
  108. /// </summary>
  109. /// <param name="db_params">
  110. /// 0：数据库类型
  111. /// 1：数据库服务器地址
  112. /// 2：端口
  113. /// 3：数据库名
  114. /// 4：登录名
  115. /// 5：密码
  116. /// </param>
  117. public DatabaseParam(string[] db_params)
  118. {
  119. int db_type, db_port;
  120. DBType = int.TryParse(db_params[0],out db_type) == true ? db_type : 1;
  121. DBHost = db_params[1];
  122. DBPort = int.TryParse(db_params[2],out db_port) == true ? db_type : 3306;
  123. DBName = db_params[3];
  124. DBUser = db_params[4];
  125. DBPassword = db_params[5];
  126. }
  127. }
[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 数据库参数
  3. /// </summary>
  4. public class DatabaseParam
  5. {
  6. /// <summary>
  7. /// 数据库类型
  8. /// 1：MySQL
  9. /// 2：SQL Server
  10. /// 3：Oracle
  11. /// 4：Sybase
  12. /// 5：DB2
  13. /// </summary>
  14. public int DBType {get; set; }
  15. /// <summary>
  16. /// 数据库服务器地址
  17. /// </summary>
  18. public string DBHost {get; set; }
  19. /// <summary>
  20. /// 端口
  21. /// </summary>
  22. public int DBPort {get; set; }
  23. /// <summary>
  24. /// 数据库名
  25. /// </summary>
  26. public string DBName {get; set; }
  27. /// <summary>
  28. /// 登录用户名
  29. /// </summary>
  30. public string DBUser {get; set; }
  31. /// <summary>
  32. /// 登录密码
  33. /// </summary>
  34. public string DBPassword {get; set; }
  36. /// <summary>
  37. /// 默认数据库参数
  38. /// 数据库类型：MySQL
  39. /// 端口：3306
  40. /// 其他为空
  41. /// </summary>
  42. public DatabaseParam()
  43. {
  44. DBType = 1;
  45. DBHost = "";
  46. DBPort = 3306;
  47. DBName = "";
  48. DBUser = "";
  49. DBPassword = "";
  50. }
  51. /// <summary>
  52. /// 指定数据库类型、主机、端口、数据库名、登录名和密码
  53. /// </summary>
  54. /// <param name="db_type">数据库类型</param>
  55. /// <param name="db_host">主机</param>
  56. /// <param name="db_port">端口</param>
  57. /// <param name="db_name">数据库名</param>
  58. /// <param name="db_user">登录名</param>
  59. /// <param name="db_pass">密码</param>
  60. public DatabaseParam(int db_type,string db_host, int db_port,string db_name, string db_user,string db_pass)
  61. {
  62. DBType = db_type;
  63. DBHost = db_host;
  64. DBPort = db_port;
  65. DBName = db_name;
  66. DBUser = db_user;
  67. DBPassword = db_pass;
  68. }
  69. /// <summary>
  70. /// 指定数据库类型、主机、数据库名、登录名和密码,端口根据数据库类型默认指定
  71. /// </summary>
  72. /// <param name="db_type">数据库类型</param>
  73. /// <param name="db_host">主机</param>
  74. /// <param name="db_name">数据库名</param>
  75. /// <param name="db_user">登录名</param>
  76. /// <param name="db_pass">密码</param>
  77. public DatabaseParam(int db_type,string db_host, string db_name,string db_user, string db_pass)
  78. {
  79. DBType = db_type;
  80. DBHost = db_host;
  81. switch (db_type)
  82. {
  83. case 1:
  84. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MYSQL;
  85. break;
  86. case 2:
  87. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MSSQL;
  88. break;
  89. case 3:
  90. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_ORACLE;
  91. break;
  92. case 4:
  93. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_SYBASE;
  94. break;
  95. case 5:
  96. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_DB2;
  97. break;
  98. default:
  99. DBPort = DatabaseDefinitions.DATABASE_DEFAULT_PORT_MYSQL;
  100. break;
  101. }
  102. DBName = db_name;
  103. DBUser = db_user;
  104. DBPassword = db_pass;
  105. }
  106. /// <summary>
  107. /// 指定参数数组
  108. /// </summary>
  109. /// <param name="db_params">
  110. /// 0：数据库类型
  111. /// 1：数据库服务器地址
  112. /// 2：端口
  113. /// 3：数据库名
  114. /// 4：登录名
  115. /// 5：密码
  116. /// </param>
  117. public DatabaseParam(string[] db_params)
  118. {
  119. int db_type, db_port;
  120. DBType = int.TryParse(db_params[0],out db_type) == true ? db_type : 1;
  121. DBHost = db_params[1];
  122. DBPort = int.TryParse(db_params[2],out db_port) == true ? db_type : 3306;
  123. DBName = db_params[3];
  124. DBUser = db_params[4];
  125. DBPassword = db_params[5];
  126. }
  127. }


DatabaseAsyncState：

[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 异步执行状态
  3. /// </summary>
  4. public class DatabaseAsyncState
  5. {
  6. /// <summary>
  7. /// IDbCommand对象
  8. /// </summary>
  9. public IDbCommand DbCommand {get; set; }
  10. /// <summary>
  11. /// IDataReader对象
  12. /// </summary>
  13. public IDataReader DataReader {get; set; }
  14. }
[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 异步执行状态
  3. /// </summary>
  4. public class DatabaseAsyncState
  5. {
  6. /// <summary>
  7. /// IDbCommand对象
  8. /// </summary>
  9. public IDbCommand DbCommand {get; set; }
  10. /// <summary>
  11. /// IDataReader对象
  12. /// </summary>
  13. public IDataReader DataReader {get; set; }
  14. }


DataBaseAccess：

[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 访问数据库，提供连接、查询、执行SQL等数据库访问功能
  3. /// author:Charley
  4. /// date:2012/4/30
  5. /// </summary>
  6. public class DataBaseAccess
  7. {
  8. private DatabaseParam m_databaseparam;
  9. /// <summary>
  10. /// 获取或设置数据库参数
  11. /// </summary>
  12. public DatabaseParam DatabaseParam
  13. {
  14. get { return m_databaseparam; }
  15. set { m_databaseparam = value; }
  16. }
  17. private string m_errormsg;
  18. /// <summary>
  19. /// 获取内部操作操作错误信息，得到上一步操作的错误消息
  20. /// </summary>
  21. public string ErrorMsg
  22. {
  23. get { return m_errormsg; }
  24. }
  25. private string m_connectionstring;
  26. /// <summary>
  27. /// 获取或设置数据库连接字符串
  28. /// </summary>
  29. public string ConnectionString
  30. {
  31. get { return m_connectionstring; }
  32. set { m_connectionstring = value; }
  33. }
  34. private AccessType m_accessType;
  35. /// <summary>
  36. /// 设置数据库访问方式
  37. /// </summary>
  38. public AccessType AccessType
  39. {
  40. set { m_accessType = value; }
  41. }
  43. private string m_providername;
  44. /// <summary>
  45. /// 设置数据提供者名称,用于ODBC连接
  46. /// </summary>
  47. public string ProviderName
  48. {
  49. //get { return m_providername; }
  50. set { m_providername = value; }
  51. }
  52. private int m_commandtimeout;
  53. /// <summary>
  54. /// 设置命令执行的超时时间，0：不限制，即采用数据库默认值
  55. /// </summary>
  56. public int CommandTimeout
  57. {
  58. set { m_commandtimeout = value; }
  59. }
  60. private int m_connectiontimeout;
  61. /// <summary>
  62. /// 设置数据库连接超时时间，0：不限制，即采用数据库默认值
  63. /// </summary>
  64. public int ConnectionTimeout
  65. {
  66. set { m_connectiontimeout = value; }
  67. }
  68. /// <summary>
  69. ///使用默认值构造
  70. /// </summary>
  71. public DataBaseAccess()
  72. {
  73. m_databaseparam = new DatabaseParam();
  74. m_accessType = AccessType.MySQLClient;
  75. m_errormsg = string.Empty;
  76. m_connectionstring = string.Empty;
  77. m_providername = string.Empty;
  78. m_commandtimeout = 0;
  79. m_connectiontimeout = 0;
  80. }
  81. /// <summary>
  82. /// 指定数据库参数构造
  83. /// </summary>
  84. /// <param name="database_param">数据库参数</param>
  85. public DataBaseAccess(DatabaseParam database_param)
  86. : this()
  87. {
  88. m_databaseparam = database_param;
  89. }
  90. /// <summary>
  91. /// 指定数据库参数及数据库访问类型构造
  92. /// </summary>
  93. /// <param name="database_param">数据库参数</param>
  94. /// <param name="access_type">数据库访问类型</param>
  95. public DataBaseAccess(DatabaseParam database_param, AccessType access_type)
  96. : this()
  97. {
  98. m_databaseparam = database_param;
  99. m_accessType = access_type;
  100. }
  101. /// <summary>
  102. /// 清除内部错误消息
  103. /// </summary>
  104. public void ClearMessage()
  105. {
  106. m_errormsg = string.Empty;
  107. }
  108. /// <summary>
  109. /// 测试数据库连接
  110. /// 连接成功：true
  111. /// 连接失败：False
  112. /// </summary>
  113. /// <returns></returns>
  114. public bool TestConnection()
  115. {
  116. bool b_return = false;
  117. if (m_connectionstring.Equals(string.Empty))
  118. {
  119. BuildConnectionString();
  120. }
  121. IDbConnection obj_connection = GetDBConnection();
  122. obj_connection.ConnectionString = m_connectionstring;
  123. try
  124. {
  125. obj_connection.Open();
  126. b_return = true;
  127. }
  128. catch (Exception ex)
  129. {
  130. m_errormsg = ex.ToString();
  131. }
  132. finally
  133. {
  134. if (obj_connection.State == ConnectionState.Open)
  135. {
  136. obj_connection.Close();
  137. }
  138. }
  139. return b_return;
  140. }
  141. /// <summary>
  142. /// 获取记录行数
  143. /// 返回 -1 指示操作有错误
  144. /// </summary>
  145. /// <param name="str_sql">查询语句</param>
  146. /// <returns></returns>
  147. public int GetRecordNum(string str_sql)
  148. {
  149. DataSet obj_ds = GetDataSet(str_sql);
  150. if (obj_ds == null || obj_ds.Tables.Count <= 0)
  151. {
  152. return -1;
  153. }
  154. return obj_ds.Tables[0].Rows.Count;
  155. }
  156. /// <summary>
  157. /// 获取指定列的值
  158. /// 返回 DATABASE_RETURN_ERROR 指示操作有错误
  159. /// </summary>
  160. /// <param name="str_sql">查询语句</param>
  161. /// <param name="column_name">列名</param>
  162. /// <returns></returns>
  163. public string GetStringValue(string column_name,string str_sql)
  164. {
  165. string s_return =string.Empty;
  166. DataRow obj_dr = GetFirstRecord(str_sql);
  167. if (obj_dr == null)
  168. {
  169. s_return = DatabaseDefinitions.DATABASE_RETURN_ERROR;
  170. return s_return;
  171. }
  172. try
  173. {
  174. s_return = obj_dr[column_name].ToString();
  175. }
  176. catch (Exception ex)
  177. {
  178. m_errormsg = ex.ToString();
  179. s_return = DatabaseDefinitions.DATABASE_RETURN_ERROR;
  180. return s_return;
  181. }
  182. return s_return;
  183. }
  184. /// <summary>
  185. /// 获取第一条记录
  186. /// 返回 null 指示操作有错误
  187. /// </summary>
  188. /// <param name="str_sql">查询语句</param>
  189. /// <returns></returns>
  190. public DataRow GetFirstRecord(string str_sql)
  191. {
  192. DataSet obj_ds = GetDataSet(str_sql);
  193. if (obj_ds == null || obj_ds.Tables.Count <= 0)
  194. {
  195. return null;
  196. }
  197. int count = obj_ds.Tables[0].Rows.Count;
  198. if (count == 0)
  199. {
  200. m_errormsg = "No Record.";
  201. return null;
  202. }
  203. return obj_ds.Tables[0].Rows[0];
  204. }
  205. /// <summary>
  206. /// 获取数据集
  207. /// 返回 null 指示操作有错误
  208. /// </summary>
  209. /// <param name="str_sql">查询语句</param>
  210. /// <returns></returns>
  211. public DataSet GetDataSet(string str_sql)
  212. {
  213. DataSet obj_ds = new DataSet();
  214. if (m_connectionstring.Equals(string.Empty))
  215. {
  216. BuildConnectionString();
  217. }
  218. IDataAdapter obj_dapt = GetDataAdapter(str_sql);
  219. try
  220. {
  221. obj_dapt.Fill(obj_ds);
  222. if (obj_ds.Tables.Count <= 0)
  223. {
  224. m_errormsg = "No table.";
  225. obj_ds = null;
  226. }
  227. }
  228. catch (Exception ex)
  229. {
  230. m_errormsg = ex.ToString();
  231. obj_ds = null;
  232. }
  233. return obj_ds;
  234. }
  236. /// <summary>
  237. /// 执行SQL命令，返回受影响的行数
  238. /// 返回 -2 指示操作有错误
  239. /// 返回 -1 指示执行DDL命令
  240. /// </summary>
  241. /// <param name="str_sql">SQL命令</param>
  242. /// <returns></returns>
  243. public int ExecuteCommand(string str_sql)
  244. {
  245. int i_return = -2;
  246. if (m_connectionstring.Equals(string.Empty))
  247. {
  248. BuildConnectionString();
  249. }
  250. IDbConnection obj_con = GetDBConnection();
  251. obj_con.ConnectionString = m_connectionstring;
  252. IDbCommand obj_cmd = GetDBCommand();
  253. try
  254. {
  255. obj_con.Open();
  256. obj_cmd.Connection = obj_con;
  257. obj_cmd.CommandText = str_sql;
  258. return obj_cmd.ExecuteNonQuery();
  259. }
  260. catch (Exception ex)
  261. {
  262. m_errormsg = ex.ToString();
  263. i_return = -2;
  264. }
  265. finally
  266. {
  267. if (obj_con.State == ConnectionState.Open)
  268. {
  269. obj_con.Close();
  270. }
  271. }
  272. return i_return;
  273. }
  274. /// <summary>
  275. /// 执行存储过程
  276. /// </summary>
  277. /// <typeparam name="T">DataParameter参数类型，实现IDataParameter接口</typeparam>
  278. /// <param name="proc_name">存储过程名称</param>
  279. /// <param name="parameters">参数列表</param>
  280. /// <returns></returns>
  281. public bool ExecuteProcedure<T>(string proc_name,ref T[] parameters) where T : IDataParameter
  282. {
  283. bool b_return = false;
  284. if (m_connectionstring.Equals(string.Empty))
  285. {
  286. BuildConnectionString();
  287. }
  288. IDbConnection obj_con = GetDBConnection();
  289. obj_con.ConnectionString = m_connectionstring;
  290. try
  291. {
  292. obj_con.Open();
  293. IDbCommand obj_cmd = GetDBCommand();
  294. obj_cmd.Connection = obj_con;
  295. obj_cmd.CommandType = CommandType.StoredProcedure;
  296. obj_cmd.CommandText = proc_name;
  297. foreach (T parameterin parameters)
  298. {
  299. obj_cmd.Parameters.Add(parameter);
  300. }
  301. obj_cmd.ExecuteNonQuery();
  302. b_return = true;
  303. }
  304. catch (Exception ex)
  305. {
  306. m_errormsg = ex.ToString();
  307. b_return = false;
  308. }
  309. finally
  310. {
  311. if (obj_con.State == ConnectionState.Open)
  312. {
  313. obj_con.Close();
  314. }
  315. }
  316. return b_return;
  317. }
  318. /// <summary>
  319. /// 执行SQL命令，返回数据读取器DataReader
  320. /// </summary>
  321. /// <param name="str_sql">SQL命令</param>
  322. /// <returns></returns>
  323. public IDataReader ExecuteReader(string str_sql)
  324. {
  325. IDataReader obj_dr;
  326. if (m_connectionstring.Equals(string.Empty))
  327. {
  328. BuildConnectionString();
  329. }
  330. IDbConnection obj_con = GetDBConnection();
  331. obj_con.ConnectionString = m_connectionstring;
  332. try
  333. {
  334. obj_con.Open();
  335. IDbCommand obj_cmd = GetDBCommand();
  336. obj_cmd.Connection = obj_con;
  337. obj_cmd.CommandText = str_sql;
  338. obj_dr = obj_cmd.ExecuteReader(CommandBehavior.CloseConnection);
  339. }
  340. catch (Exception ex)
  341. {
  342. m_errormsg = ex.ToString();
  343. obj_dr = null;
  344. }
  345. return obj_dr;
  346. }
  347. /// <summary>
  348. /// 异步执行Sql查询
  349. /// </summary>
  350. /// <param name="str_sql">sql查询</param>
  351. /// <param name="callback">回调函数，包含DatabaseAsyncState</param>
  352. /// <returns></returns>
  353. public bool BeginExecuteReader(string str_sql, Action<object> callback)
  354. {
  355. IDataReader obj_dr;
  356. if (m_connectionstring.Equals(string.Empty))
  357. {
  358. BuildConnectionString();
  359. }
  360. IDbConnection obj_con = GetDBConnection();
  361. obj_con.ConnectionString = m_connectionstring;
  362. try
  363. {
  364. obj_con.Open();
  365. IDbCommand obj_cmd = GetDBCommand();
  366. obj_cmd.Connection = obj_con;
  367. obj_cmd.CommandText = str_sql;
  368. obj_dr = obj_cmd.ExecuteReader(CommandBehavior.CloseConnection);
  369. DatabaseAsyncState async_state = new DatabaseAsyncState();
  370. async_state.DbCommand = obj_cmd;
  371. async_state.DataReader = obj_dr;
  372. System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(callback));
  373. thread.Start(async_state);
  374. return true;
  375. }
  376. catch (Exception ex)
  377. {
  378. m_errormsg = ex.ToString();
  379. return false;
  380. }
  381. }
  382. /// <summary>
  383. /// 试图取消IDbCommand的执行
  384. /// </summary>
  385. /// <param name="command">ICommand对象</param>
  386. public void Cancel(IDbCommand command)
  387. {
  388. command.Cancel();
  389. }
  390. /// <summary>
  391. /// 根据数据库访问方式构造数据库连接字符串
  392. /// </summary>
  393. public void BuildConnectionString()
  394. {
  395. switch (m_accessType)
  396. {
  397. case AccessType.MySQLClient:
  398. m_connectionstring = "Server=" + m_databaseparam.DBHost +";Port=" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword +";";
  399. if (m_commandtimeout > 0)
  400. {
  401. m_connectionstring += "Default command timeout=" + m_commandtimeout.ToString() +";";
  402. }
  403. if (m_connectiontimeout > 0)
  404. {
  405. m_connectionstring += "Connection timeout=" + m_connectiontimeout +";";
  406. }
  407. break;
  408. case AccessType.MSSQLClient:
  409. m_connectionstring = "Data Source=" + m_databaseparam.DBHost +"," + m_databaseparam.DBPort + ";Initial Catalog=" + m_databaseparam.DBName +";User ID=" + m_databaseparam.DBUser + ";Password=" + m_databaseparam.DBPassword + ";";
  410. break;
  411. case AccessType.OracleDirect:
  412. 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 +";";
  413. break;
  414. case AccessType.OracleTNS:
  415. m_connectionstring = "Data Source=" + m_databaseparam.DBName +";User Id=" + m_databaseparam.DBUser + ";Password=" + m_databaseparam.DBPassword + ";";
  416. break;
  417. case AccessType.IBMDataDB2:
  418. m_connectionstring = "Server=" + m_databaseparam.DBHost +":" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName +";userid=" + m_databaseparam.DBUser + ";password=" + m_databaseparam.DBPassword+";";
  419. break;
  420. case AccessType.MySQLODBC:
  421. 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 + ";";
  422. break;
  423. case AccessType.MSSQLODBC:
  424. 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 + ";";
  425. break;
  426. case AccessType.MSOracleODBC:
  427. m_connectionstring = "Driver={" + m_providername +"};Server=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword +";";
  428. break;
  429. case AccessType.OracleODBC:
  430. m_connectionstring = "Driver={" + m_providername +"};Server=" + m_databaseparam.DBHost + ";Dbq=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser +";Pwd=" + m_databaseparam.DBPassword + ";";
  431. break;
  432. case AccessType.DB2ODBC:
  433. 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 + ";";
  434. break;
  435. default:
  436. m_errormsg = "Access type not support.";
  437. break;
  438. }
  439. }
  440. private IDbConnection GetDBConnection()
  441. {
  442. switch (m_accessType)
  443. {
  444. case AccessType.MySQLClient:
  445. MySqlConnection mysql_connection = new MySqlConnection();
  446. return mysql_connection;
  447. case AccessType.MSSQLClient:
  448. SqlConnection mssql_connection = new SqlConnection();
  449. return mssql_connection;
  450. case AccessType.OracleDirect:
  451. case AccessType.OracleTNS:
  452. OracleConnection oracle_connection = new OracleConnection();
  453. return oracle_connection;
  454. case AccessType.IBMDataDB2:
  455. DB2Connection db2_connection = new DB2Connection();
  456. return db2_connection;
  457. case AccessType.MySQLODBC:
  458. case AccessType.MSSQLODBC:
  459. case AccessType.OracleODBC:
  460. case AccessType.MSOracleODBC:
  461. case AccessType.DB2ODBC:
  462. OdbcConnection odbc_connection = new OdbcConnection();
  463. return odbc_connection;
  464. default:
  465. m_errormsg = "Access type not support.";
  466. return null;
  467. }
  468. }
  469. private IDataAdapter GetDataAdapter(string str_sql)
  470. {
  471. switch (m_accessType)
  472. {
  473. case AccessType.MySQLClient:
  474. MySqlConnection mysql_connection = new MySqlConnection(m_connectionstring);
  475. MySqlDataAdapter mysql_dapt = new MySqlDataAdapter(str_sql, mysql_connection);
  476. return mysql_dapt;
  477. case AccessType.MSSQLClient:
  478. SqlConnection mssql_connection = new SqlConnection(m_connectionstring);
  479. SqlDataAdapter mssql_dapt = new SqlDataAdapter(str_sql, mssql_connection);
  480. return mssql_dapt;
  481. case AccessType.OracleDirect:
  482. case AccessType.OracleTNS:
  483. OracleConnection oracle_connection = new OracleConnection(m_connectionstring);
  484. OracleDataAdapter oracle_dapt = new OracleDataAdapter(str_sql, oracle_connection);
  485. return oracle_dapt;
  486. case AccessType.IBMDataDB2:
  487. DB2Connection db2_connection = new DB2Connection(m_connectionstring);
  488. DB2DataAdapter db2_dapt = new DB2DataAdapter(str_sql, db2_connection);
  489. return db2_dapt;
  490. case AccessType.MySQLODBC:
  491. case AccessType.MSSQLODBC:
  492. case AccessType.OracleODBC:
  493. case AccessType.MSOracleODBC:
  494. case AccessType.DB2ODBC:
  495. OdbcConnection odbc_connection = new OdbcConnection(m_connectionstring);
  496. OdbcDataAdapter odbc_dapt = new OdbcDataAdapter(str_sql, odbc_connection);
  497. return odbc_dapt;
  498. default:
  499. m_errormsg = "Access type not support.";
  500. return null;
  501. }
  502. }
  503. private IDbCommand GetDBCommand()
  504. {
  505. switch (m_accessType)
  506. {
  507. case AccessType.MySQLClient:
  508. MySqlCommand mysql_cmd = new MySqlCommand();
  509. return mysql_cmd;
  510. case AccessType.MSSQLClient:
  511. SqlCommand mssql_cmd = new SqlCommand();
  512. return mssql_cmd;
  513. case AccessType.OracleDirect:
  514. case AccessType.OracleTNS:
  515. OracleCommand oracle_cmd = new OracleCommand();
  516. return oracle_cmd;
  517. case AccessType.IBMDataDB2:
  518. DB2Command db2_cmd = new DB2Command();
  519. return db2_cmd;
  520. case AccessType.MySQLODBC:
  521. case AccessType.MSSQLODBC:
  522. case AccessType.OracleODBC:
  523. case AccessType.MSOracleODBC:
  524. case AccessType.DB2ODBC:
  525. OdbcCommand odbc_cmd = new OdbcCommand();
  526. return odbc_cmd;
  527. default:
  528. m_errormsg = "Access type not support.";
  529. return null;
  530. }
  531. }
  532. }
[csharp] view plaincopyprint?
  1. /// <summary>
  2. /// 访问数据库，提供连接、查询、执行SQL等数据库访问功能
  3. /// author:Charley
  4. /// date:2012/4/30
  5. /// </summary>
  6. public class DataBaseAccess
  7. {
  8. private DatabaseParam m_databaseparam;
  9. /// <summary>
  10. /// 获取或设置数据库参数
  11. /// </summary>
  12. public DatabaseParam DatabaseParam
  13. {
  14. get { return m_databaseparam; }
  15. set { m_databaseparam = value; }
  16. }
  17. private string m_errormsg;
  18. /// <summary>
  19. /// 获取内部操作操作错误信息，得到上一步操作的错误消息
  20. /// </summary>
  21. public string ErrorMsg
  22. {
  23. get { return m_errormsg; }
  24. }
  25. private string m_connectionstring;
  26. /// <summary>
  27. /// 获取或设置数据库连接字符串
  28. /// </summary>
  29. public string ConnectionString
  30. {
  31. get { return m_connectionstring; }
  32. set { m_connectionstring = value; }
  33. }
  34. private AccessType m_accessType;
  35. /// <summary>
  36. /// 设置数据库访问方式
  37. /// </summary>
  38. public AccessType AccessType
  39. {
  40. set { m_accessType = value; }
  41. }
  43. private string m_providername;
  44. /// <summary>
  45. /// 设置数据提供者名称,用于ODBC连接
  46. /// </summary>
  47. public string ProviderName
  48. {
  49. //get { return m_providername; }
  50. set { m_providername = value; }
  51. }
  52. private int m_commandtimeout;
  53. /// <summary>
  54. /// 设置命令执行的超时时间，0：不限制，即采用数据库默认值
  55. /// </summary>
  56. public int CommandTimeout
  57. {
  58. set { m_commandtimeout = value; }
  59. }
  60. private int m_connectiontimeout;
  61. /// <summary>
  62. /// 设置数据库连接超时时间，0：不限制，即采用数据库默认值
  63. /// </summary>
  64. public int ConnectionTimeout
  65. {
  66. set { m_connectiontimeout = value; }
  67. }
  68. /// <summary>
  69. ///使用默认值构造
  70. /// </summary>
  71. public DataBaseAccess()
  72. {
  73. m_databaseparam = new DatabaseParam();
  74. m_accessType = AccessType.MySQLClient;
  75. m_errormsg = string.Empty;
  76. m_connectionstring = string.Empty;
  77. m_providername = string.Empty;
  78. m_commandtimeout = 0;
  79. m_connectiontimeout = 0;
  80. }
  81. /// <summary>
  82. /// 指定数据库参数构造
  83. /// </summary>
  84. /// <param name="database_param">数据库参数</param>
  85. public DataBaseAccess(DatabaseParam database_param)
  86. : this()
  87. {
  88. m_databaseparam = database_param;
  89. }
  90. /// <summary>
  91. /// 指定数据库参数及数据库访问类型构造
  92. /// </summary>
  93. /// <param name="database_param">数据库参数</param>
  94. /// <param name="access_type">数据库访问类型</param>
  95. public DataBaseAccess(DatabaseParam database_param, AccessType access_type)
  96. : this()
  97. {
  98. m_databaseparam = database_param;
  99. m_accessType = access_type;
  100. }
  101. /// <summary>
  102. /// 清除内部错误消息
  103. /// </summary>
  104. public void ClearMessage()
  105. {
  106. m_errormsg = string.Empty;
  107. }
  108. /// <summary>
  109. /// 测试数据库连接
  110. /// 连接成功：true
  111. /// 连接失败：False
  112. /// </summary>
  113. /// <returns></returns>
  114. public bool TestConnection()
  115. {
  116. bool b_return = false;
  117. if (m_connectionstring.Equals(string.Empty))
  118. {
  119. BuildConnectionString();
  120. }
  121. IDbConnection obj_connection = GetDBConnection();
  122. obj_connection.ConnectionString = m_connectionstring;
  123. try
  124. {
  125. obj_connection.Open();
  126. b_return = true;
  127. }
  128. catch (Exception ex)
  129. {
  130. m_errormsg = ex.ToString();
  131. }
  132. finally
  133. {
  134. if (obj_connection.State == ConnectionState.Open)
  135. {
  136. obj_connection.Close();
  137. }
  138. }
  139. return b_return;
  140. }
  141. /// <summary>
  142. /// 获取记录行数
  143. /// 返回 -1 指示操作有错误
  144. /// </summary>
  145. /// <param name="str_sql">查询语句</param>
  146. /// <returns></returns>
  147. public int GetRecordNum(string str_sql)
  148. {
  149. DataSet obj_ds = GetDataSet(str_sql);
  150. if (obj_ds == null || obj_ds.Tables.Count <= 0)
  151. {
  152. return -1;
  153. }
  154. return obj_ds.Tables[0].Rows.Count;
  155. }
  156. /// <summary>
  157. /// 获取指定列的值
  158. /// 返回 DATABASE_RETURN_ERROR 指示操作有错误
  159. /// </summary>
  160. /// <param name="str_sql">查询语句</param>
  161. /// <param name="column_name">列名</param>
  162. /// <returns></returns>
  163. public string GetStringValue(string column_name,string str_sql)
  164. {
  165. string s_return =string.Empty;
  166. DataRow obj_dr = GetFirstRecord(str_sql);
  167. if (obj_dr == null)
  168. {
  169. s_return = DatabaseDefinitions.DATABASE_RETURN_ERROR;
  170. return s_return;
  171. }
  172. try
  173. {
  174. s_return = obj_dr[column_name].ToString();
  175. }
  176. catch (Exception ex)
  177. {
  178. m_errormsg = ex.ToString();
  179. s_return = DatabaseDefinitions.DATABASE_RETURN_ERROR;
  180. return s_return;
  181. }
  182. return s_return;
  183. }
  184. /// <summary>
  185. /// 获取第一条记录
  186. /// 返回 null 指示操作有错误
  187. /// </summary>
  188. /// <param name="str_sql">查询语句</param>
  189. /// <returns></returns>
  190. public DataRow GetFirstRecord(string str_sql)
  191. {
  192. DataSet obj_ds = GetDataSet(str_sql);
  193. if (obj_ds == null || obj_ds.Tables.Count <= 0)
  194. {
  195. return null;
  196. }
  197. int count = obj_ds.Tables[0].Rows.Count;
  198. if (count == 0)
  199. {
  200. m_errormsg = "No Record.";
  201. return null;
  202. }
  203. return obj_ds.Tables[0].Rows[0];
  204. }
  205. /// <summary>
  206. /// 获取数据集
  207. /// 返回 null 指示操作有错误
  208. /// </summary>
  209. /// <param name="str_sql">查询语句</param>
  210. /// <returns></returns>
  211. public DataSet GetDataSet(string str_sql)
  212. {
  213. DataSet obj_ds = new DataSet();
  214. if (m_connectionstring.Equals(string.Empty))
  215. {
  216. BuildConnectionString();
  217. }
  218. IDataAdapter obj_dapt = GetDataAdapter(str_sql);
  219. try
  220. {
  221. obj_dapt.Fill(obj_ds);
  222. if (obj_ds.Tables.Count <= 0)
  223. {
  224. m_errormsg = "No table.";
  225. obj_ds = null;
  226. }
  227. }
  228. catch (Exception ex)
  229. {
  230. m_errormsg = ex.ToString();
  231. obj_ds = null;
  232. }
  233. return obj_ds;
  234. }
  236. /// <summary>
  237. /// 执行SQL命令，返回受影响的行数
  238. /// 返回 -2 指示操作有错误
  239. /// 返回 -1 指示执行DDL命令
  240. /// </summary>
  241. /// <param name="str_sql">SQL命令</param>
  242. /// <returns></returns>
  243. public int ExecuteCommand(string str_sql)
  244. {
  245. int i_return = -2;
  246. if (m_connectionstring.Equals(string.Empty))
  247. {
  248. BuildConnectionString();
  249. }
  250. IDbConnection obj_con = GetDBConnection();
  251. obj_con.ConnectionString = m_connectionstring;
  252. IDbCommand obj_cmd = GetDBCommand();
  253. try
  254. {
  255. obj_con.Open();
  256. obj_cmd.Connection = obj_con;
  257. obj_cmd.CommandText = str_sql;
  258. return obj_cmd.ExecuteNonQuery();
  259. }
  260. catch (Exception ex)
  261. {
  262. m_errormsg = ex.ToString();
  263. i_return = -2;
  264. }
  265. finally
  266. {
  267. if (obj_con.State == ConnectionState.Open)
  268. {
  269. obj_con.Close();
  270. }
  271. }
  272. return i_return;
  273. }
  274. /// <summary>
  275. /// 执行存储过程
  276. /// </summary>
  277. /// <typeparam name="T">DataParameter参数类型，实现IDataParameter接口</typeparam>
  278. /// <param name="proc_name">存储过程名称</param>
  279. /// <param name="parameters">参数列表</param>
  280. /// <returns></returns>
  281. public bool ExecuteProcedure<T>(string proc_name,ref T[] parameters) where T : IDataParameter
  282. {
  283. bool b_return = false;
  284. if (m_connectionstring.Equals(string.Empty))
  285. {
  286. BuildConnectionString();
  287. }
  288. IDbConnection obj_con = GetDBConnection();
  289. obj_con.ConnectionString = m_connectionstring;
  290. try
  291. {
  292. obj_con.Open();
  293. IDbCommand obj_cmd = GetDBCommand();
  294. obj_cmd.Connection = obj_con;
  295. obj_cmd.CommandType = CommandType.StoredProcedure;
  296. obj_cmd.CommandText = proc_name;
  297. foreach (T parameterin parameters)
  298. {
  299. obj_cmd.Parameters.Add(parameter);
  300. }
  301. obj_cmd.ExecuteNonQuery();
  302. b_return = true;
  303. }
  304. catch (Exception ex)
  305. {
  306. m_errormsg = ex.ToString();
  307. b_return = false;
  308. }
  309. finally
  310. {
  311. if (obj_con.State == ConnectionState.Open)
  312. {
  313. obj_con.Close();
  314. }
  315. }
  316. return b_return;
  317. }
  318. /// <summary>
  319. /// 执行SQL命令，返回数据读取器DataReader
  320. /// </summary>
  321. /// <param name="str_sql">SQL命令</param>
  322. /// <returns></returns>
  323. public IDataReader ExecuteReader(string str_sql)
  324. {
  325. IDataReader obj_dr;
  326. if (m_connectionstring.Equals(string.Empty))
  327. {
  328. BuildConnectionString();
  329. }
  330. IDbConnection obj_con = GetDBConnection();
  331. obj_con.ConnectionString = m_connectionstring;
  332. try
  333. {
  334. obj_con.Open();
  335. IDbCommand obj_cmd = GetDBCommand();
  336. obj_cmd.Connection = obj_con;
  337. obj_cmd.CommandText = str_sql;
  338. obj_dr = obj_cmd.ExecuteReader(CommandBehavior.CloseConnection);
  339. }
  340. catch (Exception ex)
  341. {
  342. m_errormsg = ex.ToString();
  343. obj_dr = null;
  344. }
  345. return obj_dr;
  346. }
  347. /// <summary>
  348. /// 异步执行Sql查询
  349. /// </summary>
  350. /// <param name="str_sql">sql查询</param>
  351. /// <param name="callback">回调函数，包含DatabaseAsyncState</param>
  352. /// <returns></returns>
  353. public bool BeginExecuteReader(string str_sql, Action<object> callback)
  354. {
  355. IDataReader obj_dr;
  356. if (m_connectionstring.Equals(string.Empty))
  357. {
  358. BuildConnectionString();
  359. }
  360. IDbConnection obj_con = GetDBConnection();
  361. obj_con.ConnectionString = m_connectionstring;
  362. try
  363. {
  364. obj_con.Open();
  365. IDbCommand obj_cmd = GetDBCommand();
  366. obj_cmd.Connection = obj_con;
  367. obj_cmd.CommandText = str_sql;
  368. obj_dr = obj_cmd.ExecuteReader(CommandBehavior.CloseConnection);
  369. DatabaseAsyncState async_state = new DatabaseAsyncState();
  370. async_state.DbCommand = obj_cmd;
  371. async_state.DataReader = obj_dr;
  372. System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(callback));
  373. thread.Start(async_state);
  374. return true;
  375. }
  376. catch (Exception ex)
  377. {
  378. m_errormsg = ex.ToString();
  379. return false;
  380. }
  381. }
  382. /// <summary>
  383. /// 试图取消IDbCommand的执行
  384. /// </summary>
  385. /// <param name="command">ICommand对象</param>
  386. public void Cancel(IDbCommand command)
  387. {
  388. command.Cancel();
  389. }
  390. /// <summary>
  391. /// 根据数据库访问方式构造数据库连接字符串
  392. /// </summary>
  393. public void BuildConnectionString()
  394. {
  395. switch (m_accessType)
  396. {
  397. case AccessType.MySQLClient:
  398. m_connectionstring = "Server=" + m_databaseparam.DBHost +";Port=" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword +";";
  399. if (m_commandtimeout > 0)
  400. {
  401. m_connectionstring += "Default command timeout=" + m_commandtimeout.ToString() +";";
  402. }
  403. if (m_connectiontimeout > 0)
  404. {
  405. m_connectionstring += "Connection timeout=" + m_connectiontimeout +";";
  406. }
  407. break;
  408. case AccessType.MSSQLClient:
  409. m_connectionstring = "Data Source=" + m_databaseparam.DBHost +"," + m_databaseparam.DBPort + ";Initial Catalog=" + m_databaseparam.DBName +";User ID=" + m_databaseparam.DBUser + ";Password=" + m_databaseparam.DBPassword + ";";
  410. break;
  411. case AccessType.OracleDirect:
  412. 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 +";";
  413. break;
  414. case AccessType.OracleTNS:
  415. m_connectionstring = "Data Source=" + m_databaseparam.DBName +";User Id=" + m_databaseparam.DBUser + ";Password=" + m_databaseparam.DBPassword + ";";
  416. break;
  417. case AccessType.IBMDataDB2:
  418. m_connectionstring = "Server=" + m_databaseparam.DBHost +":" + m_databaseparam.DBPort + ";Database=" + m_databaseparam.DBName +";userid=" + m_databaseparam.DBUser + ";password=" + m_databaseparam.DBPassword+";";
  419. break;
  420. case AccessType.MySQLODBC:
  421. 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 + ";";
  422. break;
  423. case AccessType.MSSQLODBC:
  424. 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 + ";";
  425. break;
  426. case AccessType.MSOracleODBC:
  427. m_connectionstring = "Driver={" + m_providername +"};Server=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser + ";Pwd=" + m_databaseparam.DBPassword +";";
  428. break;
  429. case AccessType.OracleODBC:
  430. m_connectionstring = "Driver={" + m_providername +"};Server=" + m_databaseparam.DBHost + ";Dbq=" + m_databaseparam.DBName + ";Uid=" + m_databaseparam.DBUser +";Pwd=" + m_databaseparam.DBPassword + ";";
  431. break;
  432. case AccessType.DB2ODBC:
  433. 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 + ";";
  434. break;
  435. default:
  436. m_errormsg = "Access type not support.";
  437. break;
  438. }
  439. }
  440. private IDbConnection GetDBConnection()
  441. {
  442. switch (m_accessType)
  443. {
  444. case AccessType.MySQLClient:
  445. MySqlConnection mysql_connection = new MySqlConnection();
  446. return mysql_connection;
  447. case AccessType.MSSQLClient:
  448. SqlConnection mssql_connection = new SqlConnection();
  449. return mssql_connection;
  450. case AccessType.OracleDirect:
  451. case AccessType.OracleTNS:
  452. OracleConnection oracle_connection = new OracleConnection();
  453. return oracle_connection;
  454. case AccessType.IBMDataDB2:
  455. DB2Connection db2_connection = new DB2Connection();
  456. return db2_connection;
  457. case AccessType.MySQLODBC:
  458. case AccessType.MSSQLODBC:
  459. case AccessType.OracleODBC:
  460. case AccessType.MSOracleODBC:
  461. case AccessType.DB2ODBC:
  462. OdbcConnection odbc_connection = new OdbcConnection();
  463. return odbc_connection;
  464. default:
  465. m_errormsg = "Access type not support.";
  466. return null;
  467. }
  468. }
  469. private IDataAdapter GetDataAdapter(string str_sql)
  470. {
  471. switch (m_accessType)
  472. {
  473. case AccessType.MySQLClient:
  474. MySqlConnection mysql_connection = new MySqlConnection(m_connectionstring);
  475. MySqlDataAdapter mysql_dapt = new MySqlDataAdapter(str_sql, mysql_connection);
  476. return mysql_dapt;
  477. case AccessType.MSSQLClient:
  478. SqlConnection mssql_connection = new SqlConnection(m_connectionstring);
  479. SqlDataAdapter mssql_dapt = new SqlDataAdapter(str_sql, mssql_connection);
  480. return mssql_dapt;
  481. case AccessType.OracleDirect:
  482. case AccessType.OracleTNS:
  483. OracleConnection oracle_connection = new OracleConnection(m_connectionstring);
  484. OracleDataAdapter oracle_dapt = new OracleDataAdapter(str_sql, oracle_connection);
  485. return oracle_dapt;
  486. case AccessType.IBMDataDB2:
  487. DB2Connection db2_connection = new DB2Connection(m_connectionstring);
  488. DB2DataAdapter db2_dapt = new DB2DataAdapter(str_sql, db2_connection);
  489. return db2_dapt;
  490. case AccessType.MySQLODBC:
  491. case AccessType.MSSQLODBC:
  492. case AccessType.OracleODBC:
  493. case AccessType.MSOracleODBC:
  494. case AccessType.DB2ODBC:
  495. OdbcConnection odbc_connection = new OdbcConnection(m_connectionstring);
  496. OdbcDataAdapter odbc_dapt = new OdbcDataAdapter(str_sql, odbc_connection);
  497. return odbc_dapt;
  498. default:
  499. m_errormsg = "Access type not support.";
  500. return null;
  501. }
  502. }
  503. private IDbCommand GetDBCommand()
  504. {
  505. switch (m_accessType)
  506. {
  507. case AccessType.MySQLClient:
  508. MySqlCommand mysql_cmd = new MySqlCommand();
  509. return mysql_cmd;
  510. case AccessType.MSSQLClient:
  511. SqlCommand mssql_cmd = new SqlCommand();
  512. return mssql_cmd;
  513. case AccessType.OracleDirect:
  514. case AccessType.OracleTNS:
  515. OracleCommand oracle_cmd = new OracleCommand();
  516. return oracle_cmd;
  517. case AccessType.IBMDataDB2:
  518. DB2Command db2_cmd = new DB2Command();
  519. return db2_cmd;
  520. case AccessType.MySQLODBC:
  521. case AccessType.MSSQLODBC:
  522. case AccessType.OracleODBC:
  523. case AccessType.MSOracleODBC:
  524. case AccessType.DB2ODBC:
  525. OdbcCommand odbc_cmd = new OdbcCommand();
  526. return odbc_cmd;
  527. default:
  528. m_errormsg = "Access type not support.";
  529. return null;
  530. }
  531. }
  532. }


使用示例：

[csharp] view plaincopyprint?
  1. DatabaseParam database_param = new DatabaseParam();
  2. database_param.DBType = 1;
  3. database_param.DBHost = txt_serverName.Text;
  4. database_param.DBPort = int.Parse(txt_serverPort.Text);
  5. database_param.DBName = txt_dbName.Text;
  6. database_param.DBUser = txt_loginName.Text;
  7. database_param.DBPassword = txt_loginPwd.Password;
  8. App.G_VMCDatabaseHelper = new DataBaseAccess(database_param,AccessType.MySQLClient);
  9. App.G_VMCDatabaseHelper.ClearMessage();
  10. if (App.G_VMCDatabaseHelper.TestConnection())
  11. {
  13. }
  14. else
  15. {
  16. App.G_LogOperator.WriteOperationLog("WLogin->Login","Connect to database fail.\r\n" + App.G_VMCDatabaseHelper.ErrorMsg);
  17. ShowErrorMessageBox("Connect to database fail.");
  18. return;
  19. }
西岐网络 彭永辉 西岐网络 彭永辉
原创粉丝点击
热门IT博客
php面向对象重定向php面向对象重定向
VB中caption属性VB中caption属性
阿里云带宽跑不满
逆火刷机软件下载
青岛网络教育哪家好
黑马程序员学费是多少
手机淘宝首页装修图片
电子数据保全行业公约
淘宝出库单怎么打印
中国专利局数据网
charles破解版for mac
sql server还原
编程题 输出数字菱形
爱知产业大学
知牛财经 直播地址
查看网卡mac地址
python 剪切文件
python网络编程第3版
ubuntu软件源
江苏爱知投资有限公司
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 夜泣 夜闻 夜有 了夜 夜下独酌 白拉特夜 盖得尔夜 夜了了 夜美得凄凉 十五望月夜 夜听风 夜血僵神 滕光夜 山村风月夜 苏荷之夜 夜听 夜听电台 夜听故事 10点夜听感情 365夜童话故事mp3在线听 情感夜听在线收听 小楼一夜听风雨 夜听经典 夜听歌曲 山外小楼夜听雨歌词 夜读深度好文在线听 夜听人生感悟 告白之夜在线听 十点夜听 五夜十篇有必要听吗 抖音告白之夜在线听 黛玉有只鬼 夜听春雨 山外小楼夜听雨舞蹈 夜听在线 阵修士 倚夜听雨 10点夜听全集 情感夜听 夜听怎样在线收听 刘筱的夜听怎么收听 夜听下载 夜听风雨

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里