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();
}
}
