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 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 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 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);
}
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 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 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 Scan GetScan(int artcode)
{
Scan scan = null;
string selectCommand = @" SELECT artcode, id_gamma, qty FROM scan WHERE artcode = @artcode and id_gamma = @id_gamma";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = connect.CreateCommand())
{
command.CommandText = selectCommand;
var param = command.Parameters.Add("artcode", SqlDbType.Int);
param.Value = artcode;
param = command.Parameters.Add("id_gamma", SqlDbType.Int);
param.Value = GlobalArea.CurrentEmployee.GammaID;
using (SqlCeResultSet res = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (!res.HasRows)
{
return(null);
}
if (!res.ReadFirst())
{
return(null);
}
scan = new Scan((int)res.GetInt32(res.GetOrdinal("artcode")),
(int)res.GetInt32(res.GetOrdinal("id_gamma")),
(int)res.GetInt32(res.GetOrdinal("qty")));
}
}
return(scan);
}
}
public static List <AXF_LAYERS> GetAxfLayerss(SqlCeResultSet sqlCeResultSet, DataGridView dataGridView)
{
int idxTableId = sqlCeResultSet.GetOrdinal(TableIdFieldName);
int idxName = sqlCeResultSet.GetOrdinal(NameFieldName);
return((from DataGridViewRow dataRow in dataGridView.Rows
select new AXF_LAYERS
{
TableId = int.Parse(dataRow.Cells[idxTableId].Value.ToString()),
Name = dataRow.Cells[idxName].Value.ToString()
}).ToList());
}
public static List <AXF_GEOMETRY_COLUMNS> GetAxfGeometryColumns(SqlCeResultSet sqlCeResultSet, DataGridView dataGridView)
{
int idxTableId = sqlCeResultSet.GetOrdinal(TableIdFieldName);
int idxTableName = sqlCeResultSet.GetOrdinal(TableNameFieldName);
return((from DataGridViewRow dataRow in dataGridView.Rows
select new AXF_GEOMETRY_COLUMNS
{
TableId = int.Parse(dataRow.Cells[idxTableId].Value.ToString()),
TableName = dataRow.Cells[idxTableName].Value.ToString()
}).ToList());
}
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 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 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);
}
public static int GetTicketCount()
{
string sql = "select * from counter";
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordticketCounter = rs.GetOrdinal("ticketCounter");
rs.ReadFirst();
int count = rs.GetInt32(ordticketCounter);
return(count);
}
}
catch (Exception e)
{
Logger.Logger.Log(e);
}
return(-1);
}
/// <summary>
///
/// </summary>
/// <param name="attributeColumn"></param>
/// <param name="whereClause"> forget the "WHERE", e.g. coulumn01 = someValue</param>
/// <returns></returns>
public override List <object> GetAttributes(string attributeColumn, string whereClause)
{
//SqlCeConnection connection = new SqlCeConnection(_connectionString);
SqlCeCommand command = new SqlCeCommand(string.Format(System.Globalization.CultureInfo.InvariantCulture, "SELECT {0} FROM {1} {2}", attributeColumn, _tableName, MakeWhereClause(whereClause)), _connection);
command.CommandType = System.Data.CommandType.Text;
//connection.Open();
List <object> result = new List <object>();
SqlCeResultSet resultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (!resultSet.HasRows)
{
return(new List <object>());
}
int columnIndex = resultSet.GetOrdinal(attributeColumn);
while (resultSet.Read())
{
result.Add(resultSet.GetValue(columnIndex));
}
//connection.Close();
return(result.Cast <object>().ToList());
}
public static List <AxfField> GetAxfFieldList(SqlCeResultSet sqlCeResultSet, DataGridView dataGridView)
{
int idxObjectid = sqlCeResultSet.GetOrdinal(ObjectidName);
int idxAxfTimestamp = sqlCeResultSet.GetOrdinal(AxfTimestampName);
int idxAxfStatus = sqlCeResultSet.GetOrdinal(AxfStatusName);
int idxAxfGeneration = sqlCeResultSet.GetOrdinal(AxfGenerationName);
return((from DataGridViewRow dataRow in dataGridView.Rows
select new AxfField
{
Objectid = string.IsNullOrEmpty(dataRow.Cells[idxObjectid].Value.ToString()) ? -1 : Convert.ToInt32(dataRow.Cells[idxObjectid].Value),
AxfTimestamp = string.IsNullOrEmpty(dataRow.Cells[idxAxfTimestamp].Value.ToString()) ? DateTime.MinValue : Convert.ToDateTime(dataRow.Cells[idxAxfTimestamp].Value),
AxfStatus = string.IsNullOrEmpty(dataRow.Cells[idxAxfStatus].Value.ToString()) ? -1 : Convert.ToInt32(dataRow.Cells[idxAxfStatus].Value),
AxfGeneration = string.IsNullOrEmpty(dataRow.Cells[idxAxfGeneration].Value.ToString()) ? -1 : Convert.ToInt32(dataRow.Cells[idxAxfGeneration].Value)
}).ToList());
}
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);
}
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 List <AxfTables> GetAxfTablesList(SqlCeResultSet sqlCeResultSet, DataGridView dataGridView)
{
int idxTableName = sqlCeResultSet.GetOrdinal(TableNameName);
int idxAlias = sqlCeResultSet.GetOrdinal(AliasName);
int idxIsReadonly = sqlCeResultSet.GetOrdinal(IsReadonlyName);
int idxIsHidden = sqlCeResultSet.GetOrdinal(IsHiddenName);
int idxSourceObjectidColumn = sqlCeResultSet.GetOrdinal(SourceObjectidColumnName);
return((from DataGridViewRow dataRow in dataGridView.Rows
select new AxfTables
{
TableName = dataRow.Cells[idxTableName].Value.ToString(),
Alias = dataRow.Cells[idxAlias].Value.ToString(),
IsReadonly = Convert.ToBoolean(dataRow.Cells[idxIsReadonly].Value),
IsHidden = Convert.ToBoolean(dataRow.Cells[idxIsHidden].Value),
SourceObjectidColumn = dataRow.Cells[idxSourceObjectidColumn].Value.ToString()
}).ToList());
}
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 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 FlushCalls(SqlCeResultSet resultSet)
{
//a lock is already taken at this point
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
int childOrdinal = resultSet.GetOrdinal("ChildId");
int parentOrdinal = resultSet.GetOrdinal("ParentId");
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
foreach (KeyValuePair <int, SortedDictionary <int, SortedList <int, int> > > threadKvp in m_calls.Graph)
{
int threadId = threadKvp.Key;
foreach (KeyValuePair <int, SortedList <int, int> > parentKvp in threadKvp.Value)
{
int parentId = parentKvp.Key;
foreach (KeyValuePair <int, int> hitsKvp in parentKvp.Value)
{
int childId = hitsKvp.Key;
int hits = hitsKvp.Value;
bool result = resultSet.Seek(DbSeekOptions.FirstEqual, threadId, parentId, childId);
if (result && resultSet.Read())
{
//found it, update the hit count and move on
hits += (int)resultSet[hitsOrdinal];
resultSet.SetInt32(hitsOrdinal, hits);
resultSet.Update();
}
else
{
//not in the db, create a new record
CreateRecord(resultSet, threadId, parentId, childId, hits);
}
}
parentKvp.Value.Clear();
}
}
}
private void FlushSamples(SqlCeResultSet resultSet)
{
//now to update the samples table
foreach (KeyValuePair <int, SortedList <int, int> > sampleKvp in m_samples)
{
if (sampleKvp.Value.Count == 0)
{
continue;
}
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
int functionOrdinal = resultSet.GetOrdinal("FunctionId");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
foreach (KeyValuePair <int, int> threadKvp in sampleKvp.Value)
{
if (!resultSet.Seek(DbSeekOptions.FirstEqual, threadKvp.Key, sampleKvp.Key))
{
//doesn't exist in the table, we need to add it
var row = resultSet.CreateRecord();
row[threadOrdinal] = threadKvp.Key;
row[functionOrdinal] = sampleKvp.Key;
row[hitsOrdinal] = threadKvp.Value;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
else
{
resultSet.Read();
resultSet.SetValue(hitsOrdinal, (int)resultSet[hitsOrdinal] + threadKvp.Value);
resultSet.Update();
}
}
sampleKvp.Value.Clear();
}
}
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);
}
/// <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);
}
}
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 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);
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach(KeyValuePair<int, List<long>> timingKvp in m_timings)
{
if(timingKvp.Value.Count == 0)
continue;
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for(int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if(foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if(id != timingKvp.Key)
{
if(!resultSet.Read())
{
foundBin = false;
}
}
if(foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if(id != timingKvp.Key || time < min || time > max)
foundBin = false;
}
}
if(foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var mergeId = resultSet.GetInt32(funcOrdinal);
if(mergeId != timingKvp.Key)
resultSet.Read();
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for(int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if(range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if(shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if(!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
resultSet.ReadFirst();
else
resultSet.Read();
var tempId = resultSet.GetInt32(funcOrdinal);
if(tempId != timingKvp.Key)
resultSet.Read();
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for(int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
private void FlushSamples(SqlCeResultSet resultSet)
{
//now to update the samples table
foreach(KeyValuePair<int, SortedList<int, int>> sampleKvp in m_samples)
{
if(sampleKvp.Value.Count == 0)
continue;
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
int functionOrdinal = resultSet.GetOrdinal("FunctionId");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
foreach(KeyValuePair<int, int> threadKvp in sampleKvp.Value)
{
if(!resultSet.Seek(DbSeekOptions.FirstEqual, threadKvp.Key, sampleKvp.Key))
{
//doesn't exist in the table, we need to add it
var row = resultSet.CreateRecord();
row[threadOrdinal] = threadKvp.Key;
row[functionOrdinal] = sampleKvp.Key;
row[hitsOrdinal] = threadKvp.Value;
resultSet.Insert(row, DbInsertOptions.PositionOnInsertedRow);
}
else
{
resultSet.Read();
resultSet.SetValue(hitsOrdinal, (int) resultSet[hitsOrdinal] + threadKvp.Value);
resultSet.Update();
}
}
sampleKvp.Value.Clear();
}
}
private void FlushCalls(SqlCeResultSet resultSet)
{
//a lock is already taken at this point
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
int childOrdinal = resultSet.GetOrdinal("ChildId");
int parentOrdinal = resultSet.GetOrdinal("ParentId");
int threadOrdinal = resultSet.GetOrdinal("ThreadId");
foreach(KeyValuePair<int, SortedDictionary<int, SortedList<int, int>>> threadKvp in m_calls.Graph)
{
int threadId = threadKvp.Key;
foreach(KeyValuePair<int, SortedList<int, int>> parentKvp in threadKvp.Value)
{
int parentId = parentKvp.Key;
foreach(KeyValuePair<int, int> hitsKvp in parentKvp.Value)
{
int childId = hitsKvp.Key;
int hits = hitsKvp.Value;
bool result = resultSet.Seek(DbSeekOptions.FirstEqual, threadId, parentId, childId);
if(result && resultSet.Read())
{
//found it, update the hit count and move on
hits += (int) resultSet[hitsOrdinal];
resultSet.SetInt32(hitsOrdinal, hits);
resultSet.Update();
}
else
{
//not in the db, create a new record
CreateRecord(resultSet, threadId, parentId, childId, hits);
}
}
parentKvp.Value.Clear();
}
}
}
private void FlushTimings(SqlCeResultSet resultSet)
{
foreach (KeyValuePair <int, List <long> > timingKvp in m_timings)
{
if (timingKvp.Value.Count == 0)
{
continue;
}
int funcOrdinal = resultSet.GetOrdinal("FunctionId");
int minOrdinal = resultSet.GetOrdinal("RangeMin");
int maxOrdinal = resultSet.GetOrdinal("RangeMax");
int hitsOrdinal = resultSet.GetOrdinal("HitCount");
for (int t = 0; t < timingKvp.Value.Count; ++t)
{
bool foundBin = true;
long time = timingKvp.Value[t];
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, time))
{
foundBin = false;
}
if (foundBin)
{
resultSet.Read();
var id = resultSet.GetInt32(funcOrdinal);
if (id != timingKvp.Key)
{
if (!resultSet.Read())
{
foundBin = false;
}
}
if (foundBin)
{
var min = resultSet.GetInt64(minOrdinal);
var max = resultSet.GetInt64(maxOrdinal);
if (id != timingKvp.Key || time < min || time > max)
{
foundBin = false;
}
}
}
if (foundBin)
{
//we've got a usable bin, increment and move on
var hits = resultSet.GetInt32(hitsOrdinal);
resultSet.SetInt32(hitsOrdinal, hits + 1);
resultSet.Update();
continue;
}
//didn't find a bin, create a new one for this entry
var row = resultSet.CreateRecord();
row[funcOrdinal] = timingKvp.Key;
row[minOrdinal] = time;
row[maxOrdinal] = time;
row[hitsOrdinal] = 1;
resultSet.Insert(row, DbInsertOptions.KeepCurrentPosition);
//we need to bin-merge
//start by seeking to the first record for this function
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var mergeId = resultSet.GetInt32(funcOrdinal);
if (mergeId != timingKvp.Key)
{
resultSet.Read();
}
mergeId = resultSet.GetInt32(funcOrdinal);
//we know at least one exists, cause we just inserted one
Debug.Assert(mergeId == timingKvp.Key);
//Search for the merge that produces the smallest merged bucket
long lastMin = resultSet.GetInt64(minOrdinal);
int lastHits = resultSet.GetInt32(hitsOrdinal);
bool shouldMerge = resultSet.Read();
//these store all the data about the best merge so far
long smallestRange = long.MaxValue;
long bestMin = 0;
long bestMax = 0;
int mergedHits = 0;
for (int b = 0; b < kTimingBuckets && shouldMerge; ++b)
{
long max = resultSet.GetInt64(maxOrdinal);
long range = max - lastMin;
if (range < smallestRange)
{
smallestRange = range;
bestMin = lastMin;
bestMax = max;
mergedHits = lastHits + resultSet.GetInt32(hitsOrdinal);
}
lastMin = resultSet.GetInt64(minOrdinal);
lastHits = resultSet.GetInt32(hitsOrdinal);
//if this read fails, we have insufficient buckets to bother merging
shouldMerge = resultSet.Read();
}
if (shouldMerge)
{
//seek to the first (lower) bin
resultSet.Seek(DbSeekOptions.FirstEqual, timingKvp.Key, bestMin);
resultSet.Read();
//expand this bin to include the next one
resultSet.SetInt64(maxOrdinal, bestMax);
resultSet.SetInt32(hitsOrdinal, mergedHits);
//go to the now redundant bin
resultSet.Update();
resultSet.Read();
//delete the bin
resultSet.Delete();
}
}
#if FALSE
//DEBUG ONLY HACK: display buckets
if (!resultSet.Seek(DbSeekOptions.BeforeEqual, timingKvp.Key, 0.0f))
{
resultSet.ReadFirst();
}
else
{
resultSet.Read();
}
var tempId = resultSet.GetInt32(funcOrdinal);
if (tempId != timingKvp.Key)
{
resultSet.Read();
}
Console.WriteLine("Buckets for function {0}:", timingKvp.Key);
for (int b = 0; b < kTimingBuckets; ++b)
{
long min = resultSet.GetInt64(minOrdinal);
long max = resultSet.GetInt64(maxOrdinal);
int hits = resultSet.GetInt32(hitsOrdinal);
Console.WriteLine("[{0}, {1}]: {2}", min, max, hits);
resultSet.Read();
}
#endif
}
}
