CRUD with ADO.NET, DAL and Stored Procedures

We have already seen how to use ADO.NET to create simple CRUD application in ASP.NET with SQL Server Database. In this, I will show you how to use Data Access Layer (DAL) and Stored procedures to do the same operations. So the hierarchy of our new application is as follows:

The interface and database structure of this application remains same as the previous one. You can find an application to perform CRUD operations with only ADO.NET without using any DAL and Stored Procedures in CRUD with ADO.NET.

Creating Web Project

Follow the steps given below to create required components in the project.
  1. Start Visual Studio.NET. I expect version 2008. Of course you can as well use Visual Web Developer 2008.
  2. Create a new website by selecting File->New->Website
  3. Enter website name as ADONETCRUD2. The other options in the New Website window are File System and Visual C#.
  4. Add new database to website using Website->Add New Item -> SQL Server Database. Optionally you can change database name. You will be prompted to put database in App_Data directory. Say yes.
  5. Select database in Solution Explorer and double click on that to open up Server Explorer.
  6. Go to Tables nodes under database and select Add New Table option from popup menu.
  7. Create Books table with the structure shown in the picture below. Make sure you set bookid as primary key and also identify column by setting IsIdentity option to yes.

Creating Stored Procedures

We want to access BOOKS table only through stored procedures so that any changes to database structure only effects stored procedures and not our C# code.

Here are the stored procedures required for this application. Create all these stored procedres using Server Explorer/Database Explorer.

CREATE PROCEDURE dbo.GetBooks
AS
   select * from books


CREATE PROCEDURE dbo.GetBook(@bookid int)
AS
    select * from books where bookid = @bookid


CREATE PROCEDURE dbo.AddBook(  @title varchar(50), @authors varchar(200), @price money, @publisher varchar(50) )
AS

   insert into books (title,authors,price,publisher)
      values(@title,@authors,@price,@publisher)
      
   
CREATE PROCEDURE dbo.DeleteBook	(@bookid int)
AS
	delete from books where bookid = @bookid
	if @@rowcount <> 1 
	    raiserror('Invalid Book Id',16,1)


CREATE PROCEDURE dbo.UpdateBook( @bookid int,  @title varchar(50), @authors varchar(200), @price money, @publisher varchar(50) )
AS

   update books set title= @title, authors  = @authors, price = @price, publisher = @publisher
   where bookid = @bookid;
   
   if @@rowcount <> 1 
      raiserror('Invalid Book Id',16,1)
         

That's all we have to create. Now we can concentrate on ASP.NET application.

Create data access components in ASP.NET Application

