Post
Ask Question

CRUD Application Using DLL and Stored Procedure

Introduction

This application shows how to do Create, Read, Update and Delete (CRUD) operations on a BOOKS table using a DLL and a Stored Procedure. ASP.NET pages access methods from the DLL that contains the DAL in it and Stored Procedures in the SQL Server database to do the actual operations on the BOOKS table.

Open Visual Studio








Open Microsoft Visual Studio 2013 and create an Empty Web Application with any suitable name.

Database Structure 

Open SQL Server to create a database (we have used database as the name of our database in SQL) with any suitable name and then create table and Stored Procedure for the CRUD operations.

Table Structure

  1. create Table books
  2. (  
  3.   bookid int identity(1,1) primary Key,  
  4.   title varchar(50) null,  
  5.   authors varchar(200) null,  
  6.   price money null,  
  7.   publisher varchar(50),  
  8. )  

Stored Procedure 

  1. CREATE PROCEDURE dbo.GetBooks  
  2. AS  
  3.  select * from books  
  1. CREATE PROCEDURE dbo.GetBook(@bookid int)  
  2. AS  
  3.  select * from books where bookid = @bookid  
  1. CREATE PROCEDURE dbo.AddBook( @title varchar(50), @authors varchar(200), @price money, @publisher varchar(50) )  
  2. AS  
  3. insert into books (title,authors,price,publisher)  
  4. values(@title,@authors,@price,@publisher)  
  1. CREATE PROCEDURE dbo.DeleteBook (@bookid int)  
  2. AS  
  3. delete from books where bookid = @bookid  
  4. if @@rowcount <> 1   
  5. raiserror('Invalid Book Id',16,1)  

  1. CREATE PROCEDURE dbo.UpdateBook( @bookid int, @title varchar(50), @authors varchar(200), @price money, @publisher varchar(50) )  
  2. AS  
  3. update books set title= @title, authors = @authors, price = @price, publisher = @publisher  
  4. where bookid = @bookid;  
  5. if @@rowcount <> 1   
  6. raiserror('Invalid Book Id',16,1)  
Class Library Structure
 
Now create a Class Library with the name BooksCrud.
 

Now add class files to create a Data Access Layer (DAL). We will add the following three class files:

  1. Book.cs (to set get, set Properties).
  2. BookDal.cs (to make the DAL).
  3. DataBase.cs (to import a connection string from Web.Config and use this file name the same as our database file name). 
 





Connection String

Open the Web.Config file to add a connection string. 

  1. <configuration>  
  2. <connectionStrings>  
  3. <add name="database" connectionString="Data Source=ServerName; Initial Catalog=Database; User Id=User; Password=Password;" providerName="System.Data.SqlClient"/>  
  4. </connectionStrings>  
  5. <system.web>  
  6. <compilation debug="true" targetFramework="4.5" />  
  7. <httpRuntime targetFramework="4.5" />  
  8. </system.web>  
  9. </configuration>  

If we have made our database on Windows Authentication mode then we need to add "Integrated Security=true".

Class Files

Import the connection string into the DataBase.cs file as in the following:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Configuration;  
  6.   
  7. namespace BooksCrud  
  8. {  
  9.     public class DataBase  
  10.     {  
  11.       static public String ConnectionString  
  12.       {  
  13.           get  
  14.           {  
  15.             return ConfigurationManager.ConnectionStrings["database"].ConnectionString;  
  16.           }  
  17.       }  
  18.    }  
  19. }  

Now prepare the get and set properties in the Book.cs file as in the following:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5.   
  6. namespace BooksCrud  
  7. {  
  8.    public class Book  
  9.    {  
  10.       public int Bookid { get; set; }  
  11.       public string Title { get; set; }  
  12.       public string Authors { get; set; }  
  13.       public string Publishers { get; set; }  
  14.       public double Price { get; set; }  
  15.    }  
  16. }  
Create the DAL in BookDal.cs as in the following:
  1. using System;  
  2. using System.Data.SqlClient;  
  3. using System.Data;  
  4. using System.Configuration;  
  5.   
  6. namespace BooksCrud  
  7. {  
  8.    public class BookDal  
  9.    {  
  10.      public static DataSet GetBooks()  
  11.      {  
  12.          SqlConnection con = new SqlConnection(DataBase.ConnectionString);  
  13.          SqlDataAdapter da = new SqlDataAdapter("getbooks", con);  
  14.          da.SelectCommand.CommandType = CommandType.StoredProcedure;  
  15.          DataSet ds = new DataSet();  
  16.          da.Fill(ds, "books");  
  17.          return ds;  
  18.       }  
  19.      public static Book GetBook(int bookid)  
  20.      {  
  21.          SqlConnection con = new SqlConnection(DataBase.ConnectionString);  
  22.          try  
  23.          {  
  24.             con.Open();  
  25.             SqlCommand cmd = new SqlCommand("getbook", con);  
  26.             cmd.CommandType = CommandType.StoredProcedure;  
  27.             cmd.Parameters.AddWithValue("@bookid", bookid);     
  28.             SqlDataReader dr = cmd.ExecuteReader();  
  29.             if (dr.Read())  
  30.             {  
  31.                Book b = new Book();  
  32.                b.Title = dr["title"].ToString();  
  33.                b.Authors = dr["authors"].ToString();  
  34.                b.Price = Double.Parse(dr["price"].ToString());  
  35.                b.Publishers = dr["publisher"].ToString();  
  36.                return b;  
  37.             }  
  38.             else  
  39.                return null;  
  40.          }  
  41.          catch (Exception ex)  
  42.          {  
  43.             return null;  
  44.          }  
  45.          finally  
  46.          {  
  47.             con.Close();  
  48.          }  
  49.       }  
  50.   
  51.       public static string AddBook(string title, string authors, double price, string publisher)  
  52.       {  
  53.          SqlConnection con = new SqlConnection(DataBase.ConnectionString);  
  54.          try  
  55.          {  
  56.             con.Open();  
  57.             SqlCommand cmd = new SqlCommand("addbook", con);  
  58.             cmd.CommandType = CommandType.StoredProcedure;  
  59.             cmd.Parameters.AddWithValue("@title", title);  
  60.             cmd.Parameters.AddWithValue("@authors", authors);  
  61.             cmd.Parameters.AddWithValue("@price", price);  
  62.             cmd.Parameters.AddWithValue("@publisher", publisher);  
  63.             cmd.ExecuteNonQuery();  
  64.             return null; // success   
  65.          }  
  66.          catch (Exception ex)  
  67.          {  
  68.             return ex.Message; // return error message  
  69.          }  
  70.          finally  
  71.          {  
  72.             con.Close();  
  73.          }  
  74.       }  
  75.       public static string DeleteBook(int bookid)  
  76.       {  
  77.          SqlConnection con = new SqlConnection(DataBase.ConnectionString);  
  78.          try  
  79.          {  
  80.             con.Open();  
  81.             SqlCommand cmd = new SqlCommand("deletebook", con);  
  82.             cmd.CommandType = CommandType.StoredProcedure;  
  83.             cmd.Parameters.AddWithValue("@bookid", bookid);  
  84.             cmd.ExecuteNonQuery();  
  85.             return null; // success   
  86.          }  
  87.          catch (Exception ex)  
  88.          {  
  89.             return ex.Message; // return error message  
  90.          }  
  91.          finally  
  92.          {  
  93.             con.Close();  
  94.          }  
  95.       }  
  96.       public static string UpdateBook(int bookid, string title, string authors, double price, string publisher)  
  97.       {  
  98.          SqlConnection con = new SqlConnection(DataBase.ConnectionString);  
  99.          try  
  100.          {  
  101.             con.Open();  
  102.             SqlCommand cmd = new SqlCommand("updatebook", con);  
  103.             cmd.CommandType = CommandType.StoredProcedure;  
  104.             cmd.Parameters.AddWithValue("@bookid", bookid);  
  105.             cmd.Parameters.AddWithValue("@title", title);  
  106.             cmd.Parameters.AddWithValue("@authors", authors);  
  107.             cmd.Parameters.AddWithValue("@price", price);  
  108.             cmd.Parameters.AddWithValue("@publisher", publisher);  
  109.             cmd.ExecuteNonQuery();  
  110.             return null; // success   
  111.          }  
  112.          catch (Exception ex)  
  113.          {  
  114.             return ex.Message; // return error message  
  115.          }  
  116.          finally  
  117.          {  
  118.             con.Close();  
  119.          }  
  120.       }  
  121.    }  
  122. }  
Build the Class Library


After a successful build, now the BookCrud.dll is ready for use in our Web Project.

Now add a reference to our web project.

 
 



Web Forms Structure

menu.html

  1. <!DOCTYPE html>  
  2.    <html>  
  3.       <head>  
  4.          <title>CRUD Application using .dll, DAL and Stored Procedures</title>  
  5.          <style>  
  6.          a {  
  7.                font-weight: 700;  
  8.                color: red;  
  9.                font-size: 12pt;  
  10.            }  
  11.          </style>  
  12.       </head>  
  13.       <body>  
  14.          <h2>CRUD Application using DLL and Stored Procedure</h2>  
  15.           This application shows how to perform Create, Read , Update and Delete (CRUD) operations.  
  16.           ASP.NET pages access methods in DAL (Data Access Layer),which call stored procedures in  
  17.           Sql Server Database to perform the actual operations on BOOKS table.  
  18.   
  19.          <a href="addbook.aspx">Add New Book</a>  
  20.          <p />  
  21.          <a href="updatebook.aspx">Update Book</a>  
  22.          <p />  
  23.          <a href="deletebook.aspx">Delete Book</a>  
  24.          <p />  
  25.          <a href="listbook.aspx">List Books</a>  
  26.       </body>  
  27.    </html>  

addbook.aspx

  1. <!DOCTYPE html>  
  2.    <html xmlns="http://www.w3.org/1999/xhtml" >  
  3.       <head id="Head1" runat="server">  
  4.          <title>Add Book</title>  
  5.       </head>  
  6.       <body>  
  7.          <form id="form1" runat="server">  
  8.          <h2>Add New Book</h2>  
  9.          <table>  
  10.             <tr>  
  11.                <td>Book Title</td>  
  12.                <td><asp:TextBox ID="txtTitle" runat="server"></asp:TextBox></td>  
  13.             </tr>  
  14.             <tr>  
  15.                <td>Authors</td>  
  16.                <td><asp:TextBox ID="txtAuthors" runat="server"></asp:TextBox></td>  
  17.             </tr>  
  18.             <tr>  
  19.                <td>Price</td>  
  20.                <td><asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td>  
  21.             </tr>  
  22.             <tr>  
  23.                <td>Publisher</td>  
  24.                <td><asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td>  
  25.             </tr>  
  26.         </table>  
  27.            <br />  
  28.            <asp:Button ID="btnAdd" runat="server" Text="Add Book" OnClick="btnAdd_Click" /><br />  
  29.            <br />  
  30.            <asp:Label ID="lblMsg" runat="server" EnableViewState="False"></asp:Label><br />  
  31.             <p />  
  32.             <a href="menu.html">Go Back To Menu</a>  
  33.          </form>  
  34.         </body>  
  35.    </html>  

addbook.aspx.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using BooksCrud;  
  10.   
  11. namespace BooksView  
  12. {  
  13.    public partial class addbook : System.Web.UI.Page  
  14.    {  
  15.       protected void Page_Load(object sender, EventArgs e)  
  16.       {   
  17.       }  
  18.       protected void btnAdd_Click(object sender, EventArgs e)  
  19.       {  
  20.          string msg = BookDal.AddBook(txtTitle.Text, txtAuthors.Text, Double.Parse(txtPrice.Text), txtPublisher.Text);  
  21.          if (msg == null)  
  22.             lblMsg.Text = "Book Has Been Added Successfully!";  
  23.          else  
  24.             lblMsg.Text = "Error -> " + msg;  
  25.       }  
  26.    }  
  27. }  

deletebook.aspx

  1. <!DOCTYPE html>  
  2.   
  3.    <html xmlns="http://www.w3.org/1999/xhtml" >  
  4.       <head id="Head2" runat="server">  
  5.          <title>Delete Book</title>  
  6.       </head>  
  7.       <body>  
  8.          <form id="form2" runat="server">  
  9.             <h2>Delete Book</h2>  
  10.             Enter Book Id :   
  11.             <asp:TextBox ID="txtBookid" runat="server"></asp:TextBox>  
  12.             <p />  
  13.             <asp:Button ID="btnDelete" runat="server" Text="Delete Book" OnClick="btnDelete_Click"/>  
  14.             <p />  
  15.             <asp:Label ID="lblMsg" runat="server" EnableViewState="False"></asp:Label>  
  16.             <p />  
  17.             <a href="menu.html">Go Back To Menu</a>  
  18.          </form>  
  19.       </body>  
  20.    </html>  
deletebook.aspx.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using BooksCrud;  
  10.   
  11. namespace BooksView  
  12. {  
  13.    public partial class deletebook : System.Web.UI.Page  
  14.    {  
  15.       protected void Page_Load(object sender, EventArgs e)  
  16.       {  
  17.       }  
  18.       protected void btnDelete_Click(object sender, EventArgs e)  
  19.       {  
  20.          string msg = BookDal.DeleteBook(Int32.Parse(txtBookid.Text));  
  21.          if (msg == null)  
  22.             lblMsg.Text = "Book Has Been Deleted Successfully!";  
  23.          else  
  24.             lblMsg.Text = "Error -> " + msg;  
  25.       }  
  26.    }  
  27. }  

