Hello All, We are going to start new batch from next week. message/call or mail us for more details.

22 February 2014

Views in SQL Server Database - DotNet Brother

Whenever you want to provide different types of restriction to different user such as different-2 users can see different records in same table then we can use the concept of views. Views can help in simplifying query execution when the query involves retrieving data from multiple tables by applying join.
“A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data.”
Views ensure data security by applying certain types of restriction on user table such as
  • Specific rows of a table by using where clause. 
  • Specific columns of a table base on certain condition.
  • Rows filled by using joins.
  •  Subset of another view or a subset of views and tables.

Syntax for creating View

create view view_name as
select_statement
where
     create is a keyword which is used to create a database object
     view is a keyword which indicates that object that is created is a view
                view_name  is any valid name to your view as is a keyword select_statement is a select statement which is valid combination of join, subquery etc.

Some example which demonstrate the use of view


create view studentView as
select sid,sname from student

create view studentView1 as
select sid,sname,scity from student
where sid between 'S0001' and 'S0005'

Executing view that was early created

1) select * from studentView
Working with Views in Database
2)  select * from studentView1
Working with Views in Database

Creating an index view by using create index statement

By default when we create any view then index is not created on that view. You can create index on the view as well as you create index on table by using create index statement.

Example to demonstrate creating index on view

Before we can create index on view be have to bound view to the schema at the time of creation.
1)      Binding view to the schema by using following statement
alter view studentView1 with schemabinding as
select sid,sname,scity from student
where sid between 'S0001' and 'S0005'
2)      Creating index on view
create unique clustered index sid_student on studentView1(sid)

Altering Views

When you want to change the structure of the underlying tables such as adding new columns then we use alter commands. You can modify view without affecting its dependent objects.

Syntax for modifying view by using alter command

alter view view_name
as select_statement

Example of altering view

alter view studentView1 with schemabinding as
select sid,sname,scity from student
where sid between 'S0001' and 'S0005'

Deleting Views from database

The syntax of the drop view statement is:
                drop view view_name

Example which demonstrate use of droping view

drop view studentView1

Renaming Views

At times for security permission we need to rename an existing view. For renaming an existing view use sp_rename system stored procedure.

Syntax for renaming views

sp_rename old_view_name, new_view_name
where
     old_view_name represents name of the old view that you want to rename
     new_view_name represents name of new view that you want

Example of renaming views

sp_rename studentView1, stdView1

While renaming view mention following things


  • The view should be in current database.
  • The new name for the view must follow the rules for identifier.
  • No any other view stored in the database with new name.
  • The view can only be renamed by its owner
Posted by at 2 comments:

Registration Page Using N-Tier Architecture in ASP.NET - DotNet Brother

 N-tier application architecture provides a model for developers to create a flexible and reusable application by breaking up an application into tier. The n-Tier application has mainly three tiers or layers; they are called:
1.   Presentation Layer
2.  Business Logic Layer
3.  Data Access Layer
Each layer interacts with the layer directly below, and has specific function to perform.
The Presentation Layer is responsible for displaying the user interface to the end user. The programmer uses this layer for designing the user interface and to transfer data. In ASP.NET, ASPX pages, user controls, server is used to support the presentation layer.
The Business Logic Layer contains all the calculations and Business Rule validations that are required in the application.
Business Logic Layer is the class in which we write functions that get data from Presentation Layer and send that data to database through Data Access Layer. The business layer works as a go-between to transfer the data from presentation layer. Its responsibility is to validate the business rules of the component and communicating with the Data Access Layer.
The Data Access Layer gets the data from the business layer and sends it to the database or vice versa. DAL is responsible for accessing data and forwarding it to BLL. In an ASP.NET n-Tiered architecture, web pages do not make direct calls to the database. A given layer only communicates with its adjacent layers.
Now, Talking about What is the need to divide our code in 3-Tier Architecture? Separation of User Interface, Business Logic and Database Access has many advantages.

  • Reusability of Business Logic Component will help you for Quick Development. Let’s take an example : We have a module that’s functionality is to perform Adding New Records, Updating Existing Record, Deleting Existing Record and Finding the Record in the System. As this component is developed and tested, we can use it in any other project that might involve performing these tasks on records.
  • Transformation of the system is easy. Since the Business Logic is separate from the Data Access Layer, changing the data access layer won’t affect the business logic module much. Let's say if we are moving from SQL Server data storage to Oracle, there shouldn’t be any changes required in the business layer component and in the GUI component.
  • Maintenance of the system is easy. Let's say if there is a minor change in the business logic, we don’t have to install the entire system in individual user’s PCs. Let suppose that the government increases the Entertainment tax rate from 4% to 8 %, we only need to update the business logic component without affecting the productivity of end users and without any downtime.
  • Using 3-Tier Architecture development allows you parallel development on individual tier.
