ASP.net WebParts Vs SharePoint WebParts - Which one to choose?

And we are back! Well, it has been a very busy time for me over the last 8 months, anyhow moving on.

Well, this might be a question which should have popped-up in the head of any beginner in SharePoint probably with some .net background.

Well, an interesting point is that SharePoint 2007 is based on top on ASP.net 2.0 framework. Hence, for folks who are well versed with ASP.Net 2.0 and plan to move on to MOSS/SPS should definitely start with "HelloWorld" kind of webparts using System.Web.UI.WebControls.WebParts.WebPart as a reference.

This enables:

• Usage on websites developed on ASP.net 2.0 and also on SharePoint based portals
• Getting familiarized with Windows SharePoint Services 3.0 classes as a starting point

However, the hard truth remains that sooner or later, developers working on MOSS or SPS would need to move on to Microsoft.SharePoint.WebPartPages.WebPart in their code.

It obviously has it's own advantages:

• Enables cross-page connections
• Enables connections between webparts placed in different webpart zones on the same SharePoint page
• Helps in migrating webpart code to WSS 3.0 based webparts
• Utilize the SharePoint offerred data caching mechanism

Signing off here..enjoy coding!

SharePoint DateTime Format Conversions

SharePoint 2007 uses ISO8601 DateTime format internally. It understands either of 2 possible notations for DateTime:

YYYY-MM-DDThh:mmTZD or
YYYY-MM-DDThh:mm:ssTZD
where,

• understandably YYYY being 4-digit year, MM for 2-digit month number, DD for day of month
• T stands for start of time.
• Following that denotes the time which could be "hh:mm" (hours-mins) or "hh:mm:ss" (hours-mins-seconds)
• Z indicates the Coordinated Universal Time (UTC)

On many occassions during MOSS 2007 development, we are required to convert from DateTime to ISO8601 DateTime or vice-versa. So, I decided to post on how this could be achieved.

