Using SELECT Statement to INSERT records in Table

Edwin writes "Ok, this may be simple to some, but it sure is a puzzle to me. I want to move a set of data from one table to another table with a similar structure. What I figure was a shady solution: Selecting the data from the source table, then opening the other table and using a loop to populate the destination table. Question: Is there a way I can use just ONE insert statement to do all this? Sort of like incorporating the select statement into the insert statement? If there is one, then please give me some example code." I certainly can do this in one statement (but looks like two). 

(This article has been updated through SQL Server 2005.)

I'll use the SELECT statement in conjunction with the INSERT statement to make this as easy as possible. Normally, I would code an INSERT statement something like this (using the pubs database):

INSERT authors (au_id, au_lname, au_fname, contract)
VALUES ('123-45-6789', 'Gates', 'Bill', 1)

This will insert one row into the authors table. I could write a program to loop through a set of records and insert them one at a time into another table. SQL Server is designed for set processing. It is optimized to handle groups or sets of records.

I can actually replace the VALUES clause with a SELECT statement that will return a set of records. Suppose I have a table called CALIFORNIA_AUTHORS and we want to populate it with the ID and names of the authors from California. The statement would look something like this:

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

This will take the 15 records with State='CA' and load them into the table CALIFORNIA_AUTHORS. I can use any type of SELECT statement here. It just has to return a record set that matches the columns in the INSERT statement. The number of columns and their data types must match (or be implicitly convertible).

I can also execute a stored procedure that returns a record set using the EXEC command in place of the SELECT statement.

Using REPLACE in an UPDATE statement

This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment. 

Replace searches for certain characters in a string and replaces them with other characters. So this statement:

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'Rolls')

will return

SQLTeam.com Rolls!

REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string. You can also do replacements of different sizes. For example,

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'is cool')

gives us

SQLTeam.com is cool!

I replaced a five character string with a seven character string with no problem. If the string isn't found, no changes will be made.

SELECT Replace('SQLTeam.com Rocks!', 'Yak', 'Tibetan bison')

returns exactly what we started with which is

SQLTeam.com Rocks!

If it doesn't find anything to change it just returns the string unchanged. You can use REPLACE in an UPDATE statement. Using the pubs database we could write:

Update dbo.authors
Set    city = replace(city, 'Salt', 'Olympic');

There were two authors that had "Salt Lake City" in the CITY field. Now that field holds "Olympic Lake City" for those two authors. The CITY field is unchanged for all the other authors.

A more common approach is to use this in conjuntion with a WHERE clause like this:

UPDATE dbo.authors
SET    city = replace(city, 'Salt', 'Olympic')
WHERE  city LIKE 'Salt%';

This only affects the rows that start with 'Salt'.

Note: This article was originally published in January 2002 and has been updated through SQL Server 2008 R2. 

Silverlight DataGrid - Performing DML Operations (Update, Delete and Insert)

I came across a requirement for providing an ASP.NET GridView like behavior for the Silverlight DataGrid. I thought of writing an article and sharing my code with you all. In this article, we will see how to perform Insert, Update and Delete operations in the Silverlight DataGrid.  We will first see the Update and Delete operations, and then explore Insert operations on the Silverlight DataGrid. I have used WCF Data Service in this article.

I have also used a SQL Server 2008 database and the Database name is ‘Company’ with a table ‘Customer’. The DDL statement is as below:

CREATETABLE [dbo].[Customer](

      [CustomerID] [int] NOTNULL,

      [CustomerName] [varchar](50)NOTNULL,

      [Address] [varchar](50)NOTNULL,

      [City] [varchar](50)NOTNULL,

      [State] [varchar](50)NOTNULL,

      [Age] [int] NOTNULL,

 CONSTRAINT [PK_Customer] PRIMARYKEYCLUSTERED

(

      [CustomerID] ASC

)WITH (PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY=OFF,

ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY]

)ON [PRIMARY]

Creating WCF Data Service

 

In this step we will be creating a WCF Data Service using VS2010. This is currently targeting .NET 3.5 SP1.

