设计模式在实际业务应用中的介绍之1——抽象工厂模式实现对数据库访问组件的封装

基于C#打造的通用数据库访问组件

以前一直没有想过写一些东西来把项目中用到的知识点及技术实现做一个归纳整理并分享出来。现在打算逐渐的把项目中的一些东西整理并分享出来，与大家共勉！

基于C#打造的通用数据库访问组件，完全支持开闭原则，设计上支持可扩展支持任意主流数据库，目前组件只实现了Oracle、MS SQL、MySQL三种库。

该组件架构清晰，目前组件只包括5个类文件，兼容了三种数据库，组件实现采用了单例模式、工厂模式。

下面描述下实现思路：

5个类文件如下：DBFactorySingleton.cs、AbstractDBFactory.cs、OracleClientDBFactory.cs、MSSqlServerDBFactory.cs、MySqlDBFactory.cs

1、DBFactorySingleton文件采用单例模式管理组件访问工厂的建立

2、AbstractDBFactory文件采用抽象类描述了组件的相应方法及一些兼容多种数据库的常规应用方法

3、OracleClientDBFactory文件实现AbstractDBFactory的功能，专门用于处理Oracle数据库

4、MSSqlServerDBFactory文件实现AbstractDBFactory的功能，专门用于处理MSSqlServer数据库

5、MySqlDBFactory文件实现AbstractDBFactory的功能，专门用于处理MySql数据库

下面是组件的结构图：

下面是组件的类文件：

1、DBFactorySingleton文件：

using System;
using Common;
using FrameCommon;

namespace DataAccessLayer.DataBaseFactory
{
/// <summary>
/// 数据工厂管理器。
/// </summary>
public class DBFactorySingleton
{
static DBFactorySingleton dbFactorySingleton = null;

static string connStr = string.Empty; //保存当前数据连接串

AbstractDBFactory factory;
/// <summary>
/// 获得唯一实例。
/// </summary>
/// <returns></returns>
public static DBFactorySingleton GetInstance(DistributeDataNode ddn)
{
//这里需要支持不同数据库的单例，原则是数据连接变更需要重新实例单例
if (dbFactorySingleton == null)
{
dbFactorySingleton = new DBFactorySingleton(ddn);
connStr = ddn.Connectionstring;
}
else
{
if (connStr != ddn.Connectionstring)
{
dbFactorySingleton = new DBFactorySingleton(ddn);
connStr = ddn.Connectionstring;
}
}

return dbFactorySingleton;
}

/// <summary>
/// 数据库访问对象。
/// </summary>
public AbstractDBFactory Factory
{
get
{
return factory;
}
}

/// <summary>
/// 构造函数。
/// </summary>
private DBFactorySingleton(DistributeDataNode ddn)
{
//string dbFactoryName = APPConfig.GetAPPConfig().GetConfigValue("DBFactoryName", "");
string dbFactoryName = ddn.DbFactoryName;
if (dbFactoryName == "")
{
throw new Exception("no find DBFactoryName in config file!");
//return;
}
factory = (AbstractDBFactory) ClassBuilder.CreateObject(dbFactoryName);
if(factory==null)
throw new Exception("DBFactoryName find error in config file!");

//string connectString = APPConfig.GetAPPConfig().GetConfigValue("ConnectionString", "");
string connectString = ddn.Connectionstring;
if (connectString == "")
throw new Exception("find error for init database connection!");

//string dbschema = APPConfig.GetAPPConfig().GetConfigValue("Dbschema", "");
string dbschema = ddn.DbSchema;
if (dbschema == "")
throw new Exception("find error for init database schema!");

try
{
factory.ConnectionString = connectString;
factory.DbSchema = dbschema;
}
catch (Exception e)
{
throw new Exception("database connection error!\n" + e.ToString());
}
}

/// <summary>
/// 构建数据库连接器。
/// 该方式不支持多数据节点应用
/// </summary>
/// <param name="dbFactoryName"></param>
/// <param name="connectionstring"></param>
/// <param name="dbSchema"></param>
/// <returns></returns>
public AbstractDBFactory BuildFactory(string dbFactoryName, string connectionstring, string dbSchema)
{
factory = ClassBuilder.CreateObject(dbFactoryName) as AbstractDBFactory;
factory.ConnectionString = connectionstring;
factory.DbSchema = dbSchema;

return factory;
}
}
}

2、AbstractDBFactory文件：

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;

namespace DataAccessLayer.DataBaseFactory
{

/// <summary>
/// 执行SQL类型
/// </summary>
public enum SqlExecType
{
/// <summary>
/// 字符串
/// </summary>
SqlText,
/// <summary>
/// 存储过程
/// </summary>
SqlProcName
}

/// <summary>
/// 通用转换格式设定，目前只实现了日期的格式
/// </summary>
public enum ConvertFormat
{
intT,
yyyy,
yyyy_mm,
yyyy_mm_dd,
yyyy_mm_ddohh,
yyyy_mm_ddohhamm,
yyyy_mm_ddohhammass
}

/// <summary>
/// 转换参数说明，标识是转换的内容，还是数据字段
/// </summary>
public enum ConvertFlag
{
/// <summary>
/// 转换内容
/// </summary>
Value,
/// <summary>
/// 转换字段
/// </summary>
Field
}

/// <summary>
/// 访问数据库的数据库访问工厂。
/// </summary>
public abstract class AbstractDBFactory
{
protected string connectionString;

/// <summary>
/// 数据库链接字符串
/// </summary>
public string ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString = value;
}
}

string connectionKey = "";
/// <summary>
/// 数据库配置健名。对应于配置文件中的键名。
/// </summary>
public string ConnectionKey
{
get
{
return connectionKey;
}
set
{
connectionKey = value;
}
}

string dbSchema = "";
/// <summary>
/// 数据库对象拥有者
/// </summary>
public string DbSchema
{
get
{
return dbSchema;
}
set
{
dbSchema = value;
}
}

public IDbConnection cnn;

public IDbCommand cmd;

public IDataReader reader;

/// <summary>
/// 获得一个数据库链接。
/// </summary>
/// <returns>数据库链接</returns>
public virtual IDbConnection GetConnection()
{
return null;
}

public virtual IDbTransaction GetIDbTransaction()
{
return null;
}

/// <summary>
/// 获得一个数据库命令对象。
/// </summary>
/// <param name="sql">要执行的SQL语句或存储过程名</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="conn">数据库连接</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns>命令对象</returns>
public virtual IDbCommand GetCommand(string sql, SqlExecType sqlexectype, IDbConnection conn, IDbTransaction trans, params IDataParameter[] paras)
{
return null;
}

/// <summary>
/// 获取一个DataParameter对象。
/// </summary>
/// <param name="paraName">若参数名是oracle游标，则参数名固定为cur_rt</param>
/// <param name="type">若为oracle游标，则该参数传字符串类型即可</param>
/// <param name="paramValue">若为oracle游标，则该参数传空字符串即可</param>
/// <returns></returns>
public virtual IDataParameter GetDataParameter(string paraName, DbType type, object paramValue)
{
return null;
}

