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

Advertisements

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…

Turn Off Unnecessary Windows XP Services

Turning off unnecessary services in Windows XP can greatly reduce your
exploit risk, while improving system performance. It’s a good time to
inject that often there are all sorts of “download optimizers” and
other cute programs that vendors like to push on users. Most of the
time, installing such things slows your computer down at best. It
could subject you to potential security risks. The first rule is “If
you don’t know you need it, you probably don’t.”

Unnecessary services don’t just subject you to security risk. They
also slow down the operation of your computer. So, don’t get lazy here
and think you can just deal with the infections later. Go ahead and
turn that junk off and recapture your system from these resource hogs.
You get to services by going to Control Panel, Administrative Tools,
then Services. You should see a long list of services, some running
and some dormant. Use this checklist to help determine which services
you can live without.

If you don’t know how to find Windows Services in Windows XP, click on
Start, Control Panel, Administrative Tools, and Services. Below is a
simple step by step to finding and changing your services on Windows
XP. Keep in mind that your view settings may make your’s appear
slightly different, but will be the same basic path.

Click on start, then click on Control Panel.

In Control Panel, click on Administrative Tools

In Administrative Tools, click on Services.

Choose the service you wish to modify.

Once you select the service you wish to modify, you have several
buttons to turn the service off immediately, drop down choices to
disable a service, make it autmatic, or make it manual.

Windows XP Pro (and Home); Stuff to turn off:
Each service is listed as it is in Microsoft’s WIndows XP
Professional. These should be similar in Microsoft’s XP Home as well.
Under each is the definition given in the Services Manager.

 a.. Alerter
 Notifies selected users and computers of administrative alerts. If
the service is stopped, programs that use administrative alerts will
not receive them. If this service is disabled, any services that
explicitly depend on it will fail to start.
   Comment: I don’t want my personal computer telling me anything,
ever. Shut up and work! There’s few things I find more annoying than a
computer constantly wanting to interact with me while I’m using it to
do work or entertain myself. A computer is a tool, not a friend or
work companion. No Hal, I don’t want to talk to you. Perhaps there’s a
software vendor that can give you a compelling reason why you need
this service, but for most home and SOHO PC use it’s just an
unnecessary service taking up resources and providing risk. Unless you
are running a product that requires this service, disable it.

 b.. Application Layer Gateway Service
 Provides support for 3rd party protocol plug-ins for Internet
Connection Sharing and the Internet Connection Firewall
   Comment: Do you want to share your internet connection? That’s an
article waiting to be written. Let me be clear. Since you can buy a
router for $50 or less, and Windows does an awful job routing, using a
computer to gateway your other computers to the internet is just
stupid. “What about firewalling and admission control?” Well, that’s
not going to be done through the built-in internet sharing tools. So,
we”re not talking about that. If you use a personal computer to
gateway your other computers to the internet (and calling it a server
doesn’t change the reality), you are wasting resources. Buy a $50
router, or a $1000 router for that matter. But, buy a discrete device
that is designed to do the job. Use hardware based firewalling (OK,
it’s all based on software – but I mean a boxed solution, not software
installed on a PC that’s prone to lose autonomy). And, what about all
those cute third-party firewalling tools that plug in to this thing?
Man, give me a break. If it runs on top of your Windows installation,
it’s not a real firewall. Unless this is required by a product you
think is necessary, disable it.

 c.. Automatic Updates
 Enables the download and installation of critical Windows updates.
If the service is disabled, the operating system can be manually
updated.
   Comment: There’s only two options that may make sense with this
service. You should either set it to disable or manual. I’d disable
it. Automatic Updates is designed as a tool to aid Microsoft in
controlling their product. Props to Microsoft for trying to protect
their product from piracy. But, some updates have been known to cause
problems. Use it when you need it, and disable it when you don’t,
unless you’re too lazy to do updates on your own. Don’t you wonder why
all the computers in big, well managed networks don’t run Automatic
Updates? It’s mostly because managers of big networks create their own
update policies. If you more completely understand the thinking that
goes into deciding whether or not to distribute an update, you could
better administer your own PC. Either turn it on and assume the risk,
or turn it off and regularly visit Mircosoft’s update and news page
(discussed more below).

 d.. Background Intelligent Transfer Service
 Uses idle network bandwidth to transfer data.
   Comment: This is one of those tools they require you turn on to
