OA2021/DAL/ApprovalTravelDetailsService.cs

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

namespace DAL
{
    /// <summary>
    /// 公务出访详情
    /// </summary>
    public class ApprovalTravelDetailsService
    {

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

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

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

        public List<ApprovalTravelDetails> GetByParentID(int ParentID)
        {
            return excuteSql($"select * from ApprovalTravelDetails where ParentId= {ParentID} and isdel = 0");
        }

        public int AddList(List<ApprovalTravelDetails> list)
        {
            int rows = 0;

            SqlCommand cmd = SqlHelper.createCon().CreateCommand();

            cmd.Connection.Open();
            SqlTransaction trans = cmd.Connection.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                foreach (var item in list)
                {
                    string sql = string.Format("insert into ApprovalTravelDetails values('{0}',{1},'{2}',0)",item.Time,item.ParentId,item.Details);
                    cmd.CommandText = sql;
                    rows += cmd.ExecuteNonQuery();
                }
                trans.Commit();
                cmd.Connection.Close();
                return rows;
            }
            catch
            {
                trans.Rollback();
                cmd.Connection.Close();
                return rows;
            }
        }


        public bool DeleteByDiiD(int diid)
        {
            if (SqlHelper.ExecuteNonQuery($@"update ApprovalTravelDetails set Isdel=1 where id in (select a.id from ApprovalTravelDetails a, ApprovalTravel b where 
                a.parentId = b.Id and b.Diid = {diid} )  ", CommandType.Text) > 0)
            {
                return true;
            }
            return false;
        }
    }
}