السلام عليكم
اخوان انا عندي برنامج يقرا من ملف تكست ويستخرج منه الداتا ويضعها في الداتا بيس
المشكلة هو ان عندي انا بملف قاعدة البيانات 200 عمود
اما السجلات راح تكون بملف التكست وعددها تقريباً 400000
يوجد بطئ شديد في حالة ادخال البيانات لقاعدة البيانات بحيث البرنامج ياخذ تقرييباً ساعتين ونص لجل حفظ البيانات بالداتابيس
مع العلم اني استخدم مايكروسوفت اكسس
بينما في برنامج مصمم لكن لغة foxPro يقوم بنفس العمل لكن بأقل وقت بكثير
انا برنامجي ياخذ ساعتين والفوكس برو ياخذ تقريباً نص ساعة
هذا الكود تبعي واتمنى بالاخوان افادتي في تعديل او اي اضافة تساعد في سرعة البرنامج
myCom.CommandText = "INSERT INTO MasterDB (ORGX,ACCT,LOGO,SHRTNAME,CUSNO,ALTCUSFLG,ALTCUSNO,ACCTTYP,INTSTAT,CHOFFSTAT,CONDOFACCT,BLKCODE,BLKCODE2,CRDUSGFLG,AUTHSTAT,BILLCYC,STMTFRQ,SIORG,SINO,OWNBANKNO,OWNBRNO,OLCRESON,OWNERFLG,EMPCODE,DTAPPLI,DTOPND,DTBUREAU,DTBLKCODE,DTBLKCODE2,DTLSTCHNG,DTLSTDLQ,DTCLOSED,DTCGOFF,DTLSTPUR,MAKERDOB,COMKERDOB,CBCRSCORE,APPLCRSCOR,CRLIM,HIBAL,INCOME,NOUNCLKED,PMTCYCDUE,PMTCURDUE,PMTPASTCTR,PMTPASTDUE,PMT30DCTR,PMT30DYS,PMT60DCTR,PMT60DYS,PMT90DCTR,PMT90DYS,PMT120DCTR,PMT120DYS,PMT150DCTR,PMT150DYS,PMT180DCTR,PMT180DYS,PMT210DCTR,PMT210DYS,CURRBAL,OPNTOBUY,AMTYTDPUR,AMTYTDCASH,AMTYTDRETN,AMTYTDPMT,AMTCGOFF,CASHCGOFF,CHGOFFRSN1,CHGOFFRSN2,APSORG,APSTYPE,APSACCT,CASHBAL,CASHCRLMT,SECUR,CLLTRL,GRCDEXPDT,RELNO,CUSTORG,DTCRDEXP,PMTOTAMTDU,DTPMTDUE,AMTCTDPUR,PMTLSTAMT,DTLSTPMT,DTLSTSTMT,DTNXTSTMT,AGGRDYS,CRCLASS,WAIVCRDFEE,OWNRELFLG,PRFRPROMDT,PAYRMTMETD,PREVPMTMET,NORTNPMTS,RELBILLLVL,RELNOCRDFE,VIPSTAT,CORPIDNO,COMMRCLFLG,USRMSG1IND,USRMSG2IND,USRMSG3IND,DDPMTRCNTR,PMTCHNGDT,DDACTLPMT,DDAACNO,DDARTNGNO,SAVACNO,MKERHOMTEL,MKEREMPTEL,FXDPMTAMT,XFRACCT,DTCRDFEE,DTCRLIM,ORIGBRNO,DTLASTACT,DTLASTMAIN,PMTDLQDYS,USERCODE1,DTLSRACCR,NOYTDPUR,NOYTDCASH,YTDBILLINT,AMTYTDLCHG,USEACNO,FISCORE,FICRLIMSOR,PMTCTD,YTDCRDISFE,USRCODE2,USRCODE3,USRCODE4,USRDATE2,USRDATE3,USRDATE4,STATERESID,PREVBAL,NASTAT,DTMAINTN,DTADRCHNG,NASSANFLG,NASSANM,NAHTEL,NATELFLG,NAEMPTEL,NAPSTLCD,NAOWNRRESI,NALANGIND,NAMAILLIST,NANODEPEND,NMETYPIND1,NMETYPIND2,NMETYPIND3,NAMELINE1,NAMELINE2,NAMELINE3,NAADDR1,NAADDR2,NACITY,NASTATE,NATITLE,NACNTRYCD,NAPOSITN,NAMARITL,NADOB,NADLCNTRY,NADLSTATE,NAEMAIL,NASSANFLGC,NASSANC,NAHTELC,NATELFLGC,NAEMPTELC,NANMETYP1C,NANELIN1C,NADOBC,NAVIPSTAT,NASICCODE,NADUPCRNCY,NASTMTMSGM,NASTMTMSGC,NAEMPLOYER,NAUSRDO1,NAUSRDO2,NAUSER1,NAUSER2,NAUSER3,NAUSER4,NAUSER13,NAMOBILPH,D2NAEMAIL,FSEARNED,FSDISB,FSADJ,FSPGMBAL,FSBEGBAL,DTEXTR,DELMTR) VALUES (ORGX,@ACCT,@LOGO,@SHRTNAME,@CUSNO,@ALTCUSFLG,@ALTCUSNO,@ACCTTYP,@INTSTAT,@CHOFFSTAT,@CONDOFACCT,@BLKCODE,@BLKCODE2,@CRDUSGFLG,@AUTHSTAT,@BILLCYC,@STMTFRQ,@SIORG,@SINO,@OWNBANKNO,@OWNBRNO,@OLCRESON,@OWNERFLG,@EMPCODE,@DTAPPLI,@DTOPND,@DTBUREAU,@DTBLKCODE,@DTBLKCODE2,@DTLSTCHNG,@DTLSTDLQ,@DTCLOSED,@DTCGOFF,@DTLSTPUR,@MAKERDOB,@COMKERDOB,@CBCRSCORE,@APPLCRSCOR,@CRLIM,@HIBAL,@INCOME,@NOUNCLKED,@PMTCYCDUE,@PMTCURDUE,@PMTPASTCTR,@PMTPASTDUE,@PMT30DCTR,@PMT30DYS,@PMT60DCTR,@PMT60DYS,@PMT90DCTR,@PMT90DYS,@PMT120DCTR,@PMT120DYS,@PMT150DCTR,@PMT150DYS,@PMT180DCTR,@PMT180DYS,@PMT210DCTR,@PMT210DYS,@CURRBAL,@OPNTOBUY,@AMTYTDPUR,@AMTYTDCASH,@AMTYTDRETN,@AMTYTDPMT,@AMTCGOFF,@CASHCGOFF,@CHGOFFRSN1,@CHGOFFRSN2,@APSORG,@APSTYPE,@APSACCT,@CASHBAL,@CASHCRLMT,@SECUR,@CLLTRL,@GRCDEXPDT,@RELNO,@CUSTORG,@DTCRDEXP,@PMTOTAMTDU,@DTPMTDUE,@AMTCTDPUR,@PMTLSTAMT,@DTLSTPMT,@DTLSTSTMT,@DTNXTSTMT,@AGGRDYS,@CRCLASS,@WAIVCRDFEE,@OWNRELFLG,@PRFRPROMDT,@PAYRMTMETD,@PREVPMTMET,@NORTNPMTS,@RELBILLLVL,@RELNOCRDFE,@VIPSTAT,@CORPIDNO,@COMMRCLFLG,@USRMSG1IND,@USRMSG2IND,@USRMSG3IND,@DDPMTRCNTR,@PMTCHNGDT,@DDACTLPMT,@DDAACNO,@DDARTNGNO,@SAVACNO,@MKERHOMTEL,@MKEREMPTEL,@FXDPMTAMT,@XFRACCT,@DTCRDFEE,@DTCRLIM,@ORIGBRNO,@DTLASTACT,@DTLASTMAIN,@PMTDLQDYS,@USERCODE1,@DTLSRACCR,@NOYTDPUR,@NOYTDCASH,@YTDBILLINT,@AMTYTDLCHG,@USEACNO,@FISCORE,@FICRLIMSOR,@PMTCTD,@YTDCRDISFE,@USRCODE2,@USRCODE3,@USRCODE4,@USRDATE2,@USRDATE3,@USRDATE4,@STATERESID,@PREVBAL,@NASTAT,@DTMAINTN,@DTADRCHNG,@NASSANFLG,@NASSANM,@NAHTEL,@NATELFLG,@NAEMPTEL,@NAPSTLCD,@NAOWNRRESI,@NALANGIND,@NAMAILLIST,@NANODEPEND,@NMETYPIND1,@NMETYPIND2,@NMETYPIND3,@NAMELINE1,@NAMELINE2,@NAMELINE3,@NAADDR1,@NAADDR2,@NACITY,@NASTATE,@NATITLE,@NACNTRYCD,@NAPOSITN,@NAMARITL,@NADOB,@NADLCNTRY,@NADLSTATE,@NAEMAIL,@NASSANFLGC,@NASSANC,@NAHTELC,@NATELFLGC,@NAEMPTELC,@NANMETYP1C,@NANELIN1C,@NADOBC,@NAVIPSTAT,@NASICCODE,@NADUPCRNCY,@NASTMTMSGM,@NASTMTMSGC,@NAEMPLOYER,@NAUSRDO1,@NAUSRDO2,@NAUSER1,@NAUSER2,@NAUSER3,@NAUSER4,@NAUSER13,@NAMOBILPH,@D2NAEMAIL,@FSEARNED,@FSDISB,@FSADJ,@FSPGMBAL,@FSBEGBAL,@DTEXTR,@DELMTR)";
try //catch any error if it shown
{
myCon.Open();
string start = DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second;
while ((input = sReader.ReadLine()) != null) //Extract the data from the file line by line by its index.
{
ORGX = input.Substring(0, 3);
ACCT = input.Substring(3, 19);
LOGO = input.Substring(22, 3);
SHRTNAME = input.Substring(25, 20);
CUSNO = input.Substring(45, 19);
ALTCUSFLG = input.Substring(64, 1);
ALTCUSNO = input.Substring(65, 19);
ACCTTYP = input.Substring(84, 1);
INTSTAT = input.Substring(85, 1);
CHOFFSTAT = input.Substring(86, 1);
CONDOFACCT = input.Substring(87, 1);
BLKCODE = input.Substring(88, 1);
BLKCODE2 = input.Substring(89, 1);
CRDUSGFLG = input.Substring(90, 1);
AUTHSTAT = input.Substring(91, 1);
BILLCYC = input.Substring(92, 2);
STMTFRQ = input.Substring(94, 2);
SIORG = input.Substring(96, 3);
SINO = input.Substring(99, 9);
OWNBANKNO = input.Substring(108, 5);
OWNBRNO = input.Substring(113, 9);
OLCRESON = input.Substring(122, 2);
OWNERFLG = input.Substring(124, 1);
EMPCODE = input.Substring(125, 2);
DTAPPLI = input.Substring(127, 8);
DTOPND = input.Substring(135, 8);
DTBUREAU = input.Substring(143, 8);
DTBLKCODE = input.Substring(151, 8);
DTBLKCODE2 = input.Substring(159, 8);
DTLSTCHNG = input.Substring(167, 8);
DTLSTDLQ = input.Substring(175, 8);
DTCLOSED = input.Substring(183, 8);
DTCGOFF = input.Substring(191, 8);
DTLSTPUR = input.Substring(199, 8);
myCom.Parameters.Clear(); //clear the parameters for the next trip.
//add the value into the prameter and send it to the database.
myCom.Parameters.AddWithValue("@ORGX ", ORGX.Trim());
myCom.Parameters.AddWithValue("@ACCT ", ACCT.Trim());
myCom.Parameters.AddWithValue("@LOGO ", LOGO.Trim());
myCom.Parameters.AddWithValue("@SHRTNAME ", SHRTNAME.Trim());
myCom.Parameters.AddWithValue("@CUSNO ", CUSNO.Trim());
myCom.Parameters.AddWithValue("@ALTCUSFLG ", ALTCUSFLG.Trim());
myCom.Parameters.AddWithValue("@ALTCUSNO ", ALTCUSNO.Trim());
myCom.Parameters.AddWithValue("@ACCTTYP ", ACCTTYP.Trim());
myCom.Parameters.AddWithValue("@INTSTAT ", INTSTAT.Trim());
myCom.Parameters.AddWithValue("@CHOFFSTAT ", CHOFFSTAT.Trim());
myCom.Parameters.AddWithValue("@CONDOFACCT ", CONDOFACCT.Trim());
myCom.Parameters.AddWithValue("@BLKCODE ", BLKCODE.Trim());
myCom.Parameters.AddWithValue("@BLKCODE2 ", BLKCODE2.Trim());
myCom.Parameters.AddWithValue("@CRDUSGFLG ", CRDUSGFLG.Trim());
myCom.Parameters.AddWithValue("@AUTHSTAT ", AUTHSTAT.Trim());
myCom.Parameters.AddWithValue("@BILLCYC ", BILLCYC.Trim());
myCom.Parameters.AddWithValue("@STMTFRQ ", STMTFRQ.Trim());
myCom.Parameters.AddWithValue("@SIORG ", SIORG.Trim());
myCom.Parameters.AddWithValue("@SINO ", SINO.Trim());
myCom.Parameters.AddWithValue("@OWNBANKNO ", OWNBANKNO.Trim());
myCom.Parameters.AddWithValue("@OWNBRNO ", OWNBRNO.Trim());
myCom.Parameters.AddWithValue("@OLCRESON ", OLCRESON.Trim());
myCom.Parameters.AddWithValue("@OWNERFLG ", OWNERFLG.Trim());
myCom.Parameters.AddWithValue("@EMPCODE ", EMPCODE.Trim());
myCom.Parameters.AddWithValue("@DTAPPLI ", DTAPPLI.Trim());
myCom.Parameters.AddWithValue("@DTOPND ", DTOPND.Trim());
myCom.Parameters.AddWithValue("@DTBUREAU ", DTBUREAU.Trim());
myCom.Parameters.AddWithValue("@DTBLKCODE ", DTBLKCODE.Trim());
myCom.Parameters.AddWithValue("@DTBLKCODE2 ", DTBLKCODE2.Trim());
myCom.Parameters.AddWithValue("@DTLSTCHNG ", DTLSTCHNG.Trim());
myCom.Parameters.AddWithValue("@DTLSTDLQ ", DTLSTDLQ.Trim());
myCom.Parameters.AddWithValue("@DTCLOSED ", DTCLOSED.Trim());
myCom.Parameters.AddWithValue("@DTCGOFF ", DTCGOFF.Trim());
myCom.Parameters.AddWithValue("@DTLSTPUR ", DTLSTPUR.Trim());
count++;
myCom.ExecuteNonQuery(); //Insert data to the database
lblcount.Text = "copy record No. " + count;
}
MessageBox.Show("There are " + count + " record inserted.", "Information Message",MessageBoxButtons.OK,MessageBoxIcon.Information);اتمنى المساعده وللعلم الاعمدة اللي فوق ربع الاعمدة اللي بالداتابيس عدد جميع الاعمدة 200
ومشكورين مقدماً