public bool SaveModelListToDB(User.UserModel user, List <TBase_Model> modelList, ref string strError) { try { bool succ = false; List <OracleParameter[]> ParameterList = new List <OracleParameter[]>(); //把creater、createTime、modifyer等信息填到model中去. for (int i = 0; i < modelList.Count; i++) { TBase_Model model = modelList[i]; AddModelOperatorInfo(user, ref model); OracleParameter[] param = GetSaveModelOracleParameter(model); ParameterList.Add(param); } succ = OracleDBHelper.RunProcedures(this.GetSaveProcedureName(), ParameterList); return(succ); } catch (Exception ex) { throw new Exception(ex.Message); } }
public List <TB_PREMISEModel> GetJYDW(string keyword) { try { List <TB_PREMISEModel> datas = new List <TB_PREMISEModel>(); string where = ""; if (!string.IsNullOrEmpty(keyword)) { where = $" AND (FCODE LIKE '%{keyword}%' OR FNAME LIKE '%{keyword}%' OR FID='{keyword}')"; } else { where = ""; } var helper = new OracleDBHelper(); datas = helper.GetWithWhereStrByPage <TB_PREMISEModel>(where); return(datas); } catch (Exception ex) { LogHelper.WriteLog(ex); throw ex; } }
public virtual TBase_Model GetModelByID(int ID) { try { string sql = "SELECT * FROM " + this.GetViewName() + " WHERE ID = :ID and IsDel != 2"; OracleParameter[] param = new OracleParameter[] { new OracleParameter(":ID", ID) }; using (OracleDataReader reader = OracleDBHelper.ExecuteReader(CommandType.Text, sql, param)) { if (reader.Read()) { return(ToModel(reader)); } else { throw new Exception("在视图" + this.GetViewName() + "获取数据失败!"); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
public SyncSchedule() { string conStr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; this.Helper = new OracleDBHelper(conStr); Mapper.Initialize(new MapperConfigurationExpression()); }
public bool SaveModelBySqlToDB(User.UserModel user, ref TBase_Model model, ref string strError) { try { List <string> lstSql = new List <string>(); int i = OracleDBHelper.ExecuteNonQueryList(this.GetSaveSql(user, ref model), ref strError); if (i > 0) { model = GetModelByID(model.ID); if (model == null) { strError = "GetModelByID(modelID)出错,没有找到相符的记录,可能是视图有错误!"; return(false); } else { return(true); } } else { return(false); } } catch (Exception ex) { strError = ex.Message; return(false); //throw new Exception(ex.Message); } }
static void TestDelete() { OracleDBHelper helper = new OracleDBHelper(""); AuthorizationToken token = new AuthorizationToken(); helper.Delete <AuthorizationToken>(""); }
//public static bool CheckImportTable(int iType, UserInfo user, ref string strError) //{ // switch (iType) // { // case 1: // Basic.Area.Area_DB areadb = new Basic.Area.Area_DB(); // return areadb.CheckImportTable(ref strError); // case 2: // Stock.Stock_DB stockdb = new Stock.Stock_DB(); // return stockdb.CheckImportTable(ref strError); // default: // strError = "找不到对应的导入类型"; // return false; // } //} public static bool UpLoadSql(List <string> lstSql, User.UserModel user, ref string strError) { if (lstSql == null || lstSql.Count <= 0) { strError = "上传数据不能为空"; return(false); } if (OracleDBHelper.ExecuteNonQueryList(lstSql, ref strError) >= 1) { return(true); } else { if (string.IsNullOrEmpty(strError)) { try { OracleDBHelper.ExecuteNonQuery2(CommandType.Text, lstSql[0], null); if (lstSql.Count >= 2) { OracleDBHelper.ExecuteNonQuery2(CommandType.Text, lstSql[1], null); } } catch (Exception ex) { strError = ex.Message; } } return(false); } }
public bool UpdateModelListBySqlToDB(User.UserModel user, ref List <TBase_Model> modelList, ref string strError) { try { List <string> lstSql = new List <string>(); lstSql = this.GetUpdateModelListSql(user, modelList); if (lstSql == null || lstSql.Count == 0) { return(true); } int i = OracleDBHelper.ExecuteNonQueryList(lstSql, ref strError); if (i > 0) { return(true); } else { return(false); } } catch (Exception ex) { strError = ex.Message; return(false); //throw new Exception(ex.Message); } }
/// <summary> /// 通过查询条件得到部分或所有记录 /// </summary> /// <returns></returns> public virtual List <TBase_Model> GetModelListByFilter(string OrderBy, string Filter, string Fields) { try { string sql = "SELECT " + Fields + " FROM " + GetViewName(); if (Filter != "") { sql += " where " + Filter; } if (OrderBy != "") { sql += " " + OrderBy; } using (OracleDataReader reader = OracleDBHelper.ExecuteReader(sql)) { return(ToModels(reader)); } } catch (Exception ex) { throw new Exception(ex.Message); } }
public virtual bool DeleteModelByModel(User.UserModel user, TBase_Model model, ref string strError) { try { int bResult = 0; int iOut = 0; OracleParameter[] param = new OracleParameter[] { new OracleParameter("@bResult", bResult), new OracleParameter("@ErrorMsg", OracleDbType.NVarchar2, 100), new OracleParameter("@ID", OracleDBHelper.ToDBValue(model.ID)), new OracleParameter("@Deleter", OracleDBHelper.ToDBValue(user.UserNo)) }; param[0].Direction = System.Data.ParameterDirection.Output; param[1].Direction = System.Data.ParameterDirection.Output; int i = OracleDBHelper.RunProcedure(this.GetDeleteProcedureName(), param, out iOut); strError = param[1].Value.ToString(); if (i == -1)//(int)param[0].Value == -1 { return(false); } else { return(true); } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 获取新的树编号 /// </summary> /// <returns></returns> public string GetNewTreeNo() { string strNewNo = ""; try { string strSql = "select REPLICATE('0',10-LEN(ISNULL(MAX(ID),0)+1))+CONVERT(CHAR,ISNULL(MAX(ID),0)+1) as no from " + GetTableName() + ""; using (OracleDataReader reader = OracleDBHelper.ExecuteReader(strSql)) { if (reader.Read()) { strNewNo = reader["no"].ToString(); if (strNewNo == "") { strNewNo = "0000000001"; } return(strNewNo); } else { throw new Exception("取树编号出错!" + strNewNo); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
public bool TransferModelByProcedure(User.UserModel user, ref TBase_Model model, string ProcedureName, OracleParameter[] param, ref string strError) { try { bool succ = false; //int iRows = 0; int iOut = 0; //把creater、createTime、modifyer等信息填到model中去. AddModelOperatorInfo(user, ref model); int i = OracleDBHelper.RunProcedure(ProcedureName, param, out iOut); if ((int)param[0].Value == -1) { strError = param[1].Value.ToString(); } else { succ = true; int modelID = (int)param[2].Value; //model.ID = (int)param[2].Value; model = GetModelByID(modelID); if (model == null) { throw new Exception("GetModelByID(modelID)出错,没有找到相符的记录,可能是视图有错误!"); } } return(succ); } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 返回下拉列表 /// </summary> /// <param name="vSql"></param> /// <returns></returns> public List <LH_Policy> Select_List() { var helper = new OracleDBHelper(); var list = helper.Select <LH_Policy>(@"SELECT DISTINCT HEADID,POLICYNAME,ORDERTYPE,ORDERSUBTYPE,PRODCHANNEL,DEPTNAME from LH_POLICY"); return(list); }
public override void Initialization() { var strWhere = ""; if (ID == 55) //Fx market { strWhere = "WHERE num<=5 AND BusinessCodeType='FX' "; } else if (ID == 61) // Fi market { strWhere += "WHERE num<=5 AND BusinessCodeType IN('FI','MM') "; } else if (ID == 22003) { strWhere = "WHERE num<=5 AND BusinessCodeType IN('Agriculture','Energy','Commodities','Metal','Others') "; } var oracleDBHelper = new OracleDBHelper(ConfigurationManager.AppSettings["FileDBConStr"]); var sql = "select * FROM GETReseachReportByType " + strWhere + "ORDER BY InstitutionOrder,Code,TypeOrder,fileTypeCTIME,ReportDate DESC,num"; var dt = oracleDBHelper.GetDataTableBySql(sql); //var cmd = new OracleCommand //{ // Connection = (OracleConnection)(db.Database.Connection), // CommandText = "select * FROM GETReseachReportByType " + strWhere + // "ORDER BY InstitutionOrder,Code,TypeOrder,fileTypeCTIME,ReportDate DESC,num" //}; //var da = new OracleDataAdapter(cmd); //da.Fill(dt); InstitList = (from DataRow dr in dt.Rows select new InstitutionViewModel { Code = dr["Code"].ToString(), InstitutionNameCn = dr["InstitutionNameCn"].ToString(), InstitutionNameEn = dr["InstitutionNameEn"].ToString(), Mtime = Convert.ToDateTime(dr["MTIME"]), InstitutionExtension = dr["InstitutionExtension"].ToString(), FileTypeNameCn = dr["FileTypeNameCn"].ToString(), FileTypeNameEn = dr["FileTypeNameEn"].ToString(), FileNameCn = dr["FileNameCn"].ToString(), FileNameEn = dr["FileNameEn"].ToString(), FileId = Convert.ToInt32(dr["FileId"]), Extension = dr["Extension"].ToString(), ReportDate = Convert.ToDateTime(dr["ReportDate"]), }).ToList(); INSTITUTIONINFO = (from m in InstitList.Select(re => new { re.InstitutionNameCn, re.InstitutionNameEn, re.Mtime, re.Code, Extension = re.InstitutionExtension }).Distinct() select new INSTITUTIONINFO { INSTITUTIONNAMECN = m.InstitutionNameCn, INSTITUTIONNAMEEN = m.InstitutionNameEn, MTIME = m.Mtime, CODE = m.Code, EXTENSION = m.Extension }).Distinct().ToList(); foreach (var inst in INSTITUTIONINFO) { inst.LogoPath = GetLogoImagePath(inst); } }
public int GetExecuteNonQuery(string strSql) { try { return(OracleDBHelper.ExecuteNonQuery2(CommandType.Text, strSql).ToInt32()); } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 执行SQL返回第一条记录的第一列 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public object GetScalarBySql(string strSql) { try { return(OracleDBHelper.ExecuteScalar(CommandType.Text, strSql)); } catch (Exception ex) { throw new Exception(ex.Message); } }
public OracleDataReader GetRowBySql(string sql) { OracleDataReader reader = null; try { reader = OracleDBHelper.ExecuteReader(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return(reader); }
public AbsJob() { string conStr = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString; Helper = new OracleDBHelper(conStr); Interface = new ArrowInterface(); this.JobName = this.GetType().Name; //接口间隔定义值,于配置文件中定义 this.Interval = Convert.ToInt32(ConfigurationManager.AppSettings.Get(JobName)); DateTimeFormat = ConfigurationManager.AppSettings["DateTimeFormat"]; }
/// <summary> /// 通过SQL得到modelList /// </summary> /// <param name="strFilter"></param> /// <returns></returns> public List <TBase_Model> GetModelListBySql(string strSql) { try { using (OracleDataReader reader = OracleDBHelper.ExecuteReader(strSql)) { return(ToModels(reader)); } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 查询部门数据 /// </summary> /// <returns></returns> public DataSet QueryDepartData() { try { string sql = @"Select DEPTID,DESCR100_2,PARENT_NODE_NAME,DEPT_DESCR,IS_REAL,REAL_LVL,EFF_STATUS,MANAGER_ID,EFFDT From SYSADM.PS_C_DEPTDATA_VW ORDER BY REAL_LVL"; DataSet ds = OracleDBHelper.GetDataSet(sql); return(ds); } catch (Exception ex) { Console.WriteLine(ex.ToString()); return(null); } }
public PageResult <v_lhproducts_policyModel> GetPolicyProducts(ICPOBILL_PolicyDTO header, v_lhproducts_policyModel where, int index = 1, int size = 35) { where = ComputeWhere(header, where); var helper = new OracleDBHelper(); var total = 0; string whereStr = helper.GetWhereStr(where); List <v_lhproducts_policyModel> resultList = new List <v_lhproducts_policyModel>(); if (string.IsNullOrEmpty(header.HeadID)) { List <V_LHPRODUCTS_UNPOLICYHEADID> data = helper.GetWithWhereStrByPage <V_LHPRODUCTS_UNPOLICYHEADID>(whereStr, where, index, size); total = helper.Count <V_LHPRODUCTS_UNPOLICYHEADID>(whereStr); var t = typeof(V_LHPRODUCTS_UNPOLICYHEADID); var pis = t.GetProperties().ToList(); data.ForEach(p => { var item = new v_lhproducts_policyModel(); pis.ForEach(pi => { var value = pi.GetValue(p, null); pi.SetValue(item, value); }); resultList.Add(item); }); } else { List <v_lhproducts_policyModel> data = helper.GetWithWhereStrByPage <v_lhproducts_policyModel>(whereStr, where, index, size); total = helper.Count <v_lhproducts_policyModel>(whereStr); resultList = data; } PageResult <v_lhproducts_policyModel> result = new PageResult <v_lhproducts_policyModel>() { Total = total, Result = resultList }; return(result); }
public static OracleDataReader QueryByDividPage2(ref Common.DividPage page, string Tables) { if (page == null) { page = new Common.DividPage(); } if (page.CurrentPageNumber == 0) { page.CurrentPageNumber = 1; } int RecordCounts = 0; int TopNumber = page.CurrentPageShowCounts * page.CurrentPageNumber; int WhereNumber = (page.CurrentPageNumber - 1) * page.CurrentPageShowCounts; string strSqlRecordCounts = "select count(*) as recordcounts from (" + Tables + ")"; using (OracleDataReader dr = OracleDBHelper.ExecuteReader(strSqlRecordCounts)) { if (dr.Read()) { RecordCounts = int.Parse(dr["recordcounts"].ToString()); } } //string strSql = "Select * From (Select ROW_NUMBER() OVER(@Sort) AS PageRowNumber , * From (@Tables) ) Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber "; string strSql = "Select * From (@Tables) Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber "; strSql = strSql.Replace("@TopNumber", TopNumber.ToString()); //strSql = strSql.Replace("@Sort", Sort.ToString()); //strSql = strSql.Replace("@Fields", Fields.ToString()); strSql = strSql.Replace("@Tables", Tables.ToString()); //strSql = strSql.Replace("@Filter", Filter.ToString()); strSql = strSql.Replace("@WhereNumber", WhereNumber.ToString()); OracleDataReader dR = OracleDBHelper.ExecuteReader(strSql); page.RecordCounts = RecordCounts; if (page.RecordCounts > 0) { page.PagesCount = (RecordCounts + page.CurrentPageShowCounts - 1) / page.CurrentPageShowCounts; } else { page.PagesCount = 0; page.CurrentPageRecordCounts = 0; } return(dR); }
public bool obOrderUpload(List <string> billNos) { var token = CommonToken.GetToken(); var http = new ArrowInterface.ArrowInterface(); var Helper = new OracleDBHelper(); var uploadParamRepository = new DefaultRepository <ObOrderUploadParam>(DBTypeEnums.ORACLE); var lhobounorderRepository = new DefaultRepository <LH_OUTBOUNDORDER>(DBTypeEnums.ORACLE); Dictionary <string, CompareEnum> compare = new Dictionary <string, CompareEnum>(); compare.Add("LHODONO", CompareEnum.In); var pars = uploadParamRepository.SelectWithWhere(new { LHODONO = billNos }, compare, null); List <string> errors = new List <string>(); pars.ForEach(p => { try { var result = http.obOrderUpload(token.Token, p); if (result.Success) { var sql = "UPDATE LH_OUTBOUNDORDER SET FSTATUS=:FSTATUS WHERE lhodoID=:lhodoID"; lhobounorderRepository.Execute(sql, new { FSTATUS = 7, lhodoID = p.lhodoID }, null); } else { errors.Add($"单据【{p.lhodoID}】车牌同步失败"); } } catch (OracleException e) { var message = string.Format($"单据【{p.lhodoID}】车牌上传结果更新失败"); Common_Arrow.LogHelper.Info(message); Common_Arrow.LogHelper.Error(e); } }); if (errors.Count > 0) { throw new Exception(string.Join("\r\n", errors)); } return(true); }
/// <summary> /// 获取根据头表ID获取detail表的所有记录,没有合计行,因为上面的那个函数被子类重写了,只好加一个绝对没有合计行的函数,没写好。 /// </summary> public virtual List <TBase_Model> GetModelListByHeaderIDNoSum(int headerID) { try { string sql = "SELECT * FROM " + GetViewName() + " where IsDel != 2 and "; sql += GetHeaderIDFieldName() + " = " + headerID.ToString() + " " + GetDetailListOrderBySql(); using (OracleDataReader reader = OracleDBHelper.ExecuteReader(sql)) { return(ToModels(reader)); } } catch (Exception ex) { throw new Exception(ex.Message); } }
public virtual List <TBase_Model> GetModelListADF(User.UserModel user, TBase_Model model) { try { string sql = "SELECT * FROM " + GetViewName() + GetFilterSql(user, model); using (OracleDataReader reader = OracleDBHelper.ExecuteReader(sql)) { return(ToModels(reader)); } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 修改状态 /// </summary> /// <param name="user"></param> /// <param name="model"></param> /// <param name="NewStatus"></param> /// <param name="strError"></param> /// <returns></returns> public bool UpdateModelStatus(User.UserModel user, ref TBase_Model model, int NewStatus, ref string strError, bool NeedReturnModel) { try { int iRows = 0; int iOut = 0; OracleParameter[] param = new OracleParameter[] { new OracleParameter("@Rows", iRows), new OracleParameter("@ErrorMsg", OracleDbType.NVarchar2, 100), new OracleParameter("@ID", OracleDBHelper.ToDBValue(GetUpdateModelStatusID(user, model, NewStatus))), new OracleParameter("@NewStatus", OracleDBHelper.ToDBValue(NewStatus)), new OracleParameter("@Auditor", OracleDBHelper.ToDBValue(user.UserNo)), new OracleParameter("@AuditorTime", OracleDBHelper.ToDBValue(DateTime.Now)), new OracleParameter("@TerminateReasonID", OracleDBHelper.ToDBValue(model.TerminateReasonID)), new OracleParameter("@TerminateReason", OracleDBHelper.ToDBValue(model.TerminateReason)), new OracleParameter("@RowVersion", OracleDBHelper.ToDBValue(DateTime.Now)) }; param[0].Direction = System.Data.ParameterDirection.Output; param[1].Direction = System.Data.ParameterDirection.Output; int i = OracleDBHelper.RunProcedure(this.GetUpdateStatusProcedureName(), param, out iOut); strError = param[1].Value.ToString(); if ((int)param[0].Value == -1) { return(false); } else { if (NeedReturnModel) { model = GetModelByID(model.ID); if (model == null) { throw new Exception("GetModelByID(modelID)出错,没有找到相符的记录,可能是视图有错误!"); } } return(true); } } catch (Exception ex) { throw new Exception(ex.Message); } }
public static List <ComboBoxItemExt> GetComboBoxItemExt(string strSql) { List <ComboBoxItemExt> items = new List <ComboBoxItemExt>(); using (OracleDataReader dr = OracleDBHelper.ExecuteReader(CommandType.Text, strSql)) { while (dr.Read()) { ComboBoxItemExt item = new ComboBoxItemExt(); item.ID = dr["ID"].ToDBString(); item.Name = dr["Name"].ToDBString(); items.Add(item); } } return(items); }
//ymh 网页的获取下拉框方法 public static List <SelectListItem> GetSelectListItemExt(string strSql) { List <SelectListItem> items = new List <SelectListItem>(); using (OracleDataReader dr = OracleDBHelper.ExecuteReader(CommandType.Text, strSql)) { while (dr.Read()) { SelectListItem item = new SelectListItem(); item.Value = dr["ID"].ToDBString(); item.Text = dr["Name"].ToDBString(); items.Add(item); } } return(items); }
public List <LH_Policy> GetPolicies(ICPOBILL_PolicyDTO header) { //检查传入参数是否合法 CheckNull(header); var helper = new OracleDBHelper(); var sql = @"SELECT DISTINCT HEADID,POLICYNAME,ORDERTYPE,ORDERSUBTYPE,PRODCHANNEL,DEPTNAME from LH_POLICY WHERE 1=1 "; //选择订单类型为常规订单的,如果用户要选择促销政策头ID的话则需要同时判断:订单所属公司(事业部)、厂家账号(经销商账号)、销售渠道、业务类型、五项头字段信息来取促销政策头ID信息 string where = $@"AND ORDERTYPE='{header.OrderType}' AND DEPTNAME LIKE '%{header.BrandName}%' AND ORDERSUBTYPE='{header.OrderSubType}' AND PRODCHANNEL='{header.Channel}' AND ACCTCODES LIKE '%{header.Account}%'"; sql += where; var policies = helper.Select <LH_Policy>(sql); return(policies); }
//public bool SaveModelAndDeatilListToDB(User.UserModel user, TBase_Model model, List<TBase_DetailModel> modelList, ref string strError) //{ // try // { // bool succ = false; // Dictionary<string, OracleParameter[]> ParameterList = new Dictionary<string, OracleParameter[]>(); // //把creater、createTime、modifyer等信息填到model中去. // OracleParameter[] paramModel = GetSaveModelOracleParameter(model); // ParameterList.Add(GetModelSqlPara(GetTableName()), paramModel); // for (int i = 0; i < modelList.Count; i++) // { // TBase_DetailModel modelDetail = modelList[i]; // //AddModelOperatorInfo(user, ref model); // OracleParameter[] param = GetSaveModelDetailsOracleParameter(modelDetail); // ParameterList.Add(GetModelSqlPara(GetTableNameDetail()), param); // } // succ = OracleDBHelper.RunSqls(ParameterList); // return succ; // } // catch (Exception ex) // { // throw new Exception(ex.Message); // } //} private string GetModelSqlPara(string tableName) { try { List <string> lstSqlPara = new List <string>(); string sqlPara = "insert into " + tableName; string fields = string.Empty; string values = string.Empty; string sql = "select column_name from user_tab_columns where table_name=upper('" + tableName + "')"; using (OracleDataReader reader = OracleDBHelper.ExecuteReader(sql)) { while (reader.Read()) { lstSqlPara.Add(reader["column_name"].ToString()); } } if (lstSqlPara == null) { return(string.Empty); } foreach (string item in lstSqlPara) { if (!item.Equals("CREATEDATE") && !item.Equals("QUALITYDATE") && !item.Equals("PRINTTIME")) { fields += item + ","; values += ":" + item + ","; } } sqlPara += "(" + fields.TrimEnd(',') + ")" + " values " + "(" + values.TrimEnd(',') + ")"; return(sqlPara); } catch (Exception ex) { throw new Exception(ex.Message); } }