Wednesday, May 6, 2009

Silverlight 3 : Insert & Update Data using WCF Service with DataForm and DataGrid

In my Lap around Silverlight 3 series, I have written a separate article on DataForm in Silverlight 3, This article is a basic extension to that and I am soon pushing it par with DataPager and other few things in coming days.

Now, I am discussing a simple or rather the simplest way to push new data and update existing data. Offcourse selection of data is there by default, so delete operation I left for my blog readers. In a nutshell you can treat this as CRUD demo with DataPager. For this you need 3 Major things

  • DataForm
  • DataGrid
  • WCF Service

I will start with WCF service first.Kindly note that you need to have respective SQL database as backend. I believe “DataContract” of this will be self-explanatory, so I am not giving details of SQL schema, Also I have not much given stress on Validations and authetications since I cover that in last article and priority of this is to show basic CRUD operations.

WCF Service Code :

[ServiceContract]
   public interface IService
   {
       [OperationContract]
       bool InsertEmployee(Employee emp);
       [OperationContract]
       bool UpdateEmployee(Employee emp);
       [OperationContract]
       Employee[] GetAllEmployees();
   }

[DataContract]
   public class Employee
   {
       [DataMember]
       public int EmpNo { get; set; }
       [DataMember]
       public string EmpName { get; set; }
       [DataMember]
       public int DeptNo { get; set; }
       [DataMember]
       public int Salary { get; set; }
   }

Under Service.svc.cs you need to write following code :

public class Service : IService
   {

       SqlConnection Conn;
       SqlCommand Cmd;

       #region IService Members

       public bool UpdateEmployee(Employee emp)
       {
           bool Updated = false;
           Conn = new SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI");
           Cmd = new SqlCommand();
           Conn.Open();
           Cmd.Connection = Conn;
           Cmd.CommandText = "Update Employee Set EmpName=@EmpName,DeptNo=@DeptNo,Salary=@Salary where EmpNo=@EmpNo";

           Cmd.Parameters.AddWithValue("@EmpNo",emp.EmpNo);
           Cmd.Parameters.AddWithValue("@EmpName",emp.EmpName);
           Cmd.Parameters.AddWithValue("@DeptNo",emp.DeptNo);
           Cmd.Parameters.AddWithValue("@Salary",emp.Salary);

           int Upd = Cmd.ExecuteNonQuery();
           if (Upd > 0)
           {
               Updated = true;
           }

           Conn.Close();
           return Updated;

       }

Below will help to fetch the latest records :

       public Employee[] GetAllEmployees()
       {
           Conn = new SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI");
           Cmd = new SqlCommand();
           Conn.Open();
           Cmd.Connection = Conn;
           Cmd.CommandText = "Select * from Employee";

           SqlDataReader Reader = Cmd.ExecuteReader();

           DataTable dtEmp = new DataTable();
           dtEmp.Load(Reader);
           int RCount =  dtEmp.Rows.Count;
           Employee[] arrEmp = new Employee[RCount];
           int i = 0;

           foreach (DataRow Dr in dtEmp.Rows)
           {
               arrEmp[i] = new Employee();
               arrEmp[i].EmpNo = Convert.ToInt32(Dr["EmpNo"]);
               arrEmp[i].EmpName = Dr["EmpName"].ToString();
               arrEmp[i].DeptNo = Convert.ToInt32(Dr["DeptNo"]);
               arrEmp[i].Salary = Convert.ToInt32(Dr["Salary"]);
               i++;
           }

           Conn.Close();

           return arrEmp;

       }

       #endregion

Below will help to Insert New records from DataForm :

       #region IService Members

       public bool InsertEmployee(Employee emp)
       {
           bool Inserted = false;
           Conn = new SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI");
           Cmd = new SqlCommand();
           Conn.Open();
           Cmd.Connection = Conn;
           Cmd.CommandText = "Insert into  Employee Values (@EmpNo,@EmpName,@Salary,@DeptNo)";

           Cmd.Parameters.AddWithValue("@EmpNo", emp.EmpNo);
           Cmd.Parameters.AddWithValue("@EmpName", emp.EmpName);
           Cmd.Parameters.AddWithValue("@DeptNo", emp.DeptNo);
           Cmd.Parameters.AddWithValue("@Salary", emp.Salary);

           int ins = Cmd.ExecuteNonQuery();
           if (ins > 0)
           {
               Inserted = true;
           }

           Conn.Close();
           return Inserted;
       }

       #endregion
   }

