public void RemoveTube(InseminationTube ins) { int ModifiedBy = (int)HttpContext.Current.Session["UserNo"]; DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); SqlCommand cmdI = con.CreateCommand(); cmdI.Connection = con; cmdI.Parameters.Clear(); cmdI.CommandText = CommonStrings.UpdateInseminationTube; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@TubeId", ins.TubeId); cmdI.Parameters.AddWithValue("@AnimalId", ins.AnimalFromId); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@ModifiedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@ModifiedBy", ModifiedBy); cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId); cmdI.Parameters.AddWithValue("@Removed", true); cmdI.ExecuteNonQuery(); cmdI.Connection.Close(); con.Dispose(); }
public InseminationTube InsertTube(InseminationTube ins) { InseminationTube ReturnObject = new InseminationTube(); int ModifiedBy = (int)HttpContext.Current.Session["UserNo"]; DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); con.Open(); SqlCommand cmdI = con.CreateCommand(); SqlTransaction trx = con.BeginTransaction(CommonStrings.InsertTransaction); cmdI.Connection = con; cmdI.Transaction = trx; try { cmdI.Parameters.Clear(); cmdI.CommandText = CommonStrings.InsertInseminationTube; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@AnimalId", ins.AnimalFromId); cmdI.Parameters.AddWithValue("@CreatedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@ModifiedDate", DateTime.Now); cmdI.Parameters.AddWithValue("@ModifiedBy", ModifiedBy); cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId); cmdI.Parameters.AddWithValue("@Removed", false); ins.TubeId = (int)cmdI.ExecuteScalar(); trx.Commit(); cmdI.Connection.Close(); } catch (SqlException ex) { if (trx != null) { trx.Rollback(); } } finally { if (con.State != ConnectionState.Closed) { con.Close(); } con.Dispose(); cmdI.Dispose(); trx.Dispose(); } return(ins); }
public List <InseminationTube> GetInseminationTubes() { List <InseminationTube> ReturnObject = new List <InseminationTube>(); InseminationTube ins; DataBaseConnection dbConn = new DataBaseConnection(); var table = GetCompaniesDBVariable(); if (table != null) { using (var con = dbConn.SqlConn()) { con.Open(); using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetInseminationTubes + " @Companies", con)) { var pList = new SqlParameter("@Companies", SqlDbType.Structured); pList.TypeName = "dbo.IntList"; pList.Value = table; cmd.Parameters.Add(pList); using (var drI = cmd.ExecuteReader()) { while (drI.Read()) { ins = new InseminationTube(); ins.TubeId = Convert.ToInt32(drI["TubeId"]); ins.AnimalFromId = Convert.ToInt32(drI["AnimalId"]); ins.CompanyId = Convert.ToInt32(drI["CompanyId"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins._FromAnimal = drI["OwnerAnimal"].ToString(); ins._User = drI["Username"].ToString(); ins._Company = drI["Company"].ToString(); ins.Used = Convert.ToBoolean(drI["Used"]); ins.Success = Convert.ToBoolean(drI["Success"]); ins.AnimalUsedId = Convert.ToInt32(drI["UsedOnAnimalId"]); ins._UsedOnAnimal = drI["UsedOnAnimal"].ToString(); ReturnObject.Add(ins); } } } } } return(ReturnObject); }
public List <SelectListItem> GetAnimlDropDownFromTube(int TubeId) { List <SelectListItem> ReturnObject = new List <SelectListItem>(); InseminationTube tube = GetInseminationTube(TubeId); SelectListItem ins = new SelectListItem(); ins.Text = tube._FromAnimal; ins.Value = tube.AnimalFromId.ToString(); ins.Selected = true; ReturnObject.Add(ins); return(ReturnObject); }
public InseminationTube GetInseminationTube(int TubeId) { InseminationTube ins = new InseminationTube(); DataBaseConnection dbConn = new DataBaseConnection(); SqlConnection con = dbConn.SqlConn(); SqlCommand cmdI = con.CreateCommand(); cmdI.Connection = con; cmdI.Parameters.Clear(); cmdI.CommandText = CommonStrings.GetInseminationTube; cmdI.CommandType = System.Data.CommandType.StoredProcedure; cmdI.Parameters.AddWithValue("@TubeId", TubeId); cmdI.Connection.Open(); SqlDataReader drI = cmdI.ExecuteReader(); if (drI.HasRows) { while (drI.Read()) { ins.TubeId = Convert.ToInt32(drI["TubeId"]); ins.AnimalFromId = Convert.ToInt32(drI["AnimalId"]); ins.CreatedDate = Convert.ToDateTime(drI["CreatedDate"]); ins.CompanyId = Convert.ToInt32(drI["CompanyId"]); ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]); ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]); ins._FromAnimal = drI["Animal"].ToString(); } } cmdI.Connection.Close(); con.Dispose(); return(ins); }