private ApplicationState()
{
// read the application state from db
SqlCeConnection _dataConn = null;
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 cruiseSpeed,cruiseFuelFlow,minFuel,speed,unit,utcOffset,locationFormat,deckHoldFuel,registeredClientName FROM ApplicationState");
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
cruiseSpeed = results.GetInt64(0);
cruiseFuelFlow = results.GetInt64(1);
minFuel = results.GetInt64(2);
speed = results.GetSqlString(3).ToString();
unit = results.GetSqlString(4).ToString();
utcOffset = results.GetSqlString(5).ToString();
locationFormat = results.GetSqlString(6).ToString();
deckHoldFuel = results.IsDBNull(7) ? 0 : results.GetInt64(7);
registeredClientName = results.IsDBNull(8) ? string.Empty : results.GetString(8);
}
}
finally
{
_dataConn.Close();
}
}
public Employee GetEmployee(string barcode)
{
Employee emp = null;
string selectCommand = @" SELECT CONVERT(INT,id_gamma) id_gamma, ename, case when barcode is not null then barcode else '' END barcode FROM Employee WHERE barcode IS NOT NULL AND barcode = CONVERT(NVARCHAR(12), @barcode) ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = new SqlCeCommand(selectCommand, connect))
{
var param = command.Parameters.Add("barcode", SqlDbType.NVarChar);
param.Value = barcode;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (res.ReadFirst())
{
emp = new Employee()
{
GammaID = (int)res.GetInt32(res.GetOrdinal("id_gamma")),
Barcode = (res.IsDBNull(res.GetOrdinal("barcode"))) ? "" : res.GetString(res.GetOrdinal("barcode")),
Name = res.GetString(res.GetOrdinal("ename"))
};
}
}
}
}
return(emp);
}
// возвращает одну запись справочника
public Ean GetEan(string barcode)
{
Ean ean = null;
string commandText = @" SELECT top(1) s.artcode, s.ean13, s.names, s.koef, p.qty, s.nds, s.Manufacturer
FROM sprean s
LEFT OUTER JOIN pereuchet p ON s.artcode = p.artcode
WHERE s.ean13 = @barcode
ORDER BY (CASE WHEN p.qty IS NULL THEN 0 ELSE p.qty END) DESC, s.artcode desc ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = commandText;
command.Parameters.Add("barcode", SqlDbType.NVarChar).Value = barcode;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
try
{
if (res.HasRows)
{
res.ReadFirst();
{
ean = new Ean();
ean.ArtCode = res.GetInt32(0);
ean.Ean13 = res.GetString(1);
ean.Name = res.GetString(2);
ean.Koef = res.GetInt32(3);
ean.ControlQty = res.IsDBNull(4) ? 0 : res.GetInt32(4);
ean.Nds = res.GetInt32(5);
ean.Manufacturer = res.GetString(6);
}
}
}
finally
{
res.Close();
}
}
}
}
return(ean);
}
public static TValue SafeGet <TValue>(this SqlCeResultSet self, string columnName, TValue defaultValue = default(TValue))
{
var t = typeof(TValue);
var ordinal = self.GetOrdinal(columnName);
if (self.IsDBNull(ordinal))
{
return(defaultValue);
}
dynamic value;
if (t == typeof(int))
{
value = self.GetInt32(ordinal);
}
else if (t == typeof(long))
{
value = self.GetInt64(ordinal);
}
else if (t == typeof(bool))
{
value = self.GetBoolean(ordinal);
}
else if (t == typeof(object))
{
value = self.GetValue(ordinal);
}
else if (t == typeof(string))
{
value = self.GetString(ordinal);
}
else if (t == typeof(int?) || t == typeof(long?) || t == typeof(bool?))
{
value = self.GetValue(ordinal);
}
else
{
throw new ApplicationException($"{nameof(SafeGet)} does not support type '{t.Name}'!");
}
return(value == null ? defaultValue : (TValue)Convert.ChangeType(value, Nullable.GetUnderlyingType(typeof(TValue)) ?? typeof(TValue)));
}
/// <summary>
/// Returns a column result set as a list of the specified type (via cast, omitting NULLs)
/// </summary>
/// <typeparam name="T">Cast to this return type</typeparam>
/// <param name="query">Query to execute</param>
/// <param name="columnName">Column (name) to return</param>
/// <returns></returns>
public List <T> ExecuteListQuery <T>(string query, string columnName)
{
using (SqlCeCommand cmd = this.GetConnection().CreateCommand())
{
List <T> result = new List <T>();
cmd.CommandText = query;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None);
int ordinal = rs.GetOrdinal(columnName);
while (rs.Read())
{
if (!rs.IsDBNull(ordinal))
{
result.Add((T)rs.GetValue(ordinal));
}
}
return(result);
}
}
/// <summary>
/// Gets all columns for a given table.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="table"></param>
/// <returns></returns>
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
{
//Erik Ejlskov - exclude system columns
string getColumnSql = string.Format(@"SELECT
COLUMN_NAME as [Name],
COLUMN_DEFAULT as [Default],
IS_NULLABLE as [IsNullable],
DATA_TYPE as [DataType],
CHARACTER_MAXIMUM_LENGTH as [Length],
NUMERIC_PRECISION as [Precision],
NUMERIC_SCALE as [Scale],
AUTOINC_SEED,
AUTOINC_INCREMENT,
COLUMN_HASDEFAULT,
COLUMN_FLAGS
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = '{0}' AND
COLUMN_FLAGS <> 98 AND
COLUMN_FLAGS <> 114
ORDER BY
ORDINAL_POSITION", table.Name);
var columns = new List <ColumnSchema>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, getColumnSql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
var extendedProperties = new List <ExtendedProperty>();
if (!results.IsDBNull(7))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, true, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, results["AUTOINC_SEED"].ToString(), DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, results["AUTOINC_INCREMENT"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, false, DbType.Boolean));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, "0", DbType.String));
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, "0", DbType.String));
}
if (results["COLUMN_HASDEFAULT"] != null)
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, results["Default"].ToString(), DbType.String));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, string.Empty, DbType.String));
}
var name = (string)results["Name"];
var nativeType = (string)results["DataType"];
//Erik Ejlskov - should be "timestamp" instead
if (nativeType == "rowversion")
{
nativeType = "timestamp";
}
DbType dataType = GetDbTypeFromString(nativeType);
if ((dataType == DbType.Guid && results.GetInt32(10) == 378) || (dataType == DbType.Guid && results.GetInt32(10) == 282))
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, true, DbType.Boolean));
}
else
{
extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsRowGuidColumn, false, DbType.Boolean));
}
int size;
int.TryParse(results["Length"].ToString(), out size);
byte precision;
byte.TryParse(results["Precision"].ToString(), out precision);
int scale;
int.TryParse(results["scale"].ToString(), out scale);
bool allowNull = GetBoolFromYesNo((string)results["IsNullable"]);
var s = new ColumnSchema(table, name, dataType, nativeType, size, precision, scale, allowNull, extendedProperties.ToArray());
columns.Add(s);
} // while(read)
} // using(results)
} // using(command)
return(columns.ToArray());
}
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();
}