ASPxGridView and LinqServerModeDataSource: Inserting and updating rows without showing all columns in grid

Question

How do I use a LinqServerModeDataSource to insert or edit rows of the underlying data table when I do not show all fields of that table in the ASPxGridView?

Similar questions have been asked, but this is not a duplicate. For example, this one asks about a LinqServerModeDataSource and the accepted answer tells how to use an ordinary SqlDataSource.

I have an ASPxGridView hooked up to a table via a LinqServerModeDataSource. But I do not show all columns in the grid. For example, there are columns for the date created and some others that the user doesn't need to know about. I am allowing inline editing in the grid, but in the Inserting or Updating event, the new values passed are just a dictionary of the values displayed in the grid.

What about the other values? I would expect to be able to set any of the values for the underlying data row programmatically in the event handler, regardless of whether they are displayed and thus edited by the user. How do I get access to them and set the other values in the events of the LinqServerModeDataSource? I am having no luck reading the devexpress documentation.

I'm guessing that there must be a Linq class that hooks into the table that I can use in those events, similarly to the Selecting event. But how?

Here's what the Selecting event handler looks like... Is there not some similar interface I can use to access the underlying data in the other events?

protected void dsRecipients_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e)
{
    SmsRecipientsDataContext context = new SmsRecipientsDataContext();
    IQueryable<NotificationParty> val = context.NotificationParties;

    int notificationGroupID = Convert.ToInt32(Context.Session["NotificationGroupID"]);
    val = val.Where(n => n.NotificationGroupID == notificationGroupID && n.Active);

    e.KeyExpression = "ID";
    e.QueryableSource = val;
}

Answer 1

As much as I hate answering my own question...

I can't figure out how to get this control to do what I want. However, a simple workaround is to handle the insert and update on the grid itself.

So, it's working now. I set the EnableUpdate and EnableInsert properties on the LinqServerModeDataSource to false, and simply handle the grid's RowInserting and RowUpdating events, where I go directly to the database.

For example, my inserting event handler is this:

protected void recipientsGrid_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e)
{
    using (SqlConnection connection = new SqlConnection(App_Logic.Wrappers.DatabaseConnectionString()))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.Transaction = connection.BeginTransaction();
            try
            {
                command.CommandText = " INSERT INTO NotificationParty(NotificationGroupID, FirstName, LastName, CellNumber, Active, UserCreated, DateCreated) VALUES " +
                    "(@NotificationGroupID, @FirstName, @LastName, @CellNumber, @Active, @UserCreated, GETDATE())";

                command.Parameters.AddWithValue("@NotificationGroupID", Convert.ToInt32(Context.Session["NotificationGroupID"]));
                command.Parameters.AddWithValue("@FirstName", e.NewValues["FirstName"]);
                command.Parameters.AddWithValue("@LastName", e.NewValues["LastName"]);
                command.Parameters.AddWithValue("@CellNumber", e.NewValues["CellNumber"]);
                command.Parameters.AddWithValue("@Active", 1);
                command.Parameters.AddWithValue("@UserCreated", Session["UID"]);

                command.ExecuteNonQuery();
                command.Transaction.Commit();
            }
            catch
            {
                command.Transaction.Rollback();
            }
        }
    }

    recipientsGrid.CancelEdit();
    e.Cancel = true;
}

And my updating event handler is this:

protected void recipientsGrid_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e)
{
    using (SqlConnection connection = new SqlConnection(App_Logic.Wrappers.DatabaseConnectionString()))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.Transaction = connection.BeginTransaction();
            try
            {
                command.CommandText = " UPDATE NotificationParty SET FirstName = @FirstName, LastName = @LastName, CellNumber = @CellNumber, UserModified = @UserModified, DateModified = GETDATE() WHERE ID = @ID";

                command.Parameters.AddWithValue("@ID", e.Keys[0]);
                command.Parameters.AddWithValue("@FirstName", e.NewValues["FirstName"]);
                command.Parameters.AddWithValue("@LastName", e.NewValues["LastName"]);
                command.Parameters.AddWithValue("@CellNumber", e.NewValues["CellNumber"]);
                command.Parameters.AddWithValue("@UserModified", Session["UID"]);

                command.ExecuteNonQuery();
                command.Transaction.Commit();
            }
            catch
            {
                command.Transaction.Rollback();
            }
        }
    }

    recipientsGrid.CancelEdit();
    e.Cancel = true;
}