/// <summary>
/// 执行SQL，返回查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual Object ExecuteScalar(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
return null;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual DataSet GetDataSet(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
return null;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual DataTable GetDataTable(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
return null;
}

/// <summary>
/// 给大文本字符串赋值。
/// </summary>
/// <param name="txt"></param>
/// <param name="para"></param>
/// <param name="conn"></param>
/// <param name="tran"></param>
public virtual void SetCLOBVlaue(string txt, IDataParameter para, IDbConnection conn, IDbTransaction tran)
{

}

/// <summary>
/// 参数前面的符号
/// </summary>
/// <returns></returns>
public virtual string ParamSign()
{
return null;
}

/// <summary>
/// 获取数据库表结构
/// </summary>
/// <param name="dataEntName"></param>
/// <returns></returns>
public virtual DataTable GetDataEntity(string dataEntName)
{
return null;
}

/// <summary>
/// 获取系统时间
/// </summary>
/// <returns></returns>
public virtual string GetSystemDateTime()
{
return null;
}

/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="opPagerData"></param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual DataTable GetDataPager(FrameCommon.SSY_PagingExecuteParam opPagerData, params IDataParameter[] paras)
{
return null;
}

#region 相关转换函数

/// <summary>
/// 通用数据转换函数
/// </summary>
/// <param name="tarType"></param>
/// <param name="socExpress"></param>
/// <param name="format"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <param name="dateSplitChar">日期之间分割符号, 默认 -</param>
/// <param name="date2timeSplitChar">日期与时间之间分割符号默认空格</param>
/// <param name="timeSplitChar">时间之间分割符号，默认 :</param>
/// <returns></returns>
public virtual string ConvertTypeStr(DbType tarType, string socExpress, ConvertFormat format, bool opflag,
string dateSplitChar, string date2timeSplitChar, string timeSplitChar)
{
return "";
}

#endregion

#region 相关关键符号

/// <summary>
/// 拼接字符串
/// </summary>
/// <param name="opflags">拼接内容说明，要和内容一一对应（value 数值 field 字段）</param>
/// <param name="opfields">拼接内容, 可以是内容、字段</param>
/// <returns></returns>
public virtual string ConnectChar(List<ConvertFlag> opflags, List<string> opfields)
{
return "";
}

#endregion

#region 相关常规应用函数

/// <summary>
/// 转换大写
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string ToUpper(string soc, bool opflag)
{
return "";
}

/// <summary>
/// 转换小写
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string ToLower(string soc, bool opflag)
{
return "";
}

/// <summary>
/// 去掉空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string Trim(string soc, bool opflag)
{
return "";
}

/// <summary>
/// 去掉左空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string Ltrim(string soc, bool opflag)
{
return "";
}

/// <summary>
/// 去掉右空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string Rtrim(string soc, bool opflag)
{
return "";
}

/// <summary>
/// Length
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string Length(string soc, bool opflag)
{
return "";
}

/// <summary>
/// 字符串连接
/// </summary>
/// <param name="strs"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string ConcatString(string[] strs, bool opflag)
{
return "";
}

/// <summary>
/// 两个日期差
/// </summary>
/// <param name="datepart">仅支持yy MM dd HH mi ss ms </param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string Datediff(string datepart, string startDate, string endDate, bool opflag)
{
return "";
}

/// <summary>
/// 日期部分增加
/// </summary>
/// <param name="datepart">仅支持yy MM dd HH mi ss ms</param>
/// <param name="currDate"></param>
/// <param name="addValue"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public virtual string DateAdd(string datepart, string currDate, string addValue, bool opflag)
{
return "";
}

/// <summary>
/// v1 is null 取0
/// </summary>
/// <param name="v1">v1一般是字段，也可以取字段的聚合函数，如：sum(字段)</param>
/// <returns></returns>
public virtual string IsNull(string v1)
{
return "";
}

/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns></returns>
public virtual string GetDBDateTime()
{
return "";
}

#endregion
}

}

3、OracleClientDBFactory文件：

using System;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

namespace DataAccessLayer.DataBaseFactory
{
/// <summary>
/// 实现Oracle的数据库访问工厂
/// </summary>
public class OracleClientFactory : AbstractDBFactory
{
public OracleConnection cnn = null;
public OracleTransaction trans = null;
public OracleCommand cmd = null;
public OracleDataAdapter adapter = null;

/// <summary>
/// 获得一个数据库链接。
/// </summary>
/// <returns>数据库链接</returns>
public override IDbConnection GetConnection()
{
cnn = new OracleConnection(base.ConnectionString);
return cnn;
}

/// <summary>
/// 获取事务
/// </summary>
/// <returns></returns>
public override IDbTransaction GetIDbTransaction()
{
OracleConnection cnnTrans = (OracleConnection)this.GetConnection();

try
{
cnnTrans.Open();
trans = cnnTrans.BeginTransaction();
}
catch (Exception e)
{
throw new Exception("create transaction error!", e);
}

return trans;
}

/// <summary>
/// 获得一个数据库命令对象。
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="conn">数据库连接</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns>命令对象</returns>
public override IDbCommand GetCommand(string sql, SqlExecType sqlexectype, IDbConnection conn, IDbTransaction trans, params IDataParameter[] paras)
{
try
{
cmd = new OracleCommand();
cmd.CommandText = sql;
if (sqlexectype == SqlExecType.SqlText)
{
cmd.CommandType = CommandType.Text;
}
else if(sqlexectype == SqlExecType.SqlProcName)
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.Connection = (OracleConnection)conn;

//这里可能需要兼容处理oracle的bolb和colb数据字段，暂时先不处理，看看参数方式是否直接可以
this.PrepareCommand(cmd, paras);

if (trans != null)
{
cmd.Transaction = (OracleTransaction) trans;
}
}
catch (Exception e)
{
throw new Exception("create command error, please check sql script:" + sql, e);
}

return (IDbCommand)cmd;
}

/// <summary>
/// 构造参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="cmdParms"></param>
private void PrepareCommand(OracleCommand cmd, IDataParameter[] cmdParms)
{
if (cmdParms != null)
{
cmd.Parameters.Clear();
for (int i = 0; i < cmdParms.Length; i++)
{
OracleParameter OraParameter = (OracleParameter)cmdParms[i];
cmd.Parameters.Add(OraParameter);
}

//OracleParameter[] OraParameters =(OracleParameter[]) cmdParms;
//for (int i = 0; i < (int)OraParameters.Length; i++)
//{
// OracleParameter OraParameter = OraParameters[i];
// cmd.Parameters.Add(OraParameter);
//}
}
}

/// <summary>
/// 获取数据类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private OracleType GetDBType(DbType type)
{
OracleType dbType = OracleType.VarChar;

switch (type)
{
case DbType.Binary:
dbType = OracleType.Blob;
break;
case DbType.Boolean:
dbType = OracleType.Byte;
break;
case DbType.Date:
dbType = OracleType.DateTime;
break;
case DbType.Time:
dbType = OracleType.DateTime;
break;
case DbType.Single:
dbType = OracleType.Float;
break;
case DbType.Int32:
dbType = OracleType.Int32;
break;
case DbType.Currency:
dbType = OracleType.Float;
break;
case DbType.Double:
dbType = OracleType.Double;
break;
case DbType.Int64:
dbType = OracleType.Number;
break;
case DbType.String:
dbType = OracleType.VarChar;
break;
case DbType.Object:
dbType = OracleType.Blob;
break;
case DbType.AnsiString:
dbType = OracleType.Clob;
break;
case DbType.AnsiStringFixedLength:
dbType = OracleType.LongVarChar;
break;
}

return dbType;
}

/// <summary>
/// 获取一个DataParameter对象。
/// </summary>
/// <param name="paraName">参数名</param>
/// <param name="type">若是</param>
/// <param name="paramValue"></param>
/// <returns></returns>
public override IDataParameter GetDataParameter(string paraName, DbType type, object paramValue)
{
OracleParameter op = null;
OracleType dbType = GetDBType(type);
//处理游标，游标参数名默认cur_rt
if(paraName.ToUpper() == "cur_rt".ToUpper())
{
op = new OracleParameter(this.ParamSign() + paraName.Replace(":","").Replace("@", "").Replace("?", ""), OracleType.Cursor);
op.Value = paramValue;
op.Direction = ParameterDirection.Output;
}
else
{
op = new OracleParameter(this.ParamSign() + paraName.Replace(":", "").Replace("@", "").Replace("?", ""), dbType);
op.Value = paramValue;
op.Direction = ParameterDirection.Input;
}

return (IDataParameter)op;
}

/// <summary>
/// 执行SQL语句。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
public override int ExecuteNonQuery(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
int tempint = 0;
try
{
if (trans == null)
{
cnn = (OracleConnection)this.GetConnection();
if(cnn.State == ConnectionState.Closed)
{
cnn.Open();
}

cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if(trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}

tempint = cmd.ExecuteNonQuery();
//清除掉参数，以免二次使用.net取缓冲导致报错
cmd.Parameters.Clear();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
if (trans == null && cnn != null)
{
cnn.Close();
cnn.Dispose();
}
}

return tempint;
}

/// <summary>
/// 执行SQL，返回查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override Object ExecuteScalar(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
object tempobj = 0;

try
{
if (trans == null)
{
cnn = (OracleConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if (trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}

tempobj = cmd.ExecuteScalar();
//清除掉参数，以免二次使用.net取缓冲导致报错
cmd.Parameters.Clear();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
if (trans == null && cnn != null)
{
cnn.Close();
cnn.Dispose();
}
}

return tempobj;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到Read中返回。
/// </summary>
/// <param name="sql">要执行的SQL查询。</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns>返回一个包含查询结果的Read。</returns>
public override IDataReader GetReader(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
try
{
cnn =(OracleConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd =(OracleCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, null, paras);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除掉参数，以免二次使用.net取缓冲导致报错
cmd.Parameters.Clear();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
cnn.Close();
cnn.Dispose();
}

return reader;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataSet GetDataSet(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = new DataSet();
try
{
cnn = (OracleConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, cnn, null, paras);
adapter = new OracleDataAdapter(cmd);
adapter.Fill(ds, "tableName");
//清除掉参数，以免二次使用.net取缓冲导致报错
cmd.Parameters.Clear();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}

finally
{
adapter.Dispose();
cnn.Close();
cnn.Dispose();
}

return ds;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataTable GetDataTable(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = this.GetDataSet(sql, sqlexectype, paras);
if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}

/// <summary>
/// 处理大文本字符串。可能不完善，需要后续处理
/// </summary>
/// <returns></returns>
public override void SetCLOBVlaue(string txt, IDataParameter para, IDbConnection conn, IDbTransaction tran)
{
cmd = new OracleCommand();
if (tran != null)
cmd.Transaction = tran as OracleTransaction;
cmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
cmd.Connection = conn as OracleConnection;
OracleParameter p = new OracleParameter("tempclob", OracleType.Clob);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();

OracleLob tempLob = default(OracleLob);
tempLob = (OracleLob)cmd.Parameters[0].Value;

byte[] cbComments = Encoding.Unicode.GetBytes(txt);

tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(cbComments, 0, cbComments.Length);
tempLob.EndBatch();

para.Value = tempLob;
}

/// <summary>
/// 参数前面的符号
/// </summary>
/// <returns></returns>
public override string ParamSign()
{
return ":";
}

/// <summary>
/// 获取数据库表结构
/// </summary>
/// <param name="dataEntName"></param>
/// <returns></returns>
public override DataTable GetDataEntity(string dataEntName)
{
string sql = @"Select t1.TABLE_NAME,t1.COLUMN_NAME colname,t1.DATA_TYPE colType,t2.COMMENTS, t1.DATA_LENGTH, t1.NULLABLE
From USER_TAB_COLUMNS t1, USER_COL_COMMENTS t2
Where t1.TABLE_NAME = t2.TABLE_NAME And t1.COLUMN_NAME = t2.COLUMN_NAME And t1.TABLE_NAME = upper({0}TABLE_NAME)
Order By t1.COLUMN_ID";
sql = string.Format(sql, this.ParamSign());
OracleParameter op = null;
op = new OracleParameter(this.ParamSign() + "TABLE_NAME", OracleType.VarChar);
op.Value = dataEntName;
op.Direction = ParameterDirection.Input;

DataSet ds = this.GetDataSet(sql, SqlExecType.SqlText, op);

if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}

/// <summary>
/// 获取系统时间
/// </summary>
/// <returns></returns>
public override string GetSystemDateTime()
{
string sql = @"select sysdate from dual";
DataSet ds = this.GetDataSet(sql, SqlExecType.SqlText, null);

if(Common.Utility.DsHasData(ds))
{
return Convert.ToDateTime(ds.Tables[0].Rows[0][0].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
}

return "";
}

/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="opPagerData"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataTable GetDataPager(FrameCommon.SSY_PagingExecuteParam opPagerData, params IDataParameter[] paras)
{
if(opPagerData.PagingParam.TotalSize > 0)
{
//实现获取分页数据
StringBuilder sbbSql = new StringBuilder();
string sortType = string.Empty; //排序类型

if (opPagerData.PagingParam.SortType == FrameCommon.AscOrDesc.Asc)
{
sortType = " asc ";
}
else
{
sortType = " desc ";
}

if (string.IsNullOrEmpty(opPagerData.SqlWhere))
{
sbbSql.Append(string.Format(@" Select * FROM (Select Rownum rowcnt,
QR.* from (Select {0} from {1} {2} order by {3} {4} ", opPagerData.Fields, opPagerData.TableNameOrView,
opPagerData.Joins, opPagerData.OrderField, sortType));
}
else
{
sbbSql.Append(string.Format(@" Select * FROM (Select Rownum As rowcnt,
QR.* from (Select {0} from {1} {2} where {3} order by {4} {5} ", opPagerData.Fields,
opPagerData.TableNameOrView, opPagerData.Joins, opPagerData.SqlWhere, opPagerData.OrderField, sortType));
}

//区间段
int startRecord = (opPagerData.PagingParam.PageIndex - 1) * opPagerData.PagingParam.PageSize + 1;
int endRecord = startRecord + opPagerData.PagingParam.PageSize - 1;

sbbSql.Append(string.Format(@" ) QR) where rowcnt between {0} and {1} ", startRecord.ToString(),
endRecord.ToString()));

return this.GetDataTable(sbbSql.ToString(), SqlExecType.SqlText, paras);
}
else
{
return new DataTable();
}
}

#region 相关转换函数

/// <summary>
/// 通用数据转换函数
/// </summary>
/// <param name="tarType"></param>
/// <param name="socExpress"></param>
/// <param name="formatp"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <param name="dateSplitChar">日期之间分割符号, 默认 -</param>
/// <param name="date2timeSplitChar">日期与时间之间分割符号默认空格</param>
/// <param name="timeSplitChar">时间之间分割符号，默认 :</param>
/// <returns></returns>
public override string ConvertTypeStr(DbType tarType, string socExpress, ConvertFormat formatp, bool opflag,
string dateSplitChar, string date2timeSplitChar, string timeSplitChar)
{
string format = string.Empty; //格式处理
if (string.IsNullOrEmpty(dateSplitChar)) { dateSplitChar = "-"; }
if (string.IsNullOrEmpty(date2timeSplitChar)) { date2timeSplitChar = " "; }
if (string.IsNullOrEmpty(timeSplitChar)) { timeSplitChar = ":"; }
if (formatp == ConvertFormat.yyyy)
{
format = "yyyy";
}
else if (formatp == ConvertFormat.yyyy_mm)
{
format = "yyyy_mm".Replace("_", dateSplitChar);
}
else if (formatp == ConvertFormat.yyyy_mm_dd)
{
format = "yyyy_mm_dd".Replace("_", dateSplitChar);
}
else if (formatp == ConvertFormat.yyyy_mm_ddohh)
{
format = "yyyy_mm_ddoHH24".Replace("_", dateSplitChar).Replace("o", date2timeSplitChar);
}
else if (formatp == ConvertFormat.yyyy_mm_ddohhamm)
{
format = "yyyy_mm_ddoHH24ami".Replace("_", dateSplitChar).Replace("o", date2timeSplitChar).Replace("a", timeSplitChar);
}
else if (formatp == ConvertFormat.yyyy_mm_ddohhammass)
{
format = "yyyy_mm_ddoHH24amiass".Replace("_", dateSplitChar).Replace("o", date2timeSplitChar).Replace("a", timeSplitChar);
}
string resStr = string.Empty;
if(tarType == DbType.String)
{
if(string.IsNullOrEmpty(format))
{
if(opflag)
{
resStr = string.Format(" to_char('{0}') ", socExpress.ToString());
}
else
{
resStr = string.Format(" to_char({0}) ", socExpress.ToString());
}
}
else
{
if (opflag)
{
resStr = string.Format(" to_char('{0}', '{1}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" to_char({0}, '{1}') ", socExpress.ToString(), format);
}
}
}
else if (tarType == DbType.Int16 || tarType == DbType.Int32 || tarType == DbType.Int64)
{
if (string.IsNullOrEmpty(format))
{
if(opflag)
{
resStr = string.Format(" to_number('{0}') ", socExpress.ToString());
}
else
{
resStr = string.Format(" to_number({0}) ", socExpress.ToString());
}
}
else
{
if(opflag)
{
resStr = string.Format(" to_number('{0}', '{1}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" to_number({0}, '{1}') ", socExpress.ToString(), format);
}
}
}
else if (tarType == DbType.DateTime)
{
if (string.IsNullOrEmpty(format))
{
throw new Exception("transfering time format must to set format!");
}
else
{
if(opflag)
{
resStr = string.Format(" to_date('{0}', '{1}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" to_date({0}, '{1}') ", socExpress.ToString(), format);
}
}
}

//后续继续扩展

return resStr;
}

#endregion

#region 相关关键符号

/// <summary>
/// 拼接字符串
/// </summary>
/// <param name="opflags">拼接内容说明，要和内容一一对应（value 数值 field 字段）</param>
/// <param name="opfields">拼接内容, 可以是内容、字段</param>
/// <returns></returns>
public override string ConnectChar(List<ConvertFlag> opflags, List<string> opfields)
{
//return "||";

StringBuilder resStr = new StringBuilder();

for (int i = 0; i < opfields.Count; i++)
{
if (i == 0)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "' || ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " || ");
}
}
else if (i > 0 && i < opfields.Count - 1)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "' || ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " || ");
}
}
else if (i == opfields.Count - 1)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "' ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " ");
}
}
}

return resStr.ToString();
}

#endregion

#region 相关常规应用函数

/// <summary>
/// 转换大写
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string ToUpper(string soc, bool opflag)
{
if(opflag)
{
return string.Format(" upper('{0}') ", soc);
}
else
{
return string.Format(" upper({0}) ", soc);
}
}

/// <summary>
/// 转换小写
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string ToLower(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" lower('{0}') ", soc);
}
else
{
return string.Format(" lower({0}) ", soc);
}
}

/// <summary>
/// 去掉空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Trim(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" trim('{0}') ", soc);
}
else
{
return string.Format(" trim({0}) ", soc);
}
}

/// <summary>
/// 去掉左空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Ltrim(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" ltrim('{0}') ", soc);
}
else
{
return string.Format(" ltrim({0}) ", soc);
}
}

/// <summary>
/// 去掉右空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Rtrim(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" rtrim('{0}') ", soc);
}
else
{
return string.Format(" rtrim({0}) ", soc);
}
}

