<AnyObject> 在 C# 中从数据库填充列表的最佳方法

Question

我有3个类继承自同一类，如

class S1:X{}
class S2:X{}
class S3:X{}

我需要编写从Sql数据库填充List<X>的方法。目前，我正在使用SqlDataReader进行填充。每个类有大约35个属性，数据库结果也有大约50K行。人口增长需要很长时间。我很好奇将大数据填充到List中的最佳方式。由于公司规定，我不能使用第三方软件包。有比SqlDataReader更快的方法吗？

编辑：

下面是修改后的代码示例来描述我正在尝试的内容。首先，也许我应该解释一些要点。SmartSqlReader继承自SqlDataReader，AutoMap方法是映射程序使用的反射。

using(SmartSqlReader reader = db.ExecuteReader(sp)) {
while (reader.Read()) {
bool isFlag1 = reader.GetBool("XX_TO_SEND");
bool isFlag2 = reader.GetBool("YY_TO_SEND");
bool isFlag3 = reader.GetBool("ZZ_TO_SEND");
if (!isFlag1 && !isFlag2 && !isFlag3) {
continue;
}
X x = new X() {
RecordId = reader.GetInt64("RECORD_ID"),
PropCxxx = reader.GetInt64("CXXX"),
PropCxxt = reader.GetInt32("CXXT"),
PropCxxsn = reader.GetString("CXXSN").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXSN"),
PropCxxn = RemoveDiacritics(reader.GetString("CXXSN").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXN").ToLower()),
PropCxxmn = reader.GetString("CXXSN2").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXSN2"),
PropCxxs = reader.GetString("CXXS").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXS"),
Language = reader.GetString("LANGUAGE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("LANGUAGE"),
PropSxxx = reader.GetString("SXXX").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("SXXX"),
MobilePhone1 = reader.GetString("MobilePhone1").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("MobilePhone1"),
MobilePhone2 = reader.GetString("MobilePhone2").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("MobilePhone2"),
Email1 = reader.GetString("EMAIL1").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("EMAIL1"),
Email2 = reader.GetString("EMAIL2").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("EMAIL2"),
Profile = reader.GetString("PROFILE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("PROFILE"),
IsPersonnel = reader.GetString("PROFILE") == "XX" ? true : false,
IsPrivateBn = reader.GetString("IsOB").IsNullOrEmptyOrFullSpace() ? false : reader.GetBool("IsOB"),
VIP = reader.GetInt32("VIP_FLAG"),
Gender = reader.GetString("GENDER").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("GENDER"),
BusinessLine = reader.GetString("BUSINESSLINE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("BUSINESSLINE"),
WorkPhone = reader.GetString("WORK_PHONE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("WORK_PHONE"),
HomePhone = reader.GetString("HOME_PHONE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("HOME_PHONE"),
CompanyName = reader.GetString("COMPANY_NAME").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("COMPANY_NAME"),
BranchName = reader.GetString("BRANCH_NAME").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("BRANCH_NAME"),
PfNxxx = reader.GetString("PFNXXX").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("PFNXXX"),
Rgxxx = reader.GetString("RGXXX").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("RGXXX"),
PCBN = reader.GetString("PCBN").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("PCBN"),
BPH = reader.GetString("BPH").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("BPH"),
TranValue = reader.GetString("TRAN_VALUE"),
TranReferenceId = reader.GetString("TRAN_REFERENCE_ID"),
TranReferenceDate = reader.GetDateTime("TRAN_REFERENCE_DATE"),
Amount = reader.GetDecimal("AMOUNT"),
...
DynamicFields = new DynamicFields {
PropCxxx = reader.GetInt64("CXXX"),
FIELD1 = reader.GetString("DYNAMIC_FIELD1"),
FIELD2 = reader.GetString("DYNAMIC_FIELD2"),
FIELD3 = reader.GetString("DYNAMIC_FIELD3"),
FIELD4 = reader.GetString("DYNAMIC_FIELD4"),
FIELD5 = reader.GetString("DYNAMIC_FIELD5"),
FIELD6 = reader.GetString("DYNAMIC_FIELD6"),
FIELD7 = reader.GetString("DYNAMIC_FIELD7"),
FIELD8 = reader.GetString("DYNAMIC_FIELD8"),
FIELD9 = reader.GetString("DYNAMIC_FIELD9"),
FIELD10 = reader.GetString("DYNAMIC_FIELD10"),
FIELD11 = reader.GetString("DYNAMIC_FIELD11"),
FIELD12 = reader.GetString("DYNAMIC_FIELD12"),
FIELD13 = reader.GetString("DYNAMIC_FIELD13"),
FIELD14 = reader.GetString("DYNAMIC_FIELD14"),
FIELD15 = reader.GetString("DYNAMIC_FIELD15")
},
CampaignCodeOrLink = reader.GetString("CAMPAIGN_CODE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CAMPAIGN_CODE"),
ListId = reader.GetInt64("LIST_ID")
};
x.ChannelType = isFlag1 ? Enums.Channel.C1 : isFlag2 ? Enums.Channel.C2 : Enums.Channel.C3;
if (x.ChannelType == Enums.Channel.C1) {
S1 s1 = CommonUtils.AutoMap <S1> (x);
s1.S1Prop = reader.GetString("S1Prop");
xList.Add(s1);
}
else if (x.ChannelType == Enums.Channel.C2) {
S1 s2 = CommonUtils.AutoMap <S2> (x);
s2.S2Prop = reader.GetString("S2Prop");
xList.Add(s2);
} else {
S3 s3 = CommonUtils.AutoMap <S3> (x);
s3.S3Prop = reader.GetString("S3Prop");
xList.Add(s3);
}
}
}

第二版：

我刚刚将对象初始化从X x = new X(){...}更改为

X x;
if(isFlag1)
{
x=new S1();
}

之后，80K行花费大约10s。太神奇了。总之，当我使用CommonUtils.AutoMap方法时，过程耗时约60米。当我使用第二种方法时，它也减少到约10秒。这让我很惊讶。

Answer 1

我刚刚更改了对象初始化方法，所以我删除了映射对象使用反射的CommonUtils.AutoMap。毕竟，80K行在~10s内处理，而不是~60m。这是最终代码。

using(SmartSqlReader reader = db.ExecuteReader(sp)) {
while (reader.Read()) {
bool isFlag1 = reader.GetBool("XX_TO_SEND");
bool isFlag2 = reader.GetBool("YY_TO_SEND");
bool isFlag3 = reader.GetBool("ZZ_TO_SEND");
if (!isFlag1 && !isFlag2 && !isFlag3) {
continue;
}
X x;
if (isFlag1) {
var s = new S1();
s.S1Prop = reader.GetString("S1Prop");
x = s;
} else if (isFlag2) {
var s = new S2();
s.S2Prop = reader.GetString("S2Prop");
x = s;
} else {
var s = new S3();
s.S3Prop = reader.GetString("S3Prop");
x = s;
}
x.RecordId = reader.GetInt64("RECORD_ID"),
x.PropCxxx = reader.GetInt64("CXXX"),
x.PropCxxt = reader.GetInt32("CXXT"),
...
x.DynamicFields = new DynamicFields {
FIELD1 = reader.GetString("DYNAMIC_FIELD1"),
FIELD2 = reader.GetString("DYNAMIC_FIELD2"),
FIELD3 = reader.GetString("DYNAMIC_FIELD3"),
FIELD4 = reader.GetString("DYNAMIC_FIELD4"),
FIELD5 = reader.GetString("DYNAMIC_FIELD5"),
FIELD6 = reader.GetString("DYNAMIC_FIELD6"),
FIELD7 = reader.GetString("DYNAMIC_FIELD7"),
FIELD8 = reader.GetString("DYNAMIC_FIELD8"),
FIELD9 = reader.GetString("DYNAMIC_FIELD9"),
FIELD10 = reader.GetString("DYNAMIC_FIELD10"),
FIELD11 = reader.GetString("DYNAMIC_FIELD11"),
FIELD12 = reader.GetString("DYNAMIC_FIELD12"),
FIELD13 = reader.GetString("DYNAMIC_FIELD13"),
FIELD14 = reader.GetString("DYNAMIC_FIELD14"),
FIELD15 = reader.GetString("DYNAMIC_FIELD15")
},
};
xList.Add(x);
}
}