Step 1: Open VS2010 and create a blank solution. Name it as ‘SILV4_DataGrid_DML’. To this solution add a ‘WCF Service Application’ project, name it as ‘WCF_DMLService’. Since we are going to use WCF Data Service, we do not more require the interface ‘IService1’ and ‘Service1.svc’, so delete both these files.

Step 2: In the service project, add a new ADO.NET Entity Data Model, name it as ‘CompanyEDMX.edmx’. Complete the Wizard. Select SQL Server connection to ‘Company’ database and select ‘Customer’ table. After the completion of the wizard, you will find the following .edmx generated.

This will contains methods for Insert, Update and Delete operations. This will also contain entity of name ‘Customer’ which will read all rows from the ‘Customer’ table.

Step 3: In the service project, add a new ‘WCF Data Service’ template and name it as ‘CustomerDataService.svc’. Write the following code in the svc file:

C#

public class CustomerDataService : DataService<CompanyEntities>

{

    public static void InitializeService(IDataServiceConfiguration config)

    {

        config.SetEntitySetAccessRule("Customer", EntitySetRights.All);

 

    }

}

 

VB.NET (Converted Code)

Public Class CustomerDataService

      Inherits DataService(Of CompanyEntities)

            Public Shared Sub InitializeService(ByVal config As IDataServiceConfiguration)

                  config.SetEntitySetAccessRule("Customer", EntitySetRights.All)

 

            End Sub

End Class

 

The above class defines ‘InitializeService’ method which will initialize service side settings e.g. define access rules for all entities for performing DML operations (EntitySerRights.All).

(Note: Since this is WCF Data Service, we no more require ‘System.ServiceModel’ tag, so remove it from the Web.Config file.)

Step 4: Publish the service on IIS (recommended version IIS 7.0). Test the Svc page by browsing it, the following result must be displayed:

This output represents the connectivity between the WCF Data Service and SQL Server Database.

Once this is done, now it is time to develop our Silverlight application.

Creating Silverlight Client Application

 

In this step, we will create a Silverlight 3.0 client application using VS2010. You can select Silverlight 4.0 version as well, but then in that case the WCF Data Service referencing in Silverlight 4.0 need to be changed. So I will stick with Silverlight 3.0 for now.

Step 1: In the solution created above, add a new Silverlight application and name it as ‘Silv4_DataGrid_DML_Application’.

Step 2: In this application, add a WCF Data Service reference, name it as ‘MyRef’.

Step 3: In the MainPage.Xaml add the following Xaml:

<UserControl xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" 

             x:Class="Silv4_DataGrid_DML_Application.MainPage"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

    mc:Ignorable="d"

    d:DesignHeight="400" d:DesignWidth="850"

             xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"

              Loaded="UserControl_Loaded">

 

    <Grid x:Name="LayoutRoot" Background="White" Height="398" Width="849">

        <Grid.RowDefinitions>

            <RowDefinition Height="76*" />

            <RowDefinition Height="322*" />

        </Grid.RowDefinitions>

        <TextBlock Height="52" HorizontalAlignment="Left" Margin="16,11,0,0"

                   Name="textBlock1" Text="Customer Information System"

                    TextAlignment="Center" VerticalAlignment="Top" Width="674"

                   FontSize="36" FontFamily="Comic Sans MS" />

        <data:DataGrid x:Name="dgCustomer" AutoGenerateColumns="False" Grid.Row="1"  RowEditEnded="dgCustomer_RowEditEnded" SelectionChanged="dgCustomer_SelectionChanged" Margin="0,0,237,0">

            <data:DataGrid.Columns>

                <data:DataGridTextColumn Binding="{Binding CustomerID}" Header="Customer Id" IsReadOnly="True"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding CustomerName}" Header="Customer Name" IsReadOnly="True"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding Address}" Header="Address" IsReadOnly="True"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding City}" Header="City" IsReadOnly="True"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding State}" Header="State" IsReadOnly="True"></data:DataGridTextColumn>

                <data:DataGridTextColumn Binding="{Binding Age}" Header="Age" Width="Auto" IsReadOnly="True"></data:DataGridTextColumn>

                <data:DataGridTemplateColumn Header="Update">

                    <data:DataGridTemplateColumn.CellTemplate>

                        <DataTemplate>

                            <Button x:Name="btnUpdate" Content="Update" Click="btnUpdate_Click"></Button>

                        </DataTemplate>

                    </data:DataGridTemplateColumn.CellTemplate>

                </data:DataGridTemplateColumn>

 

                <data:DataGridTemplateColumn Header="Delete">

                    <data:DataGridTemplateColumn.CellTemplate>

                        <DataTemplate>

                            <Button x:Name="btnDelete" Content="Delete" Click="btnDelete_Click"></Button>

                        </DataTemplate>

                    </data:DataGridTemplateColumn.CellTemplate>

                </data:DataGridTemplateColumn>

 

            </data:DataGrid.Columns>

        </data:DataGrid>

    </Grid>

