.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> Silverlight >> Post New Resource Bookmark and Share   

 Subscribe to Articles

7 simple steps to connect SQL Server using WCF from SilverLight

Posted By:Shivprasad koirala       Posted Date: June 22, 2009    Points: 25    Category: Silverlight    URL: http://www.questpond.com  
 

7 simple steps to connect SQL Server using WCF from SilverLight


Introduction and Goal

Other Silverlight FAQ's

Silverlight does not have ADO.NET

Step 1:- Create the Service and Data service contract

Step 2:- Code the WCF service

Step 3:- Copy the CrossDomain.xml and ClientAccessPolicy.XML file

Step 4:- Change the WCF bindings to 'basicHttpBinding'

Step 5:- Add service reference

Step 6 :- Define the grid for Customer  Name and Customer Code

Step 7:- Bind the WCF service with the GRID

 

Introduction and Goal

 

In this article we will look how we can do database operations using SilverLight. We will first try to understand why we cannot call ADO.NET directly from a silverlight application and then we will browse through 7 steps which we need to follow to do database operation from silverlight.

I have collected around 400 FAQ questions and answers in WCF, WPF, WWF, SharePoint, design patterns, UML etc. Feel free to download these FAQ PDF's from my site http://www.questpond.com/  

 

Other Silverlight FAQ's

 

Silverlight FAQ Part 1:- http://www.dotnetspark.com/KB/WPF/WPFSilverLight.aspx  This tutorial has 21 basic FAQ's which will help you understand WPF , XAML , help your build your first silverlight application and also explains the overall silverlight architecture.

SilverLight FAQ Part 2 (Animations and Transformations):SilverLightFAQPart2.aspx This tutorial has 10 FAQ questions which starts with silverlight animation fundamentals and then shows a simple animated rectangle. The article then moves ahead and talks about 4 different ways of transforming the objects


Silverlight does not have ADO.NET


Below are the different ingredients which constitute SilverLight plugin.  One of the important points to be noted is that it does not consist of ADO.NET. In other words you can not directly call ADO.NET code from SilverLight application. Now the other point to be noted is that it has the WCF component. In other words you can call a WCF service.

 

 

In other words you can create a WCF service which does database operations and silverlight application will make calls to the same. One more important point to be noted is do not return ADO.NET objects like dataset etc because  SilverLight will not be able to understand the same.

 

 

Below are 7 important steps which we need to follow to consume a database WCF service in silverlight.

 

Step 1:- Create the Service and Data service contract


Below is a simple customer table which has 3 fields 'CustomerId' which is an identity column, 'CustomerCode' which holds the customer code and 'CustomerName' which has the name of the customer. We will fire a simple select query using WCF and then display the data on the SilverLight grid.

 

Field

Datatype

CustomerId

int

CustomerCode

nvarchar(50)

CustomerName

nvarchar(50)


As per the customer table specified above we need to first define the WCF data contract. Below is the customer WCF data contract.
 

[DataContract]
    public class clsCustomer
    {
        private string _strCustomer;
        private string _strCustomerCode;

        [DataMember]
        public string Customer
        {
            get { return _strCustomer; }
            set { _strCustomer = value; }
        }

        [DataMember]
        public string CustomerCode
        {
            get { return _strCustomerCode; }
            set { _strCustomerCode = value; }
        }
    }

We also need to define a WCF service contract which will be implemented by WCF concrete classes.

[ServiceContract]
    public interface IServiceCustomer
    {
        [OperationContract]
        clsCustomer getCustomer(int intCustomer);
    }

Step 2:- Code the WCF service


Now that we have defined the data contract and service contract it's time to implement the service contract. We have implemented the 'getCustomer' function which will return the 'clsCustomer' datacontract. 'getCustomer' function makes a simple ADO.NET connection and retrieves the customer information using the 'Select' SQL query.

 

