Monday, January 30, 2012

How to call WCF services methods from Silverlight Application

How to call WCF services methods from Silverlight Application

Here I am giving very basic idea for beginner who wants to know how we can call WCF service from Silverlight application. Calling WCF service from Silverlight uses different types of binding (basicHttpBinding), if you use RIA service then you need not to think binding configuration, it will take care automatically.

<client>
   <endpoint address="http://localhost:13513/Service1.svc" binding="basicHttpBinding"
    bindingConfiguration="BasicHttpBinding_IService1" contract="WcfService1.IService1"
    name="BasicHttpBinding_IService1" />
</client>
  
Follow the steps to create first “HelloWorld” solution  

1.     Create Simple WCF service project “WcfService1” in solution “HelloWorld.sln”
2.     Add “clientaccesspolicy.xml” and “crossdomain.xml” xml file into WCF project “WcfService1”
3.     Add one Silverlight project “SilverlightApplication1” into same solution “HelloWorld.sln”
4.     “SilverlightApplication1.Web”will get added automatically
5.     Set “SilverlightApplication1” project as startup project
6.     Now right click on “SilverlightApplication1” project and “Add service Reference” to “WcfService1”
7.     Now right click on “WcfService1” reference and click on Update Proxy
8.     Run the application

After creating project it will looks like as below screen shot

  
Source Code

C# Code in “IService1.cs”
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

namespace WcfService1
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        string GetData(int value);
    }

C# Code in “Service1.svc.cs” file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

namespace WcfService1
{
    public class Service1 : IService1
    {
        public string GetData(int value)
        {
            return string.Format("You entered: {0}", value);
        }
    }
 }

C# Code in “MainPage.XMAL”
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;

namespace SilverlightApplication1
{
    public partial class MainPage : UserControl
    {
        //Declare WCF proxy ref variable
        WcfService1.Service1Client webClient;

        public MainPage()
        {
            InitializeComponent();
            //Initialise WCF proxy variables
            webClient = new WcfService1.Service1Client();
            //Registred WCF complatedevent
            webClient.GetDataCompleted += new EventHandler<WcfService1.GetDataCompletedEventArgs>(ServiceCloud_GetDataCompleted);
        }

        //On button click event will call WCF service Async method to get data
        private void button1_Click(object sender, RoutedEventArgs e)
        {
            //Call WCF method
            webClient.GetDataAsync(30);
        }

        //once WCF service call completed then "e.Result" will give you return data from WCF call
        //you need to wait until WCF call complated and return value
        void ServiceCloud_GetDataCompleted(object sender, WcfService1.GetDataCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                //if WCF return data successfully then data will return into e.Result
                label1.Content = e.Result.ToString();
            }
            else
            {
                //other wise error message will be returned
                label1.Content = e.Error.Message.ToString();
            }

        }
    }
} 
Troubleshoots
  1. You might get error related to “Crossdomain” policy, if you don’t place “clientaccesspolicy.xml” and “crossdomain.xml” into “WcfService1” project, after changing anything/adding these .xml files you have to update the proxySee here to create these files: http://riteshkk2000.blogspot.com/2011/01/attempting-to-access-service-in-cross.html
  2. Internet explorer may ask to install Silverlight runtime even this already installed into your machine. you need to register local URL on the “Restricted Site”See more here to solve:    http://riteshkk2000.blogspot.com/2011/01/asking-to-install-silverlight-4-when.html
  3. If you need more and more details with pictorial view then good site to go through http://blogs.msdn.com/b/avkashchauhan/archive/2011/11/29/silverlight-front-end-calling-to-wcf-service-all-in-one-windows-azure-web-role-sample.aspx

Wednesday, January 11, 2012

Load/Import XML file into database table (Oracle or SQL Server)

Load/Import XML file into database table (Oracle or SQL Server)

Let say you have some XML file and you want to load xml data into database table, there are tons of xml structures and way to do the same.
I have taken very simple type of XML so that it should be easy to understand the basic of this functionality. I have seen lots of example given to take input parameter as xml string not xml as file. I am giving example to take input as xml file.
I also have given example that works with Oracle database as well in SQL server, because database could be any for this requirement.

I have tested following example with
  • Oracle Express Edition 10.2
  • SQL Server 2008
  • Window 7

  XML File Name: employee.xml

<ROWSET>
  <ROW ID="10">
    <EmpName>RiteshEmpName>
    <EmpSal>10000EmpSal>
    <DeptNo>1010DeptNo>
    <JoinDate>03-Apr-2006JoinDate>
  ROW>
  <ROW ID="20">
    <EmpName>ArtiEmpName>
    <EmpSal>20000EmpSal>
    <DeptNo>2020DeptNo>
    <JoinDate>05-May-2007JoinDate>
  ROW>
