public void LoadToDb(FileInfo dbfFile, string TableName, SqlConnection connection)
{
NDbfReader.Table dbfTable = NDbfReader.Table.Open(dbfFile.Open(FileMode.Open));
NDbfReader.Reader dbfReader = dbfTable.OpenReader(Encoding.GetEncoding(866));
SqlTransaction TRA = connection.BeginTransaction(("Bulk" + TableName));
SqlBulkCopy da = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, TRA);
da.BulkCopyTimeout = 60000;
da.DestinationTableName = string.Concat(SchemaName, ".", TableName);
DataTable servertable = ds.Tables[TableName];
List <DataRow> rows = new List <DataRow>();
while (dbfReader.Read())
{
DataRow newrow = servertable.NewRow();
foreach (NDbfReader.Column c in dbfTable.Columns)
{
newrow.SetField(c.Name, dbfReader.GetValue(c.Name));
}
rows.Add(newrow);
}
da.WriteToServer(rows.ToArray());
TRA.Commit();
dbfTable.Dispose();
dbfFile.Delete();
}
public void loadDBFToDb_House(FileInfo dbfFile, SqlConnection connection)
{
NDbfReader.Table dbfTable = NDbfReader.Table.Open(dbfFile.Open(FileMode.Open));
NDbfReader.Reader dbfReader = dbfTable.OpenReader(System.Text.Encoding.GetEncoding(866));
QueriesTableAdapter qta = new QueriesTableAdapter();
int c = 1;
guidparse gp = (string x) => { if (x == null)
{
return(null);
}
else
{
return(Guid.Parse(x));
} };
while (dbfReader.Read())
{
Int32 ESTSTATUS;
Int32.TryParse(dbfReader.GetValue("ESTSTATUS").ToString(), out ESTSTATUS);
if (!(bool)qta.CanInsert_tmpHouse_Query(Guid.Parse(dbfReader.GetValue("HOUSEID").ToString())) &&
(bool)qta.CanInsert_tmpHouseAO_Query(Guid.Parse(dbfReader.GetValue("AOGUID").ToString()))
)
{
qta.Insert_tmpHouse_Query(
dbfReader.GetString("POSTALCODE"),
dbfReader.GetString("IFNSFL"),
dbfReader.GetString("TERRIFNSFL"),
dbfReader.GetString("IFNSUL"),
dbfReader.GetString("TERRIFNSUL"),
dbfReader.GetString("OKATO"),
dbfReader.GetString("OKTMO"),
dbfReader.GetDateTime("UPDATEDATE"),
dbfReader.GetString("HOUSENUM"),
Int32.Parse(dbfReader.GetValue("ESTSTATUS").ToString()),
dbfReader.GetString("BUILDNUM"),
dbfReader.GetString("STRUCNUM"),
Int32.Parse(dbfReader.GetValue("STRSTATUS").ToString()),
Guid.Parse(dbfReader.GetValue("HOUSEID").ToString()),
Guid.Parse(dbfReader.GetValue("HOUSEGUID").ToString()),
Guid.Parse(dbfReader.GetValue("AOGUID").ToString()),
dbfReader.GetDateTime("STARTDATE").Value,
dbfReader.GetDateTime("ENDDATE").Value,
Int32.Parse(dbfReader.GetValue("STATSTATUS").ToString()),
gp(dbfReader.GetString("NORMDOC")),
Int32.Parse(dbfReader.GetValue("COUNTER").ToString()),
dbfReader.GetString("CADNUM"),
Int32.Parse(dbfReader.GetValue("DIVTYPE").ToString()));
}
c++;
LogInfo(c.ToString() + ". " + Guid.Parse(dbfReader.GetValue("HOUSEID").ToString()));
}
dbfTable.Dispose();
dbfFile.Delete();
}
private void sbtnCheck_Click(object sender, EventArgs e)
{
List <CR_FlightInfo> flightdel = new List <CR_FlightInfo>();
string f_lbaytv2 = @"\\10.100.8.108\phanbay\doantv\ddtvvfp6\solieu\lbaytv2.dbf";
string f_des = @"c:\temp\lbaytv2.dbf";
DateTime ngaymin = new DateTime(2018, 09, 01);
DateTime ngaymax = new DateTime(2018, 09, 30);
List <lbaytv2> lbtv = new List <lbaytv2>();
try {
using (ERMSEntities1 db = new ERMSEntities1())
{
flightdel = db.CR_FlightInfo.Where(x => x.IsDeleted == true && x.Date >= ngaymin && x.Date <= ngaymax).ToList();
}
File.Copy(f_lbaytv2, f_des, true);
FileStream flbtv2 = new FileStream(f_des, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
NDbfReader.Table table = NDbfReader.Table.Open(flbtv2);
NDbfReader.Reader readder = table.OpenReader();
while (readder.Read())
{
string codenv = readder.GetString("CODE_TV");
DateTime?sdate = readder.GetDateTime("START_DATE");
DateTime?edate = readder.GetDateTime("END_DATE");
string loai = readder.GetString("LOAI");
string flyno = readder.GetString("FLY_NO");
string cfg = readder.GetString("CFG");
string from = readder.GetString("FROM_PLACE");
string to = readder.GetString("END_PLACE");
if (sdate == null || edate == null)
{
continue;
}
if (ngaymin > edate || ngaymax < sdate)
{
continue;
}
if (codenv == null) //Không có Code_tv ==> Bỏ qua
{
continue;
}
if (loai != "FLY" || flyno == null || flyno.Substring(0, 1) == "R") //Không phải bay hoặc dự bị bỏ qua
{
continue;
}
var cb = flightdel.Where(x => x.Date == sdate && x.FlightNo == flyno && x.Routing == from + "-" + to).FirstOrDefault();
if (cb != null)
{
lbaytv2 item = new lbaytv2();
item.flightID = cb.FlightID;
item.code_tv = codenv;
item.start_date = (DateTime)sdate;
item.end_date = (DateTime)edate;
item.start_time = readder.GetString("START_TIME");
item.end_time = readder.GetString("END_TIME");
item.fly_no = flyno;
item.from_place = from;
item.end_place = to;
item.cfg = cfg;
item.loai = loai;
item.note = readder.GetString("NOTE");
lbtv.Add(item);
} //if
} //while
flbtv2.Dispose();
if (flightdel.Count > 0) //Ghi kết quả
{
Worksheet spreadst = base.spreadsheetControl1.ActiveWorksheet;
spreadst.Cells[0, 0].Value = "STT";
spreadst.Cells[0, 1].Value = "Date";
spreadst.Cells[0, 2].Value = "Flyno";
spreadst.Cells[0, 3].Value = "Routing";
spreadst.Cells[0, 4].Value = "Codetv";
int stt = 1, dong = 1;
foreach (var cb in flightdel)
{
spreadst.Cells[dong, 0].Value = stt;
spreadst.Cells[dong, 1].Value = cb.Date;
spreadst.Cells[dong, 2].Value = cb.FlightNo;
spreadst.Cells[dong, 3].Value = cb.Routing;
var totv = lbtv.Where(x => x.flightID == cb.FlightID).ToList();
if (totv.Count > 0)
{
foreach (var tv in totv)
{
spreadst.Cells[dong, 4].Value = "'" + tv.code_tv;
dong++;
}
}
else
{
dong++;
}
stt++;
}
}
MessageBox.Show("Complete!");
}
catch (Exception ex) { }
}
private void sbtnCollectInfo_Click(object sender, EventArgs e)
{
string f_lbaytv2 = @"\\10.100.8.108\phanbay\doantv\ddtvvfp6\solieu\lbaytv2.dbf";
string f_des = @"c:\temp\lbaytv2.dbf";
List <CR_FlightInfo> dscb = new List <CR_FlightInfo>();
List <CR_FlightInfo> kqcb = new List <CR_FlightInfo>();
List <lbaytv2> ctlb = new List <lbaytv2>();
bool flag;
DateTime ngaymin = new DateTime(2018, 09, 01);
DateTime ngaymax = new DateTime(2018, 09, 30);
FileStream flbtv2 = null;
try {
using (ERMSEntities1 db = new ERMSEntities1())
{
dscb = db.CR_FlightInfo.Where(x => x.Date >= ngaymin && x.Date <= ngaymax && x.IsDeleted == true).ToList();
}
//File.Copy(f_lbaytv2, f_des, true);
flbtv2 = new FileStream(f_des, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
NDbfReader.Table table = NDbfReader.Table.Open(flbtv2);
NDbfReader.Reader readder = table.OpenReader();
while (readder.Read())
{
string codenv = readder.GetString("CODE_TV");
DateTime?sdate = readder.GetDateTime("START_DATE");
DateTime?edate = readder.GetDateTime("END_DATE");
string loai = readder.GetString("LOAI");
string flyno = readder.GetString("FLY_NO");
string cfg = readder.GetString("CFG");
string from = readder.GetString("FROM_PLACE");
string to = readder.GetString("END_PLACE");
if (sdate == null || edate == null)
{
continue;
}
if (ngaymin > edate || ngaymax < sdate)
{
continue;
}
if (codenv == null) //Không có Code_tv ==> Bỏ qua
{
continue;
}
lbaytv2 item = new lbaytv2();
item.code_fly = readder.GetString("CODE_FLY");
item.code_tv = codenv;
item.from_place = from;
item.end_place = to;
item.loai = loai;
item.cfg = cfg;
item.fly_no = flyno;
item.start_date = (DateTime)sdate;
item.end_date = (DateTime)edate;
item.start_time = readder.GetString("START_TIME");
item.end_time = readder.GetString("END_TIME");
item.job = readder.GetString("JOB");
item.note = readder.GetString("NOTE");
item.type_apl = readder.GetString("TYPE_APL");
item.acf = readder.GetString("ACF");
item.status = readder.GetString("STATUS");
ctlb.Add(item);
} //While
MessageBox.Show(ctlb.Count.ToString());
} //Try
catch (Exception ex) {
}
finally
{
flbtv2.Dispose();
}
}