On the way of being a .Net Developer

Custom paging in Gridview With ObjectDataSource

2009-10-13T16:49:00.005+05:30

Many times we use paging with GridView with AllowPaging = "True" and binding the GridView with some ObjectDataSource. It actually fetches all row from the database and depending on the no of records it fetched, it generates the Pager section of the page and according to the current page index, it shows the set of record.
This approach is good for when you have limited no of record in the database. But there are many situations where there is thousands of rows in the database and you have to display only 25 records(PageSize) out of that thousands of records. So it is not good practice to fetch all record through object datasource to display only 25 records.
Here we should use custom paging to fetch only the 25 rows starting from the startindex which is
CurrentPageIndex*PageSize (As Index is 0 Based).
Calculated automatically by objectdatasource.
Here is the change you have to do

Set EnablePaging="True" for ObjectDataSource

Set The SelectMethod Property

Set value for following Properties of ObjecDataSource StartRowIndexParameterName, SortParameterName, MaximumRowsParameterName
These parameters needs to be present in the method attached for selecting the result for objectdatasource.

Set SelectCountMethod to the name of method returning the total no of rows.

Here is my ObjectDataSourceCode


<asp:objectdatasource id="objDS" runat="server" enablepaging="True" selectmethod="SearchResult" typename="PRP.BusinessLogic.TpilTblNsefaoscripmaster" startrowindexparametername="paramStartRowIndex" sortparametername="paramSortExpression" maximumrowsparametername="paramPageSize" selectcountmethod="GetNoOfRows">
          <selectparameters>
              <asp:parameter name="paramSymbol" type="String">
                 <asp:parameter direction="Output" name="paramTotalNoOfRows" type="Int32">
                 </asp:parameter>
             </asp:parameter>
         </selectparameters>
</asp:objectdatasource>

The SearchResult & GetNoOfRows Function


public DataTable SearchResult(string paramSymbol,
       int paramStartRowIndex, int paramPageSize, string paramSortExpression, out int paramTotalNoOfRows)
{
  TpilTblNsefaoscripmasterHelper.SearchResult(paramSymbol, paramStartRowIndex, paramStartRowIndex + paramPageSize, paramSortExpression, ref paramTotalNoOfRows);
  this.totalNoOfRows = paramTotalNoOfRows;
}
int totalNoOfRows = 0;
public int GetNoOfRows(string paramSymbol,
       int paramStartRowIndex, int paramPageSize, string paramSortExpression, out int paramTotalNoOfRows)
{
   paramTotalNoOfRows = totalNoOfRows;
   return totalNoOfRows;
}

The parameter list for both the functions are same but the second function retuns int no of rows. But i don't have used two function call for getting the total noofrows. Inside select function i have set the totalNoOfRows parameter which is accessed inside GetNoOfRows method to get the no of rows. This works as objectdatasource calls both the method on same object instance of the class and none of the methods and the class variable is static and the select methos is called first and then the row count method is called.
Now check the stored procedure used for this purpose


Create PROCEDURE [dbo].[USP_TPIL_tbl_NSEFAOScripSelectForSearch]
@Symbol varchar(50),
@StartIndex int = 0,
@EndIndex int =25,
@SortExpression varchar(50),
@TotalNoOfRows int OUTPUT
AS
SET NOCOUNT ON
Select * from
(
SELECT m.Instrument,m.Symbol,m.SymbolDescription,ExpiryDate,
Row_Number() OVER (Order By
     CASE WHEN @SortExpression='EXPIRYDATE' OR @SortExpression='EXPIRYDATE ASC' THEN m.EXPIRYDATE END DESC,
     CASE WHEN @SortExpression='EXPIRYDATE DESC' THEN m.EXPIRYDATE END ASC ,
     CASE WHEN @SortExpression='SYMBOL' OR @SortExpression='SYMBOL ASC' THEN m.SYMBOL END DESC,
     CASE WHEN @SortExpression='SYMBOL DESC' THEN m.SYMBOL END ASC
    ) as RowNumber 
FROM
[dbo].[TPILl_NSEFAOScripMaster] m
WHERE 
(m.[Symbol] LIKE @Symbol OR @Symbol = '')
)t
Where RowNumber>@StartIndex  AND RowNumber<=@EndIndex   --return SELECT @TotalNoOfRows = Count(*)  FROM   [dbo].[TPILl_NSEFAOScripMaster] m  WHERE    (m.[Symbol] LIKE @Symbol OR @Symbol = '')

As i am using Sql Server 2005, i used ROW_Number() function. The value of output parameter "TotalNoOfRows " is set to the variable "totalNoOfRows" inside "SearchResult" function.
To know about how to get Row Number in Sql Server 2000 please check the bottom of this link.

Focus the first control of your page

2009-04-16T18:07:00.004+05:30

In all pages we have to put the focus on the first control of the page. It can be a textbox,a radio button, a checkbox, a select field, a textarea or a button. For my current project i have created a function which i have placed inside the masterpage and it will traverse through the page starting from a particular control (inside the control) and will take the first occurance of the above types of control and will put the cursor focus on the control. Below is the javascript code i used to achieve this


     function selectFirstControl()
     {
         var objIP = getFocusableControl(document.getElementById('tdContentBody'));
         if(objIP!=null)
         {
             objIP.focus();
             return;
         }
     }
  
     function getFocusableControl(parentCtrl)
     {
         if(!$(parentCtrl).visible())
             return null;
          if(parentCtrl.tagName == "INPUT")
          {
             if(parentCtrl.getAttribute("type")=="hidden")
             {
                 return null;
             }
             return parentCtrl;
          }
          else          
          {
             if(parentCtrl.tagName == "SELECT")
             {
                 return parentCtrl;
             }
          }
          var children = $(parentCtrl).childElements();
         for(var i = 0;i<children.length;i++)
            {
               var resCtrl = getFocusableControl(children[i]);
               if(resCtrl != null)
                   return resCtrl;
            }
       }

Call the function selectFirstControl(); from the bottom of your page.
Here i have called the function "getFocusableControl" recursively to get the control. It is checking the tagname of the node, if it is "Input" or "Select" then it is excluding the hidden fields by checking the type property for for inputs and if it is not hidden, then it is putting the focus on the control.
Iam checking if the parent control visible or not with $(parentCtrl).visible() which is a function from prototypejs to check the visibility. But this checks for the inline "display" property of the element, if "display :none" it returns false. But it cannot check if you are using the hidden property of hiding the control with stylesheet class.
If you are not using prototypejs, you can check the direct style property of the control.

Trigger Html/Javascript Event from Javascript

2009-04-07T19:38:00.010+05:30

In many situation i had to trigger JavaScript Event from code. Like click on a button when something happened to post the page. It mainly comes into picture when i use UpdatePanel or using modalpopup for click on a button inside grid/list/gridview ect. This creation of event is very much dependent on the browser. Here is the code to trigger a button click event

function clickButton()
{
if( document.createEvent )
{
 var evObj = document.createEvent('MouseEvents');
 evObj.initMouseEvent( 'click', true, false, window, 0, 12, 345, 7, 220, false, false, true, false, 0, null );
 $(' btnTransRefresh').dispatchEvent(evObj);
}
        else
if( document.createEventObject )
{
    $('btnTransRefresh').fireEvent('onChange');
}
}

Here i am using Prototyprjs framework, so you see the "$". You can replace those with document.getElementById('') syntax.
initMouseEvent takes the following parameters.

initMouseEvent( 'type', bubbles, cancelable, windowObject, detail, screenX, screenY, clientX, clientY, ctrlKey, altKey, shiftKey, metaKey, button, relatedTarget )

In many scenarios we do some javascript calculation on change of value in some particular textboxes. But in some scenarios, the value in the textbox on change of which we need to calculate is done through some javascript. Like in my project i was using calender from ajax control toolkit which is inside a project specific control names PRPCalender. In some pages i had to do some calculations only when the value in calender is changed. So i could not use the javascript events associated with the calender control (ajax). So i decided to go with triggering onchange event as the textbox showing the calender control doesn't fire the onchange event as the value is changed through JavaScript.

