Insert ,Update,Edit,Delete In Gridview

Page.aspx

<asp:GridView ID="GridView1" runat="server" style="height:100px; overflow:auto" AutoGenerateColumns="False" 

        DataSourceID="sqldsEmpDetails" onrowcancelingedit="GridView1_RowCancelingEdit" 

        onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating" 

        DataKeyNames="id" onrowdeleting="GridView1_RowDeleting" 

        onrowcommand="GridView1_RowCommand" onrowcreated="GridView1_RowCreated" 

        ShowHeader="False">

        <Columns>

            <asp:TemplateField HeaderText="Edit">

                <EditItemTemplate>

                    <asp:LinkButton ID="lkbtnUpdate" runat="server" CommandName="Update" 

                        CommandArgument='<%# Eval("Id") %>'>Update</asp:LinkButton>

                    <asp:LinkButton ID="lkbtnCancal" runat="server" CommandName="Cancel">Cancal</asp:LinkButton>

                </EditItemTemplate>

                <FooterTemplate>

                    <asp:LinkButton ID="lkbtninsert" runat="server" CommandName="AddNew" 

                        ValidationGroup="Insert">Insert</asp:LinkButton>

                    <asp:LinkButton ID="lkbtncancel" runat="server" onclick="lkbtncancel_Click">Cancel</asp:LinkButton>

                </FooterTemplate>

                <ItemTemplate>

                    <asp:LinkButton ID="lkbtnEdit" runat="server" CommandName="Edit">Edit</asp:LinkButton>

                    <asp:LinkButton ID="lkbtnDelete" runat="server" 

                        CommandArgument='<%# Eval("id") %>' CommandName="Delete" 

                        onclientclick="javascript:return DeleteItem();">Delete</asp:LinkButton>

                </ItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="Name">

                <EditItemTemplate>

                    <asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>

                </EditItemTemplate>

                <FooterTemplate>

                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 

                        ControlToValidate="TextBox1" ErrorMessage="*" ForeColor="Red" 

                        ValidationGroup="Insert"></asp:RequiredFieldValidator>

                </FooterTemplate>

                <ItemTemplate>

                    <asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>

                </ItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="Addresse">

                <EditItemTemplate>

                    <asp:TextBox ID="txtAddresse" runat="server" Text='<%# Eval("Addresse") %>'></asp:TextBox>

                </EditItemTemplate>

                <FooterTemplate>

                    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 

                        ControlToValidate="TextBox2" ErrorMessage="*" ForeColor="Red" 

                        ValidationGroup="Insert"></asp:RequiredFieldValidator>

                </FooterTemplate>

                <ItemTemplate>

                    <asp:Label ID="lbladresse" runat="server" Text='<%# Eval("Addresse") %>'></asp:Label>

                </ItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="Cell">

                <EditItemTemplate>

                    <asp:TextBox ID="txtcell" runat="server" Text='<%# Eval("Cell") %>'></asp:TextBox>

                </EditItemTemplate>

                <FooterTemplate>

                    <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" 

                        ControlToValidate="TextBox3" ErrorMessage="*" ForeColor="Red" 

                        ValidationGroup="Insert"></asp:RequiredFieldValidator>

                </FooterTemplate>

                <ItemTemplate>

                    <asp:Label ID="lblcell" runat="server" Text='<%# Eval("Cell") %>'></asp:Label>

                </ItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="Id" Visible="False">

                <EditItemTemplate>

                    <asp:TextBox ID="txtid" runat="server" Text='<%# Eval("ID") %>'></asp:TextBox>

                </EditItemTemplate>

                <ItemTemplate>

                    <asp:Label ID="lblid" runat="server" Text='<%# Eval("id") %>'></asp:Label>

                </ItemTemplate>

            </asp:TemplateField>

        </Columns>

        <HeaderStyle CssClass="FixedHeader" />

    </asp:GridView>

<asp:Button ID="btnnewrow" runat="server" onclick="btnnewrow_Click" 

        Text="Add New" />

 <script type="text/javascript">

//conformation box for Delete

    function DeleteItem() {

        if (confirm("Are you sure you want to delete ...?")) {

            return true;

        }

        return false;

    }

 </script>

Page.CS  

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.Common;

using System.Data;

using MySql.Data.MySqlClient;

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

{

    MySqlConnection conn = new MySqlConnection();

    MySqlCommand cmd = new MySqlCommand();

    MySqlDataAdapter adp = new MySqlDataAdapter();

    sendmail objmail = new sendmail();

    protected void Page_Load(object sender, EventArgs e)

    {

        Label1.Text = string.Empty;

        conn = new MySqlConnection("Server=localhost ;Database=emp ;Uid=root;Pwd=send;");

        conn.Open();

        Bind();

    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

    {

 GridView1.EditIndex = e.NewEditIndex;

 GridView1.DataBind();

    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        LinkButton lk = (LinkButton)GridView1.Rows[e.RowIndex].FindControl("lkbtnUpdate");

        string id = lk.CommandArgument.ToString();

       // string iidd = Gvempdetails.DataKeys[e.RowIndex].Value["id"];

      //  int id = Convert.ToInt32(Gvempdetails.DataKeys[e.RowIndex].Item["youfield"].tostring());

       // TextBox id = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtid");

        TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtname");

        TextBox Address = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAddresse");

        TextBox cell = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtcell");

        string SaveQuery = "UPDATE empdetails SET NAME='" + name.Text + "',addresse='" + Address.Text + "',Cell=" + cell.Text + " WHERE id=" + id + "";

        

        //cmd = new MySqlCommand(SaveQuery, conn);

        //cmd.ExecuteNonQuery();

       sqldsEmpDetails.UpdateCommand = SaveQuery;

       sqldsEmpDetails.Update();

        GridView1.EditIndex = -1;

        GridView1.DataBind();

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

    {

        GridView1.EditIndex = -1;

        GridView1.DataBind();

    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

    {

        string test = GridView1.DataKeys[e.RowIndex].Value.ToString();

        LinkButton lk = (LinkButton)GridView1.Rows[e.RowIndex].FindControl("lkbtnDelete");

        string id = lk.CommandArgument.ToString();

        sqldsEmpDetails.DeleteCommand = "Delete From empdetails where id="+id+"";

        sqldsEmpDetails.DataBind();

        GridView1.DataBind();

    }

protected void btnnewrow_Click(object sender, EventArgs e)

    {

        GridView1.ShowFooter = true;

    }

 protected void lkbtncancel_Click(object sender, EventArgs e)

    {

        GridView1.ShowFooter = false;

    }

public void Bind()

    {

        DataSet ds= new DataSet();

        adp = new MySqlDataAdapter("SELECT id,NAME,Addresse,Cell FROM empdetails  ORDER BY NAME ASC", conn);

        adp.Fill(ds, "c");

        GridView1.DataSource = ds.Tables[0];

        GridView1.DataBind();

    }

}

SQL Server ranking functions

In SQL Server there is 4 ranking functions:

ROW_NUMBER	Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
RANK	Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
DENSE_RANK	Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
NTILE	Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

below is the syntax:

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )

DENSE_RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > ) 

NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

Three Tire

Page.aspx

Page.aspx.cs

    protected void btnsubmit_Click(object sender, EventArgs e)

    {

        BLClass _b = new BLClass();

        Info _I = new Info();

        _I._p_Fullname = txtFullname.Text;

        _b.InsertUserInformation(_I);

    }

BLClass

public class BLClass

{

public BLClass()

{

}

    //SQL Connection string

    string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["empConnectionString"].ToString();

    #region Insert User Details

   

    public string InsertUserInformation(Info _I)

    {

        SqlConnection con = new SqlConnection(ConnectionString);

        con.Open();

        SqlCommand cmd = new SqlCommand("spname", con);

        cmd.CommandType = CommandType.StoredProcedure;

        try

        {

            cmd.Parameters.AddWithValue("@Fullname", _I._p_Fullname);

            cmd.Parameters.AddWithValue("@f", "I");

            

            cmd.ExecuteNonQuery();

            

            con.Close();

            string i = "0";

            return i;

        }

        catch (Exception ex)

        {

            throw ex;

        }

        finally

        {

            cmd.Dispose();

            con.Close();

            con.Dispose();

        }

    }

    #endregion

}

Info

public class Info

{       

    

        private string _Fullname;

        public string _p_Fullname

        {

            get

                {return _Fullname;}

            set{_Fullname=value;}

        }

public Info()

{

}

}

Page Life Cycle Events

·     PreInit : PreInit is the first event in page life cycle. It checks the IsPostBack property and determines whether the page is a postback. It sets the themes and master pages, creates dynamic controls and gets and sets profile property values. This event can be handled by overloading the OnPreInit method or creating a Page_PreInit handler.

·  Init : Init event initializes the control property and the control tree is built. This event can be handled by overloading the OnInit method or creating a Page_Init handler.

·   InitComplete : InitComplete event allows tracking of view state. All the controls turn on view-state tracking.

·   LoadViewState : LoadViewState event allows loading view state information into the controls.

· LoadPostData . During this phase, the contents of all the input fields defined with the <form> tag are processed.

· PreLoad . PreLoad occurs before the post back data is loaded in the controls. This event can be handled by overloading the OnPreLoad method or creating a Page_PreLoad handler.

· Load . the Load event is raised for the page first and then recursively for all child controls. The controls in the control tree are created. This event can be handled by overloading the OnLoad method or creating a Page_Load handler.

· LoadComplete . the loading process is completed, control event handlers are run and page validation takes place. This event can be handled by overloading the OnLoadComplete method or creating a Page_LoadComplete handler.

· PreRender . the PreRender event occurs just before the output is rendered. By handling this event, pages and controls can perform any updates before the output is rendered.

·  PreRenderComplete . as the PreRender event is recursively fired for all child controls, this event ensures the completion of the pre-rendering phase.

· SaveStateComplete . state of control on the page is saved. Personalization, control state and view state information is saved. The HTML markup is generated. This stage can be handled by overriding the Render method or creating a Page_Render handler.

·  UnLoad . the UnLoad phase is the last phase of the page life cycle. It raises the UnLoad event for all controls recursively and lastly for the page itself. Final cleanup is done and all resources and references, such as database connections, are freed. This event can be handled by modifying the OnUnLoad method or creating a Page_UnLoad handler.

Array list and Hash table

1.ArrayList  represents the ordered collection of an object or it can be said that it is individually indexed where as Hash table uses a key to access the elements in the collection.

2.ArrayList is used when you want to access the elements by using index where as Hash table is used when you must access elements by using an index.

Array list	Hash table
using System; using System.Collections; class Program {   static void Main()     { // Create an ArrayList and add two ints. ArrayList list = new ArrayList();         list.Add(5);         list.Add(7); // Use ArrayList with method. Example(list);     }     static void Example(ArrayList list)     {         foreach (inti in list)         {         Console.WriteLine(i);         }     } }Output 5 7	using System; using System.Collections; class Program { static void Main() { Hashtablehashtable = new Hashtable(); hashtable[1] = "One";  hashtable[2] = "Two"; hashtable[13] = "Thirteen"; foreach (DictionaryEntry entry in hashtable) {      Console.WriteLine("{0}, {1}", entry.Key, entry.Value); } } } Result 13, Thirteen 2, Two 1, One

To Identify Deleted Id in SQL Server

select number from master..spt_values s left join TableName b on s.number=b.Id
where s.type='p' and s.number<(select (MAX(Id)) from TableName) and b.Id is null

On Button Click Processing Method

 protected void Page_Load(object sender, EventArgs e)
    {
        this.btnsubmit.Attributes.Add("onclick", DisableTheButton(this.Page, this.btnsubmit));
    }

 #region Button wait event
    private string DisableTheButton(Control pge, Control btn)
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        sb.Append("if (typeof(Page_ClientValidate) == 'function') {");
        sb.Append("if (Page_ClientValidate() == false) { return false; }} ");
        sb.Append("this.value = 'Processing...';");
        sb.Append("this.disabled = true;");
        sb.Append(Page.GetPostBackEventReference(btn));
        sb.Append(";");
        return sb.ToString();
    }
    #endregion