Silverlight CRUD Operations Using WCF Service

This articles demonstrates how to use Silverlight CRUD operations using a WCF service. We will use a Data Grid data control.

Create a new Silverlight project using .NET Framework 4.

Image1.jpg

Image 1.

You will see the project layout like this. The web project works like a server project with all the database related classes and services, .aspx test pages, configuration file and the other project has all the .xaml pages controls.

Image2.jpg

Image 2.

Now add a new Silverlight enabled WCF service.

Image3.jpg

Image 3.

In this sample I am using the Northwnd database available in the App_Data folder. First add a new class to the web project.

Customers.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace SilverlightCRUD_Using_WCFService.Web
{
public class Customers
    {
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }       
    }
}

Now add functions to the service class. This is the connection string.

string conn = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\NORTHWND.MDF;Integrated Security=True;User Instance=True";

 Add these namespaces to the service class.

using System.Data.SqlClient;
using System.Collections.Generic;

This function is used for the customer records from the database:

[OperationContract]
public List<Customers>GetAllCustomers()
{
List<Customers> customers = new List<Customers>();
using (SqlConnection con = new SqlConnection(conn))
{
using (SqlCommandcmd = new SqlCommand())
{
//cmd.CommandText = "GetAllCustomers";
cmd.CommandText = "Select CustomerID,CompanyName,ContactName,ContactTitle From Customers";
cmd.Connection = con;
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Customers customer = new Customers();
customer.CustomerID = Convert.ToString(reader["CustomerID"].ToString());
customer.CompanyName = Convert.ToString(reader["CompanyName"]);
customer.ContactName = Convert.ToString(reader["ContactName"]);
customer.ContactTitle = Convert.ToString(reader["ContactTitle"]);
customers.Add(customer);
}
}
}

return customers;
}

This method is used to update customer information.

[OperationContract]
public int UpdateCustomer(Customers customer)
{
using (SqlConnection con = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand())
{
string CommandText = "UPDATE Customers" +
"  SET(CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle)" +
" WHERECustomerID =" + customer.CustomerID;
cmd.CommandText = "Update Customers SET CompanyName=@CompanyName,ContactName=@ContactName,ContactTitle=@ContactTitle WHERE CustomerID = @CustomerID";
//cmd.CommandText = CommandText;
cmd.Connection = con;
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@CustomerID", System.Data.SqlDbType.VarChar).Value = customer.CustomerID;
cmd.Parameters.Add("@CompanyName", System.Data.SqlDbType.VarChar).Value = customer.CompanyName;
cmd.Parameters.Add("@ContactName", System.Data.SqlDbType.VarChar).Value = customer.ContactName;
cmd.Parameters.Add("@ContactTitle", System.Data.SqlDbType.VarChar).Value = customer.ContactTitle;
con.Open();
returnConvert.ToInt32(cmd.ExecuteScalar());
}
}

This method inserts a new customer entry in the database.

 [OperationContract]
public int InsertCustomer(Customers customer)
{
using (SqlConnection con = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "INSERT INTO Customers(CustomerID,CompanyName,ContactName,ContactTitle) Valu(@CustomerID,@CompanyName,@ContactName,@ContactTitle)";
cmd.Connection = con;
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
cmd.Parameters.Add("@CustomerID", System.Data.SqlDbType.VarChar).Value = customer.CustomerID;
cmd.Parameters.Add("@CompanyName", System.Data.SqlDbType.VarChar).Value = customer.CompanyName;
cmd.Parameters.Add("@ContactName", System.Data.SqlDbType.VarChar).Value = customer.ContactName;
cmd.Parameters.Add("@ContactTitle", System.Data.SqlDbType.VarChar).Value = customer.ContactTitle;
con.Open();

returnConvert.ToInt32(cmd.ExecuteScalar());
}
}

}

This method is used to delete customer details from the database.

[OperationContract]
public bool DeleteCustomer(string customerId)
{
using (SqlConnection con = new SqlConnection(conn))
{
using (SqlCommandcmd = new SqlCommand())
{
cmd.CommandText = "Delete FROM Customers Where CustomerID = @CustomerID";
cmd.Connection = con;
//cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add("@CustomerID", System.Data.SqlDbType.VarChar).Value = customerId;

con.Open();

returnConvert.ToBoolean(cmd.ExecuteNonQuery() > 0);
}
}
}


Here we have completed the service work. Now let's start working on the UI part. First of all add a service reference to the project.

Image4.jpg

Image 4.

Now discover the service reference and click OK.

Image5.jpg

Image 5.

Start working on the MainPage.xaml now.

<UserControl x:Class="SilverlightCRUD_Using_WCFService.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"
xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"
mc:Ignorable="d"
d:DesignHeight="500" d:DesignWidth="450"
 xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk">

<Grid x:Name="LayoutRoot" Background="White">
<
data:DataGridAutoGenerateColumns="False"HorizontalAlignment="Left"
 Margin="5,5,0,0"
 Name="customerGrid"VerticalAlignment="Top"
SelectionMode="Single"AllowDrop="True"LoadingRow="customerGrid_LoadingRow"></data:DataGrid>
<
sdk:Label Height="20"HorizontalAlignment="Left" Margin="22,350,0,0" Name="label1"VerticalAlignment="Top" Width="106" Content="Company Name: " />
<
TextBox Height="23"HorizontalAlignment="Left" Margin="134,347,0,0" Name="CompanyNametextBox"VerticalAlignment="Top" Width="160" />
<
sdk:Label Height="19"HorizontalAlignment="Left" Margin="22,375,0,0" Name="label2"VerticalAlignment="Top" Width="93" Content="Contact Name:" />
<
TextBox Height="23"HorizontalAlignment="Left" Margin="133,376,0,0" Name="ContactNametextBox"VerticalAlignment="Top" Width="161" />
<
sdk:Label Height="24"HorizontalAlignment="Left" Margin="22,403,0,0" Name="label3"VerticalAlignment="Top" Width="106" Content="Contact Title:" />
<
TextBox Height="23"HorizontalAlignment="Left" Margin="133,404,0,0" Name="ContactTitletextBox"VerticalAlignment="Top" Width="161" />
<
Button Content="Update" Height="23"HorizontalAlignment="Left" Margin="210,441,0,0" Name="Updatebutton"VerticalAlignment="Top" Width="75" Click="UpdateButton_Click" />
<
Button Content="Delete" Height="23"HorizontalAlignment="Left" Margin="288,441,0,0" Name="Deletebutton"VerticalAlignment="Top" Width="75" Click="Deletebutton_Click" />
<
Button Content="Insert" Height="23"HorizontalAlignment="Left" Margin="132,441,0,0" Name="Insertbutton"VerticalAlignment="Top" Width="75" Click="Insertbutton_Click" />
<
sdk:Label Height="18"HorizontalAlignment="Left" Margin="22,322,0,0" Name="label4"VerticalAlignment="Top" Width="106" Content="Customer ID:" />
<
TextBox Height="23"HorizontalAlignment="Left" Margin="134,317,0,0" Name="CustomerIDtextBox"VerticalAlignment="Top" Width="161"IsEnabled="True" />
<
sdk:DataPager Height="26"HorizontalAlignment="Left" Source="{Binding Path=ItemsSource,ElementName=customerGrid}" Margin="226,271,0,0" Name="dataPager1"PageSize="10"VerticalAlignment="Top" Width="200" />

</Grid>
</
UserControl>

MainPage.xaml.cs.

Add the service namespace.

using SilverlightCRUD_Using_WCFService.ServiceReference1;

This code and events are used to show customer details.

public MainPage()
{
InitializeComponent();
customerGrid.Columns.Add(new DataGridTextColumn
{
Header = "CustomerID",
Binding = new Binding("CustomerID"),
});

customerGrid.Columns.Add(new DataGridTextColumn
{
Header = "CompanyName",
Binding = newBinding("CompanyName"),
Width = newDataGridLength(140)

});

customerGrid.Columns.Add(new DataGridTextColumn
{
Header = "ContactName",
Binding = new Binding("ContactName"),
Width = new DataGridLength(100)
});

customerGrid.Columns.Add(new DataGridTextColumn
{
Header = "ContactTitle",
Binding = new Binding("ContactTitle"),
});

LoadCustomersGrid();

}
private void LoadCustomersGrid()
{
CustomerServiceClient customer = new CustomerServiceClient();
customer.GetAllCustomersCompleted += new EventHandler<GetAllCustomersCompletedEventArgs>(customer_GetAllCustomersCompleted);
customer.GetAllCustomersAsync();
}

void customer_GetAllCustomersCompleted(object sender, GetAllCustomersCompletedEventArgs e)
{
PagedCollectionView pageCollectionView = new PagedCollectionView(e.Result);
dataPager1.DataContext = pageCollectionView;
customerGrid.ItemsSource = pageCollectionView;
}

Now run the application.

Imag6.jpg
Image 6.

This piece of code is used to show row details in text boxes.

private void customerGrid_LoadingRow(object sender, DataGridRowEventArgs e)
{
e.Row.MouseLeftButtonUp += newMouseButtonEventHandler(Row_MouseLeftButtonUp);
}

void Row_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
Customers customer = customerGrid.SelectedItem as Customers;
CustomerIDtextBox.Text = customer.CustomerID;
ContactNametextBox.Text = customer.ContactName;
ContactTitletextBox.Text = customer.ContactTitle;
CompanyNametextBox.Text = customer.CompanyName;
}


Image7.jpg

Image 7.

This code and event is used to update the selected record.

