Saturday 28 April 2012

Export To excel from gridview


   protected void btnGenerate_Click(object sender, EventArgs e)
        {
            // Here is example code for export Gridview to excel sheet,

            Response.Clear();             // Clear the response
            // Set the type and filename
            Response.AddHeader("content-disposition", "attachment;filename =        
                                 NameofSheet.xls"
);
            Response.Charset = "";
            Response.ContentType = "application/ms-excel";
      
       // Add the HTML from the GridView to a StringWriter so we can write it out later 
     
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView.RenderControl(hw);

            // Write out the data 
            Response.Write(sw.ToString());
            Response.End();

        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            base.VerifyRenderingInServerForm(control);
        }

        //Enjoy Life as much as you can….

Monday 23 April 2012

Recursive Query in Sql Server


Employee Hierarchy from Low Position to High Position
We have Table for Employee And we want employee Position Hierarchy then Apply below query

WITH TempTable(ID,Name,ReportingTo)
AS
(
SELECT e.empID,e.empName,e.empReportingTo FROM Employee_MAster e
WHERE e.empID = 'JSD001'

UNION ALL

SELECT e.empID,e.empName,e.empReportingTo FROM Employee_MAster e, TempTable temp
WHERE e.empID = temp.ReportingTo
)

SELECT * FROM TempTable

Generate XML from Dataset


   protected void generateXML_Click(object sender, EventArgs e)
        {
            // Define Connection with connection string
            SqlConnection con = new SqlConnection("Add ConnectionString");
            // write query to retrieve data.
            SqlCommand cmd = new SqlCommand("select * from DetailMaster", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();   // Take Dataset
            da.Fill(ds);
            ds.WriteXml("D:/textXML.xml");  // Path to create XML
        }

Linq Query


Here is Simple query in Linq to get 21-30 Record from Table,

TestDBAjayEntities  is entity Model and
Detailmasters is Table

 using (LinqEF.TestDBAjayEntities Test = new LinqEF.TestDBAjayEntities())
            {
                var List = (from Detail in Test.Detailmasters
                            select new
                            {
                                Detail.Company,
                                Detail.Designation,
                                Detail.Name
                            }).Skip(20).Take(10);
            };

Enjoy Life as much as you can.....

Sunday 22 April 2012

Difference between Linq to Sql AND Linq to Entity

LINQ to SQL
•  It use domain Model
•  It support only SQL server Database 
•  Data source contains Tables only
•  It is simple to use
•  rapid development done from this Model
•  It can map class to single table
•  It is hard to apply Inheritance 
•  dbml file only generated
•  Creation of complex properties is Not Supported
•  Query
    - LINQ to SQL (for select)
    - Data Context (for update, create, delete, store procedure, view)
•  Synchronization with Database if Database Schema is changed is not
    supported in this Model
•  Very slow performance for the first query
•  It doesn't support Continuous improvement of features in the future
•  Generate database from entity model is not supported
Entity Framework
•  It use conceptual data model
•  It support variety of databases
•  Data source contains tables, replication, reporting Services, BI and etc
•  It is complex to use
•  It has slower development but more capabilities
•  It can map class to multiple table
•  It is simple to apply Inheritance 
•  After compilation generate edmx file with 3 sections to represent the schema: 
    csdl, msl and ssdl
    csdl : Conceptual schema definition language
    msl  : mapping specification language
    ssdl : store schema definition language
•  Creation of complex properties is Not Supported in VS2010, 
    we can manually modify in .edmx file
•  Query :
    - LINQ to Entities (for select)
    - Entity SQL (is a derivative of Transact-SQL, it supports inheritance and associations)
    - Object Services (for update, create, delete, store procedure, view)
    - Entity Client (is an ADO.NET managed provider, it is similar to SQLClient, OracleClient,
etc. 
It provides several components like EntityCommand, EntityTransaction)
•  Synchronization with Database if Database Schema is changed is supported in this Model
•  Very slow performance for the first query.But overall performance is better than LINQ to SQL
•  It support Continuous improvement of features in the future
•  Generate database from entity model is not supported in VS2010