I have 3 classes inherited from same class like
class S1:X{}
class S2:X{}
class S3:X{}
I need to write method to populate List<X> from Sql database. Currently, i am using SqlDataReader to populate. Each class has about 35 properties and db result also about 50K rows. Population is takes too long time. I am curious about the best way to populate large data into List. I am not able to use 3rd party packages because of corporation rules. Is there faster way than SqlDataReader?
Edit:
Modified code sample is below to describe what i am trying. Firstly, may be i should explain some points. SmartSqlReader is inherited from SqlDataReader, AutoMap method is mapper used Reflection.
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);
}
}
}
Second Edition:
I just changed object initialization from X x = new X(){...} to
X x;
if(isFlag1)
{
x=new S1();
}
and so on. After that, 80K rows took approximately 10s. It's amazing. In conclusion, when i used CommonUtils.AutoMap method process took ~60m also when i used the second method it decreased to ~10s. This surprised me a lot.
I just changed object initialization method, so i removed CommonUtils.AutoMap that map objects use reflection. After all, 80K rows processed in ~10s instead of ~60m. Here is final code.
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);
}
}