XLINQ:

XLINQ:

LINQ let us to read from varied data-store, xml is one of them. Let’s find out how we can read from an xml file using LINQ.Using LINQ with xml is often referred as XLINQ.
Here we need an xml file we add one into our web-project under XML folder call it Movie.xml
Inside this file I am adding a simple xml data,simple in sense that we don’t have an attributes with this tags.

XML file:


<?xml version="1.0" encoding="utf-8" ?>
<Movies>
<Movie>
<Title>DCH</Title>
<Director>Farahan Akhtar</Director>
<Genere>Love Story</Genere>
<Rating>3</Rating>
<ReleaseOn>2008-02-03</ReleaseOn>
<ReelSize>2</ReelSize>
</Movie>
<Movie>
<Title>RHTDM</Title>
<Director>Sanket Pawar</Director>
<Genere>Love Story</Genere>
<Rating>3</Rating>
<ReleaseOn>2009-04-06</ReleaseOn>
<ReelSize>3</ReelSize>
</Movie>
<Movie>
<Title>K3G</Title>
<Director>Karan Johar</Director>
<Genere>Love Story</Genere>
<Rating>3</Rating>
<ReleaseOn>2010-11-16</ReleaseOn>
<ReelSize>3</ReelSize>
</Movie>
<Movie>
<Title>Kites</Title>
<Director>Rakesh Roshan</Director>
<Genere>Love Story</Genere>
<Rating>2</Rating>
<ReleaseOn>2008-10-11</ReleaseOn>
<ReelSize>2</ReelSize>
</Movie>
</Movies>

Now we have this xml file,we add a gridview to our page say gridview1.
In page load of this page add






Page_load code:
var query = from m in XElement.Load(MapPath("XML/Movie.xml")).Elements("Movie")
select new Movie { Title = (string)m.Element("Title"),
Director = (string)m.Element("Director"),
Genere = (string)m.Element("Genere"),
Rating = (int)m.Element("Rating"),
ReelSize = (int)m.Element("ReelSize"),
ReleaseOn = (DateTime)m.Element("ReleaseOn")
};
this.GridView1.DataSource = query;
this.GridView1.DataBind();

Here we need to have Movie class else we will not compiler hurdle.
Lets have in same namespace

Movie class code:

public class Movie
{
public string Title { get; set; }
public string Director { get; set; }
public string Genere { get; set; }
public int Rating { get; set; }
public int ReelSize { get; set; }
public DateTime ReleaseOn { get; set; }
}

Nothing great is done just we added properties to map elemnet in our xml.

Run the code to find that datagrid is populated as required.

In next article we will go for more complex xml.

Using SQL bulk copy

Using SQL bulk copy

First we add a connection string to web-config so that we can reuse it.

<connectionstrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer"
connectionString="Integrated Security=SSPI;
Initial Catalog=NorthWind;Server=(local)"
providerName="System.Data.SqlClient"/>
</connectionStrings>


Now we will demonstrate simple use of SqlBulkCopy class to copy from source table to destination table here both table have same structure though constraint on both may be different.
I am first creating such duplicate table for product in northwind database by running following query
Query:
select * into product_backup from products where 1=2
*** Please a point to see this newly created table(product_backup) is empty

Now to page load add following code
Code-1:
/*reads connection string from web.config*/
string connStr = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

/*create a sqlconnetion object for source table*/
SqlConnection sourceConnection = new SqlConnection(connStr);
SqlCommand myCommand =new SqlCommand("SELECT * FROM Products", sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();

SqlConnection destinationConnection = new SqlConnection(connStr);

destinationConnection.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString);

/*After BatchSize is reached the rows in the batch are
* sent to the server for insert*/
bulkCopy.BatchSize = 500;

/*calls OnSqlRowsCopied after copying of each 10 rows*/
bulkCopy.NotifyAfter = 10;
/*specifies event handler to be called when NotifyAfter amount of row are copied*/
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

/*specify destination table name*/
bulkCopy.DestinationTableName = "product_backup";

/*calls the actual writing*/
bulkCopy.WriteToServer(reader);

bulkCopy.Close();
destinationConnection.Close();
sourceConnection.Close();


Further into class add event handler code bellow

Code-2:

private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
/*will abort the operation of bulk copy*/
//e.Abort = true;

Response.Write(e.RowsCopied.ToString()+",");
}

Explination:
Run above code and trace ececution using breakpoint.you will see
that after each 10 rows are inserted the event “OnSqlRowsCopied”
get fired.Inside this command you can abort it.
1)bulkCopy.BatchSize signifies that this much number of rows
inserted as single transcation.it’s default value is 0.i.e.
that each WriteToServer operation is a single batch.
2)bulkCopy.NotifyAfter this signifies that after batch of this
much rows get inserted OnSqlRowsCopied event get fired
3)bulkCopy.WriteToServer this method Copies all rows from a source
table(product) to a destination table(product_backup) specified by the
DestinationTableName property of the SqlBulkCopy object.
This method has many overload, I am here only using one that takes data-reader as input parameter.

The class SqlBulkCopy is sealed class i.e. one can’t inherits this class.

Now after running this code you can see that the records from product table are being copied to product_backup.To see run query as following.
Query:select * from product_backup

You can try this twice thrice just for that truncate destination table Using following query.
Query: truncate table product_backup

If you doesn’t truncate the destination table then duplicate entries get added except the primary key column.