Deleting an entity from a table which is dependent on another table

Question

I have 2 tables MachineGroups and Machines. MachineGroups has columns:

• MachinegroupID
• MachineGroupName
• MachineGroupDesc

And Machines has columns:

• MachineGroupID (FK)
• MachineID
• MachineName
• Machinedesc

Now I want to delete a machinegroup but not the ones that have machines in it. So if there are machines it should give an error message.... saying you cannot delete.

Here is what I am trying to do.....

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
    AutoGenerateColumns="False" CellPadding="1" CellSpacing="2"
    DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None"
     Width="100%" ondatabound="GridView1_DataBound1"
    onrowdatabound="GridView1_RowDataBound1">
    <RowStyle BackColor="#D0D8E8" ForeColor="#333333" Height="35px" />
    <Columns>
        <asp:BoundField DataField="MachineGroupID" HeaderText="MachineGroupID" 
            InsertVisible="False" ReadOnly="True" SortExpression="MachineGroupID" 
            Visible="False" />
        <asp:BoundField DataField="MachineGroupName" HeaderText="MachineGroupName" 
            SortExpression="MachineGroupName" />
        <asp:BoundField DataField="MachineGroupDesc" HeaderText="MachineGroupDesc" 
            SortExpression="MachineGroupDesc" />
        <asp:BoundField DataField="TimeAdded" HeaderText="TimeAdded" 
            SortExpression="TimeAdded" />
        <asp:TemplateField HeaderText="CanBeDeleted" SortExpression="CanBeDeleted" 
            Visible="False">
            <EditItemTemplate>
                <asp:CheckBox ID="CheckBox1" runat="server"
                    Checked='<%# Bind("CanBeDeleted") %>' />
            </EditItemTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="CheckBox1" runat="server"
                    Checked='<%# Bind("CanBeDeleted") %>' Enabled="false" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="No. of PCs" HeaderText="No. of PCs" ReadOnly="True"
            SortExpression="No. of PCs" />
        <asp:TemplateField ShowHeader="False">
            <ItemTemplate>
                <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
                    CommandName="Delete" Text="Delete"></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#4F81BD" Font-Bold="True" ForeColor="White"
        Height="30px" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="#E9EDF4" ForeColor="#284775" />
</asp:GridView>


<asp:SqlDataSource ID="SqlDataSource1" runat="server"         ConnectionString="<%$ ConnectionStrings:SumooHAgentDBConnectionString %>" 
    SelectCommand="SELECT MachineGroups.MachineGroupID, MachineGroups.MachineGroupName, MachineGroups.MachineGroupDesc, MachineGroups.TimeAdded, MachineGroups.CanBeDeleted, COUNT(Machines.MachineName) AS 'No. of PCs' FROM MachineGroups FULL OUTER JOIN Machines ON Machines.MachineGroupID = MachineGroups.MachineGroupID GROUP BY MachineGroups.MachineGroupID, MachineGroups.MachineGroupName, MachineGroups.MachineGroupDesc, MachineGroups.TimeAdded, MachineGroups.CanBeDeleted" 
    DeleteCommand="DELETE FROM MachineGroups WHERE (MachineGroupID = @original_MachineGroupID) AND (MachineGroupName = @original_MachineGroupName) AND (MachineGroupDesc = @original_MachineGroupDesc) AND (CanBeDeleted = @original_CanBeDeleted) AND (TimeAdded = @original_TimeAdded)">
    <DeleteParameters>
        <asp:Parameter Name="original_MachineGroupID" />
        <asp:Parameter Name="original_MachineGroupName" />
        <asp:Parameter Name="original_MachineGroupDesc" />
        <asp:Parameter Name="original_CanBeDeleted" />
        <asp:Parameter Name="original_TimeAdded" />
    </DeleteParameters>
</asp:SqlDataSource>

Please suggest what to do..

Answer 1

You need to delete the child rows first. So maybe have 2 delete procedures. Something like;

DELETE FROM machines WHERE MachineGroupID = @IN_MachineGroupID

DELETE FROM machinesGroups WHERE MachineGroupID = @IN_MachineGroupID

Using DataSets, and DataAdapters make this easier in my opinion. Look into ADO.Net disconnected layer.

I noticed you want to throw an error - Do you have code behind page?

You need to do

Try { call your delete; } catch(Exception ex) { throw (ex.Messgae) }

you can be more exact on the error you looking for bu tyou get the idea.

Davy

Answer 2

In good old SQL, all you need to do is delete the entries in MachineGroups that are not listed as a MachineGroupID in the Machines table:

DELETE FROM MachineGroups
 WHERE MachineGroupID NOT IN (SELECT DISTINCT MachineGroupID FROM Machines)

How you convert that into ASP/XML is anyone's guess.

Answer 3

I assume you have a foreign key relationship between the two tables Machines and MachineGroups, right? (based on the MachineGroupID)

If that FK relationship does not specify the ON DELETE CASCADE option, then what you can do is

• try to delete the machine group
• catch any exception that might happen that indicates it cannot be deleted because of existing child rows
• display that message (or a more user-friendly "translation" thereof) to the user

If that FK relationship however does specify the ON DELETE CASCADE option, then you'd have to first check to make sure there are not child rows for a given MachineGroupID before deleting that machine group.

Marc

Answer 4

Run a simple count on machines with that group id:

select count(id) from Machines where MachineGroupId == groupIdToDelete;

If there is a count you can display a nice error message or whatever.

If there is no count, proceed with the removal.

Answer 5

Handle your GridView's RowDeleting and RowDeleted events. In RowDeleting, you can run the query suggested by Palo, and run a query to check if the group is empty:

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int mgid = e.Keys["MachineGroupId"];
    if (!GroupIsEmpty(mgid))
    {
        e.Cancel = true;
    }
}

In 'RowDeleted', you can check if there was an exception, as there should be when your constraints are working properly, and you don't cancel the delete as described above:

protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
{
    if ((e.Exception != null) && (e.ExceptionHandled))
    {
        errorLabel.Text = "Machine group could not be deleted.  This is probably because machines still exist for the group.";
    }
}