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;
                    }
                }
            }
        }