</UserControl>

 

The above xaml adds Custom ‘Update’ and ‘Delete’ button. Ignore the events for now, as we will be using it in the code behind.

Step 4: Add the following code in ‘MainPage.Xaml.cs’ at the class level:

C#

MyRef.CompanyEntities ProxyEntities;

MyRef.Customer objCust,objMyCust;

List<MyRef.Customer> Result = null;

ObservableCollection<MyRef.Customer> ResultCustomer =null;

bool IsUpdated = false;

bool IsDeleted = false;

 

 

VB.NET(Converted Code)

Dim ProxyEntities As MyRef.CompanyEntities

Dim objCust, objMyCust As MyRef.Customer

Dim Result As List(Of MyRef.Customer) = Nothing

Dim ResultCustomer As ObservableCollection(Of MyRef.Customer) =Nothing

Dim IsUpdated As Boolean = False

Dim IsDeleted As Boolean = False

 

Step 5: Now since we are using Silverlight as a client to the WCF Data Service, every request is made asynchronous. To make an async call, use the class ‘DataServiceQuery<T>’ present under the namespace ‘System.Data.Services.Client’. Write the following method in the ‘MainPage.Xaml.cs’ to make an async call to service as below:

C#

private void BindCustomerGrid()

{

    var custToBind = from Cust in ProxyEntities.Customer

                        select Cust;

 

    var dsQueryCust = (DataServiceQuery<MyRef.Customer>)custToBind;

 

 

    dsQueryCust.BeginExecute(OnCallCompleted, dsQueryCust);

}

 

void OnCallCompleted(IAsyncResult ar)

{

    try

    {

        ResultCustomer.Clear(); 

        var qry = ar.AsyncState as DataServiceQuery<MyRef.Customer>;

 

        Result = qry.EndExecute(ar).ToList<MyRef.Customer>();

 

        dgCustomer.ItemsSource = ConvertListToObservableCollection(Result);

    }

    catch(Exception ex)

    {

        string s = ex.Message;

               

    }

}

 

private ObservableCollection<MyRef.Customer> ConvertListToObservableCollection(List<MyRef.Customer> lstCustomers)

{

    foreach (var item in lstCustomers)

    {

        ResultCustomer.Add(item);

    }

 

    return ResultCustomer;

}

 

 

VB.NET (Converted Code)

 

Private Sub BindCustomerGrid()

      Dim custToBind = From Cust In ProxyEntities.Customer

                       Select Cust

 

      Dim dsQueryCust = CType(custToBind, DataServiceQuery(Of MyRef.Customer))

 

 

      dsQueryCust.BeginExecute(AddressOf OnCallCompleted, dsQueryCust)

End Sub

 

Private Sub OnCallCompleted(ByVal ar As IAsyncResult)

      Try

            ResultCustomer.Clear()

            Dim qry = TryCast(ar.AsyncState, DataServiceQuery(Of MyRef.Customer))

 

            Result = qry.EndExecute(ar).ToList(Of MyRef.Customer)()

 

            dgCustomer.ItemsSource = ConvertListToObservableCollection(Result)

      Catch ex As Exception

            Dim s As String = ex.Message

 

      End Try

