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.