Click here to Skip to main content
Click here to Skip to main content

Integrate DataGridView with WCF service in a WinForms application using client side DataTable

By , 26 Mar 2013
Rate this:
Please Sign up or sign in to vote.

Introduction   

This article I would like to show how to get data from the MS-SQL relational data source by using .NET entity framework make available this for client through a WCF service and process it with a Windows Form desktop application. The main interest was to use DTOs (Data Transfer Object) to transfer the data to the client side and create a generic DataTable which utilizing the change tracking functionality implemented in the underlying .NET DataTable class. The client communicates with the server through CRUD (Create, Read, Update, Delete) function. 

The solution     

The solution contains two projects one for the server and one for the client side application. The target .NET Framework version is 3.5.

Database background 

In a server side I am using a MS-SQL database to store simple information of pet store. The database called PetStore. In this demo I am using only one following table: 

This table supposed to represent a simple inventory of a pet store. 

Server side logic

Service and data contracts    

I created a WCF service solution and I used the entity framework (OR/M) to create the object representation of my database structure (Pet table).

The following picture shows the generated Pet object from the edmx editor in Visual Studio 2008:   

Once we have the object representation of our database table we can easily execute queries on it by using LINQ. The next step is to write the server side logic.  

First of all our service will only contains one function. The contract for the service is the following:

[ServiceContract]
public interface IPetStoreService
{
    [OperationContract]
    [FaultContract(typeof(PetStore_ServiceFault))]
    //[TransactionFlow(TransactionFlowOption.Allowed)]
    List<PetDTO> GetPetsCRUD(PetDTO petDTO, CRUD operation, PetStore_Filter[] filterCriterias);
}

This function return with a list of a PetDTO object which is simple plain representation of our Pet table. In the server side logic we need to take care of the mapping. We going to do this automatically inside our CRUD server function: 


 

Our service is supposed to throw a fault in a case of something goes wrong.

/// <summary>
/// Simple class for service fault
/// </summary>
[DataContract]
public class PetStore_ServiceFault
{
    [DataMember]
    public string Message { get; set; }
}

This is a simple class that supposed to store message of the error that happened in the server side. Notice that the class is decorated with the DataContract and DataMember attributes because the instance of the class will be transferred to the client side. So it looks like any other data contract.   

If we take a look at our server side function it has 3 parameters and returning with a list of a PetDTO object. The PetDTO (Data Transfer Object) is representing the data from the Pet table. The PetDTO class definition is the following:

[DataContract]
public class PetDTO : DTOBase
{
    // DTO to store a pet information
   
    [DataMember]
    public NotNullableString Name { get; set; }

    [DataMember]
    public NotNullableString Type { get; set; }

    [DataMember]
    public int Age { get; set; }

    [DataMember]
    public NotNullableString Colour { get; set; }

}

This simple class also decorated with the DataContract and the DataMember attributes. The NotNullableString is a custom object designed to help determine - in the client side - if the property is allowed to hold null value. I have written a simple tip about this approach previously. See here: http://www.codeproject.com/Tips/499444/Using-not-nullable-string-wrapper-with-DTO

The second parameter is a CRUD enumeration that defines which operation we would like to perform on the server side. This is the following:

[DataContract]
public enum CRUD
{
    [EnumMember]
    Create,
    [EnumMember]
    Read,
    [EnumMember]
    Update,
    [EnumMember]
    Delete
}

The last parameter is filter object that can be used to perform a simple filter on the server side in a case of read.

[DataContract]
public class PetStore_Filter
{
    [DataMember]
    public string ColumnName { get; set; }           // Column name to be filtered

    [DataMember]
    public List<object> Values { get; set; }        // Values for the column name
} 

Let's summarize where we are. We have the service contract and the infrastructure to create the server side logic. The GetPetsCRUD function should be able to perform CRUD (Create, Read, Update, Delete) functions on the Pet table and do the mapping for a DTO back and forward. This DTO class will be sent to the client side.

