public void InitializePilotsDataFromDatabase()
{
try
{
_dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
_dataConn.Open();
SqlCeCommand selectCmd = new SqlCeCommand();
selectCmd.Connection = _dataConn;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT [First Name],[Last Name],[Weight]");
selectQuery.Append(" FROM Airports");
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
while (true)
{
Pilot currPilot = new Pilot();
currPilot.FName = results.GetSqlString(0).ToString();
currPilot.LName = results.GetSqlString(1).ToString();
currPilot.Weight = results.GetDouble(2);
PersonId pid = new PersonId(currPilot.FName, currPilot.LName);
if (!listofPilots.ContainsKey(pid))
{
listofPilots.Add(pid, new List <Pilot>());
}
listofPilots[pid].Add(currPilot);
nameList.Add(pid);
if (!results.Read())
{
break;
}
}
}
}
catch (Exception ex)
{
}
finally
{
_dataConn.Close();
}
}
private static void Main(string[] args)
{
SqlCeConnection sqlCeCon = new SqlCeConnection("Data Source=\\endo.sdf");
try
{
sqlCeCon.Open();
Console.WriteLine("Connection is open");
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = sqlCeCon;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM Workout;";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
List <Workout> workoutList = new List <Workout>();
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("Id");
int ordWorkoutId = rs.GetOrdinal("WorkoutId");
int ordSport = rs.GetOrdinal("Sport");
int ordDuration = rs.GetOrdinal("Duration");
while (rs.Read())
{
Guid id = rs.GetGuid(ordId);
string workoutId = rs.GetString(ordWorkoutId);
int sport = rs.GetInt32(ordSport);
double duration = rs.GetDouble(ordDuration);
workoutList.Add(new Workout(id, workoutId, sport, duration));
}
}
int counter = 1;
foreach (Workout workout in workoutList)
{
cmd.CommandText = $"SELECT * FROM Track WHERE Track.WorkoutId='{workout.Id}';";
//Console.WriteLine(cmd.CommandText);
rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
List <Track> trackList = new List <Track>();
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("Id");
int ordWorkoutId = rs.GetOrdinal("WorkoutId");
int ordTimestamp = rs.GetOrdinal("Timestamp");
int ordInstruction = rs.GetOrdinal("Instruction");
int ordLatitude = rs.GetOrdinal("Latitude");
int ordLongitude = rs.GetOrdinal("Longitude");
int ordDistance = rs.GetOrdinal("Distance");
int ordSpeed = rs.GetOrdinal("Speed");
int ordAltitude = rs.GetOrdinal("Altitude");
int ordSentToServer = rs.GetOrdinal("SentToServer");
while (rs.Read())
{
int id = rs.GetInt32(ordId);
Guid workoutId = rs.GetGuid(ordWorkoutId);
DateTime timestamp = rs.GetDateTime(ordTimestamp);
timestamp = timestamp.Subtract(new TimeSpan(2, 0, 0));
int instruction = rs.IsDBNull(ordInstruction) ? -1 : rs.GetInt32(ordInstruction);
double latitude = rs.GetDouble(ordLatitude);
double longitude = rs.GetDouble(ordLongitude);
double distance = rs.GetDouble(ordDistance);
double speed = rs.GetDouble(ordSpeed);
double altitude = rs.GetDouble(ordAltitude);
bool sentToServer = rs.GetBoolean(ordSentToServer);
trackList.Add(new Track(id, workoutId, timestamp, instruction, latitude, longitude, distance, speed, altitude, sentToServer));
}
string fileName;
fileName = String.Format("Endo_{0}_tcx.tcx", counter);
CreateXmlTcx(fileName, workout, trackList);
fileName = String.Format("Endo_{0}_gpx.gpx", counter);
CreateXmlGpx(fileName, workout, trackList);
}
counter++;
}
sqlCeCon.Close();
Console.WriteLine("Connection is closed");
}
catch (Exception ex)
{
Console.WriteLine(ex.Source + " - " + ex.Message);
}
//Console.ReadKey();
}
