Insert, Select, Update and Delete in ASP.Net With Simple Login



Before we create our ASP.NET web applications there are few questions that beginners ask frequently.

  1. How to create a Login page?

  2. How to check valid user from Login and redirect to main page?

  3. How to display Logged in user name in all pages?

  4. How to bind data to Grid?

  5. How to search data and display in Grid?

  6. How to Insert/Select/Update and delete data from grid to database?

  7. How to write an N-Teir application for ASP.Net?

  8. How to create a simple ASP.Net Web Application?

  9. Authentication and Authorization

For all the preceding questions I thought to create a simple ASP.Net application with Login and Main Page that has simple Create, Read, Update and Delete (CRUD) operations and has search results, JavaScript validation and Stored Procedures, all that using N-Teir Architecture.

First let's start with what CRUD is. CRUD Stands for:

  • Create (insert/add): Insert data into the database.

  • Read (select): Select data from the database.

  • Update (edit/update): update data to the database.

  • Delete: Delete data from the database.

Why we need CRUD and where to use it

In application development there are two kinds of development, one is frontend and the other one is backend. A frontend is an application that can be a Web, Desktop or Mobile application. The application can be developed using any one of the languages like C#, VB.Net, Java and and so on. A backend is a database like Microsoft Access, MySQL, SQL Server, Oracle and and so on.

A frontend is used to present data and a backend stores the data. To connect the backend, for example SQL Server, with a frontend, such as ASP.NET, we use ADO.Net. Using ADO.NET objects like Connection, Command, DataSet, DataAdapter and and so on we can perform CRUD operations.

Let's consider now we have connected our SQL Database and ASP.NET application using ADO.Net. Will we get data automatically from the DB to the application? We need to select the data from the database and display it in our application, we need to insert the data of user input into the database, we need to update the user data to our database and we need to delete the data of the user from the database. All these create, insert, delete and select operations from the Db to the application are CRUD operations.

N-Teir Architecture

In application development the Tier is called a layer. Let's see the following image:

N Tier Architecture

Here the layers are nothing but a class.

  • UI: User Interface where the user enters their input to be stored and perform some business logic.

  • Business logic Layer: Here the Business logic layer is a class. From the UI (our code behind) we pass all our input from the user to the Business Logic class as objects.

  • Data Access Layer: From the Business logic Layer we pass all the object parameters to this Data Access Layer Class. This class will use the ADO.Net objects like Command (Select), Command Type (Query type is text or Stored Procedure), ExceuteNonQuery (perform Insert/Update and Delete), ExecuteDataset (return select statement ) and ExecuteScalar (to return single data). For example if we need to find a Max value of our ID and return to the UI then we can use ExecuteScalar.

Authentication and Authorization

Authentication: Check for the Valid User. Here the question is how to check that a user is valid or not. When a user comes to a website for the first time he will register for that website. All his information, like user name, password, email and so on will be stored in the website database. When a user enters his userID and password, the information will be checked with the database. If the user has entered the same userID and Password as in the database then his or she is a valid user and will be redirected to the website home page. If the user enters a UserID and/or Password that does not match the database then the login page will give a message, something like “Enter valid Name or Password”. The entire process of checking whether the user is valid or not for accessing the website is called Authentication.


In ASP.NET we can use any one Authentication method to verify the user.

  • Windows Authentication: the local Windows user is used to check whether the user is valid or not.

  • Forms Authentication: Using form authentication we can write our own code and check for the valid user. The Authenticated user's details will be stored as a cookie in the local computer.

In this article I have used the Forms Authentication. To use the Form Authentication in Web.Config file we set the authentication mode to “Forms”.

Here we set the website Login Page URL and Default page URL.

defaultUrl -> From the Login Page after the user is authenticated it will be redirected to the defaultURL page. For example here I have used “Default.ASPX”. Once the user is authenticated in my demo site he will be redirected to the main page.

  1. <authentication mode="Forms"><forms defaulturl="Default.aspx" loginurl="~/Account/Login.aspx" slidingexpiration="true" timeout="2880" /></authentication> 