/// <summary>
/// 从左边截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Left(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" substr('{0}', 0, {1}) ", soc, n.ToString());
}
else
{
return string.Format(" substr({0}, 0, {1}) ", soc, n.ToString());
}
}

/// <summary>
/// 从右边截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Right(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" substr('{0}', {1}, {2}) ", soc, (soc.Length - n + 1).ToString(), n.ToString());
}
else
{
return string.Format(" substr({0}, {1}, {2}) ", soc, (soc.Length - n + 1).ToString(), n.ToString());
}
}

/// <summary>
/// 截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="startIndex"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Substring(string soc, int startIndex, int n, bool opflag)
{
if (opflag)
{
return string.Format(" substr('{0}', {1}, {2}) ", soc, startIndex.ToString(), n.ToString());
}
else
{
return string.Format(" substr({0}, {1}, {2}) ", soc, startIndex.ToString(), n.ToString());
}
}

/// <summary>
/// 字符串替换
/// </summary>
/// <param name="soc"></param>
/// <param name="oldStr"></param>
/// <param name="newStr"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Replace(string soc, string oldStr, string newStr, bool opflag)
{
if (opflag)
{
return string.Format(" replace('{0}', '{1}', '{2}') ", soc, oldStr, newStr);
}
else
{
return string.Format(" replace({0}, '{1}', '{2}') ", soc, oldStr, newStr);
}
}

/// <summary>
/// Length
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Length(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" lenght('{0}') ", soc);
}
else
{
return string.Format(" lenght({0}) ", soc);
}
}

/// <summary>
/// 字符串连接
/// </summary>
/// <param name="strs"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string ConcatString(string[] strs, bool opflag)
{
string tempStr = string.Empty;
for (int i = 0; i < strs.Length; i++)
{
if (opflag)
{
tempStr += "||" + "'" + strs[i] + "'";
}
else
{
tempStr += "||" + strs[i];
}
}

return string.Format(" {0} ", tempStr.TrimStart("||".ToCharArray()));
}

/// <summary>
/// 左补空格
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Lpad(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" lpad('{0}', {1} ) ", soc, n.ToString());
}
else
{
return string.Format(" lpad({0}, {1} ) ", soc, n.ToString());
}
}

/// <summary>
/// 右边补空格
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Rpad(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" rpad('{0}', {1} ) ", soc, n.ToString());
}
else
{
return string.Format(" rpad({0}, {1} ) ", soc, n.ToString());
}
}

/// <summary>
/// 两个日期差
/// </summary>
/// <param name="datepart">仅支持yy MM dd HH mi ss</param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Datediff(string datepart, string startDate, string endDate, bool opflag)
{
string resStr = string.Empty;
string parts = "yy|MM|dd|HH|mi|ss";
if(parts.IndexOf(datepart) == -1)
{
throw new Exception("date format error!");
}

string departFormat = string.Empty;
if(datepart.ToUpper() == "YY")
{
departFormat = "/365";
}
else if (datepart.ToUpper() == "MM")
{
departFormat = "/12";
}
else if (datepart.ToUpper() == "DD")
{
departFormat = "*1";
}
else if (datepart.ToUpper() == "HH")
{
departFormat = "*24";
}
else if (datepart.ToUpper() == "MI")
{
departFormat = "*24*60";
}
else if (datepart.ToUpper() == "SS")
{
departFormat = "*24*60*60";
}

if (opflag)
{
resStr = string.Format(" (to_date('{0}', 'yyyy-MM-dd HH:mi:ss') - to_date('{1}', 'yyyy-MM-dd HH:mi:ss')) {2} ", startDate, endDate, departFormat);
}
else
{
resStr = string.Format(" (to_date({0}, 'yyyy-MM-dd HH:mi:ss') - to_date({1}, 'yyyy-MM-dd HH:mi:ss')) {2} ", startDate, endDate, departFormat);
}

return resStr;
}

/// <summary>
/// 日期部分增加
/// </summary>
/// <param name="datepart">仅支持yy MM dd HH mi ss</param>
/// <param name="currDate"></param>
/// <param name="addValue"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string DateAdd(string datepart, string currDate, string addValue, bool opflag)
{
string resStr = string.Empty;
string parts = "yy|MM|dd|HH|mi|ss";
if (parts.IndexOf(datepart) == -1)
{
throw new Exception("date add format error!");
}
string departFormat = string.Empty;
if (datepart.ToUpper() == "YY")
{
departFormat = string.Format("+ {0}/365", addValue);
}
else if (datepart.ToUpper() == "MM")
{
departFormat = string.Format("+ {0}/12", addValue);
}
else if (datepart.ToUpper() == "DD")
{
departFormat = string.Format("+ {0}/1", addValue);
}
else if (datepart.ToUpper() == "HH")
{
departFormat = string.Format("+ {0}*24", addValue);
}
else if (datepart.ToUpper() == "MI")
{
departFormat = string.Format("+ {0}*24*60", addValue);
}
else if (datepart.ToUpper() == "SS")
{
departFormat = string.Format("+ {0}*24*60*60", addValue);
}

if (opflag)
{
resStr = string.Format(" to_date('{0}', 'yyyy-MM-dd HH:mi:ss') {1} ", currDate, departFormat);
}
else
{
resStr = string.Format(" to_date({0}, 'yyyy-MM-dd HH:mi:ss') {1} ", currDate, departFormat);
}

return resStr;
}

/// <summary>
/// 解析固定内容显示值
/// </summary>
/// <param name="soc">原始值</param>
/// <param name="anaV">固定内容值例如：value1|display1, ...</param>
/// <param name="defaultV"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Decode(string soc, string anaV, string defaultV, bool opflag)
{
//decode('a2','a1','true1','a2','true2','default')
string anaStr = string.Empty;
string[] anaVs = anaV.Split(',');
for (int i = 0; i < anaVs.Length; i++)
{
anaStr += string.Format("'{0}','{1}'", anaVs[i].Split('|')[0], anaVs[i].Split('|')[1]) + ",";
}

if (opflag)
{
return string.Format(" decode('{0}', {1} '{2}') ", soc, anaStr, defaultV);
}
else
{
return string.Format(" decode({0}, {1} '{2}') ", soc, anaStr, defaultV);
}
}

/// <summary>
/// v1 is null 取v2 否则取v1
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Nvl(string v1, string v2, bool opflag)
{
//NVL(null, '12')
if (opflag)
{
return string.Format(" nvl('{0}', '{1}') ", v1, v2);
}
else
{
return string.Format(" nvl({0}, '{1}') ", v1, v2);
}
}

/// <summary>
/// v1 not null 取v2, v1 is null取v3
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="v3"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Nvl2(string v1, string v2, string v3, bool opflag)
{
//nvl2('a', 'b', 'c')
if (opflag)
{
return string.Format(" nvl2('{0}', '{1}', '{2}') ", v1, v2, v3);
}
else
{
return string.Format(" nvl2({0}, '{1}', '{2}') ", v1, v2, v3);
}
}

