Oct 21 2008

Retrieve All Documents from SharePoint

I needed a way to quickly retrieve all documents from SharePoint. You can specify which files to retrieve by changing the WHERE clause in the SQL query.  Otherwise, just pass it a path for the output files and set the connection string for your SharePoint content database.

Props to Michael O’Donovan for his helpful article on recovering SharePoint files.

public static void GetDocs(string path)
{
    SqlConnection _conn = new SqlConnection(Settings.Default.conn);
 
    string sql = "SELECT AllDocs.LeafName, AllDocs.DirName, AllDocStreams.[Content] ";
    sql += "FROM AllDocs INNER JOIN AllDocStreams ON AllDocStreams.Id = AllDocs.Id AND AllDocs.[Level] = AllDocStreams.[Level] ";
    sql += "WHERE AllDocs.IsCurrentVersion = 1 AND right(AllDocs.LeafName, 4) in ('.doc','.pdf','.xls','.csv','.ppt','docx','xlsx','pptx','.rtf','.txt','.xps','.mdb','.msg','.wmv','.xsn','.vsd')";
 
    SqlCommand getEmp = new SqlCommand(sql, _conn);
 
    FileStream fs;                          // Writes the BLOB to a file (*.bmp).
    BinaryWriter bw;                        // Streams the BLOB to the FileStream object.
    int bufferSize = 100;                   // Size of the BLOB buffer.
    byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
    long retval;                            // The bytes returned from GetBytes.
    long startIndex = 0;                    // The starting position in the BLOB output.
    string filename = string.Empty;         // The orignial filename.
    string outfile = string.Empty;
    string subpath = string.Empty;
    int i = 0;
 
    // Open the connection and read data into the DataReader.
    _conn.Open();
    SqlDataReader myReader = getEmp.ExecuteReader(CommandBehavior.SequentialAccess);
 
    while (myReader.Read())
    {
        i++;
 
        // Get the filename and build the file path for the output files.
        filename = myReader.GetString(0);
        subpath = myReader.GetString(1).Replace("/", "\\") + "\\";
        outfile = path + subpath + filename;
 
        if (!Directory.Exists(path + subpath))
        {
            Directory.CreateDirectory(path + subpath);
        }
 
        // To prevent overwriting files.
        if (File.Exists(outfile))
        {
            outfile = path + subpath + "_" + i.ToString() + filename;
        }
 
        // Create a file to hold the output.
        fs = new FileStream(outfile, FileMode.Create, FileAccess.Write);
        bw = new BinaryWriter(fs);
 
        // Reset the starting byte for the new BLOB.
        startIndex = 0;
 
        // Read the bytes into outbyte[] and retain the number of bytes returned.
        retval = myReader.GetBytes(2, startIndex, outbyte, 0, bufferSize);
 
        // Output status messages
        string msg = String.Format("{0} ", subpath + filename); 
        Console.Write(Right(msg,48));
 
        while (retval == bufferSize)
        {
            bw.Write(outbyte);
            bw.Flush();
 
            // Reposition the start index to the end of the last buffer and fill the buffer.
            startIndex += bufferSize;
            retval = myReader.GetBytes(2, startIndex, outbyte, 0, bufferSize);
        }
 
        // Write the remaining buffer.
        bw.Write(outbyte, 0, (int)retval);
        bw.Flush();
 
        // Close the output file.
        bw.Close();
        fs.Close();
        Console.Write(" [Complete]");
        Console.WriteLine();
    }
 
    // Close the reader and the connection.
    myReader.Close();
    _conn.Close();
}

no comments   |  tags: , , , | posted in Software, Uncategorized

Feb 6 2008

SubSonic: All Your Database Are Belong To Us

I’m a lazy programmer. Don’t get me wrong, I think that’s a good thing. I believe it drives me to come up with innovative solutions for problems that I’m too lazy to deal with on a regular basis. This frees me up to work on the things that I enjoy, while spending less time on the tedious.

Lately I’ve turned to CMS systems like Kentico, Drupal, and Joomla when I’m creating websites, because I can spend more time on the design and layout and less time developing the backend.

Sometimes, I want more control than a CMS allows, and I want to write more code. In the past I’ve written my own data access layer, and in most cases that works just fine. However, i “need to spend more time with [my] friends, family, dog, bird, cat…” so I started looking for solutions…and I found several:

To be fair, I only tried the first three because they’re open source (free), and in the end, SubSonic met all my needs. It is easy to use. All you really need to do, it setup the database connection string in the app.config/web.config file, run a macro, and sit back and drink your coffee. In the end you’re presented with a couple classes for each table in your database.

Then creating a table record is as easy as:


user = new UserObject(1);
user.FirstName = "Adam";
user.LastName = "Conde";
user.EmailAddress = "myemail@something.com";
user.Save();

Querying is similarly easy.
Check it out for yourself: SubSonic: All Your Database Are Belong To Us

no comments   |  tags: , , | posted in .NET, C#, SQL