Here is the example that I have created using 3-Tier Architecture Concept. For ease of understanding I have separated the Presentation Layer, Business Access Layer and Data Access Layer.  I have created BAL and DAL into App_Code Folder.
Here is the Screenshot of the Solution Explorer:

N-Tier Architecture in ASP.NET
Lest Start with Coding. Firstly we will go with Data Access Layer then Business Access Layer and then Presentation Layer (UI).

 Creating Data Access Layer (DAL): (RecordDAL.cs)

Add a Class File by Right Clicking on App_Code Folder and name it RecordDAL.cs (In my case I have created it in DAL Folder which is Under App_Code>3-Tier Folder. Below are the codes that I have written for RecordDAL.cs file. Here I have used Stored Procedure to perform the operations in Database.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for RecordDAL
/// </summary>
public class RecordDAL
{
    // Getting Connection String that i have created in web.config file
    string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["3Tier_ConnectionString"].ConnectionString;
    public RecordDAL()
       {
             
       }

    // Used to Insert Records in Database
    public int InsertRecord(string FName,string LName,string FatherName,int Age,string Address,stringPhone,string Email,int UserID)
    {
        //Creating Connection with SQL Server Database
        SqlConnection con = new SqlConnection(conStr);
        con.Open();
        SqlCommand cmd = new SqlCommand("AddRecords",con);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            cmd.Parameters.AddWithValue("@FName", FName);
            cmd.Parameters.AddWithValue("@LName", LName);
            cmd.Parameters.AddWithValue("@FatherName", FatherName);
            cmd.Parameters.AddWithValue("@Age", Age);
            cmd.Parameters.AddWithValue("@Address", Address);
            cmd.Parameters.AddWithValue("@Phone", Phone);
            cmd.Parameters.AddWithValue("@Email", Email);
            cmd.Parameters.AddWithValue("@UserID", UserID);
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }

    //Used to Delete Records from the Database
    //This will take UserID as Input Parameter.
    public int DeleteRecord(int UserId)
    {
        SqlConnection con = new SqlConnection(conStr);
        con.Open();
        SqlCommand cmd = new SqlCommand("DeleteRecord",con);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            cmd.Parameters.AddWithValue("@UserId",UserId);
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
       
    }
    //Used to Update Records in the Database
    //This will take UserID as Input Parameter.
    public int UpdateRecord(string FName, string LName, string FatherName, int Age, string Address,string Phone, string Email, int UserID)
    {
        SqlConnection con = new SqlConnection(conStr);
        con.Open();
        SqlCommand cmd = new SqlCommand("UpdateRecord", con);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            cmd.Parameters.AddWithValue("@FName", FName);
            cmd.Parameters.AddWithValue("@LName", LName);
            cmd.Parameters.AddWithValue("@FatherName", FatherName);
            cmd.Parameters.AddWithValue("@Age", Age);
            cmd.Parameters.AddWithValue("@Address", Address);
            cmd.Parameters.AddWithValue("@Phone", Phone);
            cmd.Parameters.AddWithValue("@Email", Email);
            cmd.Parameters.AddWithValue("@UserID", UserID);           
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }

    }
    // used to Load All the Records from the Database
    public DataTable RetrieveRecords()
    {
        SqlConnection con = new SqlConnection(conStr);
        SqlDataAdapter da = new SqlDataAdapter("ShowAllRecords", con);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        SqlCommandBuilder bui = new SqlCommandBuilder(da);
        DataTable dt = new DataTable();
      
        try
        {
            da.Fill(dt);
            return dt;
        }
        catch
        {
            throw;
        }
        finally
        {
           
            da.Dispose();
            con.Close();
            con.Dispose();
        }

    }
    // Used to Check the existance of the Record.
    public int checkExistance(int UserId)
    {
        SqlConnection con = new SqlConnection(conStr);
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from tblUserInfo where UserID=" + UserId + "", con);
        SqlDataReader dr;
        try
        {
            dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                return 1;
            }
            else
                return 0;
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }
}

In the above code, I have a member variable called conStr that is used to get the database connection string from my web.config file that is being used throughout the class. I have created separate method for inserting, deleting, updating records into database and loading records from database. At the End of the Article I have given the Structure of Respective tables and Used Procedures.

Creating Business Access Layer (BAL):   (RecordBAL.cs)

Add a Class File by Right Clicking on App_Code Folder and name it RecordBAL.cs, (In my case I have created it in BAL Folder which is Under App_Code>3-Tier Folder. This contains methods that are used for calling InsertRecord (), DeleteRecord (),
RetrieveRecords () and checkExistance () method of Data Access Layer Class File. Below are the codes that I have written for RecordBAL.cs file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for RecordBAL
/// </summary>
public class RecordBAL
{
       public RecordBAL()
       {
             
       }
    //Used for Loading All Records from Database
    public DataTable RetrieveRecords()
    {
        //Creating the object of DAL file.
        RecordDAL rdAL = new  RecordDAL();
        try
        {
            //Calling the method of DAl using DAL object
            //and return the result to the caller of the method.
            return rdAL.RetrieveRecords();
        }
        catch
        {
            throw;
        }
        finally
        {
            rdAL = null;
        }
    }
    //Used for Inserting Record in the Database
    public int InsertRecord_BAL(string FName, string LName, string FatherName, int Age,string Address, string Phone, string Email, int UserID)
    {
        RecordDAL rDal = new RecordDAL();       
        try
        {
            return rDal.InsertRecord( FName,LName,FatherName,Age,Address,Phone,Email,UserID);
        }
        catch
        {
            throw;
        }
        finally
        {
            rDal = null;
        }
    }
    // Used for Deleting the Records from the Database
    public int DeleteRecord_BAL(int UserID)
    {
        RecordDAL rDal = new RecordDAL();
        try
        {
            return rDal.DeleteRecord(UserID);
        }
        catch
        {
            throw;
        }
        finally
        {
            rDal = null;
        }
    }

    //Used for Updating Record in Database
    public int UpdateRecord_BAL(string FName, string LName, string FatherName, int Age,string Address, string Phone, string Email, int UserID)
    {
        RecordDAL rDal = new RecordDAL();
        try
        {
            return rDal.UpdateRecord(FName, LName, FatherName, Age, Address, Phone, Email, UserID);
        }
        catch
        {
            throw;
        }
        finally
        {
            rDal = null;
        }
    }
    // Used for checking exixtance of the Record in the Database
    public int checkExistance_BAL(int UserID)
    {
        RecordDAL rDal = new RecordDAL();
        try
        {
            return rDal.checkExistance(UserID);
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            rDal = null;
        }
    }
}

Creating Presentation Layer (UI): (Default.aspx)

Till Now, we have created our Data Access Layer (DAL), Business Access Layer (BAL). Now, we are going to create our Presentation Layer that is User Interface (UI), it will accept the value from the user. Add a new WebPage to your project. Here I named it Default.aspx
Here is the Screenshot of the UI that I have created to accept the values from the user.
N-Tier Architecture in ASP.NET

Code for Default.aspx file


<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs"Inherits="_Default" %>

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Add New Records (3-Tier Architecture in ASP.NET)</title>
    <script type="text/javascript">
        // This will prevent to Go Back
        window.history.forward(0);
        function noBack()
        {
            window.history.forward();
        }
        //This will take the ref of textbox and will trim the white-spaces
        function trim(id) {
            if (id != null)
                id.value = id.value.toString().replace(/^\s+|\s+$/g, "");
        }
    </script>
</head>
<body >
    <form id="form1" runat="server">
    <div style="margin:0 auto; width:700px; text-align:center" >
   
        <table style="margin:0 auto;" width="400px" >
            <tr>
                <td colspan="2" align="center" bgcolor="#99CCFF">
                    <asp:Label ID="Label1" runat="server" Text="Add New Records" Font-Bold="True"
                        Font-Names="Georgia" ForeColor="#0033CC"></asp:Label>
                    &nbsp;</td>
            </tr>
            <tr>
                <td align="right" >
                    <asp:Label ID="Label8" runat="server" Text="User ID"></asp:Label>
                </td>
                <td align="left">
                    <asp:TextBox ID="txtUserID" runat="server" Width="180px"onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server"
                        ControlToValidate="txtUserID" ErrorMessage="*"
                        ToolTip="Provi User ID"></asp:RequiredFieldValidator>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator3"runat="server"
                        ControlToValidate="txtUserID" ToolTip="This Will Take Number Only (Ex: 1)"
                        ErrorMessage="*" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    <asp:Label ID="Label2" runat="server" Text="First Name"></asp:Label>
                </td>
                <td align="left">
                    <asp:TextBox ID="txtFName" runat="server" Width="180px"onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
                        ControlToValidate="txtFName" ErrorMessage="*"
                        ToolTip="First Name Can't be Left Blank"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    <asp:Label ID="Label3" runat="server" Text="Last Name"></asp:Label>
                </td>
                <td align="left">
                    <asp:TextBox ID="txtLName" runat="server" Width="180px"onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
                        ControlToValidate="txtLName" ErrorMessage="*"
                        ToolTip="Last Name Can't Be Left Blank"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    <asp:Label ID="Label4" runat="server" Text="Father Name" ></asp:Label>
                </td>
                <td align="left">
                    <asp:TextBox ID="txtFatherName" runat="server" Width="180px"onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
                        ControlToValidate="txtFatherName" ErrorMessage="*"
                        ToolTip="Father Name Can't Be Left Blank"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    <asp:Label ID="Label5" runat="server" Text="Age"></asp:Label>
                </td>
                <td class="style3" align="left">
                    <asp:TextBox ID="txtAge" runat="server" Width="100px"onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"
                        ControlToValidate="txtAge" ErrorMessage="*" ToolTip="Age Can't Be Left Blank"></asp:RequiredFieldValidator>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator2"runat="server"
                        ControlToValidate="txtAge" ToolTip="Enter Numbers Only (Ex: 25)"ErrorMessage="*" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    <asp:Label ID="Label6" runat="server" Text="Address" ></asp:Label>
                </td>
                <td align="left">
                    <asp:TextBox ID="txtAddress" runat="server" Height="49px"TextMode="MultiLine"
                        Width="180px" onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server"
                        ControlToValidate="txtAddress" ErrorMessage="*"
                        ToolTip="Provide Address Details"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    <asp:Label ID="Label7" runat="server" Text="Phone No."></asp:Label>
                </td>
                <td align="left">
                    <asp:TextBox ID="txtPhone" runat="server" Width="180px"onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server"
                        ControlToValidate="txtPhone" ErrorMessage="*"
                        ToolTip="Provide Phone Details"></asp:RequiredFieldValidator>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator4"runat="server"
                        ControlToValidate="txtPhone"
                        ToolTip="This Will Take Number Only (Ex: 9889989898)"ErrorMessage="*"
                        ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
                </td>
            </tr>
            <tr>
                <td align="right">
                    Email ID</td>
                <td align="left">
                    <asp:TextBox ID="txtEmail" runat="server" Width="180px"onblur="trim(this)"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server"
                        ControlToValidate="txtEmail" ErrorMessage="*" ToolTip="Provide Email ID"></asp:RequiredFieldValidator>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator1"runat="server" ErrorMessage="*"
                    ToolTip="Invalid Email Format. (Ex: abc@xyz.com)"
                        ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
                        ControlToValidate="txtEmail"></asp:RegularExpressionValidator>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    &nbsp;</td>
                <td align="left">
                    <asp:Button ID="btnSave" runat="server" Text="Save" Width="95px"
                        onclick="btnSave_Click" Font-Bold="True" Font-Names="Georgia"  />
                    <asp:HyperLink ID="HyperLink1" runat="server"NavigateUrl="ShowRecords.aspx">Show Records</asp:HyperLink>
                </td>
            </tr>
            <tr>
                <td align="right" >
                    &nbsp;</td>
                <td align="left">
                    <asp:Label ID="lblMessage" runat="server" Font-Bold="True" Font-Names="Georgia"
                        Font-Size="Medium" ForeColor="Red"></asp:Label>
                </td>
            </tr>
            <tr>
                <td align="right" colspan="2" >
                <hr style="border-color: #6699FF; background-color: #99CCFF" />
                    &nbsp;</td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html>

The above code contains all the validation on User Input. Like Text Can’t Be Left Blank, Only Numbers in TextBox, Only Alphabets in TextBox, and Email Format Validation at client-side. It will also check that whether the record already exists in database.

Code for Default.aspx.cs file


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

public partial class _Default : System.Web.UI.Page
{  
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        //Creating the object of BAL
        RecordBAL rBAL=new RecordBAL();      

        int recordStatus = 0;
        string FName=txtFName.Text.ToString();
        string LName=txtLName.Text.ToString();
        string FatherName=txtFatherName.Text.ToString();
        int Age=Convert.ToInt32( txtAge.Text);
        string Address=txtAddress.Text.ToString();
        string Phone=txtPhone.Text.ToString();
        string Email=txtEmail.Text.ToString();
        int UserID=Convert.ToInt32(txtUserID.Text);

        try
        {
            //Passing the Parameter in the Method of BAl using BAL object
            recordStatus = rBAL.checkExistance_BAL(UserID);
            if (recordStatus > 0)
            {
                lblMessage.Text = "This User ID Already Exists.";
                return;
            }

            recordStatus = rBAL.InsertRecord_BAL(FName, LName, FatherName, Age, Address, Phone, Email, UserID);
            if(recordStatus > 0)
                lblMessage.Text = "New Record Inserted Successfully.";
            //else
            //    lblMessage.Text = "This Record Already Exists.";
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message.ToString();
        }
        finally
        {
            rBAL = null;           
        }

      
    }
}

