How to Bind,Save and perform run time calculations in asp.net Repeater control

Introduction: In this article I am going to explain with example How to Bind, Insert and perform run time operations to calculate discount and total amount in Repeater data control in asp.net using both C# and VB.net languages.

Click on the image to enlarge

Description: In previous articles i explained How to bind, edit, update and delete data in Repeater and Custom paging in Repeater control and Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and How to Bind,save,edit,update,delete records from DataList and Bind,Save,Edit,Update,Cancel,Delete and paging example in DetailsView and Retain password value in the asp.net TextBox after postback event and Bind,upload,download,delete image files from the GridView and jQuery to validate file extension and upload image file. 

In this article I have demonstrated the way to perform run time calculations. Suppose there is a fixed book price and 10% discount on the book price, then we can calculate the discounted price on run time and display in the repeater directly without storing the discounted price in the database.

Implementation: Let's create a sample asp.net website to see the repeater operation in action.

• First of all create a database in Sql server with the name e.g. "dbBooks" and in this database create a table with the columns and the data type as shown below.

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 create the stored procedure in Sql server to insert the Book details in the data base

CREATE PROCEDURE InsertBookDetails_Sp

                @Title                  VARCHAR(100),

                @Author                             VARCHAR(100),

                @Publisher  VARCHAR(100),

                @Price                 INT,

                @ImagePath     VARCHAR(500)

AS

BEGIN 

                SET NOCOUNT ON;

                INSERT INTO TbBook (Title,Author,Publisher,Price,ImagePath)

                VALUES (@Title,@Author,@Publisher,@Price,@ImagePath) 

END

• Also create the stored procedure in Sql server to bind the Book details in Repeater control.

CREATE PROCEDURE BindBookDetails_Sp      

AS

BEGIN 

                SET NOCOUNT ON;

                SELECT * FROM tbBook

END

• Now in the web.config file create the connection string under the <configuration> tag to connect the Sql server database with the asp.net as:

<connectionStrings>

                                <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=dbBooks;Integrated Security=True;"/>   

  </connectionStrings>

Note: Replace the Data Source and the Initial Catalog as per your application.

Source Code:

• In the design page (.aspx) create and design the form as:

<div>

    <fieldset style="width:400px;">

    <legend>Example to Bind, Insert and calculations in Repeater</legend>   

    <table>

     <tr>

 <td>Book Title: </td>

<td><asp:TextBox ID="txtBookName" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>Author Name: </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 Book Picture: </td>

<td>

    <asp:FileUpload ID="FileUpload1" runat="server" /></td>

</tr>

<tr>

<td></td>

<td><asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /></td>

</tr>

    </table>

        <asp:Repeater ID="repBookDetails" runat="server">

        <HeaderTemplate>

        <table border="1">

        <tr style="background-color:Orange; color:#444;" align="center">

        <th>Image</th> 

        <th>Title</th>

        <th>Author</th>

        <th>Publisher</th>

        <th>Price</th>

        <th>Discount</th>

        <th>Amount</th>

        </tr>

        </HeaderTemplate>

        <ItemTemplate>

        <tr style="background-color:white;" align="center">

        <td><img src='<%#Eval("ImagePath") %>' height="70px" width="70px" alt="Book Image" /></td>

        <td><%#Eval("Title") %></td>

        <td><%#Eval("Author") %></td>

        <td><%#Eval("Publisher") %></td>

        <td><%#Eval("Price") %></td>

        <td><%#CalcDiscount(Convert.ToInt32(Eval("Price"))) %></td>

        <td><%#CalcAmount(Convert.ToInt32(Eval("Price")))%></td>      

        </tr>

        </ItemTemplate>

        <AlternatingItemTemplate>

        <tr style="background-color:Menu;" align="center">

         <td><img src='<%#Eval("ImagePath") %>' height="70px" width="70px" alt="Book Image" /></td>

        <td><%#Eval("Title") %></td>

        <td><%#Eval("Author") %></td>

        <td><%#Eval("Publisher") %></td>

        <td><%#Eval("Price") %></td>

        <td><%#CalcDiscount(Convert.ToInt32(Eval("Price")))%></td>

        <td><%#CalcAmount(Convert.ToInt32(Eval("Price"))) %></td>      

        </tr>

        </AlternatingItemTemplate>

        <FooterTemplate>

        </table>

        </FooterTemplate>

        </asp:Repeater>   

        </fieldset>

    </div>

Note: Create a folder in the root directory of your website and name it "BookImages". This folder will contain all the uploaded book images.

C#.Net Code to bind, save and perform calculations in repeater

• In the code behind file (.aspx.cs) write the code as:

First of all include the following required namespaces and write the code as:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            BindRepeater();

        }

    }

    protected void btnSubmit_Click(object sender, EventArgs e)

    {

        string ImgPath = string.Empty;

        string DbImgPath = string.Empty;

        SqlCommand cmd = new SqlCommand();

        try

        {

            cmd = new SqlCommand("InsertBookDetails_Sp", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@Title", txtBookName.Text.Trim());

            cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim());

            cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim());

            cmd.Parameters.AddWithValue("@Price", txtPrice.Text.Trim());  

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

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            Clear_Controls();

            BindRepeater();

        }

        catch (Exception ex)

        {

            //Response.Write("Oops!! Error occured:" + ex.Message.ToString());

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);                  

        }

        finally

        {

            cmd.Dispose();

            con.Close();

            ImgPath = string.Empty;

            DbImgPath = string.Empty;

        }

    }

   

    private void BindRepeater()

    {

        DataTable dt = new DataTable();

        SqlDataAdapter adp = new SqlDataAdapter();

        try

        {

            adp = new SqlDataAdapter("BindBookDetails_Sp", con);

            adp.SelectCommand.CommandType = CommandType.StoredProcedure;

            adp.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                repBookDetails.DataSource = dt;

                repBookDetails.DataBind();

            }

            else

            {

                repBookDetails.DataSource=null;

                repBookDetails.DataBind();

            }

        }

        catch (Exception ex)

        {

            //Response.Write("Oops!! Error occured:" + ex.Message.ToString());

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);                  

        }

        finally

        {

            con.Close();

            dt.Clear();

            dt.Dispose();

            adp.Dispose();

        }

    }

    protected  Int32 CalcDiscount(Int32 prc)

    {

        return prc * 10 / 100;

    }

    protected Int32 CalcAmount(Int32 prc)

    {

        return prc - CalcDiscount(prc);

    }

  

    private void Clear_Controls()

    {

        txtBookName.Text = string.Empty;

        txtAuthor.Text = string.Empty;

        txtPublisher.Text = string.Empty;

        txtPrice.Text = string.Empty;

        txtBookName.Focus();

    }

VB.Net Code to bind, save and perform calculations in repeater

• Design the page as described above in the C#.Net section but replace the line <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /> with the line <asp:Button ID="btnSubmit" runat="server" Text="Submit" />

• Then In the code behind file (.aspx.cs) write the code as:

First of all import the following required namespaces and write the code as:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            BindRepeater()

        End If

    End Sub

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim ImgPath As String = String.Empty

        Dim DbImgPath As String = String.Empty

        Dim cmd As New SqlCommand()

        Try

            cmd = New SqlCommand("InsertBookDetails_Sp", con)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@Title", txtBookName.Text.Trim())

            cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim())

            cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim())

            cmd.Parameters.AddWithValue("@Price", txtPrice.Text.Trim())

            ImgPath = (Server.MapPath("~/BookImages/") + Convert.ToString(Guid.NewGuid()) + FileUpload1.FileName)

            FileUpload1.SaveAs(ImgPath)

            DbImgPath = ImgPath.Substring(ImgPath.LastIndexOf("\"))

            DbImgPath = DbImgPath.Insert(0, "BookImages")

            cmd.Parameters.AddWithValue("@ImagePath", DbImgPath)

            con.Open()

            cmd.ExecuteNonQuery()

            con.Close()

            Clear_Controls()

            BindRepeater()

        Catch ex As Exception

            'Response.Write("Oops!! Error occured:" + ex.Message.ToString());

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured: " & ex.Message.ToString() & "');", True)

        Finally

            cmd.Dispose()

            con.Close()

            ImgPath = String.Empty

            DbImgPath = String.Empty

        End Try

    End Sub

    Private Sub BindRepeater()

        Dim dt As New DataTable()

        Dim adp As New SqlDataAdapter()

        Try

            adp = New SqlDataAdapter("BindBookDetails_Sp", con)

            adp.SelectCommand.CommandType = CommandType.StoredProcedure

            adp.Fill(dt)

            If dt.Rows.Count > 0 Then

                repBookDetails.DataSource = dt

                repBookDetails.DataBind()

            Else

                repBookDetails.DataSource = Nothing

                repBookDetails.DataBind()

            End If

        Catch ex As Exception

            'Response.Write("Oops!! Error occured:" + ex.Message.ToString());

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured: " & ex.Message.ToString() & "');", True)

        Finally

            con.Close()

            dt.Clear()

            dt.Dispose()

            adp.Dispose()

        End Try

    End Sub

    Protected Function CalcDiscount(ByVal prc As Integer) As Integer

        Return prc * 10 / 100

    End Function

    Protected Function CalcAmount(ByVal prc As Integer) As Integer

        Return prc - CalcDiscount(prc)

    End Function

    Private Sub Clear_Controls()

        txtBookName.Text = String.Empty

        txtAuthor.Text = String.Empty

        txtPublisher.Text = String.Empty

        txtPrice.Text = String.Empty

        txtBookName.Focus()

    End Sub

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 

• Tweet
• Share
• Share
• Share

Related Post