//09-Jan - For Attendance public int Insert(EmployeeAttendance attendance) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertEmployeeAttendance", con); cmd.CommandType = CommandType.StoredProcedure; //Be EXTRA CAREFUL WHILE ENTERING PARAMETERS as Compiler doesn't pick it // @EmployeeID int, // @AttendanceDate datetime, //@StatusID int cmd.Parameters.AddWithValue("@EmployeeID", DALHelper.getNullORValue(attendance.Emp.ID)); cmd.Parameters.AddWithValue("@StatusID", DALHelper.getNullORValue(attendance.Status.ID)); cmd.Parameters.AddWithValue("@AttendanceDate", DALHelper.getNullORValue(attendance.AttendanceDateTime)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
public int Insert(AnimalMedicalRecord medRecord) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertAnimalMedicalRecord", con); //This procedure is actually for AnimalMedicalRecord. In DB Animal = Front-end AnimalMedicalRecord cmd.CommandType = CommandType.StoredProcedure; #region Procedure Parameters // @Date datetime, //@Animal varchar(30), //@Diagnosis varchar(50), //@Treatment varchar(50), //@Prognosis varchar(50) #endregion cmd.Parameters.AddWithValue("@Animal", DALHelper.getNullORValue(medRecord.IllAnimal.ID)); //Changed 31-Mar from Tag to ID cmd.Parameters.AddWithValue("@Date", DALHelper.getNullORValue(DateTime.Now)); cmd.Parameters.AddWithValue("@Diagnosis", DALHelper.getNullORValue(medRecord.Diagnosis)); cmd.Parameters.AddWithValue("@Treatment", DALHelper.getNullORValue(medRecord.Treatment)); cmd.Parameters.AddWithValue("@Prognosis", DALHelper.getNullORValue(medRecord.Prognosis)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
public int Insert(Employee emp) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertEmployee", con); cmd.CommandType = CommandType.StoredProcedure; //Be EXTRA CAREFUL WHILE ENTERING PARAMETERS as Compiler doesn't pick it cmd.Parameters.AddWithValue("@Name", DALHelper.getNullORValue(emp.Name)); cmd.Parameters.AddWithValue("@FatherName", DALHelper.getNullORValue(emp.FatherName)); cmd.Parameters.AddWithValue("@DateofBirth", DALHelper.getNullORValue(emp.BirthDate.ToShortDateString())); cmd.Parameters.AddWithValue("@JoiningDate", DALHelper.getNullORValue(emp.JoiningDate.ToShortDateString())); cmd.Parameters.AddWithValue("@Salary", DALHelper.getNullORValue(emp.Salary)); cmd.Parameters.AddWithValue("@Role", DALHelper.getNullORValue(emp.Role.ID)); cmd.Parameters.AddWithValue("@Description", DALHelper.getNullORValue(emp.DutyDescription)); cmd.Parameters.AddWithValue("@CurrentAddress", DALHelper.getNullORValue(emp.CurrentAddress)); cmd.Parameters.AddWithValue("@PermanentAddress", DALHelper.getNullORValue(emp.PermanentAddress)); cmd.Parameters.AddWithValue("@CNIC_No", DALHelper.getNullORValue(emp.CNIC_No)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
public int Insert(Expense exp, User currUser) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertExpense", con); cmd.CommandType = CommandType.StoredProcedure; //Be EXTRA CAREFUL WHILE ENTERING PARAMETERS as Compiler doesn't pick it cmd.Parameters.AddWithValue("@Date", DALHelper.getNullORValue(DateTime.Now)); cmd.Parameters.AddWithValue("@Category", DALHelper.getNullORValue(exp.SubType.ParentCategory.ID)); cmd.Parameters.AddWithValue("@SubType", DALHelper.getNullORValue(exp.SubType.ID)); cmd.Parameters.AddWithValue("@CurrentUser", DALHelper.getNullORValue(currUser.ID)); cmd.Parameters.AddWithValue("@Amount", DALHelper.getNullORValue(exp.Amount)); cmd.Parameters.AddWithValue("@Comments", DALHelper.getNullORValue(exp.Comments)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
/// <summary> /// Added on March 12 - For updating the edited rows /// </summary> /// <param name="medRecord">Edited Row (containing ID as well)</param> /// <returns>Success as int </returns> public int Update(AnimalMedicalRecord medRecord) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_updateAnimalMedicalRecord", con); cmd.CommandType = CommandType.StoredProcedure; #region Procedure Parameters //@ID int, // @Date datetime, //@Animal varchar(30), //@Diagnosis varchar(50), //@Treatment varchar(50), //@Prognosis varchar(50) #endregion cmd.Parameters.AddWithValue("@ID", DALHelper.getNullORValue(medRecord.ID)); cmd.Parameters.AddWithValue("@Animal", DALHelper.getNullORValue(medRecord.IllAnimal.ID)); //Modified 31-Mar cmd.Parameters.AddWithValue("@Date", DALHelper.getNullORValue(medRecord.EntryDate)); cmd.Parameters.AddWithValue("@Diagnosis", DALHelper.getNullORValue(medRecord.Diagnosis)); cmd.Parameters.AddWithValue("@Treatment", DALHelper.getNullORValue(medRecord.Treatment)); cmd.Parameters.AddWithValue("@Prognosis", DALHelper.getNullORValue(medRecord.Prognosis)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
public int Insert(MilkingEntry milkEntry) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertMilkEntry", con); cmd.CommandType = CommandType.StoredProcedure; #region Procedure Parameters //@TagNo varchar(30), // @RegistrationDate datetime, // @MilkingDate date, //@Shift varchar(7), // @Quantity float, // @Comments varchar(50) #endregion cmd.Parameters.AddWithValue("@ID", DALHelper.getNullORValue(milkEntry.MilkingCattle.ID)); //Modified on 01-Apr cmd.Parameters.AddWithValue("@RegistrationDate", DALHelper.getNullORValue(DateTime.Now)); cmd.Parameters.AddWithValue("@MilkingDate", DALHelper.getNullORValue(milkEntry.MilkingDate)); cmd.Parameters.AddWithValue("@Shift", DALHelper.getNullORValue(milkEntry.MilkingShift)); cmd.Parameters.AddWithValue("@Quantity", DALHelper.getNullORValue(milkEntry.Quantity)); cmd.Parameters.AddWithValue("@Comments", DALHelper.getNullORValue(milkEntry.Comments)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
//Added 14 May public int GetAnimalStatusID(AnimalStatusNew ast) { int id = 0; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_select_AnimalStatus_ID", con); cmd.CommandType = CommandType.StoredProcedure; char isFemale = 'Y'; char IsMilk = 'Y'; if (ast.IsFemale == false) { isFemale = 'N'; } if (ast.IsMilking == false) { IsMilk = 'N'; } cmd.Parameters.AddWithValue("@Desc", DALHelper.getNullORValue(ast.Description)); cmd.Parameters.AddWithValue("@IsFemale", DALHelper.getNullORValue(isFemale)); cmd.Parameters.AddWithValue("@IsMilk", DALHelper.getNullORValue(IsMilk)); con.Open(); using (con) { id = (Int32)cmd.ExecuteScalar(); } return(id); }
public List <Cattle> GetCattlesFemale() { SqlCommand cmd = new SqlCommand("sp_selectAnimals_byGenderAll", DALHelper.getConnection()); cmd.Parameters.AddWithValue("@Gender", DALHelper.getNullORValue("F")); //Modified 31 March return(selectCattles(cmd)); }
public int Insert(Calf currCalf, int motherID, int fatherID) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertCalfwithMotherandFather", con); cmd.CommandType = CommandType.StoredProcedure; #region Procedure Parameters //@TagNo, // @RegistrationDate, //@AnimalType, //@Gender, //@BirthDate, //@Weight, //@Height, //@Length, //@Width, //@Price, //@Source, //@Breed, //@Status, //@OtherDetails //Mother //@Father #endregion cmd.Parameters.AddWithValue("@TagNo", DALHelper.getNullORValue(currCalf.TagNo)); cmd.Parameters.AddWithValue("@RegistrationDate", DALHelper.getNullORValue(DateTime.Now)); cmd.Parameters.AddWithValue("@AnimalType", DALHelper.getNullORValue(currCalf.Type.ID)); cmd.Parameters.AddWithValue("@Gender", DALHelper.getNullORValue(currCalf.Gender.Description)); cmd.Parameters.AddWithValue("@BirthDate", DALHelper.getNullORValue(currCalf.BirthDate)); cmd.Parameters.AddWithValue("@Weight", DALHelper.getNullORValue(currCalf.CurrPhysicalAttribs.Weight)); cmd.Parameters.AddWithValue("@Height", DALHelper.getNullORValue(currCalf.CurrPhysicalAttribs.CurrentSize.Height)); cmd.Parameters.AddWithValue("@Length", DALHelper.getNullORValue(currCalf.CurrPhysicalAttribs.CurrentSize.Length)); cmd.Parameters.AddWithValue("@Width", DALHelper.getNullORValue(currCalf.CurrPhysicalAttribs.CurrentSize.Width)); cmd.Parameters.AddWithValue("@Price", DALHelper.getNullORValue(currCalf.Price)); cmd.Parameters.AddWithValue("@Source", DALHelper.getNullORValue(currCalf.Source.ID)); cmd.Parameters.AddWithValue("@Breed", DALHelper.getNullORValue(currCalf.Breed.ID)); cmd.Parameters.AddWithValue("@Status", DALHelper.getNullORValue(1)); //Modified 01-Apr cmd.Parameters.AddWithValue("@OtherDetails", DALHelper.getNullORValue(currCalf.OtherDetails)); cmd.Parameters.AddWithValue("@Mother", DALHelper.getNullORValue(motherID)); //Modified 31 March cmd.Parameters.AddWithValue("@Father", DALHelper.getNullORValue(fatherID)); //Modified 31 March con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
public int Insert(Cattle cattle) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertAnimal", con); //This procedure is actually for Cattle. In DB Animal = Front-end Cattle cmd.CommandType = CommandType.StoredProcedure; #region Procedure Parameters //@TagNo, // @RegistrationDate, //@AnimalType, //@Gender, //@BirthDate, //@Weight, //@Height, //@Length, //@Width, //@Price, //@Source, //@Breed, //@Status, //@OtherDetails #endregion cmd.Parameters.AddWithValue("@TagNo", DALHelper.getNullORValue(cattle.TagNo)); cmd.Parameters.AddWithValue("@RegistrationDate", DALHelper.getNullORValue(DateTime.Now)); cmd.Parameters.AddWithValue("@AnimalType", DALHelper.getNullORValue(cattle.Type.ID)); cmd.Parameters.AddWithValue("@Gender", DALHelper.getNullORValue(cattle.Gender.Description[0])); //Changed 31-Mar. Now Gender is direct cmd.Parameters.AddWithValue("@BirthDate", DALHelper.getNullORValue(cattle.BirthDate)); cmd.Parameters.AddWithValue("@Weight", DALHelper.getNullORValue(cattle.CurrPhysicalAttribs.Weight)); cmd.Parameters.AddWithValue("@Height", DALHelper.getNullORValue(cattle.CurrPhysicalAttribs.CurrentSize.Height)); cmd.Parameters.AddWithValue("@Length", DALHelper.getNullORValue(cattle.CurrPhysicalAttribs.CurrentSize.Length)); cmd.Parameters.AddWithValue("@Width", DALHelper.getNullORValue(cattle.CurrPhysicalAttribs.CurrentSize.Width)); cmd.Parameters.AddWithValue("@Price", DALHelper.getNullORValue(cattle.Price)); cmd.Parameters.AddWithValue("@Source", DALHelper.getNullORValue(cattle.Source.ID)); cmd.Parameters.AddWithValue("@Breed", DALHelper.getNullORValue(cattle.Breed.ID)); cmd.Parameters.AddWithValue("@Status", DALHelper.getNullORValue(cattle.Status.ID)); cmd.Parameters.AddWithValue("@IsCalf", 'N'); //Added 31-March as added IsCalf column. Will be N for cattle and Y for Calf cmd.Parameters.AddWithValue("@OtherDetails", DALHelper.getNullORValue(cattle.OtherDetails)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
public int Insert(Customer cust) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertCustomer", con); cmd.CommandType = CommandType.StoredProcedure; //Be EXTRA CAREFUL WHILE ENTERING PARAMETERS as Compiler doesn't pick it // @DairyID varchar (15), //@Name varchar(50), //@Address varchar(80), //@ContactNo varchar(15), //@CNIC_No varchar(15), //@Quantity float, //@Type int, //@PriceCategory int, //@Reg_Date date, //@Del_Time time cmd.Parameters.AddWithValue("@DairyID", DALHelper.getNullORValue(cust.DairyID)); cmd.Parameters.AddWithValue("@Name", DALHelper.getNullORValue(cust.Name)); cmd.Parameters.AddWithValue("@Address", DALHelper.getNullORValue(cust.CurrentAddress)); cmd.Parameters.AddWithValue("@ContactNo", DALHelper.getNullORValue(cust.ContactNo)); cmd.Parameters.AddWithValue("@CNIC_No", DALHelper.getNullORValue(cust.CNIC_No)); cmd.Parameters.AddWithValue("@Reg_Date", DALHelper.getNullORValue(cust.RegistrationDate)); //Commented - 13 March Talha //cmd.Parameters.AddWithValue("@Quantity", DALHelper.getNullORValue(cust.Quantity)); //cmd.Parameters.AddWithValue("@Type", DALHelper.getNullORValue(cust.MilkType.ID)); //cmd.Parameters.AddWithValue("@PriceCategory", DALHelper.getNullORValue(cust.PriceCat.ID)); //cmd.Parameters.AddWithValue("@Reg_Date", DALHelper.getNullORValue(cust.RegistrationDate)); //cmd.Parameters.AddWithValue("@Del_Time", DALHelper.getNullORValue(cust.DelieveryTime.ID)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }
private List <ExpenseSubType> selectExpenseSubTypes(SqlCommand cmd, int parentID) { cmd.CommandType = CommandType.StoredProcedure; SqlConnection con = cmd.Connection; cmd.Parameters.AddWithValue("@ParentID", DALHelper.getNullORValue(parentID)); List <ExpenseSubType> ExpenseSubTypes = new List <ExpenseSubType>(); con.Open(); using (con) { SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { ExpenseSubTypes = new List <ExpenseSubType>(); while (dr.Read()) { //ID //Parent Category (ID) //Parent Category (Desc) //Description (of sub type) //Comments ExpenseCategory expCategory = new ExpenseCategory { ID = Convert.ToInt32(dr[1]), Description = Convert.ToString(dr[2]) }; ExpenseSubType expSubType = new ExpenseSubType { ID = Convert.ToInt32(dr[0]), Description = Convert.ToString(dr[3]) }; expSubType.ParentCategory = expCategory; ExpenseSubTypes.Add(expSubType); } } } return(ExpenseSubTypes); }
//Added 17 April public void UpdateStatus(Cattle selectedAnimal, int updatedStatus) { SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_updateAnimalStatus", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", DALHelper.getNullORValue(selectedAnimal.ID)); cmd.Parameters.AddWithValue("@NewStatus", DALHelper.getNullORValue(updatedStatus)); cmd.Parameters.AddWithValue("@Date", DALHelper.getNullORValue(DateTime.Now)); cmd.Parameters.AddWithValue("@Comments", DALHelper.getNullORValue("")); con.Open(); using (con) { cmd.ExecuteNonQuery(); } }
public void Add(AnimalInsemnation currAnimalInsemnation) { SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("sp_insertAnimalInsemnation", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Animal", DALHelper.getNullORValue(currAnimalInsemnation.CurrCattle.ID)); cmd.Parameters.AddWithValue("@Date", DALHelper.getNullORValue(currAnimalInsemnation.DateofInsemnation)); cmd.Parameters.AddWithValue("@Comments", DALHelper.getNullORValue(currAnimalInsemnation.Comments)); con.Open(); using (con) { cmd.ExecuteNonQuery(); } //return cmd_success; }
public int Insert(Animal Animal) { int cmd_success; SqlConnection con = DALHelper.getConnection(); SqlCommand cmd = new SqlCommand("Animals_insert_1", con); //Procedure without image for testing Talha cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", DALHelper.getNullORValue(Animal.ID)); con.Open(); using (con) { //Modified by Talha: Jan 26th, 2012 - Added Return variable to ensure data inserted successfully cmd_success = cmd.ExecuteNonQuery(); } return(cmd_success); }