Certain times we need to export our grid data or our custom query data to xml format.
.Net provides a very easy way to that , The two data object i.e. Dataset and DataTable has built-in overloads to do this.
public void WriteSQLToXML()
using (SqlConnection con = new SqlConnection(“server=your_sql_server_name;Database=your_db_name;Integrated Security=true;”))
if (con.State == ConnectionState.Closed)
using (SqlCommand cm = new SqlCommand(“Select * from your_table_name” , con))
using (SqlDataAdapter DA = new SqlDataAdapter(cm))
DataSet DS = new DataSet(“mytable”);
string _path = Application.StartupPath + “myData.xml”;
string _path = Server.MapPath(“”) + “\\myData.xml”;
//it will save myData.xml to your local root directory of your application
While working with sql queries , sometimes we need to sort result set and find out the top 10 products , prices etc . we use order by clause to sort the results either ascending or descending.
But what if someone told u to find out the top 10 records and display their ranks too besides.
Here is one demonstration of ranking query . SQL Server provides a special function rank() over that can solve the problem very easily and its is very useful .
Syntax : RANK () OVER ( < order_by_clause > )
For example if in sample DB “Adventure work” we want to find top 10 products according to unit price then we can use the query as
Select Distinct ProductID, UnitPrice , LineTotal, ReceivedQty,RANK() OVER (order by UnitPrice) as [Unit Price Rank]
Where ProductID between 800 and 900
order by [Unit Price Rank]
It will return all products sorted alongwith their rank like