End Sub

 

Private Function ConvertListToObservableCollection(ByVal lstCustomers As List(Of MyRef.Customer)) As ObservableCollection(Of MyRef.Customer)

      For Each item In lstCustomers

            ResultCustomer.Add(item)

      Next item

 

      Return ResultCustomer

End Function

The above code will read data from the WCF Data Service from ‘Customer’ entity. Once all the rows are read using ‘DataServiceQuery<T>’, the async execution will be performed on the client side. This async execution will generate the List<T> result and using ‘ConvertListToObservableCollection’ method the result will be converted into ObservableCollection<Customer> type. This will be used as an ItemsSource for the DataGrid. 

Step 6: Write the following code in Loaded event of the Silverlight Page as below (change the url at your end if needed):

C#

private void UserControl_Loaded(object sender, RoutedEventArgs e)

{

    ProxyEntities = new MyRef.CompanyEntities(new Uri("http://localhost:8090/CUST_WCF_Vd/CustomerDataService.svc/"));

    ResultCustomer = new ObservableCollection<MyRef.Customer>();

    BindCustomerGrid();

}

 

VB.NET (Converted Code)

Private Sub UserControl_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)

      ProxyEntities = New MyRef.CompanyEntities(New Uri("http://localhost:8090/CUST_WCF_Vd/CustomerDataService.svc/"))

      ResultCustomer = New ObservableCollection(Of MyRef.Customer)()

      BindCustomerGrid()

End Sub

Step 7: Run the application here, you will find the result as shown below:

Step 8: Now to perform ‘Update’ and ‘Delete’ operations, we need to first select record from the DataGrid. To do this, we will now use the ‘SelectionChanged’ event as below:

C#

private void dgCustomer_SelectionChanged(object sender, SelectionChangedEventArgs e)

{

    objCust = dgCustomer.SelectedItem as MyRef.Customer;

}

 

 

VB.NET (Converted Code)

Private Sub dgCustomer_SelectionChanged(ByVal sender As Object, ByVal e As SelectionChangedEventArgs)

      objCust = TryCast(dgCustomer.SelectedItem, MyRef.Customer)

End Sub

The above code will select the record to be updated or deleted.

Step 9: Since in XAML, we have made all the DataGrid columns as ReadOnly, to perform ‘Update’ operation we need to change it to an editable form. Write the following code in Update button click event as below:

C#

private void btnUpdate_Click(object sender, RoutedEventArgs e)

{

    IsUpdated = true;

 

    dgCustomer.Columns[2].IsReadOnly = false;

    dgCustomer.Columns[3].IsReadOnly = false;

    dgCustomer.Columns[4].IsReadOnly = false;

    dgCustomer.Columns[5].IsReadOnly = false;

}

 

VB.NET (Converted Code)

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)

      IsUpdated = True

 

      dgCustomer.Columns(2).IsReadOnly = False

      dgCustomer.Columns(3).IsReadOnly = False

      dgCustomer.Columns(4).IsReadOnly = False

      dgCustomer.Columns(5).IsReadOnly = False

End Sub

Step 10: Write the following code for disabling the columns:

C#

private void DisableAll()

{

    dgCustomer.Columns[2].IsReadOnly = true;

    dgCustomer.Columns[3].IsReadOnly = true;

    dgCustomer.Columns[4].IsReadOnly = true;

    dgCustomer.Columns[5].IsReadOnly = true;

}

 

VB.NET (Converted Code)

Private Sub DisableAll()

      dgCustomer.Columns(2).IsReadOnly = True

      dgCustomer.Columns(3).IsReadOnly = True

      dgCustomer.Columns(4).IsReadOnly = True

      dgCustomer.Columns(5).IsReadOnly = True

End Sub

Step 11: To edit the row in the DataGrid, we need to use ‘RowEditEnded’ event. To read values entered during the edit operation we need to make use of ‘FrameworkElement’ class. This class detects the element in the current edited cell. The code is as below:

