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); }