Use CAML when searching SharePoint

CAML is worth the effort. I went from this (powershell):

$listitem = $list.Items | Where { ($_.Title -eq $row["Title"]) -and ($_.Name -eq $row["Name"]) }

To this:

$query = New-Object Microsoft.SharePoint.SPQuery 
$caml = '<where><and><eq><fieldref name="Title"><value type="Text">' + $row["Title"] + '</value></fieldref></eq><eq><fieldref name="Name"><value type="Text">' + $row["Name"] + '</value></fieldref></eq></and></where>'
$query.Query = $caml 
$listitem = $list.GetItems($query)[0]

And the code in question is now about 60 times faster. I’m guessing that the first line is scanning every item until it finds the one it wants, while the CAML query somehow avoids that. Big improvement. 

Powershell tip 1: get-member

Get-Member will tell you all the properties and methods of an object.

So,

“this is a string” | Get-Member will return all the string options like split, join, whatever.

Get-SPContentDatabase | Get-Member on a sharepoint server will show you lots of methods you can run on the db, like “DiskSizeRequired” and “Server” to get information about the db.  

So if you have something in powershell and don’t know what to do with it, try piping it to get-member. 

ASP.NET without Visual Studio

Sometimes you just don't need a project. You're writing a report or something really low rent and you just need to get a page out there quickly. Well you're in luck, inline declarations can be used to obviate the need for a project, references, etc.

You can have these standalone in any IIS web directory or you can make them into an app by putting them in a folder, adding a web.config, declaring an app pool in IIS admin, etc.

You can mix HTML and ASP tags as needed, or generate elements in code (as I've done below).

Your first line needs to declare a page and language used:
<%@ Page Language="VB" Debug="true" %>

You can import libraries from the GAC by using Import
<%@ Import Namespace="System.Text" %>

You can import libraries from outside the GAC using Register Assembly and then Import
<%@ Register Assembly="System.DirectoryServices, Version=2.0.50727.3053, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="System.DirectoryServices" TagPrefix="SD" %>
<%@ Import Namespace="System.DirectoryServices" %>

VB.NET uses a Page_Init function to initialize your web app.
<script runat="server">

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)
 
    ' POST takes the usr and pwd fields and authenticates
    If Request.Form.Count > 0 Then
      AuthEntry(Request.Form.Item("usr"), Request.Form.Item("pwd"))
    End If
 
    ' no parameters will generate a login form
    If Request.QueryString.Count = 0 And Request.Form.Count = 0 Then
      GenerateForm
    End If
End Sub

' you can generate form elements using code or do more traditional ASP layout below the script tag
Protected Sub GenerateForm()
  Dim Form1 As System.Web.UI.HtmlControls.HtmlForm
  Dim Label1 As System.Web.UI.WebControls.Label
  Dim usr As System.Web.UI.WebControls.TextBox
  Dim pwd As System.Web.UI.WebControls.TextBox
  Dim btn1 As System.Web.UI.WebControls.Button

  Form1 = New HtmlForm()
  Form1.ID = "myForm"

  usr = New TextBox()
  usr.ID = "usr"
  Form1.Controls.Add(usr)

  pwd = New TextBox()
  pwd.ID = "pwd"
  pwd.TextMode = TextBoxMode.Password
  Form1.Controls.Add(pwd)

  btn1 = new Button()
  btn1.Text = "Login"
  Form1.Controls.Add(btn1)

  Page.Controls.Add(Form1)

End Sub

' Here is a function that will validate a user against Active Directory
' You are essentially performing an authenticated search for your own account.
Protected Sub AuthEntry(usr as String, pwd as String)

  Dim path as String = "LDAP://your.domain.com/CN=Users,DC=your,DC=domain,DC=com"
  Dim entry as DirectoryEntry = new DirectoryEntry(path,usr,pwd,AuthenticationTypes.Secure)

  Try
    Dim obj as Object = entry.NativeObject
    Dim search as DirectorySearcher = new DirectorySearcher(entry)
    search.Filter = "(SAMAccountName=" + usr + ")"
    search.PropertiesToLoad.Add("samaccountname")
    Dim result As SearchResultCollection = search.FindAll()
    If result.Count > 0 Then
      ' authenticated
      Response.Write("Success!")
      Exit Sub
    End If
  Catch ex As Exception
    Response.Write(ex.Message)
    Exit Sub
  End Try

End Sub

</script>

Basic sharepoint web part in .NET

1     Created a new Windows Class Library project in VS2008

2     Added a reference to System.Web

3     Added the following ‘using’ statements:

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

4     Changed the default class from public class Class1 to public class Hello: WebPart

5     Wrote an override for the “RenderContents” function (I got fancy and stripped off the leading domain portion of the login, and later wrote more code to change the font and add the little % bar)