C#

private void dgCustomer_RowEditEnded(object sender, DataGridRowEditEndedEventArgs e)

{

        #region Editing Operation

        if (IsUpdated)

        {

            var dsQrycutoToEdit = (from Cust in ProxyEntities.Customer

                                    where Cust.CustomerID == objCust.CustomerID

                                    select Cust) as DataServiceQuery<MyRef.Customer>;

 

            dsQrycutoToEdit.BeginExecute(onDataGridRowEditSelected, dsQrycutoToEdit);

 

            FrameworkElement elementCustAddress = dgCustomer.Columns[2].GetCellContent(e.Row);

            string txtAddress = ((TextBlock)elementCustAddress).Text;

            objCust.Address = txtAddress;

 

            FrameworkElement elementCustCity = dgCustomer.Columns[3].GetCellContent(e.Row);

            string txtCity = ((TextBlock)elementCustCity).Text;

            objCust.Address = txtCity;

 

            FrameworkElement elementCustState = dgCustomer.Columns[4].GetCellContent(e.Row);

            string txtState = ((TextBlock)elementCustState).Text;

            objCust.State = txtState;

 

            FrameworkElement elementCustAge = dgCustomer.Columns[5].GetCellContent(e.Row);

            string txtAge = ((TextBlock)elementCustAge).Text;

            objCust.Age = Convert.ToInt32(txtAge);

        }

        #endregion

}

 

VB.NET (Converted Code)

 

Private Sub dgCustomer_RowEditEnded(ByVal sender As Object, ByVal e As DataGridRowEditEndedEventArgs)

'           #Region "Editing Operation"

            If IsUpdated Then

'INSTANT VB TODO TASK: Assignments within expressions are not supported in VB

'ORIGINAL LINE: var dsQrycutoToEdit = TryCast((from Cust in ProxyEntities.Customer where Cust.CustomerID == objCust.CustomerID select Cust), DataServiceQuery(Of MyRef.Customer));

                  Dim dsQrycutoToEdit = TryCast((

                      From Cust In ProxyEntities.Customer

                      Where Cust.CustomerID = objCust.CustomerID

                      Select Cust), DataServiceQuery(Of MyRef.Customer))

 

                  dsQrycutoToEdit.BeginExecute(onDataGridRowEditSelected, dsQrycutoToEdit)

 

                  Dim elementCustAddress As FrameworkElement = dgCustomer.Columns(2).GetCellContent(e.Row)

                  Dim txtAddress As String = (CType(elementCustAddress, TextBlock)).Text

                  objCust.Address = txtAddress

 

                  Dim elementCustCity As FrameworkElement = dgCustomer.Columns(3).GetCellContent(e.Row)

                  Dim txtCity As String = (CType(elementCustCity, TextBlock)).Text

                  objCust.Address = txtCity

 

                  Dim elementCustState As FrameworkElement = dgCustomer.Columns(4).GetCellContent(e.Row)

                  Dim txtState As String = (CType(elementCustState, TextBlock)).Text

                  objCust.State = txtState

 

                  Dim elementCustAge As FrameworkElement = dgCustomer.Columns(5).GetCellContent(e.Row)

                  Dim txtAge As String = (CType(elementCustAge, TextBlock)).Text

                  objCust.Age = Convert.ToInt32(txtAge)

            End If

'           #End Region

End Sub

The above code makes use of an Async method call marked in yellow, to perform ‘Update’ operation. The method is implemented as below:

C#

private void onDataGridRowEditSelected(IAsyncResult ar)

{

    try

    {

        var qry = ar.AsyncState as DataServiceQuery<MyRef.Customer>;

 

        var Result = qry.EndExecute(ar).ToList<MyRef.Customer>();

 

        objMyCust = Result.First<MyRef.Customer>();

 

        ProxyEntities.UpdateObject(objMyCust); 

 

        ProxyEntities.BeginSaveChanges(SaveChangesOptions.Batch, OnUpdateComplete, ProxyEntities);

 

        DisableAll();

    }

    catch (Exception ex)

    {

        string s = ex.Message;

 

    }

}

 

