Ok, this one is fairly basic... I'm just putting it here because I use this bit of code really, really often.
SPWeb mySite = SPContext.Current.Web;
SPListItemCollection listItems = mySite.Lists["NameOfList"].Items;
int countResults = 0;
for (int i = 0; i < listItems.Count; i++)
{
SPListItem item = listItems[i];
writer.Write(item["Title"].ToString()+"<br />");
}
Nice and simple...
Most discussions on data grids were for ASP.net and not in web parts and that annoyed me... so here's a few things you need to know about datagrids in web parts in SharePoint.
Firstly, the datagrid needs to be bound in CreateChildControls.
dgRecords = new DataGrid();
dgRecords.EnableViewState = true;
dgRecords.Width = new Unit("100%");
dgRecords.AutoGenerateColumns = false;
dgRecords.BorderStyle = BorderStyle.None;
dgRecords.FooterStyle.BorderStyle = BorderStyle.None;
dgRecords.HeaderStyle.CssClass = "ms-formlabel";
dgRecords.HeaderStyle.Font.Size = FontUnit.XXSmall;
dgRecords.CellPadding = 3;
dgRecords.ItemStyle.Height = 30;
dgRecords.AllowPaging = true;
dgRecords.PageSize = 25; // results returned per page
dgRecords.PagerStyle.Mode = PagerMode.NumericPages;
dgRecords.PagerStyle.HorizontalAlign = HorizontalAlign.Center;
dgRecords.PageIndexChanged += new DataGridPageChangedEventHandler(dgRecords_PageIndexChanged);
this.Controls.Add(dgRecords);
OpenSQLConnection();
SqlCommand cmd = getSqlCommand();
cmd.CommandType = CommandType.Text;
SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
dsRecords = new DataSet();
myAdapter.Fill(dsRecords, "TableName");
dv = new DataView(dsRecords.Tables["TableName"]);
HyperLinkColumn linkColumn = new HyperLinkColumn();
linkColumn.DataTextField = "fldID";
linkColumn.DataTextFormatString = "{0}";
linkColumn.DataNavigateUrlField = "fldID";
linkColumn.DataNavigateUrlFormatString = "http://your_intranet/Pages/default.aspx?ticketid={0}";
linkColumn.HeaderText = "ID#";
BoundColumn itemColumn = new BoundColumn();
itemColumn.DataField = "fldItem";
itemColumn.DataFormatString = "{0}";
itemColumn.HeaderText = "Item Ordered";
BoundColumn priceColumn = new BoundColumn();
priceColumn.DataField = "fldPrice";
priceColumn.DataFormatString = "{0}";
priceColumn.HeaderText = "Price";
BoundColumn quantityColumn = new BoundColumn();
quantityColumn.DataField = "fldQuantity";
quantityColumn.DataFormatString = "{0}";
quantityColumn.HeaderText = "Quantity Ordered";
TemplateColumn totalColumn = new TemplateColumn();
totalColumn .ItemTemplate = new TotalColumnTemplate();
dgRecords.Columns.Add(linkColumn);
dgRecords.Columns.Add(itemColumn);
dgRecords.Columns.Add(priceColumn);
dgRecords.Columns.Add(quantityColumn);
dgRecords.Columns.Add(totalColumn);
dgRecords.HeaderStyle.BackColor = Color.FromArgb(198, 226, 255);
dgRecords.HeaderStyle.ForeColor = Color.White;
dgRecords.AlternatingItemStyle.BackColor = Color.FromArgb(247, 246, 243);
dgRecords.AlternatingItemStyle.ForeColor = Color.FromArgb(33, 33, 33);
dgRecords.ItemStyle.ForeColor = Color.FromArgb(28, 47, 75);
dgRecords.DataSource = dv;
// bind only on page load
if (!Page.IsPostBack) dgRecords.DataBind();
CloseSQLConnection();
Notice that in the code above, you are using BoundColumn a lot. This is all ok and well if you are just querying the database and retrieving the values stored in the database without needing to make any calculations on returned values. If you do need to make calculations (for example, price x quantity, then you need a TemplateColumn), which complicates things a little.
Also note that we are only binding this if it isn't a postback. The reason for this is because we need to bind it to different "data" when an event happens such as column sorting or a page change. I will go through the paginations a bit later, but won't go through the column sorting until another post in the future.
So, now if you copy and paste the above code, you will find that you don't have methods for OpenSQLConnection(), getSqlCommand(), TotalColumnTemplate() and CloseSQLConnection(). The code for all but the TotalColumnTemplate() is below:
private void OpenSQLConnection()
{
connection = new SqlConnection("server=localhost;database=hwsupport;Integrated Security=SSPI");
connection.Open();
}
private void CloseSQLConnection()
{
connection.Close();
}
private SqlCommand getSqlCommand()
{
SPWeb mySite = SPContext.Current.Web;
SqlCommand cmd = new SqlCommand("SELECT * FROM TableName", connection);
return cmd;
}
For TotalColumnTemplate(), we need to create a new class. Right click on your project and add a class file. Name it TotalColumnTemplate.cs since that is what this template is referred to as in the code. Copy the following into TotalColumnTemplate.cs through VS:
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Demo
{
class LightColumnTemplate : ITemplate
{
public void InstantiateIn(Control container)
{
LiteralControl ouputLiteral = new LiteralControl();
ouputLiteral.DataBinding += new EventHandler(myLiteral_DataBinding);
container.Controls.Add(ouputLiteral);
}
public void myLiteral_DataBinding(object sender, EventArgs e)
{
LiteralControl myLiteral = ((LiteralControl)(sender));
DataGridItem container = ((DataGridItem)(myLiteral.NamingContainer));
int Price = int.Parse(DataBinder.Eval((((DataGridItem)(container))).DataItem, "fldPrice").ToString());
int Quantity = int.Parse(DataBinder.Eval((((DataGridItem)(container))).DataItem, "fldQuantity").ToString());
string strExactOutput;
strExactOutput = Convert.ToString(Price*Quantity);
myLiteral.Text = strExactOutput;
}
}
}
Make sure you set the namespace to the same namespace that your base class is using.
If you deploy your project now, you will see a data grid with the values in it.
Let's assume now that you have thousands of results returned and you need to paginate.
In the first chunk of code, you'll see this:
dgRecords.AllowPaging = true;
dgRecords.PageSize = 25; // results returned per page
dgRecords.PagerStyle.Mode = PagerMode.NumericPages;
dgRecords.PagerStyle.HorizontalAlign = HorizontalAlign.Center;
dgRecords.PageIndexChanged += new DataGridPageChangedEventHandler(dgRecords_PageIndexChanged);
This bit tells the datagrid that you want to enable paging and to run the dgRecords_PageIndexChanged event when a new page is selected.
void dgRecords_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
OpenSQLConnection();
SqlCommand cmd = getSqlCommand();
cmd.CommandType = CommandType.Text;
SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
dsRecords = new DataSet();
myAdapter.Fill(dsRecords, "TableName");
dgRecords.DataSource = dsRecords.Tables["TableName"];
DataView dv = new DataView(dsRecords.Tables["TableName"]);
try
{
dgRecords.CurrentPageIndex = e.NewPageIndex;
}
catch
{
dgRecords.CurrentPageIndex = 0;
}
dgRecords.DataBind();
CloseSQLConnection();
}
Note that the dgRecords.DataBind() needs to be run in here to ensure that the rows returned for this page is all that is displayed. If you don't bind the data, the results will not be correct.
I remember that while I was doing this, changing pages worked, but when I go back to Page 1, nothing seems to happen. The reason is because of dgRecords.DataBind(); that I had placed in the CreateChildControls override. I had it such that it was binding when a new page is selected. It should have been if (!Page.IsPostBack) dgRecords.DataBind();. It then needs to be bound in the PageIndexChanged event handler (this will apply for column sorting too).
I wasted a few hours trying to work out why I can't go back to Page 1, so hopefully this will save you the few hours I wasted trying to figure this out.
This wasted me a few hours too trying to figure out how to alternate row colours in a data grid.
In the end it was fairly simple... just look at the following code:
dgRecords.HeaderStyle.BackColor = Color.FromArgb(198, 226, 255);
dgRecords.HeaderStyle.ForeColor = Color.White;
dgRecords.AlternatingItemStyle.BackColor = Color.FromArgb(247, 246, 243);
dgRecords.AlternatingItemStyle.ForeColor = Color.FromArgb(33, 33, 33);
dgRecords.ItemStyle.ForeColor = Color.FromArgb(28, 47, 75);
You can set alternating item styles background colors and foreground colors from RGB or from a predefined color. This one was really obvious, I don't know why it took me so long to figure out. Maybe I just didn't get enough sleep that day.
In my last post, I discussed using static global variables so the variable maintains it's value even when a browser is closed. I will be using that here, referring to that global variable as a separate class (all code is provided there).
Don't forget that you will need to add a web service since the only way I know how to get all users is through this web service.
If you're using Visual Studio 2005, right click on References and click add Web Reference
If you're using Visual Studio 2008, right click on References, and click add Service Reference, then click Advanced, then click Add Web Reference.
The web service url is http://your_intranet_name/_vti_bin/UserProfileService.asmx
I usually set the Web reference name as "UserProfileService", though I'm not 100% sure if I should be doing that since it could get confusing.
First thing, set your variables:
#region Variables
UserProfileService.UserProfileService UserProfileServiceWS = new UserProfileService.UserProfileService();
private const string UserProfileServiceURL = "/_vti_bin/UserProfileService.asmx";
#endregion
Now I check the static global variable to see if it has been set or if it has expired.
if (GlobalVariable.ExpirationTime != null)That code above will set the AllUsers variable to null because the variable has expired. Then the function below (which includes the code above) will check if AllUsers is null (which is true if the variable is not populated) and then populate the variable accordingly.
{
if (DateTime.Now.CompareTo(GlobalVariable.ExpirationTime) != -1) // if current time is after expiration time
{
GlobalVariable.AllUsers = null; // reset user list so it is then re-populated
}
}
public string RetrieveUsers()
{
string userList = "";
if (GlobalVariable.ExpirationTime != null)
{
if (DateTime.Now.CompareTo(GlobalVariable.ExpirationTime) != -1) // if current time is after expiration time
{
GlobalVariable.AllUsers = null; // reset user list so it is then re-populated
}
}
if (GlobalVariable.AllUsers == null)
{
// set the expiration time to be the next day
GlobalVariable.ExpirationTime = DateTime.Today.AddDays(1);
try
{
//SPSite site = new SPSite(SiteURL);
SPWeb web = SPContext.Current.Web;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite elevatedSite = new SPSite(web.Site.ID))
{
ServerContext context = ServerContext.GetContext(elevatedSite);
UserProfileManager profileManager = new UserProfileManager(context);
PeopleList pl = new PeopleList();
DateTime Today = DateTime.Now.Date;
UserProfileServiceWS.Credentials = CredentialCache.DefaultCredentials;
UserProfileServiceWS.Url = web.Url + UserProfileServiceURL;
foreach (UserProfile profile in profileManager) // cycle through all profiles
{
userList += "|" + profile["AccountName"].ToString();
}
GlobalVariable.AllUsers = userList;
}
});
}
catch (Exception ex)
{
throw ex; // or handle the exception any way you like.
}
}
else
{
userList = GlobalVariable.AllUsers.ToString();
}
return userList;
}
So you see, the code is fairly simple... if the global variable named AllUsers is null, then go and retrieve all users and place it in a single comma delimited string. If it is not null, then just return the userList since it already contains all the users in it.
Once you have the userList, you can turn this string into an array and handle it just like any other array.
string[] result = userList.Split(new Char[] { ',' });
foreach (string user in result)
{
// you have the username, now you can get any of their properties (described here)
}
Don't know if you'll find this useful, but I did... especially when you're doing something that you don't want to do again everytime the web part loads. For example, when you want all the users in a drop down list, but don't want to keep querying the entire user list each time the web part opens.
This will definitely help with performance since the entire user list is in a single variable that can be queried across all sharepoint users after it loads just once by any single user.
The code below is for the entire class. I posted the whole thing because it was nice and short
using System;
using System.Collections.Generic;
using System.Text;
namespace UserList
{
public class GlobalVariable
{
public static string AllUsers;
public static DateTime ExpirationTime;
}
}
I placed a global expiration time above so I can check when to actually refresh the global variables list. I usually set it to expire at midnight, so the first person in the next day will be the one to populate this variable with all the users (for everyone's use) again.
This one I figured out recently. I have always used the UserProfileService.asmx to get the profile properties of users. Even wrote a little function to return the number based on a profile property (so it would work with custom properties). Now, I don't need to do that anymore.
SPWeb mySite = SPContext.Current.Web;
SPSite site = new SPSite(mySite.Url.ToString());
ServerContext context = ServerContext.GetContext(site);
UserProfileManager profileManager = new UserProfileManager(context);
UserProfile prof = profileManager.GetUserProfile(mySite.CurrentUser.ToString());
string PreferredName = (prof[PropertyConstants.PreferredName].Value == null) ? "" : prof[PropertyConstants.PreferredName].Value.ToString();
string PictureUrl = (prof[PropertyConstants.PictureUrl].Value == null) ? DefaultImageUrl : prof[PropertyConstants.PictureUrl].Value.ToString();
You can request all the properties you want using the PropertyConstants method which uses the Microsoft.Office.Server and Microsoft.Office.Server.UserProfiles class.
That is definitely handy... The question now is, is it possible to query all the sharepoint users and place them in a drop down list so you can retrieve details of a particular user (from the drop down list) without using a web service? If it's possible, I haven't figured out how. I only know how to do that by querying the UserProfileService.asmx web service, which I will discuss in my next post.