Summary
A beta O/R mapping framework for .NET 6.0, licensed under the MIT license.
This project is pure a hobby project - with certain goals and limitations for the developers. The limitations we've set ourselves are as follows:
- Only use official documentation for the used software, programming languages and libraries;
- No Google, stackoverflow, et cetera.
Even though the last rule might seem a bit strange, since in a real world scenario you'd make use of this resource. We did set this rule in place for a reason; mainly to challenge ourselves and to tackle and solve the problems together we find along the ways. Rather than quickly finding the solution to our problems and learn nothing.
One time during the project we've made an exception. We ran into a problem with linq expression trees and our unit tests (our question) and we're unable to understand the problem. After asking for an explaination on stackoverflow someone helped us understand the underlying problem by explaining 'closing' variables in c# and by providing us with this SharpLab IO link to see what the compiler does under the hood. Which made it possible for us to solve our problem.
- Chapter 1. Getting started
- Chapter 2. CRUD operations
- Chapter 3. Direct queries
- Chapter 4. Virtual methods
- Chapter 5. Attributes
- Chapter 6. Specifications
This chapter quickly guides you through how to install SharpSql and on how to set it up in your project.
Step 1. Install SharpSql via the NuGet package: SharpSql.
PM> Install-Package SharpSql
Step 2. Create a connection
Once you've installed the NuGet package you can start initializing the framework in your source code.
First create an appsettings.json file in your project folder, and set your ConnectionStrings:
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost; Database=SharpSqlDatabase; Trusted_Connection=True; MultipleActiveResultSets=true"
}
}For a more detailed guide on creating connection strings, see Microsoft's documentation: Creating a Connection String.
Next you can create a variable named configuration (as shown below) which uses the appsettings.json file which will be needed later.
IConfiguration configuration = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();Step 3. Initialize SharpSql
Next you can initialize SharpSql with the following line of code to let SharpSql connect to the database.
_ = new SharpSqlInitializer(configuration);Even though the example above would be sufficient, the initializer does accept multiple parameters as shown here:
public SharpSqlInitializer(IConfiguration configuration = null,
bool loadAllReferencedAssemblies = false,
bool allowAnonymousTypes = false,
string schemaAlias = "DBO")The first parameter IConfiguration is used for the connection string, so the framework knows what database to connect to. The second parameter loadAllReferencesAssemblies can forcefully load all assemblies on start-up before SharpSql.dll is initialized. The third parameter allowAnonymousTypes enables the use of anonymous types in the expression trees, and the finaly parameter schemaAlias allows an override for existing databases aliases.
And that's it! SharpSql is now fully initialized and ready for the next step - setting up entities and collection classes.
Step 4. SharpSql works only with a database first approach, thankfully coding your database is fairly straightforward - for each table in your database you'll create a collection class and give this class the same name as your table. Then place the SharpSqlTable attribute above the class with the following parameters: the type of the current collection class, the type of the entity class (see Step 5.). And as a last step, inherit from the SharpSqlCollection<EntityType> class and your collection class is all set!
// The collection class of the database table Users.
[SharpSqlTable(typeof(Users), typeof(User))]
public class Users : SharpSqlCollection<User>
{
public Users() { }
}Step 5. As seen in the previous step (Step 4.) every collection class also requires an entity class. Create an entity class and inherit from the SharpSqlEntity class. After that - create a property for each column in the table and provide it with a getter and setter (setters are allowed to be private) and mark the primary key with the SharpSqlPrimaryKey attribute. In this example, we have an Id as primary key (the default -1 is not mandatory), a username, password and an organisation whereas the organisation is a foreign key (join) and last, an empty constructor for the entity class.
// The entity class User which represents a single (new) row in the collection Users.
public class User : SharpSqlEntity
{
[SharpSqlPrimaryKey]
public int Id { get; private set; } = -1;
public string Username { get; set; }
public string Password { get; set; }
public Organisation Organisation { get; set; }
public User() {}
}Step 6. The base class of SharpSqlEntity provides one optional parameter for the constructor:
protected SharpSqlEntity(bool disableChangeTracking = false) { }With this parameter you can provide whether or not you want to enable or disable DisableChangeTracking (false by default). Note that disabling change tracking causes the IsDirty property to always return true, because then the framework has to assume changes were made to the object.
// The entity class User which represents a single (new) row in the collection Users.
public class User : SharpSqlEntity
{
[SharpSqlPrimaryKey]
public int Id { get; private set; } = -1;
public string Username { get; set; }
public string Password { get; set; }
public Organisation Organisation { get; set; }
public User() { }
public User(int fetchByUserId, bool disableChangeTracking = false)
: base(disableChangeTracking)
{
base.FetchEntityById<Users, User>(fetchByUserId);
}
}And that's it for the regular tables! With this set-up you're able to perform all CRUD (Create, Read, Update and Delete) actions on your table. See Chapter 2. CRUD operations for more examples regarding all the CRUD actions or check out Chapter 4. Virtual methods to see what else can be expanded on. In the next (optional) step (Step 7.) we're gonna look at an example of a many-to-many relation.
Step 7. Many-to-many relations - this requires the use of the same SharpSqlTable attribute, but with a different constructor. In this example we'll use the previously delcared Users and User types and a collection of type Roles with entity type Role with the parameters Id as primary key and Name which will be the name of the role itself and so creating the many-to-many table UserRoles. The constructor requires the following parameters: the collection type of the current many-to-many class (in this case UserRoles), the entity type of the current many-to-many class (in this case UserRole) the first collection class (in this case Users) and the second collection class (in this case Roles).
[SharpSqlTable(typeof(UserRoles), typeof(UserRole), typeof(Users), typeof(Roles))]
public class UserRoles : SharpSqlCollection<UserRole>
{
public UserRole() { }
}Next we'll set-up the basic UserRole entity class and we'll add the primary keys as parameters to the constructor and call the base.FetchEntityByCombinedPrimaryKey<CollectionType, EntityType>() to be able to fetch specific records.
public class UserRole : SharpSqlEntity
{
[SharpSqlPrimaryKey, SharpSqlForeignKey(typeof(User)), SharpSqlColumn("UserId")]
public int Column_UserId { get; private set; }
[SharpSqlPrimaryKey, SharpSqlForeignKey(typeof(Role)), SharpSqlColumn("RoleId")]
public int Column_RoleId { get; private set; }
internal UserRole() { }
public UserRole(int userId, int roleId)
{
base.FetchEntityByPrimaryKey(userId, roleId);
}
}Now we have a many-to-many relation set-up with basic functionalities and accessability. For information on how many-to-many relations work within the framework and what else can be done with them see 2.2.6 Many-to-many relations.
Creating, reading, updating and deleting records can be done really easily in SharpSql. In this chapter we're gonna give you examples on all CRUD operations.
In this example we're gonna create a User object, and save it to the database.
var user = new User()
{
Username = "Root",
Password = "Password",
Organisation = null
};
user.Save();TodoWhen you want to fetch all the data from a specific collection class, you can easily do so with the following lines of code:
var users = new Users();
users.Fetch();This will result in the following query:
SELECT * FROM [DBO].[USERS] AS [U];It's also possible to specify how many records you want to fetch with the parameter maxNumberOfItemsToReturn in the Fetch() method.
var users = new Users();
users.Fetch(10);This will result in the following query:
SELECT TOP (10) * FROM [DBO].[USERS] AS [U];And as you may have noticed: SELECT * is being generated, this is because no columns have been specified. If you do want to get only a certain amount of columns you can do this through the Select() method, see 2.2.2 Select.
If you want to count the amount of rows you have fetched from the specified table you can use users.Collection.Count. But if you want to know the amount of records in the database table it's quite inefficient to first fetch all the data and then count it. This could be achieved through the static Records() method on the collection class.
var records = Users.Records();This will result in the following query:
SELECT COUNT(*) FROM USERS AS INT;The collection class has a method Select() which can be used to specify which column names you want to return, let's say we want to fetch all users with only the column Username.
var users = new Users();
users.Select(x => x.Username);
users.Fetch();This will result in the following query:
SELECT [U].[USERNAME] FROM [DBO].[USERS] AS [U];If you want to provide more than one column, you have to provide an object[] with the columns you wish to return.
var users = new Users();
users.Select(x => new object[] { x.Username, x.Password });
users.Fetch();This will result in the following query:
SELECT [U].[USERNAME], [U].[PASSWORD] FROM [DBO].[USERS] AS [U];When you want to join between two tables you can use the Join() method on your collection class to retrieve the information of your sub-object(s), when no join is provided the sub-object will remain null. The type of join can be specified by using either the Left() or Inner() method on the user entity (lambda expression). The left join will be used by default if none are specified.
var users = new Users();
users.Join(user => user.Organisation.Left());
users.Fetch();This will result in the following query:
SELECT * FROM [DBO].[USERS] AS [U] LEFT JOIN [DBO].[ORGANISATIONS] AS [O] ON [U].[ORGANISATION] = [O].[ID];Todo - advanced cases
When you want to filter records, you can use the Where() method and use the comparison operators (see SQL Comparison Operators) on any of the entities fields. In the example below we filter on the Users Id with the equals operator.
var users = new Users();
users.Where(x => x.Id == 1);
users.Fetch();This will result in the following query:
SELECT * FROM [DBO].[USERS] AS [U] WHERE ([U].[ID] = @PARAM1);= -- Equal to
> -- Greater than
< -- Less than
>= -- Greater than or equal to
<= -- Less than or equal to
<> -- Not equal toWe can also order our data before retrieving it through the OrderBy() method. In this method we can order on each column with the Ascending() and Descending() methods.
var users = new Users();
users.OrderBy(x => x.Username.Descending());
users.Fetch();This will result in the following query:
SELECT * FROM [DBO].[USERS] AS [U] ORDER BY [U].[USERNAME] DESC;If you want to order by more than one column, you have to provide an object[] with the columns you wish to order.
var users = new Users();
users.OrderBy(x => new object[] { x.Username.Descending(), x.Organisation.Ascending() });
users.Fetch();This will result in the following query:
SELECT * FROM [DBO].[USERS] AS [U] ORDER BY [U].[USERNAME] DESC, [U].[ORGANISATION] ASC;TodoTodoCurrently the Delete() method throws a NotImplementedException(). In alpha 0.2 the delete method will be available, but drop tables will never be implemented (but can still be achieved through a direct query).
TodoTodoTodoIn Chapter 1. Getting started (Step 5.) we left off with a fairly basic entity class, let's expand on this entity class by adding two more properties to our entity: DateCreated and DateLastModified.
// The entity class User which represents a single (new) row in the collection Users.
public class User : SharpSqlEntity
{
[SharpSqlPrimaryKey]
public int Id { get; private set; } = -1;
public string Username { get; set; }
public string Password { get; set; }
public Organisation Organisation { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateLastModified { get; set; }
public User() { }
public User(int fetchByUserId, bool disableChangeTracking = false)
: base(disableChangeTracking)
{
base.FetchEntityById<Users, User>(fetchByUserId);
}
}The SharpSqlEntity class provides multiple virtual methods, so let's say we want to override the Save() method to change it's behaviour when posting data to the database by always setting the DateCreated to the current date and time, including the DateLastModified.
// The entity class User which represents a single (new) row in the collection Users.
public class User : SharpSqlEntity
{
[SharpSqlPrimaryKey]
public int Id { get; private set; } = -1;
public string Username { get; set; }
public string Password { get; set; }
public Organisation Organisation { get; set; }
public DateTime DateCreated { get; set; }
public DateTime DateLastModified { get; set; }
public User() { }
public User(int fetchByUserId, bool disableChangeTracking = false)
: base(disableChangeTracking)
{
base.FetchEntityById<Users, User>(fetchByUserId);
}
public override void Save()
{
if (IsDirty)
{
DateLastModified = DateTime.Now;
if (IsNew)
{
DateCreated = DateLastModified;
}
}
base.Save();
}
}TodoWithin this framework we have created multiple attributes. In this chapter we'll explain how each attribute can be used and how the attributes are used within the framework itself.
Sometimes you want to name your entity property different than the actual column name, to achieve this you can use the SharpSqlColumn attribute. The framework will automatically assume the name of the property is the same name as the column name, when it doesn't find any matches it'll try and resolve it through the SharpSqlColumn attribute and throws an NotImplementedException when neither was found.
[SharpSqlColumn(ColumnName)]
public string Description { get; private set; }To tell the framework what the primary or shared key of the table is, you can use the SharpSqlPrimaryKey attribute. If there is a shared primary key, it'll map them in the same top-to-down order from the entity class, this means that any parameters regarding the primary keys which are passed on to the framework has to be passed in the exact same order.
// a single primary key:
[SharpSqlPrimaryKey]
public int Id { get; private set; } = -1;
// a shared primary key:
[SharpSqlPrimaryKey]
public int UserId { get; private set; }
[SharpSqlPrimaryKey]
public int RoleId { get; private set; }TodoThe SharpSqlUnitTest attribute is an internally used attribute. This project make use of the NUnit testing framework for all of our unit tests and the project is named "SharpSqlNUnit", which has access to all of the internal classes, methods, properties and variables through the SharpSqlUnitTest attribute which is used on the initialization class.
[SetUpFixture, SharpSqlUnitTest]
internal class NUnitSetupFixture
{
[OneTimeSetUp]
public void Initialize()
{
var memoryEntityTables = new List<string>()
{
"MemoryEntityTables/USERS.xml",
"MemoryEntityTables/ORGANISATIONS.xml"
};
var memoryCollectionTables = new List<string>()
{
"MemoryCollectionTables/BasicFetchUsers.xml",
"MemoryCollectionTables/BasicFetchTopUsers.xml",
"MemoryCollectionTables/BasicJoinInner.xml",
"MemoryCollectionTables/BasicSelectUsers.xml",
"MemoryCollectionTables/BasicJoinLeft.xml",
"MemoryCollectionTables/BasicOrderBy.xml",
"MemoryCollectionTables/BasicWhereAnd.xml",
"MemoryCollectionTables/BasicWhereLessThanOrEqual.xml",
"MemoryCollectionTables/BasicWhereGreaterThanOrEqual.xml",
"MemoryCollectionTables/ComplexJoin.xml",
"MemoryCollectionTables/ComplexWhereLike.xml"
};
_ = new SharpSqlInitialize(memoryEntityTables, memoryCollectionTables);
}
}All of the specifications of SharpSql.
The latest version of this framework is version beta-0.3, released on 2022-03-01.
SQL Server 2005 or higher
NET Standard 2.2., .NET 6.0+.