Example #1
0
        public void InitializeAirportdataFromDatabase()
        {
            try
            {
                _dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
                _dataConn.Open();

                // first delete the existing data
                SqlCeCommand selectCmd = new SqlCeCommand();
                selectCmd.Connection = _dataConn;
                StringBuilder selectQuery = new StringBuilder();
                selectQuery.Append("SELECT airport_id, airport_ident, airport_type, airport_name, airport_latdeg, airport_longdeg, airport_elev_ft, airport_muncipality, airport_freqkhz, airport_gps_code,");
                selectQuery.Append("airport_iata_code, airport_magnetic_deg, associated_airport FROM Airports");
                selectCmd.CommandText = selectQuery.ToString();
                SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (results.HasRows)
                {
                    results.ReadFirst();
                    while (true)
                    {
                        Airport currAirport = new Airport();

                        currAirport.ID = results.GetSqlString(0).ToString();
                        currAirport.ident = results.GetSqlString(1).ToString();
                        currAirport.type = results.GetSqlString(2).ToString();
                        currAirport.name = results.GetSqlString(3).ToString();
                        currAirport.latitude_deg = results.GetSqlString(4).ToString();
                        currAirport.longitude_deg = results.GetSqlString(5).ToString();
                        currAirport.elev_ft = results.GetSqlString(6).ToString();

                        currAirport.municipality = results.GetSqlString(7).ToString();
                        currAirport.frequency_khz = results.GetSqlString(8).ToString();

                        currAirport.gps_code = results.GetSqlString(9).ToString();
                        currAirport.iata_code = results.GetSqlString(10).ToString();
                        currAirport.magnetic_variation_deg = results.GetSqlString(11).ToString();
                        currAirport.associated_airport = results.GetSqlString(12).ToString();

                        if (!listofAirports.ContainsKey(currAirport.ident))
                            listofAirports.Add(currAirport.ident, new List<Airport>());
                        listofAirports[currAirport.ident].Add(currAirport);

                        identList.Add(currAirport.ident);

                        if (!results.Read())
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (_dataConn != null)
                {
                    _dataConn.Close();
                }
            }
        }
Example #2
0
        static void BulkCopy()
        {
            SqlCeConnection _conn = new SqlCeConnection("Data Source=AirportsDB.sdf;Persist Security Info=False;");
            _conn.Open();

            // first delete the existing data
            SqlCeCommand delcmd = new SqlCeCommand();
            delcmd.Connection = _conn;
            delcmd.CommandText = "delete from airports";
            int delrc = delcmd.ExecuteNonQuery();

            string line = string.Empty;
            Dictionary<string, bool> dictIdent = new Dictionary<string,bool>();
            System.IO.StreamReader file =
               new System.IO.StreamReader("data.txt");
            int index = 0;
            int affected_rc = 0;
            while ((line = file.ReadLine()) != null)
            {
                Airport currAirport = new Airport();
                string[] fields = line.Split('\t');
                if (fields.Count() < 13)
                    continue;

                currAirport.ID = fields[0];
                currAirport.ident = fields[1];
                currAirport.type = fields[2];
                currAirport.name = fields[3];
                currAirport.latitude_deg = fields[4];
                currAirport.longitude_deg = fields[5];
                currAirport.elev_ft = fields[6];

                currAirport.municipality = fields[7];
                currAirport.frequency_khz = fields[8];

                currAirport.gps_code = fields[9];
                currAirport.iata_code = fields[10];
                currAirport.magnetic_variation_deg = fields[11];

                currAirport.associated_airport = fields[12];
                if (index != 0 && !dictIdent.ContainsKey(currAirport.ident))
                {
                    SqlCeCommand cmd = new SqlCeCommand();
                    cmd.Connection = _conn;
                    StringBuilder queryBuilder = new StringBuilder("insert into airports values ('");
                    queryBuilder.Append(currAirport.ID + "','");
                    queryBuilder.Append(currAirport.ident + "','");
                    queryBuilder.Append(currAirport.type + "','");
                    queryBuilder.Append(currAirport.name + "','");
                    queryBuilder.Append(currAirport.latitude_deg + "','");
                    queryBuilder.Append(currAirport.longitude_deg + "','");
                    queryBuilder.Append(currAirport.elev_ft + "','");
                    queryBuilder.Append(currAirport.municipality + "','");
                    queryBuilder.Append(currAirport.frequency_khz + "','");
                    queryBuilder.Append(currAirport.gps_code + "','");
                    queryBuilder.Append(currAirport.iata_code + "','");
                    queryBuilder.Append(currAirport.magnetic_variation_deg + "','");
                    queryBuilder.Append(currAirport.associated_airport + "')");

                    cmd.CommandText = queryBuilder.ToString();
                    affected_rc += cmd.ExecuteNonQuery();
                    dictIdent.Add(currAirport.ident, true);
                }
                ++index;
            }
            MessageBox.Show("Bulk copy is done. No of rows inserted ="+affected_rc.ToString());
        }