The CRUD performer

The CRUD performer is a generic approach to execute CRUD operation and as we said do the mapping for the DTOs and back. This is just a simple solution and I am sure that there are better solutions out there.

The class definition is the following:

public class CRUDPerformer<DTO, EO, OC> where DTO : IDTO where EO : EntityObject where OC : ObjectContext

The public CRUD methods are the following:

public List<DTO> Read()
public void Add(DTO _DTO) 
public void Update(DTO _DTO)
public void Delete(DTO _DTO)

What we can see here that the CRUD performer is DTO specific and must be created for the given DTO. Since these functions are generic functions we have to consider this during the implementation.  

Add method: 

In this method we need to map the incoming DTO object and add to the current object context. Let's see how we can do:

public void Add(DTO _DTO)
{
    try
    {
        // Get the properties of the current DTO (fields)
        PropertyInfo[] DTOProperties = typeof(DTO).GetProperties();

        // Create a new EntityObject 
        EO newEO = (EO)Activator.CreateInstance(typeof(EO));

        // Check if the Entity has the DTO property
        object finalValue = null;

        // Loop through the DTO properties (fields)
        foreach (PropertyInfo info in DTOProperties)
        {
            finalValue = null;

            // Skip the relation
            if (info.PropertyType.Name.StartsWith("List") && info.PropertyType.IsGenericType) { continue; }
            
            // Do we have this field for the entity object
            if (newEO.GetType().GetProperty(info.Name) != null)
            {
                // Get the value from the DTO to be written into the database
                finalValue = info.GetValue(_DTO, null); 

                //Convert to NotNullableString into a string
                if (finalValue is NotNullableString) finalValue = ((NotNullableString)finalValue).Value;

                // Set propery of the new Entity object
                newEO.GetType().GetProperty(info.Name).SetValue(newEO, finalValue, null);
            }                
        }

        _addMethod(newEO);
    }
    catch (Exception ex)
    {
        throw new Exception("Exception occured while trying to update the server. CRUD create performer error: " +
             "DTO type: " + typeof(DTO).Name, ex);
    }
}
Delete Method

The delete method is simpler. Here we have to select the proper entity object and then perform the delete. The SelectEntityObject method selects the correct entity object based on the key values. Please check the source code for the details how this method works.

public void Delete(DTO _DTO)
{
    try
    {
        // Select the correct EntityObject to be deleted
        EO entityToBeDeleted = SelectEntityObject();

        // Check if we found the EntityObject. And try to delete
        if (entityToBeDeleted != null)
        {
            _ObjectContext.DeleteObject(entityToBeDeleted);
        }

    }
    catch (Exception ex)
    {
        throw new Exception("Exception occured while trying to update the server. CRUD delete performer error: " +
             "DTO type: " + typeof(DTO).Name, ex);
    }
}
Update Method

The update method selects the correct entity object and then update the corresponding fields. The  method is a modified version of the add method.

public void Update(DTO _DTO)
{
    try
    {
        // Get the properties of the DTO
        PropertyInfo[] DTOProperties = typeof(DTO).GetProperties();

        //Create a new entity object 
        EO eoToModify = SelectEntityObject();

        //Check if the Entity has the DTO property

        foreach (PropertyInfo info in DTOProperties)    // Loop through the properties of the DTO
        {
            object finalValue = null;

            //Skip the realtion now
            if (info.PropertyType.Name.StartsWith("List") && info.PropertyType.IsGenericType) { continue; }
            //We do not update the keys
            var keys = from i in eoToModify.EntityKey.EntityKeyValues where i.Key == info.Name select i;

            if (keys.Count() > 0) continue;

        // Do we have this field for the entity?
            if (eoToModify.GetType().GetProperty(info.Name) != null)
            {
                finalValue = info.GetValue(_DTO, null);

                if ((info.PropertyType.FullName == "System.String") && (finalValue == null)) finalValue = string.Empty;

                if (finalValue == null) continue;

                //Convert to string!
                if (finalValue is NotNullableString) finalValue = ((NotNullableString)finalValue).Value;

                eoToModify.GetType().GetProperty(info.Name).SetValue(
                                                                    eoToModify,
                                                                    finalValue,
                                                                    null
                                                                    );

            }
        
        }

    }
    catch (Exception ex)
    {
        throw new Exception("Exception occured while trying to update the server. CRUD update performer error: " +
             "DTO type: " + typeof(DTO).Name, ex);
    }
}
Read Method

