ListViewByQuery Control

The ListViewByQuery control renders a list view on an application page or within a web part based on a specified query.

When the control is displayed users can sort on the columns of the list view and apply filters. But all these events need to be implemented by you otherwise the rendering will fail with an ugly error.

Syntax

The ListViewByQuery control  is part of the Microsoft.SharePoint.dll and is located in theMicrosoft.SharePoint.WebControls namespace. You can use this control declaratively when developing application pages or control templates:

But there is a caveat: the properties List and Query only accept objects and cannot be set declaratively, only in code.

Don’t forget to add a directive at the top of the application page or control template:

If your are developing a web part you have to create the ListViewByQuery control completely in code:

private ListViewByQuery CustomersListViewByQuery;
private void EnsureChildControls()
{
     CustomersListViewByQuery = new ListViewByQuery();
     // The rest of the code follows here....
}

There are two mandatory properties that need to be set: the List property which is of type SPList and the Queryproperty which is of type SPQuery. The OnLoad event handler or the EnsureChildControls method then contains following code:

list = SPContext.Current.Web.Lists["Customers"];
CustomersListViewByQuery.List = list;
string query = null;
if (!Page.IsPostBack)
{
    query = "";
}
else
{
    // apply filtering and/or sorting
}
SPQuery qry = new SPQuery(list.DefaultView);
qry.Query = query;
CustomersListViewByQuery.Query = qry;

The query object must be instantiated based on an existing view of the list otherwise the ListViewByQuery control will not work. The data displayed by the control is determined by the CAML query itself.

You can limit the number of rows returned by setting the RowLimit property of the SPQuery object:

qry.RowLimit = 50;

This was the easy part but it becomes more complex when you need to implement sorting and filtering. When the user chooses to sort or to filter, the page is posted back with some extra information stored in the HttpRequest object.

Sorting

The user can decide to sort on a certain column by clicking the column header and choosing A on Top or Z on Top.

This instructs the page to post back, and the selected column and sort order is passed to the server stored into theContext.Request["SortField"]  and the Context.Request["SortDir"]. Following code sample calls the BuildSortOrder method and passes both constructs the  clause based on both request values.

if (!Page.IsPostBack)
{
    query = "";
}
else
{
    // apply filtering and/or sorting
   if (this.Context.Request != null && this.Context.Request["SortField"] != null)
   {
       string sortorder = BuildSortOrder(this.Context.Request["SortField"],
           (this.Context.Request["SortDir"] == "Asc" ? "true" : "false"));
       if (string.IsNullOrEmpty(query))
           query = sortorder;
       else
           query += sortorder;
   }
}

Following code sample constructs the  clause based on both request values:

private string BuildSortOrder(string fieldName, string sortOrder)
{
    string query = null;
    if (!string.IsNullOrEmpty(fieldName))
    {
        query = string.Format("", fieldName, sortOrder);
    }           
    return query;
}

Filtering

The user can also filter the data in the ListViewByQuery control. When the different values in one of the columns is not a large set of data, the user can select from a list of distinct values.

 

When the page is posting back after the user action, the selected field is stored in theContext.Request["FilterField1"] and the selected value is stored in the Context.Request["FilterValue1"]. If more than 1 filter criteria is specified by the user (by selecting a value of a second column) key/value pairs are added withFilterField2 and FilterValue2 as key, and so on.

Constructing the Where clause of a CAML query is not that easy. I give you the code sample to give you an idea on how you can proceed:

private string BuildFilter()
{
    string query = "{0}";
    bool isFound = true;
    int counter = 1;
    while (isFound)
    {
          string filterfield = "FilterField" + counter.ToString();
          string filtervalue = "FilterValue" + counter.ToString();
          if (this.Context.Request[filterfield] != null && this.Context.Request[filtervalue] != null)
          {
               // Field type must be treated differently in case of other data type
               if (counter > 1)
                   query = "" + query + "{0}";
               query = string.Format(query, string.Format("{1}",
                       this.Context.Request[filterfield], this.Context.Request[filtervalue]));
               counter++;
          }
          else
          {
               isFound = false;
          }
    }
    if (!string.IsNullOrEmpty(query))
       query = "" + query + "";
    return query;
}

As one of my readers pointed out, this code snippet only works for text fields. If you want to filter on other types of fields, like number fields, choice fields or lookups, you will have to change the Type attribute from the Value element into the correct type.  To get hold on the field type, you will have to retrieve the field from the list.

SPField field = list.Fields[filterfield];

And change the query statement accordingly:

query = string.Format(query, string.Format("{2}",
                       this.Context.Request[filterfield], field.TypeAsString, this.Context.Request[filtervalue]));

Grouping

