In this Tutorial i am going to explain how to insert, update and delete record from database with advanced searching facility in Asp.Net with C#. While you working on any project you need to deal with database and do some operation like insert new record into database and display it into tabular format, update inserted record as well as delete records from database with appropriate confirmation message like "Are you sure want to Delete this Record ? ", and also search record from the record list and display that record.
In this tutorial we will create one web application for library where user/admin can insert book with appropriate Book Name, Book Author and Book Price and store into database. And user/admin can also do Update record details and also Delete particular record of book with confirmation message from the database. In this application we will also implement advance searching facility where user/admin can Search Books by BookID, Search Books by Book Name, Search Books by Book Author or also Search Books by Book Price.
You May Like to Read :Download Multiple Files as Zip File From Gridview Based on CheckBox Selection or How to Create Wifi Hotspot in Vb.Net or How to Update DataTable using Linq in Asp.Net C#
So, Let's Create One Web Page for Demonstration Purpose.
First You need to create Database and also create one table with name book.
Default.aspx:
You can Design Your Web page as per your requirements.
Default.aspx.cs:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Net; using System.Net.Mail; using System.Data; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { SqlConnection cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Nikunj\Desktop\DEMO\App_Data\Database2.mdf;Integrated Security=True"); protected void Page_Load(object sender, EventArgs e) { try { name.Focus(); if (!IsPostBack) { Filgrid(); } } catch { } } void Filgrid() { try { SqlCommand cmd1 = new SqlCommand(); cmd1.CommandText = "select * from book"; cmd1.Connection = cn; SqlDataAdapter da1 = new SqlDataAdapter(cmd1); DataSet ds = new DataSet(); da1.Fill(ds); gd1.DataSource = ds; gd1.DataBind(); } catch { } } void erased() { try { name.Text = ""; author.Text = ""; price.Text = ""; id.Value = ""; insert.Visible = true; update.Visible = false; } catch { throw; } } protected void insert_Click(object sender, EventArgs e) { try { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "INSERT INTO book(book_name,book_author, book_price)VALUES(@book_name,@book_author,@150)"; cmd.Parameters.AddWithValue("@book_name", name.Text); cmd.Parameters.AddWithValue("@book_author", author.Text); cmd.Parameters.AddWithValue("@150", price.Text); cmd.Connection = cn; cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); Filgrid(); erased(); message.Text = "Record Saved Successfully..."; } catch { } finally { if (cn.State == ConnectionState.Open) cn.Close(); } } protected void erase_Click(object sender, EventArgs e) { try { erased(); } catch { } } protected void update_Click(object sender, EventArgs e) { try { SqlCommand cmd1 = new SqlCommand(); cmd1.CommandText = "UPDATE book SET book_name = @book_name, book_author = @book_author, book_price = @book_price where id = @test1"; cmd1.Parameters.AddWithValue("@test1", test1.Text); cmd1.Parameters.AddWithValue("@book_name", name.Text); cmd1.Parameters.AddWithValue("@book_author", author.Text); cmd1.Parameters.AddWithValue("@book_price", price.Text); cmd1.Connection = cn; cn.Open(); cmd1.ExecuteNonQuery(); cn.Close(); Filgrid(); erased(); message.Text = "Update Successfully..."; } catch { } finally { if (cn.State == ConnectionState.Open) { cn.Close(); } } } protected void btndelete_Click(object sender, EventArgs e) { Button btn = sender as Button; GridViewRow gdr = btn.NamingContainer as GridViewRow; id.Value = (gdr.FindControl("lblbookid") as Label).Text; test1.Text = (gdr.FindControl("lblbookid") as Label).Text; SqlCommand cmd1 = new SqlCommand(); cmd1.Connection = cn; cn.Open(); cmd1.CommandText = "Delete from book where id = @test1"; cmd1.Parameters.AddWithValue("@test1", test1.Text); cmd1.ExecuteNonQuery(); cn.Close(); message.Text = "Record Delete Successfully"; Response.Redirect(Request.RawUrl); gd1.DeleteRow(1); } protected void clearall(object sender, EventArgs e) { gd1.DataSource = null; gd1.DataBind(); } protected void btn_search_Click(object sender, EventArgs e) { cn.Open(); SqlCommand cmd1 = new SqlCommand("select * from book where id LIKE '%' + @id + '%' or book_name LIKE '%' + @book_name + '%' or book_author LIKE '%'+ @book_author + '%' or book_price LIKE '%'+@book_price+'%'"); cmd1.Parameters.AddWithValue("@id", search.Text); cmd1.Parameters.AddWithValue("@book_name", search.Text); cmd1.Parameters.AddWithValue("@book_author", search.Text); cmd1.Parameters.AddWithValue("@book_price", search.Text); cmd1.Connection = cn; DataTable dt1 = new DataTable(); using (SqlDataAdapter sd1 = new SqlDataAdapter(cmd1)) { sd1.Fill(dt1); gd1.DataSource = dt1; gd1.DataBind(); } } protected void btnedit_Click(object sender, EventArgs e) { Button btn = sender as Button; GridViewRow gdr = btn.NamingContainer as GridViewRow; id.Value = (gdr.FindControl("lblbookid") as Label).Text; test1.Text = (gdr.FindControl("lblbookid") as Label).Text; name.Text = (gdr.FindControl("Label1") as Label).Text; author.Text = (gdr.FindControl("Label2") as Label).Text; price.Text = (gdr.FindControl("Label3") as Label).Text; insert.Visible = false; update.Visible = true; } }
Demo:
Download Sample Code:
I am a passionate programmer and a Software Engineer who love developing applications in Microsoft Technologies, blogging and other related technologies like Asp.Net, VB.Net, SEO, Sql Server,My SQL,Asp.Net-MVC, LINQ, C#.Net, Stored Procedures, Ajax, JavaScript, JQuery, xml etc.
If you have any kind of question about any post, Feel free to ask.You can simply drop a comment below post. Your feedback and suggestions will be highly appreciated. ConversionConversion EmoticonEmoticon