Untitled

CREATE PROCEDURE AdminViewProducts
@PageIndex INTEGER = 1,
@PageSize INTEGER = 10,
@RecordCount INTEGER OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER()
OVER
(
 ORDER BY Product_Category_Name
)AS RowNumber,
    Product_No,
    Product_Image,
    Product_Name,
    Barcode_No,
    Product_Category_Name,
    Product_Price,
    Product_Quantity,
    Grocery_Branch_Name
    INTO #Results
    FROM Product,Product_Category,Grocery_Branch
    WHERE Product_Category.Product_Category_No = Product.Product_Category_No
    AND Grocery_Branch.Grocery_Branch_No = Product.Grocery_Branch_No

    SELECT * FROM #Results
    WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1
    AND
    (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

    SELECT @RecordCount = COUNT(*) FROM #Results

    DROP TABLE #Results
END


CREATE PROCEDURE AdminSearchProduct
@ProductName VARCHAR(50),
@PageIndex INTEGER = 1,
@PageSize INTEGER = 10,
@RecordCount INTEGER OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
 ORDER BY Product_Name
)
AS RowNumber,
    Product_No,
    Product_Image,
    Product_Name,
    Barcode_No,
    Product_Category_Name,
    Product_Price,
    Product_Quantity,
    Grocery_Branch_Name
    INTO #Results
    FROM Product,Product_Category,Grocery_Branch
    WHERE Product_Category.Product_Category_No = Product.Product_Category_No
    AND Grocery_Branch.Grocery_Branch_No = Product.Grocery_Branch_No
    AND Product_Name LIKE '%' + @ProductName + '%'

    SELECT * FROM #Results
    WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1
    AND
    (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

    SELECT @RecordCount = COUNT(*) FROM #Results

    DROP TABLE #Results
END

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link href="CSS/searchtext.css" rel="stylesheet" />
<link href="CSS/searchproducts.css" rel="stylesheet" />

<form id="form1" runat="server">

<main>
<div class="content-wrap">
<h4 class="searchtext">
<strong>Search by Product Name</strong>
</h4>

<div class="searchproducts">
<asp:Table ID="Table1" runat="server" align="center">
<asp:TableRow ID="TableRow1" runat="server">
<asp:TableCell HorizontalAlign="Right">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</asp:TableCell>
<asp:TableCell HorizontalAlign="Left">
<asp:Button ID="Button8" runat="server" OnClick="Search_Product" ValidationGroup="SearchByName" Text="Search Product" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1" ValidationGroup="SearchByName" ErrorMessage=" Please enter a product name" Font-Bold="True"></asp:RequiredFieldValidator>
<asp:Label ID="Label3" runat="server" Font-Bold="True"></asp:Label>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
</div>

<br />
<br />

<div style="margin-left: 265px;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" HorizontalAlign="Center" HeaderStyle-BackColor="#00a400" HeaderStyle-ForeColor="White">
<Columns>
<asp:BoundField DataField="Product_No" HeaderText="Product No" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" Height="100px" Width="150px"
                                            ImageUrl='<%#"data:Image/png/jpg/jpeg/gif/bmp;base64," + Convert.ToBase64String((byte[])Eval("Product_Image")) %>' />
</ItemTemplate>
                                </asp:TemplateField>
<asp:BoundField DataField="Product_Name" HeaderText="Product" />
<asp:BoundField DataField="Barcode_No" HeaderText="Barcode" />
<asp:BoundField DataField="Product_Category_Name" HeaderText="Category" />
<asp:BoundField DataField="Product_Price" HeaderText="Price" DataFormatString="{0:0.00} AUD" />
<asp:BoundField DataField="Product_Quantity" HeaderText="Quantity" />
<asp:BoundField DataField="Grocery_Branch_Name" HeaderText="Branch" />
</Columns>
</asp:GridView>

<br />
<table style="align-content:center; width:800px;">
<tr>
<td>
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>' Enabled='<%# Eval("Enabled") %>' OnClick="lnkbtn_PageIndexChanged" ForeColor="#00a400"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</td>
</tr>
</table>

</div>

</div>
</main>

</body>
</html>

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

namespace Grocery_Demo
{
 public partial class AdminViewProducts : System.Web.UI.Page
 {
  int PageSize = 10;

  protected void Page_Load(object sender, EventArgs e)
 {

  if (Session["UsernameAdmin"] != null && Session["PasswordAdmin"] != null && Session["BranchAdmin"] != null)
 {
  string username = Session["UsernameAdmin"].ToString();
  string password = Session["PasswordAdmin"].ToString();
  string branch = Session["BranchAdmin"].ToString();
  string CS = ConfigurationManager.ConnectionStrings["Grocery_DemoConnectionString"].ConnectionString;
  SqlConnection con = new SqlConnection(CS);
  SqlCommand cmd = new SqlCommand("AdminValidation", con);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;

  cmd.Parameters.AddWithValue("@AdministratorUsername", username);
  cmd.Parameters.AddWithValue("@AdministratorPassword", password);
  cmd.Parameters.AddWithValue("@GroceryBranchName", branch);
  con.Open();
  SqlDataReader read = cmd.ExecuteReader();
  read.Read();
  if (read.HasRows == false)
  {
   Response.Redirect("SignIn.aspx");
  }
    con.Close();
   }

   else
  {
   Response.Redirect("SignIn.aspx");
  }

   Label1.Text = Session["BranchAdmin"].ToString();
   Label2.Text = Session["UsernameAdmin"].ToString();
  {
    if (!Page.IsPostBack)
    DisplayProducts(1, PageSize);
   }

  }

 SqlDataAdapter da;
 DataSet ds = new DataSet();
 private void DisplayProducts(int PageIndex, int PageSize)
 {
  string CS = ConfigurationManager.ConnectionStrings["Grocery_DemoConnectionString"].ConnectionString;

 using (SqlConnection con = new SqlConnection(CS))
{
  using (SqlCommand cmd = new SqlCommand("AdminViewProducts", 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;
   da = new SqlDataAdapter(cmd);
   da.Fill(ds);
   con.Open();
   cmd.ExecuteNonQuery();

   if (ds.Tables[0].Rows.Count > 0)
   {
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind();
   }
  int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    this.PopulatePager(recordCount, PageIndex);
   }
   }


   }


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

protected void lnkbtn_PageIndexChanged(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
DisplayProducts(pageIndex, PageSize);
}

protected void Search_Product(object sender, EventArgs e)
{
int PageIndex = 1;
string CS = ConfigurationManager.ConnectionStrings["Grocery_DemoConnectionString"].ConnectionString;

using (SqlConnection con = new SqlConnection(CS))
{
using (SqlCommand cmd = new SqlCommand("AdminSearchProduct", con))
{

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%");
cmd.Parameters.AddWithValue("@PageIndex", PageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Open();
cmd.ExecuteNonQuery();

if (ds.Tables[0].Rows.Count > 0)
{
 GridView1.DataSource = ds.Tables[0];
 GridView1.DataBind();
}

else
{
 Label3.Text = " Couldn't find your product";
}

int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, PageIndex);
}
}
}

}
}

FIRST >> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 << LAST

FIRST >> 1 2 3 4 5 6 7 8 9 << LAST

FIRST >> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 << LAST

FIRST >> 1 2 3 4 5 6 7 8 9 << LAST