DapperHelper.ExecuteSQL方法执行语句块写法：
PS：BEGIN和END里面的语句需要分号（;）结尾
bool UpdateSkinTestOrderRelationShip(int orderId, int skinTestOrderId)
{
StringBuilder sb = new StringBuilder();
IList<OracleParameter> paras = new List<OracleParameter>();
using (var cn = DapperHelper.CrateConnection(Dbs.IP))
{
sb.AppendLine("BEGIN");
sb.AppendLine("update poor.subadmrequest set SKINTESTORDERREQUESTID = :sId where orderrequestid = :oId;");
sb.AppendLine("update poor.subadmrequest set SKINTESTSOURCEID = :oId where orderrequestid = :sId;");
sb.Append("END;");
int ret = DapperHelper.Execute(cn, sb.ToString(), new { oId = orderId, sId = skinTestOrderId });
return ret == -1;
}
}[TestMethod]
public void TestB()
{
StringBuilder sb = new StringBuilder();
IList<OracleParameter> paras = new List<OracleParameter>();
using (var cn = DapperHelper.CrateConnection(Dbs.IP))
{
sb.AppendLine("BEGIN");
int idx = 0;
IList<JMNEW_3103_IN_WARN> warns = new List<JMNEW_3103_IN_WARN>();
JMNEW_3103_IN_WARN warn1 = new JMNEW_3103_IN_WARN { warn_rslt_id = "1111", dspo_way = "22222", dspo_way_rea = "测试abaaa" };
JMNEW_3103_IN_WARN warn2 = new JMNEW_3103_IN_WARN { warn_rslt_id = "55555", dspo_way = "6666", dspo_way_rea = "测试aba11111aa" };
warns.Add(warn1);
warns.Add(warn2);
foreach (var item in warns)
{
idx = idx + 1;
sb.AppendLine($"insert into MC.JMNEW_3103_IN_WARNS(ENCOUNTERID, warn_rslt_id, dspo_way, dspo_way_rea, domainsystemkey) values(:p{idx}1, :p{idx}2, :p{idx}3, :p{idx}4, :p{idx}5); ");
paras.Add(new OracleParameter($"@p{idx}1", OracleDbType.Int32, 1000, ParameterDirection.Input));
paras.Add(new OracleParameter($"@p{idx}2", OracleDbType.Varchar2, item.warn_rslt_id, ParameterDirection.Input));
paras.Add(new OracleParameter($"@p{idx}3", OracleDbType.Varchar2, item.dspo_way, ParameterDirection.Input));
paras.Add(new OracleParameter($"@p{idx}4", OracleDbType.NVarchar2, item.dspo_way_rea, ParameterDirection.Input));
paras.Add(new OracleParameter($"@p{idx}5", OracleDbType.Varchar2, "opdocor", ParameterDirection.Input));
}
sb.Append("END;");
int ret = DapperHelper.ExecuteSql(cn, sb.ToString(), paras.ToArray());
}
} [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; }
}
浙公网安备 33010602011771号