Cursors in Sql server-Basics

We'll talk about the basics of cursors. These let you move through rows one at a time and perform processing on each row. (This article has been updated through SQL Server 2005.)

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row. In the Cursor Performance article I discovered that cursors are over thirty times slower than set based alternatives.

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1
INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.

If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname

You can code your UPDATE statement to update the current row in the cursor like this

UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1

That covers the basics of cursors

Export Gridview data to an Excel sheet

protected void imgExport_Click(object sender, ImageClickEventArgs e)
{
PrepareGridViewForExport(gvAttendance);
string attachment = "attachment; filename=CounsellingDetails.xls ";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
//GridView gv = new GridView();
//gv.DataSource = GetResults();
//gv.DataBind();
gvAttendance.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{

}
private void PrepareGridViewForExport(Control gv)
{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{

if (gv.Controls[i].GetType() == typeof(LinkButton))
{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))
{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(CheckBox))
{

l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())
{

PrepareGridViewForExport(gv.Controls[i]);

}

}

}

Reading a xml file-Basics

Introduction

The main reason for writing this simple article is so many guys are asking doubts how to read Xml ,how to Write Xml in DotnetSpider Questions section. I Thought this article Will helpful for beginners. You can also find an article about how to write Xml document at
http://www.dotnetspider.com/kb/SubmitSample.aspx?ArticleId=2066

System.Xml namespace contains the XmlReader and XmlTextReader.
The XmlTextReader class is derived from XmlReader class. The XmlTextReader class can be used to read the XML documents. The read function of this document reads the document until end of its nodes.
Using the XmlTextReader class you get a forward only stream of XML data j It is then possible to handle each element as you read it without holding the entire DOM in memory.
XmlTextReader provides direct parsing and tokenizing of XML and implements the XML 1.0 specifications

This article explains how to read an Xml file.

Adding NameSpace as Reference

The first step in the process of reading Xml file is to add System.Xml namespace as reference to our project ,since System.Xml namespace contains the XmlReader and XmlTextReader.




using System.Xml;



let us assume there is an Xml file in c:\Dir\XmlExample.Xml

Open an Xml Document

Create an instance of an XmlTextReader object, and populate it with the XML file. Typically, the XmlTextReader class is used if you need to access the XML as raw data without the overhead of a DOM; thus, the XmlTextReader class provides a faster mechanism for reading XML.




XmlTextReader MyReader = new XmlTextReader("c:\\dir\\XmlExample.Xml");



the above code opens Xml file

Reading Data

The Read method of the XmlTextReader class read the data. See the code



while (MyReader.Read())
{
Response.Write(MyReader.Name);
}



that’s all now we are ready to read our Xml file from the above specified directory


Read the XML File into DataSet


You can use the ReadXml method to read XML schema and data into a DataSet. XML data can be read directly from a file, a Stream object, an XmlWriter object, or a TextWriter object.

The code simply looks like the following



string MyXmlFile = @"c:\\Dir\\XmlExample2.xml";
DataSet ds = new DataSet();

System.IO.FileStream MyReadXml = new System.IO.FileStream(MyXmlFile,System.IO.FileMode.Open);
ds.ReadXml(MyReadXml);

DataGrid1.DataSource = ds;
DataGrid1.DataBind();




Thus we can read Xml Data into Dataset .

Paging in a DataList or Repeater control

Introduction
DataList is a data bound list control that displays items using certain templates defined at the design time. The content of the DataList control is manipulated by using templates sections such as AlternatingItemTemplate, EditItemTemplate, FooterTemplate, HeaderTemplate, ItemTemplate, SelectedItemTemplate and SeparatorTemplate. Each of these sections has its own characteristics to be defined but at a very minimum, the ItemTemplate needs to be defined to display the items in the DataList control. Other sections can be used to provide additional look and feel to the DataList control.

PagedDataSource, is a class that encapsulates the paging related properties for data-bound controls such as DataGrid, GridView, DataList, DetailsView and FormView that allow it to perform paging. And this article is going to combine both DataList control and PagedDataSource class to explain dynamic or custom paging methods for Asp.Net developers.


For demonstration, we are going to list out all the countries in the Country Table. We are going to create a dynamic paging control for this list and define the page size at run-time from a DropDownList control and enrich the navigation with Next and Previous buttons.

Do the Basics Right

Since everything comes with Ajax, create an Ajax Enabled Website, in your Visual Studio 2005. In your Default.aspx page, drag and drop a DataList control from the Toolbox, named it as dlCountry. Our country Table contains two fields such as Country_Code And Country_Name. Right click on the dlCountry datalist, choose Edit Templates > Item Template. Add two Label controls into it; bind its Text property to Country_Code and Country_Name respectively.









Now switch to the Code-behind Default.aspx.cs, write a method to fetch data from the Country’s table.