The read method maps the entityObjects contained by the entityObjectQuery into the corresponding DTO. The method returns with the list of the DTOs. Let see how it looks like:

public static List<DTO> ReadDTO<DTO>(IEnumerable entityObjectQuery, bool Recursive, PetStore_Filter[] _filterCriterias)
{
    Type TargetDTOType = typeof(DTO); // The target DTO where we going to read in

    List<DTO> DTOlist = new List<DTO>();
    // Create a new instance of a list of DTOs. We will fill with information

    PropertyInfo[] DTOProperties = TargetDTOType.GetProperties();
    // Get the properties for the current DTO


    foreach (var entityObject in entityObjectQuery)
    // Loop through the object query. This is a list of entity objects
    {

        IDTO newDTO = (IDTO)Activator.CreateInstance(TargetDTOType);
        // Create a new instance of the DTO

        bool filterOut = false;

        //The driver is the DTO in terms of properties
        foreach (PropertyInfo DTOPropetyInfo in DTOProperties)
        {
            object value = null; // The valut that we will set for the DTO property

            Type EntityObjectType = entityObject.GetType();

            if (EntityObjectType.GetProperty(DTOPropetyInfo.Name) != null)
            //Can we found the property?
            {
                // Here check if it is a simple property or a relation. In the demo we
                // do not handle the relations
            }
            else
            {
                value = SearchForProperty(EntityObjectType.GetProperties(), DTOPropetyInfo, entityObject);
            }


            // Convert String to a Null string object
            if (DTOPropetyInfo.PropertyType == typeof(NotNullableString))
            {
                value = new NotNullableString() { Value = value.ToString() };

                if ((value == null) && (DTOPropetyInfo.PropertyType.Name.StartsWith("Nullable")))
                {
                    throw new Exception("Not nullable field has null value!! Field: " + DTOPropetyInfo.Name);
                }

            }

            if (DTOPropetyInfo.PropertyType == typeof(string) && value == null)
            {
                value = string.Empty;
            }

            //Set the value for the DTO
            if (value != null)
            {
                DTOPropetyInfo.SetValue(newDTO, value, null);

            }
            //
            /// Filter criteria
            /// 
            if (_filterCriterias != null)
            {
                if (_filterCriterias.Where(m => m.ColumnName == DTOPropetyInfo.Name).Count() > 0)
                {
                    var r = from i in _filterCriterias where i.ColumnName == DTOPropetyInfo.Name select i;
                    var q = r.Where(d => d.Values.Contains(value, new DTOValueComparer()));

                    if (q.Count() == 0) { filterOut = true; continue; }

                }
            }
        }

        if (!filterOut) DTOlist.Add((DTO)newDTO);
    }
    return DTOlist;
} 

I have to mention here that the current approach of this CRUD performer only works on the first level so the table relations will not be considered.  In the following code snippet I am going to show how to use CRUD performer for the Pet store entities. This is the implementation of the GetPetsCRUD operation contract.

