Silverlight CRUD Operations Using WCF Service

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

Create a new Silverlight project using .NET framework 4.



Image 1.

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



Image2.

Now add a new Silverlight enabled wcf service.



Image3.

In this sample I am using NORTHWND database which is available in App_Data folder. First add a new class in 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 in service class. This is connection string.

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

 Add these namespaces in service class.

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

This function is used to customer records from 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 is use to insert new customer entry in 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 detail from 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 done with service work. Now let's start work on UI part. First of all add service reference in project.



Image4.

Now discover the service reference and click OK.



Image5.

Start work on 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 service namespace.

using SilverlightCRUD_Using_WCFService.ServiceReference1;

This code and events are used to show customer detail.

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.


Image6.

This piece of code is used to show row detail 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.

This code and event is used to update 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.

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

And click Update.



Image9.

This code and event is used to insert 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.

Click Insert button now.



Image11.

This code and event is used to delete 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.

We have done here with Silverlight CRUD using WCF service. If you have question and comment related to this article then drop me a line in c-sharp corner comment section.