Insert Multiple Records Using One Insert Statement

How can I insert multiple values in table using only one insert? Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.

USE YourDB
GO
INSERT INTO MyTable  (FirstColSecondCol)
        VALUES (‘First’,1);
INSERT INTO MyTable  (FirstColSecondCol)
        VALUES (‘Second’,2);
INSERT INTO MyTable  (FirstColSecondCol)
        VALUES (‘Third’,3);
INSERT INTO MyTable  (FirstColSecondCol)
        VALUES (‘Fourth’,4);
INSERT INTO MyTable  (FirstColSecondCol)
        VALUES (‘Fifth’,5);
GO

The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps me focus on task, instead of copy paste. I have explained following script to new developer. He was quite pleased.

USE YourDB
GO
INSERT INTO MyTable  (FirstColSecondCol)
    SELECT  ‘First’ ,1
    UNION ALL
SELECT  ‘Second’ ,2
    UNION ALL
SELECT  ‘Third’ ,3
    UNION ALL
SELECT  ‘Fourth’ ,4
    UNION ALL
SELECT  ‘Fifth’ ,5
GO

The effective result is same.

Ref :- http://blog.sqlauthority.com

Diffrence between process and thread

Process vs. Thread:
“Process is unit of allocation while Thread is unit of execution. Each process has one or more threads. Each thread belong to one process”

Process:
*Execution Context:
1)Program COunter(PC)
2)Stack Pointer
3)Data Register
*CODE
*DATA
*STACK
*Process :Unit Of allocation(Resource,priviliges etc)
*each process have one or more threads
*Inter process communication is expensive:need to context swith
*Secure:one process can not corrupt another process

Thread:
Execution Context:
1)program counter
2)stack pointer
3)Data register
*each thread belong to one process
*Unit of execution(Pc,sp etc)
*inter thread communication is chip:can use process memory and may not need to context switch
*not secure:a thread can write the memory used by the another thread

The Evolution Of LINQ And Its Impact On The Design Of C#

was a huge fan of the Connections series, hosted by James Burke, when it aired on the Discovery Channel. Its basic premise: how seemingly unrelated discoveries influenced other discoveries, which ultimately led to some modern-day convenience. The moral, if you will, is that no advancement is made in isolation. Not surprisingly, the same is true for Language Integrated Query (LINQ).

In simple terms, LINQ is a series of language extensions that supports data querying in a type-safe way; it will be released with the next version Visual Studio, code-named “Orcas.” The data to be queried can take the form of XML (LINQ to XML), databases (LINQ-enabled ADO.NET, which includes LINQ to SQL, LINQ to Dataset and LINQ to Entities), objects (LINQ to Objects), and so on. The LINQ architecture is shown in Figure 1.

Figure 1 LINQ Architecture
Figure 1 LINQ Architecture (Click the image for a smaller view)

Figure 1 LINQ Architecture
Figure 1 LINQ Architecture (Click the image for a larger view)

Let’s look at some code. A sample LINQ query in the upcoming “Orcas” version of C# might look like:

var overdrawnQuery = from account in db.Accounts                      where account.Balance < 0                      select new { account.Name, account.Address };

When the results of this query are iterated over using foreach, each element returned would consist of a name and address of an account that has a balance less than 0.

