使用存储过程和反射获取从多个数据表所需信息

本文介绍如何使用存储过程和读取范式化数据库获取所需信息的技术，使用到的技术有：存储过程，反射。此技巧适用于与多处连表（大量JOIN）的场景。 对于单表操作，建议使用UnitOfWork+Repository的设计模式的EF操作方法，该方法并非本文描述重点。

关键字： 存储过程；反射；范式；连表；

场景：需要读取收据信息

收据Model  DTO定义如下

public class ApplicantExamReceiptModel
    {
        public ApplicantExamReceiptModel()
        {
            //Claims = new List<ClaimModel>();            
        }

        public PaymentTypeEnum PaymentTypeMethod { get; set; }

        public int ApplicantExamId { get; set; }

        public string FirstName { get; set; }

        public string MiddleName { get; set; }

        public string LastName { get; set; }

        public string ExamNumber { get; set; }

        public string ExamTitle { get; set; }

        public string PhoneNumber { get; set; }

        public string Email { get; set; }

        public string AddressLine { get; set; }

        public string CityTown { get; set; }

        public string StateProvince { get; set; }

        public string PostalCode { get; set; }

        public string Country { get; set; }

        public DateTime? BirtDate { get; set; }

        public string BirtDateStr { get; set; }

        public string Gender { get; set; }

        public List<ApplicantClaimModel> ApplicantClaims { get; set; }

        public string RecruitmentSource { get; set; }

        public DateTime? PaymentStatusDate { get; set; }

        public DateTime? PaidOn { get; set; }

        public string PaymentStatusDateString { get; set; }

        public string ExamPaymentMethod { get; set; }

        public int ExamPaymentMethodId { get; set; }

        public string PaymentStatus { get; set; }

        public string PaymentDescriptor { get; set; }

        public decimal? ExamFee { get; set; }

        public string IsHispanicLatino { get; set; }

        public string Ethnicity { get; set; }

        public string PaymentReference { get; set; }

        public bool? IsHistorical { get; set; }

}

信息源表截图如下:

该表中，由于使用了部分外键。以Gender为例，字段中存储1,2， 实际内容存储在Gender表中，需要链表读取

存储过程如下：

USE [OASysLite]
GO
/****** Object:  StoredProcedure [Applicant].[usp_GetApplicantExamInfo]    Script Date: 3/15/2017 11:55:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Applicant].[usp_GetApplicantExamInfo]
	@applicantexamid int, @requestid int
AS

Begin 
	if(@requestid=1)-- get all receipt info for specific applicant exam 
	  BEGIN
	      SELECT AED.ApplicantExamId, AED.FirstName,AED.MiddleName,AED.LastName, AED.PhoneNumber,AED.Email,
		  AED.AddressLine, AED.CityTown, AED.StateProvince, AED.StateProvince, AED.PostalCode, AED.Country,
		  AED.BirthDate,CG.Name [Gender], AED.HispanicLatinoId,EE.ExamNumber, EE.Title [ExamTitle],CR.Name [RecruitmentSource],
		  PM.Name [ExamPaymentMethod],PM.PaymentMethodId [ExamPaymentMethodId],AP.ExamFee,AP.PaidOn,AP.PaymentReference,
		  HL.Name [IsHispanicLatino], CE.Name [Ethnicity]
		  FROM [OASysLite].[Applicant].[ApplicantExamDemographic] AED
		  LEFT JOIN [OASysLite].[Config].[Gender] CG ON AED.GenderId = CG.GenderId
		  LEFT JOIN [OASysLite].[Applicant].[ApplicantExam] AE ON AE.ApplicantExamId = AED.ApplicantExamId
		  LEFT JOIN [OASysLite].[ExamSetup].[Exam] EE ON EE.ExamId = AE.ExamId
		  LEFT JOIN [OASysLite].[Applicant].[ApplicantPayment] AP ON AP.ApplicantExamId = AED.ApplicantExamId
		  LEFT JOIN [OASysLite].[ExamSetup].[ExamPaymentMethod] EPM ON EPM.ExamPaymentMethodId = AP.ExamPaymentMethodId
		  LEFT JOIN [OASysLite].[Config].[PaymentMethod] PM ON PM.PaymentMethodId = EPM.PaymentMethodId
		  LEFT JOIN [OASysLite].[Config].[PaymentTransactionFee] CPTF ON PM.PaymentMethodId=CPTF.PaymentMethodId
		  LEFT JOIN [OASysLite].[Config].[HispanicLatino] HL ON HL.HispanicLatinoId=AED.HispanicLatinoId
		  LEFT JOIN [OASysLite].[Config].[Ethnicity] CE ON CE.EthnicityId=AED.EthnicityId
		  LEFT JOIN [Config].[RecruitmentSource] CR ON CR.RecruitmentSourceId = AE.RecruitmentSourceId
		  WHERE AE.ApplicantExamId = @applicantexamid
	  END

	ELSE IF(@requestid = 4) -- get claims
		BEGIN
			SELECT AC.ApplicantClaimId, AC.ApplicantExamId, AC.ProfileId, AC.ClaimTypeId,AC.IsConfirmed, CT.Name
			FROM [OASysLite].[Applicant].[ApplicantClaim] AC
			LEFT JOIN [OASysLite].[Config].[ClaimType] CT ON CT.ClaimTypeId = AC.ClaimTypeId
			WHERE AC.ApplicantExamId = @applicantexamid
		END

	ELSE IF(@requestid = 6) -- QIE Appeal Document
		BEGIN
			SELECT OAAED.ApplicantExamId,OAAED.FirstName,OAAED.MiddleName,OAAED.LastName, OAAED.AddressLine,OEE.ExamNumber,
			OEE.Title [ExamTitle], OAP.SSN
			FROM [OASysLite].[Applicant].[ApplicantExamDemographic] OAAED
			LEFT JOIN [OASysLite].[Applicant].[ApplicantExam] OAA ON OAA.ApplicantExamID=OAAED.ApplicantExamID
			LEFT JOIN [OASysLite].[ExamSetup].[Exam] OEE ON OEE.ExamId=OAA.ExamId
			LEFT JOIN [OASysLite].[Applicant].[Profile] OAP ON OAA.ProfileId = OAP.ProfileId
			WHERE OAA.ApplicantExamId = @applicantexamid
		END

	else if(@requestid=7)--QIE QualificationOption
	    BEGIN
			   SELECT AQO.ApplicantExamId,OEEQO.ExamPartSectionId,OEEQO.ExamQualificationOptionId,OEEQO.Name,AQO.ApplicantAnswer [IsChecked]
			   FROM [OASysLite].[ExamSetup].[ExamQualificationOption] OEEQO
			   join ApplicantQIE.ApplicantExamQualificationOption AQO ON AQO.ExamQualificationOptionId=OEEQO.ExamQualificationOptionId
			   WHERE ExamPartSectionId in  (select distinct(oeeps.ExamPartSectionId)
			   from [OASysLite].[Applicant].[ApplicantExam] oaa
			   left join [OASysLite].[ExamSetup].[ExamPart] oee on oee.ExamId = oaa.ExamId
			   left join [OASysLite].[ExamSetup].[ExamPartSection] oeeps on oeeps.ExamPartId=oee.ExamPartId
			   where oaa.ApplicantExamId=@applicantexamid)
		END

	else if(@requestid=8)--QIE QualificationOption
	    BEGIN
				SELECT  OEE.ExamCityEmploymentId,OEE.ExamPartSectionId,OEE.Name,ACE.ApplicantAnswer [IsChecked]
				FROM [OASysLite].[ExamSetup].[ExamCityEmployment] OEE
				join ApplicantQIE.ApplicantExamCityEmployment ACE ON ACE.ExamCityEmploymentId=OEE.ExamCityEmploymentId
				WHERE ExamPartSectionId in  (select distinct(oeeps.ExamPartSectionId)
				from [OASysLite].[Applicant].[ApplicantExam] oaa
				left join [OASysLite].[ExamSetup].[ExamPart] oee on oee.ExamId = oaa.ExamId
				left join [OASysLite].[ExamSetup].[ExamPartSection] oeeps on oeeps.ExamPartId=oee.ExamPartId
				where oaa.ApplicantExamId=@applicantexamid)
		END
END

该存储过程中，@requestid=1时为本案例需要关注的内容。执行该存储过程，结果如下图所示

注意事项：  存储过程选出来的结果必须与DTO Model中的字段名称保持一致；

 

C#代码

存储过程完成了，接下来使用代码来读取该内容以获取到存储过程执行结果。

private List<T> ExecuteCommand<T>(int applicantexamid,  int requestid = 0) where T : new()
        {
            List<T> res = new List<T>();

            using (UnitOfWork unitOfWork = new UnitOfWork())
            {
                SqlConnection dbcon = new SqlConnection(unitOfWork.Context.Database.Connection.ConnectionString);
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = dbcon;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "[Applicant].[usp_GetApplicantExamInfo]";
                    cmd.Parameters.AddWithValue("@applicantexamid", applicantexamid);
                    cmd.Parameters.AddWithValue("@requestid", requestid);

                    dbcon.Open();

                    SqlDataReader oDr = cmd.ExecuteReader();
                    while (oDr.Read())
                    {
                        T t = new T();

                        for (int inc = 0; inc < oDr.FieldCount; inc++)
                        {
                            Type type = t.GetType();
                            PropertyInfo prop = type.GetProperty(oDr.GetName(inc));
                            if (prop != null)
                            {
                                var val = oDr.GetValue(inc);
                                if (val != null && !val.ToString().Equals(""))
                                {
                                    var targetType = prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))
                                        ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType;
                                    val = Convert.ChangeType(val, targetType);
                                    prop.SetValue(t, val, null);
                                }
                            }
                        }
                        res.Add(t);
                    }

                    oDr.Close();
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    dbcon.Close();
                }
            }

            return res;
        }

使用代码如下：

ApplicantExamReceiptModel oResult = new ApplicantExamReceiptModel();

            var ApplicantExamReceiptModelList = ExecuteCommand<ApplicantExamReceiptModel>(applicantExamId,  (int)ApplicantExamRequestType.Receipt);

小结

至此，就已经从多个表格获得了所需的信息。任何方法都有适用场景和非适用场景，本方法适用于多处连表，并且需要将数据读取出来并显示到界面的操作。