public static Company GetCompany(int id)
{
string sql = "select id, name, address, OIB from company where id=@id";
Company company = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@id", id);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("id");
int ordName = rs.GetOrdinal("name");
int ordAddress = rs.GetOrdinal("address");
int ordOIB = rs.GetOrdinal("OIB");
rs.ReadFirst();
company = new Company();
company.Id = rs.GetInt32(ordId);
company.Name = rs.GetString(ordName);
company.Address = rs.GetString(ordAddress);
company.OIB = rs.GetString(ordOIB);
}
return(company);
}
internal void getTrackingState(string SolutionName, string ProjectName, ref string LocalStoredPath, ref string RemoteStoredPath, ref bool bOverride)
{
try
{
SqlCeResultSet rsResult = null;
cmd.CommandText = "select LocalStoredPath, RemoteStoredPath, ManualNotTracked from [Projects] where [SolutionName] = " +
SolutionName + " and [ProjectName] = " + ProjectName;
rsResult = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rsResult == null)
{
MessageBox.Show("Failed to execute command to get tracking info?");
return;
}
if (rsResult.RecordsAffected != 1)
{
MessageBox.Show("Seems we have too many rows in our tracker for this Solution..");
return;
}
rsResult.ReadFirst();
LocalStoredPath = (string)rsResult.GetString(0);
RemoteStoredPath = (string)rsResult.GetString(1);
bOverride = (bool)rsResult.GetBoolean(2);
}
catch (Exception ex)
{
}
}
public static List <Subscriber> GetAllSubscribers()
{
List <Subscriber> subscribers = new List <Subscriber>();
string sql = "select licencePlates, validTo from subscriber";
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordValidTo = rs.GetOrdinal("validTo");
rs.ReadFirst();
Subscriber subscriber = new Subscriber();
subscriber.LicencePlates = rs.GetString(ordLicencePlates);
subscriber.ValidTo = rs.GetDateTime(ordValidTo);
subscribers.Add(subscriber);
while (rs.Read())
{
subscriber = new Subscriber();
subscriber.LicencePlates = rs.GetString(ordLicencePlates);
subscriber.ValidTo = rs.GetDateTime(ordValidTo);
subscribers.Add(subscriber);
}
}
return(subscribers);
}
public static List <VehicleType> GetAllVehicleTypes()
{
List <VehicleType> vehicleTypes = new List <VehicleType>();
string sql = "select type from vehicleType";
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordType = rs.GetOrdinal("type");
rs.ReadFirst();
VehicleType vehicleType = new VehicleType();
vehicleType.Type = rs.GetString(ordType);
vehicleTypes.Add(vehicleType);
while (rs.Read())
{
vehicleType = new VehicleType();
vehicleType.Type = rs.GetString(ordType);
vehicleTypes.Add(vehicleType);
}
}
return(vehicleTypes);
}
public static Vehicle GetVehicle(String licencePlates)
{
if (licencePlates == null || licencePlates.Length == 0)
{
return(null);
}
string sql = "select licencePlates, vehicleType, checkedIn, checkedInDate from vehicle where licencePlates=@licencePlates";
Vehicle vehicle = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@licencePlates", licencePlates);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordVehicleType = rs.GetOrdinal("vehicleType");
int ordCheckedIn = rs.GetOrdinal("checkedIn");
int ordCheckedInDate = rs.GetOrdinal("checkedInDate");
rs.ReadFirst();
vehicle = new Vehicle();
vehicle.LicencePlates = rs.GetString(ordLicencePlates);
vehicle.VehicleType = rs.GetString(ordVehicleType);
vehicle.CheckedIn = rs.GetBoolean(ordCheckedIn);
vehicle.CheckedInDate = rs.GetDateTime(ordCheckedInDate);
}
return(vehicle);
}
public static Price GetPrice(bool summerTariff, string ticketType, string vehicleType)
{
if (ticketType == null || ticketType.Length == 0 || vehicleType == null || vehicleType.Length == 0)
{
return(null);
}
string sql = "select charge, summerTariff, ticketType, vehicleType from price where summerTariff=@summerTariff and ticketType=@ticketType and vehicleType=@vehicleType";
Price price = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@summerTariff", summerTariff);
cmd.Parameters.AddWithValue("@ticketType", ticketType);
cmd.Parameters.AddWithValue("@vehicleType", vehicleType);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordCharge = rs.GetOrdinal("charge");
int ordSummerTariff = rs.GetOrdinal("summerTariff");
int ordTicketType = rs.GetOrdinal("ticketType");
int ordVehicleType = rs.GetOrdinal("vehicleType");
rs.ReadFirst();
price = new Price();
price.Charge = rs.GetDecimal(ordCharge);
price.SummerTariff = rs.GetBoolean(ordSummerTariff);
price.TicketType = rs.GetString(ordTicketType);
price.VehicleType = rs.GetString(ordVehicleType);
}
return(price);
}
public Usuario obtenerUsuario(string userName)
{
con.Open();
Usuario usuario = new Usuario();
string query = "select * from empleat where usu = @user";
SqlCeCommand command = new SqlCeCommand(query, con);
command.Parameters.AddWithValue("@user", userName);
SqlCeResultSet results = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
usuario.idEmpleat = results.GetInt32(0);
usuario.idEmpresa = results.GetInt32(1);
usuario.usu = results.GetString(2);
usuario.nom = results.GetString(4);
usuario.email = results.GetString(5);
return(usuario);
}
con.Close();
return(usuario);
}
private void btnLerRegis_Click(object sender, EventArgs e)
{
SqlCeConnection cn = new SqlCeConnection(stringConexao());
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
// Monta a consulta SQL
string sql = "select sobrenome, nome from " + nomeTabela;
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
// se você precisa atualizar o result set então use:
// SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
if (rs.HasRows)
{
int ordSobrenome = rs.GetOrdinal("sobrenome");
int ordNome = rs.GetOrdinal("nome");
// trata a saida
StringBuilder saida = new StringBuilder();
// le o primeiro registro e pega os dados
rs.ReadFirst();
saida.AppendLine(rs.GetString(ordNome) + " " + rs.GetString(ordSobrenome));
while (rs.Read())
{
saida.AppendLine(rs.GetString(ordNome) + " " + rs.GetString(ordSobrenome));
}
// defina a saida
lblEncontrado.Text = saida.ToString();
}
else
{
lblEncontrado.Text = "Nenhum registro encontrado.";
}
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, "Entre com um banco para acessar os registros", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Bah Tchê.", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
cn.Close();
}
}
// возвращает объект Информация об базе данных переучета которая была записана при формированиия БД
public DbInfo GetDbInfo()
{
DbInfo dbInfo = new DbInfo();
List <Ean> eans = new List <Ean>();
string commandText = @" SELECT pName, val FROM Info ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = commandText;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (res.HasRows)
{
while (res.Read())
{
switch (res.GetString(0).ToUpper())
{
case "APTEKAID":
dbInfo.AptekaID = res.GetString(1);
break;
case "APTEKANAME":
dbInfo.AptekaName = res.GetString(1);
break;
case "VERSION":
dbInfo.Version = res.GetString(1);
break;
case "PEREUCHETDATE":
long ticks = long.Parse(res.GetString(1));
var dt = new DateTime(ticks);
dbInfo.PereuchetDateText = dt.ToShortDateString();
dbInfo.PereuchetDate = dt;
break;
case "DBCREATEDTIME":
long CreatedDBTicks = long.Parse(res.GetString(1));
var CreatedDbDt = new DateTime(CreatedDBTicks);
dbInfo.CreatedDBDt = CreatedDbDt;
break;
case "SPREANROWSCOUNT":
dbInfo.SprEanRowCountText = res.GetString(1);
break;
case "PEREUCHETROWSCOUNT":
dbInfo.PereuchetRowCountText = res.GetString(1);
break;
}
}
}
}
}
}
return(dbInfo);
}
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 static VehicleType GetVehicleType(String vehicleType)
{
if (vehicleType == null || vehicleType.Length == 0)
{
return(null);
}
string sql = "select type from vehicleType where type=@vehicleType";
VehicleType type = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@vehicleType", vehicleType);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordType = rs.GetOrdinal("type");
rs.ReadFirst();
type = new VehicleType();
type.Type = rs.GetString(ordType);
}
return(type);
}
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 DataTable GetGroups(string sqlText)
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("ID_AssetGroup", Type.GetType("System.Int64"));
dt.Columns.Add(dc);
dc = new DataColumn("Name", Type.GetType("System.String"));
dt.Columns.Add(dc);
DataRow dr;
using (SqlCeCommand cmd = new SqlCeCommand(sqlText, _CEConnection))
{
cmd.CommandType = CommandType.Text;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
while (rs.Read())
{
dr = dt.NewRow();
dr["ID_AssetGroup"] = rs.GetInt64(rs.GetOrdinal("ID_AssetGroup"));
dr["Name"] = rs.GetString(rs.GetOrdinal("Name"));
dt.Rows.Add(dr);
}
}
}
dt.AcceptChanges();
return(dt);
}
public ListaPub CarregarListaPublicacao(int idLista)
{
ListaPub lista;
using (SqlCeCommand command = CreateCommand("SELECT * FROM LISTA WHERE ID_LISTA = @ID_LISTA")) {
command.Parameters.AddWithValue("ID_LISTA", idLista);
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
resultSet.Read();
lista = new ListaPub()
{
IdLista = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM_SORTEIO")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
FonteSementeSorteio = resultSet.GetString(resultSet.GetOrdinal("FONTE_SEMENTE")),
SementeSorteio = resultSet.GetInt32(resultSet.GetOrdinal("SEMENTE_SORTEIO")),
Candidatos = new List <CandidatoPub>()
};
}
}
string queryCandidatos = @"
SELECT
CANDIDATO_LISTA.SEQUENCIA_CONTEMPLACAO, CANDIDATO.CPF, CANDIDATO.NOME, QUANTIDADE_CRITERIOS
FROM
CANDIDATO_LISTA
INNER JOIN LISTA ON CANDIDATO_LISTA.ID_LISTA = LISTA.ID_LISTA
INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO
WHERE LISTA.ID_LISTA = @ID_LISTA AND CANDIDATO_LISTA.SEQUENCIA_CONTEMPLACAO IS NOT NULL
ORDER BY CANDIDATO_LISTA.SEQUENCIA_CONTEMPLACAO
";
using (SqlCeCommand command = CreateCommand(queryCandidatos)) {
command.Parameters.AddWithValue("ID_LISTA", idLista);
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
lista.Candidatos.Add(new CandidatoPub {
IdCandidato = resultSet.GetInt32(resultSet.GetOrdinal("SEQUENCIA_CONTEMPLACAO")),
Cpf = resultSet.GetDecimal(resultSet.GetOrdinal("CPF")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
QuantidadeCriterios = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE_CRITERIOS"))
});
}
}
}
return(lista);
}
// возвращает одну запись справочника
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 List <Company> GetAllCompanies()
{
var list = new List <Company>();
SqlCeCommand sqlCeCommand = new SqlCeCommand("select * from company order by name", DatabaseConnector.DatabaseConnection);
sqlCeCommand.CommandType = CommandType.Text;
SqlCeResultSet sqlCeResultSet = sqlCeCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
if (sqlCeResultSet.HasRows)
{
int ordinal0 = sqlCeResultSet.GetOrdinal("id");
int ordinal1 = sqlCeResultSet.GetOrdinal("name");
int ordinal2 = sqlCeResultSet.GetOrdinal("address");
int ordinal3 = sqlCeResultSet.GetOrdinal("OIB");
sqlCeResultSet.ReadFirst();
list.Add(new Company()
{
Id = sqlCeResultSet.GetInt32(ordinal0),
Name = sqlCeResultSet.GetString(ordinal1),
Address = sqlCeResultSet.GetString(ordinal2),
OIB = sqlCeResultSet.GetString(ordinal3)
});
while (sqlCeResultSet.Read())
{
list.Add(new Company()
{
Id = sqlCeResultSet.GetInt32(ordinal0),
Name = sqlCeResultSet.GetString(ordinal1),
Address = sqlCeResultSet.GetString(ordinal2),
OIB = sqlCeResultSet.GetString(ordinal3)
});
}
}
return(list);
}
public static User GetUser(string username)
{
string sql = "select Username, Password, FirstName, LastName, UserType, OIB from users where Username=@username";
User user = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@username", username);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordUsername = rs.GetOrdinal("Username");
int ordPassword = rs.GetOrdinal("Password");
int ordUserType = rs.GetOrdinal("UserType");
int ordLastName = rs.GetOrdinal("LastName");
int ordFirstname = rs.GetOrdinal("FirstName");
int ordOIB = rs.GetOrdinal("OIB");
rs.ReadFirst();
user = new User();
user.FirstName = rs.GetString(ordFirstname);
user.LastName = rs.GetString(ordLastName);
user.Username = rs.GetString(ordUsername);
user.Password = rs.GetString(ordPassword);
user.UserType = rs.GetString(ordUserType);
user.OIB = rs.GetString(ordOIB);
}
return(user);
}
public List <OutgoingTwilioMessage> GetAllOutboxMessages()
{
List <OutgoingTwilioMessage> outboxMsgs = new List <OutgoingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableOutbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllOutboxMessages(): no messages to offload");
return(outboxMsgs);
}
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): offloading " + count + " outbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableOutbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
OutgoingTwilioMessage outMsg = new OutgoingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
From = rs.GetString(2),
To = rs.GetString(3),
Action = rs.GetString(4),
Method = rs.GetString(5),
Body = rs.GetString(6),
MediaURLs = rs.GetString(7),
Client = rs.GetString(8)
};
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): message = " + outMsg.ToString());
outboxMsgs.Add(outMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllOutboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(outboxMsgs);
}
private ICollection <Empreendimento> CarregarEmpreendimentos()
{
ObservableCollection <Empreendimento> empreendimentos = new ObservableCollection <Empreendimento>();
using (SqlCeCommand command = CreateCommand($"SELECT * FROM EMPREENDIMENTO ORDER BY ORDEM")) {
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
empreendimentos.Add(new Empreendimento {
Ordem = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME"))
});
}
}
}
return(empreendimentos);
}
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>
/// s
/// </summary>
/// <param name="whereClause"> forget the "WHERE", e.g. coulumn01 = someValue</param>
/// <returns></returns>
public override List <SqlGeometry> GetGeometries(string whereClause)
{
//SqlCeConnection connection = new SqlCeConnection(_connectionString);
//_connection.Open();
List <Microsoft.SqlServer.Types.SqlGeometry> geometries = new List <Microsoft.SqlServer.Types.SqlGeometry>();
SqlCeCommand command =
new SqlCeCommand(
string.Format(System.Globalization.CultureInfo.InvariantCulture, "SELECT {0} FROM {1} {2} ", _spatialColumnName, _tableName, MakeWhereClause(whereClause)),
_connection);
command.CommandType = System.Data.CommandType.Text;
SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (resultSet.HasRows)
{
int columnIndex = resultSet.GetOrdinal(_spatialColumnName);
if (_wktMode)
{
while (resultSet.Read())
{
geometries.Add(SqlGeometry.Parse(resultSet.GetString(columnIndex)).MakeValid());
}
}
else
{
while (resultSet.Read())
{
geometries.Add(SqlGeometry.STGeomFromWKB(
new System.Data.SqlTypes.SqlBytes((byte[])resultSet.GetValue(columnIndex)), 0).MakeValid());
}
}
}
//connection.Close();
return(geometries);
}
public ICollection <Lista> CarregarListas()
{
List <Lista> listas = new List <Lista>();
using (SqlCeCommand command = CreateCommand($"SELECT * FROM LISTA ORDER BY ORDEM_SORTEIO")) {
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
listas.Add(new Lista {
IdLista = resultSet.GetInt32(resultSet.GetOrdinal("ID_LISTA")),
OrdemSorteio = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM_SORTEIO")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
Quantidade = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE")),
Sorteada = resultSet.GetBoolean(resultSet.GetOrdinal("SORTEADA")),
Publicada = resultSet.GetBoolean(resultSet.GetOrdinal("PUBLICADA"))
});
}
}
}
return(listas);
}
public Lista CarregarProximaLista()
{
using (SqlCeCommand command = CreateCommand("SELECT TOP(1) * FROM LISTA WHERE SORTEADA = 0 ORDER BY ORDEM_SORTEIO")) {
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
if (resultSet.Read())
{
int idLista = resultSet.GetInt32(resultSet.GetOrdinal("ID_LISTA"));
return(new Lista {
IdLista = idLista,
OrdemSorteio = resultSet.GetInt32(resultSet.GetOrdinal("ORDEM_SORTEIO")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")),
Quantidade = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE")),
CandidatosDisponiveis = CandidatosDisponiveisLista(idLista)
});
}
else
{
return(null);
}
}
}
}
public static Subscriber GetSubscriber(string licencePlates)
{
string sql = "select licencePlates, validTo from subscriber where licencePlates=@licencePlates";
Subscriber subscriber = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@licencePlates", licencePlates);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordValidTo = rs.GetOrdinal("validTo");
rs.ReadFirst();
subscriber = new Subscriber();
subscriber.LicencePlates = rs.GetString(ordLicencePlates);
subscriber.ValidTo = rs.GetDateTime(ordValidTo);
}
return(subscriber);
}
private void LoadData(ThreadExecuteTask threadExecute)
{
try
{
this.symbolList.Clear();
for (int i = 0; i < countPerPage; i++)
{
//If an abort has been requested, we should quit
if (threadExecute != null && threadExecute.State ==
ThreadExecuteTask.ProcessingState.requestAbort)
{
threadExecute.setProcessingState(ThreadExecuteTask.ProcessingState.aborted);
System.Windows.Forms.MessageBox.Show("aborted");
return;
}
bool ok;
if (i == 0)
{
ok = this.resultSet.ReadAbsolute(this.firstRecordindex);
Debug.Assert(ok, "Failed to seek to position: " + this.firstRecordindex);
}
else
{
ok = resultSet.Read();
}
if (ok)
{
SymbolInfo info = new SymbolInfo();
info.Id = resultSet.GetInt32(0);
object image = resultSet.GetValue(1);
using (MemoryStream ms = new MemoryStream(image as byte[]))
{
info.Image = ResizeImage(new Bitmap(ms), this.imageSize);
}
info.Sound = resultSet.GetValue(2) as byte[];
info.Text = resultSet.GetString(3);
symbolList.Add(info);
}
else
{
break;
}
}
}
catch (Exception e)
{
Debug.WriteLine(e);
}
if (this.DataLoaded != null)
{
this.DataLoaded();
}
}
public void SortearProximaLista(Action <string> updateStatus, Action <int> updateProgress, Action <string> logText, int?sementePersonalizada = null)
{
updateStatus("Iniciando sorteio...");
Lista proximaLista = CarregarProximaLista();
if (proximaLista == null)
{
throw new Exception("Não existem listas disponíveis para sorteio.");
}
double quantidadeAtual = 0;
double quantidadeTotal = Math.Min(proximaLista.Quantidade, (int)proximaLista.CandidatosDisponiveis);
string fonteSemente = "PERSONALIZADA";
int semente = (sementePersonalizada == null) ? ObterSemente(ref fonteSemente) : (int)sementePersonalizada;
ExecuteNonQuery(
"UPDATE LISTA SET SORTEADA = 1, SEMENTE_SORTEIO = @SEMENTE_SORTEIO, FONTE_SEMENTE = @FONTE_SEMENTE WHERE ID_LISTA = @ID_LISTA",
new SqlCeParameter("SEMENTE_SORTEIO", semente),
new SqlCeParameter("FONTE_SEMENTE", fonteSemente),
new SqlCeParameter("ID_LISTA", proximaLista.IdLista)
);
Random random = new Random(semente);
string queryGrupoSorteio = @"
SELECT TOP(1) CANDIDATO_LISTA.CLASSIFICACAO AS CLASSIFICACAO, COUNT(*) AS QUANTIDADE
FROM CANDIDATO_LISTA INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO
WHERE CANDIDATO_LISTA.ID_LISTA = @ID_LISTA AND CANDIDATO_LISTA.DATA_CONTEMPLACAO IS NULL AND CANDIDATO.CONTEMPLADO = 0
GROUP BY CANDIDATO_LISTA.CLASSIFICACAO
ORDER BY CANDIDATO_LISTA.CLASSIFICACAO
";
SqlCeCommand commandGrupoSorteio = CreateCommand(queryGrupoSorteio);
commandGrupoSorteio.Parameters.AddWithValue("ID_LISTA", proximaLista.IdLista);
commandGrupoSorteio.Prepare();
string queryCandidatosGrupo = @"
SELECT CANDIDATO_LISTA.SEQUENCIA, CANDIDATO.ID_CANDIDATO, CANDIDATO.CPF, CANDIDATO.NOME
FROM CANDIDATO_LISTA INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO
WHERE CANDIDATO_LISTA.ID_LISTA = @ID_LISTA AND CANDIDATO_LISTA.DATA_CONTEMPLACAO IS NULL AND CANDIDATO.CONTEMPLADO = 0 AND CANDIDATO_LISTA.CLASSIFICACAO = @CLASSIFICACAO
ORDER BY CANDIDATO_LISTA.SEQUENCIA
";
SqlCeCommand commandCandidatosGrupo = CreateCommand(queryCandidatosGrupo);
commandCandidatosGrupo.Parameters.AddWithValue("ID_LISTA", proximaLista.IdLista);
commandCandidatosGrupo.Parameters.AddWithValue("CLASSIFICACAO", -1);
commandCandidatosGrupo.Prepare();
GrupoSorteio grupoSorteio = null;
for (int i = 1; i <= proximaLista.Quantidade; i++)
{
if (grupoSorteio == null || grupoSorteio.Quantidade < 1)
{
updateStatus("Carregando próximo grupo de sorteio.");
using (SqlCeResultSet resultSet = commandGrupoSorteio.ExecuteResultSet(ResultSetOptions.None)) {
if (resultSet.Read())
{
grupoSorteio = new GrupoSorteio {
Classificacao = resultSet.GetInt32(resultSet.GetOrdinal("CLASSIFICACAO")),
Quantidade = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE"))
};
}
else
{
grupoSorteio = null;
}
}
if (grupoSorteio != null)
{
commandCandidatosGrupo.Parameters["CLASSIFICACAO"].Value = grupoSorteio.Classificacao;
using (SqlCeResultSet resultSet = commandCandidatosGrupo.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
CandidatoGrupo candidato = new CandidatoGrupo {
Sequencia = resultSet.GetInt32(resultSet.GetOrdinal("SEQUENCIA")),
IdCandidato = resultSet.GetInt32(resultSet.GetOrdinal("ID_CANDIDATO")),
Cpf = resultSet.GetDecimal(resultSet.GetOrdinal("CPF")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME"))
};
grupoSorteio.Candidatos.Add(candidato.Sequencia, candidato);
}
}
}
}
if (grupoSorteio == null)
{
break;
}
else
{
updateStatus($"Sorteando entre o grupo de classificação \"{grupoSorteio.Classificacao}\": {quantidadeTotal - quantidadeAtual} vagas restantes.");
}
int indiceSorteado = (grupoSorteio.Quantidade == 1) ? 0 : random.Next(0, grupoSorteio.Quantidade);
CandidatoGrupo candidatoSorteado = grupoSorteio.Candidatos.Skip(indiceSorteado).Take(1).First().Value;
grupoSorteio.Candidatos.Remove(candidatoSorteado.Sequencia);
ExecuteNonQuery(
"UPDATE CANDIDATO SET CONTEMPLADO = 1 WHERE ID_CANDIDATO = @ID_CANDIDATO",
new SqlCeParameter("ID_CANDIDATO", candidatoSorteado.IdCandidato)
);
ExecuteNonQuery(
@"
UPDATE CANDIDATO_LISTA
SET SEQUENCIA_CONTEMPLACAO = @SEQUENCIA_CONTEMPLACAO, DATA_CONTEMPLACAO = @DATA_CONTEMPLACAO
WHERE ID_CANDIDATO = @ID_CANDIDATO AND ID_LISTA = @ID_LISTA
",
new SqlCeParameter("SEQUENCIA_CONTEMPLACAO", i),
new SqlCeParameter("DATA_CONTEMPLACAO", DateTime.Now),
new SqlCeParameter("ID_CANDIDATO", candidatoSorteado.IdCandidato),
new SqlCeParameter("ID_LISTA", proximaLista.IdLista)
);
grupoSorteio.Quantidade--;
quantidadeAtual++;
updateProgress((int)((quantidadeAtual / quantidadeTotal) * 100));
logText(string.Format("{0:0000} - {1:000'.'000'.'000-00} - {2}", i, candidatoSorteado.Cpf, candidatoSorteado.Nome.ToUpper()));
}
updateStatus("Sorteio da lista finalizado!");
}
private void ClassificarListaSorteio(int idUltimaLista, string tipoOrdenacao)
{
List <CandidatoGrupo> candidatosLista = new List <CandidatoGrupo>();
using (SqlCeCommand command = CreateCommand("SELECT * FROM CANDIDATO_LISTA INNER JOIN CANDIDATO ON CANDIDATO_LISTA.ID_CANDIDATO = CANDIDATO.ID_CANDIDATO WHERE CANDIDATO_LISTA.ID_LISTA = @ID_LISTA")) {
command.Parameters.AddWithValue("ID_LISTA", idUltimaLista);
using (SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.None)) {
while (resultSet.Read())
{
candidatosLista.Add(new CandidatoGrupo {
IdCandidato = resultSet.GetInt32(resultSet.GetOrdinal("ID_CANDIDATO")),
Cpf = resultSet.GetDecimal(resultSet.GetOrdinal("CPF")),
Nome = resultSet.GetString(resultSet.GetOrdinal("NOME")).ToUpper().TrimEnd(),
QuantidadeCriterios = resultSet.GetInt32(resultSet.GetOrdinal("QUANTIDADE_CRITERIOS"))
});
}
}
}
CandidatoGrupo[] candidatosOrdenados;
if (tipoOrdenacao == "SIMPLES")
{
candidatosOrdenados = candidatosLista
.OrderByDescending(c => c.QuantidadeCriterios)
.ThenBy(c => c.Nome)
.ThenByDescending(c => c.Cpf)
.ToArray();
}
else if (tipoOrdenacao == "COMPOSTO")
{
candidatosOrdenados = candidatosLista
.OrderByDescending(c => c.QuantidadeCriteriosComposta)
.ThenBy(c => c.Nome)
.ThenByDescending(c => c.Cpf)
.ToArray();
}
else
{
candidatosOrdenados = candidatosLista
.OrderBy(c => c.Nome)
.ThenByDescending(c => c.Cpf)
.ToArray();
}
CandidatoGrupo candidatoAnterior = null;
int sequencia = 1;
int classificacao = 1;
SqlCeCommand updateCommand = CreateCommand(
"UPDATE CANDIDATO_LISTA SET SEQUENCIA = @SEQUENCIA, CLASSIFICACAO = @CLASSIFICACAO WHERE ID_LISTA = @ID_LISTA AND ID_CANDIDATO = @ID_CANDIDATO",
new SqlCeParameter("SEQUENCIA", -1),
new SqlCeParameter("CLASSIFICACAO", -1),
new SqlCeParameter("ID_LISTA", idUltimaLista),
new SqlCeParameter("ID_CANDIDATO", -1)
);
updateCommand.Prepare();
foreach (CandidatoGrupo candidato in candidatosOrdenados)
{
if (candidatoAnterior != null)
{
if (tipoOrdenacao == "SIMPLES" && candidato.QuantidadeCriterios != candidatoAnterior.QuantidadeCriterios)
{
classificacao++;
}
else if (tipoOrdenacao == "COMPOSTO" && candidato.QuantidadeCriteriosComposta != candidatoAnterior.QuantidadeCriteriosComposta)
{
classificacao++;
}
}
updateCommand.Parameters["SEQUENCIA"].Value = sequencia;
updateCommand.Parameters["CLASSIFICACAO"].Value = classificacao;
updateCommand.Parameters["ID_CANDIDATO"].Value = candidato.IdCandidato;
updateCommand.ExecuteNonQuery();
sequencia++;
candidatoAnterior = candidato;
}
}
public static List <User> GetAllUsers()
{
List <User> users = new List <User>();
string sql = "select Username, Password, FirstName, LastName, UserType, OIB from users";
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordUsername = rs.GetOrdinal("Username");
int ordPassword = rs.GetOrdinal("Password");
int ordUserType = rs.GetOrdinal("UserType");
int ordLastName = rs.GetOrdinal("LastName");
int ordFirstname = rs.GetOrdinal("FirstName");
int ordOIB = rs.GetOrdinal("OIB");
rs.ReadFirst();
User user = new User();
user.FirstName = rs.GetString(ordFirstname);
user.LastName = rs.GetString(ordLastName);
user.Username = rs.GetString(ordUsername);
user.Password = rs.GetString(ordPassword);
user.UserType = rs.GetString(ordUserType);
user.OIB = rs.GetString(ordOIB);
users.Add(user);
while (rs.Read())
{
user = new User();
user.FirstName = rs.GetString(ordFirstname);
user.LastName = rs.GetString(ordLastName);
user.Username = rs.GetString(ordUsername);
user.Password = rs.GetString(ordPassword);
user.UserType = rs.GetString(ordUserType);
user.OIB = rs.GetString(ordOIB);
users.Add(user);
}
}
return(users);
}
public List <IncomingTwilioMessage> GetAllInboxMessages()
{
List <IncomingTwilioMessage> inboxMsgs = new List <IncomingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableInbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllInboxMessages(): no messages to offload");
return(inboxMsgs);
}
App.logger.Log("DBStore.GetAllInboxMessages(): offloading " + count + " inbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableInbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
IncomingTwilioMessage inMsg = new IncomingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
AccountSid = rs.GetString(2),
ApiVersion = rs.GetString(3),
Body = rs.GetString(4),
From = rs.GetString(5),
FromCity = rs.GetString(6),
FromCountry = rs.GetString(7),
FromState = rs.GetString(8),
FromZip = rs.GetString(9),
MessageSid = rs.GetString(10),
NumMedia = rs.GetString(11),
NumSegments = rs.GetString(12),
SmsSid = rs.GetString(13),
SmsStatus = rs.GetString(14),
ToState = rs.GetString(15),
To = rs.GetString(16),
ToCity = rs.GetString(17),
ToCountry = rs.GetString(18),
ToZip = rs.GetString(19),
MediaURLs = rs.GetString(20)
};
// DEBUG
App.logger.Log("DBStore.GetAllInboxMessages(): message = " + inMsg.ToString());
inboxMsgs.Add(inMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllInboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(inboxMsgs);
}
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();
}