【备忘】DapperHelper.ExecuteSQL方法执行语句块写法

DapperHelper.ExecuteSQL方法执行语句块写法：
PS：BEGIN和END里面的语句需要分号（;）结尾

        [TestMethod]
        public void SaveEncounterParticipationTest()
        {
            StringBuilder sb = new StringBuilder();
            IList<OracleParameter> paras = new List<OracleParameter>();
            using (var cn = DapperHelper.CrateConnection(Dbs.IP))
            {
                sb.AppendLine("BEGIN");
                sb.AppendLine("update PRPA.ENCOUNTERPARTICIPATION set ISLAST = 0 where ISLAST != 0 and ENCOUNTERID = :p1 and ROLECODEID = :p2 and ENTITYID = :p3;");
                sb.AppendLine("insert into PRPA.ENCOUNTERPARTICIPATION(ENCOUNTEREVENTID, ENCOUNTERID, ROLECODEID, ENTITYID, ORGANIZATIONID, STATUSFLAG, STARTTIME, ISLAST) values(0, :p1, :p2, :p3, -1, 0, :p4, 1); ");
                paras.Add(new OracleParameter($"@p1", OracleDbType.Int32, 19902, ParameterDirection.Input));
                paras.Add(new OracleParameter($"@p2", OracleDbType.Int32, 62, ParameterDirection.Input));
                paras.Add(new OracleParameter($"@p3", OracleDbType.Int32, 3378, ParameterDirection.Input));
                paras.Add(new OracleParameter($"@p4", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
                sb.Append("END;");
                int ret = DapperHelper.ExecuteSql(cn, sb.ToString(), paras.ToArray());
                Assert.AreEqual<int>(-1, ret);
            }
        }

另外，拼接查询语句需使用DapperHelper.Query<T>()方法并参数化，才能解决SQL注入的漏洞：

	using System.Data.Entity.Core.Objects.DataClasses;
		
	string sql = $"select DISTINCT \r\n"
	+ $" enc.encounterid as InPatientID, \r\n"
	+ $" ENC.SEQNOTEXT as IPSeqNoText, \r\n"
	+ $" person.DISPLAYNAME as PatientName, \r\n"
	+ $" NVL(ENC.GENDERCODEID,-1) as SexFlag, \r\n"
	+ $" NVL(enc.SOURCEKINDCODEID,0) AS SoureKindCodeId, \r\n"
	+ $" NVL(diagnose.DISEASEID,-1) AS DiseaseID, \r\n"
	+ $" NVL(diagnose.DIAGNOSEDOCTORID,-1) as OPDoctorID, \r\n"
	+ $" disease.DISEASENAME, \r\n"
	+ $" diagnose.DIAGNOSEDESC as DiagnoseDescription, \r\n"
	+ $" employee.NAME as OPDoctorName, \r\n"
	+ $" case when SOURCEKINDCODEID = 0 then ENC.ADMITDATEON \r\n"
	+ $"   when SOURCEKINDCODEID = 1 and AuditKindCodeId = 1 and NoticeStatusCodeId = 1 then ENC.ADMITDATEON \r\n"
	+ $"   else null \r\n"
	+ $" end InDateTime, \r\n"
	+ $" NVL(ENC.ADMITDEPARTMENTID,-1) as DepartmentID, \r\n"
	+ $" NVL(ENC.SICKBEDORGANIZATIONID,-1) as CureDepartmentID, \r\n"
	+ $" NVL(ENC.SICKBEDID,-1) as SickBedNo, \r\n"
	+ $" NVL(enc.ADMITTERID,-1) as PreInRegisterEmployeeID, \r\n"
	+ $" case when SOURCEKINDCODEID = 1 then AdmitSaveTime.EFFECTIVESTARTTIME else null end AppendDateTime, \r\n"
	+ $" case when SOURCEKINDCODEID = 1 then ADMITDATEON  else null end PreInDateTime, \r\n"
	+ $" Department.name as DepartmentName, \r\n"
	+ $" CureDepartment.name as CureDepartmentName, \r\n"
	+ $" treatmentCard.Identifier as PatientCardNo, \r\n"
	+ $" medicalCertificate.Identifier as MedicalCertificateNo, \r\n"
	+ $" carteVital.Identifier as MedicareCardNo, \r\n"
	+ $" citizenCard.Identifier as CitizenCardNO, \r\n"
	+ $" IDNum.Identifier as IdentityCardNo \r\n"
	+ $" from entity.person person \r\n"
	+ $" left join prpa.encounter enc on person.personid = enc.personId \r\n"
	+ $" left join (select *from OBS.CLINICALDIAGNOSE where PARENTID = -1 and DIAGNOSEKINDCODE = 150) diagnose on enc.ENCOUNTERID = diagnose.ENCOUNTERID \r\n"
	+ $" left join concept.disease disease on disease.DISEASEID = diagnose.diseaseid \r\n"
	+ $" left join role.employee employee on employee.employeeid = diagnose.DIAGNOSEDOCTORID \r\n"
	+ $" left join(select* from entity.organization)Department on Department.organizationid = enc.ADMITDEPARTMENTID \r\n"
	+ $" left join(select* from entity.organization)CureDepartment on CureDepartment.organizationid = enc.SICKBEDORGANIZATIONID \r\n"
	+ $" left join(select* from  PRPA.ENCOUNTERATTRIBUTE where ATTRIBUTE= 'OverPreInDate') AdmitSaveTime on AdmitSaveTime.EncounterId = enc.EncounterId \r\n"
	+ $" left join(select* from entity.PersonIdentifier where IdentifierRootId = 5)treatmentCard on enc.PERSONID = treatmentCard.PERSONID \r\n"
	+ $" left join(select* from entity.PersonIdentifier where IdentifierRootId = 10) medicalCertificate on enc.PERSONID = medicalCertificate.PERSONID \r\n"
	+ $" left join(select* from entity.PersonIdentifier where IdentifierRootId = 7) carteVital on enc.PERSONID = carteVital.PERSONID \r\n"
	+ $" left join(select* from entity.PersonIdentifier where IdentifierRootId = 14) citizenCard on enc.PERSONID = citizenCard.PERSONID \r\n"
	+ $" left join(select* from entity.PersonIdentifier where IdentifierRootId = 1) IDNum on enc.PERSONID = IDNum.PERSONID \r\n"
	+ $" Where 1 = 1 and enc.isdeleted = 0 and diagnose.isdeleted = 0 {condictions}";

	using (DbConnection cn = DapperHelper.CrateConnection(Dbs.IP))
	{
		IList<AdmitInformQueryData> datas = DapperHelper.Query<AdmitInformQueryData>(cn, sql, new { admitDateFrom = para.AdmitDateFrom, admitDateTo = para.AdmitDateTo, appendDateTimeFrom = para.AppendDateTimeFrom, appendDateTimeTo = para.AppendDateTimeTo, condValue = para.CondValue }, 180).ToList();
		foreach (var row in datas)
		{
			item = new AdmitInformQuery_Result();
			item.InPatientID = row.InPatientID;
			item.IPSeqNoText = row.IPSeqNoText;
			item.PatientName = row.PatientName;
			item.SexFlag = row.SexFlag;
			item.DiseaseID = row.DiseaseID;
			item.InDateTime = row.InDateTime.HasValue ? row.InDateTime.Value : new DateTime();
			item.CureDepartmentID = row.CureDepartmentID;
			item.DepartmentID = row.DepartmentID;
			item.SickBedNo = row.SickBedNo;
			item.AppendDateTime = row.AppendDateTime.HasValue ? row.AppendDateTime.Value : new DateTime(); 
			item.OPDoctorID = row.OPDoctorID;
			item.DiagnoseDescription = row.DiagnoseDescription;
			item.PreInDateTime = row.PreInDateTime.HasValue ? row.PreInDateTime.Value : new DateTime();
			item.PreInRegisterEmployeeID = row.PreInRegisterEmployeeID;
			item.DiseaseName = row.DiseaseName;
			item.OPDoctorName = row.OPDoctorName;
			item.CureDepartmentName = row.CureDepartmentName;
			item.DepartmentName = row.DepartmentName;
			item.SoureKindCodeId = row.SoureKindCodeId;
			list.Add(item);
		}
	}

    [DataContract]
    public class EncounterInformQueryParameter
    {
        [DataMember]
        public DateTime AdmitDateFrom { get; set; }
        [DataMember]
        public DateTime AdmitDateTo { get; set; }
        [DataMember]
        public DateTime AppendDateTimeFrom { get; set; }
        [DataMember]
        public DateTime AppendDateTimeTo { get; set; }
        [DataMember]
        public string CondValue { get; set; }
    }

    public class AdmitInformQueryData
    {
        public DateTime? AppendDateTime { get; set; }
        public string DepartmentName { get; set; }
        public string CureDepartmentName { get; set; }
        public string OPDoctorName { get; set; }
        public string DiseaseName { get; set; }
        public int PreInRegisterEmployeeID { get; set; }
        public DateTime? PreInDateTime { get; set; }
        public string DiagnoseDescription { get; set; }
        public int OPDoctorID { get; set; }
        public int SoureKindCodeId { get; set; }
        public string SickBedNo { get; set; }
        public int DepartmentID { get; set; }
        public int CureDepartmentID { get; set; }
        public DateTime? InDateTime { get; set; }
        public int DiseaseID { get; set; }
        public int SexFlag { get; set; }
        public string PatientName { get; set; }
        public string IPSeqNoText { get; set; }
        public int InPatientID { get; set; }
    }