string methodName = "prpCalenderDateChanged" + txtCalendar.ClientID;
calendarExtender.OnClientDateSelectionChanged = methodName;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.AppendFormat(@"
  function {0}(sender,eArg)
  {{
      //alert('Calender Date Changed');      
      var tbId = '{1}';
      //$(tbId).blur();
      //$(tbId).focus();
      if( document.createEvent )
      {{
          var evObj = document.createEvent('HTMLEvents');
          evObj.initEvent( 'onchange', true, false);
          $(tbId).dispatchEvent(evObj);
      }}
      else
          if( document.createEventObject )
          {{
              $(tbId).change();
          }}
  }}
", methodName, txtCalendar.ClientID);
ScriptManager.RegisterStartupScript(this, this.GetType(), "textChanged" + txtCalendar.ClientID, sb.ToString(), true);

Here inside my calender control PRPCalender, i am registering a function which is unique for each calender control inside any page (i am using the client id as concatined to generate the method name) which fires the onchange event on the textbox.

calendarExtender.OnClientDateSelectionChanged = methodName;

which is called by toolkit JS when the date value is changed.
Some Useful Link Link1

Trigger not working when importing data with SSIS package

2009-03-26T12:22:00.008+05:30

I am very new to SSIS package. Actually i am not a hardcode SQL guy, so i never needed to work with SSIS before. In my current project i needed to import data after fetching from a server with FTP and then extract the ZIP files and insert it into respective tables. For eatracting ZIP files i used "SharpZipLib" which is open source library for dot net. Then i created a temp table in which i first inserted to remove duplicate. Remember, i created the temp table on the fly inside the "Execute SQL Task" with create table statement and after insert to the original table i deleted the table. In this scenario you have to change the "DelayValidation" property of the "Data Flow Task" to "true" (By default it is false). Otherwise you will get some validation error as the temp table in which you are going to insert data is not present now, it is created on the fly and deleted after insert is completed.
Now in my case i had a trigger which need to fire each time i instered some data into the table. Actually, i was updating the stock rate and needed to copy the rate by trigger. But to my surprise, the trigger was not firing when i insert/update the data by the SSIS. But the trigger fires when i do the update by SQL.
The problem is with the settings in "OleDB Destination" inside "Data Flow Task". Here is how i solved the issue
Right click on th "OldeDB Destination" and you will find "Show Advanced Editor" and open the Advanced editor.

In the editor check the "FastLoadOptions" which is by default "TABLOCK,CHECK_CONSTRAINTS". You need to add one more option "FIRE_TRIGGERS" . And thats it. But remember the trigger runs once for each update/insert. Not for every update/insert. So in case where we are doing batch update, you must write the trigger code in such a way that it will be able to handle the situation for multiple row sets. Here is a sample statement

CREATE TRIGGER UpdateStockRate
ON INV_tbl_StockMaster
AFTER INSERT,Update
AS
                                                                                                            
UPDATE t
SET CMP = i.LastTradedPrice
FROM INV_tbl_Holding t
JOIN INSERTED i
 ON i.Symbol = t.Symbol

Reporting Service Custom Code Add Assembly

2009-02-27T11:03:00.002+05:30

For adding assembly you have to copy the .dll assembly file to the following location "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies" folder.

Paging in Datalist and Repeater

2009-02-20T17:21:00.009+05:30

Datalist and Repeater doesn't provide any paging by default like gridview or datagrid. So we avoid using datagrid/repeater as much as possible. But there comes situation where using datalist or repeater is easier to maintain the design for the page. Like some product is displayed like row by row with 4 producs in a single w. There its tough to achieve (but not impossible) this design output wit datagrid/gridview. So we choose datalist making its RepeatColumns="4" RepeatDirection="Horizontal".
But paging???
So i created a control named as COUSPaging (Cous is my project name). Which contains two link buttons as "Next" and "Previous" and a textbox for goto page as below

In my control i used a public event "PageIndexChanging" which is of type GridViewPageEventHandler, so that it sends the same parameter as GridView. The event is handled from the Parent page containg the DataList.
The Paging.aspx Page Code:
<table>
<tr>
<td>
<asp:LinkButton ID="linkPrevious" CssClass="button" runat="server" Text="Previous" CommandName="Previous" OnClick="linkPrevious_Click"></asp:LinkButton>
</td>
<td>
<asp:Literal ID="litPageCount" runat="server"></asp:Literal>
<span id="spanGotpPage" runat="server">Goto Page:
<asp:TextBox Width="15" ID="txtPageNo" CssClass="inputfld" runat="server" MaxLength="3"></asp:TextBox>
<asp:Button ID="btnGo" runat="server" Text="Go" CssClass="button" OnClick="btnGo_Click" />
</span>
</td>
<td>
<asp:LinkButton ID="linkNext" CssClass="button" runat="server" Text="Next" CommandName="Next" OnClick="linkNext_Click"></asp:LinkButton>
</td>
</tr>
</table>

.Cs Page Coding:


void Controls_Paging_PreRender(object sender, EventArgs e)
{
    litPageCount.Text = string.Format("{0} of {1} Pages  ", SelectedPageIndex + 1, TotalNoOfPages);
    if (SelectedPageIndex == TotalNoOfPages - 1)
    {
        linkNext.Enabled = false;
    }
    else
    {
        linkNext.Enabled = true;
    }
    if (SelectedPageIndex == 0)
    {
        linkPrevious.Enabled = false;
    }
    else
    {
        linkPrevious.Enabled = true;
    }
    txtPageNo.Text = (SelectedPageIndex + 1).ToString();
}
protected void btnGo_Click(object sender, EventArgs e)
    {
        int newPageIndex = 0;
        try
        {
            newPageIndex = Convert.ToInt32(txtPageNo.Text) - 1;
            if (newPageIndex > TotalNoOfPages - 1)
            {
                newPageIndex = TotalNoOfPages - 1;
            }
            if (newPageIndex < 0)
            {
                newPageIndex = 0;
            }
            SelectedPageIndex = newPageIndex;
            PageIndexChanging(btnGo, new GridViewPageEventArgs(newPageIndex));
        }
        catch { }
    }
public event GridViewPageEventHandler PageIndexChanging;
    public int PageSize
    {
        set
        {
            ViewState["PageSize"] = value;
        }
        get
        {
            if (ViewState["PageSize"] is int)
                return (int)ViewState["PageSize"];
            else return 0;
        }
    }

    public int CurrentPageIndex
    {
        set
        {
            ViewState["CurrentPageIndex"] = value;
        }
        private get
        {
            if (ViewState["CurrentPageIndex"] is int)
                return (int)ViewState["CurrentPageIndex"];
            else return 0;
        }
    }
    public int StartIndex
    {
        get
        {
            return SelectedPageIndex * PageSize;
        }
    }

    public int TotalNoOfPages
    {
        get
        {
            return (int)Math.Ceiling(TotalNoOfRows / (float)PageSize);
        }
    }
    public long TotalNoOfRows
    {
        set
        {
            ViewState["TotalNoOfRows"] = value;
        }
        get
        {
            if (ViewState["TotalNoOfRows"] is long)
                return (long)ViewState["TotalNoOfRows"];
            else return 0;
        }
    }
    public int SelectedPageIndex
    {
        set
        {
            ViewState["SelectedPageIndex"] = value;
        }
        get
        {
            if (ViewState["SelectedPageIndex"] is int)
                return (int)ViewState["SelectedPageIndex"];
            else return 0;
        }
    }
    protected void linkNext_Click(object sender, EventArgs e)
    {
        if (SelectedPageIndex < TotalNoOfPages)
        {
            SelectedPageIndex = SelectedPageIndex + 1;
            PageIndexChanging(sender, new GridViewPageEventArgs(SelectedPageIndex));
        }
    }
    protected void linkPrevious_Click(object sender, EventArgs e)
    {
        if (SelectedPageIndex > 0)
        {
            SelectedPageIndex = SelectedPageIndex - 1;
            PageIndexChanging(sender, new GridViewPageEventArgs(SelectedPageIndex));
        }
    }

The page where the control is used

Here the page size is initialized. From the cs page call the bind datalist function as


void bindDataList()
{
    long totalNoOfRows;
    ImageTblMasterTable dtImage = ImageTblMaster.SelectAll(-1, 1, pagingDL.StartIndex, pagingDL.PageSize, out totalNoOfRows);
    pagingDL.TotalNoOfRows = totalNoOfRows;
    dlImage.DataSource = dtImage;
    dlImage.DataBind();
}

Here i am using custom paging. So i am sending the page start index and page size which is converted to ennIndex before the call to SP. Here is code from SP


Select @TotalNoOfRows = Count(*) FROM [dbo].[Image_tbl_Master]

Select * from
(
SELECT ROW_NUMBER() OVER (ORDER BY ImageId) as RowNumber, m.* FROM [dbo].[Image_tbl_Master] as m
)t Where
RowNumber >= @StartIndex
AND
RowNumber <= @EndIndex As i am using SQL Server 2005 i get the facility of ROW_Number() but in SQL Server 2000 or before, there is no row number. So you have to change the query like Select * from ( Select (Select Count(*) from Image_tbl_Master where ImageId<=img.ImageId )as RowNumber, img.* from Image_tbl_Master as img )t  Where RowNumber >= @StartIndex
AND
RowNumber <= @EndIndex

Here row number is calculated on the fly through a subquery. See the where clause "ImageId<=img.ImageId ", it is responsible for the sorting. As i was sorting with ImageId i have used it like that.

Add / Insert a row to a gridview

2009-01-13T14:31:00.006+05:30

In many cases we come across the situation where we have to add a row to a gridview. We can create a new grid view row through the GridViewRow class. But there is no property/method to a grid to add the row to the gridview. Not even the Gridview.Rows property has any methor to add/insert a row.
In a situation i had to add a footer to show the total of the fields displayed in the gridview. I knew that i could set the value in footer through RowDataBound event of GV by inspection like


if(e.Row.RowType==DataControlRowType.Footer)
{

}

here e is the GridViewRowEventArgs parameter to the event.
But i was looking for something new. I found i can create the row through GridViewRow which takes the rowindex, rowtype ... parameters.
Then i found i can cast the parent of any row of gridview to a table but cannot cast the gridview directly to table. So here is how i implemented...
::::::::::::::::::::::::::::::::::::::::::::::::::::::


gvTaxPlanning.DataSource = dv;
         gvTaxPlanning.DataBind();
if (gvTaxPlanning.Rows.Count > 0)
         {
             GridViewRow gvr = new GridViewRow(gvTaxPlanning.Rows.Count, gvTaxPlanning.Rows.Count, DataControlRowType.Footer, DataControlRowState.Normal);
             TableCell tc = new TableCell();
             tc.Text = "Total";
             gvr.Cells.Add(tc);
             tc = new TableCell();
             tc.Text = (tEQTax + tMFTax + tULTax + tBulTax + tBankTax + tAssetTax).ToString(GlobalSettings.FloatNumberFormatter);
             gvr.Cells.Add(tc);
             gvr.Font.Bold = true;
             gvr.BackColor = System.Drawing.ColorTranslator.FromHtml("#000084");
             gvr.ForeColor = System.Drawing.ColorTranslator.FromHtml("#FFFFFF");
             Table tab = (Table)gvTaxPlanning.Rows[0].Parent;
             tab.Rows.Add(gvr);            
         }

I thought that it will need the
gridView.ShowFooter = true; to show the new row i have added as it was footer and by default grid doesn't show the footer.
But not... it ws showing the footer whithout setting it to true...
And one more thing... The RowCreated event is not fired when you add this new row.

Read More http://blog.falafel.com/2007/04/12/DynamicallyAddRowsToAGridView.aspx

Paging,Sorting in datagrid

2008-12-31T20:25:00.004+05:30

Paging and sorting is the most common feature of datagrid. As i am very much used to with ObjectDataSource, i don't have to do anything for implementing Paging and sorting.
Just set the datasource of the gridview to the ObjectDataSource and
set the AllowPaging and AllowSorting property of the grid view to true and
for each column put some SortExpression and
some pagesize for the gridview.
And thats all for implementing the paging and sorting. ObjectDataSource will take care of the remainings for you.
But some situalition arises where i must not use ObjectDataSource, actuaaly not using ObjectDataSource is easier from coding perspective though we have to implement the paging and sorting explicitly. So i used this code, a overloaded version of bindGrid() function to handle the situaltion


protected override void OnInit(EventArgs e)
  {
      base.OnInit(e);
      gvStockCode.Sorting += new GridViewSortEventHandler(gvStockCode_Sorting);
      gvStockCode.PageIndexChanging += new GridViewPageEventHandler(gvStockCode_PageIndexChanging);
  }

  void gvStockCode_PageIndexChanging(object sender, GridViewPageEventArgs e)
  {
      gvStockCode.PageIndex = e.NewPageIndex;
      bindGrid(); //Call bindgrid without any parameter so that previous sorting is maintained
  }

  void gvStockCode_Sorting(object sender, GridViewSortEventArgs e)
  {
      gvStockCode.PageIndex = 0; //Don't change page index if you want to show the old page the user was
      if (ViewState["SortDirection"] != null && ViewState["SortExpression"] != null)
        {

            SortDirection direction = (SortDirection)ViewState["SortDirection"];
            string sortExpr = ViewState["SortExpression"].ToString();
            if (sortExpr.ToLower() == e.SortExpression.ToLower())
            {
                direction = direction == SortDirection.Ascending ? SortDirection.Descending : SortDirection.Ascending;
            }
            bindGrid(e.SortExpression, direction);
        }
        else
        {
            bindGrid(e.SortExpression, e.SortDirection);
        }
  }
  protected void Page_Load(object sender, EventArgs e)
  {
If(!IsPostBack)
      bindGrid();
  }
void bindGrid()
  {
      SortDirection direction = SortDirection.Ascending; //Default Sort Direction
      string sortExpr = ""; //Default sort expression, you can put any expression if wanted to be default for grid
      if (ViewState["SortDirection"] != null && ViewState["SortExpression"] != null)
      {
          direction = (SortDirection)ViewState["SortDirection"];
          sortExpr = ViewState["SortExpression"].ToString();
      }
      bindGrid(sortExpr, direction);
  }
  void bindGrid(string sortExpression, SortDirection sortDirection)
  {
      DataTable dt = BusinessLogic.FillTable();//Some function declared in BL

      DataView dv = new DataView(dt);
      if (sortExpression != string.Empty)
      {
          if (sortDirection == SortDirection.Ascending)
              dv.Sort = sortExpression + " ASC";
          else
              dv.Sort = sortExpression + " DESC";
      }
      gvStockCode.DataSource = dv;
      gvStockCode.DataBind();
      ViewState["SortExpression"] = sortExpression;
      ViewState["SortDirection"] = sortDirection;
  }

Trigger validator control validation from javascript

2008-10-30T17:51:00.001+05:30

Run aggregate on datatable column

2008-09-18T18:58:00.005+05:30

I had one table with columns "Name" and "Department" which i need to display in a multiline textbox. like below
Name~~~~~~~~ Dept
Tarun Ghosh~~~SWD
Diptangshu Das~SSWD
So that the names and Dept are aligned properly. So i needed the Max of the string length of the column "Name" and give a right-padding to name to make all of same length with the maximum lengthed name.
Firstly i went with the primitive looping through each record and calculate the max of the row, if it is greater than the global max, set global max to current max algo...
But i was not satisfied with the approach even though no problem.
Then i got the

public Object Compute(
 string expression,
 string filter
)

function of DataTable. Expression par needs to be some aggregate function lik Sum, Avg,Min,Max,Count,Var. with filter expression is same as where clause in dt.Select() function.
But one problemmmmmmmm.... The expression parameter can't work on computed column. Like say you have two column qty and unit price. You need to get the max(qty*unitPrice), then first you have to add a new column to hold the value of the multiplied result and then use the compute function on that column.
So in my case i wanted to get the max of length. So i added a column as


           DataColumn col = new DataColumn("Length");
           col.Expression = "Len(Name)";
           col.DataType = typeof(int);
           dt.Columns.Add(col);
           object obj= dt.Compute("Max(Length)","");
           Response.Write(obj.ToString());

obj is storing the max length value
Some useful link
Link1
Link2

Get the date diference in the form of x Years y months z days

2008-09-17T13:03:00.005+05:30

Somethimes we need to get the difference of two dates in SQL server to send the result to front end. The result of DateDiff SQL function will give the difference of the date for a particular part like year or month or day... If you think of just concatinating the results got from three datediff function with parameters for year,month and day, you will get the wrong result. WHY???
DateDiff(Year,'2007/31/12','2008/1/1') is 1 though the difference only 1 day.
Similarly month differnce and day difference will be shown as 1.
So the result age is "1 year 1 month 1 day". But actyually only "1 day".
Here is a function i wrote to get the exact value. This is very useful when you need to get the age on today when you have DOB stored in your database. Here you have to send the DOB as @startdate and GetDate() as @endDate

Create FUNCTION [dbo].[fn_GetDateDifference] (@startDate as DateTime,@endDate DateTime)
RETURNS varchar(50) AS
BEGIN
DECLARE @res varchar(50)
DECLARE @mon int,@year int,@day int
SELECT @year = DateDiff(year,@startDate,@endDate)
--SET @year = @day/365
SET @startDate = DateAdd(Year,@Year,@startDate)
if(@startDate > @endDate) --If the month value of Start Date is more than that of End Date
--@startDate can become more than date 2 if the values are like
--@startDate = '2007/12/15' and @endDate = '2008/02/01',
--The datediff year will be 1 though only 2 month+ difference
BEGIN
SET @year = @year - 1
SET @startDate = DateAdd(Year,-1,@startDate)
END
SELECT @mon = DateDiff(Month,@startDate,@endDate)
--SET @mon = @day/28
--Select @mon = DateDiff(Month,@startDate,@endDate)
SET @startDate = DateAdd(Month,@mon,@startDate)
if(@startDate > @endDate)
--Same as year,month can show maximum of 1 unit more if the start date "Day" val in more than end date "Day value"
BEGIN
set @startDate = DateAdd(Month,-1,@startDate)
set @mon = @mon - 1
END
SELECT @day = DateDiff(Day,@startDate,@endDate)
set @res = ''
If(@year > 0)
SELECT @res = Cast(@year as Varchar(10)) + ' Year(s) '
if( @year>0 or @mon>0)
Set @res = @res + Cast(@mon as Varchar(10)) + ' Month(s) '

set @res = @res + Cast(@day as Varchar(10)) + ' Day(s)'
--set @res = @year & ' '
--select @res = @year & ' ' & @mon & ' ' &@day
RETURN @res
END

Note: It is not a good practice to use backend sqlserver for this kind of calculations. It is preferable to return two dates to front end and then do the desired calculations.

Generate Your Dynamic Report Through Reporting Service

2008-07-14T18:34:00.006+05:30

I have posted some articles on reporting service. Here is a sample project which will generate report dynamically. It is having an interface where you select the database in a DDL then a table or view from the selected DB and select some columns for details and group of the report. Then it will generate the report RDL and publish it to your report server. If you want to select from multiple tables with joining, you can do that also by giving your custom query.
Here are some setting which you will have to change to run the project like
string serverName = "localhost"; //The server where the report will be published.
string reportVirtualPath = "LocalReportServer"; //The virual directory path of the reportserver
string parentFolder = "QuoteReports"; //The folder where the reports will be published.
Here i am using some AppSettings val in web.config. Change those according your local settings. But in "MasterConStr" the 'Initial Catalog' value should be 'master' because this connection string is used for fetching data about DBs.
Add the web reference of the reporting service to your ptoject. You may have to change the "RSLocal.ReportService2005" AppSettings value.
Here is the link of the project http://w17.easy-share.com/1700907813.html
Here i am using sql express repoting service and my report server and database is in dame machine. If you have the Report server installed in other machine than the Database you may have some problem with the DataSource. For that pls refer to my other post Use stored credential for report created by c# code.

Use PageRequestManager To Trigger Javascript for Asynchronous Postback

2008-07-08T22:29:00.006+05:30

In my last post Send Balk Data(Datatable) From Child Window To Parent
i explained how we can send bulk data from child window to parent. But if we are using Asp.Net ajax in client page i.e the postback is caused asynchronously, then we can't use the RegisterStartUpScript to call the javascript function. But instead we have to use the Microsoft's Ajax javascript framework. I did this with the use of PageRequestManager JS Class as follows

var postBackThroughAdd = false;
function addRequestManager()
{
var reqMgr=Sys.WebForms.PageRequestManager.getInstance();
reqMgr.add_beginRequest(
function()
{
//Do something before the call begins
}
)
reqMgr.add_endRequest(
function(sender,args)
{
if (args.get_error() == undefined)//Checks there is no error
{
if(postBackThroughAdd)
{
postBackThroughAdd = false;
addToQuote();
//Function to trigger the parent postback and flag set and close the page
}
}
}
)
}

Variable postBackThroughAdd is a flag variable, which is set "true" when the button for which we want to send the data to parent is clicked. The code is like

<asp:Button ID="btnAddPartNoToCatalog" runat="server" Text="Add" CssClass="formButton"OnCommand="btnAddPartNoToCatalog_Command" OnClientClick="postBackThroughAdd = true;" />

Here if any error occurs while setting the data in session through server event btnAddPartNoToCatalog_Command, i am simply throughing the error with some userfriendly message. The message will be shown in JS message box and args.get_error() will not be undefined. So the function addToquote() will not be called.
Note: addRequestManager() function should be called on bodyload so that the JS events are registered before they fires actully. With the help of PageRequestManager class you can do some other tasks like showing some message in div after some action has succeeded of failed, show/hide loading... div without using UpdateProgress but same functionality(In the beginRequest handler, set the div's "display" property to "block" and int endRequest hander display to "none").

Send Balk Data(Datatable) From Child Window To Parent

2008-07-04T15:21:00.008+05:30

Currently in one of my .aspx page i was selecting some information from a child window which is opened from a parent window. I opened the window like
window.open('PartNoLookup.aspx','PartNo','left=150, top=60,width=800,height=600, toolbar=no, status=1,scrollbars=yes,resizable=no,dependent=yes');
Some PartNos with their information was selected by the child "PartNoLookup.aspx" page. Now i have to send the part nos to the parent page to be displayed. My parent page was NewQuote.aspx.
Now the problem arises if it was a small value then i could do it with
window.opener.document.getelementById('parentCtrlId').value='Some Value'
But as it was a bulk datatable i can't send it like that.
I stored the DataTable in Session["PartNo"] from the child page. And placed a hidden field "hidFlag" (not with runat='server'. this should be a html hiddenfield) in the Parent Page (i.e. NewQuote.aspx). And wrote some javascript with the Page.ClientScript.RegisterStartupScript like
Page.ClientScript.RegisterStartupScript(typeof(string), "", "refreshParentAndClose();", true);
And inside childpage (PartNoLookup.aspx) i added the JS function refreshParentAndClose() as
function refreshParentAndClose()
{
window.opener.document.getelementById('parentCtrlId').value='checksession'
window.opener.document.forms[0].submit();
window.close();
}
So this function will set the flag in the parent a also submit the page.
Here as the page is postback by some unconventional way, only the PageLoad,PreRender and some processing cycle events will fire in the parent. So i have to check for the flag inside tha Load() event. So i added the checking code in Page_LoadCode as
if(Request.Form["hidFlag"]!=null)
{
string flag=Request.Form["hidFlag"];
switch(flag)
{
case "checksession":
//Check the session and show inside the page.
break;
}
}

Note:Put the hidden field as Html hidden, not with runat=server coz, if you set it server variable then change by the javascript will no be visible as it will be restored from the viewstate. y JavaScript we are changing the value not the stored value inside Encripted ViewState. Also if you use server Hidden field the clientid may not be same as server id. So setting it from child page will be problemetic (but not impossible).

Resize and position Javascript popup

2008-06-11T15:48:00.011+05:30

In many of my project i came across the situation where i had to open a popup with a particular size and position the popup in the middle of the window. I was sending the height and width parameter in the window.open() funtion parameter. But many times i had to add some extra controls in that page which needs the increase of the popup size. I was problemetic for me to goback to the parent page and find the javascript which is opening the popup and change.
So i decided to control the size from the popup itself. And i used
window.resizeTo(width,height) function for that purpose and called it on body onLoad event.
But what about positioning the popup?
For both the pubpose to solve i wrote a function resizeAndPositionWindow() which takes height and width as parameter and positions the popup in the middle of the window. Below is the function...

function resizeandPositionWindow(width,height)

{

window.resizeTo(width,height);

var scrW = 1024, scrH = 768;

var popW=width,popH=height;

if (document.all document.layers) {

scrW = screen.availWidth;

scrH = screen.availHeight;

}

else

{

try

{

scrW = window.screen.availWidth;

scrH = screen.availHeight;

}

catch(e){}

}

if(document.all)

{

popW=document.body.clientWidth;

}

else

{

if(document.layers)

{

popW=window.innerWidth;

popH=window.innerHeight;

}

else

{

try

{

popW=window.innerWidth;

popH=window.innerHeight;

}

catch(e){alert('Exception');}

}

var x,y;

x=(scrW-popW)/2-13;

y=(scrH-popH)/2;

window.moveTo(x,y);

}

By default it is assumed that the screen resolution of the user is 1024X768. Then it determines the actual height.
I used the above funtion to make a function which will take a int parameter as gap between the popup and computer screen and will make the popup which will have that length in top,bottom,left and right.
function resizeAccordingToScreenSize(gap)
{
var scrW = 1024, scrH = 768;
if (document.all document.layers) {
scrW = screen.availWidth;
scrH = screen.availHeight;
}
else
{
try
{
scrW = window.screen.availWidth;
scrH = screen.availHeight;
}
catch(e){}
}
var gapPx=0;
try
{
gapPx=parseInt(gap)*2;
}
catch(e){alert("Wrong value for gap.")}
resizeAndPositionWindow(scrW-gapPx,scrH-gapPx);
}

Use stored credential for report created by c# code

2008-06-03T14:29:00.012+05:30

In my current project i have created an interface through which admin can generate the report he wants from any of the table (or combination of tables) from any DB in the server. The interface is generating the xml Report Defination (rdl) and publishing it with the reporting service web service.
Now the problem came with the datasource of the report. When i publish the report it was showing the username and password input box in the report viewer. If i put those it was running beautifulluy. But it was not what i wanted. None of us will bother to give the UID and password everytime. Here is the sample of code i used to generate the report

static int zIndex = 1;
string serverName = "localhost";
string reportVirtualPath = "LocalReportServer";
string parentFolder = "QuoteReports";
string dataSetName = "DSSOP";
string dataSourceName = "DynamicDataSource";
string parTabName = "bodyTable";

void deployReport(string reportName, string reportDefination)// reportDefination is the xml rdl
{
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] byteRDL;
System.Text.UTF8Encoding encoder = new UTF8Encoding();
byteRDL = encoder.GetBytes(reportDefination);
Property[] rsProperty = new Property[10];
//Property property = new Property();
Warning[] warnings;
try
{
warnings = rs.CreateReport(reportName, "/" + parentFolder, true, byteRDL, null);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
tdMessage.InnerHtml = "Exception in publiching report.
" + ex.Message + "";
return;
}
}

I was using report-specific datasource with authentication. My sample datasource section of the RDL is below
<datasource name="Personal">
<?xml:namespace prefix = rd /><rd:datasourceid>1a234378-11f1-4dc0-bc74-91df6d7d94f7</rd:datasourceid>
<connectionproperties>
<dataprovider>SQL</dataprovider>
<connectstring>Data Source=SODC42\SQLEXPRESS;Initial Catalog=SOP;UID=sa;password=123456;</connectstring>
</connectionproperties>
</datasource>

Though i was putting the connection string with UID and password, still it was showing the textboxes for username and password.
After a lot of google i found that reporting service uses two connection strings 1) one for connection to the report server 2) another for connection the report server to the databse (In mycase the both of report server and DB server is same express version in localhost). The connection string supplied in the RDL is for the first purpose and the second connection information is stored with the DataSource information in the ReportServer DB.
Then i open the report with the reportmanager (http://localhost/reports) and edit the report. In the editor i found the datasources link where i saw the "Credentials supplied by the user running the report" radio is selected. I canged the selection to "Credentials stored securely in the report server" and gave the desired credentials. Then i saw the report is not showinf the text boxes.
Now it was clear that the problem is with the DataSource not with the RDL. Then i went with the idea of using SharedDataSource and attach it with the report. While deploying the report i was checking if the shared datasource is already existing. If not then create it. Here is the code for creating the shared datasource.

DataSourceDefinition def = new DataSourceDefinition();
def.CredentialRetrieval = CredentialRetrievalEnum.Store;
def.ConnectString = ReportConnection.GetConnection("SOP").ConnectionString;
def.Enabled = true;
def.EnabledSpecified = true;
def.Extension = "SQL";
def.ImpersonateUser = false;
def.ImpersonateUserSpecified = true;
def.WindowsCredentials = false;
def.UserName = "sa";
def.Password = "123456";
rs.CreateDataSource(dataSourceName, dataSourcePath, true, def, null);
rs.SetDataSourceContents(dataSourcePath + "/" + dataSourceName, def);

Note:Before creating you have to check whether it already exists of not. And also the folder structure.
The rdl for datasource :
<DataSources>
<DataSource Name="DynamicDataSource">
<DataSourceReference>/Data Sources/DynamicDataSource</DataSourceReference>
</DataSource>
</DataSources>
Now the report is running fine.
But still there was something hitting in my mind. Why i shall use SharedDataSource? I shall use report specific datasource. But how?
After some days lots of RnD s i found it. It is totally my assumtion. I have no idea how far it is true. But it works.
The report specific datasources you have to keep in a folder "Data Sources" in the same directory as the report and then only the report can use it. So i created the datasource in the report folder's "Data Sources" folder. And so far it is running.

Download Sample Project
For information about the project refer to my other post Generate Your Dynamic Report Through Reporting Service

Reporting service: Dynamic Toggle Group

2008-05-29T15:41:00.008+05:30

In my current project i have to use Reporting Service 2005 for generating some reports. It was the first time for me to RS. The report was showing some group on Year >> Month >> Day. The report was drill down one. I had to show the current month's days open i.e when the user open the report which is decending sorted with year,month,day will show the report with current mont's days expanded. All other years and month will remain collapsed.
For that i added two parameter to my report through
Report Layout (Designer)-> Report (Menu) -> Report Parameters(sub menu).
The parameters are showYear of type int and showMonth of type int.
Default value for showYear, i selected "non-queried" and in the textbox wrote =Year(Today)
and same for showMonth with TB value =Month(Today) so that the default value is set to the current month and year.
Now i edit the group showing the month and in the visibility tab i set "Initial Visibility" expression to "=IIF(Parameters!showYear.Value=Fields!Year.Value,false,true)", so that if the yeay is equals to current year then it will stay initially visible. And for day i set expression to "=IIF(Parameters!showYear.Value=Fields!Year.Value,IIF(Parameters!showMonth.Value=Fields!Month.Value, false,true),false)", so that if the month and year is euals to current then it will be stay initially visible (i kept the "Visibility can be toogled by another report item" section as it was.).
Now when i preview the report i found everything is running fine. By default it is expanding current year and month and if i put some other value in the report changing textbox it is expanding corresponding record. But the "+" and "-" signgs for toogle for those initially expanded is showing opposite i.e. "+" even when initially it is expanded and "-" when clicked to collapse.
Why this is happening? After searching various properies for the report and its item i got the "InitialToogleState" property of the textbox which by default is collapsed. Then i set this property to expression so that it is "Collapsed" and "Expanded" properly to show "+" and "-" sign respectively.
I set the year textbox's (TB which is responsible for toggle in year group row) InitialToogleState property to "=IIF(Parameters!showYear.Value=Fields!Year.Value,true,false)" and for month's one "=IIF(Parameters!showYear.Value=Fields!Year.Value,IIF(Parameters!showMonth.Value=Fields!Month.Value, true,false),true)" . Then i saw the result was as desired.
We can hide the Parameter Promt in the report viewer by ShowParameterPrompts="False" in the reportviewer control and send the parameter by querystring as 'showYear=2004&showMonth=5' .
Some points to note:
1) "InitialToogleState" property is boolean. "Collapsed" indicates false and "Expanded" true
2) "Initial Visibility" is set to month and day rows where as "InitialToogleState" is set for year and month TB.

One useful link:http://msdn.microsoft.com/en-us/library/aa337391.aspx

XML writing problem.. Encoding Fixing

2008-05-21T19:54:00.004+05:30

MemoryStream ms = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(ms, System.Text.ASCIIEncoding.UTF8);
writer.Indentation = 3;
writer.Formatting = Formatting.Indented;
writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
writer.WriteStartElement("Report");
writer.WriteAttributeString("xmlns", null, "http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition");//Writing namespace
writer.WriteAttributeString("xmlns", "rd", null, "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");//Writing namespace

Or You can use StringBuilder class object like below
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);

Some Handy SQL Server Query

2008-05-01T16:01:00.003+05:30

Get the Information about all the databases available in your SQL Server
EXEC sp_databases
EXEC sp_helpdb
select * from master..sysdatabases
SELECT * FROM sys.databases
SELECT * FROM sys.sysdatabases
EXEC sp_msForEachDB 'PRINT ''?'''
Get the Information about all the tables available in your SQL Server Database
SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 ORDER BY TABLE_SCHEMA, TABLE_NAME
or
exec sp_tables
but with this you have to filter to exclude the table owned by (
TABLE_OWNER) 'sys','INFORMATION_SCHEMA'.
Get the Information about all the tables available in your SQL Server Database
sp_columns @tableName
Select * from Information_Schema.Columns where Table_Name=@tableName