We need to configure web.config to place connection string in connectionStrings section. Then we create classes related to database access.
  1. Add the following entries under connectionStrings section in web.config
        <connectionStrings>
          <add name="database" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" 
                    providerName="System.Data.SqlClient"/>
        </connectionStrings>
    
  2. Add a class to the website (and it is placed in App_Code folder) with the name Database and put the following code in it.
    using System;
    using System.Configuration;
    using System.Web.Configuration;
    
    public class Database
    {
        static public String ConnectionString 
        {
           get
           {    // get connection string with name  database from  web.config.
                return WebConfigurationManager.ConnectionStrings["database"].ConnectionString;
           }
        }
    }
    
    
  3. Create Book class to represent a book.
    using System;
    
    public class Book
    {
        // Using automatically implemented properties feature of C# 3.0
        public int Bookid { get; set; }
        public string Title { get; set; }
        public string Authors { get; set; }
        public string Publishers { get; set; }
        public double Price { get; set; }
    }
    
  4. Create a class, BooksDAL, to call stored procedures using ADO.NET. This is DAL for our application. Total data access must be done through DAL.
    using System;
    using System.Data.SqlClient;
    using System.Data;
    
    public class BooksDAL
    {
        public static DataSet GetBooks()
        {
            SqlConnection con = new SqlConnection(Database.ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("getbooks", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            da.Fill(ds, "books");
            return ds;
        }
    
        public static Book GetBook(int bookid)
        {
            SqlConnection con = new SqlConnection(Database.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("getbook", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@bookid", bookid);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    Book b = new Book();
                    b.Title = dr["title"].ToString();
                    b.Authors = dr["authors"].ToString();
                    b.Price = Double.Parse(dr["price"].ToString());
                    b.Publishers = dr["publisher"].ToString();
                    return b;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
    
        public static string AddBook(string title, string authors, double price, string publisher)
        {
            SqlConnection con = new SqlConnection(Database.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("addbook", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@title", title);
                cmd.Parameters.AddWithValue("@authors",authors);
                cmd.Parameters.AddWithValue("@price",price);
                cmd.Parameters.AddWithValue("@publisher", publisher);
                cmd.ExecuteNonQuery();
                return null; // success 
            }
            catch (Exception ex)
            {
                return ex.Message;  // return error message
            }
            finally
            {
                con.Close();
            }
        }
        public static string DeleteBook(int bookid)
        {
            SqlConnection con = new SqlConnection(Database.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("deletebook", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@bookid", bookid);
                cmd.ExecuteNonQuery();
                return null; // success 
            }
            catch (Exception ex)
            {
                return ex.Message;  // return error message
            }
            finally
            {
                con.Close();
            }
        }
        public static string UpdateBook(int bookid, string title, string authors, double price, string publisher)
        {
            SqlConnection con = new SqlConnection(Database.ConnectionString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("updatebook", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@bookid", bookid);
                cmd.Parameters.AddWithValue("@title", title);
                cmd.Parameters.AddWithValue("@authors", authors);
                cmd.Parameters.AddWithValue("@price", price);
                cmd.Parameters.AddWithValue("@publisher", publisher);
                cmd.ExecuteNonQuery();
                return null; // success 
            }
            catch (Exception ex)
            {
                return ex.Message;  // return error message
            }
            finally
            {
                con.Close();
            }
        }
    }
    
Now, let us focus on user interface. Create a couple of ASP.NET pages and write code to access DAL from ASP.NET pages.

menu.htm

This page displays the menu to user and navigates user to relevant page upon skelection.
<html>
<head>
    <title>Books CRUD Application with DAL and Stored Procedures lt;/title>
    <style>
     a  { font-weight:700; color:red;font-size:12pt}
    </style>
</head>
<body>
<h2>Books CRUD Application  with DAL and Stored Procedures </h2>
This application shows how to perform Create, Read , Update and Delete (CRUD) operations on BOOKS table through ADO.NET, 
DAL and Stored Procedures. ASP.NET pages access methods in DAL (Data Access Layer),which call stored procedures in 
Sql Server Database to perform the actual operations on BOOKS table. 

<a href="addbook.aspx">Add New Book</a>
<p />
<a href="updatebook.aspx">Update Book</a>
<p />
<a href="deletebook.aspx">Delete Book</a>
<p />
<a href="listbooks.aspx">List Books</a>
</body>
</html>

addbook.aspx and addbook.aspx.cs

Create a page to take data regarding a new book and insert that into BOOKS table. Code in this page access AddBook method of BooksDAL, which in turn calls stored procedure AddBook to insert a row into BOOKS table.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="addbook.aspx.cs" Inherits="addbook" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Add Book</title>
</head>
<body>
    <form id="form1" runat="server">
        <h2>
        Add New Book</h2>
        <table>
            <tr>
                <td>
                    Book Title</td>
                <td><asp:TextBox ID="txtTitle" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>
                    Authors</td>
                <td>
                    <asp:TextBox ID="txtAuthors" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>
                    Price</td>
                <td>
                    <asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td>
            </tr>
            
            <tr>
                <td>
                    Publisher</td>
                <td>
                    <asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td>
            </tr>
        </table>
        <br />
        <asp:Button ID="btnAdd" runat="server" Text="Add Book" OnClick="btnAdd_Click" /><br />
        <br />
        <asp:Label ID="lblMsg" runat="server" EnableViewState="False"></asp:Label><br />
        <p />
        <a href="menu.htm">Go Back To Menu</a>
    </form>
</body>
</html>
using System;
using System.Data;
using System.Data.SqlClient;

public partial class addbook : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        string msg =BooksDAL.AddBook(txtTitle.Text, txtAuthors.Text, Double.Parse(txtPrice.Text), txtPublisher.Text);
        if (msg == null)
            lblMsg.Text = "Books Has Been Added Successfully!";
        else
            lblMsg.Text = "Error -> " + msg;

    }
}

deletebook.aspx and deletebook.aspx.cs

The following files are used to take bookid from user and delete the book with the given id.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="deletebook.aspx.cs" Inherits="deletebook" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head2" runat="server">
    <title>Delete Book</title>
</head>
<body>
    <form id="form2" runat="server">
    <h2>Delete Book</h2>
    Enter Book Id : 
    <asp:TextBox ID="txtBookid" runat="server"></asp:TextBox>
    <p />
    <asp:Button ID="btnDelete" runat="server" Text="Delete Book" OnClick="btnDelete_Click"/>
    <p />
    <asp:Label ID="Label1" runat="server" EnableViewState="False"></asp:Label>
    <p />
    <a href="menu.htm">Go Back To Menu</a>
    </form>
</body>
</html>
using System;
using System.Data;
using System.Data.SqlClient;

public partial class deletebook : System.Web.UI.Page
{
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        string msg = BooksDAL.DeleteBook(Int32.Parse(txtBookid.Text));
        if (msg == null)
            lblMsg.Text = "Book Has Been Deleted Successfully!";
        else
            lblMsg.Text = "Error -> " + msg;

    }
}

updatebooks.aspx and updatebook.aspx.cs

Updation page takes bookid, retrieves details and displays them in textboxes. User can change the details and click on Update button to update BOOKS table.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="updatebook.aspx.cs" Inherits="updatebook" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head3" runat="server">
    <title>Update Book</title>
</head>
<body>
    <form id="form3" runat="server">
   <h2>
        Update Book</h2>
        <table>
             <tr>
                <td>Book ID</td>
                <td>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    <asp:Button ID="btnGetDetails" runat="server" Text="Get Details" OnClick="btnGetDetails_Click" />
                </td>
            </tr>
       
            <tr>
                <td>
                    Book Title</td>
                <td>
                    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>
                    Authors</td>
                <td>
                    <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td>
                    Price</td>
                <td>
                    <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox></td>
            </tr>
            
            <tr>
                <td>
                    Publisher</td>
                <td>
                    <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox></td>
            </tr>
            
            
        </table>
        <br />
        <asp:Button ID="btnUpdate" runat="server" Text="Update Book" Enabled="false" OnClick="btnUpdate_Click" /><br />
        <br />
        <asp:Label ID="Label2" runat="server" EnableViewState="False"></asp:Label><br />
        <p />
        <a href="menu.htm">Go Back To Menu</a>
    </form>
</body>
</html>

using System;
using System.Data;
using System.Data.SqlClient;

public partial class updatebook : System.Web.UI.Page
{
    protected void btnGetDetails_Click(object sender, EventArgs e)
    {
        Book b = BooksDAL.GetBook(Int32.Parse(txtBookid.Text));
        if (b != null)
        {
            txtTitle.Text = b.Title;
            txtAuthors.Text = b.Authors;
            txtPrice.Text = b.Price.ToString();
            txtPublisher.Text = b.Publishers;
            btnUpdate.Enabled = true;
        }
        else
        {
            lblMsg.Text = "Sorry! Book Id Not Found";
            btnUpdate.Enabled  = false;
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
       string msg = BooksDAL.UpdateBook ( Int32.Parse(txtBookid.Text), txtTitle.Text,  txtAuthors.Text, Double.Parse( txtPrice.Text), txtPublisher.Text);
       if (msg == null)
           lblMsg.Text = "Updated Book Details Successfully!";
       else
           lblMsg.Text = "Error -> " + msg;
    }
}

listbooks.aspx and listbooks.aspx.cs

List page uses gridview to display details of books taken from BOOKS table using DataSet.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="listbooks.aspx.cs" Inherits="listbooks" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head4" runat="server">
    <title>List Books</title>
</head>
<body>
    <form id="form4" runat="server">
        <h2>List Of Books</h2>
        <asp:GridView ID="GridView1" runat="server" Width="100%">
            <HeaderStyle BackColor="Red" Font-Bold="True" ForeColor="White" />
        </asp:GridView>
        <br />
        <a href="menu.htm">Go Back To Menu</a>
    </form>
</body>
</html>
using System;
using System.Data;
using System.Data.SqlClient;

public partial class listbooks : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
            GridView1.DataSource = BooksDAL.GetBooks ();
            GridView1.DataBind();
    }
}

That completes our CRUD application. Run menu.htm and select links to perform different options.


Home    Blogs

Post Your Comment

Image

Enter the code given in the above image :
Your Name :
Your Email Address :
Comment :

Comments


Posted By Chandra On 27-Mar-09 07:32:06 PM

its very helpful for me.... thanks sir.
Is there any projects related to 3-tier architecture i mean any projects based on 3-tier architecture



Posted By sridhar On 08-Apr-09 02:12:40 PM

Thanks sir,
This is very helpful for me...
i want one favour from u r side....
coule u please conduct a workshop on MVC with small project... i want to learn MVC architecture with good examples and how to apply MVC architecture of existing projects...



Posted By Francisco Montero On 06-Jun-09 05:05:30 AM

Excelent example!. Thank You very much. It help me to understand ASP.NET



Posted By vasu On 10-Jun-09 05:42:35 PM

Excellent!!!!!!!!! Many thanks



Posted By RIDVAN ARIK On 26-Feb-10 07:01:45 PM

It's very helpful for me.
Thanks sir. Eskisehir / TURKEY 26/02/2010



Posted By IntaftinS On 04-Mar-10 08:49:15 AM

I do think this is a most incredible website for proclaiming great wonders of Our God!



Posted By Manivannan On 11-May-10 09:37:36 AM

Nice work...



Posted By srinivas On 26-Aug-10 10:53:45 AM

Excelent!, Very Good Example....thanks sir, thank you very much



Posted By CRUD Queries On 06-Nov-11 04:25:19 PM

Do you suggest any tool which generates these types of stored procedures automatically? Like www.tools4sql.net



Posted By Rajeeivf On 10-Nov-11 03:21:05 PM

Nice code for absolute beginners...keep it up..



Posted By waive jnr On 12-Nov-11 11:59:11 PM

hey, you seem to be an asp.net guru. am kinda lost and i need your help.Am developing my first application and now am lost. I working on a club application, i have 5 tables (relational database) Table 1- Individual (fname, lname, date of birth, sex, passport, thumbprint, signiture) table 2-Spouse (name, sex, date of birth, tribe) table 3-Kids (name, date of birth,sex) table 3-Address (street, Town, state, country, Post box number) table 4-Employment (Occupation, name of firm, address of firm, email, phone, mobile) table 5-Referee (name, sex, address, signiture).

My question is, how do i create an insert form to insert the require details into these 5 tables? i can create one insert form to insert into one table at a time but not 5, i can also use join to retrieve the data once inserted. Kindly help me pleeeaaase....



Posted By ratnesh On 02-Jan-12 06:54:41 PM

good



Posted By Ed On 27-Feb-12 10:40:49 PM

Nice!



Posted By sheta sanjay On 22-Mar-12 06:50:09 PM

Its Very Good Example.

Is there Any simple Project to understand 3 tier properly.
send me mail.



Posted By Raju On 12-Apr-12 08:22:09 AM

nice buddy....
ihave seen lot many xample but this as simple and complete



Posted By surendar reddy.G On 14-Jun-12 06:26:43 PM

your website very good for beginners .I hope you that you will make some more articles for beginners in .net



Posted By Dhanalakshmi On 30-Oct-12 10:16:51 AM

its very usefull and it was great tutorial for biginners.Thanks you so much.
and keep post like this article especialy sql joins with stored procedure please.



Posted By meghana On 11-Dec-12 07:40:05 AM

its really nice. that helped me lot.. thankyou



Posted By Vasu Ravuri On 13-Jan-13 05:59:38 PM

The code you provided is nice.
Friends please look at below link for more projects and source codes
http://dotnettrainer.wordpress.com/sample-dot-net-projects-and-sample-dot-net-source-codes/



Posted By james On 01-Feb-13 06:09:54 PM

good



Posted By Ali On 18-Oct-13 07:50:50 PM

I got lot f knowledge...thnxxx

Copyright © Srikanth Technologies. All rights reserved.