Displaying Records from table Using Stored Procedure based on user logged in

Question

User-1994446809 posted

Hello Forum,

I have a table structure like this

Id	Email	Name	Admin_email	CreatedDate
1	[e-mail]	Simons Simons	[e-mail]
2	[e-mail]	Wiliams Terrence	[e-mail]
3	[e-mail]	Queenland Limited	[e-mail]
4	[e-mail]	Preston Associates	[e-mail]
5	[e-mail]	Larry Howard	[e-mail]
6	[e-mail]	Peter Collins	[e-mail]
7	[e-mail]	Terra Records Inc.	[e-mail]

The above table shows that Williams Terrence and Preston Associates are linked together; Queenland Limited, Larry Howard and Peter Collins are linked together. While Simons Simons and Terra Records are two separate data.

I am trying to display data in gridview from data table “Users” using SQL in stored procedure based on user that is logged in. If Simons Simons logs in and navigates to the gridview, then all data related to him should display in the gridview. If Preston Associates logs in, then all related data to Preston should be displayed and other data that is not related to Preston will not show. I want to call the column name “Admin_email”  because there is a relation between users in the column “Admin_email.

 I tried this SQL but I am finding difficult to make it work. Please is this the correct statement?

Here is my SQL (stored procedure)

CREATE PROCEDURE [dbo].[Dataregister]
	@PageIndex INT
	,@PageSize INT
	,@RecordCount INT OUT
AS
BEGIN
    DECLARE @Admin_email VARCHAR(50)

    SELECT ROW_NUMBER() OVER(ORDER BY email) RowNumber 
            ,email 
	    ,Name
	    ,Admin_email
    INTO #Temp 
    FROM Users
	WHERE Admin_email = @Admin_email

	SELECT @RecordCount = COUNT(*) FROM #Temp 

	SELECT * FROM #Temp 
    WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1 
   
    DROP TABLE #Temp 

	END
RETURN 0

HTML

  <center>
        <div class="grid-corner" style="width: 80%; background-color: white; font-size:9pt;">
            <div style="width:100%; background-color:#A0A0A0;font-family:Verdana; font-size:medium;">
              <label>Invoice List</label>
            </div>
            <br />
            <asp:GridView ID="GridView1" runat="server" GridLines="None" AllowPaging="true" HeaderStyle-ForeColor="#00003D"
                HeaderStyle-Font-Bold="true" AutoGenerateColumns="false" class="table" Width="100%">
                <EmptyDataTemplate>
                   <div style="text-align: center; font-weight: bolder; font-size: medium;">
                       <asp:Label ID="labelTemp" runat="server" Text="No Record"></asp:Label>
                   </div>
               </EmptyDataTemplate>
                <Columns>
                    <asp:BoundField DataField="Email" HeaderText="User Email" />
                     <asp:BoundField DataField="Name" HeaderText="Name" />
                    <asp:BoundField DataField="Admin_email" HeaderText="Admin Email" />
                </Columns>
            </asp:GridView>
            
            <div style="float: right; font-size:10pt;">
                Page&nbsp;
        <asp:Label ID="lblPageIndex" runat="server" Text="Label" Font-Bold="true" />
                &nbsp;of&nbsp;
        <asp:Label ID="lblTotalPage" runat="server" Font-Bold="true" />
                (<asp:Label ID="lblTotal" runat="server" Font-Bold="true" />
                items)&nbsp;&nbsp;
        <div class="dvPager">
            <asp:Repeater ID="rptPager" runat="server">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                        CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
                        OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
                </ItemTemplate>
            </asp:Repeater>
        </div>
            </div>
            <br />
            <br />
        </div>
        </center>

CSS

    <style type="text/css">
        .Item {
            border: 1px solid #ccc;
        }

            .Item th {
                background-color: #F7F7F7;
                color: #333;
                font-weight: bold;
            }

            .Item th, .Item td {
                padding: 5px;
                border-color: #ccc;
            }

        .page_enabled, .page_disabled {
            display: inline-block;
            height: 25px;
            min-width: 30px;
            line-height: 25px;
            text-align: center;
            text-decoration: none;
            border: 1px solid #ccc;
        }

        .page_disabled {
            background-color: #003366;
            color: white;
        }

        .page_enabled {
            background-color: white;
            color: #003366 !important;
        }

        .dvPager {
            border: 1px solid #ccc;
            font-size: 0;
            float: right;
            border-radius: 4px;
            font-size: 8pt;
        }

            .dvPager a {
                font-size: 10pt;
            }

            .grid-corner {
                border: 0px solid #C0C0C0;
        -webkit-border-radius: 4px;
        -moz-border-radius: 4px;
        border-radius: 4px;
        overflow: scroll;
            }
    </style>

C#

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using System.Xml.Linq;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;

public partial class InvoiceRecords : System.Web.UI.Page
{
    SqlCommand cmd = new SqlCommand();
    SqlDataAdapter sda = new SqlDataAdapter();
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True");

    private int PageSize = 5;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.GetCustomersPageWise(1);
        }
    }

    private void GetCustomersPageWise(int pageIndex)
    {
        using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True"))
        {
            using (SqlCommand cmd = new SqlCommand("Dataregister", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", PageSize);
                cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                con.Open();
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                        con.Close();
                        int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                        this.PopulatePager(recordCount, pageIndex);
                    }
                }
            }
        }
    }

    protected void Page_Changed(object sender, EventArgs e)
    {
        int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
        this.GetCustomersPageWise(pageIndex);
    }

    private void PopulatePager(int recordCount, int currentPage)
    {
        double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
        int pageCount = (int)Math.Ceiling(dblPageCount);
        List<ListItem> pages = new List<ListItem>();
        if (pageCount > 0)
        {
            if (currentPage != 1)
            {
                pages.Add(new ListItem("Prev", (currentPage - 1).ToString()));
            }
            if (pageCount < 4)
            {
                for (int i = 1; i <= pageCount; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
            }
            else if (currentPage < 4)
            {
                for (int i = 1; i <= 4; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
            }
            else if (currentPage > pageCount - 4)
            {
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
                for (int i = currentPage - 1; i <= pageCount; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
            }
            else
            {
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
                for (int i = currentPage - 2; i <= currentPage + 2; i++)
                {
                    pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                }
                pages.Add(new ListItem("...", (currentPage).ToString(), false));
            }
            if (currentPage != pageCount)
            {
                pages.Add(new ListItem("Next", (currentPage + 1).ToString()));
            }
        }
        rptPager.DataSource = pages;
        rptPager.DataBind();

        lblPageIndex.Text = currentPage.ToString();
        lblTotalPage.Text = ((recordCount / PageSize) + ((recordCount % PageSize) > 0 ? 1 : 0)).ToString();
        lblTotal.Text = recordCount.ToString();
    }
}