Passport authentication: Passport authentication is based on Microsoft Passport based on a website like Hotmail and and so on. Here the user's authentication will be verified from the Passport.

Authorization: Once the user is authenticated he needs to be redirected to the appropriate page by his role. For example when an Admin is logged in then he is to be redirected to the Admin Page. If an Accountant is logged in then he is to be redirected to his Accounts page. If an End User is logged in then he is to be redirected to his page. In ASP.NET we can use the Authorization to redirect to the appropriate page by the user's role.


ASP.Net default Login System

The attached sample program has been developed using Visual Studio 2010. When we create a new website from Visual Studio 2010 we can see the screens as in the following in the Solution Explorer. By default we can see the Microsoft ASP.NET default login and the User Registration pages. We can use this login page and user registration page to develop our website. Here we can see now the App_Data Folder is empty since there is no local database created by default.

Web Config

Web.Config File: In the Web.Config file we can see in the connection string in AttachedDBFileName we can see there is an aspnetdb.mdf. This file will be locally created in your App_Data folder when we use ASP.NET Membership or other services.

Once we execute our ASP.Net website and click on user Login and User Registration we create a new user for our website. We can see there will be a new aspnetdb.mdf created in our App_Data Folder. In this aspnetdb.mdf all the user information is stored.

It will be good to use the ASP.Net Default login system. Then when we develop our own custom login system the ASP.Net login system is a more secure way to store and retrieve user's information and password.

aspnetdb in SQL Server DB

We can also create aspnetdb in our SQL Server and use that instead of local DB.

Here are a few links that explain how to create an aspnetdb in our SQL Server.

Using the code

Create your ASP.Net web application. For my demo I used Visual Studio 2010.

We need to add all our Business Class, DAL Class and Helper Class inside the App_Code Folder of our website. When we create a new web project we need to create a new folder. Since App_Code is inside this folder you can create a sub-folder and add all our classes.

For a simple understanding in our demo I created a BIZ folder and a DAL folder. Here I used 2 layers, one is a Biz layer and the other is a DAL Layer.

In the DAL folder, I have:

  • SQLHelper Class: which is our DAL Class where we can perform CRUD functions.

  • BixBase Class: This class will be inherited in our Business Class to add and get the SQL parameters to array.
In the BIZ Folder, we can created all our business classes.

For example I created two classes, one for the Login page and another one for the main page.

We will see in more detail how to use this class below in the code section.

The next step is to create our tables in the database to perform our CRUD operations.

For a demo I have created the table Item Masters.

Connection String: For your other table database you can set the connection string. In the Shanu Connection string provide the SQL Server db connection string for where you create this ItemMasters Table.
  1. <connectionStrings><add name="shanu" connectionString="Data Source=YOURServer;Initial Catalog=YOURDB;Persist Security Info=True;User ID=YOURUID;Password=yourpwd" providerName="System.Data.SqlClient"/>  
  3. <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>  
  5. <!--<add name="ApplicationServices" connectionString="Data Source=YOURServer;Initial Catalog=aspnetdb;user id=YOURUID;password=yourpwd;Integrated Security=True" providerName="System.Data.SqlClient"/>--></connectionStrings> 
Creating the table

