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