listbook.aspx 

  1. <!DOCTYPE html>  
  2.   
  3.    <html xmlns="http://www.w3.org/1999/xhtml" >  
  4.       <head id="Head4" runat="server">  
  5.          <title>List Books</title>  
  6.       </head>  
  7.       <body>  
  8.          <form id="form4" runat="server">  
  9.             <h2>List Of Books</h2>  
  10.             <asp:GridView ID="GridView1" runat="server" Width="100%">  
  11.                <HeaderStyle BackColor="Red" Font-Bold="True" ForeColor="White" />  
  12.             </asp:GridView>  
  13.             <br />  
  14.             <a href="menu.html">Go Back To Menu</a>  
  15.          </form>  
  16.       </body>  
  17.    </html> 

listbook.aspx.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using BooksCrud;  
  10.   
  11. namespace BooksView  
  12. {  
  13.    public partial class listbook : System.Web.UI.Page  
  14.    {  
  15.       protected void Page_Load(object sender, EventArgs e)  
  16.       {  
  17.          GridView1.DataSource = BookDal.GetBooks();  
  18.          GridView1.DataBind();  
  19.       }  
  20.    }  
  21. }  

updatebook.aspx

  1. <!DOCTYPE html>  
  2.   
  3.    <html xmlns="http://www.w3.org/1999/xhtml" >  
  4.       <head id="Head3" runat="server">  
  5.          <title>Update Book</title>  
  6.       </head>  
  7.       <body>  
  8.          <form id="form3" runat="server">  
  9.          <h2>Update Book</h2>  
  10.          <table>  
  11.             <tr>  
  12.                <td>Book ID</td>  
  13.                <td><asp:TextBox ID="txtBookid" runat="server"></asp:TextBox>  
  14.                <asp:Button ID="btnGetDetails" runat="server" Text="Get Details" OnClick="btnGetDetails_Click" />  
  15.                </td>  
  16.             </tr>  
  17.             <tr>  
  18.                <td>Book Title</td>  
  19.                <td><asp:TextBox ID="txtTitle" runat="server"></asp:TextBox></td>  
  20.             </tr>  
  21.             <tr>  
  22.                <td>Authors</td>  
  23.                <td><asp:TextBox ID="txtAuthors" runat="server"></asp:TextBox></td>  
  24.             </tr>  
  25.             <tr>  
  26.                <td>Price</td>  
  27.                <td><asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td>  
  28.             </tr>  
  29.             <tr>  
  30.                <td>Publisher</td>  
  31.                <td><asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td>  
  32.             </tr>  
  33.         </table>  
  34.         <br />  
  35.         <asp:Button ID="btnUpdate" runat="server" Text="Update Book" Enabled="false" OnClick="btnUpdate_Click" /><br />  
  36.         <br />  
  37.         <asp:Label ID="lblMsg" runat="server" EnableViewState="False"></asp:Label><br />  
  38.         <p />  
  39.         <a href="menu.html">Go Back To Menu</a>  
  40.         </form>  
  41.       </body>  
  42.    </html>  

updatebook.aspx.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using BooksCrud;  
  10.   
  11. namespace BooksView  
  12. {  
  13.    public partial class updatebook : System.Web.UI.Page  
  14.    {  
  15.       protected void Page_Load(object sender, EventArgs e)  
  16.       {  
  17.       }  
  18.       protected void btnGetDetails_Click(object sender, EventArgs e)  
  19.       {  
  20.          Book b = BookDal.GetBook(Int32.Parse(txtBookid.Text));  
  21.          if (b != null)  
  22.          {  
  23.             txtTitle.Text = b.Title;  
  24.             txtAuthors.Text = b.Authors;  
  25.             txtPrice.Text = b.Price.ToString();  
  26.             txtPublisher.Text = b.Publishers;  
  27.             btnUpdate.Enabled = true;  
  28.          }  
  29.          else  
  30.          {  
  31.             lblMsg.Text = "Sorry! Book Id Not Found";  
  32.             btnUpdate.Enabled = false;  
  33.          }  
  34.       }  
  35.      protected void btnUpdate_Click(object sender, EventArgs e)  
  36.      {              string msg = BookDal.UpdateBook(Int32.Parse(txtBookid.Text), txtTitle.Text, txtAuthors.Text, Double.Parse(txtPrice.Text), txtPublisher.Text);  
  37.         if (msg == null)  
  38.          lblMsg.Text = "Updated Book Details Successfully!";  
  39.         else  
  40.          lblMsg.Text = "Error -> " + msg;  
  41.       }  
  42.   
  43.    }  
  44. }  
Summary

In this article we performed CRUD Operations but using a DLL. The new thing with our DLL is that this DLL file contains a Data Access Layer (DAL) in it, with Stored Procedures in SQL Server.


Similar Articles