gxdn

Gurux.Service

Gurux.Service Component for C# Join the Gurux Community or follow @Gurux for project updates.

Open Source Object Relational Mapping (ORM) component for c#, is a part of Gurux Device Framework.

Purpose of Gurux ORM component is give FAST and SIMPLE to use component that you can use to serialize your database data to C# objects.
Big problem with different databases is that all databases are little bit different and you can't use same SQL sentences to all databases.
Using Gurux ORM component you can serialize your data to different databases. You can use Gurux ORM to create or drop all your tables.

Best part is that in debug mode you can see generated SQL sentence if you move your mouse over argument.

debug

Source code is availabe at GitHub

You can use Lambda Expressions with Gurux ORM to tell what kind of queries you want to do.
We have added some test cases where you can get idea how to use Lambda expressions. You will find test cases from Gurux.Service_Simple_UnitTests directory.

At the moment we are supporting following databases:

General information


  • DataMember attribute will tell is field saved to the database.
  • AutoIncrement attribute will tell if field value is automatically increased on add. Value is updated automatically to the class value on insert.
  • ForeignKey attribute tells is foreign key used.
  • Relation attribute tells that there is a relation to the other table.
  • Enum values are saved as integer value as default. If you want to save enum value as string set Builder.Settings.UseEnumStringValue to true.
  • If you want to use unix date time set Builder.Settings.UseEpochTimeFormat to true.
  • Alias Attribute tells name that is used to the table with SQL queries. It works like AS in SQL.

Relations between tables

Gurux ORM supports following relations between tables:

  • 1:1
  • 1:N
  • N:N

Creating C# objects


First you should create C# objects that hold the data that you want to save to the database. There is only one limitation at the moment.
All objects must derive from IUnique. This means that they must have unique ID. Reason for this is that we want to identify every object and it's very slow find objects example by name. It's much faster to use ID.
You can use DataContract and DataMember attributes to tell what data you want to save to the database.
Relations between objects are told using ForeignKey attribute.

Creating 1:1 object


In this example we have two objects. Company and Country. Each company can be only in one country. So relation is 1:1.
[DataContract]
class GXCompany : IUnique<long>
{
    [AutoIncrement]
    [DataMember]
    public long Id
    {
        get;
        set;
    }
    [DataMember]
    public string Name
    {
       get;
       set;
    }
    
    [DataMember(Name = "CountryID")]
    [ForeignKey]
    public GXCountry Country
    {
        get;
        set;
    }

[DataContract]
class GXCountry : IUnique<int>
{
    [DataMember(Name="ID")]
    [AutoIncrement]
    public int Id
    {
        get;
        set;
    }
    [DataMember(Name = "CountryName")]
    public string Name
    {
        get;
        set;
    }	
}

Creating 1:N object


In this example we have two objects. Parent and Child. Each parent can have multiple children. So relation is 1:N.
[DataContract]
class GXParent : IUnique<int>
{
    [AutoIncrement]
    [DataMember]
    public int Id
    {
        get;
        set;
    }
    [DataMember(Name= "ParentName")]
    public string Name
    {
        get;
        set;
    }

    [DataMember]
    [ForeignKey(OnDelete = ForeignKeyDelete.Cascade)]
    public GXChild[] Children
    {
        get;
        set;
    }
}

[DataContract]
class GXChild : IUnique<long>
{
    [DataMember]
    [AutoIncrement]
    public long Id
    {
        get;
        set;
    }

    [DataMember, ForeignKey(typeof(GXParent), OnDelete=ForeignKeyDelete.Cascade)]
    public int ParentId
    {
        get;
        set;
    }

    [DataMember]
    public string Name
    {
        get;
        set;
    }
}
Because we want to that child knows it's parent only by ID we must told parent's type in ForeignKey parameter. We also told in foreign key parameter that all children are removed if parent is removed.

Creating N:N object


In this example we have two objects. User and user group. Each user can belong to several user groups. So relation is N:N.
[DataContract]
class GXUser : IUnique<int>
{
    [AutoIncrement]
    [DataMember]
    public int Id
    {
        get;
        set;
    }
    [DataMember]
    public string Name
    {
        get;
        set;
    }        
    [DataMember]
    [ForeignKey(typeof(GXUserGroup), typeof(GXUserToUserGroup))]
    public GXUserGroup2[] Groups
    {
        get;
        set;
    }
}

[DataContract]
class GXUserToUserGroup
{
    [DataMember]
    [ForeignKey(typeof(GXUser), OnDelete = ForeignKeyDelete.Cascade)]
    public int UserId
    {
        get;
        set;
    }