Problem Numeric check in SQL Server

2008-04-16T23:01:00.003+05:30

In my datase i had a table which contains both numeric and characters and the table datatype was Varchar, and i have to find the min of the of the numeric value. In sql server the function ISNUMERIC(expr) was the solution to get rid of the character value.
The ISNUMERIC function determines whether an expression is a valid numeric type. returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. So i used the query like below
Select min(ColName) from demo where ISNUMERIC(ColName)=1
And i was getting my desired value.
But one day it started an exception in my asp.net page. And i could not find the reason.
After a long debugging i found that it was the problem with ISNUMERIC function of sql server. The ISNUMERIC function is returning 1 for some non-numeric character like '-' i.e
Select Isnumeric('-') returns 1 not 0.
And it was returning '-' from the query and error in asp.net page.
Then i tried with casting the result to int as.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1
And in this way the error was solved but the min value was always 0 as Cast('-')=0
Then i identified this abnormal characters as excluded them through the where clause.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1 and eadr not in('+','-','$').
This behaviour occurs for these three charactes('+','-','$') i have found so far.
For more information check this link

XML Encoding Problem In SQL Server

2008-04-09T13:52:00.001+05:30

In a project i was sending an XML file to a SP which was written by someone. It was insering values to a master table and some details table. The SP was taking some TEXT datatype as input for the XML. Now the problem was one day we found some
"XML parsing error: An invalid character was found in text content. sql server xml parse" error and i come to know that some datatype is having some non-ascii characters like "Ö","«" for which the error was generated.
We were told to just avoid throughing error so that the user can continue their work. The fastest solution i came accross was to format the string before puttin into the XML.
So i wrote a function which will take the String as input and return the ASCII string as output as below.
public static string getUTF8String(string str) {
char[] ch = str.ToCharArray();
byte[] by = new byte[ch.Length * 2]; //If all char is UNicode then max byte is 2 X length System.Text.ASCIIEncoding enc = new ASCIIEncoding();
int charUsed, byteUsed;
bool completed;
enc.GetEncoder().Convert(ch, 0, ch.Length, by, 0, by.Length, true, out charUsed, out byteUsed, out completed);
string res=enc.GetString(by, 0, byteUsed);
return res;
}
So the Non-Ascii character may change, but the workflow wsa not hampered.
But the best solution is to change the SP and the xml sent to the SP. In the SP you should make the input parameter as NTEXT rather than TEXT and in the XML mention the encoding as UTF-16 - not UTF-8. In this way it will accept all unicode characters.

Ajax Like File Uploading

2008-04-01T12:37:00.000+05:30

Place a hiddenIFrame into the page and submit the form containing the file uploader (input type='file') to the hidden iframe by changing the target property of the form to the name of
the IFrame. Here i have made the target of form (id='formUpload') to "iframeHidden"
as my iframe name is "ifameHidden". For uploading the file you have to make the'
enctype' and 'method' attributes values as it is in the example.
I have made the action attribute of the page to UploadFile.aspx page. This is a
page where it counts the Files Collection of the Request Object and if it finds
any file in the Collection (Request.Files.Count>0), it saves the file in the same
folder with the same name as file. The saving code is very trivial, for a real world
applicatio you have to be carefull while saving like name overwrite,different path
ect.

<script type="text/javascript">
function uploadFile(id)
{
document.getElementById("formUpload").submit();
}
</script>

<iframe id="iframeHiden1" style="height:0px;width:0px;border:0"
name="iframeHiden" src="UploadFile.aspx"></iframe>

<form id="formUpload" target="iframeHiden" enctype="multipart/form-data"
action="UploadFile.aspx" method="post">

Ajax Upload File In HTML Form:<input type="file" onchange="uploadFile('file1')"
id="file1" name="fileUploadHTML" />

</form>

UploadFile.aspx Page Load Event

