Explore topic-wise InterviewSolutions in .

To customize paging in GridView we have to create a procedure which return the the EXACT result that we have set in GridView pagesize only we have to pass two parameter one is pageindex and SECOND is pagesize

alter PROCEDURE GridviewOrdersPaged ( PageIndex int, PageSize int )
AS
BEGIN
DECLARE PageLowerBound int
DECLARE PageUpperBound int
DECLARE RowsToReturn int
-- First of all set the rowcount
SET RowsToReturn = PageSize * (PageIndex + 1)
SET ROWCOUNT RowsToReturn
-- Set the page bounds
SET PageLowerBound = PageSize * PageIndex
SET PageUpperBound = PageLowerBound + PageSize + 1
-- Create a TEMP table to store the select results
CREATE TABLE #PageIndex ( IndexId int IDENTITY (1, 1) NOT NULL, OrderID int )
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID) SELECT col_id FROM tbl_name ORDER BY id DESC
-- Return total count
SELECT COUNT(col_id) FROM tbl_name
-- Return paged results
SELECT O.* FROM tbl_name O, #PageIndex PageIndex WHERE O.col_id = PageIndex.OrderID AND PageIndex.IndexID > PageLowerBound AND PageIndex.IndexID < PageUpperBound ORDER BY PageIndex.IndexID
END

What DataReader class do in ADO.NET
To get read-only and FORWARD only ACCESS to data we use DataReader .The DataReader object reduces the SYSTEM overhead because one ROW at a time is taken into memory so it is quite lightweight. To get second object connection is reconnected. We can create a DataReader object by EXECUTE Execute reader() method. There are two Data Reader class one is SqlDataReader and other is OleDbDataReader.

Below code is for GridView

string attachment = "attachment;FILENAME=Contacts.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter SW = NEW StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();


Below Code is for DataGrid

Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

How to Clear or BLANK the GRIDVIEW data?
Below are the DIFFERENT METHODS to clear or blank Gridview with very less line of CODE
(1)First Method
GridView1.DataSource = null;
GridView1.DataBind();

(2)Second Method
GridView1.Rows.Clear();
GridView1.Refresh();

(3)Third Method
dataGridView1.Rows.Clear()
dataGridView1.DataBind();

(4)Fourth Method
dataGridView.DataSource=null;
dataGridView.Rows.Clear();

To set NAME of table in DATASET first of all we have to bind the dataset with the database after this use Below syntax
ds.Tables[0].TableName = "tablename1";
ds.Tables[1].TableName = "tablename2";

Below code will help us to get the DISTINCT RECORDS from datatset tables


STRING[] TobeDistinct = { "Column1", "COLUMN2", "column3",........."columnn"};
DataTable dtDistinct = GetDistinctRecords(datatable, TobeDistinct);

PUBLIC static DataTable GetDistinctRecords(DataTable dt, string[] Columns)
{
DataTable dtUniqRecords = new DataTable();
dtUniqRecords = dt.DefaultView.ToTable(true, Columns);
return dtUniqRecords;
}

Below are the three methods to gernate xml by dataset OBJECTS:-
(1)ReadXML:-To READ a XML document in to Dataset.
(2)GetXML:-This is functions which returns a STRING containing XML document.
(3)WriteXML:-This WRITES a XML data to disk.

When we are talking about application.Database connection is most important and resource buring task. Opening database connection is time consuming and may be verys slow task.Most of the applications need to execute any query on the database server, a connection need to be established. Connection pooling increases the performance of the applications by reusing the active database connections instead of create new connection for every request.
Connection Pooling is the ability to reuse the database connection for more than ONE user. That connection might be SQL, OLEDB, ORACLE or whatever. This way of organizing connections in a SMARTER manner improves performance as the applications do not need to open and CLOSE the connection multiple times.
Connection pooling Behavior is controlled by the connection string parameters.
(1)Connection Lifetime - The time of Connection Creation will be compared to the current time. If this period exceeds the Connection Lifetime value that is set, then object pooler destroys the connection. The default value is 0; this will give the maximum timeout for connection.
(2)Connection Reset - To reset the connection after it was take out from the Connection pool. The default value is true.
(3)Max pool size - Maximum number of connections allowed WITHIN the Connection pool. The value is 100 by default.
(4)Min pool size - Minimum number of connections allowed within the Connection pool. The value is 0 by default.
(5)Pooling - To set the connection Pooling if it is true, the connection is drawn from the pool or created if no connection available from the pool. The value is true by default.
(6)Connection Timeout - Maximum Time (in secs) to wait for a free connection from the pool. The value is 15 by default.
(7)Incr Pool Size - Controls the number of connections which are established, when all the connections are used. The value is 5 by default
(8)DECR Pool Size - Controls the number of connections which are closed when most of the established connections are unused. The value is 1 by default

Below code helps us to do SORTING of data in dataset after fill data from database to dataset
// FIRST we decalre dataset
DataSet ds=new DataSet();
//function to fill dataset
ds=objclass.filldatasetfunction();
//now declare dataset
DataView dtView = ds.Tables[0].DefaultView;
//apply sorting to datatable
dtView.Sort = "fname ASC";
//bind datatable to datagrid
DataGrid1.DataSource=dtView;
DataGrid1.DataBind();

Define different execute METHODS of ADO.NET Command Object
Below are the 4 execute methods of ADO.NET Command object

(1)ExecuteScalar:- This METHOD returns a single value from the FIRST row and first column of the result get from the execution of SQL QUERY.

(2)ExecuteNonQuery:- This method executes the DML SQL query just like insert, delete or update and then returns the NUMBER of rows affected by the action.

(3)ExecuteReader:- This method returns DataReader object which is a forward-only resultset.

(4)ExecuteXMLReader:- This method is available for SQL Server 2000 or later. Upon execution it builds XMLReader object from standard SQL

Getting Error DataControlRowType does not found in dotnet GridView?
Getting this error because Namespace is not DEFINE to access DataControlRowType Enum. So NEED to define System.Web.UI.WebControls Namespace. After that write below code

Using System.Web.UI.WebControls
Using System.Web.UI.HtmlControls
...........
...........
...........

protected void GrdTask_RowDataBound(object SENDER, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
}
}

To REMOVE this error we use below code and it will simply remove the error

StringReader SR = NEW StringReader(XML);
DataSet ds = new DataSet();
ds.ReadXml(sr);