C# IConnection.ExecuteNonQuery方法代码示例

 public void Delete(int id, IConnection conn)
 {
     string sql = @"DELETE FROM purchase WHERE [purchaseId] = @Id;";
     List<SqlParameter> prms = new List<SqlParameter>();
     prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
     var rowsAffected = conn.ExecuteNonQuery(sql,prms);
     if (rowsAffected < 1)
     {
         throw new Exception("Entity has not been deleted!");
     }
 }

        public void Delete(int id, IConnection conn)
        {
            string sql = @"DELETE FROM groupMember WHERE [groupMemberId] = @Id;";
            List<SqlParameter> prms = new List<SqlParameter>();

            var param1 = new SqlParameter
            {
                ParameterName = "@Id",
                Value = id
            };
            prms.Add(param1);

            var rowsAffected = conn.ExecuteNonQuery(sql,prms);

            if (rowsAffected < 1)
            {
                throw new Exception("GroupMember has not been deleted!");
            }
        }

        public void Update(int id, ItemCommentEntity itemComment, IConnection conn)
        {
            CheckItemCommentForRequiredValues(itemComment, RepositoryUtils.RepositoryAction.Update);

            var contactToUpdate = GetItemCommentById(id);
            if (contactToUpdate == null)
            {
                throw new Exception("ItemComment does not exist in database");
            }

            string sql = @"UPDATE itemComment SET [itemFK][email protected],
                                                    [commentorFK][email protected],
                                                    [commentText][email protected],
                                                    [isHiddenFromOwner][email protected](),
                                                    [updatePersonFK][email protected]
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();

            prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
            prms.Add(new SqlParameter { ParameterName = "@itemFK", Value = itemComment.itemFK });
            prms.Add(new SqlParameter { ParameterName = "@commentorFK", Value = itemComment.commentorFK });
            prms.Add(new SqlParameter { ParameterName = "@commentText", Value = itemComment.commentText });
            prms.Add(new SqlParameter { ParameterName = "@isHiddenFromOwner", Value = itemComment.isHiddenFromOwner ? "Y" : "N" });
            prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = itemComment.updatePersonFK });

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No ItemComments were updated with Id: {id}");
            }
        }

        public void Update(int id, GroupMemberEntity groupMember, IConnection conn)
        {
            CheckGroupMemberForRequiredValues(groupMember, RepositoryUtils.RepositoryAction.Update);

            var linkToUpdate = GetGroupMemberById(groupMember.groupMemberId);
            if (linkToUpdate == null)
            {
                throw new Exception("Contact does not exist in database");
            }

            string sql = @"UPDATE person SET [groupFK][email protected],
                                                    [memberFK][email protected],
                                                    [isAdmin][email protected],
                                                    [updateTimestamp]=getdate(),
                                                    [updatePersonFK][email protected]
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();

            var param1 = new SqlParameter
            {
                ParameterName = "@groupMemberId",
                Value = groupMember.groupMemberId
            };
            prms.Add(param1);

            var param2 = new SqlParameter
            {
                ParameterName = "@groupFK",
                Value = groupMember.groupFK
            };
            prms.Add(param2);

            var param3 = new SqlParameter
            {
                ParameterName = "@memberFK",
                Value = groupMember.memberFK
            };
            prms.Add(param3);

            var param4 = new SqlParameter
            {
                ParameterName = "@isAdmin",
                Value = groupMember.isAdmin ? 'Y' : 'N'
            };
            prms.Add(param4);

            var param5 = new SqlParameter
            {
                ParameterName = "@updatePersonFK",
                Value = groupMember.updatePersonFK
            };
            prms.Add(param5);

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No GroupMembers were updated with Id: {id}");
            }
        }

        public void Update(int id, GiftListGroupEntity giftListGroup, IConnection conn)
        {
            CheckGiftListGroupForRequiredValues(giftListGroup, RepositoryUtils.RepositoryAction.Update);

            var giftListGroupToUpdate = GetGiftListGroupById(giftListGroup.giftListGroupId);
            if (giftListGroupToUpdate == null)
            {
                throw new Exception("Gift List does not exist in database");
            }

            string sql = @"UPDATE person SET [giftListFK][email protected],
                                                    [groupFK][email protected],
                                                    [updateTimestamp]=getdate(),
                                                    [updatePersonFK][email protected]
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();

            var param1 = new SqlParameter
            {
                ParameterName = "@giftListFK",
                Value = giftListGroup.giftListFK
            };
            prms.Add(param1);

            var param2 = new SqlParameter
            {
                ParameterName = "@groupFK",
                Value = giftListGroup.groupFK
            };
            prms.Add(param2);

            var param3 = new SqlParameter
            {
                ParameterName = "@updatePersonFK",
                Value = giftListGroup.updatePersonFK
            };
            prms.Add(param3);

            var param4 = new SqlParameter
            {
                ParameterName = "@giftListGroupId",
                Value = giftListGroup.giftListGroupId
            };
            prms.Add(param4);

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Gift Lsits were updated with Id: {id}");
            }
        }

        public void Update(int id, LinkEntity link, IConnection conn)
        {
            CheckLinkForRequiredValues(link, RepositoryUtils.RepositoryAction.Update);

            var linkToUpdate = GetLinkById(link.linkId);
            if (linkToUpdate == null)
            {
                throw new Exception("Contact does not exist in database");
            }
            string sql = @"UPDATE person SET [itemFK][email protected],
                                                    [linkName][email protected],
                                                    [url][email protected],
                                                    [isImage][email protected],
                                                    [updateTimestamp]=getdate(),
                                                    [updatePersonFK][email protected]
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@linkId", Value = link.linkId });
            prms.Add(new SqlParameter { ParameterName = "@itemFK", Value = link.itemFK });
            prms.Add(new SqlParameter { ParameterName = "@linkName", Value = link.linkName });
            prms.Add(new SqlParameter { ParameterName = "@url", Value = link.url });
            prms.Add(new SqlParameter { ParameterName = "@isImage", Value = link.isImage ? 'Y' : 'N' });
            prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = link.updatePersonFK });

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Links were updated with Id: {id}");
            }
        }

        public void Update(int id, PurchaseEntity purchase, IConnection conn)
        {
            CheckPurchaseForRequiredValues(purchase, RepositoryUtils.RepositoryAction.Update);

            var purchaseToUpdate = GetPurchase(purchase.itemFK, purchase.purchaserFK);
            if (purchaseToUpdate == null)
            {
                throw new Exception("Contact does not exist in database");
            }
            string sql = @"UPDATE person SET [itemFK][email protected],
                                                    [purchaserFK][email protected],
                                                    [purchaseDate][email protected],
                                                    [updateTimestamp][email protected](),
                                                    [updatePersonFK][email protected]
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
            prms.Add(new SqlParameter { ParameterName = "@itemFK", Value = purchase.itemFK });
            prms.Add(new SqlParameter { ParameterName = "@purchaserFK", Value = purchase.purchaserFK });
            prms.Add(new SqlParameter { ParameterName = "@purchaseDate", Value = purchase.purchaseDate });
            prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = purchase.updatePersonFK });

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Purchases were updated with Id: {id}");
            }
        }

        public void Update(int id, GiftListEntity giftList, IConnection conn)
        {
            CheckGiftListForRequiredValues(giftList, RepositoryUtils.RepositoryAction.Update);

            var linkToUpdate = GetListById(giftList.giftListId);
            if (linkToUpdate == null)
            {
                throw new Exception("Gift List does not exist in database");
            }

            string sql = @"UPDATE person SET [personFK][email protected],
                                                    [listName][email protected],
                                                    [isPrivate][email protected],
                                                    [updateTimestamp]=getdate(),
                                                    [updatePersonFK][email protected]
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();

            var param1 = new SqlParameter
            {
                ParameterName = "@personFK",
                Value = giftList.personFK
            };
            prms.Add(param1);

            var param2 = new SqlParameter
            {
                ParameterName = "@listName",
                Value = giftList.listName
            };
            prms.Add(param2);

            var param3 = new SqlParameter
            {
                ParameterName = "@isPrivate",
                Value = giftList.isPrivate ? 'Y' : 'N'
            };
            prms.Add(param3);

            var param4 = new SqlParameter
            {
                ParameterName = "@updatePersonFK",
                Value = giftList.updatePersonFK
            };
            prms.Add(param4);

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Gift Lsits were updated with Id: {id}");
            }
        }

        public void Update(int id, ItemEntity item, IConnection conn)
        {
            CheckItemForRequiredValues(item, RepositoryUtils.RepositoryAction.Update);

            var contactToUpdate = GetItemById(id);
            if (contactToUpdate == null)
            {
                throw new Exception("Contact does not exist in database");
            }

            string sql = @"UPDATE person SET [itemStatusFK][email protected],
                                                    [giftListFK][email protected],
                                                    [itemName][email protected],
                                                    [description][email protected],
                                                    [updateTimestamp]=getdate(),
                                                    [updatePersonFK][email protected],
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
            prms.Add(new SqlParameter { ParameterName = "@itemStatusFK", Value = item.itemStatusFK });
            prms.Add(new SqlParameter { ParameterName = "@giftListFK", Value = item.giftListFK });
            prms.Add(new SqlParameter { ParameterName = "@itemName", Value = item.itemName });
            prms.Add(new SqlParameter { ParameterName = "@description", Value = item.description });
            prms.Add(new SqlParameter { ParameterName = "@updatePersonFK", Value = item.updatePersonFK });

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Contacts were updated with Id: {id}");
            }
        }

        public void Update(int id, GroupEntity group, IConnection conn)
        {
            CheckGroupForRequiredValues(group, RepositoryUtils.RepositoryAction.Update);

            var linkToUpdate = GetGroupById(group.groupId);
            if (linkToUpdate == null)
            {
                throw new Exception("Group does not exist in database");
            }

            string sql = @"UPDATE person SET [creatorFK][email protected],
                                                    [groupName][email protected],
                                                    [description][email protected],
                                                    [isPrivate][email protected],
                                                    [updateTimestamp]=getdate(),
                                                    [updatePersonFK][email protected]
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();

            var param1 = new SqlParameter
            {
                ParameterName = "@creatorFK",
                Value = group.creatorFK
            };
            prms.Add(param1);

            var param2 = new SqlParameter
            {
                ParameterName = "@groupName",
                Value = group.groupName
            };
            prms.Add(param2);

            var param3 = new SqlParameter
            {
                ParameterName = "@description",
                Value = group.description
            };
            prms.Add(param3);

            var param4 = new SqlParameter
            {
                ParameterName = "@groupId",
                Value = group.groupId
            };
            prms.Add(param4);

            var param5 = new SqlParameter
            {
                ParameterName = "@isPrivate",
                Value = group.isPrivate ? 'Y' : 'N'
            };
            prms.Add(param5);

            var param6 = new SqlParameter
            {
                ParameterName = "@updatePersonFK",
                Value = group.updatePersonFK
            };
            prms.Add(param6);

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Groups were updated with Id: {id}");
            }
        }

        public void Update(int id, PersonEntity person, IConnection conn)
        {
            CheckPersonForRequiredValues(person, RepositoryUtils.RepositoryAction.Update);

            var contactToUpdate = GetPersonById(id);
            if (contactToUpdate == null)
            {
                throw new Exception("Contact does not exist in database");
            }
            string sql =        @"UPDATE person SET [userName][email protected],
                                                    [emailAddress][email protected],
                                                    [firstName][email protected],
                                                    [lastName][email protected],
                                                    [passwordHash][email protected],
                                                    [updateTimestamp]=getdate()
                                                    WHERE [email protected]";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
            prms.Add(new SqlParameter { ParameterName = "@userName", Value = person.userName });
            prms.Add(new SqlParameter { ParameterName = "@emailAddress", Value = person.emailAddress });
            prms.Add(new SqlParameter { ParameterName = "@firstName", Value = person.firstName });
            prms.Add(new SqlParameter { ParameterName = "@lastName", Value = person.lastName });
            prms.Add(new SqlParameter { ParameterName = "@passwordHash", Value = person.passwordHash });

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Contacts were updated with Id: {id}");
            }
        }