一、数据源连接实体类；
/// <summary>
/// 数据源信息
/// </summary>
public class DataSourceInfo
{
/// <summary>
/// 数据源类型
/// </summary>
public DataSourceType DataSourceType { get; set; }
/// <summary>
/// 服务器
/// </summary>
public string Host { get; set; }
/// <summary>
/// 端口号
/// </summary>
public string Port { get; set; }
/// <summary>
/// 用户ID
/// </summary>
public string UserId { get; set; }
/// <summary>
/// 密码
/// </summary>
public string Password { get; set; }
/// <summary>
/// 数据库
/// </summary>
public string Database { get; set; }
}
二、构造多数据库连接
/// <summary>
/// 多数据库数据访问
/// </summary>
public class MultiDbDal
{
/// <summary>
/// 各类数据库对应的常量信息
/// </summary>
public static readonly Dictionary<DataSourceType, DataSourceConstInfo> TypeDicConnectStringFormat = new Dictionary<DataSourceType, DataSourceConstInfo>()
{
{DataSourceType.MySql,new DataSourceConstInfo()
{
DefaultPort ="3306",
Provider = "MySql.Data.MySqlClient",
ValidSqlText = "select 1",
FormatConnectingString = "Server={0};Port={1};Database={2};Uid={3};Pwd={4};"
} },
{DataSourceType.SqlServer,new DataSourceConstInfo()
{
//SqlServer也有默认端口1433，但是连接字符串不需要
DefaultPort =string.Empty,
Provider = "System.Data.SqlClient",
ValidSqlText = "select 1",
FormatConnectingString = "Server={0};Database={1};User Id={2};Password={3};;"
} },
{DataSourceType.Oracle,new DataSourceConstInfo()
{
DefaultPort ="1521",
Provider = "Oracle.DataAccess.Client",
ValidSqlText = "select 1 from dual",
FormatConnectingString = "Provider=OraOLEDB.Oracle;User ID={3};Password={4};Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1}))) (CONNECT_DATA = (SERVICE_NAME = {2})))"
} }
//{DataSourceType.Oracle,new DataSourceConstInfo()
//{
// DefaultPort ="1521",
// Provider = "Oracle.DataAccess.Client",
// ValidSqlText = "select 1 from dual",
// FormatConnectingString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};"
//}
};
/// <summary>
///
/// </summary>
public DataSourceInfo DataSourceInfo { get; set; }
public MultiDbDal(DataSourceInfo dataSourceInfo)
{
if (dataSourceInfo == null)
{
throw new ArgumentNullException(nameof(dataSourceInfo));
}
//标准连接：服务器 数据库 用户名 密码 必须要； 如果是windows登录后续再扩展
if (String.IsNullOrWhiteSpace(dataSourceInfo.Host))
{
throw new ArgumentNullException(nameof(dataSourceInfo.Host));
}
if (String.IsNullOrWhiteSpace(dataSourceInfo.Database))
{
throw new ArgumentNullException(nameof(dataSourceInfo.Database));
}
if (String.IsNullOrWhiteSpace(dataSourceInfo.UserId))
{
throw new ArgumentNullException(nameof(dataSourceInfo.UserId));
}
if (String.IsNullOrWhiteSpace(dataSourceInfo.Password))
{
throw new ArgumentNullException(nameof(dataSourceInfo.Password));
}
DataSourceInfo = dataSourceInfo;
}
/// <summary>
/// 构造连接字符串
/// </summary>
/// <returns></returns>
private string BuilderConnectionString()
{
var result = string.Empty;
DataSourceConstInfo dataConst;
if (TypeDicConnectStringFormat.TryGetValue(DataSourceInfo.DataSourceType, out dataConst))
{
var port = string.IsNullOrWhiteSpace(DataSourceInfo.Port) ? dataConst.DefaultPort : DataSourceInfo.Port;
var server = DataSourceInfo.Host;
var pwd = TripleDESHelper.Decrypt(DataSourceInfo.Password, "Mysoft123456");
if (DataSourceInfo.DataSourceType == DataSourceType.SqlServer)
{
//SqlServer只有4个参数
if (string.IsNullOrWhiteSpace(port) == false)
{
server += $",{port}";
}
result = string.Format(dataConst.FormatConnectingString, server, DataSourceInfo.Database,
DataSourceInfo.UserId, pwd);
}
else
{
result = string.Format(dataConst.FormatConnectingString, server, port, DataSourceInfo.Database,
DataSourceInfo.UserId, pwd);
}
}
return result;
}
/// <summary>
/// 是否连接成功，返回值为空则表示成功，如果有错误信息则表示连接失败
/// </summary>
/// <returns></returns>
public string IsConnectSuccess()
{
var result = String.Empty;
DataSourceConstInfo dataConst;
if (TypeDicConnectStringFormat.TryGetValue(DataSourceInfo.DataSourceType, out dataConst))
{
try
{
//Query(dataConst.ValidSqlText, dataSourceInfo);
}
catch /* 测试连接的需要抓取异常*/(Exception exception)
{
result = exception.Message;
}
}
return result;
}
/// <summary>
///
/// </summary>
/// <param name="sqlText"></param>
public DataTable QueryDataTable(string sqlText)
{
var connectionString = BuilderConnectionString();
switch (DataSourceInfo.DataSourceType)
{
case DataSourceType.MySql:
return MysqlQueryDataTable(sqlText, connectionString);
case DataSourceType.Oracle:
return OracleQueryDataTable(sqlText, connectionString);
case DataSourceType.SqlServer:
return SqlServerQueryDataTable(sqlText, connectionString);
default:
throw new ArgumentException(nameof(DataSourceInfo.DataSourceType));
}
}
/// <summary>
/// Mysql查询数据
/// </summary>
/// <param name="sqlText"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
private DataTable MysqlQueryDataTable(string sqlText, string connectionString)
{
DataSet dataset = new DataSet();
using (MySqlConnection con = new MySqlConnection(connectionString))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(sqlText, con))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dataset);
}
}
return dataset.Tables[0];
}
/// <summary>
/// sqlServer查询数据
/// </summary>
/// <param name="sqlText"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
private DataTable SqlServerQueryDataTable(string sqlText, string connectionString)
{
DataSet dataset = new DataSet();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sqlText, con))
{
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
}
}
return dataset.Tables[0];
}
/// <summary>
/// Oracle 查询数据
/// </summary>
/// <param name="sqlText"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
private DataTable OracleQueryDataTable(string sqlText, string connectionString)
{
DataSet dataset = new DataSet();
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
using (OleDbCommand cmd = new OleDbCommand(sqlText, con))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dataset);
}
}
return dataset.Tables[0];
}
///// <summary>
///// Oracle 查询数据
///// </summary>
///// <param name="sqlText"></param>
///// <param name="connectionString"></param>
///// <returns></returns>
//private DataTable OracleQueryDataTable(string sqlText, string connectionString)
//{
// DataSet dataset = new DataSet();
// using (OracleConnection con = new OracleConnection(connectionString))
// {
// con.Open();
// using (OracleCommand cmd = new OracleCommand(sqlText, con))
// {
// OracleDataAdapter adapter = new OracleDataAdapter(cmd);
// adapter.Fill(dataset);
// }
// }
// return dataset.Tables[0];
//}
}
PS: oracle 开始选择了OracleClient, 后面发现它有64位和32位之分，但是自己的工程是anyCpu。那么问题来了，如果在引的64位dll，就只能在64位环境运行了，32位同理；
以上只是一个查询的实例，如果可以，最好实现多个helper来执行对应的数据库操作。
