Get children from parent child relation table

Id          Name                                               ParentId

----------- -------------------------------------------------- -----------

1           Arjun                                              NULL

2           Sarvesh                                            1

3           Nitin                                                 2

4           Gaurav                                             1

5           Mithesh                                            1

6           Ashish                                             2

7           Jay                                                  3

CREATE PROCEDURE GetAllChildren 

@ParentId INT

AS

BEGIN

WITH [CTE] AS (

    SELECT c.Id,c.Name,c.ParentId FROM ParentChildTable c WHERE c.[ParentId] = @ParentId

    UNION ALL

    SELECT c.Id,c.Name,c.ParentId  FROM [CTE] p, ParentChildTable c WHERE c.[ParentId] = p.[Id]

)

SELECT  * FROM [CTE]

END

exec GetAllChildren 1

OUTPUT:-

Id          Name                                               ParentId
----------- -------------------------------------------------- -----------
2           Sarvesh                                            1
4           Gaurav                                             1
5           Mithesh                                            1
3           Nitin                                                 2
6           Ashish                                             2
7           Jay                                                  3
8           Mayank                                            7









ChildName                                          ParentName
-------------------------------------------------- --------------------------------------------------
Sarvesh                                            Arjun
Gaurav                                             Arjun
Mithesh                                            Arjun
Nitin                                                 Sarvesh
Ashish                                             Sarvesh
Jay                                                   Nitin
Mayank                                             Jay

For getting result like above you should take self join to the table like below:-

ALTER PROCEDURE GetAllChildren 

@ParentId INT

AS

BEGIN

WITH [CTE] AS (

    SELECT c.Id,c.Name,c.ParentId FROM ParentChildTable c WHERE c.[ParentId] = @ParentId

    UNION ALL

    SELECT c.Id,c.Name,c.ParentId  FROM [CTE] p, ParentChildTable c 

    WHERE c.[ParentId] = p.[Id]

)

SELECT ([CTE].Name) AS [ChildName],(ParentChildTable.Name) AS [ParentName]  FROM [CTE] INNER JOIN ParentChildTable ON [CTE].ParentId=ParentChildTable.Id

END




Download complete script here

Multiple inner join in linq and Convert Linq result to datatable

Take Example of  three dummy tables named as Usermaster,SolutionMaster and RoleType
UserMaster contains-: UserMasterId,UserName,RoleTypeId
SolutionMaster  -: SolutionMasterId,CreatedBy(Or UserMasterId),SolutionName
RoleType :- RoleTypeId,RoleName

public DataSet GetData()
        {
            DataSet ds = new DataSet();
            string sql = "Select * from UserMaster" + "\n Select * from SolutionMaster" + "\n Select * from RoleType";
            ds = SqlHelper.ExecuteDataset(cOnstr, CommandType.Text, sql);
            ds.Tables[0].TableName = "UserMaster";
            ds.Tables[1].TableName = "SolutionMaster";
            ds.Tables[2].TableName = "RoleType";
            return ds;
        }



protected void btnGetLinqJoin_Click(object sender, EventArgs e)
        {
            DataSet ds = GetData();
            DataTable dtReturn = new DataTable();

            var ResultTable = from sm in ds.Tables[1].AsEnumerable()
                              join um in ds.Tables[0].AsEnumerable()
                              on (dynamic)sm["CreatedBy"]
                              equals (dynamic)um["UserMasterId"] into sr
                              from um in sr
                              join rm in ds.Tables[2].AsEnumerable()
                              on (dynamic)um["RoleTypeId"] equals (dynamic)rm["RoleTypeId"] into sc
                              from rm in sc
                              select new
                              {
                                  SolutionName = (dynamic)sm["SolutionName"],
                                  UserName = (dynamic)um["UserName"],
                                  RoleType = (dynamic)rm["RoleTypeName"]

                              };
            dtReturn = ToDataTable(ResultTable);


        }

        public DataTable ToDataTable<T>(IEnumerable<T> varlist)
        {
            DataTable dtReturn = new DataTable();
            PropertyInfo[] oProps = null;

            if (varlist == null) return dtReturn;

            foreach (T rec in varlist)
            {
                // Use reflection to get property names, to create table,
                //Only first time, others will follow
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;

                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                        == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }

                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }

                DataRow dr = dtReturn.NewRow();

                foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                    (rec, null);
                }

                dtReturn.Rows.Add(dr);
            }
            return dtReturn;
        }