private void UpdateButton_Click(object sender, RoutedEventArgs e)
{
if (Validate())
{
CustomerServiceClient customerServiceClient = new CustomerServiceClient();
customerServiceClient.UpdateCustomerCompleted += newEventHandler<UpdateCustomerCompletedEventArgs>(customerServiceClient_UpdateCustomerCompleted);
Customers customer = new Customers();
customer.CustomerID = CustomerIDtextBox.Text;
customer.CompanyName = CompanyNametextBox.Text;
customer.ContactName = ContactNametextBox.Text;
customer.ContactTitle = ContactTitletextBox.Text;
customerServiceClient.UpdateCustomerAsync(customer);
}
}

void customerServiceClient_UpdateCustomerCompleted(object sender, UpdateCustomerCompletedEventArgs e)
{
if (e.Result> -1)
{
MessageBox.Show("Record Updated Successfully", "Update", MessageBoxButton.OK);
ClearTextBoxes();
LoadCustomersGrid();
}
}

private void ClearTextBoxes()
{
CustomerIDtextBox.Text = string.Empty;
CompanyNametextBox.Text = string.Empty;
ContactNametextBox.Text = string.Empty;
ContactTitletextBox.Text = string.Empty;
CustomerIDtextBox.Focus();
}

private bool Validate()
{
if (CustomerIDtextBox.Text.Trim().Length == 0)
{
MessageBox.Show("Name cannot be blank", "Error", MessageBoxButton.OK);
CustomerIDtextBox.Focus();
returnfalse;
}
elseif (CompanyNametextBox.Text.Trim().Length == 0)
{
MessageBox.Show("Name cannot be blank", "Error", MessageBoxButton.OK);
CompanyNametextBox.Focus();
returnfalse;
}
elseif (ContactNametextBox.Text.Trim().Length == 0)
{
MessageBox.Show("Phone No cannot be blank", "Error", MessageBoxButton.OK);
ContactNametextBox.Focus();
returnfalse;
}
elseif (ContactTitletextBox.Text.Trim().Length == 0)
{
MessageBox.Show("Phone No cannot be blank", "Error", MessageBoxButton.OK);
ContactTitletextBox.Focus();
returnfalse;
}
else
{
returntrue;
}
}


Image8.jpg

Image 8.

Note: No need to update the Customer ID because it is the primary key.

And click Update.

Image9.jpg

Image 9.

This code and event is used to insert a new record entry.

private void Insertbutton_Click(object sender, RoutedEventArgs e)
{
if (Validate())
{
CustomerServiceClient customerServiceClient = new CustomerServiceClient();
customerServiceClient.InsertCustomerCompleted += newEventHandler<InsertCustomerCompletedEventArgs>(customerServiceClient_InsertCustomerCompleted);
Customers customer = new Customers();
customer.CustomerID = CustomerIDtextBox.Text;
customer.CompanyName = CompanyNametextBox.Text;
customer.ContactName = ContactNametextBox.Text;
customer.ContactTitle = ContactTitletextBox.Text;
customerServiceClient.InsertCustomerAsync(customer);
}
}

void customerServiceClient_InsertCustomerCompleted(object sender, InsertCustomerCompletedEventArgs e)
{
if (e.Result> -1)
{
MessageBox.Show("Record Inserted Successfully", "Insert", MessageBoxButton.OK);
ClearTextBoxes();
LoadCustomersGrid();
}
}


Now run the application and put a new entry in text boxes.

Image10.jpg

Image 10.

Click the Insert button now.

Image11.jpg

Image 11.

This code and event is used to delete the selected row entry.

private void Deletebutton_Click(object sender, RoutedEventArgs e)
{
if (CustomerIDtextBox.Text == "")
{
MessageBox.Show("Select a record to delete", "Delete", MessageBoxButton.OK);
}
else
{
if (MessageBox.Show("Are you sure you want to delete ? ", "Delete", MessageBoxButton.OKCancel) == MessageBoxResult.OK)
{

CustomerServiceClient customerServiceClient = new CustomerServiceClient();
customerServiceClient.DeleteCustomerCompleted += newEventHandler<DeleteCustomerCompletedEventArgs>(customer_DeleteCustomerCompleted);
customerServiceClient.DeleteCustomerAsync(CustomerIDtextBox.Text);
}
}
}

void customer_DeleteCustomerCompleted(object sender, DeleteCustomerCompletedEventArgs e)
{
if (e.Result)
{
MessageBox.Show("Record Deleted", "Delete", MessageBoxButton.OK);
ClearTextBoxes();
LoadCustomersGrid();
}
else
{
MessageBox.Show("Deletion failed", "Delete", MessageBoxButton.OK);
}
}


Image12.jpg
Image 12.

We are done here with Silverlight CRUD using a WCF service. If you have questions and comments related to this article then drop me a line in the C# corner comment section.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now