protected
void Page_Load(object sender, EventArgs e)

{

int noOfFile = Request.Files.Count;

if (noOfFile > 0)

{

string path = Request.PhysicalPath.Substring(0, Request.PhysicalPath.LastIndexOf("\\")
+ 1) + Request.Files[0].FileName;

Request.Files[0].SaveAs(path);

}
}

How To Keep Session Live Till The Page Stays Open

2008-02-21T22:27:00.000+05:30

One one of my project i was told that the user will keep the browser open and will
talk to their client for long time, which may be 1/2 hour continuous and after that
they will come back and add some item to the page as per the discussion over the
phone. So we have to keep the session live till that period to keep him logged in.
The way to implement this are

1) Add the following code in web.config file

<sessionState timeout="100"></sessionState>

Here timeout is in munites and sessionwill stay live upto 180 min i.e 3 hours
(by default the timeout is 20 mins) which was enough for our purpose.

But with this way of implementation we were misusing the valuable server disk space
for all the session variables will stay for that long time. So even if you close
the browser the objects will stay in the server for next 3 Hours. So we implemented
another way

2) In the Body tag of our aspx page i put a div which is having display style attribute='None'
so that the div does not display in the page.

And then put a IFrame inside the div and set another aspx page (KeepSession.aspx)
as the src of the IFrame. The the page in the IFrame i make it auto refresh after
18 mins. In this way though the session is to be expired in 20 mins (by default),
but the KeepSession.aspx page will be reloaded in every 18 mins and the session
will be refreshed and it will start counting the 20 mins expire time from start.
So in this way it will stay alive for ever. Below in the quote in my NewQuote.aspx

