C# ASP.NET CRUD PART-2: Show table using Store Procedure or Data Binding into table from database

This blog is the second part of the previous blog so before using the following codes you have to know about the insertion process into the table. So, in ‘C# ASP.NET CRUD PART-1’ we created a table “tb_ank” and inserted data into that table using store procedure. Now, I am going to explain how can you fetch your data from database or how can you bind your data into a table that you have inserted into your table using a simple store procedure.

Must read previous blog for better understanding for this article. The link for the previous blog is as follows:

Insert into table using store procedure in ASP.net C#

Create a Store Procedure for select data from table

Firstly, open your SQL server and create a store procedure for fetch data from table. As follows:

create procedure sp_tb_ank_select
as
begin
select * from tb_ank
end

In the above code “sp_tb_ank_select” is the name of the store procedure and “tb_ank” is our table that was created in part-1 of this blog.

Design a table into WebForm

Here is the code of a table and a table row within a repeater block for designing purpose. The structure of the table is as follows:

<table style="border:1px solid">
                <asp:Repeater ID="repeater1" runat="server">
                    <ItemTemplate>
                <tr>
                    <td style="border:1px solid">
                        <asp:Label ID="Label5" runat="server" Text='<%#Eval("Name")%>'></asp:Label>
                    </td>
                    <td style="border:1px solid">
                        <asp:Label ID="Label6" runat="server" Text='<%#Eval("Email")%>'></asp:Label>
                    </td>
                    <td style="border:1px solid">
                        <asp:Label ID="Label7" runat="server" Text='<%#Eval("Mobile")%>'></asp:Label>
                    </td>
                    <td style="border:1px solid">
                        <asp:Label ID="Label8" runat="server" Text='<%#Eval("City")%>'></asp:Label>
                    </td>
                </tr>
                        </ItemTemplate>
                    </asp:Repeater>
            </table>

Backend Code for data binding into the table

In the WebForm.aspx.cs page we create a function named as “Getalldata()” for data binding into the table. The code for that is as follows:

protected void Getalldata()
        {
            checkconnection();
            SqlDataAdapter sda = new SqlDataAdapter("sp_tb_ank_select",con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dt = new DataTable();
            sda.Fill(dt);
            repeater1.DataSource = dt;
            repeater1.DataBind();
        }

Now, Call this function within the Main Class. Because we have to show the table when our page loaded.

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                btnCancel.Visible = false;
                btnUpdate.Visible= false;
                Getalldata();
            }
        }

Finally we all set with our codes. Full code on the WebForm1.aspx.cs page looks like that:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace myCRUD
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        SqlConnection con= new SqlConnection(@"Data Source=LAPTOP-XXXXXXXX\SQLEXPRESS;Initial Catalog=crud;Integrated Security=True");

        protected void checkconnection()
        {
            if(con.State == System.Data.ConnectionState.Closed)
                con.Open();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Getalldata();     //call the get all function
            }
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            try
            {
                checkconnection();
                SqlCommand cmd =  new SqlCommand("sp_tb_ank_insert",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name",txtName.Text);
                cmd.Parameters.AddWithValue("@Email",txtEmail.Text);
                cmd.Parameters.AddWithValue("@Mobile",txtMobile.Text);
                cmd.Parameters.AddWithValue("@City",txtCity.Text);
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    Response.Write("<script>alert('Data Inserted!')</script>");
                }
                else
                {
                    Response.Write("<script>alert('Data Not Inserted!')</script>");
                }

            }
            catch(Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        protected void btnClear_Click(object sender, EventArgs e)
        {
            txtName.Text = "";
            txtEmail.Text = "";
            txtMobile.Text = "";
            txtCity.Text = "";
        }

      // data binding code

        protected void Getalldata()
        {
            checkconnection();
            SqlDataAdapter sda = new SqlDataAdapter("sp_tb_ank_select",con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dt = new DataTable();
            sda.Fill(dt);
            repeater1.DataSource = dt;
            repeater1.DataBind();
        }
    }
}