关键字: 通用数据库类
接上一篇文章
OleDbDataProvider.cs 提供OLEDB连接访问的类
c# 代码
- #define DEBUG
- using System;
- using System.Data;
- using System.Data.OleDb;
- namespace DataProviders
- {
- ///
- /// OleDbDataProvider 的摘要说明。
- ///
- internal class OleDbDataProvider : IDataProvider
- {
- private System.Data.OleDb.OleDbConnection oleDbConnection;
- private System.Data.OleDb.OleDbCommand oleDbCommand;
- private string connectionString;
- public OleDbDataProvider() : this(null)
- {
- //
- // TODO: 在此处添加构造函数逻辑
- //
- }
- public OleDbDataProvider(string connectionString)
- {
- if (connectionString == null || connectionString.Trim() == string.Empty)
- {
- System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
- this.connectionString = (string)(configurationAppSettings.GetValue("oleDbConnectionString", typeof(string)));
- }
- else
- {
- this.connectionString = connectionString;
- }
- }
- ///
- /// OleDb 连接字符串
- ///
- public string ConnectionString
- {
- get
- {
- return this.connectionString;
- }
- set
- {
- this.connectionString = value;
- }
- }
- ///
- /// 返回一个带有连接字符串的OleDb Connection.
- ///
- /// OracleConnection
- private OleDbConnection GetOleDbConnection()
- {
- try
- {
- return new OleDbConnection(this.connectionString);
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return null;
- }
- }
- ///
- /// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1
- ///
- /// UPDATE、INSERT 和 DELETE 语句
- public int ExecuteNonQuery(string sql)
- {
- using(oleDbConnection = this.GetOleDbConnection())
- {
- if (oleDbConnection == null)
- return -1;
- try
- {
- if (oleDbConnection.State == System.Data.ConnectionState.Closed)
- oleDbConnection.Open();
- oleDbCommand = new OleDbCommand(sql, oleDbConnection);
- return oleDbCommand.ExecuteNonQuery();
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return -1;
- }
- }
- }
- ///
- /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
- ///
- /// SELECT 语句
- /// .NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用
- public object ExecuteScalar(string sql)
- {
- using(oleDbConnection = this.GetOleDbConnection())
- {
- if (oleDbConnection == null)
- return null;
- try
- {
- if (oleDbConnection.State == System.Data.ConnectionState.Closed)
- oleDbConnection.Open();
- oleDbCommand = new OleDbCommand(sql, oleDbConnection);
- return oleDbCommand.ExecuteScalar();
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return null;
- }
- }
- }
- ///
- /// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回
- ///
- /// SELECT 语句
- /// 数据集 DataSet
- public DataSet RetriveDataSet(string sql)
- {
- if (sql == null || sql == string.Empty)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine("sql 为空");
- #endif
- return null;
- }
- using(oleDbConnection = this.GetOleDbConnection())
- {
- if (oleDbConnection == null)
- return null;
- using(OleDbDataAdapter da = new OleDbDataAdapter(sql, oleDbConnection))
- {
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds);
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- }
- return ds;
- }
- }
- }
- ///
- /// 执行Sql数组语句查询,并将查询返回的结果作为一个数据集返回
- ///
- /// Select 语句数组
- /// TableName
- /// 数据集 DataSet
- public DataSet RetriveDataSet(string[] sql, params string[] tableName)
- {
- int sqlLength;
- sqlLength = sql.Length;
- if ( sqlLength == 0)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine("sql 为空");
- #endif
- return null;
- }
- using(oleDbConnection = this.GetOleDbConnection())
- {
- if (oleDbConnection == null)
- return null;
- DataSet ds = new DataSet();
- int tableNameLength = tableName.Length;
- for (int i = 0; i < sqlLength; i++)
- {
- using(OleDbDataAdapter da = new OleDbDataAdapter(sql[i], oleDbConnection))
- {
- try
- {
- if (i < tableNameLength)
- da.Fill(ds, tableName[i]);
- else
- da.Fill(ds, "table" + i);
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return null;
- }
- }
- }
- return ds;
- }
- }
- public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
- {
- return null;
- }
- public void Dispose()
- {
- this.connectionString = null;
- this.oleDbCommand.Dispose();
- this.oleDbConnection.Dispose();
- }
- }
- }
App.config 应用程序配置文件
xml 代码
- xml version="1.0" encoding="utf-8"?>
- <configuration>
- <appSettings>
- <add key="oracleConnectionString" value="Data Source=myoracledb;User ID=me;Password=mypwd;Persist Security Info=true;" />
- <add key="sqlConnectionString" value="uid=sa;pwd=;initial catalog=mymsdb;data source=127.0.0.1;Connect Timeout=900" />
- <add key="oleDbConnectionString" value="" />
- <add key="odbcConnectionString" value="" />
- appSettings>
- configuration>
有了以上这几个类,对访问各种数据库会变得比较方便和易于管理代码,配置文件也可以随时更改,但是缺点在于配置文件使用System.Configuration只能读取,而无法在程序中改写,而把它当作xml来处理又稍显复杂,不过好在还可以用系统API调用,将属性用ini文件存储,效率高而且使用也比较简单。
(全文完)