How to connect to Oracle database using WCF in Silverlight

Vijai Anand Ramalingam
Nov 29, 2010

39.4k
0
0
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

In this article we will be seeing how to connect to the Oracle database using WCF in Silverlight and will perform a search to retrieve the data from the database, display them in the Silverlight data grid using Visual Studio 2010. In the Oracle database we will be having a table Employee Details with three columns Employee_ID, FirstName and LastName, having more than 10,000 data. Based on the Employee_ID we can search for the employees using this Silverlight search page.

Steps Involved:

Creating a Silverlight Application:

I. Open Visual Studio 2010.

ii. Go to File => New => Project.

iii. Select Silverlight from the Installed templates and choose the Silverlight Application template.

iv. Enter the Name and choose the location.

v. Click OK.
vi. In the New Silverlight Application wizard check the "Host the Silverlight Application in a new Web site".

vii. Click OK.

Adding WCF Service:

I. Right click on the asp.net website (in my case SilverlightApplicationSearchWebpartForOracle.web) which is automatically added to the Silverlight solution when we have created the Silverlight Application (If you check the Host the Silverlight application in a new Web site check box in the New Silverlight Application dialog box, an ASP.NET Web site is created and added to the Silverlight solution), select Add a new item.

ii. Select Web from the Installed templates and choose the WCF Service.

iii. Enter the Name for the service.

iv. Click OK.

v. Add the reference System.Data.OracleClient.

vi. Open IService1.cs.

vii. Replace the code with the following.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace SilverlightApplicationSearchWebpartForOracle.Web
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1"
in both code and config file together.
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        List<Employee> getEmployees(string empId);
    }
    [DataContract]
    public class Employee
    {
        public int _employee_ID;
        public string _firstName;
        public string _lastName;

        [DataMember]
        public int Employee_ID
        {
            get { return _employee_ID; }
            set { _employee_ID = value; }
        }

        [DataMember]
        public string FirstName
        {
            get { return _firstName; }
            set { _firstName = value; }
        }
        [DataMember]
        public string LastName
        {
            get { return _lastName; }
            set { _lastName = value; }
        }
    }

}

viii. Open Service1.svc.cs.

ix. Replace the code with the following.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.Data.OracleClient;
using System.Text;

namespace SilverlightApplicationSearchWebpartForOracle.Web
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    public class Service1 : IService1
    {
        public List<Employee> getEmployees(string empId)
        {

            List<Employee> employees = new List<Employee>();
            string connectionString = "Data Source=orcl;Persist Security Info=True;" +
                   "User ID=system;Password=password-1;Unicode=True";
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();
                OracleCommand command = connection.CreateCommand();
                string sql;

                if (!string.IsNullOrEmpty(empId))
                {
                    sql = "SELECT * FROM employeedetails where employee_id like '%" + empId + "%'";

                }
                else
                {
                    sql = "SELECT * FROM employeedetails";
                }

                command.CommandText = sql;
                OracleDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Employee employee = new Employee();
                    employee.Employee_ID = Convert.ToInt32(reader["Employee_ID"]);
                    employee.FirstName = Convert.ToString(reader["FirstName"]);
                   employee.LastName = Convert.ToString(reader["LastName"]);
                    employees.Add(employee);
                }
                return employees.ToList();
            }
        }
    }
}

Adding clientaccesspolicy and crossdomain.xml files:

I. 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.

ii. Add the following two files to the SilverlightApplicationSearchWebpartForOracle.web.

iii. ClientAccessPolicy.xml

<?xml version="1.0" encoding="utf-8" ?>
       <access-policy>
       <cross-domain-access>
          <policy>
             <allow-from http-request-headers="*">
               <domain uri="*"/>
             </allow-from>
             <grant-to>
               <resource include-subpaths="true" path="/"/>
             </grant-to>
          </policy>
       </cross-domain-access>
       </access-policy>

iv. CrossDomain.xml

<?xml version="1.0"?>
       <!DOCTYPE cross-domain-policy SYSTEM "http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd">
       <cross-domain-policy>
       <allow-http-request-headers-from domain="*" headers="*"/>
       </cross-domain-policy>

Creating the UI for the Search Page:

v. Open MainPage.xaml file and replace the code with the below one.

<UserControl xmlns:my="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="SilverlightApplicationSearchWebpartForOracle.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="300" d:DesignWidth="500">

        <Canvas Grid.Row="1" Grid.Column="1" >
            <TextBlock Canvas.Left="7" Canvas.Top="20" Text="Find " Height="25" Width="30" FontWeight="Bold" Foreground="Black"></TextBlock>

            <TextBox x:Name="EmpName" Width="250" Height="25" Canvas.Left="35" Canvas.Top="15" Margin="2,0,0,4"

VerticalAlignment="Bottom"/>
<Button x:Name="Search" Height="25" Width="25" Canvas.Top="15" Canvas.Left="280" Background="Transparent">

            </Button>
            <my:DataGrid x:Name="theDataGrid" AlternatingRowBackground="Beige" Canvas.Left="7" Canvas.Top="50"
AutoGenerateColumns="True" Width="380" Height="300" Grid.Row="2"
Grid.Column="1" CanUserResizeColumns="True" />
            <my:DataPager Canvas.Left="50" Canvas.Top="370" Source="{Binding Path=ItemsSource, ElementName=theDataGrid}" PageSize="100" Margin="166,0,50,0"></my:DataPager>
        </Canvas>

</Grid>

</UserControl>

VerticalAlignment="Bottom"/>
<Button x:Name="Search" Height="25" Width="25" Canvas.Top="15" Canvas.Left="280" Background="Transparent">

<Image Source="/SilverlightApplicationSearchWebpartForOracle;component/Images/search32x32.png" ></Image>

</Grid>

</UserControl>

Adding Service Reference:

I. Right click on the Silverlight solution SilverlightApplicationSearchWebpartForOracle and click Add Service Reference.

ii. Add Service Reference wizard will popup.

iii. Click on Discover.

iv. You will be able to see the service that we have created.

v. Select the service and click OK.

vi. Open MainPage.xaml.cs and replace the code with the below one.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using SilverlightApplicationSearchWebpartForOracle.ServiceReference1;
using System.Windows.Data;

namespace SilverlightApplicationSearchWebpartForOracle
{
    public partial class MainPage : UserControl
    {
        public MainPage()
        {
            InitializeComponent();
            Loaded += new RoutedEventHandler(MainPage_Loaded);
        }
        void MainPage_Loaded(object sender, RoutedEventArgs e)
        {
            Search.Click += new RoutedEventHandler(Search_Click);
        }
        void Search_Click(object sender, RoutedEventArgs e)
        {
            ServiceReference1.Service1Client webservice = new SilverlightApplicationSearchWebpartForOracle.ServiceReference1.Service1Client();
            webservice.getEmployeesCompleted += new EventHandler<ServiceReference1.getEmployeesCompletedEventArgs>(webservice_getEmployeesCompleted);
            webservice.getEmployeesAsync(EmpName.Text);
        }

        void webservice_getEmployeesCompleted(object sender, ServiceReference1.getEmployeesCompletedEventArgs e)
        {
            PagedCollectionView pcv = new PagedCollectionView(e.Result);
            theDataGrid.ItemsSource = pcv;
        }
    }
}

Testing the solution:

I. Build the solution.

ii. Hit F5.

iii. Silverlight search page will be opened.

iv. Enter some number in the text box and click search, it will retrieve the values from the Oracle database that contains the text that we have entered in the text box and display them in the grid.

Thus we have connected to the Oracle database using WCF service and performed a search on the Oracle data. The search results are displayed in the Silverlight data grid.

Recommended Free Ebook

Printing in C# Made Easy

Download Now!