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 :

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.