public class ServiceCustomer : IServiceCustomer
    {
        public clsCustomer getCustomer(int intCustomer)
        {
            SqlConnection objConnection = new SqlConnection();
            DataSet ObjDataset = new DataSet();
            SqlDataAdapter objAdapater = new SqlDataAdapter();
            SqlCommand objCommand = new SqlCommand("Select * from Customer where CustomerId=" + intCustomer.ToString());
            objConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
            objConnection.Open();
            objCommand.Connection = objConnection;
            objAdapater.SelectCommand = objCommand;
            objAdapater.Fill(ObjDataset);
            clsCustomer objCustomer = new clsCustomer();
            objCustomer.CustomerCode = ObjDataset.Tables[0].Rows[0][0].ToString();
            objCustomer.Customer = ObjDataset.Tables[0].Rows[0][1].ToString();
            objConnection.Close();
            return objCustomer;
        }
    }

 

Step 3:- Copy the CrossDomain.xml and ClientAccessPolicy.XML file


This WCF service is going to be called from an outside domain, so we need to enable the cross domain policy in the WCF service by creating 'CrossDomain.xml' and 'ClientAccessPolicy.xml'. Below are both the code snippets. The first code snippet is for cross domain and the second for client access policy.

 


	
	
	  
	 

 

 

 


	
	  
	   
	      
	        
	      
	      
	        
	      
	   
	  
	


Step 4:- Change the WCF bindings to 'basicHttpBinding'


Silverlight consumes and generates proxy for only 'basicHttpBinding' , so we need to change the endpoint bindings accordingly.

 

 

Step 5:- Add service reference


We need to consume the service reference in silverlight application using 'Add service reference' menu. So right click the silverlight project and select add service reference.

Step 6 :- Define the grid for Customer  Name and Customer Code


Now on the silverlight side we will create a 'Grid' which has two columns one for 'CustomerCode' and the other for 'CustomerName'. We have also specified the bindings using 'Binding path' in the text block.

 


        
	            
	            
	        
        
	            
	            
	        
	        
	        
	        
	        
	

 

Step 7:- Bind the WCF service with the GRID


Now that our grid is created its time to bind the WCF service with the grid. So go to the behind code of the XAML file and create the WCF service object.  There are two important points to be noted when we call WCF service using from SilverLight:-

 

  • We need to call the WCF asynchronously, so we have called 'getCustomerAsynch'. Please note this function is created by WCF service to make asynchronous calls to the method / function.
     
  • Once the function completes its work on the WCF service it sends back the message to silverlight client.  So we need to have some kind of delegate method which can facilitate this communication. You can see we have created a 'getCustomerCompleted' method which captures the arguments and ties the results with the grid 'datacontext'.
     
public partial class Page : UserControl
		    {
		        public Page()
		        {
		            InitializeComponent();
		            ServiceCustomerClient obj = new ServiceCustomerClient();
	            obj.getCustomerCompleted += new EventHandler(DisplayResults);
		            obj.getCustomerAsync(1);
		        }
		        void DisplayResults(object sender, getCustomerCompletedEventArgs e)
		        {
		            LayoutRoot.DataContext = e.Result;
		            
		        }
	    }

 

You can now run the project and see how the silverlight client consumes and displays the data.

 

Source Code

 

You can download the source code from here.

 

 


 Subscribe to Articles

     

Further Readings:

Responses
Author: bindu         Company URL: http://www.dotnetspark.com
Posted Date: September 07, 2010

hi
i am getting a run time exception "Error: Unhandled Error in Silverlight Application The method or operation is not implemented. at MyDataBindSlApp.MainPage.obj_getCustCompleted(Object sender, getCustCompletedEventArgs e)
at MyDataBindSlApp.MyServiceSample.Service1Client.OngetCustCompleted(Object state)"

please help me...i am a beginner

regards,
bindu
Author: Adnan         Company URL: http://www.dotnetspark.com
Posted Date: November 11, 2010

Very useful article.

I've a question related to "silver light 4 business application template"
How can I validate user from my own sql db table?

I've my own SQL Database where I've one table "SUUser" which has "LoginId, Pw" columns, so can you please describe by using Silverlight Business Application template how can we validate user from sql?

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend