Introduction: In this article i am going to explain with example How to bind, save, edit, update, cancel and delete records/items/data from DataList in asp.net with both the C# and VB.Net language and using stored procedures and Sql server as a back end database.
 |
| Click on the image to enlarge |
Description: Basically you will learn the following through this article.
- How to perform the operations like Bind, save, edit, update, cancel and delete data from DataList data control using stored procedures and Sql server as back end database
- How to upload image file through file upload control and show book image in DataList control.
Implementation: Let's create an asp.net application to see the DataList's operations in action.
- First of all create a database in Sql server e.g. "Book_DB" and create a table with the columns and data type as shown below and name it "Book_Tb".
Column Name
|
Data Type
|
Book_ID
|
Int(Primary Key. So set Is identity=true)
|
Title
|
varchar(100)
|
Author
|
varchar(100)
|
Publisher
|
varchar(100)
|
Price
|
Int
|
ImagePath
|
varchar(500)
|
Now we will create stored procedures to bind , save, update and delete the book records from the Sql server database table.
- Create a stored procedure to save the Book records.
CREATE PROCEDURE SaveBookRecords_Sp
(
@Title varchar(100),
@Author varchar(100),
@Publisher varchar(100),
@Price int,
@ImagePath varchar(500)
)
AS
BEGIN
insert into tbBook (Title,Author,Publisher,Price,ImagePath)
values (@Title,@Author,@Publisher,@Price,@ImagePath)
END
- Create a stored procedure to fetch the Book records and bind in DataList
CREATE PROCEDURE BindBookRecords_Sp
AS
BEGIN
select * from tbBook
END
- Create a stored procedure to update the book records.
CREATE PROCEDURE UpdateBookRecords_Sp
(
@Book_ID int,
@Title varchar(100),
@Author varchar(100),
@Publisher varchar(100),
@Price int
)
AS
BEGIN
update tbBook setTitle=@Title,Author=@Author,Publisher=@Publisher,Price=@Price
where Book_ID=@Book_ID
END
- Create a stored procedure to delete the book records.
CREATE PROCEDURE DeleteBookRecords_Sp
(
@Book_ID int
)
AS
BEGIN
delete from tbBook where Book_Id=@Book_ID
END
- Now in the web.config file create the connection string to connect the asp.net application with the Sql server database.
<connectionStrings>
<add name="conStr" connectionString="Data Source=Lalit;Initial Catalog=Book_DB;Integrated Security=True"/>
</connectionStrings>
Note: Replace the Data Source and Initial catalog (i.e. Database name) as per your application.
- Now in the <Body> tag of the design page e.g. default.aspx, design the page as shown below:
Source Code:
<fieldset style="width:550px">
<legend>Bind,Save,Edit,Update,Delete in DataList</legend>
<table style="width:100%">
<tr><td width="25%">Title</td><td>
<asp:TextBox ID="txtTitle" runat="server"></asp:TextBox></td></tr>
<tr><td>Author</td><td>
<asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox></td></tr>
<tr><td>Publisher</td><td>
<asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td></tr>
<tr><td>Price</td><td>
<asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td></tr>
<tr><td>Upload Image</td><td>
<asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>
<tr><td></td>
<td> <asp:Button ID="btnSubmit" runat="server" Text="Save"
onclick="btnSubmit_Click" /></td></tr>
</table>
<asp:DataList ID="dtlBooks" runat="server" RepeatColumns="2"
oncancelcommand="dtlBooks_CancelCommand"oneditcommand="dtlBooks_EditCommand"
onupdatecommand="dtlBooks_UpdateCommand" ondeletecommand="dtlBooks_DeleteCommand" DataKeyField="Book_ID"
BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4" GridLines="Both">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<ItemStyle BackColor="White" ForeColor="#003399" />
<ItemTemplate>
<table>
<tr>
<td><img src='<%#Eval("ImagePath") %>' width="90px" height="90px" alt="Book Image" /></td><td><b>Title : </b><%#Eval("Title") %><br />
<b>Author : </b><%#Eval("Author") %><br />
<b>Publisher : </b><%#Eval("Publisher") %><br />
<b>Price : </b><%#Eval("Price") %><br /></td>
</tr>
</table>
<asp:LinkButton ID="lnkEdit" runat="server" Text="Edit" CommandName="Edit"></asp:LinkButton>
<asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" CommandName="Delete"OnClientClick="return confirm('Are you sure you want to delete selected records')"></asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<b>Title : </b><asp:TextBox id="txtEditTitle" runat="server" Text='<%#Eval("Title") %>'></asp:TextBox><br />
<b>Author : </b><asp:TextBox id="txtEditAuthor" runat="server"Text='<%#Eval("Author") %>'></asp:TextBox><br />
<b>Publisher : </b><asp:TextBox id="txtEditPublisher" runat="server"Text='<%#Eval("Publisher") %>'></asp:TextBox><br />
<b>Price : </b><asp:TextBox id="txtEditPrice" runat="server" Text='<%#Eval("Price") %>'></asp:TextBox><br />
<asp:LinkButton ID="lnkUpdate" runat="server" Text="Update"CommandName="Update"></asp:LinkButton>
<asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel"CommandName="Cancel"></asp:LinkButton>
</EditItemTemplate>
<SelectedItemStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
</asp:DataList>
</fieldset>
Note: Create a folder in the root directory of the project and name it " BookImages" to store and fetch the book images
C#.NET Code to Bind,save,edit,update,delete records from DataList:
- In the code behind file (.aspx.cs) write the code as:
First include the following required namespaces and write the code:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindDataList();
}
}
protected void BindDataList()
{
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
adp = new SqlDataAdapter("BindBookRecords_Sp", con);
adp.SelectCommand.CommandType = CommandType.StoredProcedure;
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
dtlBooks.DataSource = dt;
dtlBooks.DataBind();
}
else
{
dtlBooks.DataSource = dt;
dtlBooks.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
dt.Clear();
dt.Dispose();
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string filePath = string.Empty;
SqlCommand cmd = new SqlCommand();
string ImgPath=string.Empty;
string DbImgPath=string.Empty;
try
{
cmd = new SqlCommand("SaveBookRecords_Sp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Title", txtTitle.Text.Trim());
cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim());
cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim());
cmd.Parameters.AddWithValue("@Price", Convert.ToInt32(txtPrice.Text));
if (FileUpload1.HasFile)
{
ImgPath = (Server.MapPath("~/BookImages/") + Guid.NewGuid() + FileUpload1.FileName);
FileUpload1.SaveAs(ImgPath);
DbImgPath = ImgPath.Substring(ImgPath.LastIndexOf("\\"));
DbImgPath = DbImgPath.Insert(0, "BookImages");
cmd.Parameters.AddWithValue("@ImagePath", DbImgPath);
}
else
{
cmd.Parameters.AddWithValue("@ImagePath", string.Empty);
}
con.Open();
cmd.ExecuteNonQuery();
BindDataList();
ClearControls();
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Book record has been saved successfully');", true);
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
cmd.Dispose();
}
}
protected void dtlBooks_CancelCommand(object source, DataListCommandEventArgs e)
{
dtlBooks.EditItemIndex = -1;
BindDataList();
}
protected void dtlBooks_EditCommand(object source, DataListCommandEventArgs e)
{
dtlBooks.EditItemIndex = e.Item.ItemIndex;
BindDataList();
}
protected void dtlBooks_UpdateCommand(object source, DataListCommandEventArgs e)
{
Int32 bookId,prc;
string tit = string.Empty;
string aut = string.Empty;
string pub = string.Empty;
SqlCommand cmd = new SqlCommand();
try
{
tit = ((TextBox)(e.Item.FindControl("txtEditTitle"))).Text;
aut = ((TextBox)(e.Item.FindControl("txtEditAuthor"))).Text;
pub = ((TextBox)(e.Item.FindControl("txtEditPublisher"))).Text;
prc = Convert.ToInt32(((TextBox)(e.Item.FindControl("txtEditPrice"))).Text);
bookId = Convert.ToInt32(dtlBooks.DataKeys[e.Item.ItemIndex]);
con.Open();
cmd = new SqlCommand("UpdateBookRecords_Sp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Book_ID", bookId);
cmd.Parameters.AddWithValue("@Title", tit);
cmd.Parameters.AddWithValue("@Author", aut);
cmd.Parameters.AddWithValue("@Publisher", pub);
cmd.Parameters.AddWithValue("@Price", prc);
cmd.ExecuteNonQuery();
dtlBooks.EditItemIndex = -1;
BindDataList();
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Book record has been updated successfully');", true);
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
cmd.Dispose();
con.Close();
tit=string.Empty;
aut=string.Empty;
pub = string.Empty;
}
}
protected void dtlBooks_DeleteCommand(object source, DataListCommandEventArgs e)
{
Int32 bookId;
SqlCommand cmd = new SqlCommand();
try
{
bookId = Convert.ToInt32(dtlBooks.DataKeys[e.Item.ItemIndex]);
cmd = new SqlCommand("DeleteBookRecords_Sp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Book_ID", bookId);
con.Open();
cmd.ExecuteNonQuery();
BindDataList();
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Book record has been deleted successfully');", true);
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
cmd.Dispose();
}
}
private void ClearControls()
{
txtTitle.Text = string.Empty;
txtAuthor.Text = string.Empty;
txtPublisher.Text = string.Empty;
txtPrice.Text = string.Empty;
txtTitle.Focus();
}
No comments:
Post a Comment