1. /* SQL CASE 语句写法
* SELECT TABLE1.USER_ID, TABLE1.COMP_CODE, TABLE1.DEPT_CODE, TABLE1.USER_NAME,
TABLE2.COMP_NAME, TABLE3.DEPT_NAME,
(case when (TABLE1.ADMIN_YN = '1') then '管理员'
else '普通用户' end) as USER, TABLE1.ADMIN_YN
*/
2. Replace语法
sql += "AND (REPLACE(BOOK_NAME,' ','') LIKE @BOOK_NAME or ";
sql += "REPLACE(BOOK_NAME1,' ','') LIKE @BOOK_NAME)";
ht.Add("@BOOK_NAME", "%" + strSm.Replace(" ","") + "%");
3. Linq
Select
IQueryable<Tb_TSGL> model = null;
if ("Keywords".Equals(strValue))
{
model = from r in DataContext.Tb_TSGL
where SqlMethods.Like(r.TSGJC, string.Format("%{0}%", strText))
select r;
}
if ("bookName".Equals(strValue))
{
model = from r in DataContext.Tb_TSGL
where SqlMethods.Like(r.TSM_ZW, string.Format("%{0}%", strText)) || SqlMethods.Like(r.TSM_CW, string.Format("%{0}%", strText))
select r;
}
return model;
Update
/// <summary>
/// 更新图书小类Table
/// </summary>
/// <param name="model"></param>
public void Tb_TSXL_UpdateAll(Tb_TSXL model)
{
Tb_TSXL updateModel = (from s in DataContext.Tb_TSXL
where s.ID == model.ID
select s).FirstOrDefault<Tb_TSXL>();
updateModel.SFSY = model.SFSY;
updateModel.NAME = model.NAME;
updateModel.TSDL_ID = model.TSDL_ID;
updateModel.KoreanName = model.KoreanName;
updateModel.FL = model.FL; DataContext.SubmitChanges();
}
Insert
public void Tb_TSDL_Insert(Tb_TSDL model)
{
DataContext.Tb_TSDL.InsertOnSubmit(model);
DataContext.SubmitChanges();
}
Delete
public void Tb_TSDL_Delete(Tb_TSDL model)
{
Tb_TSDL deleteModel = (from s in DataContext.Tb_TSDL
where s.ID == model.ID
select s).FirstOrDefault<Tb_TSDL>(); DataContext.Tb_TSDL.DeleteOnSubmit(deleteModel);
DataContext.SubmitChanges();
}
4. Procedure
/// <summary>
/// 利用存储过程，获取图书列表
/// </summary>
/// <param name="f"></param>
/// <param name="language"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="total"></param>
/// <param name="page"></param>
/// <returns></returns>
public DataTable GetBookDefault(string f, int language, int pageSize, int pageIndex, out int total, out int page)
{
IDataParameter[] iData = new IDataParameter[]; iData[] = new SqlParameter("@TableName", "Tb_TSGL");
iData[] = new SqlParameter("@FieldList", "*");
iData[] = new SqlParameter("@PrimaryKey", "ID"); switch (language)
{
case : /*朝文*/
if (f.Equals("Z")) //중점도서
{
iData[] = new SqlParameter("@Where", "SFZDTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
}
else if (f.Equals("T")) //추천도서
{
iData[] = new SqlParameter("@Where", "SFTJTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
}
else if (f.Equals("N")) //최신도서
{
iData[] = new SqlParameter("@Where", "TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
} else if (f.Equals("Natural")) //자연과학도서
{
iData[] = new SqlParameter("@Where", "TSXL_ID Like '%N%' and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
}
else if (f.Equals("Complex")) //종합성도서
{
iData[] = new SqlParameter("@Where", "TSXL_ID Like '%Z%' and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
}
else if (f.Equals("Elec")) //전자도서
{
iData[] = new SqlParameter("@Where", "SFDZTS = 1 and (BType = 'K' or BType = 'U') and SFKSXS = 1");
}
else if (f.Equals("Social")) //사회과학도서
{
iData[] = new SqlParameter("@Where", "TSXL_ID Like '%C%' and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
}
else if (f.Equals("Periodical")) //정기간행물
{
iData[] = new SqlParameter("@Where", "TSDL_ID = 2 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
}
else if (f.Equals("Cheap")) //특가도서
{
iData[] = new SqlParameter("@Where", "SFGDTS = 1 and SFKSXS = 1 and (r.BType = 'K' or BType = 'U')");
} else
{
iData[] = new SqlParameter("@Where", "(BType = 'K' || BType = 'U') and SFKSXS = 1");
}
break; case : //中文 if (f.Equals("Z")) //重点图书
{
iData[] = new SqlParameter("@Where", "SFZDTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
}
else if (f.Equals("T")) //推荐图书
{
iData[] = new SqlParameter("@Where", "SFTJTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
}
else if (f.Equals("N")) //최신도서
{
iData[] = new SqlParameter("@Where", "TSDL_ID = 1 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
} else if (f.Equals("Natural")) //자연과학도서
{
iData[] = new SqlParameter("@Where", "TSXL_ID Like '%N%' and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
}
else if (f.Equals("Complex")) //종합성도서
{
iData[] = new SqlParameter("@Where", "TSXL_ID Like '%Z%' and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
}
else if (f.Equals("Elec")) //전자도서
{
iData[] = new SqlParameter("@Where", "SFDZTS = 1 and (BType = 'C' or BType = 'U') and SFKSXS = 1");
}
else if (f.Equals("Social")) //사회과학도서
{
iData[] = new SqlParameter("@Where", "TSXL_ID Like '%C%' and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
}
else if (f.Equals("Periodical")) //정기간행물
{
iData[] = new SqlParameter("@Where", "TSDL_ID = 2 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
}
else if (f.Equals("Cheap")) //특가도서
{
iData[] = new SqlParameter("@Where", "SFGDTS = 1 and SFKSXS = 1 and (r.BType = 'C' or BType = 'U')");
} else
{
iData[] = new SqlParameter("@Where", "(BType = 'C' || BType = 'U') and SFKSXS = 1");
}
break; default:
iData[] = new SqlParameter("@Where", "");
break; }
iData[] = new SqlParameter("@Order", "SCSJ desc");
iData[] = new SqlParameter("@SortType", "");
iData[] = new SqlParameter("@RecorderCount", "");
iData[] = new SqlParameter("@PageSize", pageSize);
iData[] = new SqlParameter("@PageIndex", pageIndex);
iData[] = new SqlParameter("@TotalCount", );
iData[] = new SqlParameter("@TotalPageCount", ); iData[].Direction = ParameterDirection.Output;
iData[].Direction = ParameterDirection.Output; return Dao.GetBookDefault(iData, out total, out page);
}
====================================陆续更新=======================================