private bool CHSChartInDB(string ChartName, int TotalRowCount)
{
using (BathymetryEntities be = new BathymetryEntities())
{
// checking if chart already exist
CHSChart chsChartExist = (from c in be.CHSCharts
where c.CHSChartName == ChartName
select c).FirstOrDefault();
if (chsChartExist != null)
{
int CountOfCHSDepth = (from d in be.CHSDepths
where d.CHSChartID == chsChartExist.CHSChartID
select d).Count();
if (CountOfCHSDepth == TotalRowCount)
{
richTextBoxResults.AppendText("Chart [" + ChartName + "] already loaded.\r\n");
Application.DoEvents();
return(true);
}
be.CHSCharts.Remove(chsChartExist);
try
{
be.SaveChanges();
}
catch (Exception ex)
{
richTextBoxResults.AppendText("Error while trying to delete [" + ChartName + "]\r\n");
richTextBoxResults.AppendText("Error message [" + ex.Message + "]\r\n");
}
}
}
return(false);
}
private void Excecute(bool SaveInDB, bool ShowResInRTB, int NumberOfFile, bool DoLine, bool DoSound, int NumberOfRecordBatchSave)
{
StringBuilder sb = new StringBuilder();
int DoNumbRows = 100000000;
int CountFile = 0;
DateTime dtFileAfterDate;
if (!DateTime.TryParse(textBoxFileAfterDate.Text, out dtFileAfterDate))
{
MessageBox.Show("Wrong date format");
return;
}
foreach (FileInfo f in new DirectoryInfo(textBoxDbfFilesDirPath.Text).GetFiles().Where(d => d.CreationTime > dtFileAfterDate))
{
richTextBoxResults.AppendText("Loading [" + f.Name.Substring(0, f.Name.Length - 4) + "].\r\n");
Application.DoEvents();
double LongitudeMin = 180;
double LongitudeMax = -180;
double LatitudeMin = 90;
double LatitudeMax = -90;
lblCurrentFile.Text = f.Name;
lblCurrentFile.Refresh();
Application.DoEvents();
if (f.Name.Contains("SOUNDG"))
{
if (!DoSound)
{
continue;
}
FileInfo fi = new FileInfo(f.FullName);
MemoryStream ms = FileToMemoryStream(f.FullName);
byte[] ByteArray = ms.ToArray();
int pos = 0;
Int32 TotalRowCount = BitConverter.ToInt32(ByteArray, 4);
CHSChart chsChart = new CHSChart();
chsChart.CHSChartName = f.Name.Substring(0, f.Name.Length - 4);
if (CHSChartInDB(chsChart.CHSChartName, TotalRowCount))
{
continue;
}
CountFile += 1;
if (CountFile > NumberOfFile)
{
richTextBoxResults.AppendText("Maximum number of file done ...");
return;
}
chsChart.CHSFileName = f.Name;
if (SaveInDB)
{
using (BathymetryEntities be = new BathymetryEntities())
{
be.CHSCharts.Add(chsChart);
try
{
be.SaveChanges();
}
catch (Exception ex)
{
MessageBox.Show("Error Message [" + ex.Message + "]");
}
}
}
sb.AppendLine();
pos = 32;
sb.Append(ASCIIEncoding.ASCII.GetString(ByteArray, pos, 7) + "\t");
pos = 64;
sb.Append(ASCIIEncoding.ASCII.GetString(ByteArray, pos, 7) + "\t");
pos = 96;
sb.Append(ASCIIEncoding.ASCII.GetString(ByteArray, pos, 7) + "\t");
pos = 130;
sb.AppendLine();
int CurrentRow = 0;
List <CHSDepth> cdListToAdd = new List <CHSDepth>();
while (pos < ByteArray.Length)
{
CurrentRow += 1;
if (CurrentRow > DoNumbRows)
{
break;
}
lblStatusTxt.Text = "Reading row " + CurrentRow + " of " + TotalRowCount;
lblStatusTxt.Refresh();
Application.DoEvents();
CHSDepth cd = new CHSDepth();
cd.CHSChartID = chsChart.CHSChartID;
cd.Longitude = double.Parse(ASCIIEncoding.ASCII.GetString(ByteArray, pos, 19));
pos += 19;
cd.Latitude = double.Parse(ASCIIEncoding.ASCII.GetString(ByteArray, pos, 19));
pos += 19;
cd.Depth = double.Parse(ASCIIEncoding.ASCII.GetString(ByteArray, pos, 19));
pos += 19;
pos += 1;
cd.LineValue = -999;
if (LongitudeMin > cd.Longitude)
{
LongitudeMin = (double)cd.Longitude;
}
if (LongitudeMax < cd.Longitude)
{
LongitudeMax = (double)cd.Longitude;
}
if (LatitudeMin > cd.Latitude)
{
LatitudeMin = (double)cd.Latitude;
}
if (LatitudeMax < cd.Latitude)
{
LatitudeMax = (double)cd.Latitude;
}
cdListToAdd.Add(cd);
// this will save at every NumberOfRecordBatchSave depth
if (CurrentRow % NumberOfRecordBatchSave == 0)
{
lblStatusTxt.Text = "Reading row " + CurrentRow + " of " + TotalRowCount + " --- Transfering " + NumberOfRecordBatchSave.ToString() + " rows to SQL DB";
lblStatusTxt.Refresh();
Application.DoEvents();
chsChart.LongitudeMax = LongitudeMax;
chsChart.LongitudeMin = LongitudeMin;
chsChart.LatitudeMax = LatitudeMax;
chsChart.LatitudeMin = LatitudeMin;
using (BathymetryEntities be = new BathymetryEntities())
{
be.CHSDepths.AddRange(cdListToAdd);
be.SaveChanges();
}
cdListToAdd = new List <CHSDepth>();
}
sb.Append(cd.Longitude + "\t");
sb.Append(cd.Latitude + "\t");
sb.Append(cd.Depth + "\t");
sb.AppendLine();
}
lblStatusTxt.Text = "Saving last records";
lblStatusTxt.Refresh();
Application.DoEvents();
chsChart.LongitudeMax = LongitudeMax;
chsChart.LongitudeMin = LongitudeMin;
chsChart.LatitudeMax = LatitudeMax;
chsChart.LatitudeMin = LatitudeMin;
using (BathymetryEntities be = new BathymetryEntities())
{
be.CHSDepths.AddRange(cdListToAdd);
be.SaveChanges();
}
lblStatusTxt.Text = "Done ...";
lblStatusTxt.Refresh();
Application.DoEvents();
if (ShowResInRTB)
{
richTextBoxResults.Text = sb.ToString();
}
if (NumberOfFile == CountFile)
{
break;
}
}
else
{
if (!DoLine)
{
continue;
}
OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\CSSP\CHS Bathymetry\File_dbf;Extended Properties=dBase III");
OleDbCommand command = new OleDbCommand("select * from " + f.Name, oConn);
oConn.Open();
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
oConn.Close(); //close connection to the .dbf file
//create a reader for the datatable
DataTableReader reader = dt.CreateDataReader();
CHSChart chsChart = new CHSChart();
chsChart.CHSChartName = f.Name.Substring(0, f.Name.Length - 4);
if (CHSChartInDB(chsChart.CHSChartName, dt.Rows.Count))
{
continue;
}
CountFile += 1;
if (CountFile > NumberOfFile)
{
richTextBoxResults.AppendText("Maximum number of file done ...");
return;
}
chsChart.CHSFileName = f.Name;
if (SaveInDB)
{
using (BathymetryEntities be = new BathymetryEntities())
{
be.CHSCharts.Add(chsChart);
try
{
be.SaveChanges();
}
catch (Exception ex)
{
MessageBox.Show("Error Message [" + ex.Message + "]");
}
}
}
sb.AppendLine();
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append(dt.Columns[i].ColumnName + "\t");
}
sb.AppendLine();
List <CHSDepth> cdListToAdd = new List <CHSDepth>();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i > DoNumbRows)
{
break;
}
lblStatusTxt.Text = "Reading row " + i.ToString() + " of " + dt.Rows.Count;
lblStatusTxt.Refresh();
Application.DoEvents();
CHSDepth cd = new CHSDepth();
cd.CHSChartID = chsChart.CHSChartID;
cd.LineValue = (double)dt.Rows[i].ItemArray[0];
cd.Depth = (double)dt.Rows[i].ItemArray[1];
cd.Longitude = (double)dt.Rows[i].ItemArray[2];
cd.Latitude = (double)dt.Rows[i].ItemArray[3];
if (LongitudeMin > cd.Longitude)
{
LongitudeMin = (double)cd.Longitude;
}
if (LongitudeMax < cd.Longitude)
{
LongitudeMax = (double)cd.Longitude;
}
if (LatitudeMin > cd.Latitude)
{
LatitudeMin = (double)cd.Latitude;
}
if (LatitudeMax < cd.Latitude)
{
LatitudeMax = (double)cd.Latitude;
}
cdListToAdd.Add(cd);
// this will save at every NumberOfRecordBatchSave depth
if (i % NumberOfRecordBatchSave == 0)
{
lblStatusTxt.Text = "Reading row " + i.ToString() + " of " + dt.Rows.Count + " --- Transfering " + NumberOfRecordBatchSave.ToString() + " rows to SQL DB";
lblStatusTxt.Refresh();
Application.DoEvents();
chsChart.LongitudeMax = LongitudeMax;
chsChart.LongitudeMin = LongitudeMin;
chsChart.LatitudeMax = LatitudeMax;
chsChart.LatitudeMin = LatitudeMin;
using (BathymetryEntities be = new BathymetryEntities())
{
be.CHSDepths.AddRange(cdListToAdd);
be.SaveChanges();
}
cdListToAdd = new List <CHSDepth>();
}
sb.Append(cd.LineValue + "\t");
sb.Append(cd.Depth + "\t");
sb.Append(cd.Longitude + "\t");
sb.Append(cd.Latitude + "\t");
sb.AppendLine();
}
lblStatusTxt.Text = "Saving last records";
lblStatusTxt.Refresh();
Application.DoEvents();
chsChart.LongitudeMax = LongitudeMax;
chsChart.LongitudeMin = LongitudeMin;
chsChart.LatitudeMax = LatitudeMax;
chsChart.LatitudeMin = LatitudeMin;
using (BathymetryEntities be = new BathymetryEntities())
{
be.CHSDepths.AddRange(cdListToAdd);
be.SaveChanges();
}
reader.Close();
lblStatusTxt.Text = "Done ...";
lblStatusTxt.Refresh();
Application.DoEvents();
if (ShowResInRTB)
{
richTextBoxResults.Text = sb.ToString();
}
if (NumberOfFile == CountFile)
{
break;
}
}
if (SaveInDB)
{
}
sb.AppendLine("Done ... " + f.Name);
}
}
private void button1_Click(object sender, EventArgs e)
{
List <CHSChart> chsChartList = new List <CHSChart>();
using (BathymetryEntities be = new BathymetryEntities())
{
chsChartList = (from c in be.CHSCharts.AsNoTracking()
where c.LongitudeMin == null
select c).ToList();
}
richTextBoxResults.AppendText($"{ chsChartList.Count } to do\r\n");
foreach (CHSChart chsChart in chsChartList)
{
double?minLat = null;
double?maxLat = null;
double?minLng = null;
double?maxLng = null;
using (BathymetryEntities be = new BathymetryEntities())
{
richTextBoxResults.AppendText($"Doing { chsChart.CHSChartName } \r\n");
richTextBoxResults.Refresh();
Application.DoEvents();
minLat = (double)(from c in be.CHSDepths
where c.CHSChartID == chsChart.CHSChartID &&
c.Latitude != null
select c.Latitude).Min();
maxLat = (double)(from c in be.CHSDepths
where c.CHSChartID == chsChart.CHSChartID &&
c.Latitude != null
select c.Latitude).Max();
minLng = (double)(from c in be.CHSDepths
where c.CHSChartID == chsChart.CHSChartID &&
c.Latitude != null
select c.Longitude).Min();
maxLng = (double)(from c in be.CHSDepths
where c.CHSChartID == chsChart.CHSChartID &&
c.Latitude != null
select c.Longitude).Max();
}
using (BathymetryEntities be2 = new BathymetryEntities())
{
CHSChart chsChartToUpdate = (from c in be2.CHSCharts
where c.CHSChartID == chsChart.CHSChartID
select c).FirstOrDefault();
chsChartToUpdate.LatitudeMin = minLat;
chsChartToUpdate.LatitudeMax = maxLat;
chsChartToUpdate.LongitudeMin = minLng;
chsChartToUpdate.LongitudeMax = maxLng;
try
{
be2.SaveChanges();
}
catch (Exception ex)
{
int seilfj = 34;
}
}
}
}