private void BindGrid()
{
string sql = "Select * from Country Order By Country_Name";
SqlDataAdapter da = new SqlDataAdapter(sql, “Yourconnectionstring”);
DataTable dt = new DataTable();
da.Fill(dt);

dlCountry.DataSource = dt;
dlCountry.DataBind();
}


The above BindGrid method, fetches data from the Country table, bind it with the dlCountry datalist. So the dlCountry datalist is ready to display the records available in the Country table. Call this BindGrid method in the Page load event.

Click Here
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}


Save everything you have done and press F5. You can see the browser opening this page and dispalys all the records in the country table. No paging available at this level.
Let us focus On Paging


To create dynamic paging, we are going to use PagedDataSource Class along with another DataList control. Drag and drop the second DataList control into the page, name it as dlPaging. In its ItemTemplates section, add a LinkButton name it as lnkbtnPaging. Set its CommandName property as lnkbtnPaging. Then choose End Template Editing. Drag and drop a DropdownList Control, name it as ddlPageSize, add values such as 10, 20 and 30 or something as you wish. Then add two more LinkButton controls. Name it as lnkbtnPrevious and lnkbtnNext and change its Text property as Previous and Next respectively. Place both the Previous and Next link buttons on both sides of the dlPaging datalist. Better I suggest you to put an Html Table control and place these controls in it properly so that it will look nicely aligned.


Now we will work little bit on the code-behind of the Default.aspx. Declare a PagedDataSource object at page scope.


PagedDataSource pds = new PagedDataSource();


Then declare a public property called CurrentPage to maintain the latest selected page index. Selected page index is stored in a ViewState variable. The default value of the ViewState is 0.


public int CurrentPage
{

get
{
if (this.ViewState["CurrentPage"] == null)
return 0;
else
return Convert.ToInt16(this.ViewState["CurrentPage"].ToString());
}

set
{
this.ViewState["CurrentPage"] = value;
}

}


Next write a method ‘doPaging’ to create a list of page numbers. The total number of pages can be taken from the PagedDataSource object’s PageCount property. We create a DataTable from the information obtained from the PagedDataSource object and assign the DataTable to the dlPaging datalist.
private void doPaging()
{
DataTable dt = new DataTable();
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageText");
for (int i = 0; i < pds.PageCount; i++)
{
DataRow dr = dt.NewRow();
dr[0] = i;
dr[1] = i + 1;
dt.Rows.Add(dr);
}

dlPaging.DataSource = dt;
dlPaging.DataBind();
}

Look closely, at the doPaging method. Notice the two columns such as PageIndex and PageText. Even though it is understandable, PageIndex is the selected index value of the pages while PageText is the display value in the dlPaging Now we have to bind PageIndex and PageText into the dlPaging’s lnkbtnPaging link button. So set the CommandArgument and Text property of the lnkbtnPaging to PageIndex and PageText respectively. So the entire dlPaging datalist control’s Html source code will look like follows.







Modify BindGrid Method

The next step is to combine both the Country listing datalist and paging number datalist controls. To start this, we are going to modify the BindGrid() method slightly.


private void BindGrid()
{
string sql = "Select * from Country Order By Country_Name";
SqlDataAdapter da = new SqlDataAdapter(sql, “Yourconnectionstring”);
DataTable dt = new DataTable();
da.Fill(dt);

pds.DataSource = dt.DefaultView;
pds.AllowPaging = true;
pds.PageSize = Convert.ToInt16(ddlPageSize.SelectedValue);
pds.CurrentPageIndex = CurrentPage;
lnkbtnNext.Enabled = !pds.IsLastPage;
lnkbtnPrevious.Enabled = !pds.IsFirstPage;

dlCountry.DataSource = pds;
dlCountry.DataBind();

doPaging();
}


In the above method, as usual we fetch data from the Country table and filled it in a DataTable. Then we assign it to the DataSource of the PagedDataSource object pds. Set its AllowPaging property to true and its PageSize with the value from the ddlPageSize DropDownList control. CurrentPageIndex is assigned with our pre-defined property CurrentPage. When the page index reaches first and last page, our Previous and Next button should be disabled. So in the next two lines we check the pds whether it reaches Last or First page, accordingly we set their Enabled property. Now everything is ready to assign the pds to our Country Listing DataList. Last but not least, we are calling the doPaging method to create page numbers.




Paging Events

In the ItemCommand event of the dlPaging control, write code for the paging to take place. Here we take the PageIndex value from the CommandArgument of the lnkbtnPaging and assign it to the CurrentPage property. On every event, we call the BindGrid method.


protected void dlPaging_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName.Equals("lnkbtnPaging"))
{
CurrentPage = Convert.ToInt16(e.CommandArgument.ToString());
BindGrid();
}
}

Previous And Next Button Events


