here's my sql stored procedure...
CREATE PROCEDURE Getproducts9
@PageIndex INT
,@PageSize INT = 12
,@CategoryId int
,@Brands nvarchar(50)
,@Price int
,@PageCount INT OUTPUT
AS
BEGIN
DECLARE @RecordCount INT
DECLARE @pricestart INT
DECLARE @priceend INT
if(@Price = 1)
begin
set @pricestart = 1
set @priceend = 3000
end
if(@Price = 2)
begin
set @pricestart = 3001
set @priceend = 7000
end
if(@Price = 3)
begin
set @pricestart = 7001
set @priceend = 12000
end
if(@Price = 4)
begin
set @pricestart = 12001
set @priceend = 18000
end
if(@Price = 5)
begin
set @pricestart = 18001
set @priceend = 30000
end
/*On page Load*/
IF(@CategoryId = 0 and @Brands is null)
if(@Price = 0)
begin
SELECT * FROM [Products]
end
else
begin
SELECT * FROM [Products] where Price between @pricestart and @priceend
end
/*if category id is selected*/
ELSE IF(@CategoryId != 0 and @Brands is null)
if(@Price = 0)
begin
SELECT * FROM [Products] where Categotyid = @CategoryId
end
else if(@Price != 0)
begin
SELECT * FROM [Products] where Categotyid = @CategoryId and Price between @pricestart and @priceend
end
/*Brands is selected*/
IF(@CategoryId != 0 and @Brands is not null)
if(@Price = 0)
begin
SELECT * FROM [Products] where CategotyId = @CategoryId and Name LIKE @Brands + '%'
end
else
begin
SELECT * FROM [Products] where Categotyid = @CategoryId and Name LIKE @Brands + '%' and Price between @pricestart and @priceend
end
END
and this my code to retrieve data from database...
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//dlCustomers.DataSource = GetCustomersData(pageindex,categoryid,brands,price);
dlCustomers.DataSource = GetCustomersData(1, 0, null, 0);
dlCustomers.DataBind();
}
}
public static DataSet GetCustomersData(int pageindex, int customerId, string brands, int price)
{
string query = "Getproducts9";
//string query = "[GetCustomersPageWise]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex", pageindex);//1
cmd.Parameters.AddWithValue("@PageSize", 12);//2
cmd.Parameters.AddWithValue("@CategoryId", customerId);//3
cmd.Parameters.AddWithValue("@Brands", brands);//4
cmd.Parameters.AddWithValue("@Price", price);//5
cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd);
}
private static DataSet GetData(SqlCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
DataTable dt = new DataTable("PageCount");
dt.Columns.Add("PageCount");
dt.Rows.Add();
dt.Rows[0][0] = cmd.Parameters["@PageCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
Khan Abrar AhmedPosted Jun 17, 2015, 3:06 AM
Upendra Pratap ShahiPosted Jun 17, 2015, 4:55 AM
Sreekanth ReddyPosted Jun 17, 2015, 3:09 AM