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

  • Question

  • User-1994446809 posted

    Hello Forum,

    I have a table structure like this

    Id

    Email

    Name

    Admin_email

    CreatedDate

    1

    simons@yahoo.com

    Simons Simons

    simons@yahoo.com

     

    2

    Wills@gmail.com

    Wiliams Terrence

    Preston@devon.com

     

    3

    queensland@hotmail.com

    Queenland Limited

    queensland@hotmail.com

     

    4

    Preston@devon.com

    Preston Associates

    Preston@devon.com

     

    5

    Larry2020@gmail.com

    Larry Howard

    queensland@hotmail.com

     

    6

    Peter02@gmail.com

    Peter Collins

    queensland@hotmail.com

     

    7

    Trecords@yahoo.com

    Terra Records Inc.

    Trecords@yahoo.com

     

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

    Saturday, October 10, 2020 7:30 PM

Answers

  • User475983607 posted

    Assuming you are using standard security the username is found by...

    User.Identity.Name

    Once you have the username you can Join to the user table and filter by the username.  Or get whatever info you need about the current user.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 10, 2020 10:39 PM