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