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);
}