In the Click events of the lnkbtnPrevious and lnkbtnNext, write a line of code that decrease and increase the value of CurrentPage property.


protected void lnkbtnPrevious_Click(object sender, EventArgs e)
{
CurrentPage -= 1;
BindGrid();
}

protected void lnkbtnNext_Click(object sender, EventArgs e)
{
CurrentPage += 1;
BindGrid();
}


Change PageSize Dynamically


To change the PageSize of the PagedDataSource dynamically, we have to call the BindGrid method again because we have already assign the value of the ddlPageSize to the PageSize property of the PagedDataSource.
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
CurrentPage = 0;
BindGrid();
}

HighLight Selected Page Numbers

To inform the user about the selected page, we have to highlight the Page Number in different style. This can be achived by using the following code in ItemDataBound event of the dlPaging datalist.


protected void dlPaging_ItemDataBound(object sender, DataListItemEventArgs e)
{
LinkButton lnkbtnPage = (LinkButton)e.Item.FindControl("lnkbtnPaging");
if (lnkbtnPage.CommandArgument.ToString() == CurrentPage.ToString())
{
lnkbtnPage.Enabled = false;
lnkbtnPage.Font.Bold = true;
}
}


That’s it. Save everything and hit F5. You can see first DataList will display first 10 Records from the Country Table. The dlPaging control will populate the list of page numbers. Previous button will be in disabled state while Next button is active. Click on any page number, Page will navigate for the Page you choosed. Try by clicking Next and Previous buttons, then Change the ddlPageSize value.

To enable Ajax to this page, add the ScriptManager and include all the control inside an UpdatePanel. Its done.

Binding Data With ‘TreeView’ Control

Binding Data With �TreeView� Control Asp.net 2.0

Over the period of few weeks I have been trying to play with asp.net 2.0 controls. As most of asp.net 2.0 beginners know there are almost no working examples and tutorials available for beta release and honestly I haven�t found people working on asp.net 2.0 so far as much as I expected.

Now the real thing started when I decided to pick up some pace and started using Microsoft visual studio 2005 asp.net 2.0 instead of VS�s prior version.Among so many new tools the one which I found really fascinating in asp.net 2.0 is TreeView control, the reason is; I have been creating TreeViews on JavaScript and had really tough time integrating them with server requests/responses.

Introduction of TreeView Control

A tree view control displays a hierarchical list of items using lines to connect related items in a hierarchy. Each item consists of a label and an optional bitmap. Windows Explorer uses a tree view control to display directories. It displays a tree structure, or menu, that users can traverse to get to different pages in your site. A node that contains child nodes can be expanded or collapsed by clicking on it.

You can use ASP.NET site Navigation features to provide a consistent way for users to navigate your site. A simple solution is to include hyperlinks that allow users to jump to other pages, presenting the hyperlinks in a list or a navigation menu. However, as your site grows, and as you move pages around in the site, it quickly becomes difficult to manage all of the links.

To create a consistent, easily managed navigation solution for your site, you can use ASP.NET site navigation TreeView control.

Fig: 1.0

Note: you can manually populate this control by,
1) Clicking on TreeView control.
2) Right click � Edit Nodes
3) Under �Nodes� heading there are two buttons, to add Parent and child nodes respectively.

Lets do some work now!

Now that we have pretty clear understanding of TreeView control, we will quickly move on and integrate this control with our site.

Step 1

Create two database tables ParentTable and ChildTable, having 2 columns each.

ParentTable � [ParentName , ParentId (as PK)]
ChildTable � [ChildName , ParentId (as FK)]

Note: you can use any database, in our example we will use Microsoft SQL server.

Fig. 2.0

Step 2

Now that we have created our tables, open Microsoft Visual Studio 2005 and create and Asp.net 2.0 WebForm.

Note: You can do this by clicking File menu and then New web site.
While creating the new page do not uncheck Place code in separate file checkbox.

Step 3

Add the following LOC (Line of code) at the start of your programs along with other using keywords.

using System.Data.SqlClient;

Step 4

Place a TreeView control on your WebForm.

Note: Do not change its name, for simplicity we will use the default name TreeView1.

Now double click your page, point to Page_Load event and write fill_Tree();

protected void Page_Load(object sender, EventArgs e)
{
fill_Tree();
}

Do not worry, we will use this function in next step.

Step 5

Now the real thing starts. In this step we will populate our TreeView1 control using our two tables ParentTable and ChildTable.

Create a function fill_Tree()

void fill_Tree()

