tijian_tieying/web/dccdc.DAL/kcDal.cs

using Dapper;
using dccdc.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace dccdc.DAL
{
    public class kcDal
    {
        public List<kcModel> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from kc where 1=1";
                if (!string.IsNullOrEmpty(id))
                {
                    sql += " and id=@id";
                }
                return conn.Query<kcModel>(sql, new { @id = id }).ToList();
            }
        }

        public List<kcModel> GetAllList(string id, string zt)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from kc where 1=1";
                if (!string.IsNullOrEmpty(id))
                {
                    sql += " and id=@id";
                }
                if (!string.IsNullOrEmpty(zt))
                {
                    sql += " and zt=@zt";
                }
                return conn.Query<kcModel>(sql, new { @id = id, @zt = zt }).ToList();
            }
        }

        public List<kcModel> GetListByKey(string key)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from kc where 1=1";
                if (!string.IsNullOrEmpty(key))
                {
                    sql += " and wzmc like @key";
                }
                return conn.Query<kcModel>(sql, new { key = "%" + key + "%" }).ToList();
            }
        }

        public List<kcModel> GetListByKey(string key,string sign)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from kc where 1=1";
                if (!string.IsNullOrEmpty(key))
                {
                    sql += " and wzmc like @key";
                }
                if (!string.IsNullOrEmpty(sign))
                {
                    sql += " and sign = @sign";
                }
                return conn.Query<kcModel>(sql, new { key = "%" + key + "%", sign = sign }).ToList();
            }
        }

        public object save(kcModel model)
        {
            string sql = "";
            if (model.id == 0)
            {
                sql = @"INSERT INTO [dbo].[kc]
                               ([wzid]
                               ,[wzmc]
                               ,[sl]
                               ,[czsj]
                               ,[sign])
                         VALUES
                               (@wzid
                               ,@wzmc
                               ,@sl
                               ,@czsj
                               ,@sign)";
            }
            else
            {
                sql = @"UPDATE [dbo].[kc]
                       SET [wzid] = @wzid
                          ,[wzmc] = @wzmc
                          ,[sl] = @sl
                          ,[czsj] = @czsj
                          ,[sign] = @sign
                          WHERE  id=@id";
            }

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql, model);
                    if (result > 0)
                        return new { State = 1, Message = "保存成功！" };
                    else
                        return new { State = 0, Message = "保存失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object saveList(List<kcModel> models)
        {
            string sql1 = @"INSERT INTO [dbo].[kc]
                               ([wzid]
                               ,[wzmc]
                               ,[sl]
                               ,[czsj]
                               ,[sign])
                         VALUES
                               (@wzid
                               ,@wzmc
                               ,@sl
                               ,@czsj
                               ,@sign)";
            string sql2 = @"UPDATE [dbo].[kc]
                       SET [wzid] = @wzid
                          ,[wzmc] = @wzmc
                          ,[sl] = @sl
                          ,[czsj] = @czsj
                          ,[sign] = @sign
                          WHERE  id=@id";

            List<kcModel> models1 = new List<kcModel>();
            List<kcModel> models2 = new List<kcModel>();


            List<kcModel> haves;
            StringBuilder sqlhave=new StringBuilder();
            sqlhave.Append("select * from kc where wzid = -1 and sign = -1");
            foreach (kcModel model in models)
            {
                sqlhave.Append(" union ");
                sqlhave.Append("select * from kc where wzid = " + model.wzid + " and sign = " + model.sign);
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    haves = conn.Query<kcModel>(sqlhave.ToString()).ToList();
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }

            bool havee;
            foreach (kcModel model in models)
            {
                havee = false;
                foreach (kcModel have in haves)
                {
                    if (have.wzid == model.wzid && have.sign == model.sign)
                    {
                        have.sl += model.sl;
                        models2.Add(have);
                        havee = true;
                        break;
                    }
                }
                if (!havee)
                    models1.Add(model);
            }


            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result1 = conn.Execute(sql1, models1);
                    int result2 = conn.Execute(sql2, models2);
                    if (result1 + result2 > 0)
                        return new { State = 1, Message = "保存成功！" };
                    else
                        return new { State = 0, Message = "保存失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object delete(string id)
        {
            string sql = "delete from kc where id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql, new { id = id });
                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public int getCount(string key)
        {
            string sql = "select count(1) from dbo.kc where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { key = "%" + key + "%" });
            }
        }

        public List<kcModel> getPage(int page, int pagesize, string key)
        {
            string sql = "select *,row_number() over(order by id) as rownum from kc where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.kcModel>(sql, new { key = "%" + key + "%" }).ToList();
            }
        }

        public int getCount(string key, string sign)
        {
            string sql = "select count(1) from dbo.kc where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            if (!string.IsNullOrEmpty(sign))
            {
                sql += " and sign like @sign";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { key = "%" + key + "%", sign = "%" + sign + "%" });
            }
        }

        public List<kcModel> getPage(int page, int pagesize, string key, string sign)
        {
            string sql = "select *,row_number() over(order by id) as rownum from kc where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            if (!string.IsNullOrEmpty(sign))
            {
                sql += " and sign like @sign";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.kcModel>(sql, new { key = "%" + key + "%", sign = "%" + sign + "%" }).ToList();
            }
        }
    }
}