OUTPUT :-

SoltuinName	UserName	Role
ABCD	ABCD	Standard
XYZ	XYZ	Admin

Check link are broken or not

1) Save this code as Brtokenlink.vbs

Dim WshShell
Dim met
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Workbooks.open ("D:\result\result.xlsx")

Set WshShell = WScript.CreateObject("WScript.Shell")
Set oIE = CreateObject("InternetExplorer.Application")
oIE.Visible = True
site="http:// set the website address here"
oIE.navigate site
Do While oIE.Busy
     Wscript.Sleep 500
Loop
Set links=oIE.Document.getElementsByTagName("A")
For i =0 To oIE.Document.getElementsByTagName("A").length-1
    url=links(i).href
'Call ClearBrowserCache()
    Set webService = nothing
Set webService= CreateObject("Microsoft.XMLHTTP")
On Error Resume Next
webService.open "GET", url, False
On Error Resume Next
webService.Send (null)
If webService.status < 200 or webService.status >399 Then
   objExcel.Cells(i+2, 1).Value = i+1
objExcel.Cells(i+2, 2).Value = links(i).text
objExcel.Cells(i+2, 3).Value = url
objExcel.Cells(i+2, 4).Value = "In valid request   "& webService.status
geturlstatus = 0
else
geturlstatus = 1
objExcel.Cells(i+2, 1).Value = i+1
objExcel.Cells(i+2, 2).Value = links(i).innertext
objExcel.Cells(i+2, 3).Value = url
objExcel.Cells(i+2, 4).Value = "valid request   "& webService.status
End If
    Set webService = nothing
err.clear
next

Public Function ClearBrowserCache()
On Error Resume Next
Const TEMPORARY_INTERNET_FILES=32
Set objCacheFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(TEMPORARY_INTERNET_FILES)
objCacheFSO.DeleteFile(objFolder.Self.Path & "\*.*")
sPath = objCacheFSO.GetFolder(objFolder.Self.path) & "\Content.IE5\"
Set objFolders = objCacheFSO.GetFolder(sPath)
For Each objFName In objFolders.SubFolders
  objCacheFSO.DeleteFolder sPath & objFName.Name, True
Next
ClearBrowserCache= True
Set objFolder=Nothing
Set objShell=Nothing
Set objCacheFSO=Nothing
err.clear
End Function
objExcel.ActiveWorkBook.SaveAs "result.xlsx"
objExcel.Quit
oIE.quit
MsgBox "Task Completed"


2) create D://result/result.xls to see the result. 

Delete checked Rows in Gridview

Step 1: Create aspx page like that :-

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CheckBoxinGriedview.aspx.cs"

    Inherits="WebApplication1.WebForm5" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <script type="text/javascript">

        var TotalChkBx;

        var Counter;

        window.onload = function () {

            //Get total no. of CheckBoxes in side the GridView.

            TotalChkBx = parseInt('<%= this.gvCheckboxes.Rows.Count %>');

            //Get total no. of checked CheckBoxes in side the GridView.

            Counter = 0;

        }

        function HeaderClick(CheckBox) {

            //Get target base & child control.

            var TargetBaseControl =

       document.getElementById('<%= this.gvCheckboxes.ClientID %>');

            var TargetChildControl = "chkBxSelect";

            //Get all the control of the type INPUT in the base control.

            var Inputs = TargetBaseControl.getElementsByTagName("input");

            //Checked/Unchecked all the checkBoxes in side the GridView.

            for (var n = 0; n < Inputs.length; ++n)

                if (Inputs[n].type == 'checkbox' &&

                Inputs[n].id.indexOf(TargetChildControl, 0) >= 0)

                    Inputs[n].checked = CheckBox.checked;

            //Reset Counter

            Counter = CheckBox.checked ? TotalChkBx : 0;

        }

        function ChildClick(CheckBox, HCheckBox) {

            //get target control.

            var HeaderCheckBox = document.getElementById(HCheckBox);

            //Modifiy Counter; 

            if (CheckBox.checked && Counter < TotalChkBx)

                Counter++;

            else if (Counter > 0)

                Counter--;

            //Change state of the header CheckBox.

            if (Counter < TotalChkBx)

                HeaderCheckBox.checked = false;

            else if (Counter == TotalChkBx)

                HeaderCheckBox.checked = true;

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:GridView ID="gvCheckboxes" runat="server" AutoGenerateColumns="False" DataKeyNames="id"

            DataSourceID="SqlDataSource1" OnRowCreated="gvCheckboxes_RowCreated" OnRowDataBound="gvCheckboxes_RowDataBound">

            <Columns>

                <asp:TemplateField>

                    <ItemTemplate>

                        <asp:CheckBox ID="chkBxSelect" runat="server" />

                    </ItemTemplate>

                    <HeaderTemplate>

                        <asp:CheckBox ID="chkBxHeader" onclick="javascript:HeaderClick(this);" runat="server" />

                    </HeaderTemplate>

                </asp:TemplateField>

                <asp:BoundField HeaderText="id" DataField="id" SortExpression="id" InsertVisible="False"

                    ReadOnly="True" />

                <asp:BoundField DataField="article" HeaderText="article" SortExpression="article" />

            </Columns>

        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DemoConnectionString %>"

            SelectCommand="SELECT * FROM [blog]" 

            DeleteCommand="DELETE FROM [blog] WHERE [id] = @id" 

            InsertCommand="INSERT INTO [blog] ([article]) VALUES (@article)" 

            UpdateCommand="UPDATE [blog] SET [article] = @article WHERE [id] = @id">

            <DeleteParameters>

                <asp:Parameter Name="id" Type="Int32" />

            </DeleteParameters>

            <InsertParameters>

                <asp:Parameter Name="article" Type="String" />

            </InsertParameters>

            <UpdateParameters>

                <asp:Parameter Name="article" Type="String" />

                <asp:Parameter Name="id" Type="Int32" />

            </UpdateParameters>

        </asp:SqlDataSource>

        <asp:Button ID="Button1" runat="server" Text="Delete" OnClick="Button1_Click" />

    </div>

    </form>

</body>

</html>

Step 2: Copy the following code in code behind :-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace WebApplication1

{

    public partial class WebForm5 : System.Web.UI.Page

    {

        string strConnection = ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString;

        protected void Page_Load(object sender, EventArgs e)

        {

            

        }

 protected void Button1_Click(object sender, EventArgs e)
        {
            //StringBuilder str = new StringBuilder();

            // Select the checkboxes from the GridView control

            for (int i = 0; i < gvCheckboxes.Rows.Count; i++)
            {
                GridViewRow row = gvCheckboxes.Rows[i];
                bool isChecked = ((CheckBox)row.FindControl("chkBxSelect")).Checked;

                if (isChecked)
                {
                    // Column 2 is the name column
                    //str.Append(gvCheckboxes.Rows[i].Cells[1].Text + "<br>");
                    int employeeID = Convert.ToInt32(gvCheckboxes.DataKeys[row.RowIndex].Value);
                    // Pass the value of the selected Employye ID to the Delete //command.
                    //SqlDataSource1.DeleteParameters["id"].DefaultValue = employeeID.ToString();
                    //SqlDataSource1.Delete();
                    DeleteMultipleRecords(employeeID);
                 }
            }
            gvCheckboxes.DataBind();

            // prints out the result
            //Response.Write(str.ToString());
    }
        public void DeleteMultipleRecords(int id)
        {
            string cmd = "Delete blog where id=@Id";
            SqlConnection mycon = new SqlConnection(strConnection);
            SqlCommand mycmd = new SqlCommand(cmd, mycon);
            mycmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            mycon.Open();
            mycmd.ExecuteNonQuery();

        }

        protected void gvCheckboxes_RowDataBound(object sender, GridViewRowEventArgs e)

        {

            

                e.Row.Cells[1].Visible = false;

           

            if (e.Row.RowType == DataControlRowType.DataRow)

            {

                CheckBox chkBxSelect = (CheckBox)e.Row.Cells[0].FindControl("chkBxSelect");

                CheckBox chkBxHeader = (CheckBox)this.gvCheckboxes.HeaderRow.FindControl("chkBxHeader");

                chkBxSelect.Attributes["onclick"] = string.Format("javascript:ChildClick(this,'{0}');",chkBxHeader.ClientID);

            }

        }

        protected void gvCheckboxes_RowCreated(object sender, GridViewRowEventArgs e)

        {

            

        }

    }

}

Import and Export Data from Excel File using Gridview

First How to Import Data from Excel File

•  Create Excel file named Test.xlsx.
•  In that Excel file give two columns named Id, Name.
• Make some entries in that columns.

Now on the Page Load event (using System.Data.OleDb)

 protected void Page_Load(object sender, EventArgs e)

        {

            string FileName = Server.MapPath("Test.xlsx");

            DataSet ds=ImportExcel(FileName);

            GridView1.DataSource = ds;

            GridView1.DataBind();

        }

        public DataSet ImportExcel(string FileName)

        {

            string strSQL = "SELECT * FROM [Demo$]";

            OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;'");

            excelConnection.Open();

            OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

            DataSet dsExcel = new DataSet();

            dataAdapter.Fill(dsExcel, "Demo");

            dataAdapter.Dispose();

            dbCommand.Dispose();

            excelConnection.Close();

            excelConnection.Dispose();

            return dsExcel;

        }

Now How to Export Data to Excel File From Gridview

(using System.Web)

 protected void Button1_Click(object sender, EventArgs e)

        {

            string filename = Server.MapPath("Test1.xlsx");

            HttpResponse response = HttpContext.Current.Response;

            // first let's clean up the response.object

            response.Clear();

            response.Charset = "";

            // set the response mime type for excel

            response.ContentType = "application/vnd.ms-excel";

            response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

            // create a string writer

            using (StringWriter sw = new StringWriter())

            {

                using (HtmlTextWriter htw = new HtmlTextWriter(sw))

                {

                    // instantiate a datagrid

                  

                    GridView1.RenderControl(htw);

                    response.Write(sw.ToString());

                    response.End();

                }

            }

        }

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server." 

To avoid this error, add the following code:  

        

public override void VerifyRenderingInServerForm(Control control)

        {

        }

Add, Edit and Delete using Xml File in gridview

Step 1:  Design a page like as follows

Step 2: Make Test.xml like as follows

Step 3: on code behind


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Xml;


namespace XmlDemo
{
    public partial class XmlDemo : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            #region -OnLoad-
            DataSet ds = new DataSet();
            ds.ReadXml(Server.MapPath("Test.xml"));
            Session["Dataset"] = ds;

            grdXMLDemo.DataSource = ds;
            grdXMLDemo.DataBind();
            #endregion
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            int status=Convert.ToInt32(lblStatus.Text);
            if (status==1)
            {
             
                InsertNewRecord();
             
            }
            else
            {
                UpdateRecords();
             
            }
         
        }

        #region -Update Record-
        public void UpdateRecords()
        {
            if (Session["Question_Id"] != null)
            {
                int Question_id = Convert.ToInt32(Session["Question_Id"]);
                DataSet dsEdits;
                dsEdits = (DataSet)Session["Dataset"];


                DataView dvEditsQuestion = dsEdits.Tables["Question"].DefaultView;
                dvEditsQuestion.RowFilter = "Question_Id=" + Question_id;
                dvEditsQuestion.AllowEdit = true;
                dvEditsQuestion[0].BeginEdit();
                dvEditsQuestion[0]["QName"] = txtQuestionName.Text;
                dvEditsQuestion[0]["Text"] = txtQuestion.Text;
                dvEditsQuestion[0]["QType"] = txtQuestionType.Text;
                dvEditsQuestion[0].EndEdit();


                DataView dvEditsOpt = dsEdits.Tables["Opt"].DefaultView;
                dvEditsOpt.RowFilter = "Optionlist_Id=" + Question_id;
                dvEditsOpt.AllowEdit = true;
                dvEditsOpt[0]["O1"] = txtOption1.Text;
                dvEditsOpt[0]["O2"] = txtOption2.Text;
                dvEditsOpt[0]["O3"] = txtOption3.Text;
                dvEditsOpt[0]["O4"] = txtOption4.Text;
                dvEditsOpt[0]["O5"] = txtOption5.Text;
                dvEditsOpt[0].EndEdit();

                dsEdits.AcceptChanges();
                Session["Dataset"] = dsEdits;
                dsEdits.WriteXml(Server.MapPath("Test.xml"));
                Response.Redirect("XMLDemo.aspx");
            }
        }
        #endregion

        #region -Add New Record-
        public void InsertNewRecord()
        {
            if (Session["Dataset"] != null)
            {
                DataSet dsAdd;
                dsAdd = (DataSet)Session["Dataset"];


                DataTable dtAddQuestion = new DataTable();
                dtAddQuestion = dsAdd.Tables["Question"];
                DataRow drAddQuestion = dtAddQuestion.NewRow();
                drAddQuestion["QName"] = txtQuestionName.Text;
                drAddQuestion["Text"] = txtQuestion.Text;
                drAddQuestion["QType"] = txtQuestionType.Text;
                dtAddQuestion.Rows.Add(drAddQuestion);

                int Question_Id = Convert.ToInt32(dsAdd.Tables["Question"].Compute("MAX(Question_Id)", ""));

                DataTable dtAddOptionlist = new DataTable();
                dtAddOptionlist = dsAdd.Tables["Optionlist"];
                DataRow drAddOptionlist = dtAddOptionlist.NewRow();
                drAddOptionlist["Optionlist_Id"] = Question_Id;
                drAddOptionlist["Question_Id"] = Question_Id;
                dtAddOptionlist.Rows.Add(drAddOptionlist);

                DataTable dtAddOpt = new DataTable();
                dtAddOpt = dsAdd.Tables["Opt"];
                DataRow drAddOpt = dtAddOpt.NewRow();
                drAddOpt["O1"] = txtOption1.Text;
                drAddOpt["O2"] = txtOption2.Text;
                drAddOpt["O3"] = txtOption3.Text;
                drAddOpt["O4"] = txtOption4.Text;
                drAddOpt["O5"] = txtOption5.Text;
                drAddOpt["Optionlist_Id"] = Question_Id;
                dtAddOpt.Rows.Add(drAddOpt);

                dsAdd.AcceptChanges();
                dsAdd.WriteXml(Server.MapPath("Test.xml"));
                Session["Dataset"] = dsAdd;
                Response.Redirect("XmlDemo.aspx");
            }
        }
        #endregion

        protected void grdXMLDemo_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            int index = Convert.ToInt32(e.CommandArgument.ToString());
            int Question_Id = Convert.ToInt32(grdXMLDemo.DataKeys[index].Value);

            #region -Delete-
            if (e.CommandName.Equals("Deletes"))
            {
                if (Session["Dataset"] != null)
                {
                    DataSet dsDelete;
                    dsDelete = (DataSet)Session["Dataset"];

                    DataView dvDelete = new DataView();
                    dvDelete = dsDelete.Tables["Question"].DefaultView;
                    dvDelete.RowFilter = "Question_Id=" + Question_Id;
                    dvDelete.AllowDelete = true;
                    dvDelete[0].Delete();

                    dsDelete.AcceptChanges();
                    Session["Dataset"] = dsDelete;
                    dsDelete.WriteXml(Server.MapPath("Test.xml"));
                    Response.Redirect("XMLDemo.aspx");
                }
            }
            #endregion

            #region -Edit-
            if (e.CommandName.Equals("Edits"))
            {
                if (Session["Dataset"] != null)
                {
                    DataSet dsEdit;
                    dsEdit = (DataSet)Session["Dataset"];

                    DataView dvEditQuestion = new DataView();
                    dvEditQuestion = dsEdit.Tables["Question"].DefaultView;
                    dvEditQuestion.RowFilter = "Question_Id=" + Question_Id;
                    txtQuestionName.Text = dvEditQuestion[0].Row["QName"].ToString();
                    txtQuestion.Text = dvEditQuestion[0].Row["Text"].ToString();
                    txtQuestionType.Text = dvEditQuestion[0].Row["QType"].ToString();

                    DataView dvEditOpt=new DataView();
                    dvEditOpt = dsEdit.Tables["Opt"].DefaultView;
                    dvEditOpt.RowFilter = "Optionlist_Id=" + Question_Id;
                    txtOption1.Text = dvEditOpt[0].Row["O1"].ToString();
                    txtOption2.Text = dvEditOpt[0].Row["O2"].ToString();
                    txtOption3.Text = dvEditOpt[0].Row["O3"].ToString();
                    txtOption4.Text = dvEditOpt[0].Row["O4"].ToString();
                    txtOption5.Text = dvEditOpt[0].Row["O5"].ToString();
                    Session["Question_Id"] = Question_Id;
                    txtQuestionName.Enabled = false;
                    lblStatus.Text = "2";
                }
            }
            #endregion
        }

        protected void grdXMLDemo_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            e.Row.Cells[2].Visible = false;
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            #region -Cancel-
            txtQuestionName.Text = string.Empty;
            txtQuestion.Text = string.Empty;
            txtQuestionType.Text = string.Empty;
            txtOption1.Text = string.Empty;
            txtOption2.Text = string.Empty;
            txtOption3.Text = string.Empty;
            txtOption4.Text = string.Empty;
            txtOption5.Text = string.Empty;
            lblStatus.Text = "1";
            txtQuestionName.Enabled = true;
            #endregion
        }
    }
}

Downloading on Link Button in asp.net

Step 1: Drop Link button on aspx page

Step 2: on the cs file write  the following code:


 protected void LinkButton1_Click(object sender, EventArgs e)
 {
           string fname="Your File Name";
           string filepath = Server.MapPath("~//ZipFiles//" + fname);
            FileInfo info = new FileInfo(filepath);
            if (info.Exists == true)
            {
                Response.Clear();
                Response.AddHeader("Content-Disposition", "attachment; filename=" + info.Name);
                Response.AddHeader("Content-Length", info.Length.ToString());
                Response.ContentType = "application/octet-stream";
                Response.WriteFile(info.FullName);
                Response.End();
            }
        }

Xml to gridview

Step 1: Create test.xml 

'

Step 2: Create aspx page and add gridvew in it, in which you want to load XML

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="XML.aspx.cs" Inherits="ParentChildGridView.Example.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    

        <asp:GridView ID="GridView1" runat="server" BackColor="White" AutoGenerateColumns="False"

            BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" 

            ForeColor="Black" GridLines="Vertical">

            <RowStyle BackColor="#F7F7DE" />

            <Columns>

            <asp:BoundField DataField="Company" HeaderText="Company" SortExpression="Company" />

            <asp:BoundField DataField="Profile" HeaderText="Profile" SortExpression="Profile" />

                <asp:TemplateField HeaderText="Delete">

                    <ItemTemplate>

                        <asp:ImageButton ID="ImageButton1" runat="server" 

                            ImageUrl="~/Example/trash.gif" />

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

            <FooterStyle BackColor="#CCCC99" />

            <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

            <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />

            <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

            <AlternatingRowStyle BackColor="White" />

        </asp:GridView>

    

    </div>

    </form>