{
/*
* Fill the treeview control Root Nodes From Parent Table
* and child nodes from ChildTables
*/

/*
* Create an SQL Connection to connect to you our database
*/

SqlConnection SqlCon = new SqlConnection("server=D_hameed;uid=sa;pwd=airforce;database=test");

SqlCon.Open();

/*
* Query the database
*/

SqlCommand SqlCmd = new SqlCommand("Select * from ParentTable",SqlCon);

/*
*Define and Populate the SQL DataReader
*/

SqlDataReader Sdr = SqlCmd.ExecuteReader();

/*
* Dispose the SQL Command to release resources
*/

SqlCmd.Dispose ();

/*
* Initialize the string ParentNode.
* We are going to populate this string array with our ParentTable Records
* and then we will use this string array to populate our TreeView1 Control with parent records
*/

string[,] ParentNode = new string[100, 2];

/*
* Initialize an int variable from string array index
*/

int count = 0;

/*
* Now populate the string array using our SQL Datareader Sdr.

* Please Correct Code Formatting if you are pasting this code in your application.
*/

while (Sdr.Read())
{

ParentNode[count, 0] = Sdr.GetValue(Sdr.GetOrdinal("ParentID")).ToString();
ParentNode[count++, 1] = Sdr.GetValue(Sdr.GetOrdinal("ParentName")).ToString();

}

/*
* Close the SQL datareader to release resources
*/

Sdr.Close();

/*
* Now once the array is filled with [Parentid,ParentName]
* start a loop to find its child module.
* We will use the same [count] variable to loop through ChildTable
* to find out the number of child associated with ParentTable.
*/

for (int loop = 0; loop < count; loop++)

{

/*
* First create a TreeView1 node with ParentName and than
* add ChildName to that node
*/

TreeNode root = new TreeNode();
root.Text = ParentNode[loop, 1];
root.Target = "_blank";

/*
* Give the url of your page
*/

root.NavigateUrl = "mypage.aspx";

/*
* Now that we have [ParentId] in our array we can find out child modules

* Please Correct Code Formatting if you are pasting this code in your application.

*/

SqlCommand Module_SqlCmd = new SqlCommand("Select * from ChildTable where ParentId =" + ParentNode[loop, 0], SqlCon);

SqlDataReader Module_Sdr = Module_SqlCmd.ExecuteReader();

while (Module_Sdr.Read())
{

// Add children module to the root node

TreeNode child = new TreeNode();

child.Text = Module_Sdr.GetValue(Module_Sdr.GetOrdinal("ChildName")).ToString();

child.Target = "_blank";

child.NavigateUrl = "your_page_Url.aspx";

root.ChildNodes.Add(child);

}

Module_Sdr.Close();

// Add root node to TreeView
TreeView1.Nodes.Add(root);

}

/*
* By Default, when you populate TreeView Control programmatically, it expends all nodes.
*/
TreeView1.CollapseAll();
SqlCon.Close();

}

Lets see how it looks like!

Fig. 3.0

displaying xml with xslt

Displaying XML with XSLT

XSLT is the recommended style sheet language of XML.

XSLT (eXtensible Stylesheet Language Transformations) is far more sophisticated than CSS.

XSLT can be used to transform XML into HTML, before it is displayed by a browser:

Display XML with XSLT

If you want to learn more about XSLT, find our XSLT tutorial on our homepage.
Transforming XML with XSLT on the Server

In the example above, the XSLT transformation is done by the browser, when the browser reads the XML file.

Different browsers may produce different result when transforming XML with XSLT. To reduce this problem the XSLT transformation can be done on the server.

View the result.

Note that the result of the output is exactly the same, either the transformation is done by the web server or by the web browser.

xml tree example

XML Tree
« Previous Next Chapter »

XML documents form a tree structure that starts at "the root" and branches to "the leaves".
An Example XML Document

XML documents use a self-describing and simple syntax:


Tove
Jani
Reminder
Don't forget me this weekend!


The first line is the XML declaration. It defines the XML version (1.0) and the encoding used (ISO-8859-1 = Latin-1/West European character set).

The next line describes the root element of the document (like saying: "this document is a note"):


The next 4 lines describe 4 child elements of the root (to, from, heading, and body):
Tove
Jani
Reminder
Don't forget me this weekend!

And finally the last line defines the end of the root element:


You can assume, from this example, that the XML document contains a note to Tove from Jani.

Don't you agree that XML is pretty self-descriptive?
XML Documents Form a Tree Structure

XML documents must contain a root element. This element is "the parent" of all other elements.

The elements in an XML document form a document tree. The tree starts at the root and branches to the lowest level of the tree.

All elements can have sub elements (child elements):


.....



The terms parent, child, and sibling are used to describe the relationships between elements. Parent elements have children. Children on the same level are called siblings (brothers or sisters).

All elements can have text content and attributes (just like in HTML).
Example:
DOM node tree

The image above represents one book in the XML below:



Giada De Laurentiis
2005
30.00



J K. Rowling
2005
29.99



Erik T. Ray
2003
39.95



The root element in the example is . All elements in the document are contained within .

The element has 4 children: