.NET通用访问数据库类（C#版）

在数据库应用程序的开发中，往往一个应用程序要从多个数据库中取得所要的业务数据。对于ADO.NET更是如此，一个DataSet，可能是从几个数据库中取得的数据集合。为此开发一个访问数据库通用接口显得更为方便、灵活。下面是一个针对.NET提供的四种访问数据库的通用类，每个类都有具体实现了一些常用访问数据库的方法。

1、DataProvider.cs 此类返回一个访问数据库的接口实例。

using System;

namespace FenceKing.DataProviders

{

/// <summary>

/// 提供对数据库访问的通用类。

/// </summary>

public class DataProvider

{

/// <summary>

/// 数据库枚举类型

/// </summary>

public enum DataProviderType

{

OdbcDataProvider = 0,

OleDbDataProvider = 1,

OracleDataProvider = 2,

SqlDataProvider = 3

}

/// <summary>

/// 建立访问数据库的实例

/// </summary>

/// <param name="DataProviderType">数据库枚举类型</param>

/// <returns></returns>

public static IDataProvider CreateDataProvider(DataProviderType dataProviderType)

{

switch (dataProviderType)

{

case DataProviderType.OdbcDataProvider:

return new OdbcDataProvider();

case DataProviderType.OleDbDataProvider:

return new OleDbDataProvider();

case DataProviderType.OracleDataProvider:

return new OracleDataProvider();

case DataProviderType.SqlDataProvider:

return new SqlDataProvider();

default:

return null;

}

2、IDataProvider.cs 访问数据库的接口类。

using System;

namespace FenceKing.DataProviders

{

/// <summary>

/// 对数据库访问的通用接口

/// </summary>

public interface IDataProvider

{

/// <summary>

/// 执行 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数

/// </summary>

int ExecuteNonQuery(string sql);

/// <summary>

/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行

/// </summary>

object ExecuteScalar(string sql);

/// <summary>

/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回

/// </summary>

System.Data.DataSet RetriveDataSet(string sql);

/// <summary>

/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回

/// </summary>

System.Data.DataSet RetriveDataSet(string[] sql, params string[] tableName);

/// <summary>

/// 更新库

/// </summary>

/// <param name="sql"></param>

/// <param name="hasChangesDataSet"></param>

/// <returns></returns>

System.Data.DataSet UpdateDataSet(string sql, System.Data.DataSet hasChangesDataSet);

/// <summary>

/// 执行Dispose

/// </summary>

void Dispose();

}

3、OracleDataProvider.cs 访问Oracle的类

using System;

using System.Data;

using System.Data.OracleClient;

namespace FenceKing.DataProviders

{

/// <summary>

/// OracleDataProvider 的摘要说明。

/// </summary>

internal class OracleDataProvider : IDataProvider

{

private System.Data.OracleClient.OracleConnection oracleConnection;

private System.Data.OracleClient.OracleCommand oracleCommand;

private string connectionString;

public OracleDataProvider() : this(null)

{

// TODO: 在此处添加构造函数逻辑

}

public OracleDataProvider(string connectionString)

{

if (connectionString == null || connectionString.Trim() == string.Empty)

{

System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();

this.connectionString = (string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string)));

}

else

{

this.connectionString = connectionString;

}

/// <summary>

/// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;"

/// </summary>

public string ConnectionString

{

get

{

return this.connectionString;

}

set

{

this.connectionString = value;

}

/// <summary>

/// 返回一个带有连接字符串的Oracle Connection.

/// </summary>

/// <returns>OracleConnection</returns>

private OracleConnection GetOracleConnection()

{

try

{

return new OracleConnection(this.connectionString);

}

catch (Exception ex)

{

return null;

}

/// <summary>

/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1

/// </summary>

/// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>

public int ExecuteNonQuery(string sql)

{

using(oracleConnection = this.GetOracleConnection())

{

if (oracleConnection == null)

return -1;

int rv = -1;

OracleTransaction oracleTransaction = null;

try

{

if (oracleConnection.State == System.Data.ConnectionState.Closed)

oracleConnection.Open();

oracleCommand = new OracleCommand(sql, oracleConnection);

oracleTransaction = oracleConnection.BeginTransaction();

oracleCommand.Transaction = oracleTransaction;

rv = oracleCommand.ExecuteNonQuery();

oracleTransaction.Commit();

}

catch (Exception ex)

{

oracleTransaction.Rollback();

rv = -1;

}

return rv;

}

/// <summary>

/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。

/// </summary>

/// <param name="sql">SELECT 语句</param>

/// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>

public object ExecuteScalar(string sql)

{

using(oracleConnection = this.GetOracleConnection())

{

if (oracleConnection == null)

return null;

try

{

if (oracleConnection.State == System.Data.ConnectionState.Closed)

oracleConnection.Open();

oracleCommand = new OracleCommand(sql, oracleConnection);

return oracleCommand.ExecuteScalar();

}

catch (Exception ex)

{

return null;

}

/// <summary>

/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回

/// </summary>

/// <param name="selectSql">SELECT 语句</param>

/// <returns>数据集 DataSet</returns>

public DataSet RetriveDataSet(string sql)

{

if (sql == null || sql == string.Empty)

{

return null;

}

using(oracleConnection = this.GetOracleConnection())

{

if (oracleConnection == null)

return null;

using( OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection))

{

DataSet ds = new DataSet();

try

{

da.Fill(ds);

}

catch (Exception ex)

{

}

return ds;

}

/// <summary>

/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据集返回

/// </summary>

/// <param name="sql">Select 语句数组</param>

/// <param name="tableName">TableName</param>

/// <returns>数据集 DataSet</returns>

public DataSet RetriveDataSet(string[] sql, params string[] tableName)

{

int sqlLength;

sqlLength = sql.Length;

if ( sqlLength == 0)

{

return null;

}

using(oracleConnection = this.GetOracleConnection())

{

if (oracleConnection == null)

return null;

DataSet ds = new DataSet();

int tableNameLength = tableName.Length;

for (int i = 0; i < sqlLength; i++)

{

using(OracleDataAdapter da = new OracleDataAdapter(sql[i], oracleConnection))

{

try

{

if (i < tableNameLength)

da.Fill(ds, tableName[i]);

else

da.Fill(ds, "table" + i);

}

catch (Exception ex)

{

return null;

}

return ds;

}

/// <summary>

/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据读取器返回

/// </summary>

/// <param name="sql"></param>

/// <returns>OracleDataReader</returns>

public OracleDataReader RetriveDataReader(string sql)

{

if (sql == null || sql == string.Empty)

{

return null;

}

using(oracleConnection = this.GetOracleConnection())

{

if (oracleConnection == null)

return null;

using(oracleCommand = new OracleCommand(sql, oracleConnection))

{

try

{

OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

return oracleDataReader;

}

catch (Exception ex)

{

return null;

}

public void Dispose()

{

this.connectionString = null;

this.oracleCommand.Dispose();

this.oracleConnection.Dispose();

}

App.config 应用程序配置文件。

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>



<add key="oracleConnectionString" value="User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;" />
<add key="sqlConnectionString" value="workstation id=FENCEKING;packet size=4096;integrated security=SSPI;data source="FENCEKING\FENCESQL";persist security info=True;initial catalog=Northwind" />
<add key="oleDbConnectionString" value="" />
<add key="odbcConnectionString" value="" />
</appSettings>
</configuration>

FenceKingTest 客户测试

public FenceKingTest()
{
   //
   // Windows 窗体设计器支持所必需的
   //
   InitializeComponent();

   //
   // TODO: 在 InitializeComponent 调用后添加任何构造函数代码
   //

// 声明一个接口，返回一个访问SQL Server的数据库实例
   FenceKing.DataProviders.IDataProvider sqlDataProvider = FenceKing.DataProviders.DataProvider.CreateDataProvider(DataProvider.DataProviderType.SqlDataProvider);
   string[] sql = new string[2];
   sql[0] = "SELECT * FROM Orders";
   sql[1] = "SELECT CustomerID, CompanyName, ContactName FROM Customers";
   DataSet ds;
   string[] tableName = new string[]{"Orders","Customers"};
   ds = sqlDataProvider.RetriveDataSet(sql, tableName);
   ds.Relations.Add("CustomerOrders",ds.Tables["Customers"].Columns["CustomerID"],ds.Tables["Orders"].Columns["CustomerID"]);
   this.dataGrid1.SetDataBinding(ds, ds.Tables[1].TableName);
}

posted on 2007-12-17 22:20 XiaoBei 阅读(6028) 评论(1) 编辑收藏举报