Esempio n. 1
0
        public Simcha GetSimchaById(int simchaId)
        {
            using (var connection = new SqlConnection(_connectionString))
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = @"SELECT *, (
	                    SELECT ISNull(SUM(Amount), 0)
                                    FROM Contributions
                                    WHERE SimchaId = s.Id 
                    ) as 'Total', (
                    SELECT COUNT(*)
                                    FROM Contributions
                                    WHERE SimchaId = s.Id 
                    ) as 'ContributorAmount' FROM Simchas s
                    WHERE Id = @id";
                    cmd.Parameters.AddWithValue("@id", simchaId);
                    connection.Open();
                    var reader = cmd.ExecuteReader();
                    if (!reader.Read())
                    {
                        return(null);
                    }
                    var simcha = new Simcha();
                    simcha.Id   = (int)reader["Id"];
                    simcha.Date = (DateTime)reader["Date"];
                    simcha.Name = (string)reader["Name"];
                    simcha.ContributorAmount = (int)reader["ContributorAmount"];
                    simcha.Total             = (decimal)reader["Total"];
                    return(simcha);
                }
        }
Esempio n. 2
0
        public IEnumerable <Simcha> GetAllSimchas()
        {
            var simchas = new List <Simcha>();

            using (var connection = new SqlConnection(_connectionString))
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = @"SELECT *, (
	                            SELECT ISNull(SUM(Amount), 0)
                                            FROM Contributions
                                            WHERE SimchaId = s.Id 
                            ) as 'Total', (
                            SELECT COUNT(*)
                                            FROM Contributions
                                            WHERE SimchaId = s.Id 
                            ) as 'ContributorAmount' FROM Simchas s";
                    connection.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var simcha = new Simcha();
                        simcha.Id   = (int)reader["Id"];
                        simcha.Date = (DateTime)reader["Date"];
                        simcha.Name = (string)reader["Name"];
                        simcha.ContributorAmount = (int)reader["ContributorAmount"];
                        simcha.Total             = (decimal)reader["Total"];
                        simchas.Add(simcha);
                    }
                }

            return(simchas);
        }
Esempio n. 3
0
 public List <Simcha> GetSimchos()
 {
     using (SqlConnection connection = new SqlConnection(_connectionString))
         using (SqlCommand cmd = connection.CreateCommand())
         {
             List <Simcha> simchas = new List <Simcha>();
             cmd.CommandText = @"select s.*, COUNT(c.ContributorId) as contributorCount, sum(c.Amount) as total from Simchos s
                             left join Contributions c
                             on c.SimchaId = s.Id
                             group by s.Date, s.Name, s.Id
                             order by s.Date desc";
             connection.Open();
             SqlDataReader reader = cmd.ExecuteReader();
             while (reader.Read())
             {
                 Simcha simcha = new Simcha
                 {
                     Id               = (int)reader["Id"],
                     Name             = (string)reader["name"],
                     Date             = (DateTime)reader["date"],
                     ContributorCount = (int)reader["contributorCount"]
                 };
                 if (reader["total"] != null)
                 {
                     simcha.Total = reader.GetOrNull <decimal>("total");
                 }
                 else
                 {
                     simcha.Total = 0;
                 }
                 simchas.Add(simcha);
             }
             return(simchas);
         }
 }
Esempio n. 4
0
 public void AddSimcha(Simcha simcha)
 {
     using (var ctx = new SimchaDonorContext(_connectionString))
     {
         ctx.Simcha.Add(simcha);
         ctx.SaveChanges();
     }
 }
Esempio n. 5
0
 public ActionResult New(string name, DateTime date)
 {
     var mgr = new SimchaFundManager(Properties.Settings.Default.ConStr);
     Simcha simcha = new Simcha { Name = name, Date = date };
     mgr.AddSimcha(simcha);
     TempData["Message"] = "New Simcha Created! Id: " + simcha.Id;
     return RedirectToAction("index");
 }
Esempio n. 6
0
 public void AddSimcha(Simcha s)
 {
     using var connection = new SqlConnection(_connectionString);
     using var command    = connection.CreateCommand();
     command.CommandText  = @"INSERT INTO Simchas ([Simcha Name],Date)
                             VALUES (@simchaName,@date)";
     command.Parameters.AddWithValue("@simchaName", s.SimchaName);
     command.Parameters.AddWithValue("@date", s.Date);
     connection.Open();
     command.ExecuteNonQuery();
 }
Esempio n. 7
0
 public void AddSimcha(Simcha simcha)
 {
     using (var sqlConnection = new SqlConnection(_connectionString))
     using (var cmd = sqlConnection.CreateCommand())
     {
         sqlConnection.Open();
         cmd.CommandText = "INSERT INTO Simchas (Name, Date) VALUES (@name, @date); SELECT @@Identity";
         cmd.Parameters.AddWithValue("@name", simcha.Name);
         cmd.Parameters.AddWithValue("@date", simcha.Date);
         simcha.Id = (int)(decimal)cmd.ExecuteScalar();
     }
 }
