1、DataProvider.cs 此类返回一个访问数据库的接口实例。
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 访问数据库的接口类。
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.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="settingName" value="settingValue"/> -->
<!-- 连接数据库的字符串 -->
<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);
}