::::::::::::::::::::

<div style="display:none">

<iframe id="redresh" src="RefreshSession.aspx" height="0" width="0">

</iframe>

</div>

<form id="form1" runat="server">

<div>YOUR PAGE CODE HERE </div>

</form>

</body>........

KeepSession.aspx Page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="KeepSession.aspx.cs"
Inherits="KeepSession" %>

<html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Refresh
Session</title>

<meta http-equiv="refresh" content="1048" />



</head>

<body>

<form id="form1" runat="server

form>

</body>

</html>

How to view all triggers

2008-02-06T17:27:00.000+05:30

To view the trigger from a particular database Table
exec sp_helpTrigger @tabname='QTQuotationMaster'

If you want to see all the triggers available in the database then run this
SQL Server 2000:
select * from sysobjects where xtype ='TR'

SQL Server 2005:
Select * from sys.triggers

Using text, ntext Data Type SQL Server

2007-10-24T16:05:00.004+05:30

In sql server 2000 (or before ver) we uses Text,NText data type for storing very long length of string datatype. Text is suitable for non-unicode (1 byte) string and NText for unicode(2 bytes). we can't manipulate these datatype with the regular dmls, but it requires some other way because of it's storing process which is different from other data types (except Image which is stored in the same way for binary data). These datatypes's values are stored outside the row and a 16 bit pointer is tored which points to the root of the internal pointer which points the fragment of the data stored in different pages.
We need to use READTEXT (to read), WRITETEXT ( to replace ) and UPDATETEXT (to modify) these values.
First create a table and insert some values in it
CREATE TABLE TestNTextDataType ( [Id] [int] NULL , [Name] [Ntext] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
insert into TestNTextDataType values(1,'This is a test')
insert into TestNTextDataType values(2,'This is second test')
Declare a pointer to the 'Name' Column as
Declare @ptrToCol varbinary(16)
Select @ptrToCol=TextPtr([Name]) from TestNTextDataType where [Id]=1
The @ptrToCol pointer points to the NText value 'This is a test'.

READTEXT { table.columnName @ptrToCol @offset @size } [ HOLDLOCK ]
Reads values from a column, starting from a specified offset and reading the specified number of bytes.
READTEXT TestNTextDataType.[Name] @ptrToCol 5 2
Result: is
The value of offset is 5 and as it is zero based it points to the 6th character 'i' and the length is 2 so the two characters starting from 'i' is returned. If you pass 0 as the size then 4KB of data is read.
Read More

WRITETEXT { table.columnName @ptrToCol } [ WITH LOG ] { data }
Permits minimally logged, interactive updating of an existing column. WRITETEXT overwrites any existing data in the column it affects
WRITETEXT TestNTextDataType.[Name] @ptrToCol 'Updated Test Final'
It will update the 'This is a test' value to the new value 'Updated Test Final'. Remember that the pointer @ptrToCol points to the [Name] column of the row with the id=1 (Where clause).
Read More

UPDATETEXTUPDATETEXT { table.columnName @ptrToCol } { NULL @insertOffset } { NULL @deleteLength } [ WITH LOG ] [ inserted_data]
Updates an existing field. Use UPDATETEXT to change only a part of a text, ntext, or image column in place.
UPDATETEXT TestNTextDataType.[Name] @ptrToCol 8 4 'New String'
Previous value was 'Updated Test Final' insert offset 8 points to 'T' of Test and the delete length 4 deletes the 'Test' and inserts the value 'New String'. So the result value becomes 'Updated New String Final'.
Read More

DataLength(Expression)
To get the length of the column value for Text,NText and Image datatype you have to use DataLength function.
Select DataLength([Name]) Length from TestNTextDataType where [Id]=1
will return 48 as the length of 'Updated New String Final' string because the datatype is NText of the column so every charter will take 2 byte and there are 24 characters. If the datatype was Text then it will return 24. The DATALENGTH of NULL is NULL.

PATINDEX , SET TEXTSIZE, SUBSTRING, TEXTVALID are use useful functions to work with these datatypes.
Note: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types.