Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
stored proc for filtering winforms (c#) treeview

Answered stored proc for filtering winforms (c#) treeview

  • Saturday, January 26, 2013 11:58 PM
     
      Has Code
    Sign In to Vote
    0

    Hello.  I have a situation in which the dba is out for awhile and I suck at the sql server side of programming.  She was to provide all the stored procs, functions, etc to us.  What I have so far is what would create a treeview hierarchy on the winform.  The procedure below is a test to see what I can do.  What the form is showing are 4 checkboxes above the treeview to filter the treeview based on the checkbox selection (All, Client, Company, Vendor).  Anyone or a combination (other than All) can be selected.  A Client can also be a Vendor.  One of our own subsidiary is also a Vendor.  In the Company table there are a series of bit fields.  Of those 3 are IsCompany, IsClient, IsVendor.  Need to filter based on these 3 fields.  What is showing below works fine for the IsCompany but the others are absent.  I would like to get by with just one stored proc if possible and I'm lost on where to go form here.  I've indicated my strength at this side and seek help from those who do this alot more than I do.  Anyone able to help out?

    ...John

    ALTER PROCEDURE [dbo].[Company_tvw_company_SEL]
			@type bit
					
AS
BEGIN
	SELECT CompanyID,
		ISNULL(ParentCompanyID, 0) AS ParentID,
		Name
	FROM tblCompany
	WHERE Remove = 0 AND IsCompany = @type
	ORDER BY ParentID, Name
END


-- exec Company_tvw_company_SEL 1

     

All Replies

  • Sunday, January 27, 2013 12:30 AM
     
      Has Code
    Sign In to Vote
    0
     
    ALTER PROCEDURE [dbo].[Company_tvw_company_SEL]
(@Companytype bit,@Vendortype bit,@Clienttype bit)
					
AS
BEGIN
SELECT CompanyID,ISNULL(ParentCompanyID, 0) AS ParentID,Name
FROM tblCompany
WHERE Remove = 0 AND (IsCompany = @Companytype or 
IsVendor = @Vendortype or IsClient = @Clienttype)
ORDER BY ParentID, Name
END


-- exec Company_tvw_company_SEL 1,0,0 --if the you want find Company Type
    -- exec Company_tvw_company_SEL 0,1,0 --if the you want find Vendor Type
    -- exec Company_tvw_company_SEL 0,0,1 --if the you want find  Client Type
    -- exec Company_tvw_company_SEL 1,1,1 --if the you want find All of them
    I think you do not need Option 'ALL' since you are going to give them choice to select more than one anyway. But if you still want to do it
    ALTER PROCEDURE [dbo].[Company_tvw_company_SEL]
(@All bit,@Companytype bit,@Vendortype bit,@Clienttype bit)
					
AS
BEGIN
IF (@All =1)
Begin
set @CompanyType=1
Set @VendorType=1
Set @ClientType=1
End
SELECT CompanyID,ISNULL(ParentCompanyID, 0) AS ParentID,Name
FROM tblCompany
WHERE Remove = 0 AND (IsCompany = @Companytype or 
IsVendor = @Vendortype or IsClient = @Clienttype)
ORDER BY ParentID, Name
END



    Hope it Helps!!




    • Edited by Stan210 Sunday, January 27, 2013 1:21 AM
    •  
     
  • Sunday, January 27, 2013 1:55 AM
     
     
    Sign In to Vote
    0

    Stan210, thanks for the reply.  I read over my post and I forgot to mention that all the bit fields default to zero (0).  No nulls show in the table.  I have a test table that has 38 different companies listed.  Two of them have IsCompany only checked, two have IsClient checked with one also IsVendor checked, and one has IsClient only checked.  Even with the edited version more than these 4 are showing up.  Any you are correct in not needing the ALL.  What is needed for that already exists and I can reuse it.  It would only be just for the remaining three.

    ...John

     
  • Sunday, January 27, 2013 2:24 AM
     
     Answered Has Code
    Sign In to Vote
    0
    I used OR condition above. It looks like you need,  AND condition there.  
    ALTER PROCEDURE [dbo].[Company_tvw_company_SEL]
(@All bit,@Companytype bit,@Vendortype bit,@Clienttype bit)
					
AS
BEGIN
IF (@All =1)
Begin
set @CompanyType=1
Set @VendorType=1
Set @ClientType=1
End
SELECT CompanyID,ISNULL(ParentCompanyID, 0) AS ParentID,Name
FROM tblCompany
WHERE Remove = 0 AND IsCompany = @Companytype and
IsVendor = @Vendortype and IsClient = @Clienttype
ORDER BY ParentID, Name
END

    Hope it Helps!!


    • Edited by Stan210 Sunday, January 27, 2013 2:24 AM
    • Marked As Answer by johnboy0276 Sunday, January 27, 2013 4:16 AM
    •  
     
  • Sunday, January 27, 2013 2:28 AM
     
     
    Sign In to Vote
    0

    Thanks for the info.  I will give it a try.  How do I get rid of the @all?

     
  • Sunday, January 27, 2013 2:36 AM
     
     
    Sign In to Vote
    0
    ALTER PROCEDURE [dbo].[Company_tvw_company_SEL] (@Companytype bit,@Vendortype bit,@Clienttype bit) AS BEGIN SELECT CompanyID,ISNULL(ParentCompanyID, 0) AS ParentID,Name FROM tblCompany WHERE Remove = 0 AND IsCompany = @Companytype and IsVendor = @Vendortype and IsClient = @Clienttype ORDER BY ParentID, Name END

    Hope it Helps!!

     
  • Sunday, January 27, 2013 3:20 AM
     
      Has Code
    Sign In to Vote
    0

    I want to thank you for hangin' in on this.  To give you a rundown of what is found so far, I made a test st proc and plugged it into the front end code (c#) of the button that I'm using, passed in the 3 params (all rough code).  I do not get any errors when I run it. 
    I check the Company box, I get nothing, should get 2. 
    I check the Client box, I get one, should get 2.
    I check the Vendor box, I get nothing, should get 1. 
    The Client one that does show up has nothing checked for the other 2.  But the 2 Company's don't have anything else checked.  Combinations donot show any result.  I posted the test st proc below.  I'm thinking there should be some type of AND/OR in here. 

    What do you think?

    ALTER PROCEDURE [dbo].[Company_tvw_test_SEL]
			@iscompany bit,
			@isclient bit,
			@isvendor bit
					
AS

BEGIN

SELECT CompanyID,
		ISNULL(ParentCompanyID, 0) AS ParentID,
		Name,
		IsCompany,
		IsClient,
		IsVendor

FROM tblCompany

WHERE Remove = 0 AND IsCompany = @iscompany 
		AND 
		IsClient = @isclient 
		AND  
		IsVendor = @isvendor
			
ORDER BY ParentID, Name

END

    -- exec Company_tvw_test_SEL 1,0,0 --if the you want find Company Type

-- exec Company_tvw_test_SEL 0,1,0 --if the you want find Client Type

-- exec Company_tvw_test_SEL 0,0,1 --if the you want find Vendor Type

     
  • Sunday, January 27, 2013 4:16 AM
     
     
    Sign In to Vote
    0

    Stan210, I think I got it figured out.  After tracing thru a few times it seems to narrow down to the populating of the treeview.  The one company that shows up as client is a top level parent (0) while all the others are not so they depend on the parent before they can show in the treeview.  Case solved, for now.  When the real app goes live all entered companies will be checked one or more of the 3.  But that still may not solve the problem as a Client could have a subsidary location that might also be our vendor but won't show since the parent client is not a vendor.   Well, so much for the filtering of the treeview.  Might think about some other means to show the filtering, listbox maybe.

    Thanks again for hanging around for this.

    ...John

     
  • Sunday, January 27, 2013 4:17 AM
     
     
    Sign In to Vote
    0
    ALTER PROCEDURE [dbo].[Company_tvw_test_SEL] @iscompany bit =0, @isclient bit =0, @isvendor bit =0 AS BEGIN SELECT CompanyID, ISNULL(ParentCompanyID, 0) AS ParentID, Name, IsCompany, IsClient, IsVendor FROM tblCompany WHERE Remove = 0 AND IsCompany = @iscompany AND IsClient = @isclient AND IsVendor = @isvendor ORDER BY ParentID, Name END

    Hope it Helps!!

     
  • Sunday, January 27, 2013 4:24 AM
     
     
    Sign In to Vote
    0
    I defaulted the parameters to 0. I suspect that when you are running from the web form, I think it is passing null there which is way you are not getting any values as you mentioned earlier you do not have null values,it is 1 or 0. Could you please make it clear on this. If the user selects both company and vendor check box,, do you want return the rows where company and vendor =1 or do you want select where either one (company or vendor) =1.

    Hope it Helps!!

     
  • Sunday, January 27, 2013 4:47 AM
     
     
    Sign In to Vote
    0
    Thanks again.  What the user would do is if filter for company and vendor they would get all the companies with 1 and all the vendors with 1.  So now the treeview would have 2 categories showing.  I might still see about the populating so the treeview can still be used maybe change it so it shows a listing rather than a parent/child look during the filtering.