/// <summary>
/// Gets the primary key 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 PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)
{
// Erik Ejlskov - corrected SQL statement
string sql = string.Format("select KCU.[CONSTRAINT_NAME] as [Constraint], KCU.[COLUMN_NAME] as [ColumnName] from INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME where KCU.TABLE_NAME = '{0}' AND TC.TABLE_NAME = '{0}' AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' order by KCU.ORDINAL_POSITION", table.Name);
string name = String.Empty;
var cols = new List <string>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
name = (string)results["Constraint"];
cols.Add((string)results["ColumnName"]);
}
}
}
if (name == String.Empty && cols.Count == 0)
{
return(null);
}
return(new PrimaryKeySchema(table, name, cols.ToArray()));
}
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 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 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 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 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);
}
/// <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());
}
/// <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);
}
private IEnumerable <T> Map(SqlCeResultSet resultSet)
{
var list = new List <T>();
while (resultSet.Read())
{
list.Add(Map((SqlCeDataReader)resultSet));
}
resultSet.Close();
return(list);
}
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);
}
static void Main(string[] args)
{
// Arguments for update
int lookFor = 1;
string value = "AC/DC";
// Arguments for insert
lookFor = Int16.MaxValue;
value = "joedotnet";
using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\xeej\Downloads\ChinookPart2\Chinook.sdf"))
{
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("Artist"))
{
SqlCeUpdatableRecord myRec = null;
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.TableDirect;
cmd.IndexName = "PK_Artist";
SqlCeResultSet myResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
bool found = myResultSet.Seek(DbSeekOptions.FirstEqual, new object[] { lookFor });
if (found)
{
myResultSet.Read();
}
else
{
myRec = myResultSet.CreateRecord();
}
foreach (KeyValuePair <int, object> item in CommonMethodToFillRowData(value))
{
if (found)
{
myResultSet.SetValue(item.Key, item.Value);
}
else
{
myRec.SetValue(item.Key, item.Value);
}
}
if (found)
{
myResultSet.Update();
}
else
{
myResultSet.Insert(myRec);
}
}
}
}
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 SqlCeWrapper(SqlCeResultSet resultSet)
{
_resultSet = resultSet;
_found = resultSet.Seek();
if (_found)
{
resultSet.Read();
}
else
{
_newRecord = resultSet.CreateRecord();
}
}
public void CanExecuteResultSetWithCommand()
{
using (DbCommand command = db.GetSqlStringCommand(queryString))
{
string accumulator = "";
using (SqlCeResultSet reader = db.ExecuteResultSet(command))
{
while (reader.Read())
{
accumulator += ((string)reader["RegionDescription"]).Trim();
}
}
Assert.AreEqual("EasternWesternNorthernSouthern", accumulator);
}
}
public void ReadCompactDirect()
{
var con = new SqlCeConnection(@"Data Source=Database1.sdf");
con.Open();
var cmd = new SqlCeCommand("SELECT s1,s2 FROM TablSpecial", con);
SqlCeResultSet rec = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
while (rec.Read())
{
MessageBox.Show(rec["s1"] + @" " + rec["s2"]);
}
rec.Close();
con.Close();
}
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);
}
private void RetrivingHistoryInformation(ViewClientCard clientCard, int ClientId)
{
string queryString = "SELECT InventNum, FilmName, DistrDate, ReturnDate, CountOfDay, FilmPrice, Penny, TotalAmount FROM ClientHistory WHERE ClientId='" + ClientId + "';";
cmd.CommandText = queryString;
cmd.Connection = connection;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
while (rs.Read())
{
string[] rowI = new string[] { (rs["InventNum"]).ToString(), (rs["FilmName"]).ToString(),
(rs["DistrDate"]).ToString(), (rs["ReturnDate"]).ToString(), (rs["CountOfDay"]).ToString(),
(rs["FilmPrice"]).ToString(), (rs["Penny"]).ToString(), (rs["TotalAmount"]).ToString() };
clientCard.ClientDataGridView.Rows.Add(rowI);
for (int i = 0; i < clientCard.ClientDataGridView.RowCount; i++)
{
DataGridViewRow dataGridViewRow = clientCard.ClientDataGridView.Rows[i];
if (dataGridViewRow.IsNewRow)
{
continue;
}
if (dataGridViewRow.Cells["Дата возврата"].Value.ToString() != "-")
{
dataGridViewRow.DefaultCellStyle.BackColor = Color.BlueViolet;
}
}
}
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CountTheNumberOfClientFilms()
{
string queryString =
"SELECT FilmId FROM BusyFilmCopy WHERE ClientId='" + ClientId + "';";
cmd.CommandText = queryString;
cmd.Connection = connection;
int i = 0;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (rs.HasRows)
{
while (rs.Read())
{
i++;
}
}
else
{
i = 0;
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (i >= 0 && i < 5)
{
comboBox2.Enabled = true;
}
else
{
NextButton.Enabled = false;
comboBox2.Enabled = false;
MessageBox.Show("Извините, кол-во фильмов на руках превышено. Верните хотя бы один фильм.");
}
}
public void CanExecuteResultSetWithCommand()
{
SqlCeDatabase db = (SqlCeDatabase)this.db;
using (DbCommand command = db.GetSqlStringCommand(queryString))
{
SqlCeResultSet reader = db.ExecuteResultSet(command);
string accumulator = "";
while (reader.Read())
{
accumulator += ((string)reader["RegionDescription"]).Trim();
}
reader.Close();
command.Connection.Close();
Assert.AreEqual("EasternWesternNorthernSouthern", accumulator);
}
}
private void FillComboBoxOfPreviousPartsOfFilm()
{
comboBox1.Items.Clear();
string queryString =
"SELECT Название + ' ' + Год, Id FROM Film";
cmd.CommandText = queryString;
cmd.Connection = connection;
try
{
connection.Open();
SqlCeResultSet reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
string FilmName = "";
int FilmId = 0;
while (reader.Read())
{
FilmName = (reader[0]).ToString();
FilmId = Convert.ToInt16(reader[1]);
String[] substrings = FilmName.Split(' ');
FilmName = "";
for (int i = 0; i < substrings.Length; i++)
{
if (i == substrings.Length - 1)
{
FilmName += "(" + substrings[i] + ")";
break;
}
FilmName += substrings[i] + " ";
}
comboBox1.Items.Add(FilmName + " | " + "FilmId=" + FilmId);
}
reader.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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 void CheckAge(int FilmId, int ClientId)
{
string queryString =
"SELECT Film.[Целевая аудитория 18+], ClientPersonalCard.Возраст FROM Film, ClientPersonalCard WHERE Film.Id='" + FilmId + "' AND ClientPersonalCard.Id='" + ClientId + "'";
cmd.CommandText = queryString;
cmd.Connection = connection;
string isFilmFor18 = "";
int clientAge = 0;
try
{
connection.Open();
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
while (rs.Read())
{
isFilmFor18 = rs[0].ToString();
clientAge = Convert.ToInt16(rs[1]);
}
rs.Close();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (isFilmFor18 == "Нет")
{
NextButton.Enabled = true;
}
else if (isFilmFor18 == "Да" && clientAge < 18)
{
NextButton.Enabled = false;
MessageBox.Show("Извините, но ваш возраст не соответствует возрастному ограничению фильма.");
}
else if (isFilmFor18 == "Да" && clientAge >= 18)
{
NextButton.Enabled = true;
}
}
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);
}
/// <summary>
/// Checks if an index exists for the property of T that is not null.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="TableItem"></param>
/// <returns>the name of the index</returns>
public string CheckIndex <T>(T TableItem) where T : class, new()
{
if (!AccessPointReady())
{
return(null);
}
SetPropertyInfosAndUniqueIdentifier(TableItem);
string sqlStatement = ConstructSQLStatementCheckIndex();
SqlCeConnection connection = new SqlCeConnection(ConnectionString());
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCeCommand command = GetSqlCeCommand(connection, sqlStatement);
SqlCeResultSet ResultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (ResultSet.HasRows)
{
while (ResultSet.Read())
{
object result = ResultSet.GetValue(5);
if ((string)result == GetIndexName(TableItem))
{
return((string)result);
}
}
}
return("");
}
catch (Exception exception)
{
Console.WriteLine(exception.Message + exception.StackTrace);
return(null);
}
finally
{
connection.Close();
}
}
public List <string> getIdentsFromRoute(string routeIdent)
{
List <string> idents = new List <string>();
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(string.Format("SELECT AirportIdent FROM RouteIdents where RouteId = '{0}' order by RouteSequenceNumber asc", routeIdent));
selectCmd.CommandText = selectQuery.ToString();
SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (results.HasRows)
{
results.ReadFirst();
while (true)
{
idents.Add(results.GetSqlString(0).ToString());
if (!results.Read())
{
break;
}
}
}
return(idents);
}
catch (Exception ex)
{
return(idents);
}
finally
{
if (_dataConn != null)
{
_dataConn.Close();
}
}
}
/// <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 List <T> Select <T>(T SearchItem) where T : class, new()
{
if (!AccessPointReady())
{
return(null);
}
SetPropertyInfosAndUniqueIdentifier(SearchItem);
List <T> dataList = new List <T>();
string selectStatement = ConstructSQLStatementSelect(SearchItem);
SqlCeConnection connection = new SqlCeConnection(ConnectionString());
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCeCommand command = GetSqlCeCommand(connection, selectStatement);
AddParametersWithValuesFromProperties(SearchItem, command, Suffix.Where);
SqlCeResultSet ResultSet = command.ExecuteResultSet(ResultSetOptions.Scrollable);
if (ResultSet.HasRows)
{
while (ResultSet.Read())
{
dataList.Add(FillObjectWithResultSet(new T(), ResultSet));
}
}
return(dataList);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message + exception.StackTrace);
return(null);
}
finally
{
connection.Close();
}
}
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);
}
/// <summary>
/// Gets all of the tables available in the database.
/// </summary>
/// <param name="connectionString">The connection string used to connect to the target database.</param>
/// <param name="database"></param>
/// <returns></returns>
public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
//Erik Ejlskov - exclude system tables
const string sql = @"select [TABLE_NAME] as [TableName] from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE' ORDER BY TABLE_NAME";
var tables = new List <TableSchema>();
using (SqlCeCommand cmd = GetCeCommand(connectionString, sql))
{
using (SqlCeResultSet results = cmd.ExecuteResultSet(ResultSetOptions.None))
{
while (results.Read())
{
var tableName = (string)results["TableName"];
tables.Add(new TableSchema(database, tableName, string.Empty, DateTime.MinValue));
}
}
}
return(tables.ToArray());
}
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();
}
}
}
