public void ChargerLesEmployesDesServices(MySqlConnection Cnx)
{
MySqlCommand Cmd = new MySqlCommand();
MySqlDataReader Rdr;
Cmd.Connection = Cnx;
foreach (Service s in LesServices)
{
Cmd.CommandText = "select * from employe where emp_service=s.id;";
Rdr = Cmd.ExecuteReader();
int i;
while (Rdr.Read())
{
i = 0;
while (i < LesEmployes.Count && LesEmployes[i].Id != Int32.Parse(Rdr.GetString(0)))
{
i++;
}
if (LesEmployes[i].Id == Int32.Parse(Rdr.GetString(0)))
{
s.LesEmployesDuService.Add(LesEmployes[i]);
}
Rdr.Close();
}
}
}
public void ChargerServices(MySqlConnection Cnx)
{
MySqlCommand Cmd = new MySqlCommand();
MySqlDataReader Rdr;
Service unServiceProduction;
Service unServiceCommercial;
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandText = "select * from service;";
Rdr = Cmd.ExecuteReader();
while (Rdr.Read())
{
if (!Rdr.IsDBNull(3) && !Rdr.IsDBNull(4))
{
unServiceProduction = new Service(Convert.ToInt32(Rdr["ser_id"]), Convert.ToString(Rdr["ser_designation"]), Convert.ToChar(Rdr["ser_type"]), Convert.ToString(Rdr["ser_produit"]), Convert.ToInt32(Rdr["ser_capacite"]));
LesServices.Add(unServiceProduction);
}
if (!Rdr.IsDBNull(5) && Rdr.IsDBNull(3) && Rdr.IsDBNull(4))
{
unServiceCommercial = new Service(Convert.ToInt32(Rdr["ser_id"]), Convert.ToString(Rdr["ser_designation"]), Convert.ToChar(Rdr["ser_type"]), Convert.ToInt32(Rdr["ser_budget"]));
LesServices.Add(unServiceCommercial);
}
}
Rdr.Close();
}
private void btnLogIn_Click(object sender, EventArgs e)
{
string Username = "", Password = "";
string dbUser = "", DbPassword = "";
bool UserFound = false;
bool Match = false;
Username = txtUserName.Text;
Password = TxtPassword.Text;
if (Username == "" || Password == "")
{
MessageBox.Show("Please enter both a Username and Password");
}
conn = new SqlConnection(constr);
SqlDataReader Rdr;
String sql_Max = "SELECT * FROM Users";
cmd = new SqlCommand(sql_Max, conn);
conn.Open();
Rdr = cmd.ExecuteReader();
if (Rdr.HasRows)
{
while (Rdr.Read())
{
dbUser = Rdr["Username"].ToString();
DbPassword = Rdr["Password"].ToString();
if (dbUser == Username)
{
UserFound = true;
if (DbPassword == Password)
{
Match = true;
}
else
{
MessageBox.Show("Incorrect Password");
}
}
}
}
conn.Close();
if (UserFound == false)
{
MessageBox.Show("User Not found");
}
if (Match == true)
{
Admin admin = new Admin();
admin.Show();
this.Hide();
}
}
public void ChargerEmployes(MySqlConnection Cnx)
{
MySqlCommand Cmd = new MySqlCommand();
MySqlDataReader Rdr;
Employe unEmploye;
Service unService;
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandText = "select * from employe";
Rdr = Cmd.ExecuteReader();
while (Rdr.Read())
{
for (int i = 0; i < LesServices.Count(); i++)
{
if (LesServices[i].Id == Convert.ToInt32(Rdr["emp_service"]))
{
unService = LesServices[i];
unEmploye = new Employe(Convert.ToInt32(Rdr["emp_id"]), Convert.ToString(Rdr["emp_nom"]), Convert.ToString(Rdr["emp_prenom"]), Convert.ToChar(Rdr["emp_sexe"]), Convert.ToByte(Rdr["emp_cadre"]), Convert.ToDecimal(Rdr["emp_salaire"]), unService);
LesEmployes.Add(unEmploye);
}
}
}
Rdr.Close();
}
/// <summary>
/// Reads a line from the file, splits it according to the file format, and returns the fields as a List of strings
/// </summary>
/// <exception cref="InvalidOperationException">if the file does not have a consistent format</exception>
/// <returns>the fields as a List of strings</returns>
public override List <string> ReadLine()
{
if (!IsParseable)
{
throw new InvalidOperationException("Attempt to read from a file that does not exhibit a consistent format");
}
string InputLine;
List <string> Fields = null;
while ((InputLine = Rdr.ReadLine()) != null)
{
++TotLinesRead;
TotBytesRead += InputLine.Length + 2; // CRLF
if (SkipLines != 0)
{
if (++LinesSkippedSoFar <= SkipLines)
{
continue;
}
}
if (RowsProcessed++ >= MaxRows)
{
break;
}
if (HasEOFStr && InputLine.StartsWith(EOFStr))
{
break;
}
Fields = ParseLine(InputLine.Replace("\t", TabReplacementString).TrimEnd());
break;
}
return(Fields);
}
/// <summary>
/// Releases resources held by the instance
/// </summary>
public void Dispose()
{
if (Rdr != null)
{
Rdr.Dispose();
Rdr = null;
}
}
public void TestRdr()
{
var rdr = new Rdr();
TestRoundTripEquality(rdr);
rdr.RetestBins = new ushort[] { 1, 2, 3, 4 };
TestRoundTripEquality(rdr);
}
public void fillBWICdate()
{
string[] txtFlds;
txtFlds = new string[2];
if (usingSQLServer == true)
{
SqlConnection cn = new SqlConnection("Data Source=ZM-SQL-1;" +
"Initial Catalog=ZM_GALLAGHER; Integrated Security=SSPI;");
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd = cn.CreateCommand();
cmd.CommandText = "delete from InventoryCriteriaSaved where criteria_sector='BWIC'" +
" and criteria_field='BWICID'";
cmd.ExecuteNonQuery();
cmd = cn.CreateCommand();
SqlDataReader Rdr;
string SQL = "SELECT DATE_ENTERED, COUNT(*) AS CNT FROM BWICINVENTORY ";
if (archived == false)
{
SQL += " where da_archived='' ";
}
SQL += " GROUP BY DATE_ENTERED ORDER BY DATE_ENTERED DESC;";
cmd.CommandText = SQL;
Rdr = cmd.ExecuteReader();
BWICdatecheckedListBox.Items.Clear();
while (Rdr.Read())
{
if (Rdr.GetValue(0).ToString().Equals(""))
{
BWICdatecheckedListBox.Items.Add("Missing", false);
}
else
{
txtFlds[0] = Rdr.GetValue(0).ToString();
txtFlds[1] = Rdr.GetValue(1).ToString();
BWICdatecheckedListBox.Items.Add(txtFlds[0] + ": \t" + txtFlds[1], false);
}
}
Rdr.Close();
}
}
public void ChargerDiplomes(MySqlConnection Cnx)
{
MySqlCommand Cmd = new MySqlCommand();
MySqlDataReader Rdr;
Diplome unDiplome;
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandText = "select * from diplome;";
Rdr = Cmd.ExecuteReader();
while (Rdr.Read())
{
unDiplome = new Diplome(Convert.ToInt32(Rdr["dip_id"]), Convert.ToString(Rdr["dip_libelle"]));
LesDiplomes.Add(unDiplome);
}
Rdr.Close();
}
//CONNECTION STRING ******************************************************************************************
// public const string conn = "Data Source=UNIAD01;Initial Catalog=Northwind_Ankit;User ID=sa;Password=sa";
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection Connect; //DECLARATION FOR OBJECT OF SQL CONNECTION
StringBuilder strBuild; //DECLARATION FOR OBJECT OF STRING BUILDER
SqlCommand Cmd; //DECLARATION FOR OBJECT OF SQLCOMMAND
SqlDataReader Rdr; //DECLARATION FOR OBJECT OF SQLDATAREADER
try
{
// Connect = new SqlConnection(conn);
// string con=ConfigurationSettings.AppSettings;//("MyConnection");
Connect = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString);
Connect.Open(); //OPEN THE DATABASE
strBuild = new StringBuilder(); //INITIALIZATION OF STRING BUILDER
strBuild.Append(Constant.strSelect); //APPENDING THE STRING BUILDER
strBuild.Append(Constant.strAllEmp); //APPENDING THE STRING BUILDER
strBuild.Append(Constant.strFrom); //APPENDING THE STRING BUILDER
strBuild.Append(Constant.strEmp); //APPENDING THE STRING BUILDER
Cmd = new SqlCommand(Convert.ToString(strBuild), Connect); //INITIALIZATION OF SQL COMMAND
Rdr = Cmd.ExecuteReader(); //TO EXECUTE THE COMMAND
gdvEmployee.DataSource = Rdr; //TO SEND THE CONTENT OF TABLE INTO GRIDVIEW
gdvEmployee.DataBind(); //TO BIND THE DATA OF TABLE INTO GRIDVIEW
Connect.Close(); //TO CLOSE THE DATABASE
if (gdvEmployee.Rows.Count == 0)
{
LblError.Text = Constant.strErr;
LblError.Visible = true;
}
if (Rdr == null)
{
Rdr.Close();
}
}
catch (Exception)
{
Response.Redirect("Error.aspx"); //REDIRECT TO ERROR.ASPX IF ANY ERROR IS OCCURED
}
finally
{
Connect = null; //DESTROYING THE OBJECT OF SQL CONNECTION
strBuild = null; //DESTROYING THE OBJECT OF STRING BUILDER
Cmd = null; //DESTROYING THE OBJECT OF SQLCOMMAND
Rdr = null; //DESTROYING THE OBJECT OF SQLDATAREADER
}
}
/// <summary>
/// Gets the next line from the input file. Handles cases where a field
/// contains an embedded CR by reading the next line (in that case) and appending
/// it to the current line. Note - in this case, the physical lines read won't
/// match the lines read reported by the caller...
/// </summary>
/// <returns>a line from the file</returns>
private string NextLine()
{
string InputLine = string.Empty;
do
{
string ThisLine = Rdr.ReadLine();
if (ThisLine == null)
{
break;
}
else
{
InputLine += ThisLine;
}
} while (HasLineBreakInQuotedField(InputLine));
return(InputLine == string.Empty ? null : InputLine);
}
public void ChargerEmployes(MySqlConnection Cnx)
{
Cnx.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Cnx;
cmd.CommandText = "employe";
cmd.CommandType = CommandType.TableDirect;
MySqlDataReader Rdr;
Rdr = cmd.ExecuteReader();
while (Rdr.Read())
{
Employe unEmploye = new Employe(Int32.Parse(Rdr.GetString(0)), Rdr.GetString(1), Rdr.GetString(2), Rdr.GetString(3), Decimal.Parse(Rdr.GetString(4)), Byte.Parse(Rdr.GetString(5)), null);
lesEmployes.Add(unEmploye);
}
Cnx.Close();
}
public void ChargerDiplomes(MySqlConnection Cnx)
{
Cnx.Open();
MySqlCommand cmd = new MySqlCommand();
string resultat = "";
cmd.Connection = Cnx;
cmd.CommandText = "diplome";
cmd.CommandType = CommandType.TableDirect;
MySqlDataReader Rdr;
Rdr = cmd.ExecuteReader();
while (Rdr.Read())
{
resultat += string.Format("{0}, {1}\n", Rdr[0], Rdr[1]);
}
Cnx.Close();
}
/*************************************************************************************************************
* FUNCTION NAME ::: bind
* AUTHOR NAME ::: ANKIT SHARMA
* DATE ::: 03/09/2012
* DESCRIPTION ::: TO MAKE A METHOD TO BIND THE GRIDVIEW
* **********************************************************************************************************/
public void Bind(GridView gvdEmp)
{
SqlConnection Connect; //DECLARATION FOR OBJECT OF SQL CONNECTION
StringBuilder strBuild; //DECLARATION FOR OBJECT OF STRING BUILDER
SqlCommand Cmd; //DECLARATION FOR OBJECT OF SQLCOMMAND
SqlDataReader Rdr; //DECLARATION FOR OBJECT OF SQLDATAREADER
Connect = new SqlConnection(ConfigurationManager.ConnectionStrings[Constant.strCon_Name].ConnectionString);
try
{
Connect.Open(); //OPEN THE DATABASE
strBuild = new StringBuilder(); //INITIALIZATION OF STRING BUILDER
if (Constant.BoolAlt)
{
strBuild.Append(Constant.strQuery1);
}
else
{
strBuild.Append(Constant.strQuery); //APPENDING THE STRING BUILDER
}
Cmd = new SqlCommand(Convert.ToString(strBuild), Connect); //INITIALIZATION OF SQL COMMAND
Rdr = Cmd.ExecuteReader(); //TO EXECUTE THE COMMAND
gvdEmp.DataSource = Rdr; //TO SEND THE CONTENT OF TABLE INTO GRIDVIEW
gvdEmp.DataBind(); //TO BIND THE DATA OF TABLE INTO GRIDVIEW
Connect.Close(); //TO CLOSE THE DATABASE connection
if (Rdr == null) //IF RDR IS NULL
{
Rdr.Close(); //CLOSE THE SQL DATA READER
}
}
finally
{
strBuild = null; //DESTROYING THE OBJECT OF STRING BUILDER
Cmd = null; //DESTROYING THE OBJECT OF SQLCOMMAND
Rdr = null; //ASSIGNING NULL TO SQL DATA READER
if (Connect.State != System.Data.ConnectionState.Closed) //CHECK IF CONNECTION IS NOT CLOSED
{
Connect.Close(); //CLOSING THE SQL CONNECTION
}
}
}
public void ChargerService(MySqlConnection Cnx)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Cnx;
cmd.CommandText = "service";
cmd.CommandType = CommandType.TableDirect;
MySqlDataReader Rdr;
Rdr = cmd.ExecuteReader();
while (Rdr.Read())
{
if (Rdr.GetString(2) == "P")
{
Service unService = new Service(Int32.Parse(Rdr.GetString(0)), Rdr.GetString(1), Rdr.GetString(2), Rdr.GetString(3), Int32.Parse(Rdr.GetString(4)));
lesServices.Add(unService);
}
else if (Rdr.GetString(2) == "A")
{
Service unService = new Service(Int32.Parse(Rdr.GetString(0)), Rdr.GetString(1), Rdr.GetString(2), Decimal.Parse(Rdr.GetString(5)));
lesServices.Add(unService);
}
}
}
private void buildBWICcriteria()
{
string SQL;
string sPiece;
int i;
string[] txtFlds;
txtFlds = new string[2];
//***
SQL = "select BWIC_NAME, COUNT(*) AS CNT from BWICinventory where 1=1 ";
i = 0;
//BWIC_NAME, COUNT(*) AS CNT FROM BWICINVENTORY GROUP BY BWIC_NAME ORDER BY BWIC_NAME ASC;
//mbsRangescheckedListBox.Items.Count
if (BWICdatecheckedListBox.CheckedItems.Count > 0)
{
SQL += " and FILE_DATE IN (";
while (i < BWICdatecheckedListBox.Items.Count)
{
if (BWICdatecheckedListBox.GetItemCheckState(i).ToString().Equals("Checked"))
{
sPiece = BWICdatecheckedListBox.Items[i].ToString();
if (sPiece.IndexOf(":") > 0)
{
sPiece = sPiece.Substring(0, sPiece.IndexOf(":"));
}
SQL += "'" + sPiece + "',";
}
i++;
}
SQL = SQL.Substring(0, SQL.Length - 1);
SQL += ") ";
}
SQL += " GROUP BY BWIC_NAME ORDER BY BWIC_NAME ASC;";
if (usingSQLServer == true)
{
SqlConnection cn = new SqlConnection("Data Source=ZM-SQL-1;" +
"Initial Catalog=ZM_GALLAGHER; Integrated Security=SSPI;");
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd = cn.CreateCommand();
cmd.CommandText = SQL;
SqlDataReader Rdr;
Rdr = cmd.ExecuteReader();
BWICListcheckedListBox.Items.Clear();
while (Rdr.Read())
{
if (Rdr.GetValue(0).ToString().Equals(""))
{
BWICListcheckedListBox.Items.Add("Missing", false);
}
else
{
txtFlds[0] = Rdr.GetValue(0).ToString();
txtFlds[1] = Rdr.GetValue(1).ToString();
BWICListcheckedListBox.Items.Add(txtFlds[0] + ": \t" + txtFlds[1], false);
}
}
Rdr.Close();
}
}
private void addBWICList(string savedSearch, string criteria)
{
if (criteria.IndexOf(":") > 0)
{
criteria = criteria.Substring(0, criteria.IndexOf(":"));
}
if (usingSQLServer == false)
{
}
if (usingSQLServer == true)
{
SqlConnection cn = new SqlConnection("Data Source=ZM-SQL-1;" +
"Initial Catalog=ZM_GALLAGHER; Integrated Security=SSPI;");
cn.Open();
SqlCommand cmd = new SqlCommand();
string SQL = "select count(*) from InventoryCriteriaSaved where criteria_sector='BWIC' and " +
" criteria_field='BWICID' and " +
" criteria_min='" + criteria + "' ";
//if (MuniradioButtonGroup.Checked == true)
//{
// SQL += " and criteria_searchowner='GROUP';";
//}
//else
//{
SQL += " and criteria_searchowner='" + userID + "';";
//}
cmd = cn.CreateCommand();
SqlDataReader Rdr;
cmd.CommandText = SQL;
Rdr = cmd.ExecuteReader();
Rdr.Read();
if (Rdr.GetValue(0).ToString().Equals("0"))
{
Rdr.Close();
//if (MuniradioButtonGroup.Checked == true)
//{
// cmd.CommandText = "insert into InventoryCriteriaSaved values ('MUNI','" + savedSearch +
// "','State','" + criteria + "', '','Text','GROUP');";
//}
//else
//{
cmd.CommandText = "insert into InventoryCriteriaSaved values ('BWIC','','BWICID','" + criteria + "', '','Text','" + userID + "');";
//}
cmd.ExecuteNonQuery();
}
else
{
Rdr.Close();
}
cn.Close();
}
}
static void Main(string[] args)
{
int choix;
string sCnx;
decimal mtBudget;
MySqlConnection Cnx;
MySqlCommand Cmd;
MySqlDataReader Rdr;
// chaîne de caractères de connexion
sCnx = "server=localhost;uid=root;database=gesper;port=3306;pwd=siojjr";
//création d'un objet connexion
Cnx = new MySqlConnection(sCnx);
//ouverture de la connexion
try
{
Cnx.Open();
Console.WriteLine("connexion réussie");
}
catch (Exception e)
{
Console.WriteLine("erreur connexion " + e.Message.ToString());
}
do
{
do
{
Console.WriteLine("1 - liste complète des employés (utiliser une requête)");
Console.WriteLine("2 - liste complète des services (utiliser la table, sans écrire de requête)");
Console.WriteLine("3 - budget moyen des services administratifs");
Console.WriteLine("4 - liste des employés ne possédant pas de diplome ");
Console.WriteLine("5 - fin du traitement");
Console.WriteLine();
choix = Int32.Parse(Console.ReadLine());
} while (choix < 0 || choix > 5);
switch (choix)
{
case 1:
Console.WriteLine("1 - liste complète des employés (utiliser une requête)");
// la commande
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "select * from employe;";
try
{
Rdr = Cmd.ExecuteReader();
while (Rdr.Read())
{
// avec le numéro de la colonne
Console.WriteLine(Rdr["emp_id"].ToString() + " " + Rdr["emp_nom"].ToString() + " " + Rdr["emp_prenom"].ToString() + " " + Rdr["emp_salaire"].ToString());
}
Rdr.Close();
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
Console.WriteLine();
break;
case 2:
Console.WriteLine("2 - liste complète des services (utiliser la table, sans écrire de requête)");
// la commande
Cmd = new MySqlCommand();
Cmd.CommandText = "service;";
Cmd.CommandType = CommandType.TableDirect;
try
{
Rdr = Cmd.ExecuteReader();
while (Rdr.Read())
{
Console.WriteLine(Rdr["ser_id"].ToString() + " " + Rdr["ser_designation"].ToString() + " " + Rdr["ser_type"].ToString());
}
Rdr.Close();
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
Console.WriteLine();
break;
case 3:
Console.WriteLine("3 - budget moyen des services administratifs");
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "select ser_designation, avg(ser_budget) from service where ser_type = 'A';";
try
{
mtBudget = Cmd.ExecuteScalar();
Console.WriteLine(mtBudget["ser_designation"].toString() + " " + mtBudget["avg(ser_budget)"].toString());
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
Console.WriteLine();
break;
case 4:
Console.WriteLine("4 - liste des employés ne possédant pas de diplome");
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "SELECT emp_nom,emp_prenom,ser_designation FROM service s INNER JOIN employe e on s.ser_id = e.emp_service WHERE emp_id NOT IN(SELECT pos_employe FROM posseder); ";
MySqlDataReader pasDiplome;
pasDiplome = Cmd.ExecuteReader();
try
{
while (pasDiplome.Read())
{
Console.WriteLine(pasDiplome["emp_nom"].ToString() + " " + pasDiplome["emp_prenom"].ToString());
}
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
pasDiplome.Close();
Console.WriteLine();
break;
case 5:
Console.WriteLine("Fin du traitement");
break;
}
}while (choix != 5);
Console.ReadLine();
}
