OA2021/DAL/SendMachineSuppliesDataServices.cs

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

namespace DAL
{
    /// <summary>
    /// 雷怡 2021-08-18-14：34
    /// 送机物资资料数据访问层
    /// </summary>
    public class SendMachineSuppliesDataServices
    {

        List<SendMachineSuppliesData> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<SendMachineSuppliesData>.excuteSql(new SendMachineSuppliesData(), "SendMachineSuppliesData", sql, CommandType.Text, param);
        }

        SendMachineSuppliesData excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<SendMachineSuppliesData> List = excuteSql(sql, param);
            SendMachineSuppliesData smsd = new SendMachineSuppliesData();

            //判断集合是否为空
            if (List == null || List.Count == 0)
            {
                return smsd;
            }
            //返回单个对象
            return List[0];
        }

        /// <summary>
        /// //增Add
        /// </summary>
        /// <param name="smsd">送机物资数据类</param>
        /// <returns></returns>
        public bool Add(SendMachineSuppliesData smsd)
        {
            string sql = "insert into SendMachineSuppliesData values(@Country,@MaterialName,@MaterialPrice,@MaterialRemark,@Operator,@OperatingTime,@Isdel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Country",smsd.Country),
                new SqlParameter("@MaterialName",smsd.MaterialName),
                new SqlParameter("@MaterialPrice",smsd.MaterialPrice),
                new SqlParameter("@MaterialRemark",smsd.MaterialRemark),
                new SqlParameter("@Operator",smsd.Operator),
                new SqlParameter("@OperatingTime",smsd.OperatingTime),
                new SqlParameter("@Isdel",smsd.Isdel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// //改Update
        /// </summary>
        /// <param name="smsd">送机物资数据</param>
        /// <returns></returns>
        public bool Update(SendMachineSuppliesData  smsd)
        {
            string sql = "update SendMachineSuppliesData set Country=@Country,MaterialName=@MaterialName,MaterialPrice=@MaterialPrice,MaterialRemark=@MaterialRemark,"
                + "Operator=@Operator,OperatingTime=@OperatingTime,Isdel=@Isdel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Country",smsd.Country),
                new SqlParameter("@MaterialName",smsd.MaterialName),
                new SqlParameter("@MaterialPrice",smsd.MaterialPrice),
                new SqlParameter("@MaterialRemark",smsd.MaterialRemark),
                new SqlParameter("@Operator",smsd.Operator),
                new SqlParameter("@OperatingTime",smsd.OperatingTime),
                new SqlParameter("@Isdel",smsd.Isdel),
                new SqlParameter("@Id",smsd.Id)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        ///  //删
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update SendMachineSuppliesData set Isdel=1 where Id=@Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 根据条件查询条件获取 - 分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="MaterialName"></param>
        /// <returns></returns>
        public List<SendMachineSuppliesData> GetPagingQuery(int pageIndex, out int sumPage, out int totalRecord, string MaterialName)
        {
            string sqlwhere = "IsDel = 0  ";

            if (!string.IsNullOrEmpty(MaterialName) && !string.IsNullOrEmpty(MaterialName))
                sqlwhere += " and (MaterialName like '%" + MaterialName + "%')";

            return PageBase<SendMachineSuppliesData>.excutePageSql(new SendMachineSuppliesData(), "SendMachineSuppliesData", "SendMachineSuppliesData", "*", "OperatingTime desc", sqlwhere, 10, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <returns></returns>
        public List<SendMachineSuppliesData> GetAll()
        {
            return excuteSql("select * from SendMachineSuppliesData Where IsDel = 0 order by OperatingTime desc");
        }
        /// <summary>
        /// 根据id 查询数据
        /// </summary>
        /// <returns></returns>
        public SendMachineSuppliesData GetAllById(int id)
        {
            return excuteType("select * from SendMachineSuppliesData Where IsDel = 0 and id ='" + id + "'");
        }

        /// <summary>
        /// 根据 物资名称 查询数据
        /// </summary>
        /// <returns></returns>
        public SendMachineSuppliesData GetAllByMaterialName(string MaterialName)
        {
            return excuteType("select * from SendMachineSuppliesData Where IsDel = 0 and MaterialName like '%" + MaterialName + "'%");
        }
    }
}