Answered by:
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
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 <asp:Label ID="lblPageIndex" runat="server" Text="Label" Font-Bold="true" /> of <asp:Label ID="lblTotalPage" runat="server" Font-Bold="true" /> (<asp:Label ID="lblTotal" runat="server" Font-Bold="true" /> items) <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