Insert, Update and Delete in Silverlight DataGrid using ADO.NET

Introduction

 

This article is in continuation of the previous one in which I discussed how to create ADO.NET Data Service and access Database using Silverlight 2. Here I am going to show how to perform Insert, Update, and Delete in Silverlight Datagrid using ADO.NET Data Service.

 

1. Create a class of the Entities type

TestEntities proxy;

2. Create a variable to track if DataGrid is in Edit mode

private bool inEdit;

3. Create Page_Loaded event to initailize the ADO.NET Data Service

void Page_Loaded(object sender, RoutedEventArgs e) {
  proxy = new TestEntities(new Uri("WebDataService.svc", UriKind.Relative));
}

4. Create Events for the DataGrid

< my: DataGrid x: Name = "dataGrid"
Margin = "10"
AutoGenerateColumns = "True"
AutoGeneratingColumn = "OnGeneratedColumn"
BeginningEdit = "dataGrid_BeginningEdit"
CommittingEdit = "dataGrid_CommittingEdit"
CancelingEdit = "dataGrid_CancelingEdit"
KeyDown = "dataGrid_KeyDown" / >

5. AttachTo method is used when an entity exists in the store already and you would want the DataServiceContext to track that entity. Use AddObject or AddTo method when a new entity is created and want the Context to track the entity. Write the Event Handelers for DataGrid Events

private void dataGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e) {
  inEdit = true;
}
private void dataGrid_CancelingEdit(object sender, DataGridEndingEditEventArgs e) {
  inEdit = false;
}
private void dataGrid_CommittingEdit(object sender, DataGridEndingEditEventArgs e) {
  //Attach the object to the context.
  try {
    proxy.AttachTo("Users", dataGrid.SelectedItem);
  }
  catch {}
  proxy.UpdateObject(e.Row.DataContext);
}

6. Create a ObservableCollection

/// <summary>
/// Represents a dynamic data collection that provides notifications when items get added, removed, or when the whole list is refreshed.
/// </summary>
ObservableCollection < Users > BoundData {
  get {
    return (dataGrid.ItemsSource as ObservableCollection < Users > );
  }
}

7. KeyDown Event of the DataGrid to Insert, Update and Delete rows

private void dataGrid_KeyDown(object sender, KeyEventArgs e) {
  if (!inEdit) {
    TextBlockStatus.Text = "";
    if (e.Key == Key.Delete) {
      if (dataGrid.SelectedItem != null) {
        //Attach the object to the context.
        try {
          proxy.AttachTo("Users", dataGrid.SelectedItem);
        }
        catch(Exception ex) {
          TextBlockStatus.Text = ex.Message;
        }
        proxy.DeleteObject(dataGrid.SelectedItem);
        // Remove from the bound collection, disappears from DataGrid.
        BoundData.Remove(dataGrid.SelectedItem as Users);
      }
    }
    else if (e.Key == Key.Insert) {
      Users u = new Users() {
        FirstName = "",
        LastName = ""
      };
      int index = BoundData.IndexOf(dataGrid.SelectedItem as Users);
      BoundData.Insert(index, u);
      dataGrid.SelectedIndex = index;
      dataGrid.BeginEdit();
      proxy.AddObject("Users", u);
    }
  }
}

8. Save the changes to the database

void ButtonSave_Click(object sender, RoutedEventArgs args) {
  //save the changes to the database
  proxy.BeginSaveChanges(SaveChangesOptions.Batch, (asyncResult) =>{
    try {
      proxy.EndSaveChanges(asyncResult);
    }
    catch(Exception ex) {
      TextBlockStatus.Text = ex.Message;
    }
  },
  null);
  //datagrid is not in edit mode anymore
  inEdit = false;
  //show the message
  TextBlockStatus.Text = "Changes Saved to the database";
}

9. Currently the Data Contract objects do not support INotifyPropertyChanged or INotifyCollectionChanged so change the Proxy.cs from

[global::System.Data.Services.Common.DataServiceKeyAttribute("UserID")]
public partial class Users {
  /// <summary>
  /// Create a new Users object.
  /// </summary>
  /// <param name="userID">Initial value of UserID.</param>
  public static Users CreateUsers(int userID) {
    Users users = new Users();
    users.UserID = userID;
    return users;
  }...
  to[global::System.Data.Services.Common.DataServiceKeyAttribute("UserID")]
  public partial class Users: System.ComponentModel.INotifyPropertyChanged {
    public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;

    protected virtual void OnPropertyChanged(string propertyName) {
      if (PropertyChanged != null) {
        PropertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
      }
    }
    /// <summary>
    /// Create a new Users object.
    /// </summary>
    /// <param name="userID">Initial value of UserID.</param>
    public static Users CreateUsers(int userID) {
      Users users = new Users();
      users.UserID = userID;
      return users;
    }...

That's all. You are done. Build and run the application.

 

To load the data in DataGrid, press the GetData button.  

 

 

Select the DataGrid and Press Insert from KeyBoard. A new row will be inserted in the DataGrid. You can type text in this new row and click button Save Data to save the new row in the database.

 

 

Press the "Save Data" button, the data will be saved in the database.

 

To delete any row select the row and press the Delete button from KeyBoard, row will be removed from the DataGrid, and to persist the changes in the database click the Save Data button. Similarly to update any column, select the row, update First name or last name, and press the Save Data button.

 

Summary

 

In this article, we learned about Insert, Update, and Delete in Silverlight DataGrid using ADO.NET with example.