ROWSET>

Note: “ID” value just taken here to demo to read attribute value.

Oracle Solution

1)  Create Virtual directory from Oracle SQL Prompt

          SQL>  CREATE directory test_dir  AS 'c:\Test';

2) Copy “employee.xml” file into “c:\Test”

3) Create database Table name “EmployeeXML”


On the above table for Oracle I have created table with all columns data type as varchar2, I could not find correct syntax in a SQL query to convert datatype from String to int etc.
But anyway this is good to start, I will update later if I get a time.

3) Oracle SQL query to load data into EmplyeeXML Table

INSERT INTO EmployeeXML(EmpID,EmpName,EmpSal,DeptNo,JoinDate)
WITH t AS (SELECT xmltype(bfilename('TEST_DIR','employeexml.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual)
SELECT
extractValue(value(x),'/ROW/@ID') empid          ///* READ ATTRIBUTES */
,extractValue(value(x),'/ROW/EMPNAME') empname  ///*  READ VALUES */
,extractValue(value(x),'ROW/EMPSAL') empsal
,extractValue(value(x),'ROW/DEPTNO') deptno
,extractValue(value(x),'ROW/JOINDATE') joindate
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/ROWSET/ROW'))) x;

SQL Server Solution

In SQL server mostly you pass xml as string to stored procedure input parameter, but I was looking to pass XML file name as input parameter and below I am giving example using Bulk Insert.
In SQL server we need to go with 2 steps process, add data into temp table and then load data from temp table to emplyeexml table.

1)  Create Temp table and load XML into it
--Create Temp Table
CREATE TABLE #TempTable (Data XML)

2)  Create ‘EmployeeXML’ table and load XML data into it
--Check EmployeeXML table if already exist then TRUNCATE
IF NOT EXISTS (SELECT *
                 FROM   sys.objects
                 WHERE  object_id = OBJECT_ID(N'[dbo].[EmployeeXML]')
                        AND type in (N'U'))
    CREATE TABLE [dbo].[EmployeeXML] (
       [EmpId]         [int]   NOT NULL
      ,[EmpName]       [nvarchar](200)   NULL
      ,[EmpSal]        [int]   NULL
      ,[DeptNo]        [int]   NULL
      ,[JoinDate]    [datetime]   NULL
      )  
  ELSE
    TRUNCATE TABLE dbo.[EmployeeXML] 

3)  Insert XML into Temp table 
  /* Populate the temp table with the employee.xml file */
  INSERT INTO #TempTable
  SELECT *
  FROM   OPENROWSET(BULK 'c:\Ritesh\employee.xml',SINGLE_BLOB) AS data

4)  Insert XML into Temp table
  /* Import the users records from the working table */
  DECLARE  @XML    AS XML
           ,@hDoc  AS INT
 
  SELECT @XML = Data
  FROM   #TempTable
 
  EXEC sp_xml_preparedocument
    @hDoc OUTPUT ,
    @XML
 
   /* Insert data into employeexml table from temptable */
  INSERT INTO dbo.EmployeeXML
             ( EmpId
              ,EmpName
              , EmpSal
              ,DeptNo
              ,JoinDate)
  SELECT  Id
         ,EmpName
         ,EmpSal
         ,DeptNo
         ,CAST(JoinDate AS DATETIME)
  FROM   OPENXML (@hDoc, '/ROWSET/ROW', 2)  /* 2- READ VALUE (Hint below) */
               WITH ID            INT '@ID', /* READ ATTRIBUTE*/
                       EmpName        NVARCHAR(200) 'EmpName', /*READ VALUES */
                       EmpSal         INT 'EmpSale',
                       DeptNo         INT 'DeptNo',
                       JoinDate       VARCHAR(50) 'JoinDate') 

  /* Clean up and empty out temporary table */
  EXEC sp_xml_removedocument  @hDoc
 
4)  Drop temp table
  DROP TABLE #TempTable 

RESULT
Data will be inserted into database table

Hint:
Open the XML Document and Insert into the Database, Here the OpenXML takes 3 parameters
  1. the Handle we created for the XML document in memory,
  2. the XPath to access the various elements of the XML document and
  3. a flag here 2 means access the XPath as elements
Troubleshooting

You might get below error when you run from client; I solved this error putting my “employee.xml” file into server “C:\Ritesh\” folder.
Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "C:\Ritesh\employee.xml" could not be opened. Operating system error code 3(The system cannot find the path specified.).

You can go to following site to see the cause and solution for this error.

More Examples

You want to do the same load from .NET application into Oracle/SQL Server see following link
Oracle

SQL Server
LOAD CSV FILE
you can also load CSV comma separator file into database see below link 

Good links to see more on this