//Get: /Weight/Create
public ActionResult Create()
{
WeightRecord weightRecord = new WeightRecord();
weightRecord.Date = DateTime.Now;
return View(weightRecord);
}
// Insert/Delete Methods
public int Add(WeightRecord weight)
{
SqlConnection connection = null;
SqlCommand command = null;
try
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Weights"].ConnectionString);
command = new SqlCommand("usp_InsertWeight", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter userNameParam = command.Parameters.Add("@userName", SqlDbType.NVarChar);
userNameParam.Direction = ParameterDirection.Input;
userNameParam.Value = weight.UserName;
SqlParameter weightIdParam = command.Parameters.Add("@weightKg", SqlDbType.Int);
weightIdParam.Direction = ParameterDirection.Input;
weightIdParam.Value = weight.Weight;
SqlParameter weightDateParam = command.Parameters.Add("@date", SqlDbType.DateTime);
weightDateParam.Direction = ParameterDirection.Input;
weightDateParam.Value = weight.Date;
SqlParameter returnParam = command.Parameters.Add("", SqlDbType.DateTime);
returnParam.Direction = ParameterDirection.ReturnValue;
connection.Open();
command.ExecuteScalar();
int r = (int)returnParam.Value;
return(r);
}
catch (Exception e)
{
throw new Exception("Error getting Sortie " + e.Message.ToString(), e);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
}
// Insert/Delete Methods
public int Add(WeightRecord weight)
{
SqlConnection connection = null;
SqlCommand command = null;
try
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Weights"].ConnectionString);
command = new SqlCommand("usp_InsertWeight", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter userNameParam = command.Parameters.Add("@userName", SqlDbType.NVarChar);
userNameParam.Direction = ParameterDirection.Input;
userNameParam.Value = weight.UserName;
SqlParameter weightIdParam = command.Parameters.Add("@weightKg", SqlDbType.Int);
weightIdParam.Direction = ParameterDirection.Input;
weightIdParam.Value = weight.Weight;
SqlParameter weightDateParam = command.Parameters.Add("@date", SqlDbType.DateTime);
weightDateParam.Direction = ParameterDirection.Input;
weightDateParam.Value = weight.Date;
SqlParameter returnParam = command.Parameters.Add("", SqlDbType.DateTime);
returnParam.Direction = ParameterDirection.ReturnValue;
connection.Open();
command.ExecuteScalar();
int r = (int)returnParam.Value;
return r;
}
catch (Exception e)
{
throw new Exception("Error getting Sortie " + e.Message.ToString(), e);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
}
public ActionResult Create(WeightRecord r2)
{
WeightRecord r = new WeightRecord();
try
{
r.UserName = User.Identity.Name;
UpdateModel(r);
int newId = weightRecords.Add(r);
return RedirectToAction("Details", new { id = newId });
}
catch
{
ModelState.AddRuleViolations(r.GetRuleViolations());
return View(r);
}
}
//
// Query Methods
public List <WeightRecord> GetWeights(string userID, int?weightId, int?pageIndex, int?pageSize, string orderBy, out int totalCount)
{
SqlConnection connection = null;
SqlCommand command = null;
try
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Weights"].ConnectionString);
command = new SqlCommand("usp_SelectWeights", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter totalCountParam = command.Parameters.Add("@totalCount", SqlDbType.Int);
totalCountParam.Direction = ParameterDirection.Output;
totalCountParam.Value = 10;
SqlParameter userNameParam = command.Parameters.Add("@userName", SqlDbType.NVarChar);
userNameParam.Direction = ParameterDirection.Input;
if (userID == string.Empty)
{
userID = null;
}
userNameParam.Value = userID;
SqlParameter weightIdParam = command.Parameters.Add("@weightID", SqlDbType.Int);
weightIdParam.Direction = ParameterDirection.Input;
weightIdParam.Value = weightId;
SqlParameter pageIndexParam = command.Parameters.Add("@pageIndex", SqlDbType.Int);
pageIndexParam.Direction = ParameterDirection.Input;
pageIndexParam.Value = pageIndex;
SqlParameter pageSizeParam = command.Parameters.Add("@pageSize", SqlDbType.Int);
pageSizeParam.Direction = ParameterDirection.Input;
pageSizeParam.Value = pageSize;
SqlParameter orderByParam = command.Parameters.Add("@orderBy", SqlDbType.VarChar);
orderByParam.Direction = ParameterDirection.Input;
orderByParam.Value = orderBy;
connection.Open();
SqlDataReader r = command.ExecuteReader();
while (r.Read())
{
WeightRecord newWeightRec = new WeightRecord();
newWeightRec.UserName = Convert.ToString(r["userName"]);
newWeightRec.Weight = Convert.ToDouble(r["weightKg"]);
newWeightRec.Date = Convert.ToDateTime(r["date"]);
newWeightRec.WeightRecordID = Convert.ToInt32(r["weightId"]);
weightrecords.Add(newWeightRec);
}
connection.Close(); // You have to close the output stream before you can read output parameters
totalCount = -1;
if (totalCountParam.Value != null)
{
totalCount = (int)totalCountParam.Value;
}
}
catch (Exception e)
{
throw new Exception("Error getting Sortie " + e.Message.ToString(), e);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return(weightrecords);
}
//
// Query Methods
public List<WeightRecord> GetWeights(string userID, int? weightId, int? pageIndex, int? pageSize, string orderBy, out int totalCount )
{
SqlConnection connection = null;
SqlCommand command = null;
try
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Weights"].ConnectionString);
command = new SqlCommand("usp_SelectWeights", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter totalCountParam = command.Parameters.Add("@totalCount", SqlDbType.Int);
totalCountParam.Direction = ParameterDirection.Output;
totalCountParam.Value = 10;
SqlParameter userNameParam = command.Parameters.Add("@userName", SqlDbType.NVarChar);
userNameParam.Direction = ParameterDirection.Input;
if (userID == string.Empty)
userID = null;
userNameParam.Value = userID;
SqlParameter weightIdParam = command.Parameters.Add("@weightID", SqlDbType.Int);
weightIdParam.Direction = ParameterDirection.Input;
weightIdParam.Value = weightId;
SqlParameter pageIndexParam = command.Parameters.Add("@pageIndex", SqlDbType.Int);
pageIndexParam.Direction = ParameterDirection.Input;
pageIndexParam.Value = pageIndex;
SqlParameter pageSizeParam = command.Parameters.Add("@pageSize", SqlDbType.Int);
pageSizeParam.Direction = ParameterDirection.Input;
pageSizeParam.Value = pageSize;
SqlParameter orderByParam = command.Parameters.Add("@orderBy", SqlDbType.VarChar);
orderByParam.Direction = ParameterDirection.Input;
orderByParam.Value = orderBy;
connection.Open();
SqlDataReader r = command.ExecuteReader();
while (r.Read())
{
WeightRecord newWeightRec = new WeightRecord();
newWeightRec.UserName = Convert.ToString(r["userName"]);
newWeightRec.Weight = Convert.ToDouble(r["weightKg"]);
newWeightRec.Date = Convert.ToDateTime(r["date"]);
newWeightRec.WeightRecordID = Convert.ToInt32(r["weightId"]);
weightrecords.Add(newWeightRec);
}
connection.Close(); // You have to close the output stream before you can read output parameters
totalCount = -1;
if (totalCountParam.Value != null)
{
totalCount = (int)totalCountParam.Value;
}
}
catch (Exception e)
{
throw new Exception("Error getting Sortie " + e.Message.ToString(), e);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return weightrecords;
}