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


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 Selverlight 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 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 "Save Data" button, the data will be saved in the database

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

 

Next Recommended Readings
F11 Research & Development, LLC
F11Research & Development, LLC