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 butCreate_indexKMZ_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
richTextBoxResults.Text = "";
// creating the Index.KMZ document
sb.Append(TopOfKML("Index"));
List <CHSChart> chsChartList = new List <CHSChart>();
using (BathymetryEntities be = new BathymetryEntities())
{
chsChartList = (from c in be.CHSCharts where !c.CHSChartName.Contains("SOUND") orderby c.CHSChartName select c).ToList();
}
lblCurrentFile2.Text = "Index";
lblCurrentFile2.Refresh();
Application.DoEvents();
foreach (CHSChart chsChart in chsChartList)
{
lblStatusTxt2.Text = "Doing " + chsChart.CHSChartName;
double MinLongitude = (double)chsChart.LongitudeMin;
double MaxLongitude = (double)chsChart.LongitudeMax;
double MinLatitude = (double)chsChart.LatitudeMin;
double MaxLatitude = (double)chsChart.LatitudeMax;
CHSChart SoundChart = new CHSChart();
using (BathymetryEntities be = new BathymetryEntities())
{
SoundChart = (from c in be.CHSCharts where c.CHSChartName.Contains(chsChart.CHSChartName + "SOUNDG") select c).FirstOrDefault <CHSChart>();
}
if (SoundChart != null)
{
if (SoundChart.LongitudeMin < MinLongitude)
{
MinLongitude = (double)SoundChart.LongitudeMin;
}
if (SoundChart.LongitudeMax > MaxLongitude)
{
MaxLongitude = (double)SoundChart.LongitudeMax;
}
if (SoundChart.LatitudeMin < MinLatitude)
{
MinLatitude = (double)SoundChart.LatitudeMin;
}
if (SoundChart.LatitudeMax > MaxLatitude)
{
MaxLatitude = (double)SoundChart.LatitudeMax;
}
}
lblStatusTxt2.Refresh();
Application.DoEvents();
sb.AppendLine(@" <Folder>");
sb.AppendLine(@" <name>"+ chsChart.CHSChartName + "</name>");
sb.AppendLine(@" <Folder>");
sb.AppendLine(@" <name>Data Extent</name>");
sb.AppendLine(@" <visibility>1</visibility>");
sb.AppendLine(@" <Placemark>");
sb.AppendLine(@" <name>"+ chsChart.CHSChartName + "</name>");
string styleUrlStr = GetColorStyleID((double)((MaxLongitude - MinLongitude) + (MaxLatitude - MinLatitude)) * 100, ColorValList);
sb.AppendLine(@" <styleUrl>#"+ styleUrlStr + "</styleUrl>");
sb.AppendLine(@" <Point>");
sb.AppendLine(@" <coordinates>"+ MaxLongitude + "," + MaxLatitude + ",0 </coordinates>");
sb.AppendLine(@" </Point>");
sb.AppendLine(@" </Placemark>");
sb.AppendLine(@" <Placemark>");
sb.AppendLine(@" <name>"+ chsChart.CHSChartName + "</name>");
sb.AppendLine(@" <styleUrl>#C_0</styleUrl>");
sb.AppendLine(@" <LineString>");
sb.AppendLine(@" <tessellate>1</tessellate>");
sb.AppendLine(@" <coordinates>");
sb.Append(string.Format("{0},{1},0 ", MinLongitude, MinLatitude));
sb.Append(string.Format("{0},{1},0 ", MinLongitude, MaxLatitude));
sb.Append(string.Format("{0},{1},0 ", MaxLongitude, MaxLatitude));
sb.Append(string.Format("{0},{1},0 ", MaxLongitude, MinLatitude));
sb.Append(string.Format("{0},{1},0 ", MinLongitude, MinLatitude));
sb.AppendLine();
sb.AppendLine(@" </coordinates>");
sb.AppendLine(@" </LineString>");
sb.AppendLine(@" </Placemark>");
sb.AppendLine(@" </Folder>");
sb.AppendLine(@" <Folder>");
sb.AppendLine(@" <name>Line Data</name>");
sb.AppendLine(@" <visibility>0</visibility>");
sb.AppendLine(@" <NetworkLink>");
sb.AppendLine(@" <name>"+ chsChart.CHSChartName + "</name>");
sb.AppendLine(@" <visibility>0</visibility>");
sb.AppendLine(@" <Link>");
sb.AppendLine(@" <href>X:\"+ chsChart.CHSChartName + ".kmz</href>");
sb.AppendLine(@" </Link>");
sb.AppendLine(@" </NetworkLink>");
sb.AppendLine(@" </Folder>");
sb.AppendLine(@" <Folder>");
sb.AppendLine(@" <name>Sound Data</name>");
sb.AppendLine(@" <visibility>0</visibility>");
sb.AppendLine(@" <NetworkLink>");
sb.AppendLine(@" <name>"+ chsChart.CHSChartName + "SOUNDG</name>");
sb.AppendLine(@" <visibility>0</visibility>");
sb.AppendLine(@" <Link>");
sb.AppendLine(@" <href>X:\"+ chsChart.CHSChartName + "SOUNDG.kmz</href>");
sb.AppendLine(@" </Link>");
sb.AppendLine(@" </NetworkLink>");
sb.AppendLine(@" </Folder>");
sb.AppendLine(@" </Folder>");
}
sb.AppendLine(@"</Document>");
sb.AppendLine(@"</kml>");
richTextBoxResults.AppendText(sb.ToString());
SaveInKMZFileStream(textBoxDbfFilesDirPath.Text + @"\KMZ\_index.kmz", textBoxDbfFilesDirPath.Text + @"\KMZ\_index.kml", sb);
lblStatusTxt2.Text = "_index.kmz Saved";
lblStatusTxt2.Refresh();
Application.DoEvents();
}
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;
}
}
}
}