Tuesday, March 8, 2011

Post 1 - How to: Speedup the heavy data loading from WCF service to datagridview in c#

It is a serious challenge to load large amount of data to the data grid in case of client server applications. To speed up the data loading we forced to do something other than just asking for the server to get all thousands of records. The normal approach is paging, i.e. the data grid will show only the first 10 or 20 records and load rest of the pages as an when user click next or select specific pages. In this method the client will make call to the server when user click next or select specific page. Ok fine, instead of asking for the user to click and fetch the data what if the application load the data automatically in the back ground after showing the first 10 or 20 records so that we can use the idle time.

Following is the architecture I am looking for:

  • Client will send a request for large data from the WCF service
  • The server will get the request and start preparing the data.
  • Server will serialize the data and send the reply to the client as stream.
  • The client starts getting the data continuously and push it to the Datatable
  • Update the DataGrid when ever the DataTable get updated with the data from the server (This will be a continuous process till all records have been send to the client)

Ok lets make a proof of concept…

Here I am using a database Customer, following is the SQL Script for my database including data.

   1: USE [Customer]
   2: GO
   3: /****** Object:  Table [dbo].[Customer]    Script Date: 03/03/2011 10:30:21 ******/
   4: SET ANSI_NULLS ON
   5: GO
   6: SET QUOTED_IDENTIFIER ON
   7: GO
   8: SET ANSI_PADDING ON
   9: GO
  10: CREATE TABLE [dbo].[Customer](
  11:     [ID] [int] IDENTITY(1,1) NOT NULL,
  12:     [FirstName] [varchar](50) NULL,
  13:     [LastName] [varchar](50) NULL,
  14:     [Address] [varchar](50) NULL,
  15:     [City] [varchar](50) NULL,
  16:     [Pin] [varchar](50) NULL,
  17:     [Phone] [varchar](50) NULL,
  18:  CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
  19: (
  20:     [ID] ASC
  21: )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  22: ) ON [PRIMARY]
  23: GO
  24: SET ANSI_PADDING OFF
  25: GO
  26: SET IDENTITY_INSERT [dbo].[Customer] ON
  27: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (1, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  28: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (2, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  29: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (3, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  30: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (4, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  31: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (5, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  32: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (6, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  33: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (7, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  34: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (8, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  35: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (9, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  36: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (10, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  37: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (11, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  38: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (12, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  39: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (13, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  40: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (14, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  41: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (15, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  42: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (16, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  43: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (17, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  44: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (18, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  45: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (19, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  46: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (20, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  47: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (21, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  48: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (22, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  49: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (23, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  50: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (24, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  51: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (25, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  52: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (26, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  53: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (27, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  54: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (28, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  55: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (29, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  56: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (30, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  57: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (31, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  58: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (32, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  59: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (33, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  60: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (34, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  61: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (35, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  62: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (36, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  63: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (37, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  64: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (38, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  65: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (39, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  66: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (40, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  67: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (41, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  68: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (42, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  69: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (43, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  70: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (44, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  71: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (45, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  72: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (46, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  73: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (47, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  74: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (48, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  75: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (49, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  76: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (50, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  77: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (51, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  78: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (52, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  79: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (53, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  80: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (54, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  81: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (55, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  82: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (56, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  83: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (57, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  84: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (58, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  85: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (59, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  86: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (60, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  87: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (61, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  88: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (62, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  89: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (63, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  90: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (64, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  91: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (65, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  92: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (66, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  93: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (67, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  94: INSERT [dbo].[Customer] ([ID], [FirstName], [LastName], [Address], [City], [Pin], [Phone]) VALUES (68, N'Anilal', N'Sambasivan', N'My House', N'Bangalore', N'My pin code', N'My phone number')
  95: SET IDENTITY_INSERT [dbo].[Customer] OFF


Server side application:



Create a WCF Service Library project “StreamingServicePOC”.



Add a linq to sql class in WCF class library project and then connect to the Customer database from the server explorer and drag and drop the customer table to the designer view. now the Linq to sql Data contect class is ready.



I am going to write a service contract called “GetCustomers” which will return large number  of records from the database. Note that the return type is Message





   1: public Message GetCustomers()
   2:         {
   3:             Type t = typeof(CustomerDataLayer);
   4:             Message message = Message.CreateMessage(MessageVersion.Soap11, "GetCustomers", new GenericBodyWriter<Customer>(StreamObject.SendObjectStream<Customer>(t, "GetCustomers")));
   5:             return message;
   6:         }


Lets go little bit deep to the above code snippet, the CreateMessage will create messages to send to the client, here I am using a generic custom body writer to override the existing serialization used by the WCF library. Following is he code for Generic Body Writer class





   1: public class GenericBodyWriter<T> : BodyWriter
   2:     {
   3:         private IEnumerable<List<T>> serializableObjects;
   4:  
   5:         public GenericBodyWriter(IEnumerable<List<T>> serializableObjects)
   6:             : base(false) 
   7:         {
   8:             this.serializableObjects = serializableObjects;
   9:         }
  10:  
  11:         protected override void OnWriteBodyContents(System.Xml.XmlDictionaryWriter writer)
  12:         {
  13:  
  14:             XmlSerializer serializer = new XmlSerializer(typeof(List<T>));
  15:             string startElement = typeof(T).Name + "s";
  16:             writer.WriteStartElement(startElement);
  17:  
  18:             foreach (List<T> serializableObject in serializableObjects)
  19:             {
  20:                 serializer.Serialize(writer, serializableObject);
  21:             }
  22:  
  23:             writer.WriteEndElement();
  24:         }
  25:     }


To send the objects as separate chunk to the client we need to override the default serialization used by WCF. Following is my custom serialization code.





   1: public class GenericBodyWriter<T> : BodyWriter
   2:     {
   3:         private IEnumerable<List<T>> serializableObjects;
   4:  
   5:         public GenericBodyWriter(IEnumerable<List<T>> serializableObjects)
   6:             : base(false)
   7:         {
   8:             this.serializableObjects = serializableObjects;
   9:         }
  10:  
  11:         protected override void OnWriteBodyContents(System.Xml.XmlDictionaryWriter writer)
  12:         {
  13:             XmlSerializer serializer = new XmlSerializer(typeof(List<T>));
  14:             string startElement = typeof(T).Name;
  15:             writer.WriteStartElement(startElement);
  16:  
  17:             foreach (List<T> serializableObject in serializableObjects)
  18:             {
  19:                 serializer.Serialize(writer, serializableObject);
  20:             }
  21:  
  22:             writer.WriteEndElement();
  23:         }
  24:     }








To return large number of records in separate group of chunks we will make the return type of  IEnumerable and yeild return the list of 10 or 20 records and the generic custom body writer will serialize the data and send to the client for each of 10 or 20 records.



In Client side application we will make the proxy and make call to the service function as follows;





   1: System.ServiceModel.Channels.Message message = testStreamingServiceProxy.GetCustomers();
   2:  
   3: foreach (Customer customer in GetAllCustomers(message))
   4:             {
   5:                 Customers.Add(customer);
   6:             }








Following is the function for reading the continuous reply for the service application





   1: static IEnumerable<Customer> GetAllCustomers(System.ServiceModel.Channels.Message message)
   2:         {
   3:             XmlReader reader = message.GetReaderAtBodyContents();
   4:             
   5:             XmlSerializer serializer = new XmlSerializer(typeof(Customer));
   6:             reader.ReadStartElement("customers");
   7:  
   8:             while (!reader.EOF && reader.LocalName == "Customer")
   9:             {
  10:                 Customer customer = (Customer)serializer.Deserialize(reader);
  11:                 yield return customer;
  12:             }
  13:  
  14:             reader.ReadEndElement();
  15:         }


Change the database connection string parameters in app.config file





   1: <?xml version="1.0" encoding="utf-8" ?>
   2: <configuration>
   3:     <configSections>
   4:     </configSections>
   5:     <connectionStrings>
   6:         <add name="StreamingServicePOC.Properties.Settings.CustomerConnectionString"
   7:             connectionString="Data Source=Servername;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=Username;Password=password"
   8:             providerName="System.Data.SqlClient" />
   9:     </connectionStrings>
  10: </configuration>


You can find the source code from here



If you have any suggestions or queries, please feel free to ask….



Happy Coding!!!!!!!!!!!!


Anilal Sambasivan

Posted by Anilal Sambasivan at 11:18 PM

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)