private void button4_Click(object sender, EventArgs e)
{
dataReader = dbbk.LeseSpezArtikel(artNr);
dataReader.Read();
int menge;
bool mengeBool = Int32.TryParse(textBox10.Text, out menge);
if (mengeBool)
{
if (menge <= dataReader.GetInt32(3))
{
//Datatypes festelgen
int i = dataGridView1.Rows.Add();
dataGridView1.Rows[i].Cells[0].ValueType = typeof(int);
dataGridView1.Rows[i].Cells[1].ValueType = typeof(string);
dataGridView1.Rows[i].Cells[2].ValueType = typeof(double);
dataGridView1.Rows[i].Cells[3].ValueType = typeof(int);
dataGridView1.Rows[i].Cells[4].ValueType = typeof(double);
//value inserten
dataGridView1.Rows[i].Cells[0].Value = artNr;
dataGridView1.Rows[i].Cells[1].Value = dataReader.GetString(2);
dataGridView1.Rows[i].Cells[2].Value = dataReader.GetDouble(4);
dataGridView1.Rows[i].Cells[3].Value = menge;
dataGridView1.Rows[i].Cells[4].Value = dataReader.GetDouble(4) * menge;
}
else
{
MessageBox.Show("Nicht genügend Artikel auf Lager!");
}
}
else
{
MessageBox.Show("Bitte geben sie eine gültige Mengeanzahl an");
}
dbbk.Schliessen();
BetragsBerechnung();
}
static public List <Grade> GetGradesFromDB(string Class, string course_code)
{
List <Grade> results = new List <Grade>();
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = SQL;
conn.Open();
OleDbCommand cmd = conn.CreateCommand();
OleDbParameter p1 = new OleDbParameter();
cmd.Parameters.Add(p1);
p1.Value = course_code;
OleDbParameter p2 = new OleDbParameter();
cmd.Parameters.Add(p2);
p2.Value = Class;
OleDbParameter p3 = new OleDbParameter();
cmd.Parameters.Add(p3);
p3.Value = Class;
cmd.CommandText = "SELECT * FROM Grade WHERE Grade.CodeCourse=? AND (Grade.Class=? OR Grade.Sub_Class=?)";
//Console.WriteLine(cmd.CommandText);
OleDbDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
var item = new Grade();
item.StudentID = rd.GetInt32(1);
item.StudentName = rd.GetString(2);
item.Mid_Term = (float)rd.GetDouble(4);
item.Final_Term = (float)rd.GetDouble(5);
item.Other_grade = (float)rd.GetDouble(6);
item.Sum_Grade = (float)rd.GetDouble(7);
results.Add(item);
}
return(results);
}
// This method accepts an id and returns the Dressshirt table entry that
// matches the id
public static DressShirt SelectDressShirt(string id)
{
// Set p to null
DressShirt p = null;
// Get a connection to DB
OleDbConnection connection = GetConnection();
// Create a query string
string select = "SELECT Product.ID, Product.Desc, Product.Price, "
+ "Product.Qty, Product.Type, Apparel.Material, Apparel.Color, Apparel.Manufacturer, "
+ "DressShirt.Neck, DressShirt.Sleeve FROM (Product INNER JOIN Apparel ON Product.ID = Apparel.ID) "
+ "INNER JOIN DressShirt ON Apparel.ID = DressShirt.ID WHERE Product.ID = '" + id + "';";
// Declare and instantiate a command object
OleDbCommand command = new OleDbCommand(select, connection);
try
{
// Open connection
connection.Open();
// Use command to execute a query with results in reader
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read()) // This reads and checks if a record need to be processed
{
// Instantiate and copy data from reader
p = new DressShirt();
p.ID = reader.GetString(0);
p.Desc = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.Qty = reader.GetInt32(3);
p.Type = reader.GetString(4);
p.Material = reader.GetString(5);
p.Color = reader.GetString(6);
p.Manufacturer = reader.GetString(7);
p.Neck = reader.GetInt32(8);
p.Sleeve = reader.GetInt32(9);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
p = null;// In case the complete record was not successfully copied
}
finally
{
// Close connection
if (connection != null)
{
connection.Close();
}
}
// Will contain a Dressshirt if found and null if not
return(p);
}
public static Music SelectMusic(string id)
{
Music p = null;
OleDbConnection connection = GetConnection();
string select = "SELECT Product.ID, Product.Desc, Product.Price, "
+ "Product.Qty, Product.Type, Media.ReleaseDate, Disk.SizeDisk, Disk.NumDisks, "
+ "Disk.TypeDisk, Entertainment.Hours, Entertainment.Minutes, Entertainment.Seconds, "
+ "Music.Genre, Music.Artist, Music.Label FROM"
+ "(((Product INNER JOIN Media ON Product.ID = Media.ID)"
+ "INNER JOIN Disk ON Media.ID = Disk.ID) "
+ "INNER JOIN Entertainment ON Disk.ID = Entertainment.ID) "
+ "INNER JOIN Music ON Entertainment.ID = Music.ID WHERE Product.ID = '" + id + "';";
OleDbCommand command = new OleDbCommand(select, connection);
try
{
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
p = new Music();
p.ID = reader.GetString(0);
p.Desc = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.Qty = reader.GetInt32(3);
p.Type = reader.GetString(4);
p.ReleaseDate = reader.GetDateTime(5).ToShortDateString();
p.Size = reader.GetInt32(6);
p.NumDisks = reader.GetInt32(7);
p.TypeDisk = reader.GetString(8);
string runTime = reader.GetInt32(9) + ":" + reader.GetInt32(10) + ":" + reader.GetInt32(11);
p.RunTime = runTime;
p.Genre = reader.GetString(12);
p.Artist = reader.GetString(13);
p.Label = reader.GetString(14);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
p = null;
}
finally
{
connection.Close();
}
return(p);
}
private void populateItemDetail(string itemName, string date)
{
string sqlQry = "SELECT tDate, Pcode as Item, Tno as TagNo, MetalType, Pcs, GW,NW FROM TRANS WHERE PCODE = '" + itemName + "' AND (OUTDATE IS NULL OR OUTDATE >= #" + date + "#) AND TDATE <= #" + date + "#";
OleDbDataReader reader = dbUtils.fetch(sqlQry);
int columnCount = reader.FieldCount;
/* dgItemDetail.Columns.Clear();
* for (int i = 0; i < columnCount; i++)
* {
* dgItemDetail.Columns.Add(reader.GetName(i).ToString(), reader.GetName(i).ToString());
* }*/
dgItemDetail.Rows.Clear();
string[] rowData = new string[columnCount];
while (reader.Read())
{
for (int k = 0; k < columnCount; k++)
{
if (reader.GetFieldType(k).ToString() == "System.Int16")
{
rowData[k] = reader.GetInt16(k).ToString();
}
if (reader.GetFieldType(k).ToString() == "System.Int32")
{
rowData[k] = reader.GetInt32(k).ToString();
}
if (reader.GetFieldType(k).ToString() == "System.Int64")
{
rowData[k] = reader.GetInt64(k).ToString();
}
if (reader.GetFieldType(k).ToString() == "System.String")
{
rowData[k] = reader.GetString(k);
}
if (reader.GetFieldType(k).ToString() == "System.Double")
{
rowData[k] = dbUtils.Decimal3digit(reader.GetDouble(k).ToString());
}
if (reader.GetFieldType(k).ToString() == "System.DateTime")
{
rowData[k] = reader.GetDateTime(k).ToShortDateString();
}
}
dgItemDetail.Rows.Add(rowData);
}
reader.Close();
reader = null;
}
private void getItemDetail(string itemName, string date, DataTable stockTable)
{
string sqlQry = "SELECT * FROM TRANS WHERE PCODE = '" + itemName + "' AND (OUTDATE IS NULL OR OUTDATE > #" + date + "#) ORDER BY PCODE,TNO";
OleDbDataReader reader = dbUtils.fetch(sqlQry);
List <string> stoneWeight = null;
string path = string.Empty;
DataRow row;
if (reader.HasRows)
{
while (reader.Read())
{
row = stockTable.NewRow();
if (!reader["Photo"].Equals(DBNull.Value) && reader.GetString(8).Length > 0)
{
path = Application.StartupPath + "//Data//Images//" + reader.GetString(8); //photo
row[0] = path;
}
else
{
row[0] = string.Empty;
}
row[1] = itemName;
row[2] = reader.GetDouble(6);
stoneWeight = getStoneDetail(reader.GetInt32(0));
row[3] = stoneWeight[0];
row[4] = stoneWeight[1];
row[5] = reader.GetDouble(7);
row[6] = reader.GetInt32(3);
row[7] = reader.GetInt32(0);
stockTable.Rows.Add(row);
//MessageBox.Show(reader.GetDataTypeName(11).ToString());
if (chkComposite.Checked && !reader["SubItem"].Equals(DBNull.Value) && reader.GetByte(11) == 1)
{
getSubItemDetail(reader.GetInt32(0).ToString(), subItemTable);
}
}
}
}
//============================================================================
/// <summary>
/// Get the double value of this column.
/// </summary>
/// <param name="col">Column index 0->n</param>
/// <returns>The value. 0 if not found.</returns>
//============================================================================
public override double ColAsFloat(int col)
{
double result = 0;
if (reader != null)
{
if (reader.FieldCount >= col + 1)
{
result = reader.GetDouble(col);
}
}
return(result);
}
public Produit QueryProduit(OleDbConnection DB, string SKUProduit)
{
Produit Pro = new Produit();
string cmd1 = "SELECT * FROM PRODUIT WHERE ID_PRODUIT=?";
string cmd2 = "SELECT * FROM TAILLE_PRODUIT WHERE ID_PRODUIT=?";
try
{
OleDbCommand sqlcmd1 = new OleDbCommand(cmd1, DB);
sqlcmd1.Parameters.AddWithValue("?", SKUProduit);
OleDbDataReader Dr1 = sqlcmd1.ExecuteReader();
Dr1.Read();
Pro.VendorSKU = Dr1.GetString(0);
Pro.Collection = Dr1.GetString(1);
Pro.Designation = Dr1.GetString(2);
Pro.Origine = Dr1.GetString(3);
Pro.Composition = Dr1.GetString(4);
Pro.Longeur = Dr1.GetInt32(5).ToString();
Pro.Poid = Dr1.GetDouble(6).ToString();
Pro.PrixAchat = Dr1.GetDouble(8).ToString();
Pro.Marque = Dr1.GetString(9);
OleDbCommand sqlcmd2 = new OleDbCommand(cmd2, DB);
sqlcmd2.Parameters.AddWithValue("?", SKUProduit);
OleDbDataReader Dr2 = sqlcmd2.ExecuteReader();
while (Dr2.Read())
{
Pro.VendorSize.Add(Dr2.GetString(1));
}
return(Pro);
}catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
public bool GetLatLngProb(WorkData tempData)
{
bool success = false;
OleDbDataReader latReader = null;
if (connection == null)
{
GetConnection();
}
try
{
selectProbCim.Parameters[0].Value = tempData.Cim;
tempData.Problematic = selectProbCim.ExecuteScalar() != null;
getLatLongData.Parameters[0].Value = tempData.Utca;
latReader = getLatLongData.ExecuteReader();
if (latReader.Read())
{
tempData.Lat = latReader.GetDouble(0);
tempData.Lng = latReader.GetDouble(1);
success = true;
}
}
catch (OleDbException ole)
{
}
finally
{
if (latReader != null && !latReader.IsClosed)
{
latReader.Close();
}
}
return(success);
}
public void GetLatLng(WorkData tempData, OleDbTransaction tr)
{
OleDbDataReader latReader = null;
if (connection == null)
{
GetConnection();
}
try
{
if (tr != null)
{
getLatLongData.Transaction = tr;
}
getLatLongData.Parameters[0].Value = tempData.Utca;
latReader = getLatLongData.ExecuteReader();
if (latReader.Read())
{
tempData.Lat = latReader.GetDouble(0);
tempData.Lng = latReader.GetDouble(1);
}
}
catch (OleDbException ole)
{
}
finally
{
if (latReader != null && !latReader.IsClosed)
{
latReader.Close();
}
}
}
// This method accepts an id and returns the Pants table entry that
// matches the id
public static Pants SelectPants(string id)
{
// Set p to null
Pants p = null;
// Get a connection to DB
OleDbConnection connection = GetConnection();
// Create a query string
string select = "SELECT Product.ID, Product.Desc, Product.Price, "
+ "Product.Qty, Product.Type, Apparel.Material, Apparel.Color, Apparel.Manufacturer, "
+ "Pants.Waist, Pants.Inseam FROM (Product INNER JOIN Apparel ON Product.ID = Apparel.ID)"
+ "INNER JOIN Pants ON Apparel.ID = Pants.ID WHERE Product.ID = '" + id + "';";
// Declare and instantiate a command object
OleDbCommand command = new OleDbCommand(select, connection);
try
{
// Open connection
connection.Open();
// Use command to execute a query with results in reader
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
// Instantiate and copy data from reader
p = new Pants();
p.ID = reader.GetString(0);
p.Desc = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.Qty = reader.GetInt32(3);
p.Type = reader.GetString(4);
p.Material = reader.GetString(5);
p.Color = reader.GetString(6);
p.Manufacturer = reader.GetString(7);
p.Waist = reader.GetInt32(8);
p.Inseam = reader.GetInt32(9);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
p = null; // In case the complete record was not successfully copied
}
finally
{
// Close connection
connection.Close();
}
// Will contain a Pant if found and null if not
return(p);
}
public static FoodStuff GetFoodstuffWithID(string fsID)
{
//really simple, match the foodstuff ID passed in with the ID in the table and return it.
FoodStuff fs = new FoodStuff();
try
{
string query = "Select * from Foodstuff where FoodstuffID like ?";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fsID;
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read();
string id = reader.IsDBNull(0) ? "" : reader.GetString(0);
List <Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
string[] strTagList = reader.IsDBNull(7) ? new string[0] : reader.GetString(7).Split(',');
if (strTagList.Length != 0)
{
foreach (string t in strTagList)
{
fs.AddTag(t);
}
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
DataConnection.CloseConnection();
}
return(fs);
}
private static IEnumerable <EcCard> LoadFromReader(OleDbDataReader reader)
{
if (reader == null)
{
yield break;
}
int idIndex = reader.GetOrdinal("ID");
int custIndex = reader.GetOrdinal("KndNr");
int projIndex = reader.GetOrdinal("ProjNr");
int cardNoIndex = reader.GetOrdinal("Kartennr");
int cardIdIndex = reader.GetOrdinal("CardZK");
int textIndex = reader.GetOrdinal("Text");
int[] flagIndexes = new int[13];
for (int i = 0; i < FlagNames.Length; i++)
{
flagIndexes[i] = reader.GetOrdinal(FlagNames[i]);
}
while (reader.Read())
{
var card = new EcCard();
card.ID = reader.GetInt32(idIndex);
card.CustomerNumber = (int)reader.GetDouble(custIndex);
card.ProjectNumber = (int)reader.GetDouble(projIndex);
card.CardNumber = reader.GetInt16(cardNoIndex);
card.CardIdentity = reader.GetString(cardIdIndex);
card.Text = reader.GetValue(textIndex) as string;
for (int i = 0; i < FlagNames.Length; i++)
{
card._flags[i] = reader.GetBoolean(flagIndexes[i]);
}
yield return(card);
}
}
public double getAvgSpeed()
{
string sql = "SELECT AVG(v) FROM [VEH_RECORD]";
OleDbCommand mycom = new OleDbCommand(sql, mycon);
myReader = mycom.ExecuteReader();
double type = -1;
if (myReader.HasRows)
{
myReader.Read();
type = myReader.GetDouble(0);
}
return(type);
}
public double getAvgQueueLengh()
{
string sql = "SELECT AVG(Avg_) FROM [QUEUELENGTH]";
OleDbCommand mycom = new OleDbCommand(sql, mycon);
myReader = mycom.ExecuteReader();
double type = -1;
if (myReader.HasRows)
{
myReader.Read();
type = myReader.GetDouble(0);
}
return(type);
}
private void getSubItemDetail(string Tid, DataTable subItemTable)
{
string sqlQry = "SELECT * FROM SUBITEMTRANS WHERE TID = " + Tid + " ORDER BY IDESC";
OleDbDataReader reader = dbUtils.fetch(sqlQry);
List <string> subStoneWeight = null;
string path = string.Empty;
DataRow row;
if (reader.HasRows)
{
while (reader.Read())
{
row = subItemTable.NewRow();
row[0] = reader.GetString(2);
row[1] = reader.GetDouble(4);
subStoneWeight = getSubItemStoneDetail(reader.GetInt32(0));
row[2] = subStoneWeight[0];
row[3] = subStoneWeight[1];
row[4] = reader.GetDouble(5);
row[5] = reader.GetInt32(1);
subItemTable.Rows.Add(row);
}
}
}
public double getAvgDelayTime()
{
string sql = "SELECT AVG(Delay) FROM [DELAYTIMES]";
OleDbCommand mycom = new OleDbCommand(sql, mycon);
myReader = mycom.ExecuteReader();
double type = -1;
if (myReader.HasRows)
{
myReader.Read();
type = myReader.GetDouble(0);
}
return(type);
}
public Dictionary <string, double> GetTestFileAndNumberPairDictionary()
{
string databaseConnectionString = SPFileManagementUtils.ConnectionStringSPTestDatabase;
using (OleDbConnection connection = new OleDbConnection(databaseConnectionString))
{
string sqlQuery = getSqlQuery();
OleDbCommand command = new OleDbCommand(sqlQuery, connection);
connection.Open();
OleDbDataReader dataReader = command.ExecuteReader();
Dictionary <string, double> testFilePathAndNumberDictionary = new Dictionary <string, double> ();
int desiredColumnOrdinalNumber = dataReader.GetOrdinal(_desiredDetailTableColumnHeader);
while (dataReader.Read())
{
string cusip = dataReader ["Cusip"].ToString();
string filePath = TestUtils.GetTestStringFilePathForGivenCusip(cusip);
double detailBeingExtracted;
try
{
detailBeingExtracted = dataReader.GetDouble(desiredColumnOrdinalNumber);
}
catch (System.InvalidCastException)
{
string debugMsg = String.Format("Unable to cast value for column '{0}' to double so attempting to cast as decimal first.", _desiredDetailTableColumnHeader);
Debug.WriteLine(debugMsg);
try
{
detailBeingExtracted = (double)dataReader.GetDecimal(desiredColumnOrdinalNumber);
}
catch (System.InvalidCastException)
{
debugMsg = String.Format("Unable to cast value for column '{0}' to decimal first either so attempting to cast as integer now.", _desiredDetailTableColumnHeader);
detailBeingExtracted = (double)dataReader.GetInt32(desiredColumnOrdinalNumber);
}
catch (Exception e)
{
string errorMsg = "Unhandled exception encountered in GetTestFileAndNumberPairDictionary() " +
"method of AccessDatabaseNumericalStructureDetailSource class.\nSql query = " + sqlQuery + "\nError details: " + e.ToString();
Debug.WriteLine(errorMsg);
throw;
}
}
testFilePathAndNumberDictionary.Add(filePath, detailBeingExtracted);
}
return(testFilePathAndNumberDictionary);
}
}
private void cargarLista()
{
ctdor = 0;
lstPersonas.Items.Clear();
abrirConexion(cadenaConexion);
comm.CommandText = "SELECT * FROM Personas";
lector = comm.ExecuteReader();
while (lector.Read())
{
Persona p = new Persona();
if (!lector.IsDBNull(0))
{
p.pDocumento = lector.GetDouble(0);
}
if (!lector.IsDBNull(1))
{
p.pApellido = lector.GetString(1);
}
if (!lector.IsDBNull(2))
{
p.pNombres = lector.GetString(2);
}
if (!lector.IsDBNull(3))
{
p.pTipoDoc = lector.GetInt16(3);
}
if (!lector.IsDBNull(4))
{
p.pEstCivil = lector.GetInt16(4);
}
if (!lector.IsDBNull(5))
{
p.pSexo = lector.GetInt16(5);
}
if (!lector.IsDBNull(6))
{
p.pFallecio = lector.GetInt16(6);
}
aPersona[ctdor] = p;
ctdor++;
}
lector.Close();
conex.Close();
for (int i = 0; i < ctdor; i++)
{
lstPersonas.Items.Add(aPersona[i].ToString());
}
}
public List <int> find_List_MatPart_byMatricule_Adherent(int matricule)
{
List <int> list = new List <int>();
string req = string.Format("select participant.matricule from Participant , adherent , conjoint , enfant where Adherent.matriculeEtap=conjoint.matricule and Adherent.matriculeEtap=enfant.matricule and Adherent.matriculeEtap=" + matricule + " and enfant.matricule=" + matricule + " and conjoint.matricule=" + matricule + " and participant.matricule=enfant.id union all select distinct participant.matricule from Participant , adherent, conjoint, enfant where Adherent.matriculeEtap = conjoint.matricule and Adherent.matriculeEtap = enfant.matricule and Adherent.matriculeEtap = " + matricule + " and enfant.matricule =" + matricule + " and conjoint.matricule =" + matricule + " and participant.matricule = conjoint.cin");
cn.Open();
cmd = new OleDbCommand(req, cn);
OleDbDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
list.Add((int)Reader.GetDouble(0));
}
Reader.Close();
cn.Close();
return(list);
}
public static Bus FindBusI(string studenID)
{
Bus bs = null;
string ss = "select * from Bus where Sid = @sid ";
OleDbCommand cmd = new OleDbCommand(ss, ConnectionClass.publicConnection);
cmd.Parameters.AddWithValue("@sid", studenID);
OleDbDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
bs = new Bus(rd.GetString(0), rd.GetString(1), rd.GetInt32(2),
rd.GetDateTime(3), rd.GetDouble(4), rd.GetString(5));
}
rd.Close();
return(bs);
}
public BudgetCategorie getBudgetCategorieByIdActivite(int idActivite)
{
BudgetCategorie budgetCategorie = new BudgetCategorie();
string req = string.Format("select budgetCategorie.id , budgetCategorie.provisoire from budgetCategorie , Activite where budgetCategorie.id=Activite.idBudgetCat and Activite.id=" + idActivite);
cn.Open();
cmd = new OleDbCommand(req, cn);
OleDbDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
budgetCategorie = new BudgetCategorie((int)Reader.GetDecimal(0), Reader.GetDouble(1));
}
Reader.Close();
cn.Close();
return(budgetCategorie);
}
public double getMontantProvisoire(int annee, String categorie)
{
double provisoire = 0;
string req = string.Format("select BudgetCategorie.Provisoire from BudgetCategorie , Budget where (Budget.id=BudgetCategorie.idBudget) and (Budget.annee=" + annee + ") and (BudgetCategorie.Categorie='" + categorie + "')");
cn.Open();
cmd = new OleDbCommand(req, cn);
OleDbDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
provisoire = Reader.GetDouble(0);
}
Reader.Close();
cn.Close();
return(provisoire);
}
public static Software SelectSoftware(string id)
{
Software p = null;
OleDbConnection connection = GetConnection();
string select = "SELECT Product.ID, Product.Desc, Product.Price, "
+ "Product.Qty, Product.Type, Media.ReleaseDate, Disk.SizeDisk, Disk.NumDisks, "
+ "Disk.TypeDisk, Software.TypeSoft FROM ((Product INNER JOIN Media ON Product.ID = Media.ID)"
+ "INNER JOIN Disk ON Media.ID = Disk.ID) "
+ "INNER JOIN Software ON Disk.ID = Software.ID WHERE Product.ID = '" + id + "';";
OleDbCommand command = new OleDbCommand(select, connection);
try
{
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
p = new Software();
p.ID = reader.GetString(0);
p.Desc = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.Qty = reader.GetInt32(3);
p.Type = reader.GetString(4);
p.ReleaseDate = reader.GetDateTime(5).ToShortDateString();
p.Size = reader.GetInt32(6);
p.NumDisks = reader.GetInt32(7);
p.TypeDisk = reader.GetString(8);
p.TypeSoft = reader.GetString(9);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
p = null;
}
finally
{
connection.Close();
}
return(p);
}
public static List <Library> FindAllBooks(string studenID)
{
List <Library> sl = new List <Library>();
string ss = "select * from Library where SId = @sid ";
OleDbCommand cmd = new OleDbCommand(ss, ConnectionClass.publicConnection);
cmd.Parameters.AddWithValue("@sid", studenID);
OleDbDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
Library s = new Library(rd.GetString(0), rd.GetString(2),
rd.GetDateTime(3), rd.GetDateTime(4), rd.GetDouble(5), rd.GetString(6));
sl.Add(s);
}
rd.Close();
return(sl);
}
public TTPhongKhamDTO getTTPK()
{
TTPhongKhamDTO ttpk = new TTPhongKhamDTO();
OleDbConnection connection = openConnection();
string sql = "SELECT So_Luong_Kham, Phi_Kham_Benh from Thong_tin_phong_kham where ID=1";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ttpk.SoLuong = int.Parse(reader.GetInt32(0).ToString());
ttpk.TienKham = float.Parse(reader.GetDouble(1).ToString());
}
reader.Close();
return(ttpk);
}
public ConnectAccess(List <Professeur> listprof)
{
ListProf = listprof;
OleDbConnection connection = new OleDbConnection(ConnectionString);
//MessageBox.Show("Connection a la base de donnée.");
try // essaye.
{
connection.Open();
const string SQL = "SELECT * FROM Professeur";
OleDbCommand SelectProf = new OleDbCommand(SQL, connection);
OleDbDataReader ReadSQL = SelectProf.ExecuteReader();
if (ReadSQL.HasRows)
{
while (ReadSQL.Read())
{
Professeur Prof = new Professeur();
Prof.idProf = ReadSQL.GetInt32(0);
Prof.Nom = ReadSQL.GetString(1);
Prof.Prenom = ReadSQL.GetString(2);
Prof.AnneeNaissance = ReadSQL.GetInt32(3);
Prof.SalaireMensuel = ReadSQL.GetDouble(4);
ListProf.Add(Prof);
}
ReadSQL.Close();
}
}
catch (Exception ex)//si erreur.
{
MessageBox.Show("Connection a la base de donnée:Erreur:" + "\n" + ex.Message);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public static Pants SelectPants(string id)
{
Pants p = null;
OleDbConnection connection = GetConnection();
string select = "SELECT Product.ID, Product.Desc, Product.Price, "
+ "Product.Qty, Product.Type, Apparel.Material, Apparel.Color, Apparel.Manufacturer, "
+ "Pants.Waist, Pants.Inseam FROM (Product INNER JOIN Apparel ON Product.ID = Apparel.ID)"
+ "INNER JOIN Pants ON Apparel.ID = Pants.ID WHERE Product.ID = '" + id + "';";
OleDbCommand command = new OleDbCommand(select, connection);
try
{
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
p = new Pants();
p.ID = reader.GetString(0);
p.Desc = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.Qty = reader.GetInt32(3);
p.Type = reader.GetString(4);
p.Material = reader.GetString(5);
p.Color = reader.GetString(6);
p.Manufacturer = reader.GetString(7);
p.Waist = reader.GetInt32(8);
p.Inseam = reader.GetInt32(9);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
p = null;
}
finally
{
connection.Close();
}
return(p);
}
public void listaTabella(string tabName)
{
if (conStr != null)
{
OleDbConnection con = new OleDbConnection(conStr);
using (con)
{
con.Open();
OleDbCommand cmd = new OleDbCommand($"SELECT * FROM {tabName}", con);
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
Console.WriteLine("\n");
while (reader.Read())
{
string immatricolazione = reader.GetDateTime(6).ToShortDateString();
string final = $"{reader.GetInt32(0)} - {reader.GetString(1)} - {reader.GetString(2)} - " +
$"{reader.GetString(3)} - {reader.GetInt32(4)} - {reader.GetDouble(5)} - " +
$"{immatricolazione} - {reader.GetString(7)} - {reader.GetString(8)} - " +
$"{reader.GetInt32(9)} - {reader.GetDouble(10)}";
if (tabName == "AUTO")
{
final += $" - {reader.GetInt32(11)}";
}
else
{
final += $" - {reader.GetString(11)}";
}
Console.WriteLine(final);
System.Threading.Thread.Sleep(6000);
}
}
else
{
Console.WriteLine("\n\nNessun risultato.");
}
reader.Close();
}
}
}
public static Book SelectBook(string id)
{
Book p = null;
OleDbConnection connection = GetConnection();
string select = "SELECT Product.ID, Product.Desc, Product.Price, "
+ "Product.Qty, Product.Type, Media.ReleaseDate, Book.NumPages, Book.Author, "
+ "Book.Publisher FROM(Product INNER JOIN Media ON Product.ID = Media.ID) "
+ "INNER JOIN Book ON Media.ID = Book.ID WHERE Product.ID = '" + id + "';";
OleDbCommand command = new OleDbCommand(select, connection);
try
{
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
p = new Book();
p.ID = reader.GetString(0);
p.Desc = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.Qty = reader.GetInt32(3);
p.Type = reader.GetString(4);
p.ReleaseDate = reader.GetDateTime(5).ToShortDateString();
p.NumPages = reader.GetInt32(6);
p.Author = reader.GetString(7);
p.Publisher = reader.GetString(8);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
p = null;
}
finally
{
connection.Close();
}
return(p);
}
