How to connect to Oracle database using WCF in Silverlight

0 comments
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. 



WcfSil1.gif



v. Click OK.

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



WcfSil2.gif



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. 



WcfSil3.gif



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



iii. Enter the Name for the service. 

WcfSil4.gif



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"

    mc:Ignorable="d"
    d:DesignHeight="300" d:DesignWidth="500">

    <Grid x:Name="LayoutRoot"  Background="Beige" ShowGridLines="False">

        <Grid.RowDefinitions>

            <RowDefinition Height="25" />
            <!--0 Margin-->
            <RowDefinition Height="50"  />
            <!--1 Prompts-->
            <RowDefinition Height="*" />
            <!--2 DataGrid-->
            <RowDefinition Height="10" />
            <!--3 Margin-->
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="20" />
            <!--0 Margin-->
            <ColumnDefinition Width="*" />
            <!--1 Controls-->
            <ColumnDefinition Width="20" />
            <!--2 Margin-->
        </Grid.ColumnDefinitions>
        <Canvas Grid.Column="0"  Grid.Row="0" Background="White"/>
        <Canvas Grid.Column="2"  Grid.Row="0" Background="White" />
        <Canvas Grid.Column="1"  Grid.Row="0" Background="White">
            <Image Canvas.Left="10" Canvas.Top="5" Height="20" Width="20"Source="/SilverlightApplicationSearchWebpartForOracle;component/Images/peopletitle.png"></Image>
            <TextBlock Canvas.Left="35" Canvas.Top="5" Text="Search for employee from Oracle Database"></TextBlock>
        </Canvas>

        <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">

                <Image Source="/SilverlightApplicationSearchWebpartForOracle;component/Images/search32x32.png"  ></Image>
            </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>
<UserControl xmlns:my="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="SilverlightApplicationSearchWebpartForOracle.MainPage"


    mc:Ignorable="d"
    d:DesignHeight="300" d:DesignWidth="500">

    <Grid x:Name="LayoutRoot"  Background="Beige" ShowGridLines="False">

        <Grid.RowDefinitions>

            <RowDefinition Height="25" />
            <!--0 Margin-->
            <RowDefinition Height="50"  />
            <!--1 Prompts-->
            <RowDefinition Height="*" />
            <!--2 DataGrid-->
            <RowDefinition Height="10" />
            <!--3 Margin-->
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="20" />
            <!--0 Margin-->
            <ColumnDefinition Width="*" />
            <!--1 Controls-->
            <ColumnDefinition Width="20" />
            <!--2 Margin-->
        </Grid.ColumnDefinitions>
        <Canvas Grid.Column="0"  Grid.Row="0" Background="White"/>
        <Canvas Grid.Column="2"  Grid.Row="0" Background="White" />
        <Canvas Grid.Column="1"  Grid.Row="0" Background="White">
            <Image Canvas.Left="10" Canvas.Top="5" Height="20" Width="20"Source="/SilverlightApplicationSearchWebpartForOracle;component/Images/peopletitle.png"></Image>
            <TextBlock Canvas.Left="35" Canvas.Top="5" Text="Search for employee from Oracle Database"></TextBlock>
        </Canvas>

        <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">

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

            </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>



0 comments: