In this article we will cover CRUD Operation (Create, Update, Delete, Display) on single page in Asp.Net 4.0 Web Forms Application using C# and SQL. Live Example
First Step is creating a database in SQL server with any name. Download text file from the link given below and run all scripts mentioned inside the file.
Download Sql ScriptInfo: Sql Script file has create table script and stored procedure for crud operation
Events.aspx
Create a Web Form named "Events.aspx"
Add below mentioned html code which consist Add event section. This section has Server side controls, including file upload and dependent dropdown
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:UpdateProgress ID="up1" AssociatedUpdatePanelID="UpdatePanel1" runat="server" DisplayAfter="10"> <ProgressTemplate> <div class="loading" style="display: block !important"> <div class="spinner-border" role="status"> <span class="sr-only">Loading...</span> </div> </div> </ProgressTemplate> </asp:UpdateProgress> <div class="form-row"> <div class="control-group col-md-6"> <div class="form-group controls"> <label>Event Name <span class="red">*</span></label> <asp:TextBox ID="txtName" runat="server" CssClass="form-control" /> <asp:RequiredFieldValidator ID="reqName" runat="server" ControlToValidate="txtName" ErrorMessage="Required." CssClass="red" SetFocusOnError="true"></asp:RequiredFieldValidator> </div> </div> <div class="control-group col-md-6"> <div class="form-group controls"> <label>Event City <span class="red">*</span></label> <asp:DropDownList ID="ddlCity" runat="server" CssClass="form-control" OnSelectedIndexChanged="ddlCity_SelectedIndexChanged" AutoPostBack="true" /> <asp:RequiredFieldValidator ID="reqCity" runat="server" ControlToValidate="ddlCity" ErrorMessage="Required." CssClass="red" SetFocusOnError="true"></asp:RequiredFieldValidator> </div> </div> <div class="control-group col-md-6"> <div class="form-group controls"> <label>Event Location <span class="red">*</span></label> <asp:DropDownList ID="ddlArea" runat="server" CssClass="form-control" /> <asp:RequiredFieldValidator ID="reqArea" runat="server" ControlToValidate="ddlArea" ErrorMessage="Required." CssClass="red" SetFocusOnError="true"></asp:RequiredFieldValidator> </div> </div> <div class="control-group col-md-6"> <div class="form-group controls"> <label>Event Image <span class="red">*</span></label> <asp:Label ID="lblBannerImage" runat="server" Visible="false"></asp:Label> <asp:Image ID="imgBannerImage" runat="server" Width="50" Height="50" Visible="false" /> <asp:FileUpload ID="fileBannerImage" runat="server" CssClass="form-control" /> <div style="font-size: 14px; color: green;">Allowed file types (jpeg,png,jpg) & size less than 1MB </div> <asp:RequiredFieldValidator ID="reqfileBannerImage" runat="server" ControlToValidate="fileBannerImage" ErrorMessage="Required." CssClass="red" SetFocusOnError="true"></asp:RequiredFieldValidator> </div> </div> <div class="control-group col-md-12"> <div class="form-group controls"> <label>Event Description <span class="red">*</span></label> <asp:TextBox ID="txtDescription" runat="server" CssClass="form-control" TextMode="MultiLine" Height="100" /> <asp:RequiredFieldValidator ID="reqDescription" runat="server" ControlToValidate="txtDescription" ErrorMessage="Required." CssClass="red" SetFocusOnError="true"></asp:RequiredFieldValidator> </div> </div> <div class="control-group col-md-12"> <div class="form-group controls"> <div class="form-group col-md-12" align="right" id="div_add" runat="server"> <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" CssClass="btn btn-primary" /> </div> <div class="form-group col-md-12" align="right" id="div_edit" runat="server" visible="false"> <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="Update" CssClass="btn btn-primary" CausesValidation="true" /> <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="Cancel" CssClass="btn btn-primary" CausesValidation="false" /> </div> </div> </div> </div> </ContentTemplate> <Triggers> <asp:PostBackTrigger ControlID="btnAdd" /> <asp:PostBackTrigger ControlID="btnUpdate" /> </Triggers> </asp:UpdatePanel>
Add below mentioned html code which Lists all the events. This section has Server side Gridview Control to display data
<asp:UpdatePanel ID="UpdatePanel2" runat="server"> <ContentTemplate> <asp:UpdateProgress ID="UpdateProgress2" AssociatedUpdatePanelID="UpdatePanel2" runat="server" DisplayAfter="10"> <ProgressTemplate> <div class="loading" style="display: block !important"> <div class="spinner-border" role="status"> <span class="sr-only">Loading...</span> </div> </div> </ProgressTemplate> </asp:UpdateProgress> <div class="form-row"> <div class="control-group col-md-12"> <h3>Event List</h3> <asp:GridView ID="GridView1" runat="server" Width="100%" AutoGenerateColumns="false" EmptyDataText="No Records Found" OnRowDataBound="GridView1_RowDataBound"> <Columns> <asp:BoundField DataField="Name" HeaderText="Name" /> <asp:BoundField DataField="Description" HeaderText="Description" /> <asp:BoundField DataField="CityName" HeaderText="City" /> <asp:BoundField DataField="AreaName" HeaderText="Area" /> <asp:TemplateField HeaderText="Event Image"> <ItemTemplate> <img src="/images/events/<%#Eval("EventImage") %>" style="width: 50px; height: 50px;"> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Action"> <ItemTemplate> <asp:HiddenField ID="hdnID" runat="server"></asp:HiddenField> <a href="/events.aspx?id=<%# Eval("ID") %>">Edit</a><span style="padding: 0 10px;">|</span><asp:LinkButton ID="btnDelete" OnClientClick="return confirm('Do you want to delete data?')" runat="server" OnClick="btnDelete_Click" Text="Delete" CausesValidation="false" /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </div> </ContentTemplate> </asp:UpdatePanel>
<style> .red { color: red; } </style>
Events.aspx.cs
Add below mentioned code to .cs file
using System; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Web.UI; using System.Web; using System.IO; namespace HpBlogs { public partial class Events : System.Web.UI.Page { private const string constr = "Password=dbpassword;Persist Security Info=True;User ID=dbuserid;Initial Catalog=databasename;Data Source=datasource"; protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { BindGrid(); BindCity(); ddlArea.Items.Insert(0, new ListItem("--Select Location--", "")); if (!string.IsNullOrEmpty(Request.QueryString["id"])) { int id = Convert.ToInt32(Request.QueryString["id"]); EditData(id); } } } private void BindGrid() { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("EVENT_CRUD", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@TYPE", 0); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } } } private void BindCity() { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("select * from city", con)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@TYPE", 0); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); ddlCity.DataSource = dt; ddlCity.DataTextField = "Name"; ddlCity.DataValueField = "ID"; ddlCity.DataBind(); } } } } ddlCity.Items.Insert(0, new ListItem("--Select City--", "")); } private void BindArea(string city) { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("select * from area where city=" + ddlCity.SelectedValue + "", con)) { cmd.CommandType = CommandType.Text; using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); ddlArea.DataSource = dt; ddlArea.DataTextField = "Name"; ddlArea.DataValueField = "ID"; ddlArea.DataBind(); } } } } ddlArea.Items.Insert(0, new ListItem("--Select Location--", "")); } private void EditData(int id) { using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("EVENT_CRUD", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", id); cmd.Parameters.AddWithValue("@TYPE", 0); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); txtName.Text = HttpUtility.HtmlDecode(dt.Rows[0]["Name"].ToString()); txtDescription.Text = HttpUtility.HtmlDecode(dt.Rows[0]["Description"].ToString()); ddlCity.SelectedValue = dt.Rows[0]["CityID"].ToString(); BindArea(ddlCity.SelectedValue); ddlArea.SelectedValue = dt.Rows[0]["Area"].ToString(); lblBannerImage.Text = dt.Rows[0]["EventImage"].ToString(); imgBannerImage.ImageUrl = "/images/Events/" + dt.Rows[0]["EventImage"].ToString(); imgBannerImage.Visible = true; reqfileBannerImage.Enabled = false; div_add.Visible = false; div_edit.Visible = true; } } } } } protected void Insert(object sender, EventArgs e) { if (Page.IsValid) { string fileuploadsize = fileBannerImage.PostedFile.ContentLength.ToString(); bool validFile = IsValidFile(fileBannerImage.PostedFile.FileName); bool validSize = IsValidSize(double.Parse(fileuploadsize)); if (validFile && validSize) { string name = HttpUtility.HtmlEncode(txtName.Text); string description = HttpUtility.HtmlEncode(txtDescription.Text); string ImgName = DateTime.Now.Ticks + "_" + Path.GetFileName(fileBannerImage.PostedFile.FileName).Replace(" ", ""); using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("EVENT_CRUD", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@TYPE", 1); cmd.Parameters.AddWithValue("@Name", name); cmd.Parameters.AddWithValue("@Description", description); cmd.Parameters.AddWithValue("@EventImage", ImgName); cmd.Parameters.AddWithValue("@Area", ddlArea.SelectedValue); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); fileBannerImage.PostedFile.SaveAs(Server.MapPath("~/images/Events/") + ImgName); } } BindGrid(); Clear(); ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Record Added sucessfully');", true); } else { ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Invalid File Type or Size ');", true); } } } protected void Update(object sender, EventArgs e) { if (Page.IsValid) { bool validFile = true; bool validSize = true; if (fileBannerImage.HasFile) { string fileuploadsize = fileBannerImage.PostedFile.ContentLength.ToString(); validFile = IsValidFile(fileBannerImage.PostedFile.FileName); validSize = IsValidSize(double.Parse(fileuploadsize)); } if (validFile && validSize) { int Id = Convert.ToInt32(Request.QueryString["id"]); string name = HttpUtility.HtmlEncode(txtName.Text); string description = HttpUtility.HtmlEncode(txtDescription.Text); string img = lblBannerImage.Text; if (fileBannerImage.HasFile) img = DateTime.Now.Ticks + "_" + Path.GetFileName(fileBannerImage.PostedFile.FileName).Replace(" ", ""); using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("EVENT_CRUD", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@TYPE", 2); cmd.Parameters.AddWithValue("@ID", Id); cmd.Parameters.AddWithValue("@Name", name); cmd.Parameters.AddWithValue("@Description", description); cmd.Parameters.AddWithValue("@EventImage", img); cmd.Parameters.AddWithValue("@Area", ddlArea.SelectedValue); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); if (fileBannerImage.HasFile) { fileBannerImage.PostedFile.SaveAs(Server.MapPath("~/images/Events/") + img); File.Delete(Server.MapPath("~/images/Events/" + lblBannerImage.Text)); } } } ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Record Updated sucessfully');window.location ='/events.aspx';", true); } else { ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Invalid File Type or Size ');", true); } } } protected void Cancel(object sender, EventArgs e) { Response.Redirect("~/events.aspx"); } protected void btnDelete_Click(object sender, EventArgs e) { GridViewRow item = (sender as LinkButton).NamingContainer as GridViewRow; string datetime = DateTime.Now.ToString(); int Id = Convert.ToInt32((item.FindControl("hdnID") as HiddenField).Value); using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("EVENT_CRUD", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", Id); cmd.Parameters.AddWithValue("@TYPE", 3); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } BindGrid(); ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Record Deleted sucessfully');", true); } protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e) { BindArea(ddlCity.SelectedValue); } private void Clear() { txtName.Text = ""; txtDescription.Text = ""; ddlCity.SelectedValue = ""; ddlArea.SelectedValue = ""; } protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { DataRowView drv = e.Row.DataItem as DataRowView; (e.Row.FindControl("hdnID") as HiddenField).Value = drv.Row["ID"].ToString(); } } public bool IsValidFile(string Filename) { bool RetValue = false; string[] inValidFileTypes = { "jpeg", "png", "jpg" }; string ext = System.IO.Path.GetExtension(Filename).ToLower(); if (((ext.Length) - 1) > 15) return false; if (ext.Equals("")) return false; for (int i = 0; i < inValidFileTypes.Length; i++) { if (ext.Equals("." + inValidFileTypes[i]) || ext.Equals("")) { RetValue = true; break; } else { RetValue = false; } } return RetValue; } public bool IsValidSize(Double currentfilesize) { int chksize = 1; Boolean isvalid = false; currentfilesize = (((currentfilesize) / 1024f) / 1024f); if (chksize >= currentfilesize) { isvalid = true; } return isvalid; } } }
Run the project. Output page would look like: Live Example
Post Comments(0)