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