.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> XML >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Working with objectDatasource in ASP.NET

Posted By:Athira Sreejith       Posted Date: March 11, 2009    Points: 10    Category: XML    URL: http://www.dotnetspark.com  
 

Working with objectDatasource in ASP.NET


                                                                                                            OnPageIndexChanging="gvPSpopup_PageIndexChanging" AllowPaging="True" PageSize="20"
                                                                                                            DataKeyNames="Id" DataSourceID="ObjectDataSource1">
                                                                                                           
                                                                                                                Product Sampled not avaliable.
                                                                                                           

                                                                                                           
                                                                                                               
                                                                                                                   
                                                                                                                        Select
                                                                                                                   

                                                                                                                   
                                                                                                                       
                                                                                                                   

                                                                                                               

                                                                                                               
                                                                                                                   
                                                                                                                       
                                                                                                                   

                                                                                                               

                                                                                                               
                                                                                                           

                                                                                                       


        TypeName="VetCareWeb.UserDAO" EnablePaging="True" SelectCountMethod="GetUserCount" >
       
           
           
           
           
           
       

   


UserDAO.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace VetCareWeb
{
    public class UserDAO
    {
        public const int results = 20;
        int max;
        string Admincount = "0";

        public DataTable BindUsers(int startRowIndex, int maximumRows, string strFilterIDs, string strFindPSText, string EmpId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["VetCareWeb"].ConnectionString);
            SqlCommand com = new SqlCommand("GetProductsampled", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add("@RowIndex", SqlDbType.Int, 4).Value = startRowIndex;
            com.Parameters.Add("@MaxRows", SqlDbType.Int, 4).Value = 20;
            com.Parameters.Add("@Id", SqlDbType.VarChar, 50).Value = strFilterIDs;
            com.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = strFindPSText;
            com.Parameters.Add("@Employee_Id", SqlDbType.VarChar, 50).Value = VetCareSession.LoginID;

            SqlDataAdapter ada = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            ada.Fill(dt);
            return dt;
        }
        public int GetUserCount(int startRowIndex, int maximumRows, string strFilterIDs, string strFindPSText, string EmpId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["VetCareWeb"].ConnectionString);
            con.Open();
            SqlCommand com = new SqlCommand("GetProductCount", con);
            //com.CommandType = CommandType.StoredProcedure;
            //com.Parameters.Add("@RowIndex", SqlDbType.Int, 4).Value = startRowIndex;
            //com.Parameters.Add("@MaxRows", SqlDbType.Int, 4).Value = maximumRows;
            //com.Parameters.Add("@Id", SqlDbType.VarChar, 50).Value = strFilterIDs;
            //com.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = strFindPSText;
            //com.Parameters.Add("@Employee_Id", SqlDbType.VarChar, 50).Value = VetCareSession.LoginID;

            SqlDataReader dr = com.ExecuteReader();
            int count = 0;
            while (dr.Read())
            {
                count = dr.GetInt32(0);
            }
            return count;
        }
    }
}


SP:

ALTER PROCEDURE dbo.GetProductsampled
(
@Id varchar(50)='',
@Name varchar(50)='',
@Employee_Id varchar(50)='',
@start int = 0,
@RowIndex INT ,
@MaxRows INT
)
AS
DECLARE @StartRow INT
DECLARE @EndRow INT
 
SET @StartRow = (@RowIndex+1)
SET @EndRow = @StartRow + @MaxRows

  if(@Id <> '')
    begin 
             
             SELECT * FROM (
      select Id,Name,PrintName ,Row_Number() over (order by name)as ROW From dbo.In_ItemMaster
      where IsSampled=1 and Active=1  and id not in(@Id) and MktGroup_Id in(select Id from In_MktItemGroup
               where Division_Id in(select division_id from pay_employee where Id=@Employee_Id))
                )As NumberedUsers WHERE ROW BETWEEN @StartRow AND @EndRow           
             
            end
            else
           
             if(@Name <> '')
             begin  
            
             SELECT * from (select Id,Name,PrintName ,ROw_NUMBER() over(order by name) as ROw From dbo.In_ItemMaster
              where IsSampled=1 and Active=1 and  MktGroup_Id in(select Id from In_MktItemGroup where Division_Id
             in(select division_id from pay_employee where id=@Employee_Id))
             and Name like '%'+ @Id +'"%')As NumberedUsers WHERE ROW BETWEEN @StartRow AND @EndRow
            
             end
            
             else
             begin
            
            
              SELECT  * from (select Id,Name,PrintName ,ROw_NUMBER() over(order by name) as ROW From dbo.In_ItemMaster
              where IsSampled=1 and Active=1 and  MktGroup_Id in(select Id from In_MktItemGroup where Division_Id
             in(select division_id from pay_employee where id=@Employee_Id))
             )As NumberedUsers WHERE ROW BETWEEN @StartRow AND @EndRow
            
            
             end
           
          
           
 
ALTER PROCEDURE GetProductCount
(
@Id varchar(50)='',
@Name varchar(50)='',
@Employee_Id varchar(50)='',
@start int = 0
)
 
AS
 Set NOCOUNT ON
 
 select count(*) from In_ItemMaster



     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend