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.
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;
}
}
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;
}
}
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:");
Connection = new GXDbConnection(c, null);
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);
//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);