    [DataMember]
    [ForeignKey(typeof(GXUserGroup), OnDelete = ForeignKeyDelete.Cascade)]
    public int GroupId
    {
        get;
        set;
    }
}

[DataContract]
class GXUserGroup : IUnique<int>
{
    [DataMember]
    [AutoIncrement]
    public int Id
    {
        get;
        set;
    }

    [DataMember]        
    public string Name
    {
        get;
        set;
    }

    [DataMember]
    [ForeignKey(typeof(GXUser), typeof(GXUserToUserGroup))]
    public GXUser[] Users
    {
        get;
        set;
    }
}
With N:N relations we need extra table (GXUserToUserGroup) where we are saving relation information. We told this to User and user group table in second parameter of ForeignKey attribute.

Making connection to the DB


First you should initialize connection to the DB. Like below:
//For MySQL
MySqlConnection c = new MySqlConnection("Server=localhost;Database=test;UID=root;Password=");
//For Maria DB
MySqlConnection c = new MySqlConnection("Server=localhost;Database=test;UID=root;Password=");
//For Microsoft SQL Server
SqlConnection connection = new SqlConnection("DataBase=test;Server=localhost;Integrated Security=True;");
//For Oracle
OracleConnection connection = new OracleConnection("User Id=system;Password=");
//For SQ Lite
SQLiteConnection c = new SQLiteConnection("Data Source=:memory:");
After that you create Gurux DB connection component.
Connection = new GXDbConnection(c, null);
Connection is now ready and you can create your tables to the database. Gurux ORM can walk through relations and create all tables that are connected to others.
Connection.CreateTable<GXUser>();
Connection.CreateTable<GXParent>();
Connection.CreateTable<GXCompany>();

Insert data


Data is inserted by GXInsertArgs.
GXUser user = new GXUser();
//Fill you class data.

//This generates SQL sentence.
GXInsertArgs arg = GXInsertArgs.Insert(user);
//Insert data to the DB.
Connection.Insert(arg);

Select data


Data is selected by GXSelectArgs.
//Generate SQL sentence where data is search by ID from the DB.
GXSelectArgs arg = GXSelectArgs.SelectById<GXUser>(10);
//Generate SQL sentence where data is search by name like "Gurux.
GXSelectArgs arg = GXSelectArgs.Select<GXUser>(q => q.Name, q => q.Name.Equals("Gurux"));

//Find data from the DB.
List<GXUser> users = Connection.Select<GXUser>(arg);
In default ALL relation data is searched from the DB. If you do not want to get relation data you can set Relations to false or tell what relation data you do not want to get.
//Do not get relation data from other tables.
arg.Relations = false;
//Do not retrieve user group information at all.
arg.Excluded.Add<GXUserGroup>();

//Do not retrieve user group information in User table. 
//If there are other tables where is relation to the user group field they are retrieved.
arg.Excluded.Add<GXUser>(q => q.Groups);

//Find data from the DB.
List<GXUser> users = Connection.Select<GXUser>(arg);

Subqueries


It's faster to get all data with one query than execute several queries. You can make subqueries like this:
//Generate SQL sentence where select User IDs.
GXSelectArgs subQuery = GXSelectArgs.Select<GXUser>(q => q.Id, q => q.Id > 100);
//Select all columns from user group where User Group ID in subquery.
GXSelectArgs arg = GXSelectArgs.Select<GXUserGroup>>null, q => q.Id > GXSql.In(q => q.Users, subQuery));

Update data


Data is updated by GXUpdateArgs.
//Update all data to the DB.
GXUpdateArgs arg = GXUpdateArgs.Update(user);
//Update only name to the DB.
GXUpdateArgs arg = GXUpdateArgs.Update(user, q => q.Name);
//Update data to the DB.
Connection.Update(arg);

Delete data


Data is Deleted by GXDeleteArgs.
//Generate SQL sentence where all data is deleted from the table.
GXDeleteArgs arg = GXDeleteArgs.DeleteAll();
//Generate SQL sentence where user is deleted from the DB.
GXDeleteArgs arg = GXDeleteArgs.Delete(user);
//Generate SQL sentence where user is deleted by id.
GXDeleteArgs arg = GXDeleteArgs.DeleteById(10);
//Delete data from the DB.
Connection.Delete(arg);
If you have problems you can ask your questions in Gurux Forum.
Project type: 
Full project
Short name: 
Gurux.Service
Enable issue tracker: 
Yes