private void button1_Click(object sender, EventArgs e) { //Mostrar na listBox2 os alunos do curso selecionado no listBox1: try { listBox2.Items.Clear(); //limpamos a segunda lista string cursoSelec = (string)((DataRowView)listBox1.SelectedItem)[0]; string sql = "SELECT * FROM Alunos WHERE abrevcurso = '" + cursoSelec + "'"; //suponhamos o BD em C:\tempo System.Data.OleDb.OleDbConnection conexao = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/temp/Universidade3.mdb"); conexao.Open(); System.Data.OleDb.OleDbCommand comando = new System.Data.OleDb.OleDbCommand(sql, conexao); System.Data.OleDb.OleDbDataReader dr = comando.ExecuteReader(); while (dr.Read()) { string dadosAluno = dr.GetString(0) + ", " + dr.GetString(1) + ", " + dr.GetString(2) + ", " + dr.GetInt32(3) + ", " + dr.GetString(4); listBox2.Items.Add(dadosAluno); } dr.Close(); comando.Dispose(); conexao.Close(); } catch (Exception exc) { MessageBox.Show(exc.Message); } }
private void button3_Click(object sender, EventArgs e) { // Botão btnResultados try { string id1 = txtIdade1.Text; string id2 = txtIdade2.Text; string strSsexo = ""; if (rbMasculino.Checked) strSsexo = " sexo = 'M' AND "; else if (rbFeminino.Checked) strSsexo = " sexo = 'F' AND "; else strSsexo = ""; sql = "select * from alunos WHERE " + strSsexo + " idade>=" + id1 + " AND idade<=" + id2 + " ORDER BY nome"; stm = new System.Data.OleDb.OleDbCommand(sql, conexao); rs = stm.ExecuteReader(); string strSaida = ""; while (rs.Read()) { string dados = "RGM: " + rs.GetString(0) + ", " + rs.GetString(1) + ", idade: " + rs.GetInt32(3) + ", sexo: " + rs.GetString(4) + ", curso: " + rs.GetString(2); strSaida += dados + "\r\n"; } txtResultados.Text = strSaida; } catch (Exception exsql) { MessageBox.Show("Erro na consulta"); } if(stm!=null)stm.Dispose(); if(rs!=null)rs.Close(); }
public List <OpstiDomenskiObjekat> VratiListu(System.Data.OleDb.OleDbDataReader citac) { List <OpstiDomenskiObjekat> proizvodjaci = new List <OpstiDomenskiObjekat>(); while (citac.Read()) { Proizvodjac p = new Proizvodjac(); p.prozvodjacID = citac.GetString(0); p.Naziv = citac.GetString(1); proizvodjaci.Add(p); } return(proizvodjaci); }
private void button3_Click(object sender, EventArgs e) { // Botão btnResultados try { string id1 = txtIdade1.Text; string id2 = txtIdade2.Text; string strSsexo = ""; if (rbMasculino.Checked) { strSsexo = " sexo = 'M' AND "; } else if (rbFeminino.Checked) { strSsexo = " sexo = 'F' AND "; } else { strSsexo = ""; } sql = "select * from alunos WHERE " + strSsexo + " idade>=" + id1 + " AND idade<=" + id2 + " ORDER BY nome"; stm = new System.Data.OleDb.OleDbCommand(sql, conexao); rs = stm.ExecuteReader(); string strSaida = ""; while (rs.Read()) { string dados = "RGM: " + rs.GetString(0) + ", " + rs.GetString(1) + ", idade: " + rs.GetInt32(3) + ", sexo: " + rs.GetString(4) + ", curso: " + rs.GetString(2); strSaida += dados + "\r\n"; } txtResultados.Text = strSaida; } catch (Exception exsql) { MessageBox.Show("Erro na consulta"); } if (stm != null) { stm.Dispose(); } if (rs != null) { rs.Close(); } }
public List <OpstiDomenskiObjekat> VratiListu(System.Data.OleDb.OleDbDataReader citac) { List <OpstiDomenskiObjekat> rentiranja = new List <OpstiDomenskiObjekat>(); while (citac.Read()) { Rentiranje rent = new Rentiranje(); Klijent k = new Klijent(); Automobil a = new Automobil(); Zaposleni zz = new Zaposleni(); Zaposleni zr = new Zaposleni(); rent.rentiranjeID = citac.GetString(0); a.AutomobilID = citac.GetString(1); k.KlijentID = citac.GetString(2); rent.datumZaduzenja = citac.GetDateTime(3); if (!citac.IsDBNull(4)) { rent.datumRazduzenja = citac.GetDateTime(4); } rent.ukupnaCena = Convert.ToDouble(citac["UkupnaCena"]); rent.razduzeno = citac.GetBoolean(6); rent.stornirano = citac.GetBoolean(7); zz.ZaposleniID = citac.GetString(8); if (!citac.IsDBNull(9)) { zr.ZaposleniID = citac.GetString(9); } else { zr.ZaposleniID = "/"; } rent.klijent = k; rent.automobil = a; rent.zaduzioID = zz; rent.razduzioID = zr; rentiranja.Add(rent); } return(rentiranja); }
public override void ExecuteQuery(CommerceServer.Foundation.CommerceQueryOperation queryOperation, CommerceServer.Foundation.OperationCacheDictionary operationCache, CommerceServer.Foundation.CommerceQueryOperationResponse response) { CommerceModelSearch search = queryOperation.GetSearchCriteria <CommerceModelSearch>(); ParameterChecker.CheckForNull(search, "searchCriteria"); if (!String.IsNullOrEmpty(search.WhereClause)) { var sqlFormat = " SELECT u_user_id, u_first_name, u_last_name, u_email_address FROM [BEK_Commerce_profiles].[dbo].[UserObject] {0}"; var sql = string.Format(sqlFormat, search.WhereClause.IndexOf("where", StringComparison.CurrentCultureIgnoreCase) >= 0 ? search.WhereClause : "WHERE " + search.WhereClause); CommerceServer.Core.Runtime.Configuration.CommerceResourceCollection csResources = SiteHelper.GetCsConfig(); String connStr = csResources["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString(); using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr)) { conn.Open(); using (System.Data.OleDb.OleDbCommand cmdRead = new System.Data.OleDb.OleDbCommand(sql, conn)) { using (System.Data.OleDb.OleDbDataReader dataReader = cmdRead.ExecuteReader()) { while (dataReader.Read()) { CommerceEntity org = new CommerceEntity("UserProfile"); org.Id = dataReader.GetString("u_user_id"); org.SetPropertyValue("FirstName", dataReader.GetString("u_first_name")); org.SetPropertyValue("LastName", dataReader.GetString("u_last_name")); org.SetPropertyValue("Email", dataReader.GetString("u_email_address")); response.CommerceEntities.Add(org); } } } } } else { base.ExecuteQuery(queryOperation, operationCache, response); } }
public List <OpstiDomenskiObjekat> VratiListu(System.Data.OleDb.OleDbDataReader citac) { List <OpstiDomenskiObjekat> automobili = new List <OpstiDomenskiObjekat>(); while (citac.Read()) { Automobil a = new Automobil(); Proizvodjac p = new Proizvodjac(); a.automobilID = citac.GetString(0); a.modelAutomobila = citac.GetString(1); a.GodinaProizvodnje = Convert.ToInt32(citac["GodinaProizvodnje"]); a.registarskiBroj = citac.GetString(3); a.cenaRentiranja = Convert.ToDouble(citac["CenaRentiranja"]); p.ProzvodjacID = citac.GetString(5); p.Naziv = citac.GetString(7); a.proizvodjac = p; automobili.Add(a); } return(automobili); }
/* * selectAllCustomers generates an arraylist of all customer ids to use in admin page * @returns ArrayList */ public ArrayList selectAllCustomers() { ArrayList allCustomers = new ArrayList(); String query = "SELECT CustID FROM Customers"; String custID; dr = db.SelectCommand(query); while (dr.Read()) { custID = dr.GetString(0); allCustomers.Add(custID); } db.DBClose(); return(allCustomers); }
public static IList <string> ReadExcel() { IList <RvtRoom> rooms = new List <RvtRoom>(); IList <string> types = new List <string>(); // Connection string string connectionString = ConfigurationManager.ConnectionStrings["excel"].ConnectionString; // Select using a Named range //string selectString = "SELECT * FROM Cutomers"; // Select using a worksheet name //string selectStringSheet = "SELECT * FROM [Sheet1$]"; using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString)) { connection.Open(); string sheetName = connection.GetOleDbSchemaTable (System.Data.OleDb.OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"] as string; string selectString = "SELECT mcRm_SubZone FROM [ZONE_1$]"; System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(selectString, connection); using (System.Data.OleDb.OleDbDataReader reader = myCommand.ExecuteReader()) { try { while (reader.Read()) { //Room room = new Room(reader.GetString(0), reader.GetString(4), //reader.GetString(6), reader.GetString(8), reader.GetString(14), reader.GetString(21)); //rooms.Add(room); types.Add(reader.GetString(0)); } } catch (Exception ex) { Trace.Write(string.Format("{0}\n{1}", ex.Message, ex.StackTrace)); } } } //return rooms; return(types); }
public EspecialZonesDataGrid(EntitiesCollections entitiesCollections) : base(entitiesCollections) { InitializeComponent(); string query = null; string Contract = null; List <string> listazonas = new List <string>(); query = "SELECT DISTINCT(Nombre) FROM ZonaBasica"; reader = DataBaseManager.ReadData(query); while (reader.Read()) { Contract = (reader.GetString(0)); listazonas.Add(Contract); } DataBaseManager.DbConnection.Close(); // Contrato.DataContext = lista; (NameColumn).ItemsSource = listazonas; // Country.GetCountries(); EspecialZonesCollectionViewModel items = ItemsSource as EspecialZonesCollectionViewModel; if (items.Count == 1) { EspecialZoneViewModel item = items[0] as EspecialZoneViewModel; if (item.Name == null) { CurrentColumn = NameColumn; NameColumn.IsReadOnly = false; IndiceIniColumn.IsReadOnly = false; IndiceFinColumn.IsReadOnly = false; } } }
/* * run_query_bttn_Click handler for button click * Auto generated method */ private void run_query_bttn_Click(object sender, EventArgs e) { result_listbox.Clear(); if (string.IsNullOrWhiteSpace(sql_textbox.Text)) // check for empty sql textbox { MessageBox.Show("Your sql file is empty!"); return; } if (string.IsNullOrWhiteSpace(access_textbox.Text)) // check for empty database textbox { MessageBox.Show("Your database is empty!"); return; } System.IO.FileStream InFile; System.IO.StreamReader Reader; InFile = new System.IO.FileStream(sql_textbox.Text, System.IO.FileMode.Open, System.IO.FileAccess.Read); Reader = new System.IO.StreamReader(InFile); string Buffer = Reader.ReadToEnd(); string[] sql_commands = Buffer.Split(new char[] { ';' }); // Attempt to open the database string ConnectString = "provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + access_textbox.Text; System.Data.OleDb.OleDbConnection Connection = new System.Data.OleDb.OleDbConnection(ConnectString); System.Data.OleDb.OleDbCommand SQLCommand = null; System.Data.OleDb.OleDbDataReader SQLDataReader = null; // copy pasta from lab string Result_string; try /* opening the db connection */ { result_listbox.AppendText(ConnectString + "\r\n"); Connection.Open(); result_listbox.AppendText("Connection open...\r\n"); } catch (System.Exception f) { result_listbox.AppendText("Problems opening..." + f.Message + "\r\n"); return; //This is a fatal error, return } for (int i = 0; i < sql_commands.Length; i++) // Go through each string buffer line and add to the db { if (!string.IsNullOrWhiteSpace(sql_commands[i])) { SQLCommand = new System.Data.OleDb.OleDbCommand(sql_commands[i], Connection); //execute the statement result_listbox.AppendText("Attempting to add: " + sql_commands[i] + "\r\n"); try { SQLDataReader = SQLCommand.ExecuteReader(); result_listbox.AppendText("Execution successful\r\n\r\n"); } catch (System.Exception f) { result_listbox.AppendText("Problems executing..." + f.Message + "\r\n"); } /* at this point we know that execution succeeded so we can start reading the * results. Since we do not know how many records we get returned, we'll stick * the read in a while() loop that keeps executing until we no longer get * results from the reader. */ try { while (SQLDataReader.Read()) { Result_string = SQLDataReader.GetString(0); result_listbox.AppendText(Result_string + "\r\n"); } SQLDataReader.Close(); } catch (System.Exception f) { result_listbox.AppendText("Problems reading data..." + f.Message + "\r\n"); break; } } } try /* closing the connection */ { Connection.Close(); result_listbox.AppendText("Connection closed... \r\n"); } catch (System.Exception f) { result_listbox.AppendText("Problems closing..." + f.Message + "\r\n"); } }
protected CalendarEvent _CreateEventFromData(System.Data.OleDb.OleDbDataReader pEventData, Boolean bRException) { try { CalendarEvent pEvent = null; int nEventID; if (pEventData.IsClosed /*|| !pEventData.HasRows*/) { return(null); } nEventID = pEventData.GetInt32(pEventData.GetOrdinal("EventID")); pEvent = m_pCalendar.DataProvider.CreateEventEx(nEventID); if (pEvent == null) { return(null); } pEvent.Subject = pEventData.GetString(pEventData.GetOrdinal("Subject")); pEvent.Location = pEventData.GetString(pEventData.GetOrdinal("Location")); pEvent.Body = pEventData.GetString(pEventData.GetOrdinal("Body")); pEvent.MeetingFlag = ToBool(pEventData.GetInt32(pEventData.GetOrdinal("IsMeeting"))); pEvent.PrivateFlag = ToBool(pEventData.GetInt32(pEventData.GetOrdinal("IsPrivate"))); pEvent.Label = pEventData.GetInt32(pEventData.GetOrdinal("LabelID")); pEvent.BusyStatus = (CalendarEventBusyStatus)pEventData.GetInt32(pEventData.GetOrdinal("BusyStatus")); pEvent.Importance = (CalendarEventImportance)pEventData.GetInt32(pEventData.GetOrdinal("ImportanceLevel")); pEvent.StartTime = pEventData.GetDateTime(pEventData.GetOrdinal("StartDateTime")); pEvent.EndTime = pEventData.GetDateTime(pEventData.GetOrdinal("EndDateTime")); pEvent.AllDayEvent = ToBool(pEventData.GetInt32(pEventData.GetOrdinal("IsAllDayEvent"))); pEvent.Reminder = ToBool(pEventData.GetInt32(pEventData.GetOrdinal("IsReminder"))); pEvent.ReminderMinutesBeforeStart = pEventData.GetInt32(pEventData.GetOrdinal("ReminderMinutesBeforeStart")); pEvent.ReminderSoundFile = pEventData.GetString(pEventData.GetOrdinal("RemainderSoundFile")); pEvent.CustomProperties.LoadFromString(pEventData.GetString(pEventData.GetOrdinal("CustomPropertiesXMLData"))); pEvent.CustomIcons.LoadFromString(pEventData.GetString(pEventData.GetOrdinal("CustomIconsIDs"))); pEvent.ScheduleID = pEventData.GetInt32(pEventData.GetOrdinal("ScheduleID")); if (bRException) { pEvent.MakeAsRException(); pEvent.RExceptionStartTimeOrig = pEventData.GetDateTime(pEventData.GetOrdinal("RExceptionStartTimeOrig")); pEvent.RExceptionEndTimeOrig = pEventData.GetDateTime(pEventData.GetOrdinal("RExceptionEndTimeOrig")); pEvent.RExceptionDeleted = ToBool(pEventData.GetInt32(pEventData.GetOrdinal("ISRecurrenceExceptionDeleted"))); } if (!bRException) { // "process_RecurrenceState" and "process_RecurrencePatternID" properties // are used to process master events. // // If they are set and RecurrenceStaie is Master Data provider will // fier DoReadRPattern event and make event as Master. // And it will also generate occurrences for RetrieveDayEvents method. // // These properties are temporary and they will be removed by data provider. // // If these properties are not set data provider expect that master event // is already compleated - CreateRecurrence method is called and // Recurrence pattern is set. // // This mechanism is useful for DB data providers, when events and patterns // are stored separately (in tables). // But if events stored in some memory collection or array // it should not be used because master event store recurrence pattern inside. pEvent.CustomProperties["process_RecurrenceState"] = pEventData.GetValue(pEventData.GetOrdinal("RecurrenceState")); pEvent.CustomProperties["process_RecurrencePatternID"] = pEventData.GetValue(pEventData.GetOrdinal("RecurrencePatternID")); } return(pEvent); } catch (System.Exception e) { System.Diagnostics.Debug.WriteLine("EXCEPTION! SQLDataHelper.CreateEventFromRS: " + e.Message); } return(null); }
public CalendarRecurrencePattern CreateRPatternFromData(System.Data.OleDb.OleDbDataReader pRPatternData, System.Data.OleDb.OleDbConnection pOleDbConn) { try { CalendarRecurrencePattern pRPattern = null; int nPatternID; if (pRPatternData.IsClosed /*|| !pRPatternData.HasRows*/) { return(null); } nPatternID = pRPatternData.GetInt32(pRPatternData.GetOrdinal("RecurrencePatternID")); pRPattern = m_pCalendar.DataProvider.CreateRecurrencePattern(nPatternID); if (pRPattern == null) { return(null); } pRPattern.MasterEventId = pRPatternData.GetInt32(pRPatternData.GetOrdinal("MasterEventID")); pRPattern.StartTime = pRPatternData.GetDateTime(pRPatternData.GetOrdinal("EventStartTime")); pRPattern.DurationMinutes = pRPatternData.GetInt32(pRPatternData.GetOrdinal("EventDuration")); pRPattern.StartDate = pRPatternData.GetDateTime(pRPatternData.GetOrdinal("PatternStartDate")); pRPattern.EndMethod = (CalendarPatternEnd)pRPatternData.GetInt32(pRPatternData.GetOrdinal("PatternEndMethod")); if (pRPattern.EndMethod == CalendarPatternEnd.xtpCalendarPatternEndDate) { pRPattern.EndDate = pRPatternData.GetDateTime(pRPatternData.GetOrdinal("PatternEndDate")); } else if (pRPattern.EndMethod == CalendarPatternEnd.xtpCalendarPatternEndAfterOccurrences) { pRPattern.EndAfterOccurrences = pRPatternData.GetInt32(pRPatternData.GetOrdinal("PatternEndAfterOccurrences")); } else { System.Diagnostics.Debug.Assert(pRPattern.EndMethod == CalendarPatternEnd.xtpCalendarPatternEndNoDate); } pRPattern.Options.Data1 = pRPatternData.GetInt32(pRPatternData.GetOrdinal("OptionsData1")); pRPattern.Options.Data2 = pRPatternData.GetInt32(pRPatternData.GetOrdinal("OptionsData2")); pRPattern.Options.Data3 = pRPatternData.GetInt32(pRPatternData.GetOrdinal("OptionsData3")); pRPattern.Options.Data4 = pRPatternData.GetInt32(pRPatternData.GetOrdinal("OptionsData4")); pRPattern.CustomProperties.LoadFromString(pRPatternData.GetString(pRPatternData.GetOrdinal("CustomPropertiesXMLData"))); pRPatternData.Close(); ReadRPatternExceptions(pRPattern, pOleDbConn); return(pRPattern); } catch (System.Exception e) { System.Diagnostics.Debug.WriteLine("EXCEPTION! SQLDataHelper.CreateRPatternFromData: " + e.Message); } return(null); }