Esempio n. 8
0
 public void NewSimcha(Simcha simcha)
 {
     using (var connection = new SqlConnection(_connectionString))
         using (var cmd = connection.CreateCommand())
         {
             cmd.CommandText = "INSERT INTO Simchas (Name,Date) VALUES (@Name,@Date)";
             cmd.Parameters.AddWithValue("@Name", simcha.Name);
             cmd.Parameters.AddWithValue("@Date", simcha.Date);
             connection.Open();
             cmd.ExecuteNonQuery();
         }
 }
Esempio n. 9
0
 public void AddSimcha(Simcha simcha)
 {
     using (var sqlConnection = new SqlConnection(_connectionString))
         using (var cmd = sqlConnection.CreateCommand())
         {
             sqlConnection.Open();
             cmd.CommandText = "INSERT INTO Simchas (Name, Date) VALUES (@name, @date) SELECT SCOPE_IDENTITY()";
             cmd.Parameters.AddWithValue("@name", simcha.Name);
             cmd.Parameters.AddWithValue("@date", simcha.Date);
             simcha.Id = (int)(decimal)cmd.ExecuteScalar();
         }
 }
Esempio n. 10
0
 public void AddSimcha(Simcha simcha)
 {
     using (SqlConnection connection = new SqlConnection(_connectionString))
         using (SqlCommand cmd = connection.CreateCommand())
         {
             cmd.CommandText = "insert into Simchos values(@name, @date)";
             cmd.Parameters.AddWithValue("@name", simcha.Name);
             cmd.Parameters.AddWithValue("@date", simcha.Date);
             connection.Open();
             cmd.ExecuteNonQuery();
         }
 }
Esempio n. 11
0
 public void AddSimcha(Simcha simcha)
 {
     using (SqlConnection connection = new SqlConnection(_connectionString))
         using (SqlCommand cmd = connection.CreateCommand())
         {
             cmd.CommandText = "INSERT INTO Simcha (Name, Date) " +
                               "VALUES(@Name, @Date)SELECT CAST(SCOPE_IDENTITY() AS INT)";
             cmd.Parameters.AddWithValue("@Name", simcha.Name);
             cmd.Parameters.AddWithValue("@Date", simcha.Date);
             connection.Open();
             simcha.Id = cmd.ExecuteNonQuery();
         }
 }
Esempio n. 12
0
 private void SetSimchaTotals(Simcha simcha)
 {
     using (var connection = new SqlConnection(_connectionString))
         using (var cmd = connection.CreateCommand())
         {
             cmd.CommandText = @"SELECT ISNull(SUM(Amount), 0) as Total, Count(*) as ContributorAmount
         FROM Contributions
         WHERE SimchaId = @simchaId";
             cmd.Parameters.AddWithValue("@simchaId", simcha.Id);
             connection.Open();
             var reader = cmd.ExecuteReader();
             reader.Read();
             simcha.ContributorAmount = (int)reader["ContributorAmount"];
             simcha.Total             = (decimal)reader["Total"];
         }
 }
Esempio n. 13
0
 public Simcha GetSimchaById(int simchaId)
 {
     using (SqlConnection connection = new SqlConnection(_connectionString))
         using (SqlCommand cmd = connection.CreateCommand())
         {
             cmd.CommandText = "select * from simchas where Id = @simchaId";
             cmd.Parameters.AddWithValue("@simchaId", simchaId);
             connection.Open();
             SqlDataReader reader = cmd.ExecuteReader();
             if (!reader.Read())
             {
                 return(null);
             }
             Simcha simcha = new Simcha();
             simcha.Id   = (int)reader["Id"];
             simcha.Date = (DateTime)reader["Date"];
             simcha.Name = (string)reader["Name"];
             return(simcha);
         }
 }
Esempio n. 14
0
 public IEnumerable <Simcha> GetSimchas()
 {
     using (SqlConnection connection = new SqlConnection(_connectionString))
         using (SqlCommand cmd = connection.CreateCommand())
         {
             cmd.CommandText = "select * from simchas";
             connection.Open();
             List <Simcha> results = new List <Simcha>();
             SqlDataReader reader  = cmd.ExecuteReader();
             while (reader.Read())
             {
                 Simcha simcha = new Simcha();
                 simcha.Id    = (int)reader["Id"];
                 simcha.Name  = (string)reader["Name"];
                 simcha.Date  = (DateTime)reader["Date"];
                 simcha.Total = GetTotalForSimcha(simcha.Id);
                 results.Add(simcha);
             }
             return(results);
         }
 }