</body>

</html>

Step 3: Code Behind

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml;

namespace ParentChildGridView.Example

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            DataSet dsStore = new DataSet();

            dsStore.ReadXml(Server.MapPath("test.xml"));

            XmlDocument objDoc = new XmlDocument();

            objDoc.LoadXml(dsStore.GetXml());

            DataTable dt = new DataTable();

            DataColumn dc;

            dc = new DataColumn("Company");

            dt.Columns.Add(dc);

            dc = new DataColumn("Profile");

            dt.Columns.Add(dc);

            DataRow dr;

            XmlNodeList objNodeList = objDoc.SelectNodes("/root/Personalabc/Name/Job_Experience");

            foreach (XmlNode objNode in objNodeList)

            {

                //Response.Write(objNode.InnerText + "<br/>");

                dr = dt.NewRow();

                dr["Company"] = objNode.ChildNodes[0].InnerXml.Trim();

                dr["Profile"] = objNode.ChildNodes[1].InnerXml.Trim();

                dt.Rows.Add(dr);

            }

            GridView1.DataSource = dt;

            GridView1.DataBind();

        }

    }

}

Facebook like post using repeater in asp.net

create a database demo and add table PostMaster 
table contain the columns "postid(pk),post,postdate"


Step 1: Create StyleSheet "Default.css"
.table

{
width: 60%;
border-radius: 14px;
-moz-border-radius: 14px;
-webkit-border-radius: 14px;
border: 1px groove #B7B7B7;
margin-left: 108px;
vertical-align: text-top;
background-color: #CACACA;
}
.td
{
height: 79px;
text-align:center;
}
.link
{
text-decoration:none;
font-weight:bold;
background: #0571A6;
background: -moz-linear-gradient(top,#73AEC9 0,#73AEC9 1px,#338AB0 1px,#0571A6 100%);
background: -webkit-gradient(linear,left top,left bottom,color-stop(0,#73AEC9),color-stop(5%,#73AEC9),color-stop(5%,#338AB0),color-stop(100%,#0571A6));
background: #0571A6;
filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#338AB0',endColorstr='#0571A6',GradientType=0);
border-color: #045A8B;
color: white!important;
margin-left: 0px;
margin-right:20px;
}
.divPost
{
height: 104px;
}
.divborder
{
margin-top:23px;
border-top:groove 1px #CACACA;
}
.divcontent
{
margin-top:10px;
width: 532px;
}
.PostTable
{
border:1px groove #C0C0C0;
border-radius: 11px;
margin-bottom:8px;
text-align:justify;
margin-left:20px;
-moz-border-radius: 11px;
-webkit-border-radius: 11px;
background-color:#EDFAEF;
width:500px;
height:100px;
}

Step 2: Create an asp webform "Sharing.aspx" 


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Sharing.aspx.cs" Inherits="ASP_NET.Examples.Sharing" %>

<%@ Register src="BlogPost.ascx" tagname="BlogPost" tagprefix="uc1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Wall Post Example</title>
    <link href="Default.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div class="divPost">
        <table class="table">
            <tr>
                <td class="td">
                    <asp:TextBox ID="txtPost" runat="server" style="margin-top:10px" Height="50px"
                        Width="554px" TextMode="MultiLine"></asp:TextBox>
                    <p align="right" style="margin-top:5px;">
                        <asp:RequiredFieldValidator ID="RFV" runat="server" style="float:left; margin-left: 23px;"
                            ErrorMessage="Enter Text First" ControlToValidate="txtPost"
                            ForeColor="White"></asp:RequiredFieldValidator>
                        <asp:Button ID="btnPost" runat="server" CssClass="link" Text="Post"
                            Width="91px" onclick="Button1_Click" />
                    </p>
                 </td>
            </tr>
        </table>
    </div>
    <div class="divborder">
    </div>
    <div class="divcontent">
        <uc1:BlogPost ID="BlogPost1" runat="server" />
    </div>
    </form>
    </body>
</html>

Step 3: Go to code file "Sharing.aspx.cs"

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;

namespace ASP_NET.Examples

{

    public partial class Sharing : System.Web.UI.Page

    {

        string connectionstring = ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString;

        DateTime today_date;

        protected void Page_Load(object sender, EventArgs e)

        {

            

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            today_date = DateTime.Now;

            SqlConnection mycon = new SqlConnection(connectionstring);

            SqlCommand mycmd = new SqlCommand("InsertPost", mycon);

            mycon.Open();

            mycmd.CommandType = CommandType.StoredProcedure;

            mycmd.Parameters.Add("@Post", SqlDbType.NVarChar).Value = txtPost.Text;

            mycmd.Parameters.Add("@PostDate", SqlDbType.DateTime).Value = today_date;

            mycmd.ExecuteNonQuery();

            mycmd.Dispose();

            mycon.Close();

            Response.Redirect("Sharing.aspx");

        }

    }

}

Step 4: Create Web User Control "BlogPost.ascx"

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="BlogPost.ascx.cs" Inherits="ASP_NET.Examples.BlogPost" %>

<link href="Default.css" rel="stylesheet" type="text/css" />

<div id="storydiv" style="width:235px">

    

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">

<ItemTemplate><table class="PostTable"><tr><td>

<asp:Label ID="lblStory" runat="server" Text='<%# Eval("Post") %>' style="text-align:justify;font-family:Calibri; color:Gray; margin-left:25px;" /></td></tr></table>

</ItemTemplate>

</asp:Repeater></div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 

    ConnectionString="<%$ ConnectionStrings:DemoConnectionString %>" 

    

    SelectCommand="SELECT PostId, Post, PostDate FROM PostMaster ORDER BY PostId DESC"></asp:SqlDataSource>

Self Join Example in sql server

Employee				Table#1
Employee ID	FirstName	LastName	DesignationID	ReportingManager
1001	Amit	Johnson	101	1004
1002	Jim	Collins	102	1001
1003	John	Rosemond	102	1001
1004	Liz	Pulliam	103	1004
1005	Clara	Shih	101	1004
1006	Rocky	Singh	102	1005

Designation		Table#2
DesignationID	Name
101	Creative Director
102	Designer
103	Digital Marketing Manager
104	President

Output		Table#3
Write an SQL query to retrieve following output in following format.
Full Name	Designation	Manager's Name

======================================================================

SELECT     EmployeeName.FirstName + ' ' + EmployeeName.LastName AS EmployeeName, Employee.FirstName + ' ' + Employee.LastName AS ManagerName,                       Designation.Name AS Designation FROM         Employee INNER JOIN                       Employee AS EmployeeName ON Employee.EmployeeId = EmployeeName.ReportingManager INNER JOIN                       Designation ON Employee.DesignationId = Designation.DesignationId ====================================================================== Output:              EmployeeName         ManagerName        Designation             =============       ============        =========              Amit Johnson            Liz Pulliam               Digital Marketing Manager             Jim  Collins                Amit Johnson           Creative Director             John Rosemond        Amit Johnson           Creative Director             Liz Pulliam                 Liz Pulliam                Digital Marketing Manager             Clara Shih                  Liz Pulliam               Digital Marketing Manager             Rocky  Singh             Clara Shih                Creative Director