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); } }
/// <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); } }
/// <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 OracleDataReader GetRowBySql(string sql) { OracleDataReader reader = null; try { reader = OracleDBHelper.ExecuteReader(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return(reader); }
/// <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); } }
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 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> /// 获取根据头表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 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 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); } }
/// <summary> /// 通过sql获取单个对象 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public TBase_Model GetModelBySql(TBase_Model mdoel) { try { using (OracleDataReader reader = OracleDBHelper.ExecuteReader(GetModelSql(mdoel))) { if (reader.Read()) { return(ToModel(reader)); } else { return(null); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 获取新的单据号 /// </summary> /// <returns></returns> public string GetNewOrderNo(string strPrex, int iPadLeft) { string strNewNo = strPrex; try { string strSql = "select isnull(max(" + GetOrderNoFieldName() + "),'') as no from " + GetTableName() + " where " + GetOrderNoFieldName() + " like '" + strNewNo + "%'"; using (OracleDataReader reader = OracleDBHelper.ExecuteReader(strSql)) { if (reader.Read()) { string strNo = reader["no"].ToString(); if (strNo == "") { strNo = "1".PadLeft(iPadLeft, '0'); } else { long iNumber = long.Parse(strNo.Substring(strNewNo.Length)); iNumber++; strNo = iNumber.ToString().PadLeft(iPadLeft, '0'); } strNewNo += strNo; return(strNewNo); } else { throw new Exception("取单据号出错!" + strNewNo); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
public int GetTableID(string strSeq) { try { string strSql = "select " + strSeq + ".Nextval from dual"; using (OracleDataReader reader = OracleDBHelper.ExecuteReader(strSql)) { if (reader.Read()) { int ID = reader["Nextval"].ToInt32(); return(ID); } else { throw new Exception("取单据ID出错!"); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 通过过滤条件得到model /// </summary> /// <param name="strFilter"></param> /// <returns></returns> public TBase_Model GetModelByFilter(string strFilter) { try { string sql = "SELECT * FROM " + this.GetViewName() + " WHERE IsDel != 2 and " + strFilter; using (OracleDataReader reader = OracleDBHelper.ExecuteReader(sql)) { if (reader.Read()) { return(ToModel(reader)); } else { return(null); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 分页查询 /// </summary> /// <param name="RecordCounts"></param> /// <param name="Tables"></param> /// <param name="Filter"></param> /// <param name="PageSize"></param> /// <param name="PageNumber"></param> /// <param name="Fields"></param> /// <param name="Sort"></param> /// <returns></returns> public static OracleDataReader QueryByDividPage(ref Common.DividPage page, string Tables, string Filter = "", string Fields = "*", string Sort = "Order by ID Desc") { if (Fields.Trim() == "*") { if (Tables.Contains(")N")) { string temtable = Tables.Substring(Tables.Length - 1, 1); if (Fields.Trim() == "*") { Fields = temtable + '.' + Fields; } } else { Fields = Tables + '.' + Fields; } } 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 + " " + Filter; 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 , @Fields From @Tables @Filter ) 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); }