enable Automatic Updates. Think about it. It connects your PC to the
internet or network and works behind your back to do stuff you didn’t
explicitly tell it to do. It sounds like a great tool to help hackers
collect data from your PC and slowly seep it back to their lair.
Unless it’s immediately required, disable it. It you use it and then
go for some time with no need to use it, disable it. If you can’t
remember to keep your PC updated with the latest security fixes,
you’ll need it.

 e.. ClipBook
 Enables ClipBook Viewer to store information and share it with
remote computers. If the service is stopped, ClipBook Viewer will not
be able to share information with remote computers. If this service is
disabled, any services that explicitly depend on it will fail to
start.
   Comment: So, you want to copy stuff to your clipbook and allow
remote computers to access it? I don’t. There may be a software vendor
that requires this service to run. I’ve yet to find it useful. I
suggest you disable it.

 f.. Computer Browser
 Maintains an updated list of computers on the network and supplies
this list to computers designated as browsers. If this service is
stopped, this list will not be updated or maintained. If this service
is disabled, any services that explicitly depend on it will fail to
start.
   Comment: If you are on a network with other computers, and need to
see them, this may be a useful tool. Otherwise, disable it.

 g.. Cryptographic Services
 Provides three management services: Catalog Database Service, which
confirms the signatures of Windows files; Protected Root Service,
which adds and removes Trusted Root Certification Authority
certificates from this computer; and Key Service, which helps enroll
this computer for certificates. If this service is stopped, these
management services will not function properly. If this service is
disabled, any services that explicitly depend on it will fail to
start.
   Comment: It is very necessary if you are passing certificates for
networking. Unless you are in a large corporate network where
connections are managed through authentication, this is unnecessary;
disable it.

 h.. Distributed Transaction Coordinator
 Coordinates transactions that span multiple resource managers, such
as databases, message queues, and file systems. If this service is
stopped, these transactions will not occur. If this service is
disabled, any services that explicitly depend on it will fail to
start.
   Comment: Extremely few personal computers will require this
service. If you use it, you may want to review the reasons it is being
used. Unless you are accessing network filesystems and databases,
disable it.

 i.. DNS Client
 Resolves and caches Domain Name System (DNS) names for this
computer. If this service is stopped, this computer will not be able
to resolve DNS names and locate Active Directory domain controllers.
If this service is disabled, any services that explicitly depend on it
will fail to start.
   Comment: It’s typically good to leave this on.

 j.. Error Reporting Service
 Allows error reporting for services and applications running in non-
standard environments.
   Comment: Erorr reporting is very useful, if you know what to do
with the errors or you are running software that adjusts based on
error reporting. This is that annoying “feature” in Windows that
constantly pops up wanting to ship information about your software
failures to Redmond. People promise me it helps find problems and
solutions. I’ve mostly seen problem reports that you could as easily
search out yourself. If you’re advanced enough to use this, you’ll
likely use a search engine just as well. Chances are, the best thing
for you to do is disable it.

 k.. Help and Support
 Enables Help and Support Center to run on this computer. If this
service is stopped, Help and Support Center will be unavailable. If
this service is disabled, any services that explicitly depend on it
will fail to start.
   Comment: I don’t find this service useful, other than sucking up
resources. If you know how to use Google, I’d disable it.

 l.. Human Interface Device Access
 Enables generic input access to Human Interface Devices (HID), which
activates and maintains the use of predefined hot buttons on
keyboards, remote controls, and other multimedia devices. If this
service is stopped, hot buttons controlled by this service will no
longer function. If this service is disabled, any services that
explicitly depend on it will fail to start.
   Comment: Whether or not you should disable this service, depends
on other services you need. If you don’t know, turn it off and see if
it breaks anything. It says that it deals with hotkeys, however all
the system hotkeys that most of us enjoy aren’t controlled by this
service, they are built into the core OS. Control C, for example, to
copy and Control V to past, do not stop working when you turn this
service off. It seems this has more to do with specific hotkeys that a
software vendor may want to insert into their installed program or
internet product. Until you see a reason for it, I’d turn this one
off. Personally, I consider relying on such services to be lazy
programming. But, there may be good reason for using it if it’s more
efficient.

 m.. Indexing Service
 Indexes contents and properties of files on local and remote
computers; provides rapid access to files through flexible querying
language.
   Comment: To date, nobody has shown me real system performance
