tijian_tieying/web/dccdc.DAL/InfectionRepositoryDal.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 InfectionRepositoryDal
    {

        public List<InfectionRepositoryModel> GetAllData()
        {
            string sql = "Select * From infection_repository Order By create_time desc";
            
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionRepositoryModel>(sql).ToList();
            }
        }

        public List<InfectionRepositoryModel> GetDataByID(int id)
        {
            string sql = string.Format("Select * From infection_repository Where id = {0} Order By create_time", id);

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionRepositoryModel>(sql).ToList();
            }
        }

        public List<InfectionRepositoryModel> getAllDataList(int page, int pagesize, string title, string start, string end)
        {
            string sql = "Select *,row_number() over(order by id) As rownum From infection_repository Where 1 = 1";
            if (!string.IsNullOrEmpty(title))
            {
                sql += " And title Like '%" + title + "%'";
            }
            if (!string.IsNullOrEmpty(start))
            {
                sql += string.Format(" And create_time >= '") + start + "'";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += string.Format(" And create_time <= '") + end + "'";
            }
            sql = "Select * From (" + sql + ") t Where t.rownum>(" + page + "-1)*" + pagesize + " And t.rownum<=" + page + "*" + pagesize + " Order By create_time desc";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<InfectionRepositoryModel>(sql).ToList();
            }
        }

        public int getCount(string title, string start, string end)
        {
            string sql = "Select Count(1) From infection_repository Where 1=1";
            if (!string.IsNullOrEmpty(title))
            {
                sql += " And title Like '%" + title + "%'";
            }
            if (!string.IsNullOrEmpty(start))
            {
                sql += string.Format(" And create_time >= '") + start + "'";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += string.Format(" And create_time <= '") + start + "'";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql);
            }
        }

        public object repositorySaveData(InfectionRepositoryModel model)
        {
            string sql = @"Insert Into infection_repository (title,address,content,create_time) Values(@title,@address,@content,@create_time)";
            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)
                {
                    string s = ex.Message;
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object repositoryModifyData(InfectionRepositoryModel model, string state)
        {
            string sql = string.Empty;
            if (state == "1")
            {
                sql = @"Update infection_repository Set title=@title,content=@content Where id=@id";
            }
            else
            {
                sql = @"Update infection_repository Set title=@title,address=@address,content=@content 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)
                {
                    string s = ex.Message;
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object deleteByID(string id)
        {
            string sql = string.Format("Delete From infection_repository Where id = {0}", id);
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql);
                    if (c > 0)
                    {
                        return new { State = 1, Message = "删除成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

    }
}