CRUD with ADO.NET

ADO.NET is used to access database from .NET applications. The best way to understand any API is to use it. So, we use ADO.NET to perform Create, Read, Update and Delete details regarding books. In this sample application we use ADO.NET to directly talk to BOOKS table in the local database (database that is part of project).

Creating Web Project

Follow the steps given below to create required components in the project.

Start Visual Studio.NET. I expect version 2005 or above. Of course you can as well use Visual Web Developer 2005/2008.
Create a new website by selecting File->New->Website
Enter website name as ADONETCURD. The other options in the New Website window are File System and Visual C#.
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.
Select database in Solution Explorer and double click on that to open up Server Explorer.
Go to Tables node under database and select Add New Table option from popup menu.
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.

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>

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

So, we got a database, web.config and Database class to retrieve connectionstring from web.config. Let us now focus on web pages to interact with user.

menu.htm

This page displays the menu to user and navigates user to relevant page upon selection.

<html>
<head>
    <title>Books CRUD Application</title>
    <style>
     a  { font-weight:700; color:red;font-size:12pt}
    </style>
</head>
<body>
<h2>Books CRUD Application</h2>
This application shows how to perform Create, Retrieve, Update and Delete operations on BOOKS table through ADO.NET. 
<p />
ASP.NET pages access the BOOKS table through SQL using ADO.NET.
<p />

<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.

<%@ 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.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class addbook : System.Web.UI.Page
{
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try {
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into books(title,authors,price,publisher) values(@title,@authors,@price,@publisher)", con);
            cmd.Parameters.AddWithValue ("@title", txtTitle.Text);
            cmd.Parameters.AddWithValue ("@authors", txtAuthors.Text);
            cmd.Parameters.AddWithValue ("@price", txtPrice.Text);
            cmd.Parameters.AddWithValue ("@publisher", txtPublisher.Text);

            int count = cmd.ExecuteNonQuery ();
            if ( count == 1 )
                lblMsg.Text = "Book [" + txtTitle.Text + "] has been added!";
            else
                lblMsg.Text = "Could not add book!";
        }
        catch(Exception ex)
        {
             lblMsg.Text = "Error --> " + ex.Message ;
        }
        finally {
            con.Close();
        }
    }
}

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;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class deletebook : System.Web.UI.Page
{
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("delete from books where bookid = @bookid", con);
            cmd.Parameters.AddWithValue("@bookid", txtBookid.Text);
            if (cmd.ExecuteNonQuery() == 1)
            {
                lblMsg.Text = "Book Deleted Successfully!";
            }
            else
            {
                lblMsg.Text = "Sorry! Could Not Delete Book.";
            }
        }
        catch (Exception ex)
        {
            lblMsg.Text = "Error --> " + ex.Message;
        }
        finally
        {
            con.Close();
        }
    }
}

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;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class updatebook : System.Web.UI.Page
{
    protected void btnGetDetails_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from books where bookid = @bookid", con);
            cmd.Parameters.AddWithValue("@bookid", txtBookid.Text);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                // display data in textboxes
                txtTitle.Text = dr["title"].ToString();
                txtAuthors.Text = dr["authors"].ToString();
                txtPrice.Text = dr["price"].ToString();
                txtPublisher.Text = dr["publisher"].ToString();
                btnUpdate.Enabled = true;
            }
            else
            {
                lblMsg.Text = "Sorry! Invalid Book Id";
                btnUpdate.Enabled = false;
            }
            dr.Close();
        }
        catch (Exception ex)
        {
            lblMsg.Text = "Error --> " + ex.Message;
        }
        finally
        {
            con.Close();
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {

        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("update books set title=@title, authors = @authors, price = @price, publisher = @publisher where bookid = @bookid", con);
            cmd.Parameters.AddWithValue("@bookid", txtBookid.Text);
            cmd.Parameters.AddWithValue("@title", txtTitle.Text);
            cmd.Parameters.AddWithValue("@authors", txtAuthors.Text);
            cmd.Parameters.AddWithValue("@price", txtPrice.Text);
            cmd.Parameters.AddWithValue("@publisher", txtPublisher.Text);
            
            if ( cmd.ExecuteNonQuery () == 1)
            {
                lblMsg.Text = "Updated Successfully!";
            }
            else
            {
                lblMsg.Text = "Sorry! Could not update";
            }
        }
        catch (Exception ex)
        {
            lblMsg.Text = "Error --> " + ex.Message;
        }
        finally
        {
            con.Close();
        }
    }
}

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" %><!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 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.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class listbooks : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            SqlConnection con = new SqlConnection(Database.ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("select * from books order by title", con);
            DataSet ds = new DataSet();
            da.Fill(ds, "books");
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
        }
    }

}

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