在数据库应用程序的开发中,往往一个应用程序要从多个数据库中取得所要的业务数据。对于ADO.NET更是如此,一个DataSet,可能是从几个数据库中取得的数据集合。为此开发一个访问数据库通用接口显得更为方便、灵活。下面是一个针对.NET提供的四种访问数据库的通用类,每个类都有具体实现了一些常用访问数据库的方法。对于UpdateDataSet 方法,要考虑数据更新的并发冲突,就目前还没有一个非常人性的解决办法。不过本人到总结了几个常用解决此问题的方法,在稍后将作为一个专门的话题推出。在这里暂时没有把此源代码贴上,望大家原谅!
DataProvider.cs 此类返回一个访问数据库的接口实例。
#define DEBUG
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:
#if DEBUG
System.Diagnostics.Debug.WriteLine("dataProviderType 类型不存在!");
#endif
return null;
}
}
}
}
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();
}
}
OracleDataProvider.cs 访问Oracle的类
#define DEBUG
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
{
// if (connectionString == null || connectionString.Trim() == string.Empty)
// {
//
// System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
// this.connectionString = ((string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string))));
// }
return new OracleConnection(this.connectionString);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
}
/// <summary>
/// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回
/// </summary>
/// <param name="selectSql">SELECT 语句</param>
/// <returns>数据集 DataSet</returns>
public DataSet RetriveDataSet(string sql)
{
if (sql == null || sql == string.Empty)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
}
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
}
return ds;
}
}
/// <summary>
/// 更新数据集.
/// 过程:客户层(dataSet.GetChanges()) -- 修改 --> 数据服务层(hasChangesDataSet.update()) -- 更新--> 数据层(hasChangesDataSet) ...
/// 数据层(hasChangesDataSet) -- 新数据 --> 数据服务层 (hasChangesDataSet) -- 合并 -- > 客户层(dataSet.Merge(hasChangesDataSet))
/// </summary>
/// <param name="hasChangeDataSet"></param>
/// <returns></returns>
public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
{
}
/// <summary>
/// 执行Sql数组语句查询,并将查询返回的结果作为一个数据读取器返回
/// </summary>
/// <param name="sql"></param>
/// <returns>OracleDataReader</returns>
public OracleDataReader RetriveDataReader(string sql)
{
if (sql == null || sql == string.Empty)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
}
}
public void Dispose()
{
this.connectionString = null;
this.oracleCommand.Dispose();
this.oracleConnection.Dispose();
}
}
}
SqlDataProvider.cs 访问SQL Server的类。
#define DEBUG
using System;
using System.Data;
using System.Data.SqlClient;
namespace FenceKing.DataProviders
{
/// <summary>
/// SqlDataProvider 的摘要说明。
/// </summary>
internal class SqlDataProvider : IDataProvider
{
private System.Data.SqlClient.SqlConnection sqlConnection;
private System.Data.SqlClient.SqlCommand sqlCommand;
private string connectionString;
public SqlDataProvider() : this(null)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public SqlDataProvider(string connectionString)
{
if (connectionString == null || connectionString.Trim() == string.Empty)
{
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
this.connectionString = (string)(configurationAppSettings.GetValue("sqlConnectionString", typeof(string)));
}
else
{
this.connectionString = connectionString;
}
}
/// <summary>
/// SQL 连接字符串
/// </summary>
public string ConnectionString
{
get
{
return this.connectionString;
}
set
{
this.connectionString = value;
}
}
/// <summary>
/// 返回一个带有连接字符串的SQL Connection.
/// </summary>
/// <returns>OracleConnection</returns>
private SqlConnection GetSqlConnection()
{
try
{
// if (connectionString == null || connectionString.Trim() == string.Empty)
// {
// System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
// this.connectionString = ((string)(configurationAppSettings.GetValue("sqlConnectionString", typeof(string))));
// }
return new SqlConnection(this.connectionString);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
/// <summary>
/// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1
/// </summary>
/// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>
public int ExecuteNonQuery(string sql)
{
using(sqlConnection = this.GetSqlConnection())
{
if (sqlConnection == null)
return -1;
try
{
if (sqlConnection.State == System.Data.ConnectionState.Closed)
sqlConnection.Open();
sqlCommand = new SqlCommand(sql, sqlConnection);
return sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return -1;
}
}
}
/// <summary>
/// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
/// </summary>
/// <param name="sql">SELECT 语句</param>
/// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用</returns>
public object ExecuteScalar(string sql)
{
using(sqlConnection = this.GetSqlConnection())
{
if (sqlConnection == null)
return null;
try
{
if (sqlConnection.State == System.Data.ConnectionState.Closed)
sqlConnection.Open();
sqlCommand = new SqlCommand(sql, sqlConnection);
return sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
}
/// <summary>
/// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回
/// </summary>
/// <param name="sql">SELECT 语句</param>
/// <returns>数据集 DataSet</returns>
public DataSet RetriveDataSet(string sql)
{
if (sql == null || sql == string.Empty)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
return null;
}
using(sqlConnection = this.GetSqlConnection())
{
if (sqlConnection == null)
return null;
using(SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
}
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
return null;
}
using(sqlConnection = this.GetSqlConnection())
{
if (sqlConnection == null)
return null;
DataSet ds = new DataSet();
int tableNameLength = tableName.Length;
for (int i = 0; i < sqlLength; i++)
{
using(SqlDataAdapter da = new SqlDataAdapter(sql[i], sqlConnection))
{
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.sqlCommand.Dispose();
this.sqlConnection.Dispose();
}
}
}
OdbcDataProvider.cs 提供ODBC连接访问的类
#define DEBUG
using System;
using System.Data;
using System.Data.Odbc;
namespace FenceKing.DataProviders
{
/// <summary>
/// OdbcDataProvider 的摘要说明。
/// </summary>
internal class OdbcDataProvider : IDataProvider
{
private System.Data.Odbc.OdbcConnection odbcConnection;
private System.Data.Odbc.OdbcCommand odbcCommand;
private string connectionString;
public OdbcDataProvider() : this(null)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public OdbcDataProvider(string connectionString)
{
if (connectionString == null || connectionString.Trim() == string.Empty)
{
System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
this.connectionString = (string)(configurationAppSettings.GetValue("odbcConnectionString", 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>
/// 返回一个带有连接字符串的Odbc Connection.
/// </summary>
/// <returns>OracleConnection</returns>
private OdbcConnection GetOdbcConnection()
{
try
{
// if (connectionString == null || connectionString.Trim() == string.Empty)
// {
// System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
// this.connectionString = ((string)(configurationAppSettings.GetValue("odbcConnectionString", typeof(string))));
// }
return new OdbcConnection(this.connectionString);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
/// <summary>
/// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1
/// </summary>
/// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>
public int ExecuteNonQuery(string sql)
{
using(odbcConnection = this.GetOdbcConnection())
{
if (odbcConnection == null)
return -1;
try
{
if (odbcConnection.State == System.Data.ConnectionState.Closed)
odbcConnection.Open();
odbcCommand = new OdbcCommand(sql, odbcConnection);
return odbcCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return -1;
}
}
}
/// <summary>
/// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
/// </summary>
/// <param name="sql">SELECT 语句</param>
/// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用</returns>
public object ExecuteScalar(string sql)
{
using(odbcConnection = this.GetOdbcConnection())
{
if (odbcConnection == null)
return null;
try
{
if (odbcConnection.State == System.Data.ConnectionState.Closed)
odbcConnection.Open();
odbcCommand = new OdbcCommand(sql, odbcConnection);
return odbcCommand.ExecuteScalar();
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
}
/// <summary>
/// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回
/// </summary>
/// <param name="selectSql">SELECT 语句</param>
/// <returns>数据集 DataSet</returns>
public DataSet RetriveDataSet(string sql)
{
if (sql == null || sql == string.Empty)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
return null;
}
using(odbcConnection = this.GetOdbcConnection())
{
if (odbcConnection == null)
return null;
using( OdbcDataAdapter da = new OdbcDataAdapter(sql, odbcConnection))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
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)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
return null;
}
using(odbcConnection = this.GetOdbcConnection())
{
if (odbcConnection == null)
return null;
DataSet ds = new DataSet();
int tableNameLength = tableName.Length;
for (int i = 0; i < sqlLength; i++)
{
using(OdbcDataAdapter da = new OdbcDataAdapter(sql[i], odbcConnection))
{
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 ds;
}
}
public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
{
return null;
}
public void Dispose()
{
this.connectionString = null;
this.odbcCommand.Dispose();
this.odbcConnection.Dispose();
}
}
}
OleDbDataProvider.cs 提供OLEDB连接访问的类。
#define DEBUG
using System;
using System.Data;
using System.Data.OleDb;
namespace FenceKing.DataProviders
{
/// <summary>
/// OleDbDataProvider 的摘要说明。
/// </summary>
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;
}
}
/// <summary>
/// OleDb 连接字符串
/// </summary>
public string ConnectionString
{
get
{
return this.connectionString;
}
set
{
this.connectionString = value;
}
}
/// <summary>
/// 返回一个带有连接字符串的OleDb Connection.
/// </summary>
/// <returns>OracleConnection</returns>
private OleDbConnection GetOleDbConnection()
{
try
{
// if (connectionString == null || connectionString.Trim() == string.Empty)
// {
// System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
// this.connectionString = ((string)(configurationAppSettings.GetValue("oleDbConnectionString", typeof(string))));
// }
return new OleDbConnection(this.connectionString);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return null;
}
}
/// <summary>
/// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1
/// </summary>
/// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>
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;
}
}
}
/// <summary>
/// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
/// </summary>
/// <param name="sql">SELECT 语句</param>
/// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用</returns>
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;
}
}
}
/// <summary>
/// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回
/// </summary>
/// <param name="selectSql">SELECT 语句</param>
/// <returns>数据集 DataSet</returns>
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;
}
}
}
/// <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)
{
#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 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);
}