protected override void RenderContents(HtmlTextWriter writer)

{

 string name = this.Context.User.Identity.Name;

 writer.Write(" You are logged in as " + name + "! ");

}

6     Built the dll in release mode and put it in a shared dir on my machine.

7     Logged onto the mossdev server and copied the dll to the sharepoint server into the web server’s ‘bin’ directory (\Inetpub\wwwroot\wss\VirtualDirectories\80\bin)

8     Added a line to web.config to make the server trust the dll (\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config). In the section of web.config, I added a entity that looked like this:

9     Went onto sharepoint, went to site settings (for the whole site), and chose ‘Web Parts’ under Galleries. Clicked New, found my ‘Hello’ part, and clicked ‘Populate Gallery’ which added it for use on the site.

1    Went to a site and added the part to my front page. Opah!

automating silent MSI installs

I had to dig a bit to figure this out, and I know I've done this before so I'm making a note here so I can find this information again the next time I forget about it.

To automate a standard MSI install, run it once and log all the properties to a file:
msiexec /i "My Program Installer.msi" /Lp options.log

Make a note of anything you change during this first run. Then take a look at your options.log file. It should have a lot of items like this:

Property(C): ProgramFilesFolder = C:\Program Files\
Property(C): SourceDir = E:\
Property(C): VersionNT = 501
Property(C): ALLUSERS = 1
Property(C): INSTALLLOCATION = C:\Program Files\Whoever\Whatever
Property(C): Manufacturer = Whoever


You can run a silent install by setting these properties on the command line. The ones in all caps are "public" so they can be set by msiexec:

msiexec /qn /i "My Program Installer.msi" INSTALLLOCATION="C:\MyFolder\Whatever"

That's all there is to it! Find the properties you want, pass them on the command line. I have no idea why I can't seem to remember the steps to do that.

Going Meta

Lately I've been using some of the same tricks over and over again in different contexts. Essentially I've just been doing a lot of complex queries, where I select a set of records first, and then select other data out of the first set:

select my_set.X, my_set.Y, my_set.Z
from (
select * from whatever_it_is
where my_item = 'something'
) my_set


Why would I need to do this? Well, for one thing it breaks a problem down into smaller bits, so it is easier to work on. I can write and validate a query that gets all the records I might want to work with, and then write two or three queries that use it to produce friendlier output. I can also use it to do things like counts:

select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X

This will produce something that looks like this:

Item	Num_Chicago	Num_Phoenix	Total
Item 1	4	1	5
Item 2	2	2	4

You can do grand totals using a UNION ALL with another query:

select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X
UNION ALL
select 'Totals',
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set

The key to doing a UNION is you need your columns to match up. So note that I have the same number of columns (3) and I used the same names (Item, Num_Chicago, Num_Phoenix, Total) to describe them. By not including my_set.X in the second query I end up with total counts of all the rows, not simply of one item. The output from this should look like:

Item	Num_Chicago	Num_Phoenix	Total
Item 1	4	1	5
Item 2	2	2	4
Totals	6	3	9

I've used the same sort of complex queries to pull the earliest or latest item by category from a set of records; it is a fairly simple concept but can be very powerful.

SQL*Plus formatting and automation

Here is some advice on how to run your SQL and generate a file with the delimiter of your choice.

1. Login to sqlplus
   
2. Set your page size (how often you get headers – so set this to be arbitrarily large), line size (try to make this long enough to hold a complete row of data), column separator, and suppress the “X rows selected” feedback.
   set pagesize 5000 linesize 500 colsep ',' feedback off
   
3. Setup an output file
   spool output.txt
   
4. Run your sql command
   select ... from ... where ... ;
   
5. Turn off the output
   spool off
   At this point you should have a file called output.txt with your data. Don’t worry if you have some command data in there, just read on.
   
6. You might also read up on how to format columns – in particular you can have nicer column headings:
   http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm
   

Once you have tweaked these settings to your liking, you can automate the process pretty easily. Put your commands into a file. To suppress output on screen you can set termout off (this only works with a command file):

set pagesize 5000 linesize 500 colsep ',' feedback off termout off
spool output.txt
select whatever;
spool off
quit

Then run sqlplus with that file as an argument (the -S will run the commands silently):

sqlplus -S login/pwd@server @commands.txt

An added bonus of doing it this way is you won’t see the sql commands in your output file anymore.

This is particularly handy if you need to do some scripting in an environment where you don't have control over what libraries are installed; I recently used something similar on a unix machine that had Perl installed but not DBD::Oracle. I shelled out and ran my query using sqlplus, and parsed the output in the script. It won't perform as well as a built-in, but for occasional use it is very low rent.