The code of this post has been tested on Umbraco 7.12.
“PetaPoco” uses class definitions to create tables. Basically, it analyzes a class definition and uses the attributes applied to its members to create the corresponding tables. For example, the following class would create a “Directors” table in the “Umbraco” database.
As you can see, a lot of attributes are used in order to specify how the table gets created. Here is a review of the most important one at the class level:
- “TableName” defines the name of the table.
- “PrimaryKey” creates a primary key in the table and defines whether it gets auto incremented or not.
- “ExplicitColumns” defines that only the properties marked with the “Column” attribute get created. If you don’t specify this attribute, all the properties of the class will get mapped to a column in the table. Most of the time, this is not wanted as some properties could simply be a calculation of other properties but you don’t want them to correspond to a column in the table.
You can also define attributes at property level:
- “Column” defines the name of the column (and also specifies that the property corresponds to a column in the database if “ExplicitColumns” is set at the class level).
- “PrimaryKeyColumn” defines that the column is a primary key of the table. Even though this is redundant with the “PrimaryKey” attribute at class level, it is required.
- “ForeignKey” creates a foreign key to another table. If the property “Column” is not used, the primary key (of the foreign table) is used as reference column. Moreover, I recommend using the “Name” property to define the name of the created foreign key or you will have a conflict if you have more than one foreign key.
- Column types normally depend on the .NET type of the property but you can use “SpecialDbType” to specify a different one (for example “NTEXT” for a long text).
- “Index” creates an index in the table. This attribute is the one to use to create a “unique” constraint.
- “NullSettings” defines whether the column accepts “NULL” value or not.
- “Constraints” defines a default value constraint on the column.
- “Length” defines the maximum length for the column content.
- “ResultColumn” defines that the property is only populated when the database is queried but the property is not used when creating or updating the record in the database.
Now, let’s create some other tables. First, a “Movies” one that stores movies:
Then, the “Actors” table:
Finally, we need to create an “ActorMovie” table to create a many-to-many relationship:
The primary key of this table is composed of the “ActorId” and “MovieId” column, so we need to separate them with a comma. We also need to put “AutoIncrement” to false otherwise we won’t be able to modify the content of this column.
Now that the entities are defined, we need to create the tables in the “Umbraco” database. Most of the time, you’ll want to do this in an “ApplicationEventHandler” in order to create them at the application startup if it does not exist already.
The generic version of the “TableExist” method has been introduced with Umbraco 7.12. If you use a lower version, you can use the non-generic one where you have to pass the name of the table instead of its type.
The class “DatabaseSchemaHelper” is used to perform schema related operations on the database (create a database, create a table, drop a table, etc…). Its constructor takes three arguments:
- The database to use. Here, we’re using the database of “Umbraco”.
- A logger.
- The “SqlSyntax” to use. This is important as you can plug “Umbraco” to a SQL Server but also to a MySql one which uses a slightly different SQL syntax for some concepts.
Our code simply checks if the table corresponding to the “ActorMovie” class exists in the database. If not, it gets created along with the other ones.
Inserting data is pretty straightforward as the only thing to do is creating a new object of the desired type and inserting it in the database. For example, the following code creates some records in our tables.
One cool thing is that we do not need to set the value of the “Id” of the inserted records. Indeed, as they are the primary key of their tables, they are automatically filled in by “PetaPoco” once the item is inserted in the table.
If you’re using SqlCe for your tests and want to be able to check the content of the “Umbraco” database, I recommend you install the SQLite/SQL Server Compact Toolbox extension for Visual Studio.
Note that you also need the Microsoft SQL Server Compact 4.0 runtime if you want to use this extension.
You have two solutions to query data from the database; either you use the functionality provided by “PetaPoco” and write full “SQL” queries as a string or you use a functionality provided by “Umbraco” allowing you to query data using LINQ.
For example, you could retrieve all the directors who have “James” as first name like this:
Note that you can use placeholders via the “at” symbol (@) to insert parameters in the query in a safe manner.
Even though this is correct, this approach is error prone. Indeed, you could easily have mistyped a column name or the table name making the query invalid but “Visual Studio” wouldn’t notice it as for it, it’s just a string.
That’s why I prefer to use the second approach provided by “Umbraco” (although, we’ll see that there are some limitations).
This way, you can’t mistype a column name as they are retrieved from the generic type you pass to the method. The cool thing is also that all these methods return a “Sql” object allowing you to chain them together.
Joins are also very easy. For example, let’s retrieve all movies with their director:
Biascally, the “On” method expects two delegates that must return the values to compare. Here, we want to join the tables based on the value of the “DirectorId” column of the “Movies” table and the “Id” column of the “Directors” one.
Note that in that case, the “Fetch” method is a bit more complex. Indeed, we select columns from the “Movies” table but also from the “Directors” one so we need to be able to split them into objects. So for every record, the delegate is called and:
- “m” contains the movie data.
- “d” contains the director data.
- The property “Director” of the “Movie” object is set on “d” then “m” is returned.
In conclusion, for each record, the fields from “Movies” are put in “m”, the ones of “Directors” are put in “d”, then “m” is returned once its “Director” property is set on “d”, which is why the final result is a list of “Movie” object.
Another example consists in retrieving the data of the “Avatar” movie including all the actors who play in it:
You can see the generated SQL query by using the “SQL” property of the “Sql” object.
Since the version 7.12, it is also possible to specify the column to select with delegates like this:
If you don’t pass any delegate, all the columns will be selected. Moreover, if you want to select fields from different tables, you can use the “AndSelect” method like this:
A lot of queries are writable with the aforementioned approach, however, some aren’t. For example, there is currently no way to specify aliases for tables making some queries impossible to build for the “Sql” class.
Imagine these tables:
The “Drivers” table stores drivers data while “Constructors” stores data about Formula One constructors including a foreign key to the first and the second driver. In that case, if you want to retrieve all constructors along with the first and the second driver, you’ll need to join the “Drivers” table twice and therefore, you’ll need to use aliases, which is currently impossible.
So for these kind of cases, you’ll have to use hard-coded queries.
Updating data is also very simple thanks to the “Update” method. If you don’t have the object to update (because you didn’t fetch it) but you know the its ID and the column you want to update, you can use the “Update” method like this:
Note that it’s very important to specify the name of the column to update or “PetaPoco” will try to update all the columns and in that case, it will try to put “NULL” in the “LastName” and “Biography” columns as they are not defined in the object we pass in parameter.
A solution to avoid this is to fetch the object before updating it:
As you selected all the columns, you don’t need to specify the columns to update. However, this means that you execute one more SQL query.
The method “Delete” is used to delete records from the database. You can either pass an object or the value of its primary key to specify which element to delete. So if you want to delete the director with the “ID” 1, you could do it like this:
Or like that:
Finally, it’s also pretty easy to use transactions by using the “GetTransaction” method. This method returns an “IDisposable” object and should be used in a “using” clause:
You don’t need to use a “try … catch … finally” clause to rollback the transaction in case of errors as the “Dispose” method of the “Transaction” object does it for you.