private void OnUpdateComplete(IAsyncResult ar)

{

    var dataToUpdate = ar.AsyncState as MyRef.CompanyEntities;

    ProxyEntities.EndSaveChanges(ar);

 

    if (IsUpdated)

    {

        MessageBox.Show("Record Updated Successfully");

        IsUpdated = false;

    }

    if (IsInserted)

    {

        MessageBox.Show("Record Inserted Successfully");

        IsInserted = false;

    }

}

 

VB.NET (Converted Code)

Private Sub onDataGridRowEditSelected(ByVal ar As IAsyncResult)

      Try

            Dim qry = TryCast(ar.AsyncState, DataServiceQuery(Of MyRef.Customer))

 

            Dim Result = qry.EndExecute(ar).ToList(Of MyRef.Customer)()

 

            objMyCust = Result.First(Of MyRef.Customer)()

 

            ProxyEntities.UpdateObject(objMyCust)

 

            ProxyEntities.BeginSaveChanges(SaveChangesOptions.Batch, AddressOf OnUpdateComplete, ProxyEntities)

 

            DisableAll()

      Catch ex As Exception

            Dim s As String = ex.Message

 

      End Try

End Sub

 

Private Sub OnUpdateComplete(ByVal ar As IAsyncResult)

      Dim dataToUpdate = TryCast(ar.AsyncState, MyRef.CompanyEntities)

      ProxyEntities.EndSaveChanges(ar)

 

      If IsUpdated Then

            MessageBox.Show("Record Updated Successfully")

            IsUpdated = False

      End If

      If IsInserted Then

            MessageBox.Show("Record Inserted Successfully")

            IsInserted = False

      End If

End Sub

The ‘OnDataGridRowEditSelected()’ method is an async method which uses DataServiceQuery<T> class to locate the record to be edited. Once the edit operation is completed, it needs to be updated back to the Database using WCF Data Service. To do this, the object ‘objMyCust’ needs to be passed to the ‘UpdateObject()’ method of the Service proxy ‘ProxyEntities’. To update the data, the service needs to be called asynchronously using ‘BeginSaveChanges()’ methods using the service proxy. The ‘OnUpdateComplete()’ method performs the Update operation.   

Step 12: Run the application, and click on the ‘Update’ button. Put the values which need to be changed.    

After clicking the ‘Update’ button, change the focus to the next row by using ‘TAB’ button, the following result will be displayed:

Note: The Selected row is shown selected. You can check the values in database.

Step 13: To perform the ‘Delete’ Operation, write the following code on ‘Delete’ button click event as below:

C#

private void btnDelete_Click(object sender, RoutedEventArgs e)

{

    IsDeleted = true;

    var Res = MessageBox.Show("Do you want to delete this record?","Warning",MessageBoxButton.OKCancel);

 

    if (Res == MessageBoxResult.OK)

    {

        ProxyEntities.DeleteObject(objCust);

        ProxyEntities.BeginSaveChanges(SaveChangesOptions.Batch, OnUpdateComplete, ProxyEntities);

        MessageBox.Show("Deleted");

        BindCustomerGrid();

    }

}

 

VB.NET (Converted Code)

 

Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)

      IsDeleted = True

      Dim Res = MessageBox.Show("Do you want to delete this record?","Warning",MessageBoxButton.OKCancel)

 

      If Res = MessageBoxResult.OK Then

            ProxyEntities.DeleteObject(objCust)

            ProxyEntities.BeginSaveChanges(SaveChangesOptions.Batch, OnUpdateComplete, ProxyEntities)

            MessageBox.Show("Deleted")

            BindCustomerGrid()

      End If

End Sub

The above code passes the object to be deleted ‘objCust’ to the ‘DeleteObject’ method of the service proxy ‘ProxyEntities’ object. Again, here we are using the async ‘BeginSaveChanges’ method of the proxy object which completes the operation using ‘OnUpdateComplete’ method, which is written above.

Step 14: Run the application.

Click the ‘Delete’ button on the row to be deleted. The following result will be displayed:

The marked row in the above picture is the row to be deleted. Click on the ‘OK’ button, the row will be removed from the DataGrid.

 

Performing Insert Operations in the Silverlight DataGrid

 

Step 1: Open ‘MainPage.Xaml’ and add the following Button element after the end of DataGrid tag:

</data:DataGrid.Columns>

</data:DataGrid>

<Button Content="Insert New Customer" Grid.Row="1" Height="23" HorizontalAlignment="Left" Margin="610,287,0,0" Name="btnInsertRow" VerticalAlignment="Top" Width="137" Click="btnInsertRow_Click" />

 

Step 2: Add the following Boolean variable at the class level in MainPage.Xaml.cs

C#

bool IsInserted = false;

 

VB.NET (Converted Code)

Dim IsInserted As Boolean = False

 

Step 3: Write the following code in the ‘Insert New Customer’ button.

C#

private void btnInsertRow_Click(object sender, RoutedEventArgs e)

{

    ResultCustomer.Add(new MyRef.Customer());

    dgCustomer.ItemsSource = ResultCustomer;

 

    dgCustomer.Columns[0].IsReadOnly = false;

    dgCustomer.Columns[1].IsReadOnly = false;

    dgCustomer.Columns[2].IsReadOnly = false;

    dgCustomer.Columns[3].IsReadOnly = false;

    dgCustomer.Columns[4].IsReadOnly = false;

    dgCustomer.Columns[5].IsReadOnly = false;

 

    IsInserted = true;

}

 

VB.NET (Converted Code)

Private Sub btnInsertRow_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)

      ResultCustomer.Add(New MyRef.Customer())

      dgCustomer.ItemsSource = ResultCustomer

 

      dgCustomer.Columns(0).IsReadOnly = False

      dgCustomer.Columns(1).IsReadOnly = False

      dgCustomer.Columns(2).IsReadOnly = False

      dgCustomer.Columns(3).IsReadOnly = False

      dgCustomer.Columns(4).IsReadOnly = False

      dgCustomer.Columns(5).IsReadOnly = False

 

      IsInserted = True

End Sub

 

The above code adds a new Customer record entry in the ‘ObservableCollection<MyReg.Customet>’ which is used as a source for the DataGrid and bind with the ‘ItemsSource’ property of the DataGrid. ObservableCollection<T> is used because it has the capability of the change notification, it will bind a blank row at the end of the DataGrid. Once the row is added all the cells are made as editable.

Step 4: Add some code in ‘RowEditEnded’ event of the DataGrid, just below the update row, as shown below:

C#

private void dgCustomer_RowEditEnded(object sender, DataGridRowEditEndedEventArgs e)

{

    //Code for Update is removed from here it is present in the above update Steps

 

    #region Insert new Row

 

    if (IsInserted)

    {

        FrameworkElement elementCustId = dgCustomer.Columns[0].GetCellContent(e.Row);

        string txtCustId = ((TextBlock)elementCustId).Text;

        objCust.CustomerID = Convert.ToUInt16(txtCustId);

 

        FrameworkElement elementCustName = dgCustomer.Columns[1].GetCellContent(e.Row);

        string txtCustName = ((TextBlock)elementCustName).Text;

        objCust.CustomerName = txtCustName;

 

 

        FrameworkElement elementCustAddress = dgCustomer.Columns[2].GetCellContent(e.Row);

        string txtAddress = ((TextBlock)elementCustAddress).Text;

        objCust.Address = txtAddress;

 

        FrameworkElement elementCustCity = dgCustomer.Columns[3].GetCellContent(e.Row);

        string txtCity = ((TextBlock)elementCustCity).Text;

        objCust.Address = txtCity;

 

        FrameworkElement elementCustState = dgCustomer.Columns[4].GetCellContent(e.Row);

        string txtState = ((TextBlock)elementCustState).Text;

        objCust.State = txtState;

 

        FrameworkElement elementCustAge = dgCustomer.Columns[5].GetCellContent(e.Row);

        string txtAge = ((TextBlock)elementCustAge).Text;

        objCust.Age = Convert.ToInt32(txtAge);

 

 

        ProxyEntities.AddToCustomer(objCust);

 

        ProxyEntities.BeginSaveChanges(SaveChangesOptions.Batch, OnUpdateComplete, ProxyEntities);

 

    }

 

    #endregion

 

}

 

