private Double tampilSisa()
{
double jmlsisa = 0;
string sqlTampil = "SELECT ij.idJaminan,ij.sisa FROM esdm_izin_jaminan ij " +
"INNER JOIN esdm_izin_perusahaan ip ON ij.idIzinPerusahaan = ip.idIzinPerusahaan " +
"INNER JOIN esdm_bank eb ON ij.idBank = eb.idBank " +
"WHERE ij.idJaminan='" + this.IdJaminan + "'";
Debug.Print(sqlTampil);
if (dbConnect.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(sqlTampil, dbConnect.connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
jmlsisa = Convert.ToDouble(dataReader["sisa"] + "");
}
dataReader.Close();
dbConnect.CloseConnection();
}
else
{
return(0);
}
dbConnect.CloseConnection();
return(jmlsisa);
}
public void tampilLokasi(string nama)
{
MySqlCommand cmd;
MySqlDataAdapter tampil;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strquery = "SELECT el.namaLokasi FROM esdm_perusahaan p " +
" INNER JOIN esdm_izin_perusahaan ip ON p.idPerusahaan = ip.idPerusahaan " +
" INNER JOIN esdm_lokasi el ON ip.idLokasi = el.idLokasi " +
" where p.namaPerusahaan like '%" + nama + "%'";
Debug.Print(strquery);
dbConnect.OpenConnection();
cmd = new MySqlCommand(strquery, dbConnect.connection);
tampil = new MySqlDataAdapter(cmd);
ds.Clear();
dt = new DataTable();
tampil.Fill(dt);
foreach (DataRow r in dt.Rows)
{
var rw = r.Field <string>("namaLokasi");
lbLokasi.Text = rw.ToString();
}
dbConnect.CloseConnection();
}
public void addToSql(Course c)
{
int courseID = 0;
string query = "INSERT INTO courses (COURSE_NAME,TEACHER,PRICE,DETAILS) VALUES('" + c.name + "','" + c.teacherName + "','" + c.price + "','" + c.details + "');";
//open connection
if (dbManager.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, dbManager.connection);
cmd.ExecuteNonQuery();
query = "SELECT LAST_INSERT_ID();";
cmd = new MySqlCommand(query, dbManager.connection);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
courseID = Int32.Parse(rdr.GetString(0));
}
foreach (KeyValuePair <string, string> entry in c.dayAndHour)
{
dbManager.CloseConnection();
dbManager.OpenConnection();
string day = entry.Key;
string hour = entry.Value;
Regex rgx = new Regex("[^a-zA-Z0-9 : -]");
day = rgx.Replace(day, "");
day = day.Substring(5);
hour = rgx.Replace(hour, "");
hour = hour.Substring(5);
query = "INSERT INTO dayofcourse (CID,DAY,HOUR) VALUES(" + courseID + ",'" + day + "','" + hour + "'); ";
cmd = new MySqlCommand(query, dbManager.connection);
cmd.ExecuteNonQuery();
}
dbManager.CloseConnection();
}
}
public void tampildata()
{
string sqlTampil = "SELECT il.idLokasi,kk.namaKabKota,il.alamatLokasi,kk.latKabKota,kk.longKabKota FROM esdm_lokasi il inner join esdm_kab_kota kk on il.idKabKota=kk.idKabKota";
Debug.Print(sqlTampil);
dbConnect.OpenConnection();
dbConnect.cmd = new MySqlCommand(sqlTampil, dbConnect.connection);
dbConnect.tampil = new MySqlDataAdapter(dbConnect.cmd);
dbConnect.ds.Clear();
dbConnect.tampil.Fill(dbConnect.ds);
dgvList.DataSource = dbConnect.ds.Tables[0];
dbConnect.CloseConnection();
}
public static List <Paciente> GetAll()
{
DBConnect db = new DBConnect();
try
{
List <Paciente> pacientes = new List <Paciente>();
string query = "SELECT * FROM pacientes;";
//Open connection
db.OpenConnection();
//Create Command
MySqlCommand cmd = new MySqlCommand(query, db.connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
var paciente = new Paciente();
paciente.cod_paciente = Convert.ToInt32(dataReader["cod_paciente"]);
paciente.nombre = Convert.ToString(dataReader["nombre"]);
paciente.Apellido = Convert.ToString(dataReader["Apellido"]);
paciente.ObraSocial = Convert.ToString(dataReader["obraSocial"]);
paciente.Sexo = Convert.ToString(dataReader["sexo"]);
pacientes.Add(paciente);
}
dataReader.Close();
db.CloseConnection();
return(pacientes);
}
catch (Exception ex)
{
db.CloseConnection();
throw new Exception(ex.Message);
}
finally
{
if (db.OpenConnection() == true)
{
db.CloseConnection();
}
;
}
}
public List <DrivingCycleSegment> ListSegments(string DcId)
{
List <DrivingCycleSegment> list = new List <DrivingCycleSegment>();
if (db.OpenConnection())
{
var query = "SELECT driving_cycle_segments.* FROM driving_cycle_segments INNER JOIN tbldriving_cycle_master ON driving_cycle_segments.Dc_Id=@DcId GROUP BY Seg_Id";
MySqlCommand cmd = new MySqlCommand(query, db.conn);
cmd.Parameters.AddWithValue("DcId", DcId);
try
{
MySqlDataReader datareader = cmd.ExecuteReader();
while (datareader.Read())
{
var data = new DrivingCycleSegment
{
SegID = datareader["Seg_Id"].ToString(),
AddedLoad = (int)(datareader["Added_Load"]),
Defaultload = (int)(datareader["Default_Load"]),
Gradient = (int)(datareader["Gradient"]),
GetRpm = (int)(datareader["Get_Rpm"]),
RunTime = Convert.ToDateTime(datareader["Run_Time"]),
};
list.Add(data);
}
datareader.Close();
}
catch (MySqlException ex)
{
throw new Exception(ex.Message);
}
catch (Exception em)
{
throw new Exception(em.Message);
}
db.CloseConnection();
return(list);
}
else
{
return(list);
}
}
private void unArchive()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE Remarks SET archived= 0 WHERE remarkId ='" + remarkId + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Remark is UnArchived");
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while executing the action!");
MessageBox.Show(ex.Message);
//throw;
}
loadRemark();
}
private void markNotCompleted()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE Remarks SET status = 'Not Reviewed' WHERE remarkId =" + remarkId;
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Remark is marked not Reviewed");
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while executing the action!");
MessageBox.Show(ex.Message);
//throw;
}
loadRemark();
}
private void lowPrioritize()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE Remarks SET priority= 3 WHERE remarkId =" + remarkId;
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Remark is marked as Low Priority Remark");
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while executing the action!");
MessageBox.Show(ex.Message);
//throw;
}
loadRemark();
}
private void editRawMaterial()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE Products_RawMaterials SET productId=@1,rawMaterialId=@2,quantity=@3,notes=@4 Where productId=@1 and rawMaterialId=@2";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
//cmd.CommandType = CommandType.Text; //default
cmd.Parameters.AddWithValue("@1", productId);
cmd.Parameters.AddWithValue("@2", rawMaterialId);
cmd.Parameters.AddWithValue("@3", numericUpDown1.Value);
cmd.Parameters.AddWithValue("@4", richTextBox1.Text);
//cmd.CommandType = CommandType.Text; //default
//connection.OpenConnection();
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Raw Material has been Edited!");
this.Close();
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while deleting the Raw Material!");
MessageBox.Show(ex.Message);
}
}
public List <Employee> GetAllEmployees()
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
string stm = "SELECT * FROM Employee";
MySqlCommand cmd = new MySqlCommand(stm, conn);
List <Employee> allEmployees = new List <Employee>();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
allEmployees.Add(new Employee()
{
employeeID = rdr.GetInt32(0), employeeName = rdr.GetString(1), employeePhone = rdr.GetString(2), employeeEmail = rdr.GetString(3), employeeAddress = rdr.GetString(4)
});
}
db.CloseConnection();
return(allEmployees);
}
}
else
{
return(new List <Employee>());
}
}
public DataSet getData(string table)
{
DBConnect conn = new DBConnect();
conn.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = conn.GetConnection();
if (returnConn.State.ToString() == "Closed")
{
returnConn.Open();
}
MySqlCommand newCmd = returnConn.CreateCommand();
newCmd.Connection = returnConn;
newCmd.CommandType = CommandType.Text;
newCmd.CommandText = "SELECT * FROM itp." + table;
MySqlDataAdapter da = new MySqlDataAdapter(newCmd);
DataSet ds = new DataSet();
da.Fill(ds, table);
returnConn.Close();
return(ds);
}
private void addProductSQL()
{
//SQL statemnts
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "INSERT INTO Products (name,description,notes,weight) VALUES (@1, @2, @3, @4)";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
//cmd.CommandType = CommandType.Text; //default
cmd.Parameters.AddWithValue("@1", textBox1.Text);
cmd.Parameters.AddWithValue("@2", textBox2.Text);
cmd.Parameters.AddWithValue("@3", richTextBox1.Text);
cmd.Parameters.AddWithValue("@4", numericUpDown1.Value);
//connection.OpenConnection();
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("New Product has been added!");
this.Close();
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while adding new Product!");
MessageBox.Show(ex.Message);
}
}
public string setAdvisor(int studentId, int newAdvisorId)
{
string response;
DBConnect setNewAdvisorDb = new DBConnect();
MySqlConnection newAdvisorConn = setNewAdvisorDb.getConn();
if (setNewAdvisorDb.OpenConnection() == true)
{
using (MySqlCommand cmd = new MySqlCommand("setAdvisor", newAdvisorConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("newadv", newAdvisorId);
cmd.Parameters.AddWithValue("stid", studentId);
cmd.ExecuteNonQuery();
}
response = "query succesful";
setNewAdvisorDb.CloseConnection();
return(response);
}
else
{
setNewAdvisorDb.CloseConnection();
return(error.badConn);
}
//setNewAdvisorDb.CloseConnection();
//return response;
}
public void AddTLI(TransactionLineItem value)
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"INSERT INTO TransactionLineItem(ProductID, ProductName, ProductPrice, ProductType, ProductDiscount, TransactionID)
VALUES(@ProductID, @ProductName, @ProductPrice, @ProductType, @ProductDiscount, @TransactionID)";
cmd.Parameters.AddWithValue("@ProductID", value.productID);
cmd.Parameters.AddWithValue("@ProductName", value.productName);
cmd.Parameters.AddWithValue("@ProductPrice", value.productPrice);
cmd.Parameters.AddWithValue("@ProductType", value.productType);
cmd.Parameters.AddWithValue("@ProductDiscount", value.productDiscount);
cmd.Parameters.AddWithValue("@TransactionID", value.transactionID);
cmd.Prepare();
cmd.ExecuteNonQuery();
//close connection
db.CloseConnection();
}
}
public Member GetMember(int memberID)
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
string stm = "SELECT * FROM Member WHERE MemberID = @MemberID";
MySqlCommand cmd = new MySqlCommand(stm, conn);
cmd.Parameters.AddWithValue("@MemberID", memberID);
cmd.Prepare();
using var rdr = cmd.ExecuteReader();
rdr.Read();
Member member = new Member()
{
memberID = rdr.GetInt32(0), memberName = rdr.GetString(1), memberAddress = rdr.GetString(2), memberEmail = rdr.GetString(3), memberDOB = rdr.GetString(4), memberPhone = rdr.GetString(5)
};
db.CloseConnection();
return(member);
}
else
{
return(new Member());
}
}
public List <Member> GetAllMembers()
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
string stm = "SELECT * FROM Member";
MySqlCommand cmd = new MySqlCommand(stm, conn);
List <Member> allMembers = new List <Member>();
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
allMembers.Add(new Member()
{
memberID = rdr.GetInt32(0), memberName = rdr.GetString(1), memberAddress = rdr.GetString(2), memberEmail = rdr.GetString(3), memberDOB = rdr.GetString(4), memberPhone = rdr.GetString(5)
});
}
db.CloseConnection();
return(allMembers);
}
}
else
{
return(new List <Member>());
}
}
private void loadRawMaterial()
{
try
{
DBConnect conn = new DBConnect();
conn.OpenConnection();
string query = "";
MySqlConnection returnConn = new MySqlConnection();
returnConn = conn.GetConnection();
query = Tquery;
//cmd.ExecuteNonQuery();
MySqlCommand cmd = new MySqlCommand(query, returnConn);
DataTable dt = new DataTable();
MySqlDataAdapter ada = new MySqlDataAdapter(cmd);
ada.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
conn.CloseConnection();
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while laoding rawMaterials!");
MessageBox.Show(ex.Message);
}
}
private void loadRawMaterialDetails()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "SELECT * FROM Products_RawMaterials WHERE productId = '" + productId + "' and rawMaterialId = '" + rawMaterialId + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
//cmd.CommandType = CommandType.Text; //default
//MySqlDataReader read;
using (MySqlDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
int checkNotes = read.GetOrdinal("notes");
//MessageBox.Show("" + read.GetInt32("quantity"));
richTextBox1.Text = read.IsDBNull(checkNotes) ? string.Empty : read.GetString("notes").ToString();
numericUpDown1.Value = read.GetInt32("quantity");
}
}
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while loading the product");
MessageBox.Show(ex.Message);
}
}
private void editRemark()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE Remarks SET title = '" + textBoxTitle.Text + "', notes = '" + richTextBoxNotes.Text + "', priority = " + numericUpDownPriority.Value + ", status = '" + comboBoxStatus.Text + "' WHERE remarkId =" + remarkId;
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Remark has been edited");
loadRemark();
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while executing the action!");
MessageBox.Show(ex.Message);
//throw;
}
}
public void AddMember(Member value)
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"INSERT INTO Member(MemberName, MemberAddress, MemberEmail, MemberDOB, MemberPhone)
VALUES(@MemberName, @MemberAddress, @MemberEmail, @MemberDOB, @MemberPhone)";
cmd.Parameters.AddWithValue("@MemberName", value.memberName);
cmd.Parameters.AddWithValue("@MemberAddress", value.memberAddress);
cmd.Parameters.AddWithValue("@MemberEmail", value.memberEmail);
cmd.Parameters.AddWithValue("@MemberDOB", value.memberDOB);
cmd.Parameters.AddWithValue("@MemberPhone", value.memberPhone);
cmd.Prepare();
cmd.ExecuteNonQuery();
//close connection
db.CloseConnection();
}
}
public string putSchedule(int courseId, int studentId, int scheduleYear, int scheduleTerm)
{
DBConnect putSchedDB = new DBConnect();
MySqlConnection putSchedConn = putSchedDB.getConn();
string response;
if (courseId == 0 || studentId == 0 || scheduleTerm == 0 || scheduleYear == 0)
{
return(error.badInfo);
}
if (putSchedDB.OpenConnection() == true)
{
using (MySqlCommand cmd = new MySqlCommand("putSchedule", putSchedConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("cid", courseId);
cmd.Parameters.AddWithValue("sid", studentId);
cmd.Parameters.AddWithValue("schyear", scheduleYear);
cmd.Parameters.AddWithValue("schterm", scheduleTerm);
cmd.ExecuteNonQuery();
}
response = "query successful";
putSchedDB.CloseConnection();
return(response);
}
else
{
putSchedDB.CloseConnection();
return(error.badConn);
}
//putSchedDB.CloseConnection();
//return response;
}
private void editProductSQL()
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE Products SET name='" + textBox1.Text + "', description='" + textBox2.Text + "', notes='" + richTextBox1.Text + "', weight='" + numericUpDown1.Value + "' WHERE productId ='" + productId + "'";;
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Product has been updated");
this.Close();
}
catch (Exception ex)
{
//MessageBox.Show("Something went wrong while updating the Product!");
MessageBox.Show(ex.Message);
//throw;
}
}
public void EditManager(Manager value)
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"UPDATE Manager SET ManagerName = @ManagerName, ManagerPhone = @ManagerPhone, ManagerEmail = @ManagerEmail, ManagerAddress = @ManagerAddress WHERE ManagerID = @ManagerID";
cmd.Parameters.AddWithValue("@ManagerID", value.managerID);
cmd.Parameters.AddWithValue("@ManagerName", value.managerName);
cmd.Parameters.AddWithValue("@ManagerPhone", value.managerPhone);
cmd.Parameters.AddWithValue("@ManagerEmail", value.managerEmail);
cmd.Parameters.AddWithValue("@ManagerAddress", value.managerAddress);
cmd.Prepare();
cmd.ExecuteNonQuery();
//close connection
db.CloseConnection();
}
}
static public void Parse(string path)
{
int fileIndex = 0;
DBConnect mysql = new DBConnect();
mysql.OpenConnection();
while (!shouldExit)
{
string fileName = path + getFileName(fileIndex);
if (File.Exists(fileName))
{
byte[] currentFile = File.ReadAllBytes(fileName);
//List<uint> blockIndexes = buildIndex(ref currentFile);
parseFileDataIntoClasses(ref currentFile, mysql);
fileIndex++;
Console.WriteLine("Finished proccessing file " + fileName);
}
else
{
shouldExit = true;
mysql.UnlockTables();
mysql.CloseConnection();
Console.WriteLine("Done.");
Console.WriteLine("Outputs" + ScryptParser.outputs.ToString());
Console.WriteLine("Invalid : " + ScryptParser.invalidOutputAddresses.ToString());
Console.WriteLine("Unparsible : " + ScryptParser.unparsibleOuptuAddresses.ToString());
Console.ReadLine();
}
}
}
public void AddProduct(Product value)
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"INSERT INTO Product(ProductName, ProductPrice, ProductType, ProductStatus, ProductDiscount, DateOrdered, DateAddedToInv, ManagerID, ManagerName, EmployeeID, EmployeeName)
VALUES(@ProductName, @ProductPrice, @ProductType, @ProductStatus, @ProductDiscount, @DateOrdered, @DateAddedToInv, @ManagerID, @ManagerName, @EmployeeID, @EmployeeName)";
cmd.Parameters.AddWithValue("@ProductName", value.productName);
cmd.Parameters.AddWithValue("@ProductPrice", value.productPrice);
cmd.Parameters.AddWithValue("@ProductType", value.productType);
cmd.Parameters.AddWithValue("@ProductStatus", "In Stock");
cmd.Parameters.AddWithValue("@ProductDiscount", " ");
cmd.Parameters.AddWithValue("@DateOrdered", value.dateOrdered);
cmd.Parameters.AddWithValue("@DateAddedToInv", DateTime.Now.ToString());
cmd.Parameters.AddWithValue("@ManagerID", value.managerID);
cmd.Parameters.AddWithValue("@ManagerName", value.managerName);
cmd.Parameters.AddWithValue("@EmployeeID", value.employeeID);
cmd.Parameters.AddWithValue("@EmployeeName", value.employeeName);
cmd.Prepare();
cmd.ExecuteNonQuery();
//close connection
db.CloseConnection();
}
}
public Employee GetEmployee(int employeeID)
{
DBConnect db = new DBConnect();
bool isOpen = db.OpenConnection();
if (isOpen)
{
MySqlConnection conn = db.GetConn();
string stm = "SELECT * FROM Employee WHERE EmployeeID = @EmployeeID";
MySqlCommand cmd = new MySqlCommand(stm, conn);
cmd.Parameters.AddWithValue("@EmployeeID", employeeID);
cmd.Prepare();
using var rdr = cmd.ExecuteReader();
rdr.Read();
Employee employee = new Employee()
{
employeeID = rdr.GetInt32(0), employeeName = rdr.GetString(1), employeePhone = rdr.GetString(2), employeeEmail = rdr.GetString(3), employeeAddress = rdr.GetString(4)
};
db.CloseConnection();
return(employee);
}
else
{
return(new Employee());
}
}
private void button1_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you really want to delete this entry from the table?", "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
try
{
DBConnect connection = new DBConnect();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "DELETE FROM itp.buyer WHERE storeName = '" + listBox1.Text + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.ExecuteNonQuery();
connection.CloseConnection();
PanMessage.Show(this.MdiParent, "Success", "Entry deleted successfully");
this.listBox1.Items.Clear();
fillList();
}
catch (MySqlException ex)
{
PanException.Show(this.MdiParent, ex);
}
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
string searchQuery = searchKey.Text;
try
{
DBConnect conn = new DBConnect();
conn.OpenConnection();
string query = "";
MySqlConnection returnConn = new MySqlConnection();
returnConn = conn.GetConnection();
//query = "SELECT * FROM itp.buyer WHERE @1 LIKE '" + searchQuery + "%'";
if (radioStore.Checked)
{
query = "SELECT * FROM itp.buyer WHERE storeName LIKE '" + searchQuery + "%'";
}
else if (radioContact.Checked)
{
query = "SELECT * FROM itp.buyer WHERE contactName LIKE '" + searchQuery + "%'";
}
else if (radioAdd.Checked)
{
query = "SELECT * FROM itp.buyer WHERE address LIKE '" + searchQuery + "%'";
}
else if (radioCity.Checked)
{
query = "SELECT * FROM itp.buyer WHERE city LIKE '" + searchQuery + "%'";
}
else if (radioDis.Checked)
{
query = "SELECT * FROM itp.buyer WHERE district LIKE '" + searchQuery + "%'";
}
else if (radioCredit.Checked)
{
query = "SELECT * FROM itp.buyer WHERE creditLimit LIKE '" + searchQuery + "%'";
}
//cmd.ExecuteNonQuery();
MySqlCommand cmd = new MySqlCommand(query, returnConn);
DataTable dt = new DataTable();
MySqlDataAdapter ada = new MySqlDataAdapter(cmd);
ada.Fill(dt);
viewTable.DataSource = dt;
conn.CloseConnection();
}
catch
{
}
}
public string putStudent(string sName, int aId, int mId, string sPin, string sTheme)
{
string response;
DBConnect putStuDb = new DBConnect();
MySqlConnection putStuConn = putStuDb.getConn();
if (putStuDb.OpenConnection() == true)
{
using (MySqlCommand cmd = new MySqlCommand("putStudent", putStuConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("sname", sName);
cmd.Parameters.AddWithValue("aid", aId);
cmd.Parameters.AddWithValue("mid", mId);
cmd.Parameters.AddWithValue("spin", sPin);
cmd.Parameters.AddWithValue("stheme", sTheme);
cmd.ExecuteNonQuery();
}
response = "query succesful";
putStuDb.CloseConnection();
return(response);
}
else
{
putStuDb.CloseConnection();
return(error.badConn);
}
}