Now, to display the records, I have created another page and named it ShowRecords.aspx. Here is the Screenshot of the UI ofShowRecords.aspx

ShowRecords.aspx (UI)

N-Tier Architecture in ASP.NET
This will get all the Record from the database and display it in GridView. Here I have make the GridView Editable to enable it to perform Update and Delete Operation. You can Perform Update Operation by Clicking on Edit Button and can perform Delete Operation by ClickingDelete Button in GridView.

Code for ShowRecords.aspx file


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowRecords.aspx.cs"Inherits="ShowRecords" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>List of All Records (3-Tier Architecture in ASP.NET)</title>
    <script type="text/javascript">
        window.history.forward(0);
        function noBack()
        {
            window.history.forward();
        }
        function trim(id) {
            if (id != null)
                id.value = id.value.toString().replace(/^\s+|\s+$/g, "");
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div style="margin: 0 auto;" width="1100px">
        <table style="margin: 0 auto;" width="1100px">
            <tr>
                <td>
                    <asp:HyperLink ID="HyperLink1" runat="server"
                        NavigateUrl="~/3-Tier/Default.aspx">Add New Record</asp:HyperLink>
                </td>
            </tr>
            <tr>
                <td bgcolor="#6699FF" align="center">
                    &nbsp;
                    <asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Names="Georgia" ForeColor="White"
                        Text="List of All Records"></asp:Label>
                </td>
            </tr>
            <tr>
                <td align="center">
                    <asp:GridView ID="GridViewShowRecords" runat="server" BackColor="White"BorderColor="#DEDFDE"
                        BorderStyle="None" BorderWidth="1px" CellPadding="4"EnableModelValidation="True"
                        ForeColor="Black" GridLines="Vertical" AutoGenerateColumns="false"OnRowCancelingEdit="RowCancelingEdit"
                        OnRowDeleting="RowDeleting" OnRowEditing="RowEditing"OnRowUpdating="RowUpdating">
                        <AlternatingRowStyle BackColor="White" />
                        <FooterStyle BackColor="#CCCC99" />
                        <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White"/>
                        <PagerStyle BackColor="#F7F7DE" ForeColor="Black"HorizontalAlign="Right" />
                        <RowStyle BackColor="#F7F7DE" />
                        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True"ForeColor="White" />
                        <Columns>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    ID</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblUserID" runat="server"Text='<%#Bind("UserId") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtUserID" Text='<%#Bind("UserId") %>'runat="server" Width="20px"
                                        Enabled="False"></asp:TextBox>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    FirstName</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblFirstName" runat="server"Text='<%#Bind("FName") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtFirstName" Text='<%#Bind("FName")%>' runat="server" Width="80px"></asp:TextBox>
                                    <asp:RequiredFieldValidatorID="RequiredFieldValidator1" runat="server" ErrorMessage="RequiredFieldValidator"
                                        ControlToValidate="txtFirstName" ToolTip="Can't Be Left Blank.">*</asp:RequiredFieldValidator>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    LastName</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblLastName" runat="server"Text='<%#Bind("LName") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtLastName" Text='<%#Bind("LName")%>' runat="server" Width="80px"></asp:TextBox>
                                    <asp:RequiredFieldValidatorID="RequiredFieldValidator2" runat="server" ErrorMessage="RequiredFieldValidator"
                                        ControlToValidate="txtLastName" ToolTip="Can't Be Left Blank.">*</asp:RequiredFieldValidator>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    FatherName</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblFatherName" runat="server"Text='<%#Bind("FatherName") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtFatherName"Text='<%#Bind("FatherName") %>' runat="server" Width="100px"></asp:TextBox>
                                    <asp:RequiredFieldValidatorID="RequiredFieldValidator3" runat="server" ErrorMessage="RequiredFieldValidator"
                                        ControlToValidate="txtFatherName" ToolTip="Can't Be Left Blank.">*</asp:RequiredFieldValidator>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    Age</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblAge" runat="server"Text='<%#Bind("Age") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtAge" Text='<%#Bind("Age") %>'runat="server" Width="30px"></asp:TextBox>
                                    <asp:RequiredFieldValidatorID="RequiredFieldValidator4" runat="server" ErrorMessage="RequiredFieldValidator"
                                        ControlToValidate="txtAge" ToolTip="Provide Age Details.(EX: 24)">*</asp:RequiredFieldValidator>
                                    <asp:RegularExpressionValidatorID="RegularExpressionValidator2" runat="server" ControlToValidate="txtAge"
                                        ToolTip="Enter Numbers Only (Ex: 25)"ErrorMessage="*" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    Address</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblAddress" runat="server"Text='<%#Bind("Address") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtAddress" Text='<%#Bind("Address")%>' runat="server" Width="150px"></asp:TextBox>
                                    <asp:RequiredFieldValidatorID="RequiredFieldValidator5" runat="server" ErrorMessage="RequiredFieldValidator"
                                        ControlToValidate="txtAddress" ToolTip="Provide Address Details.">*</asp:RequiredFieldValidator>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    Phone</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblPhone" runat="server"Text='<%#Bind("Phone") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtPhone" Text='<%#Bind("Phone") %>'runat="server" Width="80px"></asp:TextBox>
                                    <asp:RequiredFieldValidatorID="RequiredFieldValidator6" runat="server" ErrorMessage="RequiredFieldValidator"
                                        ControlToValidate="txtPhone" ToolTip="Provide Phone No. Details.">*</asp:RequiredFieldValidator>
                                    <asp:RegularExpressionValidatorID="RegularExpressionValidator4" runat="server" ControlToValidate="txtPhone"
                                        ToolTip="This Will Take Number Only (Ex: 9889989898)" ErrorMessage="*" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    Email</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblEmail" runat="server"Text='<%#Bind("Email") %>'></asp:Label>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtEmail" Text='<%#Bind("Email") %>'runat="server" Width="120px"></asp:TextBox>
                                    <asp:RequiredFieldValidatorID="RequiredFieldValidator7" runat="server" ErrorMessage="RequiredFieldValidator"
                                        ControlToValidate="txtEmail" ToolTip="Provide Email Details.(EX: abc@xyz.com)">*</asp:RequiredFieldValidator>
                                    <asp:RegularExpressionValidatorID="RegularExpressionValidator1" runat="server" ErrorMessage="*"
                                        ToolTip="Invalid Email Format. (Ex: abc@xyz.com)"ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
                                        ControlToValidate="txtEmail"></asp:RegularExpressionValidator>
                                </EditItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField>
                                <HeaderTemplate>
                                    Command Button</HeaderTemplate>
                                <ItemTemplate>
                                    <asp:Button ID="btnEdit" runat="server"CommandName="Edit" Text="Edit" />
                                    <asp:Button ID="btnDelete" runat="server"CommandName="Delete" Text="Delete" CausesValidation="true"
                                        OnClientClick="return confirm('Are you sure?')"/>                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:Button ID="btnUpdate" runat="server"CommandName="Update" Text="Update" />
                                    <asp:Button ID="btnCancel" runat="server"CommandName="Cancel" Text="Cancel" CausesValidation="false" />
                                </EditItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td>                  
                    <asp:Label ID="lblMessage" runat="server" Font-Bold="True" Font-Names="Georgia" ForeColor="Red"></asp:Label>
                </td>
            </tr>
            <tr>
                <td>
                    <hr style="border-color: #6699FF" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

Code for ShowRecords.aspx.cs file


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

public partial class ShowRecords : System.Web.UI.Page
{  
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //Binding the GridView
            BindGridViewShowRecords();
        }
    }
    private void BindGridViewShowRecords()
    {
        //Setting the DataSource of GridView
        GridViewShowRecords.DataSource = GridShowRecordsDataSource();
        GridViewShowRecords.DataBind();
    }
    private DataTable GridShowRecordsDataSource()
    {
        //Creating object of BAL
        RecordBAL rBAL = new  RecordBAL();
        DataTable dTable = new DataTable();
        try
        {
            //Getting the DataSource for GridView from BAL Using BAL object
            dTable = rBAL.RetrieveRecords();
           
        }
        catch (Exception ex)
        {
           lblMessage.Text = ex.Message.ToString();
        }
        finally
        {
            rBAL = null;
        }
        return dTable;
       
    }
    protected void RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridViewShowRecords.EditIndex = -1;
        BindGridViewShowRecords();
    }  
    protected void RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridViewShowRecords.EditIndex = e.NewEditIndex;
        BindGridViewShowRecords();
    }
    protected void RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int recordStatus = 0;

        try
        {
            TextBox UserID = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtUserID") as TextBox;
            TextBox FName = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtFirstName") as TextBox;
            TextBox LName = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtLastName") as TextBox;
            TextBox FatherName = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtFatherName") as TextBox;
            TextBox Age = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtAge") as TextBox;
            TextBox Address = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtAddress") as TextBox;
            TextBox Phone = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtPhone") as TextBox;
            TextBox Email = GridViewShowRecords.Rows[e.RowIndex].FindControl("txtEmail") as TextBox;          

            RecordBAL rBAL = new RecordBAL();
            try
            {
              
                recordStatus = rBAL.UpdateRecord_BAL(FName.Text.Trim(),LName.Text.Trim(),FatherName.Text.Trim(),Convert.ToInt32(Age.Text.Trim()),Address.Text.Trim(),
Phone.Text.Trim(),Email.Text.Trim(),Convert.ToInt32( UserID.Text.Trim()));
                if (recordStatus > 0)
                    lblMessage.Text = "Record Updated Successfully.";
                else
                   lblMessage.Text = "Record couldn't updated";
            }
            catch (Exception ee)
            {
               lblMessage.Text = ee.Message.ToString();
            }
            finally
            {
                rBAL = null;
            }

            GridViewShowRecords.EditIndex = -1;
            BindGridViewShowRecords();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }
    protected void RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
       
        int recordStatus = 0;
        Label UserID = GridViewShowRecords.Rows[e.RowIndex].FindControl("lblUserID") as Label;
        int id = Convert.ToInt32(UserID.Text);

        RecordBAL rBAL = new RecordBAL();
        try
        {
            recordStatus = rBAL.DeleteRecord_BAL(id);
            if (recordStatus > 0)
                lblMessage.Text = "Record Deleted Successfully.";
            else
                lblMessage.Text = "Record couldn't Deleted";
        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            rBAL = null;
        }

        GridViewShowRecords.EditIndex = -1;
        BindGridViewShowRecords();
    }
}

 Following are the Screenshots of the output window of the program.
N-Tier Architecture in ASP.NET
N-Tier Architecture in ASP.NET

Database Structure

Following are the structure of the Respective Tables and Stored Procedures used in this example.
tblUserInfo
CREATE TABLE [dbo].[tblUserInfo]
(
      [FName] [varchar](100) NOT NULL,
      [LName] [varchar](100) NULL,
      [FatherName] [varchar](100) NULL,
      [Age] [int] NULL,
      [Address] [varchar](200) NULL,
      [Phone] [varchar](20) NULL,
      [Email] [varchar](50) NULL,
      [UserID] [int] primary key,
)

Stored Procedure


AddRecords
CREATE PROCEDURE [dbo].[AddRecords]
      (          
            @FName varchar(100),
            @LName varchar(100),
            @FatherName varchar(100),
            @Age int,
            @Address varchar(200),
            @Phone varchar(20),
            @Email varchar(50),
            @UserID int
      )
AS
      insert into tblUserInfo
      (FName,LName,FatherName,Age,Address,Phone,Email,UserID)
      values
      (@FName,@LName,@FatherName,@Age,@Address,@Phone,@Email,@UserID )
      RETURN
GO

UpdateRecord 

CREATE PROCEDURE [dbo].[UpdateRecord]
      (
            @FName varchar(100),
            @LName varchar(100),
            @FatherName varchar(100),
            @Age int,
            @Address varchar(200),
            @Phone varchar(20),
            @Email varchar(50),
            @UserID int
      )
AS
      update tblUserInfo
      set  
            FName=@FName,
            LName=@LName,
            FatherName=@FatherName,
            Age=@Age,
            Address=@Address,
            Phone=@Phone,
            Email=@Email
           
      where
            UserID= @UserID
      RETURN
GO

DeleteRecord

CREATE PROCEDURE [dbo].[DeleteRecord]
      (
      @UserId int
      )
     
AS
      delete from tblUserInfo where UserID = @UserId
      RETURN
GO
Posted by at No comments:
Subscribe to: Posts (Atom)