Introduction
In my long journey to work with data, I have used lot of data access libraries to simplify the development, but in some moment, this libraries always present a problem which is difficult to solve, and in most cases I always have to code SQL queries for data retrieving and performance.
The process of coding SQL queries always forces me to remember column and tables names that is all the time changing in our projects.
Background
With Rednet.DataAccess
, we will define the classes with some of your properties with attributes that indicate our preferences on the tables and columns, and execute CRUD operations and data queries with a strong typing approach, and in the background, it will generate the appropriate connections and SQL statements for us.
Using the Code
Before use, we need to set up the database configuration. We need to do this only once, for example, on startup routine of our app.
DatabaseObjectShared
is a class that will contains the parameters to connect on database. DataFunctionsSQLite
is one of the supported databases of the framework. Currently supports Oracle, MySQL and SQLite. PostgreSQL and SQLServer soon.
var _file = Path.Combine(_dataBasePath, "RednetAccess.db3");
var _dbFuncName = "MyDataFunctionName";
var _function = new DataFunctionsSQLite() { DatabaseFile = _file, Name = _dbFuncName };
DatabaseObjectShared.DataFunctions.Clear();
DatabaseObjectShared.DataFunctions.Add(_dbFuncName, _function);
DatabaseObjectShared.DefaultDataFunctionName = _dbFuncName;
In this framework, we have a generic class named DatabaseObject<>
that encapsulates all the methods to work with data (querys and CRUD) in static and instance manner. We will create our classes inheriting from it, for that we can manipulate the data.
Defining our User
class sample:
public enum UserType
{
Simple,
Administrator
}
public class User : DatabaseObject<User>
{
[FieldDef(IsPrimaryKey = true)]
public int Id { get; set; }
public string Name { get; set; }
public string Password { get; set; }
public UserType UserType { get; set; }
}
Well, here we define a class User
inheriting from DatabaseObject<>
class with four fields and setting the Id
property with FieldDefAttribute
and his IsPrimaryKey
field to true
. Even that your table does not have a primary key, you must define one of your properties to have one. This is important to have one that will be used to localize the records when this in CRUD operations.
Inheriting from DatabaseObject<>
, our class User
now has some new methods.
Go to add some rows with the instance SaveChanges() method:
var _user1 = new User { Id = 1, Name = 'Nelson',
Password = 'xyz', UserType = UserType.Administrator };
var _user2 = new User { Id = 2, Name = 'Robert',
Password = 'xyz', UserType = UserType.Simple };
var _user3 = new User { Id = 3, Name = 'Willian', Password = 'xyz',
UserType = UserType.Simple };
var _user4 = new User { Id = 4, Name = 'Thompson',
Password = 'xyz', UserType = UserType.Simple };
_user1.SaveChanges();
_user2.SaveChanges();
_user3.SaveChanges();
_user4.SaveChanges();
The SaveChanges()
method do a check on the record checking if it already exists on database table. If it exists, do a update on it, otherwise is inserted.
Here, we will retrieve the data with the static Load() method:
var _user = User.Load(u => u.Id = 1);
Console.WriteLine(_user.Name);
Like the Load() method, the Query() method can retrieve a list of User class.
var _users = User.Query(u => u.UserType = UserType.Simple);
foreach(var _user in _users)
{
Console.WriteLine(_user.Name);
}
Both Load()
and Query()
methods use the same mechanism to retrieve data. The difference is that Load()
returns null
when no data is found, and Query()
returns a empty list.
Rednet.DataAccess
uses one of first versions of Dapper.Net inside the framework, and we could use that approach on the same methods to load data with a custom SQL statement.
var _users = User.Query("select * from Users Where UserType = @UserType",
new {UserType = UserType.Simple});
foreach(var _user in _users)
{
Console.WriteLine(_user.Name);
}
The FieldDefAttribute
The FieldDefAttribute
can change the behavior of your class. He is a very important part of framework and indicates how some properties inside your class will be treated by the engine of Rednet.DataAccess
. Below are some of main fields descriptions for FieldDefAttribute
:
Property Name | Data type | Description |
AutomaticValue | Enum | This has three values
None = Default - Does nothing.
AutoIncrement = Indicate that properties with this type are backend calculate and will not be included in Insert fields statements. On SQLite database, you can use it to generate auto increment columns.
BackEndCalculated = Properties with this type are treated like AutoIncrement value, but does not auto generate a value. Like object Sequences on Oracle, you need to make some work to put the new value on your column (like a trigger) and the Rednet.DataAccess will get it back to your object. |
IsPrimaryKey | Boolean | Indicate that the property is part of a Primary Key Constraint. This can be applied on more than one property inside your class. All classes that inherit from DatabaseObject<> must have at least one property decorated with FieldDefAttribute and set with IsPrimaryKey = true to function properly. |
IgnoreForSave | Boolean | Properties marked with this will be ignored from DML Statements inside the framework. It's useful when you need to create some properties that return some data that is generated in run time and are not present in your table. |
Populating Inner Objects
With Rednet.DataAccess
, we can populate inner objects that are present on our classes. For that, we need to use JoinFieldAttribute
on the properties indicating to the framework that in the moment of generating the SQL statement that he will include a inner
, left
or right join
command and populate the inner properties with the results. See the code:
public class Purchase : DatabaseObject<Purchase>
{
[FieldDef(AutomaticValue = AutomaticValue.AutoIncrement, IsPrimaryKey = true)]
public int PurchaseId { get; set; }
public DateTime PurchaseDate { get; set; }
[FieldDef(IgnoreForSave = true)]
public double TotalPurchase
{
get { return this.OrderItems.Sum(i => i.TotalItem); }
}
[JoinField(SourceColumnNames = new [] { "OrderId" },
TargetColumnNames = new [] {"OrderId"},
JoinRelation = JoinRelation.OneToMany, JoinType = JoinType.LeftJoin)]
public ObservableCollection<PurchaseItem> OrderItems { get; set; }
}
public class PurchaseItem : DatabaseObject<PurchaseItem>
{
[FieldDef(IsPrimaryKey = true)]
public int PurchaseId { get; set; }
[FieldDef(IsPrimaryKey = true)]
public int ItemId { get; set; }
public int Amount { get; set; }
public double Price { get; set; }
[FieldDef(IgnoreForSave = true)]
public double TotalItem
{
get
{
return this.Amount * this.Price;
}
}
}
Put some data to test:
for (var _id = 1; _id < 3; _id++)
{
var _order = new Purchase
{ PurchaseId = _id, PurchaseDate = DateTime.Parse("2016-05-25") };
_order.SaveChanges();
var _amount = 1;
for (int _item = 1; _item < (_id == 1 ? 4 : 3); _item++)
{
var _orderitem = new PurchaseItem { PurchaseId = _id,
ItemId = _item, Amount = _amount, Price = 1.0 };
_orderitem.SaveChanges();
_amount++;
}
}
One line of code, all your records...
var _orders = Purchase.Query();
and, displaying then!
foreach (var _order in _orders)
{
Console.WriteLine(string.Format("Purchase: {0} - PurchaseDate: {1} - Total: {2}",
_order.PurchaseId, _order.PurchaseDate, _order.TotalPurchase));
foreach (var _item in _order.OrderItems)
{
Console.WriteLine("Item: {0} - Amount: {1} - Price: {2} - Total: {3}",
_item.ItemId, _item.Amount, _item.Price, _item.TotalItem);
}
}
The JoinFieldAttribute main properties:
Property Name | Type | Description |
SourceColumnNames | String[] | An array with the source column names on current table/class object that will be connected with the foreign table/class. |
TargetColumnNames | String[] | An array with the target column names of the foreign table/class object that will be connected with the source table/class. This list of column names must be the same of the type of decorated with JoinFieldAttribute property. |
JoinRelation | Enum | Informs framework how this data will be populate on property.
OneToOne = Informs that only one row of that type will be populated on the property.
OneToMany = Informs that zero or more than one row will be populated on the property. Usually, this is used on properties of types IList<> , IObservables<> , IEnumerables<> , etc. |
JoinType | Enum | Informs the framework how the SQL statement will be constructed with relation to Join command.
InnerJoin = Selects all rows from both tables as long as there is a match between the columns in both tables.
LeftJoin = Selects all rows from the left table (main object class), with the matching rows in the right table (property on main object class). The result is NULL on the right side when there is no match.
RightJoin = Selects all rows from the right table (property on main object class), with the matching rows in the left table (main object class). The result is NULL on the left side when there is no match. |
Below is some sample code for delete data:
var _users = User.Query();
var _user = _users.LastOrDefault();
if (_user != null)
_user.Delete();
User.DeleteAll(u => u.UserType == UserType.Administrator);
User.DeleteAll(u => u.UserType == UserType.Simple);
User.DeleteAll();
Useful Functions
Below a list of useful functions to use with it:
Function Name | Type | Description |
FromJson() | static | Transforms a json data in the corresponding object |
ToJson() | instance | Export the object into json data format |
Exists() | static | Check if indicated predicate on static object exists in the corresponding database table |
Exists() | instance | Check if current data on instanced object exists on corresponding database table |
Clone() | instance | Clone current object in a new instance object |
CloneTo() | instance | Clone current object in a new instance generic object using its properties names to populate the new object. |
Some Code Samples
Json
var _jsonData =
"{ Id : 9999, Name : \"Nelson Santos\", Password : \"123\", UserType : 1 }";
var _user = User.FromJson(_jsonData);
_user.SaveChanges();
_user.ToJson();
Notes:
ToJson()
can be used with optional parameter compressString
(defaults to false
) for guiding the method to compress the data before its return. FromJson()
can be used with optional parameter decompressString
(defaults to false
) for guiding the method to decompress the string
before deserialize the data and return the new object.
Checking if data exists (static
method):
var _exists = User.Exists(u => u.Id == 1);
Checking if data exists (instance method):
var _jsonData =
"{ Id : 9999, Name : \"Nelson Santos\", Password : \"123\", UserType : 1 }";
var _user = User.FromJson(_jsonData);
var _exists = _user.Exists();
Here, he uses the property decorated with FieldDefAttribute (Id)
where IsPrimaryKey
field is set to true
to identify which columns to use on internal SQL statement.
Points of Interest
I have used this framework in my own projects targeting Xamarin Android, iOS and ASP.NET.
This is useful and gave me some agility to do my work.
History
- 1.0.3 - 26th May, 2016 - First version