public static dbo_CountStockClass Select_Record(String Count_No) { dbo_CountStockClass clsdbo_CountStock = new dbo_CountStockClass(); SqlConnection connection = SAMDataClass.GetConnection(); string selectProcedure = "[CountStockSelect]"; SqlCommand selectCommand = new SqlCommand(selectProcedure, connection); selectCommand.CommandType = CommandType.StoredProcedure; selectCommand.Parameters.AddWithValue("@Count_No", Count_No); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { clsdbo_CountStock.Count_No = reader["Count_No"] is DBNull ? null : reader["Count_No"].ToString(); clsdbo_CountStock.CV_Code = reader["CV_Code"] is DBNull ? null : reader["CV_Code"].ToString(); clsdbo_CountStock.Count_Date = reader["Count_Date"] is DBNull ? null : (DateTime?)reader["Count_Date"]; clsdbo_CountStock.Status = reader["Status"] is DBNull ? null : reader["Status"].ToString(); clsdbo_CountStock.Stock_on_Hand = reader["Stock_on_Hand"] is DBNull ? null : (Int32?)reader["Stock_on_Hand"]; clsdbo_CountStock.Count_Quantity = reader["Count_Quantity"] is DBNull ? null : (Int32?)reader["Count_Quantity"]; clsdbo_CountStock.Diff_Quantity = reader["Diff_Quantity"] is DBNull ? null : (Int16?)reader["Diff_Quantity"]; } else { clsdbo_CountStock = null; } reader.Close(); } catch (SqlException ex) { logger.Error(ex.Message); return(clsdbo_CountStock); } finally { connection.Close(); } return(clsdbo_CountStock); }
public static bool Delete(dbo_CountStockClass clsdbo_CountStock) { SqlConnection connection = SAMDataClass.GetConnection(); string deleteProcedure = "[dbo].[CountStockDelete]"; SqlCommand deleteCommand = new SqlCommand(deleteProcedure, connection); deleteCommand.CommandType = CommandType.StoredProcedure; if (clsdbo_CountStock.Count_No != null) { deleteCommand.Parameters.AddWithValue("@OldCount_No", clsdbo_CountStock.Count_No); } else { deleteCommand.Parameters.AddWithValue("@OldCount_No", DBNull.Value); } if (clsdbo_CountStock.CV_Code != null) { deleteCommand.Parameters.AddWithValue("@OldCV_Code", clsdbo_CountStock.CV_Code); } else { deleteCommand.Parameters.AddWithValue("@OldCV_Code", DBNull.Value); } if (clsdbo_CountStock.Count_Date.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldCount_Date", clsdbo_CountStock.Count_Date); } else { deleteCommand.Parameters.AddWithValue("@OldCount_Date", DBNull.Value); } if (clsdbo_CountStock.Status != null) { deleteCommand.Parameters.AddWithValue("@OldStatus", clsdbo_CountStock.Status); } else { deleteCommand.Parameters.AddWithValue("@OldStatus", DBNull.Value); } if (clsdbo_CountStock.Stock_on_Hand.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldStock_on_Hand", clsdbo_CountStock.Stock_on_Hand); } else { deleteCommand.Parameters.AddWithValue("@OldStock_on_Hand", DBNull.Value); } if (clsdbo_CountStock.Count_Quantity.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldCount_Quantity", clsdbo_CountStock.Count_Quantity); } else { deleteCommand.Parameters.AddWithValue("@OldCount_Quantity", DBNull.Value); } if (clsdbo_CountStock.Diff_Quantity.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldDiff_Quantity", clsdbo_CountStock.Diff_Quantity); } else { deleteCommand.Parameters.AddWithValue("@OldDiff_Quantity", DBNull.Value); } deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int); deleteCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output; try { connection.Open(); deleteCommand.ExecuteNonQuery(); int count = System.Convert.ToInt32(deleteCommand.Parameters["@ReturnValue"].Value); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { logger.Error(ex.Message); return(false); } finally { connection.Close(); } }
public static bool Update(dbo_CountStockClass newdbo_CountStockClass, String Last_Modified_By) { SqlConnection connection = SAMDataClass.GetConnection(); string updateProcedure = "[CountStockUpdate]"; SqlCommand updateCommand = new SqlCommand(updateProcedure, connection); updateCommand.CommandType = CommandType.StoredProcedure; if (newdbo_CountStockClass.Count_No != null) { updateCommand.Parameters.AddWithValue("@NewCount_No", newdbo_CountStockClass.Count_No); } else { updateCommand.Parameters.AddWithValue("@NewCount_No", DBNull.Value); } switch (newdbo_CountStockClass.Status) { case "รอการคอนเฟิร์ม": updateCommand.Parameters.AddWithValue("@NewStatus", "1"); break; case "คอนเฟิร์มแล้ว": updateCommand.Parameters.AddWithValue("@NewStatus", "2"); break; case "ยกเลิกการนับ": updateCommand.Parameters.AddWithValue("@NewStatus", "3"); break; default: updateCommand.Parameters.AddWithValue("@NewStatus", DBNull.Value); break; } if (newdbo_CountStockClass.Stock_on_Hand.HasValue == true) { updateCommand.Parameters.AddWithValue("@NewStock_on_Hand", newdbo_CountStockClass.Stock_on_Hand); } else { updateCommand.Parameters.AddWithValue("@NewStock_on_Hand", DBNull.Value); } if (newdbo_CountStockClass.Count_Quantity.HasValue == true) { updateCommand.Parameters.AddWithValue("@NewCount_Quantity", newdbo_CountStockClass.Count_Quantity); } else { updateCommand.Parameters.AddWithValue("@NewCount_Quantity", DBNull.Value); } if (newdbo_CountStockClass.Diff_Quantity.HasValue == true) { updateCommand.Parameters.AddWithValue("@NewDiff_Quantity", newdbo_CountStockClass.Diff_Quantity); } else { updateCommand.Parameters.AddWithValue("@NewDiff_Quantity", DBNull.Value); } if (!string.IsNullOrEmpty(Last_Modified_By)) { updateCommand.Parameters.AddWithValue("@Last_Modified_By", Last_Modified_By); } else { updateCommand.Parameters.AddWithValue("@Last_Modified_By", DBNull.Value); } updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int); updateCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output; try { connection.Open(); updateCommand.ExecuteNonQuery(); int count = System.Convert.ToInt32(updateCommand.Parameters["@ReturnValue"].Value); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { logger.Error(ex.Message); return(false); } finally { connection.Close(); } }
public static bool Add(dbo_CountStockClass clsdbo_CountStock, String Created_By) { SqlConnection connection = SAMDataClass.GetConnection(); string insertProcedure = "[dbo].[CountStockInsert]"; SqlCommand insertCommand = new SqlCommand(insertProcedure, connection); insertCommand.CommandType = CommandType.StoredProcedure; if (clsdbo_CountStock.Count_No != null) { insertCommand.Parameters.AddWithValue("@Count_No", clsdbo_CountStock.Count_No); } else { insertCommand.Parameters.AddWithValue("@Count_No", DBNull.Value); } if (clsdbo_CountStock.CV_Code != null) { insertCommand.Parameters.AddWithValue("@CV_Code", clsdbo_CountStock.CV_Code); } else { insertCommand.Parameters.AddWithValue("@CV_Code", DBNull.Value); } if (clsdbo_CountStock.Count_Date.HasValue == true) { insertCommand.Parameters.AddWithValue("@Count_Date", clsdbo_CountStock.Count_Date); } else { insertCommand.Parameters.AddWithValue("@Count_Date", DBNull.Value); } switch (clsdbo_CountStock.Status) { case "รอการคอนเฟิร์ม": insertCommand.Parameters.AddWithValue("@Status", "1"); break; case "คอนเฟิร์มแล้ว": insertCommand.Parameters.AddWithValue("@Status", "2"); break; case "ยกเลิกการนับ": insertCommand.Parameters.AddWithValue("@Status", "3"); break; default: insertCommand.Parameters.AddWithValue("@Status", clsdbo_CountStock.Status); break; } if (clsdbo_CountStock.Stock_on_Hand.HasValue == true) { insertCommand.Parameters.AddWithValue("@Stock_on_Hand", clsdbo_CountStock.Stock_on_Hand); } else { insertCommand.Parameters.AddWithValue("@Stock_on_Hand", DBNull.Value); } if (clsdbo_CountStock.Count_Quantity.HasValue == true) { insertCommand.Parameters.AddWithValue("@Count_Quantity", clsdbo_CountStock.Count_Quantity); } else { insertCommand.Parameters.AddWithValue("@Count_Quantity", DBNull.Value); } if (clsdbo_CountStock.Diff_Quantity.HasValue == true) { insertCommand.Parameters.AddWithValue("@Diff_Quantity", clsdbo_CountStock.Diff_Quantity); } else { insertCommand.Parameters.AddWithValue("@Diff_Quantity", DBNull.Value); } if (!string.IsNullOrEmpty(Created_By)) { insertCommand.Parameters.AddWithValue("@Created_By", Created_By); } else { insertCommand.Parameters.AddWithValue("@Created_By", DBNull.Value); } insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int); insertCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output; try { connection.Open(); insertCommand.ExecuteNonQuery(); int count = System.Convert.ToInt32(insertCommand.Parameters["@ReturnValue"].Value); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { logger.Error(ex.Message); return(false); } finally { connection.Close(); } }