Finding the count of entities using using ObjectContext in Entity Data Model

The best part of using Entity Data Model designer is having the facility to use LINQ with the objectcontext.

Now consider you have the object-layer code created as follows-

/// 

/// No Metadata Documentation available.
/// 
public partial class InventoryEntities : ObjectContext
{

//autogenerated code
 /// 

        /// No Metadata Documentation available.
        /// 
        public ObjectSet Products
        {
            get
            {
                if ((_Products == null))
                {
                    _Products = base.CreateObjectSet("Products");
                }
                return _Products ;
            }
        }
        private ObjectSet _Products; 

}

To find the list of products in the database the code should be

InventoryEntities inventory = new InventoryEntities ();
long cnt = inventory.Products.Execute(MergeOption.AppendOnly).LongCount();

The inside of a computer is as dumb as hell but it goes like mad!
~Richard Feynman

Continue Reading

Object-Layer Code not getting updated correctly

Generating Object-Layer Code is the easiest way to generate code for database instead of writing the for yourself. refer to Generating Object-Layer Code

Code can be generated for Object-Layer as follows:-

1. Create an Entity Data Model design
2. Right click on EDM design and select "Update Model from Database..."
3. Select the database and select the required table
4. Select finish to get the code generated

The code is now generated and is ready to be used.

Now consider you have modified the database tables and would like to update Object-Layer code. Just need to run the steps from 2 to 4.

But you might have observed that the code does not get updated, the reason being the code is generated only first time it is created.


This is a feature which is provided so that customised changes to the code does not get lost when re-generating the code.


So to overcome this create another class that extends this class and add all your custom code to the derived class.

Every time you update the database
- Remove the contents of the file TestDatabase.Designer.cs.
- Re-run the steps from 2 to 4, this will recreated the code that reflects the database changes



To have no errors
Would be life without meaning
No struggle, no joy
~Brian M. Porter, 1998

Continue Reading

Improving Performance of JQGrid for millions of records : Part2

This article will address more from ASP.NET point of view assuming you have already read Part 1

Now that you have all the back-end available lets get on with developing the website.

Start with the usual steps

1. Create an ASP.NET website
2. Add the required java scripts (JQuery, JQueryUI, JQGrid plugin)
3. Now add a new web service to the project and name it InvoicingService

InvoicingService.cs should look like

[WebMethod, ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string GetInvoiceData(int? page, int? rows, string sidx, string sord, bool _search, string searchField, string searchOper, string searchString)
{
// code to run stored procedure and get the relavent data from database 
// need to pass the search condition, page and rows to stored procedure
}

refer to call a stored procedure .

Now modify default.aspx to have the following :-

Create a new script file common.js with following code

$(function () {

  $("#grid_invoices_tbl").jqGrid({
        url: "InvoicingService.asmx/GetInvoiceData",
        datatype: "json",
        mtype: 'POST',
        ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
        colNames:
            ['INVOICEID',
            'CUSTOMER NAME',
            'PRODUCT NAME',
            'UNIT PRICE',
            'QUANTITY',
            'TOTAL PRICE'],
        colModel: [
        { name: 'INVOICEID', index: 'INVOICEID', align: 'left'},
  { name: 'CUSTOMERNAME', index: 'CUSTOMERNAME', align: 'left'},
  { name: 'PRODUCTNAME', index: 'PRODUCTNAME', align: 'left'},
  { name: 'UNITPRICE', index: 'UNITPRICE', align: 'left'},
  { name: 'QUANTITY', index: 'QUANTITY', align: 'left'},
  { name: 'TOTALPRICE', index: 'TOTALPRICE', align: 'left'}
  ],
        rowNum: 10,
        rowList: [5, 10, 20, 50],
        pager: '#grid_invoices_pager',
        sortname: 'INVOICEID',
        viewrecords: true,
        sortorder: "asc",
        caption: 'Invoice Details',
        serializeGridData: function (postData) {
            if (postData.searchField === undefined) postData.searchField = null;
            if (postData.searchString === undefined) postData.searchString = null;
            if (postData.searchOper === undefined) postData.searchOper = null;            
            return JSON.stringify(postData);
        },
        loadError: function (xhr, status, error) {
            alert(status);
            alert(xhr);
            alert(error);
        },
        loadComplete: function (data) {
           // alert(data);
        },
        jsonReader: {
            root: function (obj) { return obj.d.rows; },
            page: function (obj) { return obj.d.page; },
            total: function (obj) { return obj.d.total; },
            records: function (obj) { return obj.d.records; }
        },             
        autowidth: true
    }).jqGrid('navGrid', '#grid_invoices_pager', { edit: false, add: false, del: false, search: true });

});

Hope this should have helped you to handle huge amounts of data.


Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
~Andy Rooney

Continue Reading

Improving Performance of JQGrid for millions of records : Part 1

JQGrid plugin does not require any introduction for itself. As everyone already knows the fact that JQGrid plugin is a powerful plugin for serving data from server to the client. Also it provides very good features such as sorting, paging, filtering etc.

Now lets look at how JQGrid can be used in ASP.NET for serving millions of records with out having the following effects:-

- website hanging
- memory leaks
- page time out
- taking 5 to 6 mins to serve data (only after increasing page timeout)
-
etc.

Well if you have come before you have decided on the implementation this is the right article for you.

But if you have come here after facing the issue then the changes you would have to make will be little painful but will solve your nightmares once for all.

Now enough of the explanation lets start with step by step guidelines.

This article will address more from database point of view.

Assume a simple database with 2 tables (Customer and Invoice) as follows:-

CREATE TABLE [dbo].[CUSTOMER](
   [CUSTOMERID] [bigint] IDENTITY(1,1) NOT NULL,   
   [CUSTOMERNAME] [nvarchar](255) NULL,
   [ADDRESS] [nvarchar](255) NULL
);

CREATE TABLE [dbo].[PRODUCT](
   [PRODUCTID] [bigint] IDENTITY(1,1) NOT NULL,   
   [PRODUCTNAME] [nvarchar](255) NULL,   
   [UNITPRICE] [int] NULL
);

CREATE UNIQUE NONCLUSTERED INDEX [CUSTOMER_CUSTOMERID_IDX] ON [dbo].[CUSTOMER] ([CUSTOMERID] ASC);

CREATE TABLE [dbo].[INVOICE](
   [INVOICEID] [bigint] IDENTITY(1,1) NOT NULL,      
   [CUSTOMERID] [bigint] NOT NULL,      
   [PRODUCTID] [int] NULL,
   [QUANTITY] [int] NULL
  );

CREATE UNIQUE NONCLUSTERED INDEX [INVOICE_INVOICEID_IDX] ON [dbo].[INVOICE] ([INVOICEID] ASC);
CREATE NONCLUSTERED INDEX [INVOICE_CUSTOMERID_IDX] ON [dbo].[INVOICE] ([CUSTOMERID] ASC);

Now create a view to get the data from both the tables

CREATE VIEW [dbo].[INVOICE_VIEW]  
AS  
SELECT   
 INV.[INVOICEID],  
 CUS.[CUSTOMERNAME],  
 PRD.[PRODUCTNAME],  
 PRD.[UNITPRICE],   
 INV.[QUANTITY],  
 (PRD.[UNITPRICE] * INV.[QUANTITY]) AS [TOTALPRICE]   
FROM   
 INVOICE INV,  
 CUSTOMER CUS,  
 PRODUCT PRD  
WHERE  
 INV.CUSTOMERID = CUS.CUSTOMERID AND  
 INV.PRODUCTID = PRD.PRODUCTID  

Now crucial part is to get the data from the server and the data you need to display in a page (either 10,20 etc based on the page size).

Now lets create a stored procedure to serve the data required for jqgrid.

CREATE PROCEDURE [dbo].[GETINVOICES]
 @CONDITION nvarchar(255) = NULL,
 @SORTCONDITION nvarchar(255) = NULL,
 @PAGE INT,
 @ROWS INT,
 @RECORDCOUNT INT OUTPUT
 AS 
 DECLARE
  @params nvarchar(4000),
  @sql    nvarchar(4000),
  @STARTRECORD INT,
  @ENDRECORD INT
 BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  SET @RecordCount = 0;

  SET @SQL = 'SELECT @cnt = COUNT(*) FROM [INVOICE_VIEW] WHERE ' + COALESCE(@CONDITION,' 1 = 1');
  SELECT @params = N'@cnt int OUTPUT';
  EXEC sp_executesql @sql, @params, @cnt = @RecordCount OUTPUT
  
  if(@RecordCount > 0)
  begin
  
   SET @ENDRECORD = @PAGE * @ROWS;
   SET @STARTRECORD = @ENDRECORD - @ROWS + 1;      
   
   SET @SQL = 'select INVOICE_VIEW.INVOICEID,CUSTOMERNAME,PRODUCTNAME,UNITPRICE,QUANTITY,TOTALPRICE from [INVOICE_VIEW],'; 
   SET @SQL = @SQL  + '(select row_number() over (' + COALESCE(@SORTCONDITION,' order by INVOICEID asc ') + ') as rowid,INVOICEID';   
   SET @SQL = @SQL  + ' from [INVOICE_VIEW]) INVOICEROWS';
   SET @SQL = @SQL  + ' where ' + COALESCE(@CONDITION,' 1 = 1');
   SET @SQL = @SQL  + ' and (INVOICEROWS.rowid between @PARAM_STARTRECORD and @PARAM_ENDRECORD)';
   SET @SQL = @SQL  + ' and INVOICE_VIEW.INVOICEID = INVOICEROWS.INVOICEID';
   
   SELECT @params = N'@PARAM_STARTRECORD int,
       @PARAM_ENDRECORD int';

   EXEC sp_executesql @SQL,@params,@PARAM_STARTRECORD=@STARTRECORD,@PARAM_ENDRECORD=@ENDRECORD
  end;

 END;

continue to Improving Performance of JQGrid for millions of records : Part2

Database: the information you lose when your memory crashes.
~Dave Barry, Claw Your Way to the Top

Continue Reading

Create Work Item Template for Team

Work item templates are very good option to apply a set of changes for a group of work items( for eg: bugs). But have you come across having difficulty not to be able to have shared work item templates, if so just follow these simple steps and you can achieve it.

Before creating a work item template, choose a shared location where each of team member have access such \\csharptechies\workitemtemplates, if you do not already have one create one shared folder on a network.

Now that you have the shared location, update your work item template settings in visual studio settings to point a shared location as follows :-

1. In the Tools menu, click Options.
2. In Options window, click Team Foundation Server Power Tools then click Work Item Template.
3. In the Work Item Template option page, click Browse to change the default location to \\csharptechies\workitemtemplates.
4. Click OK.

(above steps would only work for VS2010)

Now that your default location is set for work item template, create a new work item template.

After creating, browse to \\csharptechies\workitemtemplates and you should be able to see a new file created with extension .wt

Ask all of your team members to update there default work item template location.

Note:- A work item template is specific to a project

There is only one satisfying way to boot a computer. ~J.H. Goldfuss

Continue Reading