/// <summary>
/// v1 = v2取null 否则取v1
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string NullIf(string v1, string v2, bool opflag)
{
// NULLIF('a','b')
if (opflag)
{
return string.Format(" nullif('{0}', '{1}') ", v1, v2);
}
else
{
return string.Format(" nullif({0}, '{1}') ", v1, v2);
}
}

/// <summary>
/// v1 is null 取0
/// </summary>
/// <param name="v1">v1一般是字段，也可以取字段的聚合函数，如：sum(字段)</param>
/// <returns></returns>
public override string IsNull(string v1)
{
return string.Format(" nvl({0}, '0') ", v1);
}

/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns></returns>
public override string GetDBDateTime()
{
return " sysdate ";
}

#endregion

}
}

4、MSSqlServerDBFactory文件：

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Sql;

namespace DataAccessLayer.DataBaseFactory
{
/// <summary>
/// 实现sql server的数据库访问工厂
/// </summary>
public class MSSqlServerDBFactory : AbstractDBFactory
{
public SqlConnection cnn = null;
public SqlTransaction trans = null;
public SqlCommand cmd = null;
public SqlDataAdapter adapter = null;

/// <summary>
/// 获得一个数据库链接。
/// </summary>
/// <returns>数据库链接</returns>
public override IDbConnection GetConnection()
{
cnn = new SqlConnection(base.ConnectionString);
return cnn;
}

/// <summary>
/// 获取事务
/// </summary>
/// <returns></returns>
public override IDbTransaction GetIDbTransaction()
{
SqlConnection cnnTrans = (SqlConnection)this.GetConnection();

try
{
cnnTrans.Open();
trans = cnnTrans.BeginTransaction();
}
catch (Exception e)
{
throw new Exception("create transaction error!", e);
}

return trans;
}

/// <summary>
/// 获得一个数据库命令对象。
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="conn">数据库连接</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns>命令对象</returns>
public override IDbCommand GetCommand(string sql, SqlExecType sqlexectype, IDbConnection conn, IDbTransaction trans, params IDataParameter[] paras)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = sql;
if (sqlexectype == SqlExecType.SqlText)
{
cmd.CommandType = CommandType.Text;
}
else if (sqlexectype == SqlExecType.SqlProcName)
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.Connection = (SqlConnection)conn;

//这里可能需要兼容处理大数据字段，暂时先不处理，看看参数方式是否直接可以
this.PrepareCommand(cmd, paras);

if (trans != null)
{
cmd.Transaction = (SqlTransaction)trans;
}
}
catch (Exception e)
{
throw new Exception("create command error, please check sql script:" + sql, e);
}

return (IDbCommand)cmd;
}

/// <summary>
/// 构造参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="cmdParms"></param>
private void PrepareCommand(SqlCommand cmd, IDataParameter[] cmdParms)
{
if (cmdParms != null)
{
cmd.Parameters.Clear();
for (int i = 0; i < cmdParms.Length; i++)
{
//SqlParameter pp = (SqlParameter)((ICloneable)p).Clone();
//SqlParameter OraParameter = (SqlParameter)cmdParms[i];
//这里会发送重复添加参数问题，克隆处理下
SqlParameter OraParameter = (SqlParameter)((ICloneable)(SqlParameter)cmdParms[i]).Clone();
cmd.Parameters.Add(OraParameter);
}

/// <summary>
/// 获取数据类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private SqlDbType GetDBType(DbType type)
{
SqlDbType dbType = SqlDbType.VarChar;

switch (type)
{
case DbType.Binary:
dbType = SqlDbType.Binary;
break;
case DbType.Boolean:
dbType = SqlDbType.Bit;
break;
case DbType.Date:
dbType = SqlDbType.DateTime2;
break;
case DbType.Time:
dbType = SqlDbType.DateTime2;
break;
case DbType.Single:
dbType = SqlDbType.Float;
break;
case DbType.Int32:
dbType = SqlDbType.Int;
break;
case DbType.Currency:
dbType = SqlDbType.Decimal;
break;
case DbType.Double:
dbType = SqlDbType.Float;
break;
case DbType.Int64:
dbType = SqlDbType.BigInt;
break;
case DbType.String:
dbType = SqlDbType.VarChar;
break;
case DbType.Object:
dbType = SqlDbType.Binary;
break;
case DbType.AnsiString:
dbType = SqlDbType.VarChar;
break;
case DbType.AnsiStringFixedLength:
dbType = SqlDbType.VarChar;
break;
}

return dbType;
}

op = new SqlParameter(this.ParamSign() + paraName.Replace(":", "").Replace("@", "").Replace("?", ""), dbType);
op.Value = paramValue;
op.Direction = ParameterDirection.Input;

return (IDataParameter)op;
}

cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if (trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}

tempint = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
if (trans == null && cnn != null)
{
cnn.Close();
cnn.Dispose();
}
}

return tempint;
}

/// <summary>
/// 执行SQL，返回查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override Object ExecuteScalar(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
object tempobj = 0;

try
{
if (trans == null)
{
cnn = (SqlConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if (trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}

tempobj = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
if (trans == null && cnn != null)
{
cnn.Close();
cnn.Dispose();
}
}

return tempobj;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到Read中返回。
/// </summary>
/// <param name="sql">要执行的SQL查询。</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns>返回一个包含查询结果的Read。</returns>
public override IDataReader GetReader(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
try
{
cnn = (SqlConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, null, paras);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
cnn.Close();
cnn.Dispose();
}

return reader;
}

adapter = new SqlDataAdapter(cmd);

adapter.Fill(ds, "tableName");
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}

finally
{
adapter.Dispose();
cnn.Close();
cnn.Dispose();
}

return ds;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataTable GetDataTable(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = this.GetDataSet(sql, sqlexectype, paras);

if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}

/// <summary>
/// 参数前面的符号
/// </summary>
/// <returns></returns>
public override string ParamSign()
{
return "@";
}

/// <summary>
/// 获取数据库表结构
/// </summary>
/// <param name="dataEntName"></param>
/// <returns></returns>
public override DataTable GetDataEntity(string dataEntName)
{
string sql = @" select c.name TABLE_NAME, t.name colname, d.name colType, cr.value COMMENTS,
t.max_length DATA_LENGTH , t.is_nullable NULLABLE
from sys.all_columns t
inner join sys.all_objects c
on t.object_id = c.object_id
inner join sys.types d
on t.user_type_id = d.user_type_id
left join sys.extended_properties cr
on t.object_id = cr.major_id
and t.column_id = cr.minor_id
where {0} = upper({1}TABLE_NAME)
Order By t.column_id";
sql = string.Format(sql, this.ToUpper("c.name", false), this.ParamSign());
SqlParameter op = null;
op = new SqlParameter(this.ParamSign() + "TABLE_NAME", SqlDbType.VarChar);
op.Value = dataEntName;
op.Direction = ParameterDirection.Input;

DataSet ds = this.GetDataSet(sql, SqlExecType.SqlText, op);

if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}

/// <summary>
/// 获取系统时间
/// </summary>
/// <returns></returns>
public override string GetSystemDateTime()
{
string sql = @"select getdate() ";
DataSet ds = this.GetDataSet(sql, SqlExecType.SqlText, null);

if (Common.Utility.DsHasData(ds))
{
return Convert.ToDateTime(ds.Tables[0].Rows[0][0].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
}

return "";
}

/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="opPagerData"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataTable GetDataPager(FrameCommon.SSY_PagingExecuteParam opPagerData, params IDataParameter[] paras)
{
if (opPagerData.PagingParam.TotalSize > 0)
{
//实现获取分页数据
StringBuilder sbbSql = new StringBuilder();
string sortType = string.Empty; //排序类型

if (opPagerData.PagingParam.SortType == FrameCommon.AscOrDesc.Asc)
{
sortType = " asc ";
}
else
{
sortType = " desc ";
}

if (string.IsNullOrEmpty(opPagerData.SqlWhere))
{
sbbSql.Append(string.Format(@" Select * FROM (select ROW_NUMBER() Over(order by {0} {1}) as rowId, {2} from {3} {4} ",
opPagerData.OrderField, sortType, opPagerData.Fields, opPagerData.TableNameOrView, opPagerData.Joins));
}
else
{
sbbSql.Append(string.Format(@" Select * FROM (select ROW_NUMBER() Over(order by {0} {1}) as rowId, {2} from {3} {4} where {5} ",
opPagerData.OrderField, sortType, opPagerData.Fields, opPagerData.TableNameOrView, opPagerData.Joins, opPagerData.SqlWhere));
}

//区间段
int startRecord = (opPagerData.PagingParam.PageIndex - 1) * opPagerData.PagingParam.PageSize + 1;
int endRecord = startRecord + opPagerData.PagingParam.PageSize - 1;

sbbSql.Append(string.Format(@" ) as t where rowId between {0} and {1} ", startRecord.ToString(),
endRecord.ToString()));

return this.GetDataTable(sbbSql.ToString(), SqlExecType.SqlText, paras);
}
else
{
return new DataTable();
}
}

#region 相关转换函数

string resStr = string.Empty;
if (tarType == DbType.String)
{
if (string.IsNullOrEmpty(format))
{
if (opflag)
{
resStr = string.Format(" convert(varchar, '{0}') ", socExpress.ToString());
}
else
{
resStr = string.Format(" convert(varchar, {0}) ", socExpress.ToString());
}
}
else
{
if (opflag)
{
resStr = string.Format(" convert(varchar, '{0}', '{1}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" convert(varchar, {0}, '{1}') ", socExpress.ToString(), format);
}
}
}
else if (tarType == DbType.Int16 || tarType == DbType.Int32 || tarType == DbType.Int64)
{
if (string.IsNullOrEmpty(format))
{
if (opflag)
{
resStr = string.Format(" convert(int, '{0}') ", socExpress.ToString());
}
else
{
resStr = string.Format(" convert(int, {0}) ", socExpress.ToString());
}
}
else
{
if (opflag)
{
resStr = string.Format(" convert(int, '{0}', '{1}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" convert(int, {0}, '{1}') ", socExpress.ToString(), format);
}
}
}
else if (tarType == DbType.DateTime)
{
format = "any"; //sql无需指定格式，只要是日期格式即可
if (string.IsNullOrEmpty(format))
{
throw new Exception("转换时间时必须指定format格式！");
}
else
{
if (opflag)
{
resStr = string.Format(" convert(datetime, '{0}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" convert(datetime, {0}) ", socExpress.ToString(), format);
}
}
}

//后续继续扩展

return resStr;
}

#endregion

#region 相关关键符号

/// <summary>
/// 拼接字符串
/// </summary>
/// <param name="opflags">拼接内容说明，要和内容一一对应（value 数值 field 字段）</param>
/// <param name="opfields">拼接内容, 可以是内容、字段</param>
/// <returns></returns>
public override string ConnectChar(List<ConvertFlag> opflags, List<string> opfields)
{
//return "+";
StringBuilder resStr = new StringBuilder();

for (int i = 0; i < opfields.Count; i++)
{
if (i == 0)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "' + ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " + ");
}
}
else if (i > 0 && i < opfields.Count - 1)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "' + ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " + ");
}
}
else if (i == opfields.Count - 1)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "' ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " ");
}
}
}

return resStr.ToString();
}

#endregion

#region 相关常规应用函数

/// <summary>
/// 转换大写
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string ToUpper(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" upper('{0}') ", soc);
}
else
{
return string.Format(" upper({0}) ", soc);
}
}

/// <summary>
/// 去掉空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Trim(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" replace('{0}', ' ', '') ", soc);
}
else
{
return string.Format(" replace({0}, ' ', '') ", soc);
}
}

/// <summary>
/// 从左边截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Left(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" left('{0}', 0, {1}) ", soc, n.ToString());
}
else
{
return string.Format(" left({0}, 0, {1}) ", soc, n.ToString());
}
}

/// <summary>
/// 从右边截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Right(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" right('{0}', {1}) ", soc, n.ToString());
}
else
{
return string.Format(" right({0}, {1}) ", soc, n.ToString());
}
}

/// <summary>
/// 截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="startIndex"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Substring(string soc, int startIndex, int n, bool opflag)
{
if (opflag)
{
return string.Format(" substring('{0}', {1}, {2}) ", soc, startIndex.ToString(), n.ToString());
}
else
{
return string.Format(" substring({0}, {1}, {2}) ", soc, startIndex.ToString(), n.ToString());
}
}

/// <summary>
/// Length
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Length(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" len('{0}') ", soc);
}
else
{
return string.Format(" len({0}) ", soc);
}
}

/// <summary>
/// 字符串连接
/// </summary>
/// <param name="strs"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string ConcatString(string[] strs, bool opflag)
{
string tempStr = string.Empty;
for (int i = 0; i < strs.Length; i++)
{
if (opflag)
{
tempStr += "+" + "'" + strs[i] + "'";
}
else
{
tempStr += "+" + strs[i];
}
}

return string.Format(" {0} ", tempStr);
}

/// <summary>
/// 左补空格
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Lpad(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" space({1}) + '{0}' ", soc, n.ToString());
}
else
{
return string.Format(" space({1}) + '{0}' ", soc, n.ToString());
}
}

/// <summary>
/// 右边补空格
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Rpad(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" '{0}' + space({1}) ", soc, n.ToString());
}
else
{
return string.Format(" {0} + space({1}) ", soc, n.ToString());
}
}

/// <summary>
/// 两个日期差
/// </summary>
/// <param name="datepart">仅支持yy MM dd HH mi ss</param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Datediff(string datepart, string startDate, string endDate, bool opflag)
{
string resStr = string.Empty;
string parts = "yy|MM|dd|HH|mi|ss";
if (parts.IndexOf(datepart) == -1)
{
throw new Exception("date format error!");
}

if (opflag)
{
resStr = string.Format(" datediff({2}, convert(datetime, '{0}'), convert(datetime, '{1}')) ",
startDate, endDate, datepart);
}
else
{
resStr = string.Format(" datediff({2}, convert(datetime, {0}), convert(datetime, {1})) ",
startDate, endDate, datepart);
}

return resStr;
}

if (opflag)
{
resStr = string.Format(" dateadd({0}, {1}, convert(datetime, '{2}')) ", datepart, addValue, currDate);
}
else
{
resStr = string.Format(" dateadd({0}, {1}, convert(datetime, {2})) ", datepart, addValue, currDate);
}

return resStr;
}

/// <summary>
/// 解析固定内容显示值
/// </summary>
/// <param name="soc">原始值</param>
/// <param name="anaV">固定内容值例如：value1|display1, ...</param>
/// <param name="defaultV"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Decode(string soc, string anaV, string defaultV, bool opflag)
{
//select case '2'
//when '1' then '2'
//when '2' then '2'
//when '3' then '2'
//else '4'
//end
StringBuilder resSb = new StringBuilder();
string[] anaVs = anaV.Split(',');
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", soc));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", soc));
}

for (int i = 0; i < anaVs.Length; i++)
{
resSb.AppendLine(string.Format(" when '{0}' then '{1}' ", anaVs[i].Split('|')[0], anaVs[i].Split('|')[1]));
}

resSb.AppendLine(string.Format(" else '{0}' ", defaultV));
resSb.AppendLine(" end ");

return resSb.ToString();
}

/// <summary>
/// v1 is null 取v2 否则取v1
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Nvl(string v1, string v2, bool opflag)
{
//NVL(null, '12')
StringBuilder resSb = new StringBuilder();
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", v1));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", v1));
}

resSb.AppendLine(string.Format(" when '{0}' then '{0}' ", v1));
resSb.AppendLine(string.Format(" else '{0}' ", v2));
resSb.AppendLine(" end ");

return resSb.ToString();

}

/// <summary>
/// v1 not null 取v2, v1 is null取v3
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="v3"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Nvl2(string v1, string v2, string v3, bool opflag)
{
//nvl2('a', 'b', 'c')
StringBuilder resSb = new StringBuilder();
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", v1));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", v1));
}

resSb.AppendLine(string.Format(" when '{0}' then '{1}' ", v2));
resSb.AppendLine(string.Format(" else '{0}' ", v3));
resSb.AppendLine(" end ");

return resSb.ToString();
}

/// <summary>
/// v1 = v2取null 否则取v1
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string NullIf(string v1, string v2, bool opflag)
{
// NULLIF('a','b')
StringBuilder resSb = new StringBuilder();
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", v1));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", v1));
}

resSb.AppendLine(string.Format(" when '{0}' then null ", v2));
resSb.AppendLine(string.Format(" else '{0}' ", v1));
resSb.AppendLine(" end ");

return resSb.ToString();
}

/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns></returns>
public override string GetDBDateTime()
{
return " GETDATE() ";
}

#endregion
}
}

5、MySqlDBFactory文件：

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace DataAccessLayer.DataBaseFactory
{
/// <summary>
/// 实现sql server的数据库访问工厂
/// </summary>
public class MySqlDBFactory : AbstractDBFactory
{
public MySqlConnection cnn = null;
public MySqlTransaction trans = null;
public MySqlCommand cmd = null;
public MySqlDataAdapter adapter = null;

/// <summary>
/// 获得一个数据库链接。
/// </summary>
/// <returns>数据库链接</returns>
public override IDbConnection GetConnection()
{
cnn = new MySqlConnection(base.ConnectionString);
return cnn;
}

/// <summary>
/// 获取事务
/// </summary>
/// <returns></returns>
public override IDbTransaction GetIDbTransaction()
{
MySqlConnection cnnTrans = (MySqlConnection)this.GetConnection();

try
{
cnnTrans.Open();
trans = cnnTrans.BeginTransaction();
}
catch (Exception e)
{
throw new Exception("create transaction error!", e);
}

return trans;
}

/// <summary>
/// 获得一个数据库命令对象。
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="conn">数据库连接</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns>命令对象</returns>
public override IDbCommand GetCommand(string sql, SqlExecType sqlexectype, IDbConnection conn, IDbTransaction trans, params IDataParameter[] paras)
{
try
{
cmd = new MySqlCommand();
cmd.CommandText = sql;
if (sqlexectype == SqlExecType.SqlText)
{
cmd.CommandType = CommandType.Text;
}
else if (sqlexectype == SqlExecType.SqlProcName)
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.Connection = (MySqlConnection)conn;

//这里可能需要兼容处理大数据字段，暂时先不处理，看看参数方式是否直接可以
this.PrepareCommand(cmd, paras);

if (trans != null)
{
cmd.Transaction = (MySqlTransaction)trans;
}
}
catch (Exception e)
{
throw new Exception("create command error, please check sql script:" + sql, e);
}

return (IDbCommand)cmd;
}

/// <summary>
/// 构造参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="cmdParms"></param>
private void PrepareCommand(MySqlCommand cmd, IDataParameter[] cmdParms)
{
if (cmdParms != null)
{
cmd.Parameters.Clear();
for (int i = 0; i < cmdParms.Length; i++)
{
//SqlParameter pp = (SqlParameter)((ICloneable)p).Clone();
//SqlParameter OraParameter = (SqlParameter)cmdParms[i];
//这里会发送重复添加参数问题，克隆处理下
MySqlParameter OraParameter = (MySqlParameter)((ICloneable)(MySqlParameter)cmdParms[i]).Clone();
cmd.Parameters.Add(OraParameter);
}
}
}

/// <summary>
/// 获取数据类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private SqlDbType GetDBType(DbType type)
{
SqlDbType dbType = SqlDbType.VarChar;

return dbType;
}

op = new MySqlParameter(this.ParamSign() + paraName.Replace(":", "").Replace("@", "").Replace("?", ""), dbType);
op.Value = paramValue;
op.DbType = type;
op.Direction = ParameterDirection.Input;

return (IDataParameter)op;
}

cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if (trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}

return tempint;
}

/// <summary>
/// 执行SQL，返回查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override Object ExecuteScalar(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
object tempobj = 0;

try
{
if (trans == null)
{
cnn = (MySqlConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if (trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}

return tempobj;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到Read中返回。
/// </summary>
/// <param name="sql">要执行的SQL查询。</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns>返回一个包含查询结果的Read。</returns>
public override IDataReader GetReader(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
try
{
cnn = (MySqlConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, null, paras);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
cnn.Close();
cnn.Dispose();
}

return reader;
}

adapter = new MySqlDataAdapter(cmd);

adapter.Fill(ds, "tableName");
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}

finally
{
adapter.Dispose();
cnn.Close();
cnn.Dispose();
}

return ds;
}

/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataTable GetDataTable(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = this.GetDataSet(sql, sqlexectype, paras);

if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}

/// <summary>
/// 参数前面的符号
/// </summary>
/// <returns></returns>
public override string ParamSign()
{
return "@";
}

/// <summary>
/// 获取数据库表结构
/// </summary>
/// <param name="dataEntName"></param>
/// <returns></returns>
public override DataTable GetDataEntity(string dataEntName)
{
string sql = @" select c.TABLE_NAME, t.column_name colname, t.column_type colType, t.column_COMMENT COMMENTS,
t.CHARACTER_MAXIMUM_LENGTH DATA_LENGTH , t.IS_NULLABLE NULLABLE
from information_schema.COLUMNS t
inner join information_schema.tables c
on t.table_schema = c.table_schema
and t.table_name = c.table_name
where {0} = upper({1}TABLE_NAME)
Order By t.ordinal_position";
sql = string.Format(sql, this.ToUpper("c.TABLE_NAME", false), this.ParamSign());
MySqlParameter op = null;
op = new MySqlParameter(this.ParamSign() + "TABLE_NAME", SqlDbType.VarChar);
op.Value = dataEntName;
op.DbType = DbType.String;
op.Direction = ParameterDirection.Input;

DataSet ds = this.GetDataSet(sql, SqlExecType.SqlText, op);

if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}

/// <summary>
/// 获取系统时间
/// </summary>
/// <returns></returns>
public override string GetSystemDateTime()
{
string sql = @"select now() ";
DataSet ds = this.GetDataSet(sql, SqlExecType.SqlText, null);

if (Common.Utility.DsHasData(ds))
{
return Convert.ToDateTime(ds.Tables[0].Rows[0][0].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
}

return "";
}

/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="opPagerData"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataTable GetDataPager(FrameCommon.SSY_PagingExecuteParam opPagerData, params IDataParameter[] paras)
{
if (opPagerData.PagingParam.TotalSize > 0)
{
//实现获取分页数据
StringBuilder sbbSql = new StringBuilder();
string sortType = string.Empty; //排序类型

if (opPagerData.PagingParam.SortType == FrameCommon.AscOrDesc.Asc)
{
sortType = " asc ";
}
else
{
sortType = " desc ";
}

if (string.IsNullOrEmpty(opPagerData.SqlWhere))
{
sbbSql.Append(string.Format(@" Select {0} FROM {1} {2} order by {3} {4} ",
opPagerData.Fields, opPagerData.TableNameOrView, opPagerData.Joins, opPagerData.OrderField, sortType));
}
else
{
sbbSql.Append(string.Format(@" Select {0} FROM {1} {2} where {5} order by {3} {4} ",
opPagerData.Fields, opPagerData.TableNameOrView, opPagerData.Joins, opPagerData.OrderField, sortType, opPagerData.SqlWhere));
}

//区间段
int startRecord = (opPagerData.PagingParam.PageIndex - 1) * opPagerData.PagingParam.PageSize + 1;
int endRecord = startRecord + opPagerData.PagingParam.PageSize - 1;

sbbSql.Append(string.Format(@" limit {0} , {1} ", startRecord.ToString(), opPagerData.PagingParam.PageSize.ToString()));

return this.GetDataTable(sbbSql.ToString(), SqlExecType.SqlText, paras);
}
else
{
return new DataTable();
}
}

#region 相关转换函数

string resStr = string.Empty;
if (tarType == DbType.String)
{
if (string.IsNullOrEmpty(format))
{
if (opflag)
{
resStr = string.Format(" CONCAT('{0}','') ", socExpress.ToString());
}
else
{
resStr = string.Format(" CONCAT({0},'') ", socExpress.ToString());
}
}
else
{
if (opflag)
{
resStr = string.Format(" date_format('{0}', '{1}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" date_format('{0}', '{1}') ", socExpress.ToString(), format);
}
}
}
else if (tarType == DbType.Int16 || tarType == DbType.Int32 || tarType == DbType.Int64)
{
if (string.IsNullOrEmpty(format))
{
if (opflag)
{
resStr = string.Format(" CAST('{0}' SIGNED) ", socExpress.ToString());
}
else
{
resStr = string.Format(" CAST({0} AS SIGNED) ", socExpress.ToString());
}
}
else
{
if (opflag)
{
resStr = string.Format(" unix_timestamp('{0}') ", socExpress.ToString());
}
else
{
resStr = string.Format(" unix_timestamp('{0}') ", socExpress.ToString());
}
}
}
else if (tarType == DbType.DateTime)
{
format = "any"; //sql无需指定格式，只要是日期格式即可
if (string.IsNullOrEmpty(format))
{
throw new Exception("转换时间时必须指定format格式！");
}
else
{
if (opflag)
{
resStr = string.Format(" str_to_date('{0}','{1}') ", socExpress.ToString(), format);
}
else
{
resStr = string.Format(" str_to_date('{0}','{1}') ", socExpress.ToString(), format);
}
}
}

//后续继续扩展

return resStr;
}

#endregion

#region 相关关键符号

/// <summary>
/// 拼接字符串
/// </summary>
/// <param name="opflags">拼接内容说明，要和内容一一对应（value 数值 field 字段）</param>
/// <param name="opfields">拼接内容, 可以是内容、字段</param>
/// <returns></returns>
public override string ConnectChar(List<ConvertFlag> opflags, List<string> opfields)
{
StringBuilder resStr = new StringBuilder();

for (int i = 0; i < opfields.Count; i++)
{
if(i == 0)
{
if(opflags[i] == ConvertFlag.Value)
{
resStr.Append(" concat( '" + opfields[i] + "' , ");
}
else if(opflags[i] == ConvertFlag.Field)
{
resStr.Append(" concat( " + opfields[i] + " , ");
}
}
else if(i > 0 && i < opfields.Count - 1)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "' , ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " , ");
}
}
else if(i == opfields.Count - 1)
{
if (opflags[i] == ConvertFlag.Value)
{
resStr.Append(" '" + opfields[i] + "') ");
}
else if (opflags[i] == ConvertFlag.Field)
{
resStr.Append(" " + opfields[i] + " ) ");
}
}
}

return resStr.ToString();
}

#endregion

#region 相关常规应用函数

/// <summary>
/// 转换大写
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string ToUpper(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" upper('{0}') ", soc);
}
else
{
return string.Format(" upper({0}) ", soc);
}
}

/// <summary>
/// 去掉空格
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Trim(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" replace('{0}', ' ', '') ", soc);
}
else
{
return string.Format(" replace({0}, ' ', '') ", soc);
}
}

/// <summary>
/// 从左边截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Left(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" left('{0}', 0, {1}) ", soc, n.ToString());
}
else
{
return string.Format(" left({0}, 0, {1}) ", soc, n.ToString());
}
}

/// <summary>
/// 从右边截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Right(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" right('{0}', {1}) ", soc, n.ToString());
}
else
{
return string.Format(" right({0}, {1}) ", soc, n.ToString());
}
}

/// <summary>
/// 截取字符串
/// </summary>
/// <param name="soc"></param>
/// <param name="startIndex"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Substring(string soc, int startIndex, int n, bool opflag)
{
if (opflag)
{
return string.Format(" substring('{0}', {1}, {2}) ", soc, startIndex.ToString(), n.ToString());
}
else
{
return string.Format(" substring({0}, {1}, {2}) ", soc, startIndex.ToString(), n.ToString());
}
}

/// <summary>
/// Length
/// </summary>
/// <param name="soc"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Length(string soc, bool opflag)
{
if (opflag)
{
return string.Format(" LENGTH('{0}') ", soc);
}
else
{
return string.Format(" LENGTH({0}) ", soc);
}
}

/// <summary>
/// 字符串连接
/// </summary>
/// <param name="strs"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string ConcatString(string[] strs, bool opflag)
{
string tempStr = string.Empty;
for (int i = 0; i < strs.Length; i++)
{
if (opflag)
{
tempStr += "," + "'" + strs[i] + "'";
}
else
{
tempStr += "," + strs[i];
}
}
if (!string.IsNullOrEmpty(tempStr))
{
tempStr = tempStr.Substring(1, tempStr.Length - 1);
}
return string.Format(" concat_ws('',{0}) ", tempStr);
}

/// <summary>
/// 左补空格
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Lpad(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" concat_ws('',space({1}),'{0}') ", soc, n.ToString());
}
else
{
return string.Format(" concat_ws('',space({1}) ,'{0}') ", soc, n.ToString());
}
}

/// <summary>
/// 右边补空格
/// </summary>
/// <param name="soc"></param>
/// <param name="n"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Rpad(string soc, int n, bool opflag)
{
if (opflag)
{
return string.Format(" concat_ws('','{0}' ,space({1})) ", soc, n.ToString());
}
else
{
return string.Format(" concat_ws('','{0}' ,space({1})) ", soc, n.ToString());
}
}

/// <summary>
/// 两个日期差
/// </summary>
/// <param name="datepart">仅支持yy MM dd HH mi ss</param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Datediff(string datepart, string startDate, string endDate, bool opflag)
{
string resStr = string.Empty;
string parts = "yy|MM|dd|HH|mi|ss";
if (parts.IndexOf(datepart) == -1)
{
throw new Exception("date format error!");
}

if (opflag)
{
resStr = string.Format(" datediff('{1}', '{0}') ",
startDate, endDate);
}
else
{
resStr = string.Format(" datediff('{1}', '{0}') ",
startDate, endDate);
}

return resStr;
}

if (opflag)
{
resStr = string.Format(" date_add({0}, interval {1} day) ", currDate, addValue);
}
else
{
resStr = string.Format(" date_add({0}, interval {1} day) ", currDate, addValue);
}

return resStr;
}

/// <summary>
/// 解析固定内容显示值
/// </summary>
/// <param name="soc">原始值</param>
/// <param name="anaV">固定内容值例如：value1|display1, ...</param>
/// <param name="defaultV"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Decode(string soc, string anaV, string defaultV, bool opflag)
{
//select case '2'
//when '1' then '2'
//when '2' then '2'
//when '3' then '2'
//else '4'
//end
StringBuilder resSb = new StringBuilder();
string[] anaVs = anaV.Split(',');
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", soc));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", soc));
}

for (int i = 0; i < anaVs.Length; i++)
{
resSb.AppendLine(string.Format(" when '{0}' then '{1}' ", anaVs[i].Split('|')[0], anaVs[i].Split('|')[1]));
}

resSb.AppendLine(string.Format(" else '{0}' ", defaultV));
resSb.AppendLine(" end ");

return resSb.ToString();
}

/// <summary>
/// v1 is null 取v2 否则取v1
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Nvl(string v1, string v2, bool opflag)
{
//NVL(null, '12')
StringBuilder resSb = new StringBuilder();
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", v1));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", v1));
}

resSb.AppendLine(string.Format(" when '{0}' then '{0}' ", v1));
resSb.AppendLine(string.Format(" else '{0}' ", v2));
resSb.AppendLine(" end ");

return resSb.ToString();

}

/// <summary>
/// v1 not null 取v2, v1 is null取v3
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="v3"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string Nvl2(string v1, string v2, string v3, bool opflag)
{
//nvl2('a', 'b', 'c')
StringBuilder resSb = new StringBuilder();
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", v1));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", v1));
}

resSb.AppendLine(string.Format(" when '{0}' then '{1}' ", v2));
resSb.AppendLine(string.Format(" else '{0}' ", v3));
resSb.AppendLine(" end ");

return resSb.ToString();
}

/// <summary>
/// v1 = v2取null 否则取v1
/// </summary>
/// <param name="v1"></param>
/// <param name="v2"></param>
/// <param name="opflag">true 数值 false 字段</param>
/// <returns></returns>
public override string NullIf(string v1, string v2, bool opflag)
{
// NULLIF('a','b')
StringBuilder resSb = new StringBuilder();
if (opflag)
{
resSb.AppendLine(string.Format(" case '{0}' ", v1));
}
else
{
resSb.AppendLine(string.Format(" case {0} ", v1));
}

resSb.AppendLine(string.Format(" when '{0}' then null ", v2));
resSb.AppendLine(string.Format(" else '{0}' ", v1));
resSb.AppendLine(" end ");

return resSb.ToString();
}

/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns></returns>
public override string GetDBDateTime()
{
return " now() ";
}

#endregion
}
}

获取实例源码请入QQ群706224870，在群文件中下载。

posted on 2018-06-01 10:47 猫头007 阅读(585) 评论(0) 编辑收藏举报

刷新页面返回顶部

猫头007

设计模式在实际业务应用中的介绍之1——抽象工厂模式实现对数据库访问组件的封装

导航

公告