Esempio n. 15
0
 public IEnumerable <Simcha> GetSimcha()
 {
     using (var con = new SqlConnection(_connectionString))
         using (var cmd = con.CreateCommand())
         {
             cmd.CommandText = "SELECT * FROM Simchas";
             List <Simcha> list = new List <Simcha>();
             con.Open();
             var reader = cmd.ExecuteReader();
             while (reader.Read())
             {
                 Simcha simcha = new Simcha();
                 simcha.Id         = (int)reader["Id"];
                 simcha.SimchaName = (string)reader["name"];
                 simcha.Date       = (DateTime)reader["date"];
                 SetSimchaTotals(simcha);
                 list.Add(simcha);
             }
             return(list);
         }
 }
Esempio n. 16
0
 public Simcha GetSimchaById(int simchaId)
 {
     using (var connection = new SqlConnection(_connectionString))
         using (var cmd = connection.CreateCommand())
         {
             cmd.CommandText = "SELECT * FROM Simchas WHERE Id = @id";
             cmd.Parameters.AddWithValue("@id", simchaId);
             connection.Open();
             var reader = cmd.ExecuteReader();
             if (!reader.Read())
             {
                 return(null);
             }
             var simcha = new Simcha();
             simcha.Id   = (int)reader["Id"];
             simcha.Date = (DateTime)reader["Date"];
             simcha.Name = (string)reader["Name"];
             SetSimchaTotals(simcha);
             return(simcha);
         }
 }
Esempio n. 17
0
 public Simcha GetSimchaById(int id)
 {
     using (var conn = new SqlConnection(_connectionString))
         using (var cmd = conn.CreateCommand())
         {
             cmd.CommandText = @"SELECT * FROM Simchas
                             WHERE Id = @id";
             cmd.Parameters.AddWithValue("@id", id);
             conn.Open();
             SqlDataReader reader = cmd.ExecuteReader();
             reader.Read();
             Simcha simcha = new Simcha
             {
                 Id            = (int)reader["Id"],
                 Name          = (string)reader["Name"],
                 Date          = (DateTime)reader["Date"],
                 Contributions = GetContributionCountForSimcha(id),
                 Total         = GetTotalAmountForSimcha(id)
             };
             return(simcha);
         }
 }
Esempio n. 18
0
        public IEnumerable<Simcha> GetAllSimchas()
        {
            var simchas = new List<Simcha>();
            using (var connection = new SqlConnection(_connectionString))
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM Simchas";
                connection.Open();
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var simcha = new Simcha();
                    simcha.Id = (int)reader["Id"];
                    simcha.Date = (DateTime)reader["Date"];
                    simcha.Name = (string)reader["Name"];
                    SetSimchaTotals(simcha);
                    simchas.Add(simcha);
                }
            }

            return simchas;
        }
Esempio n. 19
0
        public IEnumerable <Simcha> GetAllSimchas()
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
                using (SqlCommand cmd = connection.CreateCommand())
                {
                    List <Simcha> simchas = new List <Simcha>();
                    cmd.CommandText = @"SELECT * From Simcha";
                    connection.Open();

                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var simcha = new Simcha();
                        simcha.Date = (DateTime)reader["Date"];
                        simcha.Name = (string)reader["Name"];
                        simcha.Id   = (int)reader["Id"];
                        // simcha.Total = Total(simcha.Id);
                        simchas.Add(simcha);
                    }
                    return(simchas);
                }
        }
Esempio n. 20
0
 public Simcha GetSimchaById(int ContributorId)
 {
     using (var con = new SqlConnection(_connectionString))
         using (var cmd = con.CreateCommand())
         {
             cmd.CommandText = @"select top 1 * from simchas
                            where id = @id";
             cmd.Parameters.AddWithValue("@id", ContributorId);
             con.Open();
             SqlDataReader reader = cmd.ExecuteReader();
             if (!reader.Read())
             {
                 return(null);
             }
             Simcha simcha = new Simcha
             {
                 Id         = (int)reader["Id"],
                 SimchaName = (string)reader["name"],
                 Date       = (DateTime)reader["date"]
             };
             return(simcha);
         }
 }
Esempio n. 21
0
 private void SetSimchaTotals(Simcha simcha)
 {
     using (var connection = new SqlConnection(_connectionString))
     using (var cmd = connection.CreateCommand())
     {
         cmd.CommandText = @"SELECT ISNull(SUM(Amount), 0) as Total, Count(*) as ContributorAmount
         FROM Contributions
         WHERE SimchaId = @simchaId";
         cmd.Parameters.AddWithValue("@simchaId", simcha.Id);
         connection.Open();
         var reader = cmd.ExecuteReader();
         reader.Read();
         simcha.ContributorAmount = (int)reader["ContributorAmount"];
         simcha.Total = (decimal)reader["Total"];
     }
 }