improvements with this technology. Keep in mind, I’m limited in this
conversation to Windows. Indexing is very useful. Indexing databases
is very useful. Indexing your computer isn’t very useful at all.
Typically, if you are on a network, you know where on a network to
find your chosen data. If you are not on a network, there’s no real
performance enhancement to this service that justifies the complexity
and resource use. Chances are good you should disable it.

 n.. IMAPI CD-Burning COM Service
 Manages CD recording using Image Mastering Applications Programming
Interface (IMAPI). If this service is stopped, this computer will be
unable to record CDs. If this service is disabled, any services that
explicitly depend on it will fail to start.
   Comment: Obviously, there may be some usefulness leaving this
service as manual, if you have a CD burner installed. If you don’t,
disable it.

 o.. Internet Connection Firewall (ICF) / Internet Connection Sharing
(ICS)
 Provides network address translation, addressing, name resolution
and/or intrusion prevention services for a home or small office
network.
   Comment: This tool does a great job of complicating my internet
connection and slowing down transactions. It’s not likely this tool is
sophisticated enough to make a major impact in your system’s
performance. You should disable it.

 p.. Messenger
 Transmits net send and Alerter service messages between clients and
servers. This service is not related to Windows Messenger. If this
service is stopped, Alerter messages will not be transmitted. If this
service is disabled, any services that explicitly depend on it will
fail to start.
   Comment: Turn this thing off! It’s a simple kit for anybody that
can connect via any network to your computer to access your system and
do things you don’t want them to. Disable it.

 q.. Net Logon
 Supports pass-through authentication of account logon events for
computers in a domain.
   Comment: Unless you need this to operate inside a domain, it’s
likely not necessary or useful. If you are using a home or SOHO PC and
don’t have a local domain based network, disable it.

 r.. NetMeeting Remote Desktop Sharing
 Enables an authorized user to access this computer remotely by using
NetMeeting over a corporate intranet. If this service is stopped,
remote desktop sharing will be unavailable. If this service is
disabled, any services that explicitly depend on it will fail to
start.
   Comment: Do you really want a built in tool to share control of
your desktop over your network connection? There are better tools for
doing this kind of work, if needed. If someone you buy software from
insists you let them use this tool to help you install it one time,
then enable it and disable it immediately afterward. For typical use,
you should disable it.

 s.. Remote Desktop Help Session Manager
 Manages and controls Remote Assistance. If this service is stopped,
Remote Assistance will be unavailable. Before stopping this service,
see the Dependencies tab of the Properties dialog box.
   Comment: Refer to NetMeeting. If you don’t want to share control
of your computer through your network, disable it.

 t.. Remote Procedure Call (RPC) Locator
 Manages the RPC name service database.
   Comment: There are some network programs and protocols that
require this to be turned on. Chances are you could just turn it off
and see if you break anything. If you are using a single PC in your
home or SOHO, it’s likely just a security risk. If you don’t know you
need it, disable it.

 u.. Remote Registry
 Enables remote users to modify registry settings on this computer.
If this service is stopped, the registry can be modified only by users
on this computer. If this service is disabled, any services that
explicitly depend on it will fail to start.
   Comment: Seems self-explanatory. You can enable this service to
help remote people or programs change your registry. Great hacker tool
if you can’t secure it. Disable it.

 v.. System Restore Service
 Performs system restore functions. To stop service, turn off System
Restore from the System Restore tab in My Computer->Properties
   Comment: This is almost useless if you ever have a problem with
damaged drives, corrupted data, or malware. It uses a lot of resources
and isn’t useful for most people. You can turn it on before you
install a big piece of software. This service allows you to backup to
a previous system should you mess your’s up with an installation of
software or a modification to your system settings, usually registry
damage. To improve system performance and take the minor risk of not
being able to make your computer work like it did yesterday, disable
it.

 w.. TCP/IP NetBIOS Helper
 Enables support for NetBIOS over TCP/IP (NetBT) service and NetBIOS
name resolution.
   Comment: Very few people use NetBIOS at home. This is the Windows
built-in protocol for simple networking. You may need it. Otherwise,
disable it.

 x.. Telephony
 Provides Telephony API (TAPI) support for programs that control
telephony devices and IP based voice connections on the local computer
and, through the LAN, on servers that are also running the service.
   Comment: If you use telephony, you probably use discrete devices
or proprietary services that don’t rely on this service. However, you
do need this servive if you use a modem to connect to the ineternet.
If you don’t specifically need the Microsoft Telephony service,
disable it. If you use a modem to connect to the internet, leave it
enabled.

 y.. Telnet
 Enables a remote user to log on to this computer and run programs,
and supports various TCP/IP Telnet clients, including UNIX-based and
Windows-based computers. If this service is stopped, remote user
access to programs might be unavailable. If this service is disabled,
any services that explicitly depend on it will fail to start.
   Comment: There’s just not a circumstance where I can imagine that
turning this service on is a good idea. Unless you need to let people
telnet into your computer and have a really good reason for doing so,
disable it.

 z.. Terminal Services
 Allows multiple users to be connected interactively to a machine as
well as the display of desktops and applications to remote computers.
The underpinning of Remote Desktop (including RD for Administrators),
Fast User Switching, Remote Assistance, and Terminal Server.
   Comment: As I’ve said above, there’s better tools for remote
desktop administration. The idea of Terminal Services is to allow
remote desktop administration of a system, like the user was on the
actual console. In almost all circumstances you should disable it.

 aa.. Themes
 Provides user experience theme management.
   Comment: Themes are cute and bloated. Enabling themes is not a
good way to increase performance, but you may think it’s neat. If you
aren’t addicted to cute desktop eye candy, disable it.

 ab.. Uninterruptible Power Supply
 Manages an uninterruptible power supply (UPS) connected to the
computer.
   Comment: Unless you are using a UPS on your computer and it has
the capability of managing the system, disable it.

 ac.. Upload Manager
 Manages synchronous and asynchronous file transfers between clients
and servers on the network. If this service is stopped, synchronous
and asynchronous file transfers between clients and servers on the
network will not occur. If this service is disabled, any services that
explicitly depend on it will fail to start.
   Comment: If you are not in a local network sharing data (files and/
or services), disable it.

 ad.. Windows Time
 Maintains date and time synchronization on all clients and servers
in the network. If this service is stopped, date and time
synchronization will be unavailable. If this service is disabled, any
services that explicitly depend on it will fail to start.
   Comment: Sometimes it works. Unless you are really needing your
time to sync to something running a Windows time server, disable it.

 ae.. Wireless Zero Configuration
 Provides automatic configuration for the 802.11 adapters
   Comment: Unless you use 802.11 devices, disable it.

 af.. Workstation
 Creates and maintains client network connections to remote servers.
If this service is stopped, these connections will be unavailable. If
this service is disabled, any services that explicitly depend on it
will fail to start.
   Comment: If you are not in a local network sharing data (files and/
or services), disable it.

If you turn off all the services suggested above and try to use
Automatic Updates via WindowsUpdate.Microsoft.com, you will likely see
a message something like this:

Windows Update cannot continue because a required service application
is disabled. Windows Update requires the following services:

“Automatic Updates enables detection, downloading, and installation of
critical updates for your computer.

Background Intelligent Transfer Service (BITS) enables faster,
restartable downloading of updates.

Event Log logs Windows Update events for troubleshooting. To ensure
that these services are enabled:”

It’s easy to just go back to Services, and turn these services on as
you need them. An operating system shouldn’t need daily updates to
run. And, the more services you run, the more likely you are to need
updates. See a circle here? Occasionally, a little laziness won’t kill
you. Though you could just go to Technet (Microsoft’s only support for
IT professionals) and get all your news and update files with
descriptions of their efficacy and safety, you may occasionally just
want to veg out and let Microsoft do the work for you. You should
still read each update and decide for yourself whether it makes sense.
Some of them are flat out bad news. But, turning up these services for
a few minutes to run Automatic Updates may be a shortcut to periodic
updates.

So, let’s look at the services they want you to turn on.

Automatic Updates
Background Intelligent Transfer Service
Event Log

I haven’t a clue why you need Background Intelligent Transfer Services
to run so you can go to a website, download, and install service
packs. But, you can turn it, and the others, on and then turn it back
off when you are done. It’s just three services.

If you turn off all the services suggested above and try to use
Automatic Updates via WindowsUpdate.Microsoft.com, you will likely see
a message something like this:
 Windows Update cannot continue because a required service
application is disabled. Windows Update requires the following
services:
 Automatic Updates enables detection, downloading, and installation
of critical updates for your computer.
 Background Intelligent Transfer Service (BITS) enables faster,
restartable downloading of updates.
 Event Log logs Windows Update events for troubleshooting. To ensure
that these services are enabled:
It’s easy to just go back to Services, and turn these services on as
you need them. An operating system shouldn’t need daily updates to
run. And, the more services you run, the more likely you are to need
updates. See a circle here? Occasionally, a little laziness won’t kill
you. Though you could just go to Technet (Microsoft’s only support for
IT professionals) and get all your news and update files with
descriptions of their efficacy and safety, you may occasionally just
want to veg out and let Microsoft do the work for you. You should
still read each update and decide for yourself whether it makes sense.
Some of them are flat out bad news. But, turning up these services for
a few minutes to run Automatic Updates may be a shortcut to periodic
updates.

So, let’s look at the services they want you to turn on.

 1.. Automatic Updates
 2.. Background Intelligent Transfer Service
 3.. Event Log
I haven’t a clue why you need Background Intelligent Transfer Services
to run so you can go to a website, download, and install service
packs. But, you can turn it, and the others, on and then turn it back
off when you are done. After all, it’s just three services.

If you take a minimalist’s point of view to running both software and
services on your computer, it will perform faster and more safely than
it will if you just randomly load anything anyone tells you to. To
better secure your PC, stick to a mindset that if you don’t absolutely
need a service running right now, you should just turn it off.

For those of you that break stuff when you turn off services I suggest
are unnecessary.
If you turn off all the stuff that blatantly doesn’t have anything to
do with the network, you should be fine. Then, turn off one thing at a
time that you /think/ doesn’t support your network connection. If you
lose your connection, turn the service back on. Next, and this part is
very important, make sure your network settings are accurate and set
for “on” so you can reconnect. You should find out how your PC
connects to your local network before you get started and document it.
But, anything you turn off that breaks something needs to be carefully
examined and documented (write it down somewhere).

Just because you turn a service back on, doesn’t mean your broken
software will magically start working again. For example, remember the
issue of using a computer in your network to manage your connection?
If that’s how you connect, you’ll have to reconfigure that connection
to get online if you kill it by killing a service. Likely, Computer
Browser will cause this kind of problem. Also, just enabling the
service doesn’t turn it on. You need to manually restart it, since the
start, enable, stop, settings go into action on bootup. So, if you
don’t want to wait til your box reboots, you’ll need to manually turn
off the service if you want it disabled, and manually turn it on if
you want to enable it (and see if that given service is your
problem).

What about System Restore? Well, I don’t like it. If I had a dollar
for every time someone fouled up their PC, ran system restore, and
were amazed that all wasn’t suddenly happy sunshine, I’d have a really
nice vacation home. I realize it makes life easier for newbies that
randomly screw stuff up and can’t be hassled with learning the things
they use. But, for most cases, it’s a great tool for virus and worms
to avoid capture. You delete them, they have a backup switch to
recapture control through the restore program. Many antivirus programs
don’t work properly when you have this feature enabled. You can leave
it on if you’re a fan. Perhaps it makes you feel good, like it would
make everything right again if you had it around. But, it won’t save
you from a serious problem. Backups and safe computer practices will.

You need to learn enough about your PC to run a functional backup and
restoration of your critical data. If you can’t do this, you’re just
waiting to lose everything.

Don’t be afraid to learn more about your PC. It’s one thing to run
through a checklist of stuff I suggest you don’t need. But, it’s not a
guarantee that I address every concern for every user. With a few
hours here and there of learning now, you’ll save yourself a lot of
frustration later. The things I learned about Windows five and six
years ago still serve me today. The things I learned about networks
running Unix systems serve me every day, no matter what operating
system I may run.

Microsoft’s Programming Language : F#

F#Combining the efficiency, scripting, strong typing and productivity of ML with the stability, libraries, cross-language working and tools of .NET. F# is a programming language that provides the much sought-after combination of type safety, performance and scripting, with all the advantages of running on a high-quality, well-supported modern runtime system. F# gives you a combination of

The only language to provide a combination like this is F# (pronounced FSharp) – a scripted/functional/imperative/object-oriented programming language that is a fantastic basis for many practical scientific, engineering and web-based programming tasks. F# is a pragmatically-oriented variant of ML that shares a core language with OCaml. F# programs run on top of the .NET Framework. Unlike other scripting languages it executes at or near the speed of C# and C++, making use of the performance that comes through strong typing. Unlike many statically-typed languages it also supports many dynamic language techniques, such as property discovery and reflection where needed. F# includes extensions for working across languages and for object-oriented programming, and it works seamlessly with other .NET programming languages and tools.For further information, read about F# in more detail, download the F# distribution, read the getting started pages (a guide to installation and running your first program), go to the F# Manual, and learn about the growing F# Community. A short FAQ is also included on this site.Some Starting points

http://fsharpnews.blogspot.com/