Insert Update Delete Select Book Details with Multi Select & Image File Upload
In this ASP.NET Database tutorial we will perform Insert Update, Delete & Select operations using C# programming in ASP.NET and MS SQL Server Database. We will also perform multi select list box & store that data in single column in the Database. We will also perform Image file upload to server & store the uploaded image link to the database.
Watch the video tutorial at the end of this video to understand more about this tutorial. Use the code given below for this page.
Download all Images for this project – CLICK HERE
adminbookinventory.aspx code –
<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="adminbookinventory.aspx.cs" Inherits="WebApplication3.adminbookinventory" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<script type="text/javascript">
$(document).ready(function () {
$(".table").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable();
});
function readURL(input) {
if (input.files && input.files[0]) {
var reader = new FileReader();
reader.onload = function (e) {
$('#imgview').attr('src', e.target.result);
};
reader.readAsDataURL(input.files[0]);
}
}
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<div class="container-fluid">
<div class="row">
<div class="col-md-5">
<div class="card">
<div class="card-body">
<div class="row">
<div class="col">
<center>
<h4>Book Details</h4>
</center>
</div>
</div>
<div class="row">
<div class="col">
<center>
<img id="imgview" Height="150px" Width="100px" src="book_inventory/books1.png" />
</center>
</div>
</div>
<div class="row">
<div class="col">
<hr>
</div>
</div>
<div class="row">
<div class="col">
<asp:FileUpload onchange="readURL(this);" class="form-control" ID="FileUpload1" runat="server" />
</div>
</div>
<div class="row">
<div class="col-md-4">
<label>Book ID</label>
<div class="form-group">
<div class="input-group">
<asp:TextBox CssClass="form-control" ID="TextBox1" runat="server" placeholder="Book ID"></asp:TextBox>
<asp:Button class="form-control btn btn-primary" ID="Button4" runat="server" Text="Go" OnClick="Button4_Click" />
</div>
</div>
</div>
<div class="col-md-8">
<label>Book Name</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox2" runat="server" placeholder="Book Name"></asp:TextBox>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label>Language</label>
<div class="form-group">
<asp:DropDownList class="form-control" ID="DropDownList1" runat="server">
<asp:ListItem Text="English" Value="English" />
<asp:ListItem Text="Hindi" Value="Hindi" />
<asp:ListItem Text="Marathi" Value="Marathi" />
<asp:ListItem Text="French" Value="French" />
<asp:ListItem Text="German" Value="German" />
<asp:ListItem Text="Urdu" Value="Urdu" />
</asp:DropDownList>
</div>
<label>Publisher Name</label>
<div class="form-group">
<asp:DropDownList class="form-control" ID="DropDownList2" runat="server">
<asp:ListItem Text="Publisher 1" Value="Publisher 1" />
<asp:ListItem Text="Publisher 2" Value="Publisher 2" />
</asp:DropDownList>
</div>
</div>
<div class="col-md-4">
<label>Author Name</label>
<div class="form-group">
<asp:DropDownList class="form-control" ID="DropDownList3" runat="server">
<asp:ListItem Text="A1" Value="a1" />
<asp:ListItem Text="a2" Value="a2" />
</asp:DropDownList>
</div>
<label>Publish Date</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox3" runat="server" placeholder="Date" TextMode="Date"></asp:TextBox>
</div>
</div>
<div class="col-md-4">
<label>Genre</label>
<div class="form-group">
<asp:ListBox CssClass="form-control" ID="ListBox1" runat="server" SelectionMode="Multiple" Rows="5">
<asp:ListItem Text="Action" Value="Action" />
<asp:ListItem Text="Adventure" Value="Adventure" />
<asp:ListItem Text="Comic Book" Value="Comic Book" />
<asp:ListItem Text="Self Help" Value="Self Help" />
<asp:ListItem Text="Motivation" Value="Motivation" />
<asp:ListItem Text="Healthy Living" Value="Healthy Living" />
<asp:ListItem Text="Wellness" Value="Wellness" />
<asp:ListItem Text="Crime" Value="Crime" />
<asp:ListItem Text="Drama" Value="Drama" />
<asp:ListItem Text="Fantasy" Value="Fantasy" />
<asp:ListItem Text="Horror" Value="Horror" />
<asp:ListItem Text="Poetry" Value="Poetry" />
<asp:ListItem Text="Personal Development" Value="Personal Development" />
<asp:ListItem Text="Romance" Value="Romance" />
<asp:ListItem Text="Science Fiction" Value="Science Fiction" />
<asp:ListItem Text="Suspense" Value="Suspense" />
<asp:ListItem Text="Thriller" Value="Thriller" />
<asp:ListItem Text="Art" Value="Art" />
<asp:ListItem Text="Autobiography" Value="Autobiography" />
<asp:ListItem Text="Encyclopedia" Value="Encyclopedia" />
<asp:ListItem Text="Health" Value="Health" />
<asp:ListItem Text="History" Value="History" />
<asp:ListItem Text="Math" Value="Math" />
<asp:ListItem Text="Textbook" Value="Textbook" />
<asp:ListItem Text="Science" Value="Science" />
<asp:ListItem Text="Travel" Value="Travel" />
</asp:ListBox>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label>Edition</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox9" runat="server" placeholder="Edition"></asp:TextBox>
</div>
</div>
<div class="col-md-4">
<label>Book Cost(per unit)</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox10" runat="server" placeholder="Book Cost(per unit)" TextMode="Number"></asp:TextBox>
</div>
</div>
<div class="col-md-4">
<label>Pages</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox11" runat="server" placeholder="Pages" TextMode="Number"></asp:TextBox>
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<label>Actual Stock</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox4" runat="server" placeholder="Actual Stock" TextMode="Number"></asp:TextBox>
</div>
</div>
<div class="col-md-4">
<label>Current Stock</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox5" runat="server" placeholder="Book Cost(per unit)" TextMode="Number" ReadOnly="True"></asp:TextBox>
</div>
</div>
<div class="col-md-4">
<label>Issued Books</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox7" runat="server" placeholder="Pages" TextMode="Number" ReadOnly="True"></asp:TextBox>
</div>
</div>
</div>
<div class="row">
<div class="col-12">
<label>Book Description</label>
<div class="form-group">
<asp:TextBox CssClass="form-control" ID="TextBox6" runat="server" placeholder="Book Description" TextMode="MultiLine" Rows="2"></asp:TextBox>
</div>
</div>
</div>
<div class="row">
<div class="col-4">
<asp:Button ID="Button1" class="btn btn-lg btn-block btn-success" runat="server" Text="Add" OnClick="Button1_Click" />
</div>
<div class="col-4">
<asp:Button ID="Button3" class="btn btn-lg btn-block btn-warning" runat="server" Text="Update" OnClick="Button3_Click" />
</div>
<div class="col-4">
<asp:Button ID="Button2" class="btn btn-lg btn-block btn-danger" runat="server" Text="Delete" OnClick="Button2_Click" />
</div>
</div>
</div>
</div>
<a href="homepage.aspx"><< Back to Home</a><br>
<br>
</div>
<div class="col-md-7">
<div class="card">
<div class="card-body">
<div class="row">
<div class="col">
<center>
<h4>Book Inventory List</h4>
</center>
</div>
</div>
<div class="row">
<div class="col">
<hr>
</div>
</div>
<div class="row">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:elibraryDBConnectionString %>" SelectCommand="SELECT * FROM [book_master_tbl]"></asp:SqlDataSource>
<div class="col">
<asp:GridView class="table table-striped table-bordered" ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="book_id" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="book_id" HeaderText="ID" ReadOnly="True" SortExpression="book_id" >
<ControlStyle Font-Bold="True" />
<ItemStyle Font-Bold="True" />
</asp:BoundField>
<asp:TemplateField>
<ItemTemplate>
<div class="container-fluid">
<div class="row">
<div class="col-lg-10">
<div class="row">
<div class="col-12">
<asp:Label ID="Label1" runat="server" Text='<%# Eval("book_name") %>' Font-Bold="True" Font-Size="X-Large"></asp:Label>
</div>
</div>
<div class="row">
<div class="col-12">
<span>Author - </span>
<asp:Label ID="Label2" runat="server" Font-Bold="True" Text='<%# Eval("author_name") %>'></asp:Label>
| <span><span> </span>Genre - </span>
<asp:Label ID="Label3" runat="server" Font-Bold="True" Text='<%# Eval("genre") %>'></asp:Label>
|
<span>
Language -<span> </span>
<asp:Label ID="Label4" runat="server" Font-Bold="True" Text='<%# Eval("language") %>'></asp:Label>
</span>
</div>
</div>
<div class="row">
<div class="col-12">
Publisher -
<asp:Label ID="Label5" runat="server" Font-Bold="True" Text='<%# Eval("publisher_name") %>'></asp:Label>
| Publish Date -
<asp:Label ID="Label6" runat="server" Font-Bold="True" Text='<%# Eval("publish_date") %>'></asp:Label>
| Pages -
<asp:Label ID="Label7" runat="server" Font-Bold="True" Text='<%# Eval("no_of_pages") %>'></asp:Label>
| Edition -
<asp:Label ID="Label8" runat="server" Font-Bold="True" Text='<%# Eval("edition") %>'></asp:Label>
</div>
</div>
<div class="row">
<div class="col-12">
Cost -
<asp:Label ID="Label9" runat="server" Font-Bold="True" Text='<%# Eval("book_cost") %>'></asp:Label>
| Actual Stock -
<asp:Label ID="Label10" runat="server" Font-Bold="True" Text='<%# Eval("actual_stock") %>'></asp:Label>
| Available Stock -
<asp:Label ID="Label11" runat="server" Font-Bold="True" Text='<%# Eval("current_stock") %>'></asp:Label>
</div>
</div>
<div class="row">
<div class="col-12">
Description -
<asp:Label ID="Label12" runat="server" Font-Bold="True" Font-Italic="True" Font-Size="Smaller" Text='<%# Eval("book_description") %>'></asp:Label>
</div>
</div>
</div>
<div class="col-lg-2">
<asp:Image class="img-fluid" ID="Image1" runat="server" ImageUrl='<%# Eval("book_img_link") %>' />
</div>
</div>
</div>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</asp:Content>adminbookinventory.aspx.cs code –
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace WebApplication3
{
public partial class adminbookinventory : System.Web.UI.Page
{
string strcon = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
static string global_filepath;
static int global_actual_stock, global_current_stock, global_issued_books;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillAuthorPublisherValues();
}
GridView1.DataBind();
}
// go button click
protected void Button4_Click(object sender, EventArgs e)
{
getBookByID();
}
// update button click
protected void Button3_Click(object sender, EventArgs e)
{
updateBookByID();
}
// delete button click
protected void Button2_Click(object sender, EventArgs e)
{
deleteBookByID();
}
// add button click
protected void Button1_Click(object sender, EventArgs e)
{
if (checkIfBookExists())
{
Response.Write("<script>alert('Book Already Exists, try some other Book ID');</script>");
}
else
{
addNewBook();
}
}
// user defined functions
void deleteBookByID()
{
if (checkIfBookExists())
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("DELETE from book_master_tbl WHERE book_id='" + TextBox1.Text.Trim() + "'", con);
cmd.ExecuteNonQuery();
con.Close();
Response.Write("<script>alert('Book Deleted Successfully');</script>");
GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
}
else
{
Response.Write("<script>alert('Invalid Member ID');</script>");
}
}
void updateBookByID()
{
if (checkIfBookExists())
{
try
{
int actual_stock = Convert.ToInt32(TextBox4.Text.Trim());
int current_stock = Convert.ToInt32(TextBox5.Text.Trim());
if (global_actual_stock == actual_stock)
{
}
else
{
if (actual_stock < global_issued_books)
{
Response.Write("<script>alert('Actual Stock value cannot be less than the Issued books');</script>");
return;
}
else
{
current_stock = actual_stock - global_issued_books;
TextBox5.Text = "" + current_stock;
}
}
string genres = "";
foreach (int i in ListBox1.GetSelectedIndices())
{
genres = genres + ListBox1.Items[i] + ",";
}
genres = genres.Remove(genres.Length - 1);
string filepath = "~/book_inventory/books1";
string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
if (filename == "" || filename == null)
{
filepath = global_filepath;
}
else
{
FileUpload1.SaveAs(Server.MapPath("book_inventory/" + filename));
filepath = "~/book_inventory/" + filename;
}
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("UPDATE book_master_tbl set book_name=@book_name, genre=@genre, author_name=@author_name, publisher_name=@publisher_name, publish_date=@publish_date, language=@language, edition=@edition, book_cost=@book_cost, no_of_pages=@no_of_pages, book_description=@book_description, actual_stock=@actual_stock, current_stock=@current_stock, book_img_link=@book_img_link where book_id='" + TextBox1.Text.Trim() + "'", con);
cmd.Parameters.AddWithValue("@book_name", TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@genre", genres);
cmd.Parameters.AddWithValue("@author_name", DropDownList3.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publisher_name", DropDownList2.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publish_date", TextBox3.Text.Trim());
cmd.Parameters.AddWithValue("@language", DropDownList1.SelectedItem.Value);
cmd.Parameters.AddWithValue("@edition", TextBox9.Text.Trim());
cmd.Parameters.AddWithValue("@book_cost", TextBox10.Text.Trim());
cmd.Parameters.AddWithValue("@no_of_pages", TextBox11.Text.Trim());
cmd.Parameters.AddWithValue("@book_description", TextBox6.Text.Trim());
cmd.Parameters.AddWithValue("@actual_stock", actual_stock.ToString());
cmd.Parameters.AddWithValue("@current_stock", current_stock.ToString());
cmd.Parameters.AddWithValue("@book_img_link", filepath);
cmd.ExecuteNonQuery();
con.Close();
GridView1.DataBind();
Response.Write("<script>alert('Book Updated Successfully');</script>");
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
}
else
{
Response.Write("<script>alert('Invalid Book ID');</script>");
}
}
void getBookByID()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("SELECT * from book_master_tbl WHERE book_id='" + TextBox1.Text.Trim() + "';", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count >= 1)
{
TextBox2.Text = dt.Rows[0]["book_name"].ToString();
TextBox3.Text = dt.Rows[0]["publish_date"].ToString();
TextBox9.Text = dt.Rows[0]["edition"].ToString();
TextBox10.Text = dt.Rows[0]["book_cost"].ToString().Trim();
TextBox11.Text = dt.Rows[0]["no_of_pages"].ToString().Trim();
TextBox4.Text = dt.Rows[0]["actual_stock"].ToString().Trim();
TextBox5.Text = dt.Rows[0]["current_stock"].ToString().Trim();
TextBox6.Text = dt.Rows[0]["book_description"].ToString();
TextBox7.Text = "" + (Convert.ToInt32(dt.Rows[0]["actual_stock"].ToString()) - Convert.ToInt32(dt.Rows[0]["current_stock"].ToString()));
DropDownList1.SelectedValue = dt.Rows[0]["language"].ToString().Trim();
DropDownList2.SelectedValue = dt.Rows[0]["publisher_name"].ToString().Trim();
DropDownList3.SelectedValue = dt.Rows[0]["author_name"].ToString().Trim();
ListBox1.ClearSelection();
string[] genre = dt.Rows[0]["genre"].ToString().Trim().Split(',');
for (int i = 0; i < genre.Length; i++)
{
for (int j = 0; j < ListBox1.Items.Count; j++)
{
if (ListBox1.Items[j].ToString() == genre[i])
{
ListBox1.Items[j].Selected = true;
}
}
}
global_actual_stock = Convert.ToInt32(dt.Rows[0]["actual_stock"].ToString().Trim());
global_current_stock = Convert.ToInt32(dt.Rows[0]["current_stock"].ToString().Trim());
global_issued_books = global_actual_stock - global_current_stock;
global_filepath = dt.Rows[0]["book_img_link"].ToString();
}
else
{
Response.Write("<script>alert('Invalid Book ID');</script>");
}
}
catch (Exception ex)
{
}
}
void fillAuthorPublisherValues()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("SELECT author_name from author_master_tbl;", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
DropDownList3.DataSource = dt;
DropDownList3.DataValueField = "author_name";
DropDownList3.DataBind();
cmd = new SqlCommand("SELECT publisher_name from publisher_master_table;", con);
da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
DropDownList2.DataSource = dt;
DropDownList2.DataValueField = "publisher_name";
DropDownList2.DataBind();
}
catch (Exception ex)
{
}
}
bool checkIfBookExists()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("SELECT * from book_master_tbl where book_id='" + TextBox1.Text.Trim() + "' OR book_name='" + TextBox2.Text.Trim() + "';", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count >= 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
return false;
}
}
void addNewBook()
{
try
{
string genres = "";
foreach (int i in ListBox1.GetSelectedIndices())
{
genres = genres + ListBox1.Items[i] + ",";
}
// genres = Adventure,Self Help,
genres = genres.Remove(genres.Length - 1);
string filepath = "~/book_inventory/books1.png";
string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(Server.MapPath("book_inventory/" + filename));
filepath = "~/book_inventory/" + filename;
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("INSERT INTO book_master_tbl(book_id,book_name,genre,author_name,publisher_name,publish_date,language,edition,book_cost,no_of_pages,book_description,actual_stock,current_stock,book_img_link) values(@book_id,@book_name,@genre,@author_name,@publisher_name,@publish_date,@language,@edition,@book_cost,@no_of_pages,@book_description,@actual_stock,@current_stock,@book_img_link)", con);
cmd.Parameters.AddWithValue("@book_id", TextBox1.Text.Trim());
cmd.Parameters.AddWithValue("@book_name", TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@genre", genres);
cmd.Parameters.AddWithValue("@author_name", DropDownList3.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publisher_name", DropDownList2.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publish_date", TextBox3.Text.Trim());
cmd.Parameters.AddWithValue("@language", DropDownList1.SelectedItem.Value);
cmd.Parameters.AddWithValue("@edition", TextBox9.Text.Trim());
cmd.Parameters.AddWithValue("@book_cost", TextBox10.Text.Trim());
cmd.Parameters.AddWithValue("@no_of_pages", TextBox11.Text.Trim());
cmd.Parameters.AddWithValue("@book_description", TextBox6.Text.Trim());
cmd.Parameters.AddWithValue("@actual_stock", TextBox4.Text.Trim());
cmd.Parameters.AddWithValue("@current_stock", TextBox4.Text.Trim());
cmd.Parameters.AddWithValue("@book_img_link", filepath);
cmd.ExecuteNonQuery();
con.Close();
Response.Write("<script>alert('Book added successfully.');</script>");
GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
}
}
}