private void btnSave_Click(object sender, EventArgs e) { if (!validField()) { return; } EntityClass.Orders ord = FormToOrders(); if (isAdd) { int rs = SQLAdd(ord); if (rs > 0) // success { ResetForm(); LoadAllQuery(); } else // failed { MessageBox.Show("No row was added. Tips: Try to sync.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } else // update { int rs = SQLUpdate(ord); if (rs > 0) { // update UI DataGridViewRow row = dgOrders.SelectedRows[0]; int index = row.Index; //dgOrders.DataSource = dataTbl; dataTbl.Rows.RemoveAt(index); DataRow r = dataTbl.NewRow(); r.SetField <string>(COLUMN_ORDERID, ord.ID); r.SetField <string>(COLUMN_CUSID, ord.CusID); r.SetField <string>(COLUMN_CUSNAME, ord.CusName); r.SetField <string>(COLUMN_EMID, ord.EmpID); r.SetField <string>(COLUMN_EMNAME, ord.EmName); r.SetField <string>(COLUMN_ORDER_DATE, ord.OrderDate); r.SetField <string>(COLUMN_REQUIRED_DATE, ord.RequiredDate); r.SetField <string>(COLUMN_SHIPPED_DATE, ord.ShippedDate); r.SetField <string>(COLUMN_SHIPPER_ID, ord.ShipperID); r.SetField <string>(COLUMN_SHIPPER_COM_NAME, ord.ShipComName); r.SetField <string>(COLUMN_FREIGHT, ord.Freight); r.SetField <string>(COLUMN_SHIPNAME, ord.ShipName); r.SetField <string>(COLUMN_SHIP_ADDRESS, ord.ShipAddress); r.SetField <string>(COLUMN_SHIP_CITY, ord.ShipCity); r.SetField <string>(COLUMN_SHIP_REGION, ord.ShipRegion); r.SetField <string>(COLUMN_POSTAL, ord.ShipPostalCode); r.SetField <string>(COLUMN_COUNTRY, ord.ShipCountry); dataTbl.Rows.InsertAt(r, index); } else { MessageBox.Show("No row was updated. Tips: Try to sync.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } }
private EntityClass.Orders FormToOrders() { EntityClass.Orders ord = new EntityClass.Orders(); ord.ID = txtOrderID.Text.Trim(); ord.EmpID = cbEmployeeID.SelectedValue.ToString(); ord.EmName = cbEmployeeID.Text.Trim(); ord.OrderDate = dpkOrderDate.Value.ToShortDateString(); if (ckShipped.Checked) { ord.ShippedDate = null; } else { ord.ShippedDate = dpkShippedDate.Value.ToShortDateString(); } ord.RequiredDate = dpkRequiredDate.Value.ToShortDateString(); ord.ShipperID = cbShippedID.SelectedValue.ToString(); ord.ShipComName = cbShippedID.Text.Trim(); ord.Freight = txtFreight.Text.Trim(); ord.ShipName = txtShipName.Text.Trim(); ord.ShipAddress = txtShipAddress.Text.Trim(); ord.ShipCity = cbCity.Text.Trim(); ord.ShipCountry = cbCountry.Text.Trim(); if (ckCus.Checked) { ord.CusID = null; ord.CusName = null; } else { ord.CusID = cbCustomerID.SelectedValue.ToString(); ord.CusName = cbCustomerID.Text.Trim(); } if (cbRegion.SelectedIndex > 0) { ord.ShipRegion = cbRegion.Text.Trim(); } if (mtxtPostal.Text.Trim().Length > 0) { ord.ShipPostalCode = mtxtPostal.Text.Trim(); } return(ord); }
private int SQLUpdate(EntityClass.Orders ord) { SqlCommand cmd; try { con = AppConfig.GetConnection(); con.Open(); cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "[UpdateOrder]"; cmd.CommandType = CommandType.StoredProcedure; if (ckCus.Checked) { cmd.Parameters.Add("custid", SqlDbType.Int).Value = (object)DBNull.Value; } else { cmd.Parameters.Add("custid", SqlDbType.Int).Value = int.Parse(ord.CusID); } cmd.Parameters.Add("empid", SqlDbType.Int).Value = ord.EmpID; cmd.Parameters.Add("orderdate", SqlDbType.DateTime).Value = Convert.ToDateTime(ord.OrderDate); cmd.Parameters.Add("requireddate", SqlDbType.DateTime).Value = Convert.ToDateTime(ord.RequiredDate); if (ckShipped.Checked) { cmd.Parameters.Add("shippeddate", SqlDbType.DateTime).Value = (object)DBNull.Value; } else { cmd.Parameters.Add("shippeddate", SqlDbType.DateTime).Value = Convert.ToDateTime(ord.ShippedDate); } cmd.Parameters.Add("shipperid", SqlDbType.Int).Value = ord.ShipperID; cmd.Parameters.Add("freight", SqlDbType.Money).Value = Convert.ToDecimal(ord.Freight); cmd.Parameters.Add("shipname", SqlDbType.NVarChar).Value = ord.ShipName; cmd.Parameters.Add("shipaddress", SqlDbType.NVarChar).Value = ord.ShipAddress; cmd.Parameters.Add("shipcity", SqlDbType.NVarChar).Value = ord.ShipCity; cmd.Parameters.Add("shipregion", SqlDbType.NVarChar).Value = ord.ShipRegion != null ? ord.ShipRegion : (object)DBNull.Value; cmd.Parameters.Add("shippostalcode", SqlDbType.NVarChar).Value = ord.ShipPostalCode != null ? ord.ShipPostalCode : (object)DBNull.Value; cmd.Parameters.Add("shipcountry", SqlDbType.NVarChar).Value = ord.ShipCountry; cmd.Parameters.Add("orderid", SqlDbType.NVarChar).Value = ord.ID; int rs = cmd.ExecuteNonQuery(); if (rs > 0) { return(rs); } else { return(0); } } catch (SqlException) { return(-1); } catch (Exception) { return(-1); } finally { con.Close(); } }