Sql – LinqDataSource/ListView for child records – only save when the containing record is saved

asp.netlinq-to-sqllinqdatasource

I have a single form for editing an event, in which the user can (a) edit the details of the event (title, description, dates, etc.) in a FormView and (b) view and edit a ListView of contacts who are registered for the event.

Here's my LinqDataSource, which allows me to add and remove contacts to the event. 

<asp:linqdatasource runat="server" id="ContactsDatasource" contexttypename="Db" tablename="ContactsCalendarEventsXtabs" autogeneratewhereclause="true" enabledelete="true" enableinsert="true">
    <whereparameters>
        <asp:querystringparameter name="CalendarEventID" querystringfield="id" type="Int32" />
    </whereparameters>
    <insertparameters>
        <asp:querystringparameter name="CalendarEventID" querystringfield="id" type="Int32" />
    </insertparameters>
</asp:linqdatasource>

This works fine, but of course it persists the changes to the database as they're made; and it only works when the event has already been created. Ideally, I'd like for my changes to the ListView to be persisted only once the FormView saves (so if someone makes some changes to the ListView and then cancels out of the FormView, the changes are discarded). Along the same lines, I'd like to be able to create a new event, enter its details, and sign some people up for it, all at once; when the FormView saves, it gets the new ID for the event, and then the ListView saves using that ID.

In the past (pre-Linq) I've accomplished this with my own extensively customized FormView and SqlDataSource objects, which take care of temporarily persisting the data changes, getting the event ID from the FormView, etc. Is there a more standard way of dealing with this scenario using the LinqDataSource?

Best Answer

So effectively you want to wrap the two LinqDataSources in a single transaction. There is a sneaky way to leverage the LinqDataSource databinding and events and still do only one commit. Using this method you can still use Dynamic Data, FormView, GridView, validation, etc. It only relies on hooking into the data sources.

Example markup:

<FormView>...</FormView>
<ListView>...</ListView>
<LinqDataSource ID="dsEvent" ...> </LinqDataSource>
<LinqDataSource ID="dsContact" ...> </LinqDataSource>
<asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit it All!"/>

Now in the code behind you are using the single Submit button to mimic the behaviour of the LinqDataSources. You use the datasource to insert the item, which creates the new object, snaffle the object, do it again for the second object. Link the two items together and any other logic you want and then insert it into the database as a single transaction. The key is to set the Cancel flag in the Inserting event so that the datasource doesn't actually insert into the database.

protected void btnSubmit_Click(object sender, EventArgs e)
{
    Event evt = null;
    Contact contact = null;

    dsEvent.Inserting += (o,ea) => { evt = (ea.NewObject as Event); ea.Cancel = true; };
    dsEvent.InsertItem(true);
    dsContact.Inserting += (o, ea) => { contact = (ea.NewObject as Contact); ea.Cancel = true; };
    dsContact.InsertItem(true);

    evt.Contacts.Add(contact);

    using (var dbContext = new ContactsCalendarEventsXtabs())
    {
        dbContext.Events.InsertOnSubmit(evt);
        dbContext.SubmitChanges();
    }
}
Related Topic