public List<PetDTO> GetPetsCRUD(PetDTO pet, CRUD operation, PetStore_Filter[] filterCriterias)
{
    try
    {
        CRUDPerformer<PetDTO, Pet, PetStoreEntities> PetsCRUD = new CRUDPerformer<PetDTO, Pet, PetStoreEntities>
          (pet, PetStore_Entities.Pet, PetStore_Entities, PetStore_Entities.AddToPet);
      switch (operation)
      {
          case CRUD.Create:
              PetsCRUD.Add(pet);
              break;
          case CRUD.Read:
              return PetsCRUD.Read();
          case CRUD.Update:
              PetsCRUD.Update(pet);
              break;
          case CRUD.Delete:
              PetsCRUD.Delete(pet);
              break;
      }
      PetStore_Entities.SaveChanges();
      return null;
    }
    catch (Exception ex)
    {
        PetStore_ServiceFault asf = new PetStore_ServiceFault() { Message = GetInnerExcpetion(ex) };
        throw new FaultException<PetStore_ServiceFault>(asf);
    }
}

On the server side we successfully created the CRUD operation which works on our Pet table. In the next section I am going to show the client side logic. 

Client side logic 

The main idea was to create a generic DataTable that can be automatically created for a specific DTO.

public class DTO_DataTable<T> : System.Data.DataTable, ICommonTableMethod where T : 
              PetStoreClient.PetStoreProxy.DTOBase

As we can see the DTO_DataTable<T> can be created for a type that inherited from the DTObase class. Notice that our PetDTO is inherited from from the DTOBase class. Also the FTO_DataTable inherited from the DataTable .Net class and implements the ICommonTableMethod interface.

What this DTO_DataTable can do?  

  • This data table can automatically creates it's schema based on the DTO has been defined for its type.
  • Takes the benefit of the inherited getChanges method implemented for the DataTable
  • The DTO_DataTabe has a pointer to the server side CRUD function
  • Automatically executes the CRUD functions 

How to use this DTO_DataTable 

The simple client application is a WinForm application called PetStoreForm. We have two declaration inside of this form class:  

private DTO_DataTable<PetDTO> _petsDataTable;
private PetStoreServiceClient _client;

The initialization of the PetStoreForm is the following: 

_client = new PetStoreServiceClient();
_petsDataTable = new DTO_DataTable<PetDTO>(_client.GetPetsCRUD);
_petsDataTable.InitializeSchema();
petsDataGrid.DataSource = _petsDataTable;

We create an instance of PetStoreServiceClient proxy and _petsDataTable. Notice that the constructor requires a pointer to the server side CRUD function. This is our GetPetsCRUD function and we passed as a constructor parameter. The InitializeSchema method cretes the columns and set column's parameters (Name, Nullable or not for example) based to the DTO type which the DTO_DataTable has been created for.   

Read and update server data

I have put two buttons on the form to get and commit the data to the server. The following code snippet is the event handler for the get data button click event. This is basically updates our data table with the information from the server. Since our data table is a source of a DataGridView on the form we can immediately see the results.

private void gntGetData_Click(object sender, EventArgs e)
{
    _client = new PetStoreServiceClient();
    _client.Open();
    _petsDataTable.GetData();
    _petsDataTable.AcceptALCChanges();
    _client.Close();
    MessageBox.Show("Success!");
} 

petsDataGrid.DataSource = _petsDataTable;

The update button event handler is the following:

private void btnSaveData_Click(object sender, EventArgs e)
{
    _client = new PetStoreServiceClient();
    _client.Open();
    _petsDataTable.UpdateServer();
    _petsDataTable.AcceptALCChanges();
    _client.Close();
    MessageBox.Show("Data has been saved!");
}

Any feedback is welcome! Please let me know if you find something or you have  some question.

Points of Interest 

The code can be used as an example how to integrate client side DataTable to a WCF service.

History

2nd release  (2013-03-25 )

Server side logic details updated.  

 1st release 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

M_Tamas
Engineer
Hungary Hungary
No Biography provided

Comments and Discussions

 
GeneralMy vote of 3 PinmemberSagarJaybhay128-Feb-13 17:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 26 Mar 2013
Article Copyright 2013 by M_Tamas
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid