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