SPDatabase is a class that encapsulates access to SQL Server databases. For my new CodePlex project I wanted to add a new database to SQL Server. The issue I was having was how to create this new database in SQL Server and manage access to it without having to store another connection string. Someone pointed me to the SPDatabase class that was supposed to be able to do this. I decided to figure it out and this post describes how I added a new database using this object.
In my project I have created a database setup page. This page can be accessed by farm administrators in the Central Admin site:
This menu option is added to the Application Management section by activating a site collection feature in the Central Admin Site. The first time an admin clicks the link, the page will find out the database does not yet exist and it will display as shown in the screenshot below:
The bottom of the page contains a Create button and when clicked, it will create and provision the database. After provisioning it will register version number 1 of the database. The page will then look like this:
The page shows the database server that hosts the new database and its name. It also shows when the database was created, by who this was done and the current version of the database schema. This is a custom (very simple) versioning system that I implemented in the database. The database setup page will automatically check if the current schema is up to date. If it is not, it will notify the administrator as shown in this screenshot:
In this blogpost I will describe the most relevant parts of this database setup and upgrade process. The page also has the option to delete the database.
Step 1 – Create your own database object
First thing to do is create your own database object. Your project in Visual Studio needs a reference to Microsoft.SharePoint.dll. Add a new class to your Visual Studio project and add Microsoft.SharePoint.Administration to the using statements. You can now inherit your class from SPDatabase. Add the 2 constructors to your object that are shown in the code sample below:
1: public class SharePointObjectsDatabase: SPDatabase
2: { 3: private VersionInfo _versionInfo;
4:
5: public SharePointObjectsDatabase()
6: { 7: _versionInfo = new VersionInfo(this);
8: }
9:
10: public SharePointObjectsDatabase(String componentName, SPDatabaseServiceInstance databaseService) :
11: base(componentName, databaseService)
12: { 13: _versionInfo = new VersionInfo(this);
14: }
15: }
These constructors create a new VersionInfo object. See step 2 for more details. Next thing to do is to override the Provision method. In the implementation you need to tell the SPDatabase where to find the SQL file with the setup script of your database. My provision method looks like this:
1: public override void Provision()
2: { 3: base.Provision();
4: FileInfo sqlFile = GetProvisioningSQL();
5: if (sqlFile != null)
6: { 7: Dictionary<string, bool> options = new Dictionary<string, bool>();
8: Provision(this.DatabaseConnectionString, sqlFile.FullName, options);
9: SqlConnection.ClearPool(new SqlConnection(this.DatabaseConnectionString));
10: _versionInfo.RegisterVersion(1);
11: }
12: }
The file containing my initial database scripts is loaded by the function GetProvisioningSQL. After calling the Provision method of the SPDatabase and passing the name of the file and the connection string (again, automatically managed by our database object), all existing connections to the new database are closed. After these connections are closed, the database schema version 1 is registered in the database by calling RegisterVersion.
The code to load the SQL is shown below. This function finds the feature definition of the site collection feature that registers the menu item in the Central Administration site. The feature folder for this feature also contains the provisioning SQL Script for my database.
1: private FileInfo GetProvisioningSQL()
2: { 3: foreach (SPFeatureDefinition feature in SPFarm.Local.FeatureDefinitions)
4: { 5: if (feature.Id == new Guid(SharePointObjectsMenuFeatureID))
6: { 7: DirectoryInfo dir = new DirectoryInfo(feature.RootDirectory);
8: if (dir.Exists)
9: { 10: string mask = @"CreateDatabase.sql";
11: FileInfo[] files = dir.GetFiles(mask, SearchOption.AllDirectories);
12: if (files.Length == 1)
13: { 14: return files[0];
15: }
16: }
17: }
18: }
19: return null;
20: }
If the feature definition is found, the code will try to find a file in the feature folder called “CreateDatabase.sql”. This file contains all SQL scripts for the initial provisioning of my database. If nothing is found, or multiple files with that name are found, nothing gets provsioned and we end up with an empty database.
Step 2 – Create the versioning object
In the first step we will create the versioning object. Nothing special here, it just is an object that knows how to read the database schema version from that database once it is created. It also knows how to register a version information for a new version of the database schema.
1: public class VersionInfo
2: { 3: private int _version;
4: private string _createdBy;
5: private DateTime _created;
6: private SharePointObjectsDatabase _database;
7:
8: internal VersionInfo(SharePointObjectsDatabase database)
9: { 10: _database = database;
11: }
12:
13: internal int DatabaseVersion
14: { 15: get
16: { 17: if (_version == 0)
18: GetVersionInfo();
19: return _version;
20: }
21: }
22:
23: internal string CreatedBy
24: { 25: get
26: { 27: if (_createdBy == null)
28: GetVersionInfo();
29: return _createdBy;
30: }
31: }
32:
33: internal DateTime Created
34: { 35: get
36: { 37: if (_created == null)
38: GetVersionInfo();
39: return _created;
40: }
41: }
42:
43: internal void GetVersionInfo()
44: { 45: // Omitted for brevity
46: // Call stored procedure to read versioninfo and set properties
47: }
48:
49: internal void RegisterVersion(int version)
50: { 51: // Omitted for brevity
52: // Call stored procedure to register new version info
53: }
54:
55: }
This example does not contain the code for the SQL Server access. This is very common .NET code and therefore not interesting. The only interesting bit is the connection string to the database. In the functions GetVersionInfo and RegisterVersion, I use the connectionstring that is available on our custom database object:
1: SqlConnection connection = new SqlConnection(_database.DatabaseConnectionString);
The connection string used is the value of the DatabaseConnectionString property of our custom SPDatabase object that we created in the previous step. This is all managed by the SPDatabase itself. I see this as a big advantage of using this object. I no longer have to worry about connection strings and getting the correct connection string applied when my project moves across different servers towards production.
Step 3 – Initialize the database setup page
Next thing to do is to create the database setup page that is shown in the screenshots above. I won’t bore you with the HTML of this page. In the Onload, the status of our database is checked and the page is updated accordingly.
1: private void InitUI()
2: { 3: _action = string.Empty;
4: labelVersion.Text = "N/A";
5: labelCreated.Text = "N/A";
6: labelCreatedBy.Text = "N/A";
7: labelDatabase.Text = "N/A";
8: labelServer.Text = "N/A";
9: if (Database == null)
10: { 11: buttonCreatePage.Text = "Create";
12: _action = "create";
13: labelStatus.Text = "Database does not exist. Click the 'Create' button to create the database.";
14: labelStatus.ForeColor = System.Drawing.Color.Red;
15: labelStatus.Font.Bold = true;
16: }
17: else if (!Database.Exists)
18: { 19: buttonCreatePage.Text = "Configure";
20: _action = "configure";
21: labelStatus.Text = "Database has not been configured. Click the 'Configure' button to configure the database.";
22: labelStatus.ForeColor = System.Drawing.Color.Orange;
23: labelStatus.Font.Bold = true;
24: }
25: else if (Database.NeedsUpgrade)
26: { 27: buttonCreatePage.Text = "Upgrade";
28: _action = "upgrade";
29: labelStatus.Text = "Database needs an upgrade. Click the 'Upgrade' button to upgrade the database to the latest version.";
30: labelVersion.Text = Database.VersionInfo.DatabaseVersion.ToString();
31: labelCreated.Text = Database.VersionInfo.Created.ToString(SPContext.Current.Web.Locale);
32: labelCreatedBy.Text = Database.VersionInfo.CreatedBy;
33: labelStatus.ForeColor = System.Drawing.Color.Orange;
34: labelStatus.Font.Bold = true;
35: }
36: else
37: { 38: buttonCreatePage.Text = "OK";
39: _action = "close";
40: labelStatus.Text = "Database is up to date. No further action required.";
41: labelVersion.Text = Database.VersionInfo.DatabaseVersion.ToString();
42: labelCreated.Text = Database.VersionInfo.Created.ToString(SPContext.Current.Web.Locale);
43: labelCreatedBy.Text = Database.VersionInfo.CreatedBy;
44: labelStatus.ForeColor = System.Drawing.Color.Green;
45: labelStatus.Font.Bold = true;
46: }
47: if (Database != null)
48: { 49: labelDatabase.Text = Database.Name;
50: labelServer.Text = Database.Server.Name;
51: }
52: }
The function InitUI checks whether or not the value of the property Database is assigned:
1: protected SharePointObjectsDatabase Database
2: { 3: get
4: { 5: if (_database == null)
6: { 7: _database = SharePointObjectsDatabase.GetDatabase();
8: }
9: return _database;
10: }
11: set
12: { 13: _database = value;
14: }
15: }
The getter of this property calls the GetDatabase method of the database object that we created in the previous step. The implementation of the GetDatabase method is shown below:
1: internal static SharePointObjectsDatabase GetDatabase()
2: { 3: SPAdministrationWebApplication adminWeb = SPAdministrationWebApplication.Local;
4: foreach (SPDatabase db in adminWeb.WebService.DefaultDatabaseInstance.Databases)
5: { 6: if (db is SharePointObjectsDatabase)
7: { 8: return (SharePointObjectsDatabase)db;
9: }
10: }
11: return null;
12: }
This code iterates through all databases of the DefaultDatabaseInstance that is associated with the Central Administration web application. If it finds a database of the correct type, e.g. it is created as database object of the class we created in step 1, it returns that database object.
Step 4 – Create the database
If our database does not yet exist, our database setup page will create and provision it. The code snippet below is copied from the event handler that handles the click event on the Create button:
1: Database = SharePointObjectsDatabase.Create();
2: Database.Update();
3: if (!Database.Exists)
4: { 5: Database.Provision();
6: }
The Update and Exists methods in the SPDatabase object, so we don’t have to worry about that. We have seen our custom implementation of the Provision method before, so the code here is very easy and simple. This snippet calls the static create method of our custom database object:
1: internal static SharePointObjectsDatabase Create()
2: { 3: SPAdministrationWebApplication adminWeb = SPAdministrationWebApplication.Local;
4: return new SharePointObjectsDatabase(DATABASETITLE, adminWeb.WebService.DefaultDatabaseInstance);
5: }
The DATABASETITLE constant string contains the name of the database. In my case this is ‘SharePoint Objects database’. This is the name of the database that will be displayed in the UI and it is the name of the database in SQL Server.
Step 5 – Upgrade the database
First we need to check whether or not our database needs an upgrade. To do this, we override the NeedsUpgrade property of SPDatabase:
1: public override bool NeedsUpgrade
2: { 3: get
4: { 5: List<FileInfo> sqlFiles = GetUpgradeSQL();
6: return sqlFiles.Count > 0;
7: }
8: set
9: { 10: base.NeedsUpgrade = value;
11: }
12: }
Our new implementation loads a list of SQL files that contain upgrade scripts. These are loaded by the method GetUpgradeSQL:
1: private List<FileInfo> GetUpgradeSQL()
2: { 3: List<FileInfo> result = new List<FileInfo>();
4: foreach (SPFeatureDefinition feature in SPFarm.Local.FeatureDefinitions)
5: { 6: if (feature.Id == new Guid(SharePointObjectsMenuFeatureID))
7: { 8: DirectoryInfo dir = new DirectoryInfo(feature.RootDirectory);
9: if (dir.Exists)
10: { 11: string mask = @"UpgradeDatabase_*.sql";
12: FileInfo[] files = dir.GetFiles(mask, SearchOption.AllDirectories);
13: foreach (FileInfo file in files)
14: { 15: if (GetVersionForUpgradeFile(file) > VersionInfo.DatabaseVersion)
16: { 17: result.Add(file);
18: }
19: }
20: }
21: }
22: }
23: return result;
24: }
This method tries to find the feature definition of the feature that installed the menu item for the database setup page. Just like in step 1, the code checks this folder and its sub folder for presence of SQL files. The naming convention for upgrade files is ‘UpgradeDatabase_<version>.sql’. The upgrade SQL file for the schema upgrade to version 4 is called ‘UpgradeDatabase_4.sql’. It compares the version number in the name of this file with the current database schema version registered in the database. If the file version is bigger, the SQL file is returned. If NeedsUpgrade finds SQL files that contains SQL script that was not yet handled, the Upgrade action is started when the user clicks the Upgrade button:
1: if (Database.NeedsUpgrade)
2: { 3: Database.Upgrade();
4: }
The Upgrade method of our database is another overridden method of SPDatabase:
1: public override void Upgrade()
2: { 3: base.Upgrade();
4: List<FileInfo> sqlFiles = GetUpgradeSQL();
5: if (sqlFiles != null)
6: { 7: foreach (FileInfo file in sqlFiles)
8: { 9: StreamReader reader = File.OpenText(file.FullName);
10: string sql = reader.ReadToEnd();
11: reader.Close();
12: RunSQLFile(sql);
13: VersionInfo.RegisterVersion(GetVersionForUpgradeFile(file));
14: }
15: }
16: }
This code simply iterates through all upgrade SQL files that were found. It gets the upgrade version number from the file name, runs the upgrade script and registers the new version information in the database. This way the version information table in the database always can tell us when we upgraded to which version.
Step 6 – Delete the database
This last step is very simple. Because our SharePoint knows everything about our database, we just need to unprovision and delete it:
1: if (Database.Exists)
2: { 3: Database.Unprovision();
4: Database.Delete();
5: }
This snippet is taken from the eventhandler associated with the Delete button.
Conclusion
As we have seen in this post, creating a database in your SharePoint farm can be easily done using the SPDatabase object. You still find a lot of code in this post, but most of it is to make to maintenance page work correctly and to implement the upgrade scenario. If you just need to deploy a database without having to worry about versioning of the database schema, you just need a few lines of code.
Another big advantage of using SPDatabase is that you no longer need to worry about connection strings. And because we inherit our database from SPDatabase, we can also have our database in the SharePoint backup of the content databases.