VB.NET (Converted Code)

Private Sub dgCustomer_RowEditEnded(ByVal sender As Object, ByVal e As DataGridRowEditEndedEventArgs)

      'Code for Updateremoved from here it is present in the above update Steps

 

'     #Region "Insert new Row"

 

      If IsInserted Then

            Dim elementCustId As FrameworkElement = dgCustomer.Columns(0).GetCellContent(e.Row)

            Dim txtCustId As String = (CType(elementCustId, TextBlock)).Text

            objCust.CustomerID = Convert.ToUInt16(txtCustId)

 

            Dim elementCustName As FrameworkElement = dgCustomer.Columns(1).GetCellContent(e.Row)

            Dim txtCustName As String = (CType(elementCustName, TextBlock)).Text

            objCust.CustomerName = txtCustName

 

 

            Dim elementCustAddress As FrameworkElement = dgCustomer.Columns(2).GetCellContent(e.Row)

            Dim txtAddress As String = (CType(elementCustAddress, TextBlock)).Text

            objCust.Address = txtAddress

 

            Dim elementCustCity As FrameworkElement = dgCustomer.Columns(3).GetCellContent(e.Row)

            Dim txtCity As String = (CType(elementCustCity, TextBlock)).Text

            objCust.Address = txtCity

 

            Dim elementCustState As FrameworkElement = dgCustomer.Columns(4).GetCellContent(e.Row)

            Dim txtState As String = (CType(elementCustState, TextBlock)).Text

            objCust.State = txtState

 

            Dim elementCustAge As FrameworkElement = dgCustomer.Columns(5).GetCellContent(e.Row)

            Dim txtAge As String = (CType(elementCustAge, TextBlock)).Text

            objCust.Age = Convert.ToInt32(txtAge)

 

 

            ProxyEntities.AddToCustomer(objCust)

 

            ProxyEntities.BeginSaveChanges(SaveChangesOptions.Batch, OnUpdateComplete, ProxyEntities)

 

      End If

 

'     #End Region

 

End Sub

 

The above code reads all values entered in the in the new row added. Once it is done, a call is made to the ‘BeginSaveChanges’ method on the WCF Data Service object ‘ProxyEntities’. The Async operation is completed using ‘OnUpdateComplete’ method. Modify ‘OnUpdateComplete’ method as below:

C#

private void OnUpdateComplete(IAsyncResult ar)

{

    var dataToUpdate = ar.AsyncState as MyRef.CompanyEntities;

    ProxyEntities.EndSaveChanges(ar);

 

    if (IsUpdated)

    {

        // Code Is already put in the previous update operation

    }

    if (IsInserted)

    {

        MessageBox.Show("Record Inserted Successfully");

        IsInserted = false;

    }

}

 

VB.NET (Converted Code)

 

private void OnUpdateComplete(IAsyncResult ar)

{

    var dataToUpdate = ar.AsyncState as MyRef.CompanyEntities;

    ProxyEntities.EndSaveChanges(ar);

 

    if (IsUpdated)

    {

        // Code Is already put in the previous update operation

    }

    if (IsInserted)

    {

        MessageBox.Show("Record Inserted Successfully");

        IsInserted = false;

    }

}

 

 

Step 5: Run the application.

Click on the ‘Insert New Customer’ button. A new row will be added at the end of the DataGrid. Enter values in the cells. Use TAB key to navigate through all cells of the row, the following result will be displayed.

Click on the ‘OK’ button of the message box. The record will be inserted 

Conclusion: Silverlight DataGrid provides nice mechanism to perform DML operations as like ASP.NET GridView.