With the ListViewByQuery control you can also build a view where the data is grouped. In that case you have to use the CAML GroupBy element:

query = "";

This sample code groups the data by the CountryRegionName and then by City. You can specify more than one group by criterium but the choice of showing the groups collapsed or expanded must be set for all groups by using theCollapse attribute of the GroupBy element.

Use the + and – signs to expand and collapse the different groups.

If you decide to show the groups expanded, you set the Collapse attribute to TRUE and you get the following view:

But then there is a small problem: you can expand all groups, except the latest one: it shows a Loading label but nevers shows the data.

When taking a look at the rendered HTML you can see that all of the hyperlinks that have to do with theListViewByQuery contain a call to the ExpCollGroup javascript call, which is a function in one of the javascript files that come with SharePoint.

Thanks to one of my readers, Alex, we can now solve this problem with a small piece of javascript. First set the Collapseattribute to FALSE and place your ListViewByQuery control in a DIV and give this DIV an ID. Immediately after the DIV  add the following javascript: 

The ListViewByQuery control loads with all groups expanded but when it is rendered to the page, the click method is executed on all hyperlinks that have a call to  ExpCollGroup in their href attribute.

SharePoint FBA – Adding users programmatically

Colleagues of mine have implemented Forms based Authentication (FBA) for a customer site. The customer was so “kind” to supply them with a Excel sheet containing 1200 usernames, email addresses and passwords. Every single user had to be imported into the FBA SQL database and certain SharePoint groups.
I decided to make a friendly web part which allows the user to select a *.csv file on their hard drive, and with a push on the button to import the users from the file.
Adding users to FBA went smooth, but adding those users to SharePoint groups threw Exceptions. Weird thing was, that even though I could not add the users through code, those users could be found in the People picker (and thus could be added through the user interface).
After adding users to FBA, I had to add them to SharePoint explicitly, before I could put them in SharePoint groups through code.
So the code for adding users to FBA, and adding the SharePoint groups should look like this:


MembershipUser existingUser = System.Web.Security.Membership.GetUser(“TheUserName”);
if (existingUser != null)
{
    Membership.DeleteUser(“TheUserName”, true);
}
MembershipCreateStatus createStatus;
Membership.CreateUser(“TheUserName”, “ThePassword”, “TheEmailAdress”, "FbaPasswordQuestion", "FbaPasswordAnswer", true, out createStatus);
if (createStatus == MembershipCreateStatus.Success)
{
    string userName = "aspnetsqlmembershipprovider:" + “TheUserName”;  //Don’t forget to prefix the username with the membership provider you defined
    web.SiteUsers.Add(userName, “EmailAdress”, “Name”, "Batchimport");
    SPUser user = web.SiteUsers[userName];
    if (user != null)
    {
            web.Groups["GroupName"].AddUser(user);
            web.Update();
    }
    else
    {
        throw new Exception("The user was added to FBA, but couldn't be added to SharePoint.");
    }
}
else
{
    throw new Exception("The user could not be added to FBA.");
}

Reduce Sharepoint Log file

SharePoint Config database logs are one thing to keep an eye on since they do have a tendency to grow. If you don’t perform a full farm backup usually the log doesn’t get emptied and it just keeps bloating.

If you’re running SQL Server Express with default installation, you can find the files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

As we see in the example above, even though the database is only 6 megs, the log file grew to 11 gigs. Let’s reduce that:

1. If you don’t have it yet, download and install SQL Server Management Studio express from here.

2. Run the Management Studio and connect to your SQL Server.

3. Expand “Databases” and select your config database “SharePoint_Config”.

4. Right Click it, select Tasks –> Shrink –> Files

5. In the new window select Release unused space and click OK.

If that doesn’t decrease the database size much, do the following:

1. First to be on the safe side, let’s back it up (this step is optional)

Select New Query

type the following:

BACKUP LOG [Sharepoint_Config] TO DISK=’D:\configLogBackup.bak’
GO

where SharePoint_Config is the name of your config database file and D:\configlogbackup.bak is the location and file name of where you’ll make the backup.

And click Execute

This may take a while if your log file is big.

2. Next clear the query (or click New Query again) and enter the following commands

BACKUP LOG [Sharepoint_Config] WITH TRUNCATE_ONLY
USE [SharePoint_Config]
GO

anc click Execute again

3. Clear the query or open another query tab and enter the next command:

DBCC SHRINKFILE (N’SharePoint_Config_log’ , 50) 
GO

The 50 in the command above sets the size in MB to truncate the log to. If your config db is of different name, replace the SharePoint_Config part above with your config db name.

And click Execute yet again.

The result you get should be something like below:

and ofcourse the file size:

NOW WE HAVE SOME SPACE! :)