示例#1
0
        public IEnumerable <Simcha> GetSimchas()
        {
            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);
        }
示例#2
0
 public void AddSimcha(Simcha simcha)
 {
     using (var connection = new SqlConnection(connectionString))
         using (var command = connection.CreateCommand())
         {
             command.CommandText = @"INSERT INTO Simchas(Name,Date)
                               VALUES(@name,@date);
                               SELECT SCOPE_IDENTITY()";
             command.Parameters.AddWithValue("@name", simcha.Name);
             command.Parameters.AddWithValue("@date", simcha.Date);
             connection.Open();
             simcha.Id = (int)(decimal)command.ExecuteScalar();
         }
 }