Now we have our basic logic in place, So its time to go for design the UI, Before this, Let me give a URL of my fellow who is WCF Geek and he blogs at :

http://maheshsabnis.spaces.live.com/blog/

Though Mahesh blog in irregular intervals but you can buzz him if you have any queries in WCF.

Well,getting back to our demo, For design, I am putting DataForm and DataGrid Head to Head, So code will look like this :

XAML Code :

<Grid x:Name="LayoutRoot"   Width="690" Height="490" ShowGridLines="True" Background="Gainsboro">
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="345"></ColumnDefinition>
            <ColumnDefinition Width="345"></ColumnDefinition>
        </Grid.ColumnDefinitions>
        <dataControls:DataForm Height="200" Grid.Column="0" x:Name="dfEmp" ItemsSource="{Binding Emp}" ItemEditEnded="dfEmp_ItemEditEnded"
                               AddingItem="dfEmp_AddingItem"
                              ></dataControls:DataForm>
        <data:DataGrid Grid.Column="1" Height="200" x:Name="dgEmp"   Width="300" ItemsSource="{Binding Emp}"></data:DataGrid>
    </Grid>

C# Code :

Some global level declarations :

MyRef.ServiceClient Proxy;

private ObservableCollection<MyRef.Employee> _Emp;

bool IsNewInsertFlag = false;

public ObservableCollection<MyRef.Employee> Emp
{
        get { return _Emp; }
        set { _Emp = value; }
}

Initial Binding of Data :

public MainPage_DataForm_DataGrid()
       {
           InitializeComponent();
           Proxy = new SILV3_DataBoundControls.MyRef.ServiceClient();
       }

       private void UserControl_Loaded(object sender, RoutedEventArgs e)
       {
           Proxy.GetAllEmployeesCompleted += new EventHandler<SILV3_DataBoundControls.MyRef.GetAllEmployeesCompletedEventArgs>(Proxy_GetAllEmployeesCompleted);
           Proxy.GetAllEmployeesAsync();         
       }

Actual Operations :

void Proxy_GetAllEmployeesCompleted(object sender, SILV3_DataBoundControls.MyRef.GetAllEmployeesCompletedEventArgs e)
        {
            if (e.Result != null)
            {
                Emp = e.Result;
            }
            this.DataContext = this;          
        }

I was actually looking for some specific event to each CRUD operation, but finally I landed up with ItemEditEnded (Feedback most welcome if there any better way to do it here), So I did both operations in one event by using flag IsNewInsertFlag.

        private void dfEmp_ItemEditEnded(object sender, DataFormItemEditEndedEventArgs e)
        {
            if (IsNewInsertFlag)
            {
                Proxy.InsertEmployeeCompleted += new EventHandler<SILV3_DataBoundControls.MyRef.InsertEmployeeCompletedEventArgs>(Proxy_InsertEmployeeCompleted);
                Proxy.InsertEmployeeAsync((MyRef.Employee)dfEmp.CurrentItem);
            }
            else
            {
                Proxy.UpdateEmployeeCompleted += new EventHandler<MyRef.UpdateEmployeeCompletedEventArgs>(obj_UpdateEmployeeCompleted);
                Proxy.UpdateEmployeeAsync((MyRef.Employee)dfEmp.CurrentItem);
            }
        }

        void Proxy_InsertEmployeeCompleted(object sender, SILV3_DataBoundControls.MyRef.InsertEmployeeCompletedEventArgs e)
        {
            bool Res = e.Result;
        }

        void obj_UpdateEmployeeCompleted(object sender, MyRef.UpdateEmployeeCompletedEventArgs e)
        {
            bool Res = e.Result;
        }

        private void dfEmp_AddingItem(object sender, CancelEventArgs e)
        {
            IsNewInsertFlag = true;
        }

