/// <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(); } } }