Paging Procedure.

I was need to do the paging at run time and get the page specific data.
Below is the procedure for the same i found while surfing the net and i belive this is a grat findings.

CREATE PROCEDURE [dbo].[utilPAGE]
@datasrc nvarchar(200)
,@orderBy nvarchar(200)
,@fieldlist nvarchar(200) = '*'
,@filter nvarchar(200) = ''
,@pageNum int = 1
,@pageSize int = NULL
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max) -- SQL to execute
,@recct int -- total # of records (for GridView paging interface)

IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @fieldlist +
'FROM ' + @datasrc +
'WHERE ' + @filter +
'ORDER BY ' + @orderBy
EXEC (@STMT) -- return requested records
END ELSE BEGIN
SET @STMT = 'SELECT @recct = COUNT(*)
FROM ' + @datasrc + '
WHERE ' + @filter
EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
SELECT @recct AS recct -- return the total # of records

DECLARE
@lbound int,
@ubound int

SET @pageNum = ABS(@pageNum)
SET @pageSize = ABS(@pageSize)
IF @pageNum < 1 SET @pageNum = 1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@pageNum - 1) * @pageSize)
SET @ubound = @lbound + @pageSize + 1
IF @lbound >= @recct BEGIN
SET @ubound = @recct + 1
SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if -- no records would be on the
-- specified page
END
SET @STMT = 'SELECT ' + @fieldlist + '
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
FROM ' + @datasrc + '
WHERE ' + @filter + '
) AS tbl
WHERE
row > ' + CONVERT(varchar(9), @lbound) + ' AND
row < ' + CONVERT(varchar(9), @ubound)
EXEC (@STMT) -- return requested records
END

utilPAGE 'TableName', 'OrderBy', '*', '', 4, 10

@datasrc
- the table (or stored procedure, etc.) name
@orderBy
- the ORDER BY clause
@fieldlis
- the fields to return (including calculated ex-pressions)
@filter
- the WHERE clause
@pageNum
- the page to return (must be greater than or equal to one)
@pageSize
- the number of records per page

Way to bind Command Arguments Value for dynamic value in Html view (aspx page).

Below is the way to bind the CityID and Sname to command arguments.

CommandArgument='<%#Eval("CityID") + ";" +Eval("Sname")%>'

Javascript validation in Grid

In the row Data Bound event of the grid User has to find the Update control which will normally be in the first cell of the every grid row.Then after finding the control user has to add the attributes to the link button of the grid row.

if (e.Row.Cells[0].HasControls())
{
for (int i = 0; i < e.Row.Cells[0].Controls.Count; i++)
{
if (e.Row.Cells[0].Controls[i].ToString() == "System.Web.UI.WebControls.DataControlLinkButton")
{
LinkButton lnkbtnUpdate = (LinkButton)e.Row.Cells[0].Controls[i];
if (lnkbtnUpdate.Text.ToUpper() == "UPDATE")
{
lnkbtnUpdate.Attributes.Add("onclick", "return onUpdateValidation('" + e.Row.FindControl("txtName").ClientID + "');");
break;
}
if (lnkbtnUpdate.Text.ToUpper() == "EDIT")
{
break;
}
}
}
}

Example to filter the DataTable

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace Utility
{
public class FilterDataTableForInQuery
{
public DataTable FilterDataTable(DataTable dtLocal, string value)
{
dtLocal.DefaultView.RowStateFilter = DataViewRowState.OriginalRows;
if (value != string.Empty)
{
dtLocal.DefaultView.RowFilter = "ID in( " + value + ")";
return dtLocal.DefaultView.ToTable();
}
else
{
return null;
}
}
}
}

Code to send the e-mail from .NET Code

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Mail;

namespace Mail
{
public class SendMail
{
public void Mail(string strFrom,string strTo,string strBody,string strSubject)
{
MailMessage objMailmsg = new MailMessage();
objMailmsg.To = strTo;
objMailmsg.From = strFrom;
objMailmsg.BodyFormat = MailFormat.Html;
objMailmsg.Subject = strSubject;
objMailmsg.Body = strBody;
SmtpMail.SmtpServer = "";
SmtpMail.Send(objMailmsg);
}
}
}

Transfering file from server to client.

Below code will give user option to
* User can download the file from the server and save it by choosing the save option.
* User can open the file.
* User can cancel the all operation.

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
///

/// Summary description for TransmitFile
///

public class TransmitFile
{
public TransmitFile()
{
}
FileDownloadHelper objFileDownloadHelper = new FileDownloadHelper();
public void Transmit(string filePath,string FileName,string FileID)
{
FileInfo myfile = new FileInfo(filePath);
if (myfile.Exists)
{
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + "(" + FileID + ").doc");
HttpContext.Current.Response.AddHeader("Content-Length", myfile.Length.ToString());
HttpContext.Current.Response.ContentType = objFileDownloadHelper.ReturnExtension(myfile.Extension.ToLower());
HttpContext.Current.Response.TransmitFile(myfile.FullName);
HttpContext.Current.Response.End();
}
}
}

Handling Special Case Like & and > while Genrating Xml

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.Specialized;
namespace Utility
{
class GenrateXML
{
public static string Genrate(string Screen, NameValueCollection nvCollection)
{
StringBuilder strXML=new StringBuilder();
strXML.Append("");

strXML.Append("<");
strXML.Append(Screen);
strXML.Append(">");

for (int i = 0; i < nvCollection.Count; i++)
{
strXML.Append("<");
strXML.Append(nvCollection.GetKey(i));
strXML.Append(">");

string strValue = nvCollection.Get(i).Replace("&", "&");
strValue = strValue.Replace("<", "<");
strValue = strValue.Replace(">", ">");

strXML.Append(strValue);

strXML.Append(" strXML.Append(nvCollection.GetKey(i));
strXML.Append(">");
}
strXML.Append(" strXML.Append(Screen);
strXML.Append(">");
strXML.Append("");
//strXML.Replace("&", "&");
return strXML.ToString();
}
}
}

Setting the focus on Open form.

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.Regularex-pressions;
using System.Windows.Forms;
namespace Utility
{
class Validations
{
//Open Form
public static void OpenForm(Form objForm,MDIMainForm _MAIN)
{
bool isOpened = false;
for (int I = 0; I < Application.OpenForms.Count; I++)
{
if (objForm.GetType().Equals(Application.OpenForms[I].GetType()))
{
isOpened = true;
Application.OpenForms[I].Focus();
objForm = null;
break;
}
}
if (!(isOpened))
{
objForm.MdiParent = _MAIN;
objForm.Show();
}
}
}
}