Convert from DateTime to ISO8601 DateTime (C#):

DateTime date = DateTime.Now; string isoDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(date);

When you are trying to insert or modify some DateTime fields to or from a SPListItem, you could use the code given above to format values into ISO 8601.
You can build the CAML query for the SPQuery object as shown below:
Example:

Note: Don't forget to add reference of Microsoft.SharePoint.Utilities in your class file before building your project.

Convert from ISO8601 to DateTime (C#):

DateTime date = DateTime.Parse("2010-06-15 00:00:00"); string sysDate = date.ToString("MM/dd/yyyy");

Note: In above example, the ISO DateTime string is converted to DateTime equivalent using DateTime.Parse method.

Also, incase you want to change the Site Collection date format:

1. Go to "Site Settings" & navigate to "Modify All Site Settings"
2. Click on Regional Settings under Site Administration
3. Change the time zone. Save the changes.

This brings us to the end of this lengthy post. Hope it helps you SharePoint folks, goodbye for now.

Find the IP Address of the SharePoint 2007 User

Suppose you have a SharePoint website that is being accessed by a lot of users everyday (~1K users daily).

Now for some reason or the other you want to capture the client IP address of the end user accessing your MOSS web application.

Purposes could be like:

• Reporting/Logging user IP address
• Security purposes
• Filtering content based on the client IP address
• Getting the location of the user based on the client IP address
• so on..

You could simply use HttpContext.Current.Request.UserHostAddress to get the IP address of a user.

This could be easily used in both ASP.Net applications as well as SharePoint development projects.

Difference between SPWeb.Users, SPWeb.AllUsers, SPWeb.SiteUsers

MOSS 2007 offers 3 different user collections in the SPWeb object.

• SPWeb.Users
  This represents the collection of users or user objects who have been explicitly assigned permissions in the Web site . This does not return users who have access through a group.
  
  Example (C#):
  SPUserCollection users = web.Users;
• SPWeb.AllUsers
  This gives us the collection of user objects who are either members of the site collection or who have atleast navigated to the site as authenticated members of a domain group in the site.
  
  Example (C#):
  SPUserCollection users = web.AllUsers;
• SPWeb.SiteUsers
  This is the collection of all users that belong to the site collection.
  
  Example (C#):
  SPUserCollection users = web.SiteUsers;

These terms tend to confuse us unless we really know the differences between each one of them.

So I thought it would be nice to write an article stating which is what. Hope this information helps you SharePoint 2007 developers.

Extracting data from Sharepoint Version table

When using SharePoint 2007, if we turn the Version Control settings to On for a List/Library, then different versions of the file or listitem get stored in the Content DB.

There are multiple ways to retrieve this information:

1. Using MOSS 2007 API
2. Via Versions.asmx - An OOB SharePoint Webservice
3. Or Querying the Docs & DocVersions table directly in using T-SQL statements

Now through SP API, you can try something like this -->
SPWeb web = new SPSite("your site url").OpenWeb();


SPList lib = web.Lists["your library"];


foreach (SPListItem item in lib.Items)


{


SPFile file=item.File


SPFileVersionCollection versions = file.Versions;


//Add your code to loop through the version collection & add custom logic here..


}


Coming to WebService approach:


SharePoint has a Versions.asmx web service to work with versions in document libraries.


For introduction, check out how to utilize this to get some information :
http://www.c-sharpcorner.com/UploadFile/klaus_salchner@hotmail.com/SharePointWS11152005045049AM/SharePointWS.aspx




Incase, you want to go ahead querying the SQL tables directly, then you could try a query as below:


SELECT DocVersions.version
FROM Docs INNER JOIN DocVersions ON DocVersions.Id = Docs.Id


Although it is not recommended that you query the SP DB directly. Any modifications or changes to the Content DB tables in unsupported.




Also the data is stored in UTF-8 Encoded format. After the query runs, it returns some data like this:
UIVersion




512
1024
1536
2048
512
1024
512


The above data is difficult to interpret in terms of Version numbers. Anyway, just to let you know


512 = version 1,
1024 =version 2,
1025 =version2.1,
2048= version 3,
1=version 0.1
and so on..


I guess the pattern is clear to you. Hope this article helps you.

Read an .xls file using OleDb in seconds

How often we have been involved with Microsoft's Office applications like MS Word, MS Excel in our day-to-day life. Every time we hear about importing/exporting the contents of an Excel file. I fortunately have been involved with both the tasks.

Here, I would like to show a fast way to read an excel file using OleDb. I have tried to make it compatible with excel 2003(.xls) as well as 2007(.xlsx) formats.

Add these references at the top of the class file:-

using System; using System.Text; using System.Data; using System.Data.OleDb;

Create a method, name it ReadExcelContents and pass the fileName as a parameter to that function.

public static DataTable ReadExcelContents(string fileName) { try { OleDbConnection connection = new OleDbConnection(); if (fileName.ToLower().IndexOf(".xlsx") != -1)//Checking source file for Excel 2007 { if (CheckOfficeVersion.ExcelVersion() == 2007)//Checking if Excel 2007 is installed or not { connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="+ fileName); //Excel 2007, .xlsx } else//if Excel 2003 or other are installed { MessageBox.Show("Sorry! You do not have Excel 2007 installed on your machine.", "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="+ metadataFile); //Excel 97-2003, .xls } string excelQuery = @"Select [Name], [Address], [Age], [Occupation], [Degree] FROM [Sheet1$] where LTRIM(RTRIM([Name]))<>'' & LTRIM(RTRIM([Degree]))<>'' connection.Open(); OleDbCommand cmd = new OleDbCommand(excelQuery, connection); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; DataSet ds = new DataSet(); adapter.Fill(ds); DataTable dt = ds.Tables[0]; connection.Close(); return dt; } catch(Exception ex) { MessageBox.Show("Sorry! "+ex.Message, "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } }

Explanation:

• 
  
  At first, we are opening an OleDb connection, checking the file extenstion to see if the selected file is having an Excel 2007(.xlsx) format. If this is the case, we are checking the system HKLM(Local machine level) registry to see if Excel 2007 is even installed on the client system or not?
• 
  
  If present we are setting the connectionString properties according to Excel 2007 where
  Provider: Microsoft.ACE.OLEDB.12.0 andExtended Properties: Excel 12.0Otherwise, if the user has excel 2003 & has selected an Excel 2007 file, we are throwing an error message, else, we know that the user has selected an Excel 2003 file, so we set the Provider: Microsoft.Jet.OLEDB.4.0 and Extended Properties: Excel 8.0
• 
  
  Alongwith this we need to pass the filename as the DataSource.Now that you must have understood that it is very similar to Ado.net, our next step should be to write the query. In the query we need to pass the ColumnNames same as the ones present in xl column headers. You can provide all clauses such as where, having, etc.
• 
  
  Rest is straight forward for any .Net developer as to opening a connection, setting up a CommandObject with the excelQuery & connectionString. Create an OleDbAdapter object and set its command property. Execute the adapter.Fill(ds) to put the contents read in a DataSet.
• 
  
  Close the connection after reading is over so that there are no fileOpen conflicts and return the datatable.

If you felt that this post has been of help to you, please drop in a small note. It would be a big encouragement for me to write more.