OA2021/DAL/OfferDataService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    /// <summary>
    /// 报价基础信息数据访问类
    /// </summary>
    public class OfferDataService
    {
        /// <summary>
        /// 查询所有—分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public DataTable GetAll(int pageIndex, out int sumPage, out int totalRecord,int Cid,int pageSize)
        {
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@pageIndex", pageIndex),
                new SqlParameter("@pageSize", pageSize),
                new SqlParameter("@Cid",Cid)
            };
            //查询总条数
            totalRecord = Convert.ToInt32(SqlHelper.ExecuteScalar("select count(1) from dbo.OfferData T left join setData S on T.Pid=S.id where T.IsDel <> 1 and S.IsDel <> 1 and Cid="+Cid, CommandType.Text, null));
            //计算出总页数
            sumPage = totalRecord%10==0?totalRecord/10:totalRecord/10+1;
            return SqlHelper.TransferProcedure("[exec_OfferData_Query]", CommandType.StoredProcedure, parameter);
        }

        public bool AddOfferData(OfferData od)
        {
            string sql = "insert into OfferData values(@PId,@Cid,@PublishedPrice,@BasePrice,@AddedTax,@IsDel)";
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@PId",od.PId),
                new SqlParameter("@Cid",od.Cid),
                new SqlParameter("@PublishedPrice",od.PublishedPrice),
                new SqlParameter("@BasePrice",od.BasePrice),
                new SqlParameter("@AddedTax",od.AddedTax),
                new SqlParameter("@IsDel",od.IsDel)
            };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter)>0)
                return true;
            return false;
            
        }

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

        /// <summary>
        /// 根据编号查询信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OfferData> GetOfferDataByID(int id)
        {
            //调用获取单个对象的方法
            return excuteSql("select T.*,S.Name from OfferData T left join setData S on T.Pid=S.id where T.Id = @id and T.IsDel = 0", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        OfferData excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<OfferData> setDataList = excuteSql(sql, param);
            //判断集合是否为空
            if (setDataList == null || setDataList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return setDataList[0];
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<OfferData> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<OfferData>.excuteSql(new OfferData(), "OfferData", sql, CommandType.Text, param);
        }
        
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="od"></param>
        /// <returns></returns>
        public bool EditSetData(OfferData od)
        {
            string sql = "update OfferData set PublishedPrice = @PublishedPrice ,BasePrice = @BasePrice,AddedTax=@AddedTax where id=@id";
            SqlParameter[] parameter = new SqlParameter[] { 
                new SqlParameter("@PublishedPrice",od.PublishedPrice),
                new SqlParameter("@BasePrice",od.BasePrice),
                new SqlParameter("@AddedTax",od.AddedTax),
                new SqlParameter("@id",od.Id)
            };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter)>0)
                return true;
            return false;
        }
    }
}