protected void gvClinetInv_RowUpdating(object sender, GridViewUpdateEventArgs e) { // Create object from dal and fetching data after updating in gridview and send it to update. ClientsInvoicesDAL clientsInvoicesDAL = new ClientsInvoicesDAL(); clientsInvoicesDAL.ConnectionString = ConfigurationManager.ConnectionStrings["MySQLConn"].ToString(); Clients_Invoices com = new Clients_Invoices(); com.OrderNo = (gvClinetInv.Rows[e.RowIndex].FindControl("lblOrderNo") as Label).Text; com.EmployeeID = (gvClinetInv.Rows[e.RowIndex].FindControl("lblEmployeeID") as Label).Text; com.FirstName = (gvClinetInv.Rows[e.RowIndex].FindControl("lblFirstName") as Label).Text; com.LastName = (gvClinetInv.Rows[e.RowIndex].FindControl("lblLastName") as Label).Text; com.CatalogName = (gvClinetInv.Rows[e.RowIndex].FindControl("lblCatalogName") as Label).Text; com.Quantity = Convert.ToInt32((gvClinetInv.Rows[e.RowIndex].FindControl("lblQuantity") as Label).Text); com.OrderDate = Convert.ToDateTime((gvClinetInv.Rows[e.RowIndex].FindControl("lblOrderDate") as Label).Text); com.LocalCost = Convert.ToDouble((gvClinetInv.Rows[e.RowIndex].FindControl("lblLocalCost") as Label).Text); com.USDCost = Convert.ToDouble((gvClinetInv.Rows[e.RowIndex].FindControl("lblUSDCost") as Label).Text); com.TotalLocalCost = Convert.ToDouble(gvClinetInv.DataKeys[e.RowIndex].Values["TotalLocalCost"].ToString()); com.RedemptionPoints = (gvClinetInv.Rows[e.RowIndex].FindControl("lblRedemptionPoints") as Label).Text; // com.ReasonofReturen = gvClinetInv.DataKeys[e.RowIndex].Values["ReasonofReturen"].ToString(); com.ReasonofReturen = (gvClinetInv.Rows[e.RowIndex].FindControl("ddlReasonofReturen") as DropDownList).SelectedValue; //int company = Convert.ToInt32(CompanyDDL.SelectedValue); com.Country = gvClinetInv.DataKeys[e.RowIndex].Values[2].ToString(); clientsInvoicesDAL.InsertClientsInvoices(com); gvClinetInv.EditIndex = -1; GetClinetInvoice(search, search, company, sdate, edate); }
public List <Clients_Invoices> GetInvoiceTotal(string OrderNo, string EmployeeID, DateTime StartDate, DateTime EndDate, int Company) { MySqlConnection con = new MySqlConnection(ConnectionString); try { MySqlCommand com = new MySqlCommand("GetInvoiceTotal", con); com.CommandType = System.Data.CommandType.StoredProcedure; com.Parameters.Add(new MySqlParameter("VarOrderNo", OrderNo)); com.Parameters.Add(new MySqlParameter("VarEmployeeID", EmployeeID)); if (StartDate != DateTime.MinValue) { com.Parameters.Add(new MySqlParameter("VarStartDate", StartDate.Date)); } else { com.Parameters.Add(new MySqlParameter("VarStartDate", null)); } if (EndDate != DateTime.MinValue) { com.Parameters.Add(new MySqlParameter("VarEndDate", EndDate.Date)); } else { com.Parameters.Add(new MySqlParameter("VarEndDate", null)); } com.Parameters.Add(new MySqlParameter("VarCompany", Company)); List <Clients_Invoices> TotalInvoicesList = new List <Clients_Invoices>(); con.Open(); MySqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { Clients_Invoices clientsInvoices = new Clients_Invoices(); clientsInvoices.TotalLocalCost = Convert.ToDouble(reader["TotalLocalCost"]); clientsInvoices.TotalUSDCost = Convert.ToDouble(reader["TotalUSDCost"]); clientsInvoices.Country = reader["Country"].ToString(); TotalInvoicesList.Add(clientsInvoices); } con.Close(); return(TotalInvoicesList); } catch { con.Close(); return(null); } }
public void InsertClientsInvoices(Clients_Invoices clients_Invoices) { //Connection and Command objects. MySqlConnection con = new MySqlConnection(ConnectionString); MySqlCommand com = new MySqlCommand("UpsertClientsInvoices", con); //Procedure Parameters. com.CommandType = System.Data.CommandType.StoredProcedure; com.Parameters.Add(new MySqlParameter("VarOrderNo", clients_Invoices.OrderNo)); com.Parameters.Add(new MySqlParameter("VarEmployeeID", clients_Invoices.EmployeeID)); com.Parameters.Add(new MySqlParameter("VarFirstName", clients_Invoices.FirstName)); com.Parameters.Add(new MySqlParameter("VarLastName", clients_Invoices.LastName)); com.Parameters.Add(new MySqlParameter("VarCatalogName", clients_Invoices.CatalogName)); com.Parameters.Add(new MySqlParameter("VarQuantity", clients_Invoices.Quantity)); com.Parameters.Add(new MySqlParameter("VarOrderDate", clients_Invoices.OrderDate)); com.Parameters.Add(new MySqlParameter("VarRedemptionPoints", clients_Invoices.RedemptionPoints)); com.Parameters.Add(new MySqlParameter("VarLocalCost", clients_Invoices.LocalCost)); if (clients_Invoices.ReasonofReturen == "0") { com.Parameters.Add(new MySqlParameter("VarReasonofReturen", "Not Available")); } else if (clients_Invoices.ReasonofReturen == "1") { com.Parameters.Add(new MySqlParameter("VarReasonofReturen", "By Customer")); } else { com.Parameters.Add(new MySqlParameter("VarReasonofReturen", "None")); } com.Parameters.Add(new MySqlParameter("VarCountry", clients_Invoices.Country)); // con.Open(); com.ExecuteNonQuery(); con.Close(); }
public List <Clients_Invoices> GetClientsInvoices(string OrderNo, string EmployeeID, DateTime StartDate, DateTime EndDate, int Company) { MySqlConnection con = new MySqlConnection(ConnectionString); try { MySqlCommand com = new MySqlCommand("GetClientsInvoices", con); com.CommandType = System.Data.CommandType.StoredProcedure; if (string.IsNullOrEmpty(OrderNo)) { com.Parameters.Add(new MySqlParameter("VarOrderNo", null)); } else { com.Parameters.Add(new MySqlParameter("VarOrderNo", OrderNo)); } if (string.IsNullOrEmpty(OrderNo)) { com.Parameters.Add(new MySqlParameter("VarEmployeeID", null)); } else { com.Parameters.Add(new MySqlParameter("VarEmployeeID", EmployeeID)); } if (StartDate != DateTime.MinValue) { com.Parameters.Add(new MySqlParameter("VarStartDate", StartDate.Date)); } else { com.Parameters.Add(new MySqlParameter("VarStartDate", null)); } if (EndDate != DateTime.MinValue) { com.Parameters.Add(new MySqlParameter("VarEndDate", EndDate.Date)); } else { com.Parameters.Add(new MySqlParameter("VarEndDate", null)); } com.Parameters.Add(new MySqlParameter("VarCompany", Company)); List <Clients_Invoices> ClientsInvoicesList = new List <Clients_Invoices>(); con.Open(); MySqlDataReader reader = com.ExecuteReader(); while (reader.Read()) { Clients_Invoices clientsInvoices = new Clients_Invoices(); clientsInvoices.OrderNo = reader["OrderNo"].ToString(); clientsInvoices.EmployeeID = reader["EmployeeID"].ToString(); clientsInvoices.FirstName = reader["FirstName"].ToString(); clientsInvoices.LastName = reader["LastName"].ToString(); clientsInvoices.CatalogName = reader["CatalogName"].ToString(); clientsInvoices.Quantity = Convert.ToInt32(reader["Quantity"]); DateTime?OrderDate = (reader["OrderDate"] == System.DBNull.Value) ? (DateTime?)null : Convert.ToDateTime(reader["OrderDate"]).Date; clientsInvoices.OrderDate = OrderDate; clientsInvoices.RedemptionPoints = reader["RedemptionPoints"].ToString(); clientsInvoices.ReasonofReturen = reader["ReasonofReturen"].ToString(); clientsInvoices.USDCost = Convert.ToDouble(reader["USDCost"]); clientsInvoices.Country = reader["Country"].ToString(); clientsInvoices.LocalCost = Convert.ToDouble(reader["LocalCost"]); ClientsInvoicesList.Add(clientsInvoices); } con.Close(); return(ClientsInvoicesList); } catch { con.Close(); return(null); } }