SQL 语句整理

  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[11];

            iData[0] = new SqlParameter("@TableName", "Tb_TSGL");
            iData[1] = new SqlParameter("@FieldList", "*");
            iData[2] = new SqlParameter("@PrimaryKey", "ID");

            switch (language)
            {
                case 1: /*朝文*/
                    if (f.Equals("Z"))  //중점도서
                    {
                        iData[3] = new SqlParameter("@Where", "SFZDTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
                    }
                    else if (f.Equals("T"))  //추천도서
                    {
                        iData[3] = new SqlParameter("@Where", "SFTJTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
                    }
                    else if (f.Equals("N"))  //최신도서
                    {
                        iData[3] = new SqlParameter("@Where", "TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
                    }

                    else if (f.Equals("Natural"))  //자연과학도서
                    {
                        iData[3] = 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[3] = 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[3] = new SqlParameter("@Where", "SFDZTS = 1 and (BType = 'K' or BType = 'U') and SFKSXS = 1");
                    }
                    else if (f.Equals("Social"))  //사회과학도서
                    {
                        iData[3] = 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[3] = new SqlParameter("@Where", "TSDL_ID = 2 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
                    }
                    else if (f.Equals("Cheap"))  //특가도서
                    {
                        iData[3] = new SqlParameter("@Where", "SFGDTS = 1 and SFKSXS = 1  and (r.BType = 'K' or BType = 'U')");
                    }

                    else
                    {
                        iData[3] = new SqlParameter("@Where", "(BType = 'K' || BType = 'U') and SFKSXS = 1");
                    }
                    break;

                case 2: //中文

                    if (f.Equals("Z"))  //重点图书
                    {
                        iData[3] = new SqlParameter("@Where", "SFZDTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'K' or BType = 'U')");
                    }
                    else if (f.Equals("T"))  //推荐图书
                    {
                        iData[3] = new SqlParameter("@Where", "SFTJTS = 1 and TSDL_ID = 1 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
                    }
                    else if (f.Equals("N"))  //최신도서
                    {
                        iData[3] = new SqlParameter("@Where", "TSDL_ID = 1 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
                    }

                    else if (f.Equals("Natural"))  //자연과학도서
                    {
                        iData[3] = 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[3] = 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[3] = new SqlParameter("@Where", "SFDZTS = 1 and (BType = 'C' or BType = 'U') and SFKSXS = 1");
                    }
                    else if (f.Equals("Social"))  //사회과학도서
                    {
                        iData[3] = 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[3] = new SqlParameter("@Where", "TSDL_ID = 2 and SFKSXS = 1 and (BType = 'C' or BType = 'U')");
                    }
                    else if (f.Equals("Cheap"))  //특가도서
                    {
                        iData[3] = new SqlParameter("@Where", "SFGDTS = 1 and SFKSXS = 1  and (r.BType = 'C' or BType = 'U')");
                    }

                    else
                    {
                        iData[3] = new SqlParameter("@Where", "(BType = 'C' || BType = 'U') and SFKSXS = 1");
                    }
                    break;

                default:
                    iData[3] = new SqlParameter("@Where", "");
                    break;

            }
            iData[4] = new SqlParameter("@Order", "SCSJ desc");
            iData[5] = new SqlParameter("@SortType", "1");
            iData[6] = new SqlParameter("@RecorderCount", "");
            iData[7] = new SqlParameter("@PageSize", pageSize);
            iData[8] = new SqlParameter("@PageIndex", pageIndex);
            iData[9] = new SqlParameter("@TotalCount", 0);
            iData[10] = new SqlParameter("@TotalPageCount", 0);

            iData[9].Direction = ParameterDirection.Output;
            iData[10].Direction = ParameterDirection.Output;

            return Dao.GetBookDefault(iData, out total, out page);
        }

 

====================================陆续更新=======================================