It’s immediately obvious from the sample above that the syntax is like SQL. Several years ago, Anders Hejlsberg (chief designer of C#) and Peter Golde thought of extending C# to better integrate data querying. Peter, who was the C# compiler development lead at the time, was investigating the possibility of making the C# compiler extensible, specifically to support add-ins that could verify the syntax of domain-specific languages like SQL. Anders, on the other hand, was conceiving a deeper, more specific level of integration. He was thinking about a set of “sequence operators” that would operate on any collection that implemented IEnumerable, as well as remote queries for types that implemented IQueryable. Ultimately, the sequence operator idea gained the most support, and in early 2004 Anders submitted a paper about the idea to Bill Gates’s Thinkweek. The feedback was overwhelmingly positive. In the early stages of the design, a simple query had the following syntax:

sequence<Customer> locals = customers.where(ZipCode == 98112);

Sequence, in this case, was an alias for IEnumerable<T>, and the word “where” was a special operator understood by the compiler. The implementation of the where operator was a normal C# static method that took in a predicate delegate (that is, a delegate of the form bool Pred<T>(T item)). The idea was for the compiler to have special knowledge about the operator. This would allow the compiler to correctly call the static method and create the code to hook up the delegate to the expression.

Let’s suppose that the example above would be the ideal syntax for a query in C#. What would this query look like in C# 2.0, without any language extensions?

IEnumerable<Customer> locals = EnumerableExtensions.Where(customers,                                                     delegate(Customer c)         {             return c.ZipCode == 98112;         });

This code is frightfully verbose, and worse, it requires significant digging to find the relevant filter (ZipCode == 98112). And this example is simple; imagine how much more unreadable this would be with several filters, projections, and so forth. The root of the verbosity is the syntax required for anonymous methods. In the ideal query, the expression would require nothing but the expression to be evaluated. The compiler would then attempt to infer the context; for example, that ZipCode was really referring to the ZipCode defined on Customer. How to fix this problem? Hardcoding the knowledge of specific operators into the language didn’t sit well with the language design team, so they started looking for an alternate syntax for anonymous methods. They wanted it to be extremely concise, and yet not necessarily require more knowledge than the compiler currently needed for anonymous methods. Ultimately they devised lambda expressions.

 http://msdn.microsoft.com/msdnmag/issues/07/06/CSharp30/

 

4-Tier Architecture in ASP.NET with C#

Almost all of us must have heard about 3-Tier architecture but what is this 4-Tier architecture? What are the benefits and how it is different from other architectures?
 
Well, the architecture I am going to demonstrate here is just enhancement of 3-Tier archicture. In this architecture; you no need of writing long function parameters throughout the layers (as in traditionally 3-Tier archicture has to) and the actual objects of the application will be in a separate tier so that in future you can separately use these objects for enhancements. Change in the object definition can be done without touching the entire Business Access Layers …………

Let me explain you step-wise process of creatioin of 4-Tier architecture application.

In this application, I am going to take example of a Person that will have 3 properties: FirstName, LastName, Age. We will create a separate pages to insert these records (default.aspx) into database and list,update,delete records (list.aspx) from database.

In this application we will have following 4-Tiers
1. Business Object [BO]
2. Business Access Layer [BAL]
3. Data Access Layer [DAL]
4. UI (4-Tier) folder [UI]

Picture – 1 (Solution Explorer)

For simplicity reason, I have created separate folders for first 3-tiers into App_Code folder. You can create a separate projects for these tiers and add into forth tier (UI) solution.

Lets create above tiers one by one.

Business Object [BO – Person.cs]

Create a separate folder by right-clicking App_Code folder and name it as BO. Right click this folder and create a new .cs (Class) file named Person.cs. Write following code inside it.

– Hide Code

int m_PersonID = 0; 
    string m_FirstName = string.Empty; 
    string m_LastName = string.Empty; 
    int m_Age = 0; 

    #region Propertiers 
    public int PersonID 
    { 
        get { return m_PersonID; } 
        set { m_PersonID = value; } 
    } 

    public string FirstName 
    { 
        get { return m_FirstName; } 
        set { m_FirstName = value; } 
    } 

    public string LastName 
    { 
        get { return m_LastName; } 
        set { m_LastName = value; } 
    } 

    public int Age 
    { 
        get { return m_Age; } 
        set { m_Age = value; } 
    } 
    #endregion Properties

Here, we are first declaring 4 variables for corresponding properites and defining properties for them.This is your Business Object with all its properties/attributes to work with. Next step is to create Data Access Layer.

Data Access Layer [DAL – PersonDAL.cs]

The way you created BO folder inside App_Code folder, create another folder named DAL. Create a .cs file inside it and name it as PersonDAL.cs

Write following code inside it (You can copy-paste).

– Hide Code

using System; 
using System.Data; 
using System.Configuration; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using System.Data.SqlClient; 

/// <summary> 
/// Summary description for PersonDAL 
/// </summary> 
public class PersonDAL : IDisposable         
{ 
    string connStr = ConfigurationManager.ConnectionStrings["TutTestConn"].ToString(); 
    SqlConnection conn = null; 

	public PersonDAL() 
	{ 
        conn = new SqlConnection(connStr); 
        conn.Open(); 
	} 

    /// <summary> 
    /// Used to insert records into database 
    /// </summary> 
    /// <param name="p"></param> 
    /// <returns></returns> 
    public int Insert(Person person) 
    { 

        // If any duplicate recods found, then return 0 that will indicate in the UI that no records inserted as  

firstname already exists 
        if (CheckForDuplicateRecord(person.FirstName) > 0) 
            return 0; 

        // go ahead with inserting records into database 
        string strSql = "INSERT INTO Person (firstname, lastname, age) VALUES ('" + 
            person.FirstName + "', '" + person.LastName + "', " + person.Age + ")"; 

        SqlCommand dCmd = new SqlCommand(strSql, conn); 
        try 
        { 
            return dCmd.ExecuteNonQuery(); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            dCmd.Dispose(); 
        } 
    } 

    /// <summary> 
    /// Update record into database 
    /// </summary> 
    /// <param name="p"></param> 
    /// <returns></returns> 
    public int Update(Person person) 
    { 
        string connStr = ConfigurationManager.ConnectionStrings["TutTestConn"].ToString(); 
        string strSql = "UPDATE Person SET firstname = '" + person.FirstName + "', lastname = '" +  

person.LastName + "', " + 
            "age = " + person.Age + " WHERE PersonID = " + person.PersonID; 

        SqlConnection conn = new SqlConnection(connStr); 
        conn.Open(); 
        SqlCommand dCmd = new SqlCommand(strSql, conn); 
        try 
        { 
            return dCmd.ExecuteNonQuery(); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            dCmd.Dispose(); 
        } 
    } 

    /// <summary> 
    /// Load all records from database 
    /// </summary> 
    /// <returns></returns> 
    public DataTable Load() 
    { 
        string strSql = "SELECT * FROM Person ORDER BY FirstName"; 
        SqlConnection conn = new SqlConnection(connStr); 
        conn.Open(); 
        SqlDataAdapter dAd = new SqlDataAdapter(strSql, conn); 
        DataSet dSet = new DataSet(); 
        try 
        { 
            dAd.Fill(dSet, "PersonTable"); 
            return dSet.Tables["PersonTable"]; 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            dSet.Dispose(); 
            dAd.Dispose(); 
        } 
    } 

    /// <summary> 
    /// Search records from database using its Local connection  
    /// </summary> 
    /// <param name="person"></param> 
    /// <returns></returns> 
    public DataTable SearchFirstName(Person person) 
    { 
        string strSql = "SELECT * FROM Person WHERE firstname LIKE '%" + person.FirstName + "%'  

ORDER BY firstname";  
        SqlConnection localConn = new SqlConnection(connStr); 
        conn.Open(); 

        SqlDataAdapter dAd = new SqlDataAdapter(strSql, localConn); 
        DataSet dSet = new DataSet(); 
        try 
        { 
            dAd.Fill(dSet, "SearchResult"); 
            return dSet.Tables["SearchResult"]; 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            dSet.Dispose(); 
            dAd.Dispose(); 
            localConn.Close(); 
            localConn.Dispose(); 

            // As we are not using public connection here, so lets close it explicitely here 
            this.Dispose(); 
        } 

    } 

    /// <summary> 
    /// delete record from database 
    /// </summary> 
    /// <param name="person"></param> 
    /// <returns></returns> 
    public int delete(Person person) 
    { 
        string strSql = "delete Person WHERE PersonID = " + person.PersonID; 
        SqlCommand dCmd = new SqlCommand(strSql, conn); 
        try 
        { 
            return dCmd.ExecuteNonQuery(); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            dCmd.Dispose(); 
        } 
    } 

    /// <summary> 
    /// Check for duplicate records based on first name 
    /// </summary> 
    /// <param name="firstName"></param> 
    /// <returns></returns> 
    public int CheckForDuplicateRecord(string firstName) 
    { 
        string strSql = "select PersonID from Person WHERE FirstName = '" + firstName + "'"; 
        // check for the duplicate records 
        SqlCommand dCmd = new SqlCommand(strSql, conn); 
        try 
        { 
            object obj = dCmd.ExecuteScalar(); 
            if (obj != null) 
                return 1; 
            else 
                return 0; 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            dCmd.Dispose(); 
        } 
    } 

    #region IDisposable Members 

    public void Dispose() 
    { 
        conn.Close(); 
        conn.Dispose(); 
    } 

    #endregion 
}

In this class file, we have Insert, Update, delete, Load and few other supporting methods. In this class file, first I am getting the connectionstring from the web.config file in a class level variable named connStr and defining a class level SqlConnection variable. In the constructor of this class, I am instantiating the connection object with the connectionstring and opening the connection.

Instead of using above approach you can open and close connection in respective methods. Just for demonstration purpose, I have created SearchFirstName method that use its internal connection object and close when work is done.

For simplicity reason, I have not used Store Procedure to Insert, Update, delete or Load records. You can use stored procedure here in the way it is used normally. This was your Data Access Layer. Till now you have your Business Object and Data Access Layer ready. Now lets go to the third layer and create Business Access Layer.

Business Access Layer [BAL – PersonBAL.cs]

Again, right click App_Code folder and add a new folder named BAL. Create a new class file inside it and name it as PersonBAL.cs. Write following code inside it.

– Hide Code

using System; 
using System.Data; 
using System.Configuration; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 

/// <summary> 
/// Summary description for PersonBAL 
/// </summary> 
public class PersonBAL 
{ 
	public PersonBAL() 
	{ 

	} 

    /// <summary> 
    /// insert records into database 
    /// </summary> 
    /// <param name="person"></param> 
    /// <returns></returns> 
    public int Insert(Person person) 
    { 
        PersonDAL pDAL = new PersonDAL(); 
        try 
        { 
            return pDAL.Insert(person); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            pDAL.Dispose(); 
        } 
    } 

    /// <summary> 
    /// Update records into database 
    /// </summary> 
    /// <param name="person"></param> 
    /// <returns></returns> 
    public int Update(Person person) 
    { 
        PersonDAL pDAL = new PersonDAL(); 
        try 
        { 
            return pDAL.Update(person); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            pDAL.Dispose(); 
        } 
    } 

    /// <summary> 
    /// Load records from database 
    /// </summary> 
    /// <returns></returns> 
    public DataTable Load() 
    { 
        PersonDAL pDAL = new PersonDAL(); 
        try 
        { 
            return pDAL.Load(); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            pDAL.Dispose(); 
        } 
    } 

    /// <summary> 
    /// Search  
    /// </summary> 
    /// <param name="person"></param> 
    /// <returns></returns> 
    public DataTable SearchFirstName(Person person) 
    { 
        PersonDAL pDAL = new PersonDAL(); 
        try 
        { 
            return pDAL.SearchFirstName(person); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            pDAL = null; 
        } 
    } 

    /// <summary> 
    /// delete record from database 
    /// </summary> 
    /// <param name="person"></param> 
    /// <returns></returns> 
    public int delete(Person person) 
    { 
        PersonDAL pDAL = new PersonDAL(); 
        try 
        { 
            return pDAL.delete(person); 
        } 
        catch 
        { 
            throw; 
        } 
        finally 
        { 
            pDAL.Dispose(); 
        } 
    } 

}

Here, we are creating separate methods each for respective PersonDAL.cs methods here. As in our case we don’t have any business logic, so we are just instantiating the Data Access Layer objects, using its methods and and returning to UI (fourth layer, described later on).

You must have noticed here that in the try catch block, I am just writing throw; statement. This is because when any error will occur it will be send to the calling layers (in our case UI) and there we will handle it.

Till now, we have BO, BAL and DAL ready. Now we are left with our application face, I mean UI. Lets first create default.aspx file that will contain one form and textboxs that will be used to enter records.

User Interface – [UI]-Default.aspx

Create a separate folder in your UI solution named 4-Tier and add one .aspx page called Default.aspx (Picture – 2). In this page, we will write ASP.NET code to render textboxes and buttons. OnClick event of the button we will calll AddRecords method that will ultimately insert the records into database. Below is the code to render the asp.net form.

– Hide Code

<form id="form1" runat="server"> 
    <div> 
        <p><a href="List.aspx">List Records</a></p> 
        <asp:Label ID="lblMessage" runat="Server" ForeColor="red"  

EnableViewState="False"></asp:Label> 
        <table style="border:2px solid #cccccc;"> 
            <tr style="background-color:#ECF3AB;"> 
                <th colspan="3">Add Records</th> 
            </tr> 
            <tr> 
                <td> 
                    First Name: 
                </td> 
                <td> 
                    <asp:TextBox ID="txtFirstName" runat="Server"></asp:TextBox> 
                </td> 
                <td> 
                    <asp:RequiredFieldValidator ID="req1" runat="Server" Text="*"  

ControlToValidate="txtFirstName" Display="dynamic"></asp:RequiredFieldValidator> 
                </td> 
            </tr> 
            <tr> 
                <td> 
                    Last Name: 
                </td> 
                <td> 
                    <asp:TextBox ID="txtLastName" runat="Server"></asp:TextBox> 
                </td> 
                <td> 
                    <asp:RequiredFieldValidator ID="req2" runat="Server" Text="*"  

ControlToValidate="txtLastName" Display="dynamic"></asp:RequiredFieldValidator> 
                </td> 
            </tr> 
            <tr> 
                <td> 
                    Age: 
                </td> 
                <td> 
                    <asp:TextBox ID="txtAge" runat="Server" Columns="4"></asp:TextBox> 
                </td> 
                <td> 
                    <asp:RequiredFieldValidator ID="req3" runat="Server" Text="*" ControlToValidate="txtAge"  

Display="dynamic"></asp:RequiredFieldValidator> 
                    <asp:CompareValidator ID="Comp1" runat="Server" Text="Only integer"  

ControlToValidate="txtAge" Operator="DataTypeCheck" Type="Integer"></asp:CompareValidator> 
                </td> 
            </tr> 
            <tr> 
                <td> </td> 
                <td> 
                    <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="AddRecords" /> 
                </td> 
            </tr> 
        </table> 
    </div> 
    </form>

Picture – 2 (Default.aspx)

AddRecords method

– Hide Code

protected void AddRecords(object sender, EventArgs e) 
    { 
        //Lets validate the page first 
        if (!Page.IsValid) 
            return; 

        int intResult = 0; 
        // Page is valid, lets go ahead and insert records 
        // Instantiate BAL object 
        PersonBAL pBAL = new PersonBAL(); 
        // Instantiate the object we have to deal with 
        Person person = new Person(); 
        // set the properties of the object 
        person.FirstName = txtFirstName.Text; 
        person.LastName = txtLastName.Text; 
        person.Age = Int32.Parse(txtAge.Text); 

        try 
        { 
            intResult = pBAL.Insert(person); 
            if (intResult > 0) 
                lblMessage.Text = "New record inserted successfully."; 
            else 
                lblMessage.Text = "FirstName [<b>"+ txtFirstName.Text +"</b>] alredy exists, try another  

name"; 

        } 
        catch (Exception ee) 
        { 
            lblMessage.Text = ee.Message.ToString(); 
        } 
        finally 
        { 
            person = null; 
            pBAL = null; 
        }         
    }

In the above method, I am doing following things mainly:
1. Instantiating BAL object
2. Instantiating BO object
3. Settinng properties of BO object by the textbox values
4. Calling Insert method of the BAL object and passing BO object as parameter [pBAL.Insert(person)] in try block
5. Checking for number of records affected, If the number is more than zero, I am writing Success message otherwise Duplicate records found.
6. If any layer will throw any error, I am catching it and displaying to the user in throw block.
7. Whatever objects I had instantiated, I am specifying their values to null to let the GC know that I am no more going to use them.

User Interface – [UI]-List.aspx

In this page, I am going to use a GridView to List, Modify and delete records from the database. Create an .aspx page in the same 4-Tier folder named List.aspx (Picture – 3). Following is the code for the GridView that will do data manipulation for us.

– Hide Code

<form id="form1" runat="server"> 
    <div> 
        <p><a href="Default.aspx">Add Record</a></p> 
        <asp:Label ID="lblMessage" runat="Server" ForeColor="red"  

EnableViewState="False"></asp:Label> 
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"  

GridLines="None" 
         DataKeyNames="PersonID" AutoGenerateEditButton="True" AutoGenerateColumns="False" 
          OnRowEditing="EditRecord" OnRowUpdating="UpdateRecord"  

OnRowCancelingEdit="CancelRecord" 
           OnRowDeleting="deleteRecord"> 
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> 
            <RowStyle BackColor="#EFF3FB" /> 
            <EditRowStyle BackColor="#2461BF" /> 
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> 
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> 
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> 
            <AlternatingRowStyle BackColor="White" /> 
            <Columns> 
                <asp:BoundField DataField="PersonID" HeaderText="Person ID" /> 
                <asp:TemplateField HeaderText="First Name"> 
                    <ItemTemplate> 
                        <%# Eval("FirstName") %> 
                    </ItemTemplate> 
                    <EditItemTemplate> 
                        <asp:TextBox ID="txtFName" runat="Server" Text='<%# Eval("FirstName")  

%>'></asp:TextBox> 
                    </EditItemTemplate> 
                </asp:TemplateField> 
                <asp:TemplateField HeaderText="Last Name"> 
                    <ItemTemplate> 
                        <%# Eval("LastName") %> 
                    </ItemTemplate> 
                    <EditItemTemplate> 
                        <asp:TextBox ID="txtLName" runat="Server" Text='<%# Eval("LastName")  

%>'></asp:TextBox> 
                    </EditItemTemplate> 
                </asp:TemplateField> 
                <asp:TemplateField HeaderText="Age"> 
                    <ItemTemplate> 
                        <%# Eval("Age") %> 
                    </ItemTemplate> 
                    <EditItemTemplate> 
                        <asp:TextBox ID="txtAge" runat="Server" Text='<%# Eval("Age") %>'></asp:TextBox> 
                    </EditItemTemplate> 
                </asp:TemplateField> 
                <asp:TemplateField HeaderText="delete?"> 
                    <ItemTemplate> 
                        <span onclick="return confirm('Are you sure to delete?')"> 
                            <asp:LinkButton ID="lnBD" runat="server" Text="delete"  

CommandName="delete"></asp:LinkButton> 
                        </span> 
                    </ItemTemplate> 
                </asp:TemplateField> 

            </Columns> 
        </asp:GridView> 
    </div> 
    </form>

Picture – 3 (List.aspx)

On the OnRowEditing, OnRowUpdating, OnRowCancelEdit and OnRowDeleting events we are calling respective methods to do data manipulation. Following are codes to bind the GridView and methods that will fire on GridView events.

– Hide Code

protected void Page_Load(object sender, EventArgs e) 
    { 
        if (!IsPostBack) 
            BindGrid(); 
    } 

    private void BindGrid() 
    { 
        PersonBAL p = new PersonBAL(); 
        try 
        { 
            GridView1.DataSource = p.Load(); 
            GridView1.DataBind(); 
        } 
        catch (Exception ee) 
        { 
            lblMessage.Text = ee.Message.ToString(); 
        } 
        finally 
        { 
            p = null; 
        } 
    } 

    /// <summary> 
    /// Fired when Cancel button is clicked 
    /// </summary> 
    /// <param name="sender"></param> 
    /// <param name="e"></param> 
    protected void CancelRecord(object sender, GridViewCancelEditEventArgs e) 
    { 
        GridView1.EditIndex = -1; 
        BindGrid(); 
    } 

    /// <summary> 
    /// Fires when Edit button is clicked 
    /// </summary> 
    /// <param name="sender"></param> 
    /// <param name="e"></param> 
    protected void EditRecord(object sender, GridViewEditEventArgs e) 
    { 
        GridView1.EditIndex = e.NewEditIndex; 
        BindGrid(); 
    } 

    /// <summary> 
    /// Fires when Update button is clicked 
    /// </summary> 
    /// <param name="sender"></param> 
    /// <param name="e"></param> 
    protected void UpdateRecord(object sender, GridViewUpdateEventArgs e) 
    { 
        int personID = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString()); 
        int intResult = 0; 
        GridViewRow row = GridView1.Rows[e.RowIndex]; 

        TextBox tFN = (TextBox) row.FindControl("txtFName"); 
        TextBox tLN = (TextBox)row.FindControl("txtLName"); 
        TextBox tAge = (TextBox)row.FindControl("txtAge"); 

        // instantiate BAL 
        PersonBAL pBAL = new PersonBAL(); 
        Person person = new Person(); 
        try 
        { 
            person.PersonID = personID; 
            person.FirstName = tFN.Text; 
            person.LastName = tLN.Text; 
            person.Age = Int32.Parse(tAge.Text); 
            intResult = pBAL.Update(person); 
            if (intResult > 0) 
                lblMessage.Text = "Record Updated Successfully."; 
            else 
                lblMessage.Text = "Record couldn't updated"; 
        } 
        catch (Exception ee) 
        { 
            lblMessage.Text = ee.Message.ToString(); 
        } 
        finally 
        { 
            person = null; 
            pBAL = null; 
        } 

        GridView1.EditIndex = -1; 
        // Refresh the list 
        BindGrid(); 
    } 

    /// <summary> 
    /// fires when delete button is clicked 
    /// </summary> 
    /// <param name="sender"></param> 
    /// <param name="e"></param> 
    protected void deleteRecord(object sender, GridViewdeleteEventArgs e) 
    { 
        int personID = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());  

        // instantiate BAL 
        PersonBAL pBAL = new PersonBAL(); 
        Person person = new Person(); 
        try 
        { 
            person.PersonID = personID; 
            pBAL.delete(person); 

            lblMessage.Text = "Record deleted Successfully."; 
        } 
        catch (Exception ee) 
        { 
            lblMessage.Text = ee.Message.ToString(); 
        } 
        finally 
        { 
            person = null; 
            pBAL = null; 
        } 

        GridView1.EditIndex = -1; 
        // Refresh the list 
        BindGrid(); 
    }

http://www.dotnetfunda.com/articles/article18.aspx

Good Links: ASP.NET, ASP.NET AJAX, Visual Studio, Silverlight and IIS 7.0

LATEST LINKS 

ASP.NET

  • Cleanup Inactive Anonymous Users: ASP.NET 2.0 has support for an optional feature called “anonymous users” – which enables you to optionally track and store profile data for non-authenticated users visiting your sites (read K. Scott Allen’s article here for more details on how this works).  Omar Al Zabir published this handy article that describes how to “clean up” this anonymous user data periodically to avoid storing it forever in your database.

ASP.NET AJAX

Visual Studio

  • Speed up Visual Studio 2005: Here are a bunch of useful suggestions for how you can speed up your VS 2005 IDE environment.  For other performance suggestions, please review my previous blog posts here and here.
  • SQL Database Publishing Wizard Now in VS 2008: One of the post-Beta2 features that we are adding for the final VS 2008 release is the inclusion of a SQL database publishing wizard inside Visual Studio.  This will be supported in both the free Visual Web Developer Express edition as well as the full Visual Studio, and provides an easy way to script out your database schema and data to .SQL files.  You can then copy these files to a remote hoster to deploy a database.  You can learn more about how this works from my earlier posts here and here which describes how to use a previous standalone tool with this functionality (all of this functionality is now built-in to VS 2008).
  • Convert Selected Text to a Label: Fons Sonnemans has a nice article that demonstrates a cool macro that can be used to automatically convert literal text in your .aspx files to a <asp:label> control.  A neat shortcut.

Silverlight

  • Building Silverlight Application using .NET MIX UK Videos: The videos from the recent MIX UK conference are now live on the web and can be watched (for free) by anyone.  If you are interested in Silverlight I recommend watching my Building Silverlight Application using .NET (Part 1 and 2) talks.  You can download the slides + samples for my talks here
  • Silverlight Kids Doodler Sample: David Anson has published a neat sample that demonstrates how to build a neat Paint program in Silverlight.  Ideal for small children. 🙂
  • Great Silverlight Tutorials: Gerard Leblanc has a great blog, and publishes regular blog posts on Silverlight.  Check out this list of some of some of his tutorials.

IIS 7.0

  • Using LINQ with Microsoft.Web.Administration: Carlos from the IIS7 dev team has a really cool blog post that shows how you can use LINQ with .NET 3.5 to easily query and manipulate the new web server administration API introduced with IIS7.  For more information on this new API, you can also read an old post of mine here.

Diffrence between DELETE TABLE and TRUNCATE TABLE commands

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE TABLE may not be used on tables participating in an indexed view.

Microsoft decided to release source code of dot net framework

Great News from Microsoft Corporation. Microsoft has decided to provide the source code of the dot net frame work class library. The source code will be provided with the final release of the VS 2008 and Dot net framework 3.5. The source code is provided under the Microsoft reference License. You can read the full post hereI know we can see the code using reflector, but the ability to debug with the source code will be just great. Think how great it would be to be able to press F11 (Step Into) at the Gridview.databind() and see whats happening inside the databind method. IT would be great for all developers of dot net. Just wait for this one and enjoy debugging…