CRUD with ADO.NET, DAL and Stored Procedures

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

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

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)
         

Create data access components in ASP.NET Application

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();
           }
       }
   }
   

menu.htm

<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

<%@ 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

<%@ 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

<%@ 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

<%@ 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();
    }
}

Post Your Comment