public int UpdatePaintingNumber(PaintingNumber _paintingNumber) { int result = -1; try { conn = db.openConn(); tr = conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append(" UPDATE painting_numbers "); sb.Append(" SET rev='" + _paintingNumber.Rev + "',"); sb.Append(" color_no='" + _paintingNumber.ColerNo + "',"); sb.Append(" process='" + _paintingNumber.Process + "'"); sb.Append(" WHERE (complete_no='" + _paintingNumber.CompleteNo + "')"); sb.Append(" and (part='" + _paintingNumber.Part + "')"); string sqlUpdate; sqlUpdate = sb.ToString(); comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = sqlUpdate; comm.Transaction = tr; comm.Parameters.Clear(); comm.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); conn.Close(); return result; throw ex; } finally { conn.Close(); } return result; }
public int CreatePaintingNumber(PaintingNumber newPaintingNumber) { int result = -1; try { conn = db.openConn(); tr = conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO painting_numbers(part,complete_no,rev,color_no,process,user_create_name,create_date,status)"); sb.Append(" VALUES (@part,@complete_no,@rev,@color_no,@process,@user_create_name,@create_date,@status)"); string sqlsave; sqlsave = sb.ToString(); comm = new SqlCommand(); comm.Connection = conn; comm.Transaction = tr; comm.CommandText = sqlsave; comm.Parameters.Clear(); comm.Parameters.Add("@part", SqlDbType.NVarChar).Value = newPaintingNumber.Part; comm.Parameters.Add("@complete_no", SqlDbType.NVarChar).Value = newPaintingNumber.CompleteNo; comm.Parameters.Add("@rev", SqlDbType.NVarChar).Value = newPaintingNumber.Rev; comm.Parameters.Add("@color_no", SqlDbType.NVarChar).Value = newPaintingNumber.ColerNo; comm.Parameters.Add("@process", SqlDbType.NVarChar).Value = newPaintingNumber.Process; comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newPaintingNumber.UserCreateName; comm.Parameters.Add("@create_date", SqlDbType.NVarChar).Value = newPaintingNumber.CreateDate; comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newPaintingNumber.Status; comm.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); conn.Close(); return result; throw ex; } finally { conn.Close(); } return result; }
public List<PaintingNumber> getPaintingNumberAllTotal() { List<PaintingNumber> paintingNumbers = new List<PaintingNumber>(); PaintingNumber inc; try { conn = db.openConn(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append(" SELECT part,complete_no,rev,color_no,process,user_create_name,create_date,status FROM painting_numbers "); //sb.Append(" WHERE complete_no='" + _completeNo + "'"); string sql; sql = sb.ToString(); comm = new SqlCommand(); comm.CommandText = sql; comm.CommandType = CommandType.Text; comm.Connection = conn; dr = comm.ExecuteReader(); if (dr.HasRows) { DataTable dt = new DataTable(); dt.Load(dr); foreach (DataRow drw in dt.Rows) { inc = new PaintingNumber(); inc.ID = 0; inc.Part = drw["part"].ToString(); inc.CompleteNo = drw["complete_no"].ToString(); inc.Rev = drw["rev"].ToString(); inc.ColerNo = drw["color_no"].ToString(); inc.Process = drw["process"].ToString(); inc.UserCreateName = drw["user_create_name"].ToString(); inc.CreateDate = drw["create_date"].ToString(); inc.Status = drw["status"].ToString(); paintingNumbers.Add(inc); } } } catch (Exception ex) { dr.Close(); conn.Close(); return null; throw ex; } finally { conn.Close(); } return paintingNumbers; }