And you are done with it..Many of you might be wondering about few service calls, Let me explain, Thought we are using ObservableCollection it will surely update in Grid whatever we enter in DataForm, but since we need to push all updates to Database we are doing this service calls, I personally don’t think of any overhead here,if you feel please let me know.

So output will be look like this :

New

Screen1

I hope this will give you better idea and help you to implement in your POCs/Projects etc., do let me know your feedback.

Vikram.

17 comments:

sana said...

I can't seem to call Employee in the
public ObservableCollectionMyRef.Empolyee _Emp;

as if it's not passing the datacontract information

Phillip Knezevich said...

Good article, really insightful, well thought out.

Can you put up some sample code?

Phillip Knezevich said...

I've spent a lot of time on the web looking for some help with little luck. This is the first article that really drilled into things I need. This is great, well thought out and really insightful. Are you able to post the source code?

Vikram Pendse said...

Hello Phillip,

Thanks for your comments, I need to figure out what will be the good way to share code in some zip file, till that you can drop me a mail at :
vikram.pendse[AT]puneusergroup.org

I will send the project in zip file across to you, hope that will be fine with you.

surama said...

Hello,
This is a very good article which is solve my problem some extend.
I am not able to Update the records....
Can you help me on this.

Sorio said...

Hello Vikram, just what I needed to start off a user maintainance. I don't want to get through RIA Services yet, so WCF right now is great for me. Great article, but I got one problem; I can't get the datagrid and dataform to browse the same record like bindingsource did with the dataview and bindingnavigator in Windows Forms. Any idea?

Deendayal Sundaria said...

Hi Sir,

I have tried to develop this walkthrough but I am getting an error
at initializine of service :

proxy = new SilverlightClient.MyRef.Service1Client();

error as :

"The given key was not present in the dictionary."


Please tell me what solution can be as soon as possible.

Personal said...

This is a fantastic walk through and really pointed me in the rite direction BUT I don't know how old it is. Your dataform image has a pencil icon in edit mode, mine does not. And when I update from EditEnded the CurrentItem of the DataForm is no longer the one just edited but the one just moved onto.

Vikram Pendse said...

Thanks Personal, The code snippet for this article was made in Silverlight 3 Beta(Way back !).After wonderful response for this article,I am now planning to put this in Silverlight 4 Beta, Still if you are using Silverlight 3 RTW, with few minor changes, you can fix the issues which you have state,Breaking changes document will help you a lot in this.Hope this helps.

ashutosh.rashivadekar said...

How to get DataTable

ankit gandhi said...

hi sir,

great article, sir i want sample code so please tell me from where i can get it. or can u mail me akgandhi85@gmail.com



Ankit

ankit gandhi said...

hi sir,

good article, its really informatics.. i want some sample code so from where i can get ir or will you please send zip file on my mail id its akgandhi85@gmail.com


thanks,

Ankit

Edwin Cieza Mostacero said...

Por favor amigo, comparte tu codigo. Mi nombre es Edwin cieza y mi correo es ecieza.18@hotmail.com . Gracias!!

arian said...

good articles Mr. Vikram, but I'm confused with this code.
C# Code :

Some global level declarations :

MyRef.ServiceClient Proxy;

private ObservableCollection _Emp;

bool IsNewInsertFlag = false;
...
May I know your source code?
this is my email address varchar.arian@gmail.com

arian said...

good articles mr. vikram, but i still confused with this code
C# Code :

Some global level declarations :

MyRef.ServiceClient Proxy;

private ObservableCollection _Emp;

bool IsNewInsertFlag = false;

public ObservableCollection...

where should i put it?
may i know your source code?
this is my email damayanti.arian@gmail.com

Dhruvani said...

good articles sir, but I'm confused with this code.
C# Code :

Some global level declarations :

MyRef.ServiceClient Proxy;

private ObservableCollection _Emp;

bool IsNewInsertFlag = false;
...
May I know your source code?
this is my email address mistrydhruvani403@gmail.com

Dhruvani said...

good articles sir, but I'm confused with this code.
C# Code :

Some global level declarations :

MyRef.ServiceClient Proxy;

private ObservableCollection _Emp;

bool IsNewInsertFlag = false;
...
May I know your source code?
this is my email address mistrydhruvani403@gmail.com