We will create a User Master table to be used for login verification.
  1. -- Create Table Item Master - this table will be used in for Complete CRUD Sample  
  2. CREATE TABLE [dbo].[ItemMasters](  
  3.     [Item_Code] [varchar](20) NOT NULL,  
  4.     [Item_Name] [varchar](100) NOT NULL,  
  5.     [Price]  Int NOT NULL,  
  6.     [TAX1]  Int NOT NULL,  
  7.     [Discount]  Int NOT NULL,  
  8.     [Description] [varchar](200) NOT NULL,  
  9.     [IN_DATE] [datetime] NOT NULL,  
  10.     [IN_USR_ID] [varchar](50) NOT NULL,  
  11.     [UP_DATE] [datetime] NOT NULL,  
  12.     [UP_USR_ID] [varchar](50) NOT NULL,  
  14. (  
  15.     [Item_Code] ASC  
  17. ON [PRIMARY]  
  21. -- insert sample data to Item Master table  
  22. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  23.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  24.      VALUES  
  25.            ('Item001','Coke',55,1,0,'Coke which need to be cold',GETDATE(),'root'  
  26.            ,GETDATE(),'root')  
  28. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  29.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  30.      VALUES  
  31.            ('Item002','Coffee',40,0,2,'Coffe Might be Hot or Cold user choice',GETDATE(),'root'  
  32.            ,GETDATE(),'root')  
  34. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  35.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  36.      VALUES  
  37.            ('Item003','Chiken Burger',125,2,5,'Spicy',GETDATE(),'root'  
  38.            ,GETDATE(),'root')  
  40. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  41.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  42.      VALUES  
  43.            ('Item004','Potato Fry',15,0,0,'No Comments',GETDATE(),'root'  
  44.            ,GETDATE(),'root'
Login Screen:

Here is my simple login screen. You can design your own login screen now.


I have used the ASP.NET default login page. Here we can see a New User registration link. When the user clicks on this link the User Registration page will be opened.


Once the user is registered his or her information will be stored in the aspnetdb.mdf file.

Main page

Now we have completed our Login page and let's design our Main page.

Here is how my Simple Main page looks.

Main page

I have used an ASP.NET Master Page to add the top details to be displayed in all pages. In the Master page I have used the ASP.Net LoginName and LoginStatus. This will be used to display the Authenticated username in all the pages.

In the Master page I have added the two Menus “Home” and “Item Management”.

In the Item Management page the user can search for items and insert, edit and delete items from the database.

Item Management Search

In the page init method we first check the authentication of the user. If the user is not authenticated then he or she is redirected to the Login Page.
  1. protected override void OnInit(EventArgs e)  
  2. {  
  3.    base.OnInit(e);  
  5.    if (!this.Page.User.Identity.IsAuthenticated)  
  6.    {  
  7.       FormsAuthentication.RedirectToLoginPage();  
  8.    }  

Next we see how to search the user input data and display the results to the grid.



In the Search Button Click: the Login page is the same as here, we pass our parameters to our BIZ class and from the BIZ class to the DAL Class and get the result as a dataset and bind it to our GridView.

Here the user can search by Item code or by Item Name. If both of the input is empty, I will return all the records from the database using the executeDataset.
  1. protected void btnSearch_Click(object sender, ImageClickEventArgs e)  
  2. {  
  3.     SelectList();  
  4. }  
  5. //This Method is used for the search result bind in Grid  
  6. private void SelectList()  
  7. {  
  8.     SortedDictionary<stringstring=""> sd = new SortedDictionary<stringstring="">()   { };  
  9.     sd.Add("@pTYPE""S1");  
  10.     sd.Add("@Item_Code", txtSitemCDE.Text.Trim());  
  11.     sd.Add("@Item_Name", txtSItemNme.Text.Trim());  
  12.     DataSet ds = new DataSet();  
  13.     ds = new ShanuCRUDBizClass().SelectList(sd);  
  14.     GridView1.DataSource = ds;  
  15.     GridView1.DataBind();  
  16. }  
  18. </string,></string,> 
Main page CRUD Stored Procedure

For the main page I created a single SP to perform all our CRUD Operations. We can pass the necessary parameters to the SP with its type:
  • pType=”S1”: is for a select search.
  • pType=”S2”: is to Find the next Item code and return the value.
  • pType=”I3”: is for inserting records.
  • pType=”U4”: is for updating the record.
  • pType=”D5”: is for deleting the record.
  1. -- Author      : Shanu                                                                  
  2. -- Create date : 2014-12-10                                                                  
  3. -- Description : To Check valid User                                                  
  4. -- Tables used :  userMasters                                                                 
  5. -- Modifier    : Shanu                                                                  
  6. -- Modify date : 22014-12-10                                                                  
  7. -- =============================================                                                                  
  8. -- exec USP_Item_CRUD 'S1','','Coffee'  
  9. --  exec USP_Item_CRUD 'S2'        
  10. ---- exec USP_Item_CRUD 'I3','Item009','Coffee',100,10,1,'test','SHANU'             
  11. -- =============================================                                                             
  12. Alter PROCEDURE [dbo].[USP_Item_CRUD]                                                
  13.    (         
  14.            @pTYPE              VARCHAR(02)     = '',                           
  15.            @Item_Code          VARCHAR(50)     = '',                              
  16.            @Item_Name          VARCHAR(50)     = '',  
  17.            @Price              INT=0    ,  
  18.            @TAX1               INT=0    ,  
  19.            @Discount           INT=0    ,  
  20.            @Description        VARCHAR(50)     = '',  
  21.            @USR_Name           VARCHAR(50)     = ''       
  22.       )                                                          
  23. AS                                                                  
  24. BEGIN      
  25. Declare @maxItemCode varchar(30)='';  
  28. IF          @pTYPE = 'S1'   GOTO S1_RTN  -- Select Query  
  29. ELSE IF     @pTYPE = 'S2'   GOTO S2_RTN  -- Select for ExceuteScalar Query  
  30. ELSE IF     @pTYPE = 'I3'   GOTO I3_RTN  -- Insert Query  
  31. ELSE IF     @pTYPE = 'U4'   GOTO U4_RTN  -- Update Query  
  32. ELSE IF     @pTYPE = 'D5'   GOTO D5_RTN  -- Delete Query  
  35. RETURN  
  37. --Select  
  38. S1_RTN:  
  39.     BEGIN     
  40.          Select Item_Code,  
  41.                 Item_Name,  
  42.                 Price,  
  43.                 TAX1,  
  44.                 Discount,  
  45.                 Description,  
  46.                 UP_USR_ID as user_Name  
  47.             FROM   
  48.                 ItemMasters   
  49.             WHERE  
  50.                 Item_Code like  @Item_Code +'%'  
  51.                 AND Item_Name like @Item_Name +'%'  
  52.             ORDER BY  
  53.                 Item_Name,  
  54.                 Item_Code  
  55.     RETURN    
  56. END  
  58. --Select  
  59. S2_RTN:  
  60.     BEGIN     
  62.         Set @maxItemCode='Item00' + Convert(Varchar(10),(Select MAX(RIGHT(ITEM_CODE, 3))+1 from itemMasters))  
  63.         select @maxItemCode ItemCODE  
  65.     RETURN    
  66. END  
  68. --Insert  
  69. I3_RTN:  
  70.     BEGIN     
  71.     IF NOT EXISTS (SELECT * FROM ItemMasters WHERE Item_Name=@Item_Name)  
  72.         BEGIN  
  74.             INSERT INTO [ItemMasters]     
  75.                         ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  76.                        ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  77.             VALUES  
  78.                        (@Item_Code,@Item_Name,@Price,@TAX1,@Discount,@Description,GETDATE(),@USR_Name  
  79.                        ,GETDATE(),@USR_Name)  
  80.                        SET @Item_Code='';  
  81.                        SET @Item_Name='';  
  82.                         GOTO S1_RTN  
  84.          END  
  85.          ELSE  
  86.          BEGIN  
  87.          Select 'Exists'  
  88.          END  
  90.     RETURN    
  91. END   
  93. --Update  
  94. U4_RTN:  
  95.     BEGIN     
  96.     IF  EXISTS (SELECT * FROM ItemMasters WHERE Item_Code=@Item_Code)  
  97.         BEGIN  
  98.             UPDATE [ItemMasters]   
  99.                     SET [Item_Name]=@Item_Name,  
  100.                         [Price]=@Price,  
  101.                         [TAX1]=@TAX1,  
  102.                         [Discount]=@Discount,  
  103.                         [Description]=@Description,  
  104.                         [UP_DATE]=GETDATE(),  
  105.                         [UP_USR_ID]=@USR_Name  
  106.                     WHERE  
  107.                         Item_Code=@Item_Code  
  109.                            SET @Item_Code='';  
  110.                          SET @Item_Name='';  
  111.                           GOTO S1_RTN  
  112.          END  
  113.     RETURN    
  114. END   
  116. --Delete  
  117. D5_RTN:  
  118.     BEGIN     
  120.             DELETE FROM [ItemMasters]   
  121.                         WHERE  
  122.                         Item_Code=@Item_Code                          
  124. END       
  125. END 

New item Add /Insert: the user can enter their input to store the new record to the database.


When the user clicks on the New Button I will generate the next ItemCode from the database and return a single value using the ExecuteScalar. By default I will hide the New and Edit tables from the user and when the user clicks on the New button I will make the the table visible to add or edit items.

  1. //Here used the Datareader to get the max itemCode and display in Item Code Textbox   
  2.     protected void btnAdd_Click(object sender, ImageClickEventArgs e)  
  3.     {  
  4.         if (hidsaveType.Value == "Edit")  
  5.         {  
  6.             return;  
  7.         }  
  8.         txtitemCode.Text = new ShanuCRUDBizClass().SelectScalar("S2");  
  9.         tdADD.Visible = true;  
  11.     } 

In the Save Button Click we get all the input and pass the parameters to our BAL and from the BAL to the DAL to insert the new record using ExecuteNonQuery.

  1.  //Save Button Click  
  2.     protected void btnSave_Click(object sender, ImageClickEventArgs e)  
  3.     {  
  4.         if (hidsaveType.Value == "Add")  
  5.         {  
  6.             InsertCall("I3");  
  8.         }  
  9.         else if(hidsaveType.Value == "Edit")  
  10.         {  
  11.             UpdateCall("U4");  
  12.         }  
  13.     }  
  14. //This method is used for both Insert and Update Funtionc  
  15.     private void InsertCall(String PTYPE)  
  16.     {  
  18.         SortedDictionary<stringstring=""> sd = new SortedDictionary<stringstring="">() { };  
  19.         sd.Add("@pTYPE", PTYPE);  
  20.         sd.Add("@Item_Code", txtitemCode.Text.Trim());  
  21.         sd.Add("@Item_Name", txtitemName.Text.Trim());  
  22.         sd.Add("@Price", txtPrice.Text.Trim());  
  23.         sd.Add("@TAX1", txtTax.Text.Trim());  
  24.         sd.Add("@Discount", txtDiscount.Text.Trim());  
  25.         sd.Add("@Description", txtdescription.Text.Trim());  
  26.         sd.Add("@USR_Name", User.Identity.Name);       
  28.         DataSet ds = new DataSet();  
  29.         ds = new ShanuCRUDBizClass().SelectList(sd);  
  30.         if (ds.Tables.Count > 0)  
  31.         {  
  32.             if (ds.Tables[0].Rows[0].ItemArray[0].ToString() == "Exists")  
  33.             {  
  34.                 Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alert""alert('Item already Exist !')"true);  
  35.                 txtitemName.Focus();  
  36.             }  
  37.         }  
  38.         else  
  39.         {  
  40.             GridView1.DataSource = ds;  
  41.             GridView1.DataBind();  
  42.             clearControls();  
  43.         }        
  45.     }  
  46.     private void UpdateCall(String PTYPE)  
  47.     {  
  49.         SortedDictionary<stringstring=""> sd = new SortedDictionary<stringstring="">() { };  
  50.         sd.Add("@pTYPE", PTYPE);  
  51.         sd.Add("@Item_Code", txtitemCode.Text.Trim());  
  52.         sd.Add("@Item_Name", txtitemName.Text.Trim());  
  53.         sd.Add("@Price", txtPrice.Text.Trim());  
  54.         sd.Add("@TAX1", txtTax.Text.Trim());  
  55.         sd.Add("@Discount", txtDiscount.Text.Trim());  
  56.         sd.Add("@Description", txtdescription.Text.Trim());  
  57.         sd.Add("@USR_Name", User.Identity.Name);  
  58.         DataSet ds = new DataSet();  
  59.         ds = new ShanuCRUDBizClass().SelectList(sd);         
  60.             GridView1.DataSource = ds;  
  61.             GridView1.DataBind();  
  62.             clearControls();  
  64.     }  
  65. </string,></string,></string,></string,> 

In our code behind it looks very simple because we have separated our Business logic and database connection into a separate class.

database connection

In my GridView I used the TemplateFiled to add, edit and delete the image buttons. Using the Gridview RowCommand, I will check for which button is clicked and perform the action.

Here we can see in the GridVie Rowcommand I check for the command name for Edit or Delete. If the edit image button is clicked then I will get the clicked row index. Using GridViewRow get all the row items and display it in the TextBox for user modification.

  1. // Grid Row command to do Edit and delete Operations  
  2.    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)  
  3.    {  
  4.        clearControls();  
  5.        if (e.CommandName == "edits")  
  6.        {  
  7.            hidsaveType.Value = "Edit";  
  8.        // To get the Current Row Number  
  9.            GridViewRow row = (GridViewRow)((Control)e.CommandSource).NamingContainer;  
  10.            int rowIndex = row.RowIndex;  
  12.            txtitemCode.Text = row.Cells[2].Text;  
  13.            txtitemName.Text = row.Cells[3].Text;  
  14.            txtPrice.Text = row.Cells[4].Text;  
  15.            txtTax.Text = row.Cells[5].Text;  
  16.            txtDiscount.Text = row.Cells[6].Text;  
  17.            txtdescription.Text = row.Cells[7].Text;  
  18.            tdADD.Visible = true;  
  19.            btnAdd.ImageUrl = "~/Images/btnEdit.jpg";           
  21.        }  
  22.        else if (e.CommandName == "deletes")  
  23.        {  
  24.            // To get the Current Row Number  
  25.            GridViewRow row = (GridViewRow)((Control)e.CommandSource).NamingContainer;  
  26.            int rowIndex = row.RowIndex;  
  27.            DeleteItem(row.Cells[2].Text);  
  28.        }         
  29.    } 

Delete Record

Delete Record

The same as for edit, in the GridView “RowCommand” check for the Delete command being clicked and if so then call the function to perform the delete operation.

  1. // This method will delete the selected Rocord from DB  
  2.    private void DeleteItem(String ItemCode)  
  3.    {  
  4.        ShanuCRUDBizClass obj = new ShanuCRUDBizClass();  
  5.        obj.CRUD_Deletes(ItemCode);  
  7.        SelectList();  
  8.    } 

Delete Biz Class method: Here I used ExecuteNonQuery to delete the record from the DB. In my deleteItems method after a delete I called the SelectList method to rebind the result to the GridView.

  1. public void CRUD_Deletes(string ItemCode)  
  2. {  
  3.     try  
  4.     {            
  5.         SqlParameter[] paramArray = new SqlParameter[] { };  
  6.         AddParameter(ref paramArray, "@pTYPE""D5");  
  7.         AddParameter(ref paramArray, "@Item_Code", ItemCode);  
  9.         SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "USP_Item_CRUD", paramArray);  
  10.     }  
  11.     catch (Exception ex)  
  12.     {  
  13.         throw ex;  
  14.     }  

Note: To run my application, kindly create a table and insert sample data into your SQL Server. You can find the table creation and insert SQL script from this article and also I have provided the table creation script files in my Zip file. After creating tables, in my ASP.Net project you can find the WEB.Config file, you need to change it to your DB server name, your database name and your database UserName and Password in the WEB.Config Connection string.

Procedure to run the program : unzip the file.

  1. Open Visual Studio. Go to File and click Open Web site.

    In the File System select the "\SHANUCRUDV1.2" folder and you can see all the files in Solution Explorer.

  2. Run all the Database scripts in your SQL Server.

  3. In your ASP.Net open the "Web.Config" file then change the Database Connection string to your local database connection.

  4. Run the program. I hope this will help you.