Sunday, September 9, 2012

How to delete nth row of the table in SQL Server?


If database table contains any primary key value then performing update and delete operations are not big task. But generally we face problem when we don’t have any primary key in table, on that case we can’t target to any particular column for deleting or updating records because that column can contain duplicate values.

In this article, I am explaining –

How to delete record based on row number? 
Or
How to delete nth row of the table?           
Or
How to delete record without primary key?           
 
                             
To perform this operation we have to use ROW_NUMBER() function of SQL Server. This function will help us to get nth row which we want to delete from table.

ROW_NUMBER()- 

ROW_NUMBER() is function in SQL Server which return sequential number for each record in result set. This returned number can be used as an identity for each record which is present in result set. When we don’t have any primary key in table then it is very useful to identify the record in result set. Using this sequential number we can perform operation like delete and update even we don’t have primary key for table.

Here is syntax to delete nth row from the table-

Syntax-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (Column_Name))
        FROM    Table_ame
        Where Condition
        ) AS Record
WHERE   Record.rowno = RowIndex


Here RowIndex is an index of the row which you want to delete from table. Here inner query will return result set including sequential number for each record and this will be ordered based on column_name which will be passed in ORDER BY clause. Finally outer query will perform delete operation based on given condition . 

Example-

Suppose if we want to delete 5th row of the Employee table then query will be like this-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (EmpName))
        FROM    Employee
        ) AS Record
WHERE   Record.rowno = 5


In above query, First inner query will be executed, which will generate result set over Employee table including row number over EmpName. Finally outer query will perform delete operation over result of inner query with given condition-

WHERE   Record.rowno = 5

This condition will detect 5th row from inner result and delete record from the table. In this example I didn't put any condition for inner query but if we have requirement then we have put where condition also to get result set like I have written in given syntax.


Thanks

Tuesday, September 4, 2012

How to change sort order of crystal report by using code?



Crystal report -

Crystal report is a tool which provides facility to display data in reporting format. It also support functionality like sorting of data based on sort field. This sorting can be done in design time and run-time also.


Sorting data using sort field is easy while design time but if we want to sort data in run-time then some time it can generate error. The most common error for run-time sorting is-


"The sorting already exists"


In this article, I will explain -
How to change sort order of crystal report by using code?

First I will tell what the reason for this error is and how we can solve this error.


Reason-


This error comes when we are trying for setting sort field for crystal report using code and that particular sort-field is already added as sort order field for crystal report in design time.


Example-


Suppose we want set "Name" as sort order field and this field ("Name") is already present as sort order in design time then it will generate error.


Workaround-


Simple workaround for this problem is that swapping of sort order fields.

Suppose you have added two fields (firstName and lastName) as sort fields for report in design time
And present sort field is firstName now if you want to changes sort field to lastName then will generate error. So here we have a trick – Take any column from table which is not present in sort field which you have added in design time.

Design time –

Sort field (0) - firstName
Sort field (1)-lastName

In code set like this-

Sort field (0) - firstName
Sort field (1) - address (any field of table which is not in sort field)

Now again swap between firstName and lastName. This time it will successful because now currently we don’t have lastName as sort field in report. 

Here I am going to explain how we can solve this problem? Here is sample code to solve this problem-

String SortField="yourSortField";

bool fieldPresent = false;

 //CHECKING SORT FIELD PRESENT IN THE REPORT IF YES THEN SWAP THE POSITION //TO ZERO

    for (int i = 0; i <= Report100.DataDefinition.SortFields.Count - 1; i++)
  {
       if (Report100.DataDefinition.SortFields[i].Field.FormulaName == SortField)
        {
            fieldPresent = true;
            FieldDefinition tempDef = New FieldDefinition;
            FieldDefinition currentDef =New FieldDefinition;

            //HOLDING CURRENT [0'TH] SORT FIELD

            currentDef = Report100.DataDefinition.SortFields[0].Field;

            //HOLDING FOUND SORT FIELD

            tempDef = Report100.DataDefinition.SortFields[i].Field;

            //REPLACING FOUND FIELD  DEFINITION WITH OTHER FIELD DEFINITION EXCEPT (Already Present SortField in design time) BECAUSE THESE SORT FIELDS ARE ALREADY IN REPORT SO GENERATE ERROR


            Report100.DataDefinition.SortFields[i].Field = Report100.Database.Tables[0].Fields[5];


            //SWAPPING THE CURRENT SORT FIELD WITH FOUND SORT FIELD

            Report100.DataDefinition.SortFields[0].Field = tempDef;
            Report100.DataDefinition.SortFields[i].Field = currentDef;

            break;

        }
    }

    //IF SORT FIELD IS NOT PRESENT THEN REPLACE THE CURRENT FIELD DEFINITION


    if (fieldPresent == false)

    {
        FieldDefinition FieldDef = New FieldDefinition;
        FieldDef = Report100.Database.Tables[0].Fields[SortField];
        Report100.DataDefinition.SortFields[0].Field = FieldDef;
    }

Report100.DataDefinition.SortFields[0].SortDirection = CrystalDecisions.Shared.SortDirection.AscendingOrder;



In above code, I am setting sort order field, Here this I am swapping sort field orders but we can’t swap sort field directly because if field is present as sort field then it will generate error, for this I have written code to identify that sort field is present or not.


If sort field is not present then I am directly setting given field ("SortField") as sort field. Otherwise to set sort order field I am implementing swapping concept.


For this I have take two objects of  FieldDefinition class.


FieldDefinition tempDef = New FieldDefinition;
FieldDefinition currentDef =New FieldDefinition;


Using this first, I am replacing present sort order field  with different (which is not present in sort order field ) sort order field . Finally I am changing the sort order like this-

 //SWAPPING THE CURRENT SORT FIELD WITH FOUND SORT FIELD


Report100.DataDefinition.SortFields[0].Field = tempDef;

Report100.DataDefinition.SortFields[i].Field = currentDef;


Now done, try this code, I have successfully implemented this code.



Thanks