public OracleCommand[] CreateDbCommandByXml <T>(string xmlFileName, string sqlName, T entity) where T : class
{
SqlString[] sqlString = SqlConfigHelper.Instance.GetSqlString(xmlFileName, sqlName);
if ((sqlString == null) || (sqlString.Length == 0))
{
throw new MB.Orm.Exceptions.XmlSqlConfigNotExistsException(xmlFileName, sqlName);
}
List <OracleCommand> list = new List <OracleCommand>();
foreach (SqlString str in sqlString)
{
IList <SqlParamInfo> paramFields = str.ParamFields;
OracleCommand sqlStringCommand = GetSqlStringCommand(str.SqlStr);
if ((paramFields != null) && (paramFields.Count > 0))
{
for (int i = 0; i < paramFields.Count; i++)
{
SqlParamInfo parInfo = paramFields[i];
var t = entity.GetType().GetProperty(parInfo.MappingName).GetValue(entity, null);
AddParamInfoToDbCommand(sqlStringCommand, parInfo, t);
}
}
list.Add(sqlStringCommand);
}
return(list.ToArray());
}
private void AddParamInfoToDbCommand(OracleCommand command, SqlParamInfo parInfo, object value)
{
OracleParameter parameter = new OracleParameter(parInfo.Name, FromDbType(parInfo.DbType));
parameter.Value = value;
command.Parameters.Add(parameter);
}
public OracleCommand[] CreateDbCommandByXml(string xmlFileName, string sqlName, params object[] parValues)
{
SqlString[] sqlString = SqlConfigHelper.Instance.GetSqlString(xmlFileName, sqlName);
if ((sqlString == null) || (sqlString.Length == 0))
{
throw new MB.Orm.Exceptions.XmlSqlConfigNotExistsException(xmlFileName, sqlName);
}
List <OracleCommand> list = new List <OracleCommand>();
foreach (SqlString str in sqlString)
{
IList <SqlParamInfo> paramFields = str.ParamFields;
if (((paramFields != null) && (paramFields.Count > 0)) && ((parValues == null) || (parValues.Length != paramFields.Count)))
{
throw new APPException("调用XML文件:" + xmlFileName + " 下的SQL" + sqlName + " 传入的参数和配置的参数不一致！");
}
OracleCommand sqlStringCommand = GetSqlStringCommand(str.SqlStr);
if ((paramFields != null) && (paramFields.Count > 0))
{
for (int i = 0; i < paramFields.Count; i++)
{
SqlParamInfo parInfo = paramFields[i];
AddParamInfoToDbCommand(sqlStringCommand, parInfo, parValues[i]);
}
}
list.Add(sqlStringCommand);
}
return(list.ToArray());
}
public SqlParamInfo AddDateTimePar(string parName, object value)
{
SqlParamInfo sqlpar = new SqlParamInfo(parName, DbType.DateTime, value);
_sqlPars.Add(sqlpar);
return(sqlpar);
}
public SqlParamInfo AddInt64Par(string parName, object value)
{
SqlParamInfo sqlpar = new SqlParamInfo(parName, DbType.UInt64, value);
_sqlPars.Add(sqlpar);
return(sqlpar);
}
public SqlParamInfo AddParameter(string parName, DbType dbType, object value)
{
SqlParamInfo sqlpar = new SqlParamInfo(parName, dbType, value);
_sqlPars.Add(sqlpar);
return(sqlpar);
}
/// <summary>
/// 获取分类下的项目
/// </summary>
/// <param name="examClassId"></param>
/// <returns></returns>
public DataTable GetExamItemByClass(string examClassId)
{
string sql = "select 项目ID, 项目分类ID, 项目名称, 项目信息, 绑费信息 from 影像项目信息 where 项目分类ID=:项目分类ID";
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("项目分类ID", DbType.String, examClassId) };
sql = SqlHelper.GetSqlBiz().GetSqlContext("根据分类查询影像项目", sql);
return(_dbHelper.ExecuteSQL(sql, sqlPars));
}
/// <summary>
/// 获取影像分类信息
/// </summary>
/// <param name="imgKind"></param>
/// <returns></returns>
public DataTable GetExamClass(string imgKind)
{
string sql = "select 项目分类ID, 上级分类ID, 影像类别, 分类名称, 分类信息 from 影像项目分类 where 影像类别=:影像类别";
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("影像类别", DbType.String, imgKind) };
sql = SqlHelper.GetSqlBiz().GetSqlContext("根据类别查询影像分类", sql);
return(_dbHelper.ExecuteSQL(sql, sqlPars));
}
/// <summary>
/// 根据项目名称查询项目ID
/// </summary>
/// <param name="itemName"></param>
/// <returns></returns>
public string GetExamItemIdByName(string itemName)
{
string sql = "select 项目ID from 影像项目信息 where 项目名称=:项目名称";
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("项目名称", DbType.String, itemName) };
sql = SqlHelper.GetSqlBiz().GetSqlContext("根据名称查询影像项目", sql);
object value = _dbHelper.ExecuteSQLOneOutput(sql, sqlPars);
return(value == null ? "" : value.ToString());
}
/// <summary>
/// 根据分类名称获取ID
/// </summary>
/// <param name="className"></param>
/// <param name="imageKind"></param>
/// <returns></returns>
public string GetExamClassIdByName(string className, string imageKind)
{
string sql = "select 项目分类ID from 影像项目分类 where 分类名称=:分类名称 and 影像类别=:影像类别";
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("分类名称", DbType.String, className),
new SqlParamInfo("影像类别", DbType.String, imageKind) };
sql = SqlHelper.GetSqlBiz().GetSqlContext("根据名称查询影像分类", sql);
object value = _dbHelper.ExecuteSQLOneOutput(sql, sqlPars);
return(value == null ? "" : value.ToString());
}
/// <summary>
/// 更新HIS服务配置
/// </summary>
/// <param name="hisServerData"></param>
public void UpdateHisServerCfg(HisServerCfgData hisServerData)
{
string sql = "Update HIS服务配置 " +
" Set 服务名称=:服务名称, 服务配置=:服务配置 where HIS服务ID=:HIS服务ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新HIS服务配置", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("服务名称", DbType.String, hisServerData.务名称),
new SqlParamInfo("服务配置", DbType.String, hisServerData.务配置.ToString()),
new SqlParamInfo("HIS服务ID", DbType.String, hisServerData.HIS服务ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
}
/// <summary>
/// 更新房间
/// </summary>
/// <param name="hisServerData"></param>
public void UpdateRoomInfo(DepRoomData roomInfo)
{
string sql = "Update 影像房间信息 " +
" Set 房间名称=:房间名称, 房间信息=:房间信息 where 房间ID=:房间ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新科室角色信息", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("房间名称", DbType.String, roomInfo.房间名称),
new SqlParamInfo("房间信息", DbType.String, roomInfo.房间信息.ToString()),
new SqlParamInfo("房间ID", DbType.String, roomInfo.房间ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
}
/// <summary>
/// 更新科室信息
/// </summary>
/// <param name="departmentInfo"></param>
public void UpdateDepartmentInfo(DepartmentInfoData departmentInfo)
{
string sql = "Update 影像科室信息 " +
" Set 科室名称=:科室名称, 附加数据=:附加数据 where 科室ID=:科室ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新影像科室信息", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("科室名称", DbType.String, departmentInfo.科室名称),
new SqlParamInfo("附加数据", DbType.String, departmentInfo.附加数据.ToString()),
new SqlParamInfo("科室ID", DbType.String, departmentInfo.科室ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
}
/// <summary>
/// 更新字典内容
/// </summary>
/// <returns></returns>
public void UpdateDictContent(DictManageData dictInfo)
{
string sql = "Update 影像字典信息 " +
" Set 字典信息=:字典信息 where 字典名称=:字典名称";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新科室角色信息", sql);
//SqlParamInfo sqlPars = new SqlParamInfo("角色信息", DbType.String, dictInfo.字典信息.ToString());
SqlParamInfo [] sqlPars = new SqlParamInfo[] { new SqlParamInfo("字典名称", DbType.String, dictInfo.字典信息.ToString()) };
//return _dbHelper.ExecuteSQL(sql, new SqlParamInfo[] { new SqlParamInfo("字典名称", DbType.String, sDicName) });
_dbHelper.ExecuteSQL(sql, sqlPars);
}
/// <summary>
/// 更新部位方法
/// </summary>
/// <param name="bodypartData"></param>
/// <returns></returns>
public bool UpdateBodypartWay(BodypartInfoData bodypartData)
{
string sql = "update 影像部位信息 set 部位信息=:部位信息 where 部位ID=:部位ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新检查部位方法", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("部位信息", DbType.String, bodypartData.部位信息.ToString()),
new SqlParamInfo("部位ID", DbType.String, bodypartData.部位ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
return(true);
}
/// <summary>
/// 更新角色
/// </summary>
/// <param name="hisServerData"></param>
public void UpdateRoleInfo(RoleInfoData roleInfo)
{
string sql = "Update 影像角色信息 " +
" Set 角色名称=:角色名称, 分组标记=:分组标记, 角色信息=:角色信息 where 角色ID=:角色ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新科室角色信息", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("角色名称", DbType.String, roleInfo.角色名称),
new SqlParamInfo("分组标记", DbType.String, roleInfo.分组标记),
new SqlParamInfo("角色信息", DbType.String, roleInfo.角色信息.ToString()),
new SqlParamInfo("角色ID", DbType.String, roleInfo.角色ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
}
/// <summary>
/// 更新窗体
/// </summary>
/// <param name="hisServerData"></param>
public void UpdateWindowInfo(WindowInfoData roleWindowInfo)
{
SQL sql = SqlHelper.CreateSQL("更新窗体信息", "Update 影像窗体信息 " +
" Set 窗体名称=:窗体名称, 分组标记=:分组标记, 版本=:版本, 窗体信息=:窗体信息 where 窗体ID=:窗体ID");
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("窗体名称", DbType.String, roleWindowInfo.窗体名称),
new SqlParamInfo("分组标记", DbType.String, roleWindowInfo.分组标记),
new SqlParamInfo("版本", DbType.Int32, roleWindowInfo.版本),
new SqlParamInfo("窗体信息", DbType.String, roleWindowInfo.窗体信息.ToString()),
new SqlParamInfo("窗体ID", DbType.String, roleWindowInfo.窗体ID) };
sql.AddParameterRange(sqlPars);
_dbHelper.ExecuteSQL(sql);
}
/// <summary>
/// 更新检查项目
/// </summary>
/// <param name="examItem"></param>
/// <returns></returns>
public bool UpdateExamItem(ExamItemData examItem)
{
string sql = "update 影像项目信息 set 项目名称=:项目名称, 项目分类ID=:项目分类ID, 项目信息=:项目信息 where 项目ID=:项目ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新检查项目信息", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("项目名称", DbType.String, examItem.项目名称),
new SqlParamInfo("项目分类ID", DbType.String, examItem.项目分类ID),
new SqlParamInfo("项目信息", DbType.String, examItem.项目信息.ToString()),
new SqlParamInfo("项目ID", DbType.String, examItem.项目ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
return(true);
}
/// <summary>
/// 更新用户
/// </summary>
/// <param name="hisServerData"></param>
public void UpdateUserInfo(UserInfoData userInfo, UserReleationData userReleation)
{
//添加事务处理
_dbHelper.TransactionBegin();
try
{
string sql = "Update 影像用户信息 " +
" Set 系统账号=:系统账号, 用户名称=:用户名称, 职称级别=:职称级别, 账号信息=:账号信息,人员信息=:人员信息" + //,变更日志=:变更日志 " +
" where 用户ID=:用户ID ";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新科室用户信息", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("系统账号", DbType.String, userInfo.系统账号),
new SqlParamInfo("用户名称", DbType.String, userInfo.用户名称),
new SqlParamInfo("职称级别", DbType.Int32, userInfo.职称级别),
new SqlParamInfo("账号信息", DbType.String, userInfo.账号信息.ToString()),
new SqlParamInfo("人员信息", DbType.String, userInfo.人员信息.ToString()),
//new SqlParamInfo("变更日志", DbType.String, JsonHelper.SerializeObject(userInfo.变更日志)),
new SqlParamInfo("用户ID", DbType.String, userInfo.用户ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
//更新角色关联
if (userReleation != null)
{
sql = "update 影像用户关联 " +
" set 角色ID=:角色ID " +
" where 用户ID=:用户ID and 科室ID=:科室ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("更新科室用户关联", sql);
sqlPars = new SqlParamInfo[] { new SqlParamInfo("角色ID", DbType.String, userReleation.角色ID),
new SqlParamInfo("用户ID", DbType.String, userInfo.用户ID),
new SqlParamInfo("科室ID", DbType.String, userReleation.科室ID) };
_dbHelper.ExecuteSQL(sql, sqlPars);
}
//更新照片
UpdatePhoto(userInfo);
_dbHelper.TransactionCommit();
}
catch (Exception ex)
{
_dbHelper.TransactionRollback();
throw new Exception("保存账户信息失败", ex);
}
}
/// <summary>
/// 根据参数和XML文件获取数据库执行的DbCommand.
/// </summary>
/// <param name="db">当前连接库</param>
/// <param name="xmlFileName">Mapping对应的XML 文件名称</param>
/// <param name="sqlName">SQL 语句配置对应的名称</param>
/// <param name="sqlParams">参数以及值</param>
/// <returns>Command 数组,如果只配置一个SqlString 那么就只返回一个DbCommand</returns>
public System.Data.Common.DbCommand[] CreateDbCommandBySqlParams(Database db, string xmlFileName, string sqlName, List <SqlParamInfo> sqlParams)
{
var dbType = MB.Orm.Persistence.DatabaseHelper.GetDatabaseType(db);
MB.Orm.DbSql.SqlString[] sqlStrsArray = MB.Orm.Mapping.Xml.SqlConfigHelper.Instance.GetSqlString(xmlFileName, sqlName);
if (sqlStrsArray == null || sqlStrsArray.Length == 0)
{
throw new MB.Orm.Exceptions.XmlSqlConfigNotExistsException(xmlFileName, sqlName);
}
List <System.Data.Common.DbCommand> dbCmds = new List <System.Data.Common.DbCommand>();
Dictionary <string, SqlParamInfo> parsHas = new Dictionary <string, SqlParamInfo>();
foreach (SqlParamInfo info in sqlParams)
{
if (parsHas.ContainsKey(info.Name))
{
continue;
}
parsHas.Add(info.Name, info);
}
foreach (MB.Orm.DbSql.SqlString sqlStr in sqlStrsArray)
{
sqlStr.SqlStr = formatSqlString(sqlStr.SqlStr);
IList <SqlParamInfo> tPars = sqlStr.ParamFields;
System.Data.Common.DbCommand dbCmd = db.GetSqlStringCommand(sqlStr.SqlStr);
if (tPars != null && tPars.Count > 0)
{
for (int i = 0; i < tPars.Count; i++)
{
SqlParamInfo parInfo = tPars[i];
if (!parsHas.ContainsKey(parInfo.Name))
{
throw new MB.Util.APPException(string.Format("调用XML文件:{0} 下的SQL {1} 配置的参数 {2} 在传入的参数中不存在！", xmlFileName, sqlName, parInfo.Name), MB.Util.APPMessageType.SysErrInfo);
}
SqlParamInfo valPar = parsHas[parInfo.Name];
AddParamInfoToDbCommand(db, dbCmd, parInfo, valPar.Value);
}
}
dbCmd.CommandText = ReplaceSqlParamsByDatabaseType(db, dbCmd.CommandText);
dbCmds.Add(dbCmd);
}
return(dbCmds.ToArray());
}
/// <summary>
/// 新增部位信息
/// </summary>
/// <param name="bodypartData"></param>
/// <returns></returns>
public bool NewBodypartInfo(BodypartInfoData bodypartData)
{
string sql = "insert into 影像部位信息(部位ID, 影像类别, 部位名称, 分组标记, 部位信息)" +
"values(:部位ID, :影像类别, :部位名称, :分组标记, :部位信息)";
sql = SqlHelper.GetSqlBiz().GetSqlContext("插入检查部位信息", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("部位ID", DbType.String, bodypartData.部位ID),
new SqlParamInfo("影像类别", DbType.String, bodypartData.影像类别),
new SqlParamInfo("部位名称", DbType.String, bodypartData.部位名称),
new SqlParamInfo("分组标记", DbType.String, bodypartData.分组标记),
new SqlParamInfo("部位信息", DbType.String, bodypartData.部位信息.ToString()) };
_dbHelper.ExecuteSQL(sql, sqlPars);
return(true);
}
/// <summary>
/// 获取角色ID
/// </summary>
/// <param name="roleName">角色名称</param>
/// <param name="roomId">科室ID</param>
/// <returns></returns>
public string GetRoleID(string roleName, string roomId)
{
//TODO:需要结合科室ID进行判断
string sql = "Select 角色ID From 影像角色信息 where 角色名称=:角色名称 and 科室ID=:科室ID";
sql = SqlHelper.GetSqlBiz().GetSqlContext("获取科室角色ID", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("角色名称", DbType.String, roleName),
new SqlParamInfo("科室ID", DbType.String, roomId) };
_dbHelper.ExecuteSQL(sql, sqlPars);
object result = _dbHelper.ExecuteSQLOneOutput(sql, sqlPars);
//object result = _dbHelper.ExecuteSQLOneOutput(sql, new SqlParamInfo[] { new SqlParamInfo("角色名称", DbType.String, roleName) });
return(result == null ? "" : result.ToString());
}
/// <summary>
/// 新增项目分类
/// </summary>
/// <param name="examClass"></param>
/// <returns></returns>
public bool NewExamClass(ExamClassData examClass)
{
string sql = "insert into 影像项目分类(项目分类ID, 上级分类ID, 影像类别, 分类名称, 分类信息)" +
"values(:项目分类ID, :上级分类ID, :影像类别, :分类名称, :分类信息)";
sql = SqlHelper.GetSqlBiz().GetSqlContext("插入检查项目分类", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("项目分类ID", DbType.String, examClass.项目分类ID),
new SqlParamInfo("上级分类ID", DbType.String, examClass.级分类ID),
new SqlParamInfo("影像类别", DbType.String, examClass.影像类别),
new SqlParamInfo("分类名称", DbType.String, examClass.分类名称),
new SqlParamInfo("分类信息", DbType.String, examClass.分类信息.ToString()) };
_dbHelper.ExecuteSQL(sql, sqlPars);
return(true);
}
/// <summary>
/// 新增检查项目
/// </summary>
/// <param name="examItem"></param>
/// <returns></returns>
public bool NewExamItem(ExamItemData examItem)
{
//暂时没有绑费信息，绑费信息固定插入空
string sql = "insert into 影像项目信息(项目ID, 项目分类ID, 项目名称, 项目信息, 绑费信息)" +
"values(:项目ID, :项目分类ID, :项目名称, :项目信息, null)";
sql = SqlHelper.GetSqlBiz().GetSqlContext("插入检查项目信息", sql);
SqlParamInfo[] sqlPars = new SqlParamInfo[] { new SqlParamInfo("项目ID", DbType.String, examItem.项目ID),
new SqlParamInfo("项目分类ID", DbType.String, examItem.项目分类ID),
new SqlParamInfo("项目名称", DbType.String, examItem.项目名称),
new SqlParamInfo("项目信息", DbType.String, examItem.项目信息.ToString()) };
//new SqlParamInfo("绑费信息", DbType.String, examItem.绑费信息.ToString())};
_dbHelper.ExecuteSQL(sql, sqlPars);
return(true);
}
//创建DBCommand 对象.
public System.Data.Common.DbCommand createDbCommandByXml(Database db, string xmlFileName, string sqlName, IList lstData)
{
SimulatedOracleHelper oh = new SimulatedOracleHelper();
MB.Orm.Persistence.PersistenceManagerHelper pm = new PersistenceManagerHelper();
var dbType = MB.Orm.Persistence.DatabaseHelper.GetDatabaseType(db);
List <System.Data.Common.DbCommand> dbCmds = new List <System.Data.Common.DbCommand>();
MB.Orm.DbSql.SqlString sqlStr = this.GetXmlSqlString(db, xmlFileName, sqlName);
IList <SqlParamInfo> tPars = sqlStr.ParamFields;
if (_PersistenceManagerHelper == null)
{
_PersistenceManagerHelper = new PersistenceManagerHelper();
}
var dbCmd = oh.CreateOracleCommand(_PersistenceManagerHelper.ReplaceSqlParamsByDatabaseType(db, sqlStr.SqlStr));// new OracleCommand(_PersistenceManagerHelper.ReplaceSqlParamsByDatabaseType(db, sqlStr.SqlStr));
//dbCmd.ArrayBindCount = lstData.Count;
List <SqlParamInfo> overcastPars = new List <SqlParamInfo>();
oh.SetCommandArrayBindCount(dbCmd, lstData.Count);
Type oracleDbType = oh.GetOracleDbType();
Dictionary <string, ArrayList> pValues = paramValues(db, tPars, lstData);
for (int i = 0; i < tPars.Count; i++)
{
SqlParamInfo parInfo = tPars[i];
if (parInfo.Overcast)
{
dbCmd.CommandText = pm.ReaplaceSpecString(dbCmd.CommandText, MB.Orm.DbSql.SqlShareHelper.ORACLE_PARAM_PREFIX + pm.CreateParName(db, parInfo.Name), pValues[parInfo.Name][0] == null ? null : pValues[parInfo.Name][0].ToString());
}
else
{
var oraInfo = oh.CreateOracleParameter(parInfo.Name, convertToOracleType(oh, oracleDbType, parInfo.DbType));// new OracleParameter(parInfo.Name, convertToOracleType(parInfo.DbType));
oraInfo.Direction = System.Data.ParameterDirection.Input;
oraInfo.Value = pValues[parInfo.Name].ToArray();
dbCmd.Parameters.Add(oraInfo);
}
}
return(dbCmd);
}
/// <summary>
/// 根据参数和XML文件获取数据库执行的DbCommand.
/// 根据该方法获取SqlString 都具有相同的参数。
/// <param name="db">当前连接库</param>
/// <param name="xmlFileName">Mapping对应的XML 文件名称</param>
/// <param name="sqlName">SQL 语句配置对应的名称</param>
/// <param name="parValues">参数的值,根据配置的顺序来匹配</param>
/// <returns>Command 数组,如果只配置一个SqlString 那么就只返回一个DbCommand</returns>
public System.Data.Common.DbCommand[] CreateDbCommandByXml(Database db, string xmlFileName, string sqlName, params object[] parValues)
{
var dbType = MB.Orm.Persistence.DatabaseHelper.GetDatabaseType(db);
MB.Orm.DbSql.SqlString[] sqlStrsArray = MB.Orm.Mapping.Xml.SqlConfigHelper.Instance.GetSqlString(xmlFileName, sqlName);
if (sqlStrsArray == null || sqlStrsArray.Length == 0)
{
throw new MB.Orm.Exceptions.XmlSqlConfigNotExistsException(xmlFileName, sqlName);
}
List <System.Data.Common.DbCommand> dbCmds = new List <System.Data.Common.DbCommand>();
foreach (MB.Orm.DbSql.SqlString sqlStr in sqlStrsArray)
{
sqlStr.SqlStr = formatSqlString(sqlStr.SqlStr);
IList <SqlParamInfo> tPars = sqlStr.ParamFields;
if (tPars != null && tPars.Count > 0)
{
if (parValues == null || parValues.Length != tPars.Count)
{
throw new MB.Util.APPException("调用XML文件:" + xmlFileName + " 下的SQL" + sqlName + " 传入的参数和配置的参数不一致！");
}
}
System.Data.Common.DbCommand dbCmd = db.GetSqlStringCommand(sqlStr.SqlStr);
if (tPars != null && tPars.Count > 0)
{
for (int i = 0; i < tPars.Count; i++)
{
SqlParamInfo parInfo = tPars[i];
AddParamInfoToDbCommand(db, dbCmd, parInfo, parValues[i]);
}
}
dbCmd.CommandText = ReplaceSqlParamsByDatabaseType(db, dbCmd.CommandText);
dbCmds.Add(dbCmd);
}
return(dbCmds.ToArray());
}
public void TestSqlWithPara()
{
try
{
Database db = MB.Orm.Persistence.DatabaseHelper.CreateDatabase();
//string sqlFilter = "((SF_DGN.DOC_DATE BETWEEN to_Date('2012-11-01','YYYY-MM-DD') AND (to_Date('2012-11-30','YYYY-MM-DD')+ 0.99999)))";
//string sql = "SELECT SF_DGN.BF_ORG_UNIT_ID,SUM(SF_DGN.TTL_QTY) AS TTL_QTY FROM SF_DGN LEFT JOIN SF_DGN_DTL ON SF_DGN.ID = SF_DGN_DTL.ID WHERE :CONDITION GROUP BY SF_DGN.BF_ORG_UNIT_ID";
string sqlFilter = "2012-11-01";
string sql = "SELECT SF_DGN.BF_ORG_UNIT_ID,SUM(SF_DGN.TTL_QTY) AS TTL_QTY FROM SF_DGN LEFT JOIN SF_DGN_DTL ON SF_DGN.ID = SF_DGN_DTL.ID WHERE ((SF_DGN.DOC_DATE BETWEEN to_Date(:CONDITION,'YYYY-MM-DD') AND (to_Date('2012-11-30','YYYY-MM-DD')+ 0.99999))) GROUP BY SF_DGN.BF_ORG_UNIT_ID";
SqlParamInfo[] paramArray = new SqlParamInfo[1];
paramArray[0] = new SqlParamInfo("CONDITION", sqlFilter, System.Data.DbType.String);
System.Data.Common.DbCommand dbcmd = db.GetSqlStringCommand(sql);
db.AddInParameter(dbcmd, "CONDITION", DbType.String, sqlFilter);
DataSet ds = new DatabaseExecuteHelper(null).ExecuteDataSet(db, dbcmd);
}
catch (Exception ex)
{
string msg = ex.ToString();
}
}
/// <summary>
/// 创建Command 执行的参数
/// </summary>
/// <param name="db">配置的数据库</param>
/// <param name="dbCmd">dbCommand</param>
/// <param name="paramInfo">sql参数</param>
/// <param name="parValue">参数值</param>
public void AddParamInfoToDbCommand(Database db, System.Data.Common.DbCommand dbCmd, SqlParamInfo parInfo, object parValue)
{
if (parInfo.Overcast)
{
dbCmd.CommandText = ReaplaceSpecString(dbCmd.CommandText, parInfo.Name, parValue.ToString());
return;
}
//判断是否为特殊的字段。
//string tmp = Array.Find<string>(SQL_SPEC_STRING, o => string.Compare(o, parInfo.Name, true) == 0);
//if (tmp != null && tmp.Length > 0) {
// dbCmd.CommandText = reaplaceSpecString(dbCmd.CommandText, parInfo.Name, parValue.ToString());
// return;
//}
if (parInfo.Direction == ParameterDirection.Output)
{
db.AddOutParameter(dbCmd, CreateParName(db, parInfo.Name), parInfo.DbType, parInfo.Length);
}
else
{
DbType dtype = ConvertToRealDbType(db, parInfo.DbType);
db.AddInParameter(dbCmd, CreateParName(db, parInfo.Name), dtype, ConvertToRealDbValue(db, parValue, parInfo.DbType));
}
}
/// <summary>
/// 创建Command 执行的参数
/// </summary>
/// <param name="db">配置的数据库</param>
/// <param name="dbCmd">dbCommand</param>
/// <param name="paramInfo">sql参数</param>
public void AddParamInfoToDbCommand(Database db, System.Data.Common.DbCommand dbCmd, SqlParamInfo parInfo)
{
AddParamInfoToDbCommand(db, dbCmd, parInfo, parInfo.Value);
}
//获取SQL 节点的所有参数信息。
private List <SqlParamInfo> getSqlParams(System.Xml.XmlNode sqlNode)
{
List <SqlParamInfo> pars = new List <SqlParamInfo>();
if (sqlNode == null || sqlNode.ChildNodes.Count == 0)
{
return(pars);
}
foreach (System.Xml.XmlNode node in sqlNode.ChildNodes)
{
if (node.NodeType != System.Xml.XmlNodeType.Element)
{
continue;
}
if (string.Compare(node.Name, "Param", true) != 0)
{
continue;
}
SqlParamInfo sqlParam = new SqlParamInfo();
string parName = getNodeAttValue(node, "Name");
parName = MB.Orm.DbSql.SqlShareHelper.SQL_XML_CFG_PARAM_PREFIX + parName.Substring(1, parName.Length - 1);
sqlParam.Name = parName;
string mapppingColumn = getNodeAttValue(node, "Column");
if (!string.IsNullOrEmpty(mapppingColumn))
{
sqlParam.MappingName = mapppingColumn;
}
string description = getNodeAttValue(node, "Description");
if (string.IsNullOrEmpty(description))
{
sqlParam.Description = sqlParam.Name;
}
else
{
sqlParam.Description = description;
}
string typeName = getNodeAttValue(node, "TypeName");
if (string.IsNullOrEmpty(typeName))
{
sqlParam.DbType = MB.Orm.Common.DbShare.Instance.SystemTypeNameToDbType("System.String");
}
else
{
sqlParam.DbType = MB.Orm.Common.DbShare.Instance.SystemTypeNameToDbType(typeName);
}
string length = getNodeAttValue(node, "Length");
if (string.IsNullOrEmpty(length))
{
sqlParam.Length = DEFAULT_PARAM_LENGTH;
}
else
{
sqlParam.Length = MB.Util.MyConvert.Instance.ToInt(length);
}
string overcast = getNodeAttValue(node, "Overcast");
if (!string.IsNullOrEmpty(overcast))
{
sqlParam.Overcast = MB.Util.MyConvert.Instance.ToBool(overcast);
}
string direction = getNodeAttValue(node, "Direction");
if (string.IsNullOrEmpty(direction))
{
sqlParam.Direction = System.Data.ParameterDirection.Input;
}
else
{
sqlParam.Direction = (System.Data.ParameterDirection)Enum.Parse(typeof(System.Data.ParameterDirection), direction);
}
//如果是特殊字段的话,那么现阶段只能采取覆盖的方式来进行处理
if (MB.Orm.DbSql.SqlShareHelper.HS_SQL_SPEC_STRING.ContainsKey(parName.ToUpper()))
{
sqlParam.Overcast = true;
}
pars.Add(sqlParam);
}
return(pars);
}
