public IList <ReferencesSchool> GetBySchoolId(int schId)
{
string sql = "SELECT * FROM v_sch_refs WHERE SId=" + schId;
DataSet set = DbHelperSQL.Query(sql);
return(DbHelperSQL.DT2List <ReferencesSchool>(set.Tables[0]));
}
public IList <ReferencesSchool> GetBySchoolId(int schId, int index, int count, out int total)
{
total = DbHelperSQL.Count("v_sch_refs");
string sql = string.Format("SELECT * FROM v_sch_refs WHERE SId={0} ORDER BY RId OFFSET {1} ROW FETCH NEXT {2} ROWS ONLY",
schId,
(index - 1) * count,
count);
DataSet set = DbHelperSQL.Query(sql);
return(DbHelperSQL.DT2List <ReferencesSchool>(set.Tables[0]));
}
public IList <ReferencesSchool> GetByFuzzyName(string fuzzy)
{
if (string.IsNullOrEmpty(fuzzy))
{
fuzzy = "";
}
string sql = string.Format("SELECT TOP 20 * FROM v_sch_refs WHERE SName LIKE '%{0}%' OR RName LIKE '%{0}%'", fuzzy);
DataSet set = DbHelperSQL.Query(sql);
return(DbHelperSQL.DT2List <ReferencesSchool>(set.Tables[0]));
}
public IList <Model.Area> Get(string where, params string[] parameters)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select Id,Level,Name,Pid,Position,ShortName,Sort from Area ");
if (!string.IsNullOrEmpty(where))
{
sb.Append("where ").Append(where);
}
DataSet set = DbHelperSQL.Query(sb.ToString());
return(DbHelperSQL.DT2List <Model.Area>(set.Tables[0]));
}
public IList <School> Get(string where, params string[] parameters)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select Id,Name,ShortName,AreaId from School ");
if (!string.IsNullOrEmpty(where))
{
sb.Append("where ").Append(where);
}
DataSet set = DbHelperSQL.Query(sb.ToString());
return(DbHelperSQL.DT2List <School>(set.Tables[0]));
}
public School Get(int id)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select s.*,a.Name as AreaName from School s left join Area a on s.AreaId=a.Id where s.Id={0}", id);
DataSet set = DbHelperSQL.Query(sb.ToString());
IList <School> list = DbHelperSQL.DT2List <School>(set.Tables[0]);
if (list == null || list.Count <= 0)
{
return(School.NullSchool());
}
return(list[0]);
}
public References GetById(int id)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select r.*,s.Name as SchoolName from [References] r left join School s on r.SchoolId=s.Id where r.Id={0}", id);
DataSet set = DbHelperSQL.Query(sb.ToString());
IList <References> list = DbHelperSQL.DT2List <References>(set.Tables[0]);
if (list == null || list.Count <= 0)
{
return(References.NullReferences());
}
return(list[0]);
}
public IList <ReferencesSchool> GetAll()
{
DataSet set = DbHelperSQL.Query("select RName,Tel, SName from v_sch_refs");
if (set != null && set.Tables.Count > 0)
{
return(DbHelperSQL.DT2List <ReferencesSchool>(set.Tables[0]));
}
return(new List <ReferencesSchool>()
{
new ReferencesSchool()
});
}
public IList <Model.Area> Get(string where, int page_index, int page_count, out int total_page, params string[] parameters)
{
total_page = DbHelperSQL.Count("Area");
StringBuilder sb = new StringBuilder();
sb.Append("SELECT Id,Level,Name,Pid,Position,ShortName,Sort from Area ");
if (!string.IsNullOrEmpty(where))
{
sb.Append("where ").Append(where);
}
sb.AppendFormat("ORDER BY Id OFFSET {0} ROW FETCH NEXT {1} ROWS ONLY ", (page_index - 1) * page_count, page_count);
DataSet ds = DbHelperSQL.Query(sb.ToString());
return(DbHelperSQL.DT2List <Model.Area>(ds.Tables[0]));
}
public IList <References> Get(string where, int page_index, int page_count, out int total_page, params string[] parameters)
{
total_page = DbHelperSQL.Count("[References]");
StringBuilder sb = new StringBuilder();
sb.Append("SELECT Id,Name,Tel,SchoolId FROM [References] ");
if (!string.IsNullOrEmpty(where))
{
sb.Append("where ").Append(where);
}
sb.AppendFormat("ORDER BY Id OFFSET {0} ROW FETCH NEXT {1} ROWS ONLY ", (page_index - 1) * page_count, page_count);
DataSet ds = DbHelperSQL.Query(sb.ToString());
return(DbHelperSQL.DT2List <References>(ds.Tables[0]));
}
public IList <School> GetBySql(string sql)
{
if (string.IsNullOrEmpty(sql))
{
throw new NullReferenceException("null err,parameter sql is null");
}
DataSet set = DbHelperSQL.Query(sql);
if (set == null || set.Tables.Count <= 0)
{
return(new List <School>()
{
School.NullSchool()
});
}
return(DbHelperSQL.DT2List <School>(set.Tables[0]));
}
public IList <School> Get(string where, int page_index, int page_count, out int total_page, params string[] parameters)
{
total_page = DbHelperSQL.Count("School");
StringBuilder sb = new StringBuilder();
sb.Append("SELECT Id,AreaId,Name,ShortName FROM School ");
if (!string.IsNullOrEmpty(where))
{
sb.Append("where ").Append(where);
}
sb.AppendFormat("ORDER BY Id OFFSET {0} ROW FETCH NEXT {1} ROWS ONLY ", (page_index - 1) * page_count, page_count);
DataSet ds = DbHelperSQL.Query(sb.ToString());
if (ds == null || ds.Tables.Count <= 0)
{
return(new List <School>()
{
School.NullSchool()
});
}
return(DbHelperSQL.DT2List <School>(ds.Tables[0]));
}
