Link to home
Create AccountLog in
Avatar of ddpatel0520
ddpatel0520

asked on

How to make dynamic CAML query in custom web part by c# ?

Experts,

 I have created the custom web part and used gridview to display sharepoint list data for this requirement we need to allow users to filter data so i added some dropdown and text controls for filter.

I am going to use six columns for filter so i need to make CAML quesry based on six columns. It takes time to manually write CAML query based on filter thay use so i am looking for any dynamic CAML query.

Can you please help me to make this query ?

Thank you
Avatar of Rajendra Shekhawat
Rajendra Shekhawat
Flag of India image

Hi,

Use caml builder tool to query all columns. In webpart based on whether user has entered values in specific columns or not, change caml query in code behind.

Cheers,
Rajendra shekhawar
Avatar of ddpatel0520
ddpatel0520

ASKER

Hi,

 Thanks for quick response. I am using c# code i used .net controls in panel and and based on user input i need to make query and display data in Gridview.  let me add some pics so you can better idea. can you please provide some example if you have so i can use that code in my webpart.

here is the input form

User generated image
And here is the code i am using for now..

  SPQuery query2 = new SPQuery();
            if (lblDept == "All" & lblCaltype == "All" & lblOwnerGroup == "All" & ddlRFCType.Text == "All")
            {

                query2.Query = "<OrderBy><FieldRef Name='Requested_x0020_Date' Ascending='True' /></OrderBy><Where><And><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Closed</Value></Neq><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Resolved</Value></Neq></And></Where>";
            }
            else if (lblDept != "All" & lblCaltype == "All" & lblOwnerGroup == "All" & ddlRFCType.Text == "All")
            {
                query2.Query = "<OrderBy><FieldRef Name='Requested_x0020_Date' Ascending='True' /></OrderBy><Where><And><And><Contains><FieldRef Name='Department' /><Value Type='Text'>" + ddlDepartment.Text + "</Value></Contains><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Closed</Value></Neq></And><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Resolved</Value></Neq></And></Where>";
            }
            else if (lblDept == "All" & lblCaltype != "All" & lblOwnerGroup == "All" & ddlRFCType.Text == "All")
            {
                query2.Query = "<OrderBy><FieldRef Name='Requested_x0020_Date' Ascending='True' /></OrderBy><Where><And><And><Eq><FieldRef Name='RFC_CalType' /><Value Type='Text'>" + ddlCalType.Text + "</Value></Eq><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Closed</Value></Neq></And><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Resolved</Value></Neq></And></Where>";
            }
            else if (lblDept == "All" & lblCaltype == "All" & lblOwnerGroup != "All" & ddlRFCType.Text == "All")
            {
                query2.Query = "<OrderBy><FieldRef Name='Requested_x0020_Date' Ascending='True' /></OrderBy><Where><And><And><Contains><FieldRef Name='Group_x0020_Name' /><Value Type='Text'>" + ddlOwnerGroup.Text + "</Value></Contains><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Closed</Value></Neq></And><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Resolved</Value></Neq></And></Where>";
            }
            else if (lblDept == "All" & lblCaltype == "All" & lblOwnerGroup == "All" & ddlRFCType.Text != "All")
            {
                query2.Query = "<OrderBy><FieldRef Name='Requested_x0020_Date' Ascending='True' /></OrderBy><Where><And><And><Contains><FieldRef Name='RFC_x0020_Request_x0020_Type' /><Value Type='Text'>" + ddlRFCType.Text + "</Value></Contains><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Closed</Value></Neq></And><Neq><FieldRef Name='RFC_x0020_Status' /><Value Type='Text'>Resolved</Value></Neq></And></Where>";

            }
etc...

I have to use make query for all possibility and i don't want to do that.

let me know if you need more information..
ASKER CERTIFIED SOLUTION
Avatar of Rajendra Shekhawat
Rajendra Shekhawat
Flag of India image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Hi,
 Thank you Sir, It will work. i will close this question. I have Date column and i am planning to add filter option for date so user can use this column to filter quaterly , monthly or they can also input date. is there any inbuilt date control available in web part ?
Hi,

SharePoint has Datetime control. You can use the same.
Reference - http://karinebosch.wordpress.com/sharepoint-controls/datetimecontrol-control/ 

Cheers.
Rajendra Shekhawat
Thank you Sir !!