Archive

Archive for October, 2006

Select Top 10 records from a DataTable in ADO.NET

October 30th, 2006 No comments

As far as I know, there is no way to perform a SELECT TOP 10 query on a disconnected Recordset in ADO.NET. However, you can create a DataView over the target table and use the technique described below to extract Top 10 records.  


As you can see in the below excerpt, I am trying to return Top 10 records of my ds dataset using a method called “GetTopTen”. First of all, I have assigned the defaultView of the only DataTable in input dataset named “SITE_DT” to a DataView object and then have sorted the result based on “Total_Size” field. This guarantees that our result (no matter how many records is returned) are always sorted in a descending manner.


Next, I have cloned the schema of first DateTable to another DataTable and added the new DataTable to a completely new DataSet. ADO.NET normally does not let you add a DataTable (neither a DataRow) directly from one dataset to another, so you have to clone it in a way. Finally I’ve just grunt it out the old-fashioned way by looping through each row of the DataView object and adding them to a new DataTable. My method ends by returning the new dataset which contains the Top 10 records based on “Total_Size”. This might not be the best option for you, but perfectly works in my case.



public DataSet GetTopTen(DataSet ds)


{
    Try
   {
    DataView dwDs= ds.Tables[“SITE_DT”].DefaultView;
    dwDs.Sort=”Total_Size DESC”;
 
    DataSet dss  = new DataSet();
    DataTable dt = dwDs.Table.Clone();
    dss.Tables.Add(dt);


    int counter = 0;


    if(dwDs.Count > 10)    
     
     counter = 10;     
    else
     counter = dwDs.Count;


    
    for (int i=0; i < counter; i++)
      {
         DataRow dv = dwDs[i].Row;
         dss.Tables[0].ImportRow(dv);
      }


    dss.AcceptChanges();
    return(dss);


   }
           
   catch(Exception e)
   {
    throw e;
   }


}

Categories: Uncategorized Tags:

How to get the size of attachments in list items using SharePoint object model

October 24th, 2006 No comments

I have recently written a report generator for a client that enumerates all the items in WSS lists of a web site and then attempts to count them and calculate the overall size. Unlike many reporting tools out there which only go through list items in document libraries and ultimately through document libraries + picture libraries, my reporting tool, iterate through all the lists of any kind. It goes through all the attachments in attachment-enabled lists like links, Announsment, Custom lists,… and literally counts and calculates the size of not only the actual file, but attachments or items in the catalogs like WebTemplateCatalog, WebPartCatalog or ListTemplateCatalog. Unfortunately SharePoint 2003 does not provide a direct access to the actual files in attachments through SPAttachmentCollection and instead it only returns the URL of the attachments, so I had to use my own trick to loop in each indivitual attachments as described below:



    1  foreach(SPListItem listitem in list.Items )


    2  {


    3     SPAttachmentCollection attachmentCol = listitem.Attachments;


    4     totalFiles+= attachmentCol.Count;


    5     for(int p=0;p < attachmentCol.Count ; p++)


    6     {


    7         fileName = attachmentCol[p];


    8         fileUrl  = attachmentCol.UrlPrefix + fileName;


    9         SPFile attachFile = web.GetFile(fileUrl);


    10         toalSize += attachFile.Length;


    11     }


    12  }


 



If you run the code above in a context of a web part or a web part page , you will soon get trapped with “Object reference not set to an instance of an object”. why? Some lists like survey does not have attachments at all and the code crashes on 3rd line.


To solve this problem, you should treat each list (based on its BaseType) differently than the rest. Here is the complete method to get the count and size of all files of a site.It has worked fine so far:



private void ReportInvidualWeb(SPWeb web)
  {
   string fileName=””,fileUrl=””;
   int totalFiles=0, toalSize=0;
   try
   {
  SPListCollection listcol = web.Lists;
  foreach(SPList list in listcol )
  {
   switch (list.BaseType )
   {
   case SPBaseType.DocumentLibrary:
    foreach(SPListItem listitem in list.Items )
    {  


     totalFiles++;      
     toalSize += listitem.File.Length;
    }
    break;


   case SPBaseType.DiscussionBoard:
   case SPBaseType.Issue:
   case SPBaseType.GenericList:
       
    if(list.EnableAttachments)
    {
     foreach(SPListItem listitem in list.Items )
     {    
      SPAttachmentCollection attachmentCol = listitem.Attachments;
      totalFiles+= attachmentCol.Count;
      for(int p=0;p < attachmentCol.Count ; p++)
      {
       fileName = attachmentCol[p];
       fileUrl  = attachmentCol.UrlPrefix + fileName;
       SPFile attachFile = web.GetFile(fileUrl);
       toalSize += attachFile.Length;
      }
     }
    }
    break;


   case SPBaseType.Survey:
   case SPBaseType.UnspecifiedBaseType:
   default:
    //Take the appropriate action
    break;     


   }
  }


   }
   catch(Exception e)
   {
  throw e;
   }
   finally
   {
  //save memory, close the web               
  web.Close();               
   }
  }

Categories: Uncategorized Tags:

Customizing Task Pane (Pain!!) in Office 2003

October 13th, 2006 No comments

I have recently done an extensive research on the possibility of tweaking “Shared Workspace”  task pane for SharePoint and came to conclusion that there is no way of modifying it. I have two reasons to support it :


1) SharePoint 2003 doesn’t have “hide features” in its design model. Everything is shown to everyone ,but actions can only be completed by people who have right permissions. Having said this , I think any mechanism to hide features, on the server side, are not seen in SharePoint 2003 architecture. I went through all the registry keys related to office 11.0 in the “HKEY_CURRENT_USERSoftwareMicrosoftOffice11.0” and “HKEY_LOCAL_MACHINESoftwareMicrosoftOffice11.0” and I could not find any keys in regards to Disabling/Enabling “Shared Document” workspace features.


2) According to my research on the client side, all office applications (like word, Excel and etc) provide a customizable mechanism to end users to customize their environments, but they DO NOT really provide facilities for administrators to lock things down.The “Shared Workspace” pane is not programmable which means that there is not such a real programmability model to override the task pane behavior.


We might be able to write Macros or leverage Policies (as stated in Office Resource Kit) to disable a task pane completely ,but who wants to take this great collaboration feature away from office ?


Look at this answer by Mike Hernandez who is the program manager of VSTO (Visual Studion Tools for Office) Team:


http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=197143&SiteID=1

Categories: Uncategorized Tags: