Another Hack's SharePoint Experiences

Here's a way to get the server and database name of the content database that a site belongs to using C#:

using Microsoft.SharePoint;

using Microsoft.SharePoint.Administrator;

public class Test

{

private static SPGlobalAdmin oGlobAdmin = new SPGlobalAdmin();

public static SPVirtualServer GetVirtualServerBySite(SPSite oSite)
  { 
   foreach(SPVirtualServer iServer in oGlobAdmin.VirtualServers)
   {    
    foreach(SPSite iSite in iServer.Sites)
    {
     if(iSite.ID == oSite.ID)
     {
      return iServer;
     }
    }
   }
   throw new IndexOutOfRangeException("Unable to find site in configuration database: " + oSite.Url);
  }

public static void Main(String[] Args)

{

SPSite oSite = new SPSite([Your site's URL here]);

SPVirtualServer oVS = GetVirtualServerBySite(oSite);

string SPContentDBServer = oVS.ContentDatabases[0].Server;
string SPContentDB = oVS.ContentDatabases[0].Name;

Console.WriteLine(SPContentDBServer);

Console.WriteLine(SPContentDB);

}

}

Hopefully someone finds this the least bit interesting…Personally I'm trying to recreate the connection string for the SharePoint DB, but shhhhh, don't tell anyone.  Mucking w/ the SharePoint databases is not supported by MS.

Filed under: Uncategorized

Here's a way to get the server and database name of the content database that a site belongs to using C#:

using Microsoft.SharePoint;

using Microsoft.SharePoint.Administrator;

public class Test

{

private static SPGlobalAdmin oGlobAdmin = new SPGlobalAdmin();

public static SPVirtualServer GetVirtualServerBySite(SPSite oSite)
  { 
   foreach(SPVirtualServer iServer in oGlobAdmin.VirtualServers)
   {    
    foreach(SPSite iSite in iServer.Sites)
    {
     if(iSite.ID == oSite.ID)
     {
      return iServer;
     }
    }
   }
   throw new IndexOutOfRangeException("Unable to find site in configuration database: " + oSite.Url);
  }

public static void Main(String[] Args)

{

SPSite oSite = new SPSite([Your site's URL here]);

SPVirtualServer oVS = GetVirtualServerBySite(oSite);

string SPContentDBServer = oVS.ContentDatabases[0].Server;
string SPContentDB = oVS.ContentDatabases[0].Name;

Console.WriteLine(SPContentDBServer);

Console.WriteLine(SPContentDB);

}

}

Hopefully someone finds this the least bit interesting…Personally I'm trying to recreate the connection string for the SharePoint DB, but shhhhh, don't tell anyone.  Mucking w/ the SharePoint databases is not supported by MS.

Filed under: Uncategorized

Anyone ever notice a common practice with Microsoft: how they make something that is ALMOST useful, but stop right at the place where they are juuust at the cusp of something being great and just make it OK?  It's something that we notice here at my work…

Anyways, for just about the past 2 years I've been trying on-and-off to find a way to customize and automatically trigger alerts.  I've been told that is a bad idea, but unfortunately we need it.  Last month I attended SharePoint Experts SharePoint programming class, and since I've been tinkering a bit w/ Web Part coding, etc.  (Aside: It was a good class…you probably got more out of it if a) you are experienced with SharePoint and b) you go having an idea of what you want SharePoint to do, rather than an expectation of learning what SharePoint CAN do.  Imagination is helpful when it comes to programming web parts).

Since the class I've gotten a requirement to find a way to automatically create an alert for every creator of an item in a specific task list.  Evidently the people in my company are too stupid to actually check on the requests they make, and instead pepper others with status update questions.  One could make a point that this is really a training issue rather than a coding issue, but if every item does need an alert going to the creator, it would save time to programmatically do that rather than have everyone create an alert every time they make an item.

I've been playing with the SPAlert class, and let me tell you, it's a pain in the butt.  First of all, I'd like a function to find all the alerts on a given list or item.  Here's where that first rant plays in: Microsoft evidently thought that the only time someone might want to query the alerts is on a per user basis only.  Uh huh…  So I had to build a procedure to get the creator of the list item, which isn't as easy as you'd think.  I would have thought that since every item has a creator, there might be, oh I don't know, a Creator property on the SPItem class.  Nope.  You need to do this:

SPSite oSite = new SPSite("Your site URL");
SPList oList = oSite.OpenWeb().Lists["Your list name"];
SPListItem oItem = oList.GetItemById("Item ID");
string userValue = oItem["Created By"].ToString();
int index = userValue.Index Of(';');
int id = Int32.Parse(userValue.Substring(0, index));
SPUser itemCreator = oSite.OpenWeb().SiteUsers.GetByID(id);

When I finish my code (including SPs) I'll post it here if someones interested.  What I have yet to overcome is how exactly do I trigger the alert creation.  I'm considering writing a web control I can plug into create item page, but I don't know if I can both call the Submit form routine and the create alert stuff I'm making.  I'm also considering creating a service to check on new list items and adding the alerts on the back end.  I'm honestly not sure.  If someone has ideas on that, I'd love to hear them.

Anyways, it's good to be back after 2 years.  Hopefully I'll post w/ more frequency.

Tips I learned:
if you ever end up with a really long list [mine had 10000+ items] don't get an item by doing SPList[ItemID].  I ended up getting memory leaks and things died.  Use SPList.GetItemById(ItemID).  You'll be a happier person for it

Filed under: Uncategorized

Anyone ever notice a common practice with Microsoft: how they make something that is ALMOST useful, but stop right at the place where they are juuust at the cusp of something being great and just make it OK?  It's something that we notice here at my work…

Anyways, for just about the past 2 years I've been trying on-and-off to find a way to customize and automatically trigger alerts.  I've been told that is a bad idea, but unfortunately we need it.  Last month I attended SharePoint Experts SharePoint programming class, and since I've been tinkering a bit w/ Web Part coding, etc.  (Aside: It was a good class…you probably got more out of it if a) you are experienced with SharePoint and b) you go having an idea of what you want SharePoint to do, rather than an expectation of learning what SharePoint CAN do.  Imagination is helpful when it comes to programming web parts).

Since the class I've gotten a requirement to find a way to automatically create an alert for every creator of an item in a specific task list.  Evidently the people in my company are too stupid to actually check on the requests they make, and instead pepper others with status update questions.  One could make a point that this is really a training issue rather than a coding issue, but if every item does need an alert going to the creator, it would save time to programmatically do that rather than have everyone create an alert every time they make an item.

I've been playing with the SPAlert class, and let me tell you, it's a pain in the butt.  First of all, I'd like a function to find all the alerts on a given list or item.  Here's where that first rant plays in: Microsoft evidently thought that the only time someone might want to query the alerts is on a per user basis only.  Uh huh…  So I had to build a procedure to get the creator of the list item, which isn't as easy as you'd think.  I would have thought that since every item has a creator, there might be, oh I don't know, a Creator property on the SPItem class.  Nope.  You need to do this:

SPSite oSite = new SPSite("Your site URL");
SPList oList = oSite.OpenWeb().Lists["Your list name"];
SPListItem oItem = oList.GetItemById("Item ID");
string userValue = oItem["Created By"].ToString();
int index = userValue.Index Of(';');
int id = Int32.Parse(userValue.Substring(0, index));
SPUser itemCreator = oSite.OpenWeb().SiteUsers.GetByID(id);

When I finish my code (including SPs) I'll post it here if someones interested.  What I have yet to overcome is how exactly do I trigger the alert creation.  I'm considering writing a web control I can plug into create item page, but I don't know if I can both call the Submit form routine and the create alert stuff I'm making.  I'm also considering creating a service to check on new list items and adding the alerts on the back end.  I'm honestly not sure.  If someone has ideas on that, I'd love to hear them.

Anyways, it's good to be back after 2 years.  Hopefully I'll post w/ more frequency.

Tips I learned:
if you ever end up with a really long list [mine had 10000+ items] don't get an item by doing SPList[ItemID].  I ended up getting memory leaks and things died.  Use SPList.GetItemById(ItemID).  You'll be a happier person for it

Filed under: Uncategorized

Anyone out there have a reference to what the best practices for programming SharePoint are?  Specifically, how much normalization one should use in their database/lists?  Here's my scenario:

I'd like to create a Time Off Request and Reporting application. It would have the following operation:

• Employees would go to a form to request a period or day as PTO
• Managers would be alerted to the request and either approve or deny it (which would then update the employee
• When the scheduled day came, the manager would confirm that the employee did or didn't take the day off
• Payroll would be able to run a report of the data to see who took what time off.  Managers could run the same report for their departments.  Employees could not.

With a normal database application, I'd have 4 related tables: Employees, Departments, Administrators (but actually, this could be a boolean field for the employee table), and the  PTOData table.  However, I'm not sure if you can write all of these within the context of SharePoint lists.  Anyone have any ideas?  Should I just create separate tables, and only use SharePoint as a wrapper?  Let me know what you think!

BTW – Dustin – I asked my manager if they'd send me to the dev training.  If I can go, this is something I'd like to cover.  For those of you who also would like to go, but are afraid of asking your boss for a few grand, I can send you my request document (the sucker was 2 1/2 pages long!) as a template.

Filed under: Uncategorized

Anyone out there have a reference to what the best practices for programming SharePoint are?  Specifically, how much normalization one should use in their database/lists?  Here's my scenario:

I'd like to create a Time Off Request and Reporting application. It would have the following operation:

• Employees would go to a form to request a period or day as PTO
• Managers would be alerted to the request and either approve or deny it (which would then update the employee
• When the scheduled day came, the manager would confirm that the employee did or didn't take the day off
• Payroll would be able to run a report of the data to see who took what time off.  Managers could run the same report for their departments.  Employees could not.

With a normal database application, I'd have 4 related tables: Employees, Departments, Administrators (but actually, this could be a boolean field for the employee table), and the  PTOData table.  However, I'm not sure if you can write all of these within the context of SharePoint lists.  Anyone have any ideas?  Should I just create separate tables, and only use SharePoint as a wrapper?  Let me know what you think!

BTW – Dustin – I asked my manager if they'd send me to the dev training.  If I can go, this is something I'd like to cover.  For those of you who also would like to go, but are afraid of asking your boss for a few grand, I can send you my request document (the sucker was 2 1/2 pages long!) as a template.

Filed under: Uncategorized

I think I found a bug in using SMigrate to move a site – it's only a minor one.  I'm putting it here because I'm not sure where to submit bugs to Microsoft anymore, and I'm not willing to spend an hour to figure it out.

Here's the scenario: I used SMigrate to copy our production WSS site collection to some development servers.  It worked great, actually: the prod server was mis-configured to use a MSDE database rather than a Web Farm w/ SQL, but when I moved it to a server w/ that configuration, it moved the data into SQL just fine.  However, when I went to add a Web Part to the development server, it tried to connect to the old server's web part library.  I know this because it prompted me to log into the old server.

I'm not sure how to fix it quite yet, but I'm going to hack into the database in the next few days to figure out where it's storing the old server's name.  If I find anything, I'll update you.

Filed under: Uncategorized

I think I found a bug in using SMigrate to move a site – it's only a minor one.  I'm putting it here because I'm not sure where to submit bugs to Microsoft anymore, and I'm not willing to spend an hour to figure it out.

Here's the scenario: I used SMigrate to copy our production WSS site collection to some development servers.  It worked great, actually: the prod server was mis-configured to use a MSDE database rather than a Web Farm w/ SQL, but when I moved it to a server w/ that configuration, it moved the data into SQL just fine.  However, when I went to add a Web Part to the development server, it tried to connect to the old server's web part library.  I know this because it prompted me to log into the old server.

I'm not sure how to fix it quite yet, but I'm going to hack into the database in the next few days to figure out where it's storing the old server's name.  If I find anything, I'll update you.

Filed under: Uncategorized

I'm starting to feel pretty constricted by SharePoint.  There's a lot of little things that I'd like to customize that you just can't with SharePoint right out of the box.  For instance, I've gotten requests to make attachments open in a new window when you click on them.  In fact, most of my limitations seem to come from the inherent inflexibility of the out of the box web parts.  Yes, you can change some of the attributes.  However, in order to customize the display of the data, you need to convert the views from web parts to data views.  Of course, in a data view, you can't edit the data.  It's all very frustrating…I wish you could edit the default web parts…If you know a way, please let me know.

I'd like to be able to automatically create alerts for the people who actually submit an item to issue lists, rather than only send out an email only to the person the problem is assigned to.

Also, there aren't any books on using only SharePoint, at least none I could find.  The documentation is pretty sparse.  It seems like the primary method for learning SharePoint remains seminars like those put on by SharePoint Experts.  They are good classes, don't get me wrong, but they are few and far between.  Not only that, but the price for a book would be around $50 – $70, while classes are around $2000 w/o room and board.  You do the math…

It looks like in order to get SharePoint to do what we want it to do, we're going to have to write custom web parts and web pages.  That isn't too big of a problem, as that the SDK does in deed seem to have a lot of information in it.  I'm just ignorant of how to code specifically for SharePoint, and I have a pretty high learning curve to overcome to learn it.  Dustin, when's the next coding class?!

Enough ranting for me.  If you have any ideas how to correct or work around my problems, let me know, please!

Filed under: Uncategorized

I'm starting to feel pretty constricted by SharePoint.  There's a lot of little things that I'd like to customize that you just can't with SharePoint right out of the box.  For instance, I've gotten requests to make attachments open in a new window when you click on them.  In fact, most of my limitations seem to come from the inherent inflexibility of the out of the box web parts.  Yes, you can change some of the attributes.  However, in order to customize the display of the data, you need to convert the views from web parts to data views.  Of course, in a data view, you can't edit the data.  It's all very frustrating…I wish you could edit the default web parts…If you know a way, please let me know.

I'd like to be able to automatically create alerts for the people who actually submit an item to issue lists, rather than only send out an email only to the person the problem is assigned to.

Also, there aren't any books on using only SharePoint, at least none I could find.  The documentation is pretty sparse.  It seems like the primary method for learning SharePoint remains seminars like those put on by SharePoint Experts.  They are good classes, don't get me wrong, but they are few and far between.  Not only that, but the price for a book would be around $50 – $70, while classes are around $2000 w/o room and board.  You do the math…

It looks like in order to get SharePoint to do what we want it to do, we're going to have to write custom web parts and web pages.  That isn't too big of a problem, as that the SDK does in deed seem to have a lot of information in it.  I'm just ignorant of how to code specifically for SharePoint, and I have a pretty high learning curve to overcome to learn it.  Dustin, when's the next coding class?!

Enough ranting for me.  If you have any ideas how to correct or work around my problems, let me know, please!

Filed under: Uncategorized