public int CreateWorkTable(WorkTable newWorkTable) { int result = -1; try { conn = db.openConn(); tr = conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbWorkTable(WID,psnCode)"); sb.Append(" VALUES (@WID,@psnCode)"); string sqlsave; sqlsave = sb.ToString(); comm = new SqlCommand(); comm.Connection = conn; comm.Transaction = tr; comm.CommandText = sqlsave; comm.Parameters.Clear(); int year = Convert.ToInt32(DateTime.Now.Year.ToString("0000")); string _dNow = DateTime.Now.Day.ToString("00") + "" + DateTime.Now.Month.ToString("00") + "" + year.ToString() + DateTime.Now.ToShortTimeString(); comm.Parameters.Add("@WID", SqlDbType.NVarChar).Value = "W"+_dNow; comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newWorkTable.psnCode; comm.ExecuteNonQuery(); foreach (WorkTableDetail wdt in newWorkTable.workTableDetails) { sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbWorkTableDetail(WID,wday,wstart,wend)"); sb.Append(" VALUES (@WID,@wday,@wstart,@wend)"); string sqlsave2; sqlsave2 = sb.ToString(); comm = new SqlCommand(); comm.Connection = conn; comm.Transaction = tr; comm.CommandText = sqlsave2; comm.Parameters.Clear(); comm.Parameters.Add("@WID", SqlDbType.NVarChar).Value = "W" + _dNow; comm.Parameters.Add("@wday", SqlDbType.NVarChar).Value = wdt.day; comm.Parameters.Add("@wstart", SqlDbType.NVarChar).Value = wdt.start; comm.Parameters.Add("@wend", SqlDbType.NVarChar).Value = wdt.end; comm.ExecuteNonQuery(); } tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); conn.Close(); return result; throw ex; } finally { conn.Close(); } return result; }
private void cmdSave_Click(object sender, EventArgs e) { if (txtpsnCode.Text.Trim() == "") { MessageBox.Show("กรุณาเลือกพนักงาน ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtpsnCode.Focus(); return; } WorkTable workTable = new WorkTable(); workTable.psnCode = txtpsnCode.Text.Trim(); List<WorkTableDetail> workTableDetails = new List<WorkTableDetail>(); WorkTableDetail workTableDetail = null; for (int i = 0; i < dgvList.Rows.Count; i++) { if (Convert.ToBoolean(dgvList.Rows[i].Cells[0].Value = true)) { workTableDetail = new WorkTableDetail(); workTableDetail.day = dgvList.Rows[i].Cells[3].Value.ToString(); workTableDetail.start = dgvList.Rows[i].Cells[4].Value.ToString(); workTableDetail.end = dgvList.Rows[i].Cells[5].Value.ToString(); workTableDetails.Add(workTableDetail); } } workTable.workTableDetails = workTableDetails; int result = workTableService.CreateWorkTable(workTable); if (result > -1) { Console.WriteLine("Insert Complete"); lblresult.Visible = true; lblresult.Text = " บันทึกเรียบร้อย "; } else { Console.WriteLine("Insert Not Complete"); lblresult.Visible = true; lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้"; } }