Navigation

• Main Page

• Random Page
• Create a new Page
• All Pages
• Categories

• Administration
• File Management

• Login/Logout
• Language Selection
• Your Profile
• Create Account

Gemli.Data

• Database O/RM to provide lightweight persistence support for C# projects with *no* code generators required, *no* XML-based mappings required, *no* attributes required, and *no* code-based setup required in basic scenarios where inferences cannot be made by looking at the POCO with .NET reflection.
  • Of course, it also aims to support and work well with code generators, support XML-based mappings, work best by default with attributes-based mapping details, and allow for relatively easy manipulation of mappings in explicit code.
  • So, essentially, the mappings are reflection-driven, but can instead be XML-driven or code invocation driven.
  • All of the reflection-driven (attributes-driven) mappings are cached to optimized CLR objects in the same way an XML-driven implementation should be.
  • You do not have to choose one mapping paradigm or another. XML mappings can override attributes/reflection, and manual code can override further.
• Complete persistence capacity with O/RM-neutral POCO objects with as minimal code or learning curve required as feasibly possible.
  • Two or three lines of code to wrap a POCO object, reference a database provider, and persist the POCO object as a DB record.
• Reference field mappings by CLR property/field or by DB column name, whichever is convenient.
  • Example: myQuery.WhereProperty["ID"].IsEqualTo(2) might be the same as myQuery.WhereColumn["mytable_id"].IsEqualTo(2)
• Support for SQL stored procedures based CRUD operations
• Support one-to-one relationships, one-to-many relationships, many-to-many relationships, and many-to-one relationships
  • with n-level or infinite deep-loading
    • including with SQL stored procedures for per-table CRUD operations (using client-side joins)
• Support all ADO.NET compatible (requires DbFactory) database providers that speak ANSI SQL and support bi-directional parameters
• Expose a basic query mechanism for loading filtered collections of data entities.
• Does not replace the usefulness of other O/RM solutions or raw ADO.NET in scenarios that have complex functional or performance requirements
  • If you need significantly more detailed control of your mappings or mapping behavior than the basics, or you need the raw performance of a highly optimized code generator, Gemli.Data probably isn't for you
  • Not a do-it-all solution like nHibernate tries to be, only a do-the-obvious data persistence solution.
  • No support for aggregate functions (i.e. SUM) or for GROUP BY, or similar advanced queries. There are always workarounds using raw ADO.NET and the load-by-DataRow feature of the DataModel class. ;)
  • Probably the biggest down-side of Gemli.Data at this point is that only the Table/Class maps are strongly typed when declaring queries, whereas field names in query conditions must be identified with strings. This can't change with C# 3.0 or earlier without a code generator (or lots of manual code). This might change in the future with C# 4.0 dynamic objects, otherwise it might evolve with some Visual Studio integration to gain some optional "live code generation" that might pre-compile as you are writing code and/or saving changes so that you get the full IntelliSense of a field mapping. All of this is speculative for now.
• Minor data utilities
  • Example: Convert a POCO object to a DataRow with one line of code: var dr = new DataModel<MyPoco>(myObject).Convert.ToDataRow();

// attributes only used where the schema is not inferred
// inferred: [DataModelTable(Schema = "dbo", Table = "SamplePoco")]
public class SamplePoco
{
    // inferred: [DataModelColumn("ID", IsPrimaryKey = true, IsIdentity = true, 
    //     IsNullable = false, DataType = DbType.Int32)] // note: DbType.Int32 is SQL type: int
    public int ID { get; set; }

    // inferred: [DataModelColumn("SampleStringValue", IsNullable = true, 
    //     DataType = DbType.String)] // note: DbType.String is SQL type: nvarchar
    public string SampleStringValue { get; set; }

    // inferred: [DataModelColumn("SampleDecimalValue", IsNullable = true, 
    //     DataType = DbType.Decimal)] // note: DbType.Decimal is SQL type: money
    public decimal? SampleDecimalValue { get; set; }
}

[TestMethod]
public void CreateAndDeleteEntityTest()
{
    var sqlFactory = System.Data.SqlClient.SqlClientFactory.Instance;
    var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnectionString);

    // create my poco
    var poco = new SamplePoco { SampleStringValue = "abc" };

    // wrap and auto-inspect my poco
    var dew = new DataModel<SamplePoco>(poco); // data entity wrapper

    // save my poco
    dew.DataProvider = dbProvider;
    dew.Save(); // auto-synchronizes ID
    // or...
    //dbProvider.SaveModel(dew);
    //dew.SynchronizeFields(SyncTo.ClrMembers); // manually sync ID

    // now let's load it and validate that it was saved
    var mySampleQuery = DataModel<SamplePoco>.NewQuery()
        .WhereProperty["ID"] == poco.ID; // poco.ID was inferred as IsIdentity so we auto-returned it on Save()
    var data = dbProvider.LoadModel(mySampleQuery);
    Assert.IsNotNull(data); // success!

    // by the way, you can go back to the POCO type, too
    SamplePoco poco2 = data.Entity; // no typecast nor "as" statement
    Assert.IsNotNull(poco2);
    Assert.IsTrue(poco2.ID > 0);
    Assert.IsTrue(poco2.SampleStringValue == "abc");

    // test passed, let's delete the test record
    data.MarkDeleted = true; 
    data.Save();

    // ... and make sure that it has been deleted
    data = dbProvider.LoadModel(mySampleQuery);
    Assert.IsNull(data);

}

var mySampleQuery = DataModel<SamplePoco>.NewQuery()
    .WhereColumn["SampleStringValue"].IsLike("%bc");
var models = dbProvider.LoadModels(mySampleQuery);
SamplePoco theFirstSamplePocoEntity = models.Unwrap<SamplePoco>()[0];
// or.. SamplePoco theFirstSamplePocoEntity = models[0].Entity;

Data Providers

Initializing A Data Provider

Gemli.Data.Providers.DbDataProvider

var sqlFactory = System.Data.SqlClient.SqlClientFactory.Instance;
var dbProvider = new Gemli.Data.Providers.DbDataProvider(sqlFactory, AppSqlConnectionString);

Gemli.Data.Providers.MemoryDataProvider

var memProvider = new MemoryDataProvider();

// add a table
var dt = new System.Data.DataTable("Company");
var companyIdCol = new DataColumn("company_id", typeof (int));
dt.Columns.Add(companyIdCol);
var companyNameCol = new DataColumn("company_name", typeof (string));
dt.Columns.Add(companyNameCol);
memProvider.AddTable(dt);

One-Time Referencing Of The Initialized Provider

var sqlFactory = System.Data.SqlClient.SqlClientFactory.Instance;
var dbProvider = new Gemli.Data.Providers.DbDataProvider(sqlFactory, AppSqlConnectionString);

// Assign as application default provider
Gemli.Data.Providers.ProviderDefaults.AppProvider = dbProvider;

DataModel and DataModel<T>

Gemli.Data.DataModel (inheriting)

// A basic DataModel class.
public class Customer : DataModel
{
    [DataModelColumn("customer_id")]
    public virtual int ID
    {
        get { return (int)base["ID"]; }
        set { base["ID"] = value; }
    }

    [DataModelColumn("customer_name")]
    public virtual string Name
    {
        get { return (string) base["Name"]; }
        set { base["Name"] = value; }
    }
}

Gemli.Data.DataModel<TEntity> (wrapping)

public class Customer // : object, or your own base class
{
    [DataModelColumn("customer_id")]
    public virtual int ID { get; set; }

    [DataModelColumn("customer_name")]
    public virtual string Name { get; set ] 
}

...

var customer = new Customer {Name = "Jon"};
DataModel customerDataModel = new DataModel<Customer>(customer);

Saving Data

• Execute its .Save() method. If the DataModel instance was not already loaded from the database, you will need to first set its .DataProvider property.
• Invoke the data provider's .SaveModel(dataModel) method.
• If the DataModel is in a Gemli.Data.DataModelCollection, save the collection.

DataModel.Save()

var sqlFactory = System.Data.SqlClient.SqlClientFactory.Instance;
var dbProvider = new Gemli.Data.Providers.DbDataProvider(sqlFactory, AppSqlConnectionString);
Gemli.Data.Providers.ProviderDefaults.AppProvider = dbProvider;

var contact = new Contact();
contact.Name = "Bob Smith";
var contactModel = new DataModel<Contact>(myContactObject);
contactModel.Save();

var contact = new Contact();
contact.Name = "Bob Smith";

var contactModel = new DataModel<Contact>(myContactObject);

var sqlFactory = System.Data.SqlClient.SqlClientFactory.Instance;
var dbProvider = new Gemli.Data.Providers.DbDataProvider(sqlFactory, AppSqlConnectionString);
contactModel.DataProvider = dbProvider;

contactModel.Save();

var sqlFactory = System.Data.SqlClient.SqlClientFactory.Instance;
var dbProvider = new Gemli.Data.Providers.DbDataProvider(sqlFactory, AppSqlConnectionString);

var contactQuery = DataModel<Contact>.NewQuery()
    .WhereProperty["Name"] == "Bob Smith";
var contactModel = dbProvider.LoadModel(contactQuery);

contactModel.Entity.Salary += 10000m; // give Bob a raise

contactModel.Save(); // contactModel already knows its DataProvider

DataProvider.SaveModel()

dataModel.SynchronizeFields(SyncTo.FieldMappedData);

dataModel.SynchronizeFields(SyncTo.ClrMembers);

dataModel.SynchronizeFields(SyncTo.FieldMappedData);
dataProvider.SaveModel(dataModel);
dataModel.SynchronizeFields(SyncTo.ClrMembers);

Loading Data

Queries

var customerQuery = DataModel<Customer>.NewQuery();

Query Conditions

var customerQuery = DataModel<Customer>.NewQuery()
    .WhereProperty["salary"].IsGreaterThan(60000m)
    .WhereProperty["department"].IsEqualTo("Sales");

Requesting Data

Single Record

• From a DataModelQuery<TModel> object you can invoke SelectFirst().
• From the DataModel/DataModel<T> classes you can statically load a single record with a DataModelQuery<T> using the static .Load() method.
• From a data provider, you can invoke LoadModel().
• From a DataModel/DataModel<T> object you can load a single record as an instance of the DataModel/DataModel<T> using .Load(), passing in an ADO.NET DataRow or IDataReader.

var sqlFactory = SqlClientFactory.Instance;
var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

var myCustomerQuery = DataModel<Customer>.NewQuery()
    .WhereColumn["customer_id"].IsEqualTo(153);
DataModel<Customer> customer = myCustomerQuery.SelectFirst();

var sqlFactory = SqlClientFactory.Instance;
var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

var myCustomerQuery = DataModel<Customer>.NewQuery()
    .WhereColumn["customer_id"].IsEqualTo(153);
DataModel<Customer> customer = DataModel<Customer>.Load(myCustomerQuery);

var sqlFactory = SqlClientFactory.Instance;
var dbProvider = new DbDataProvider(sqlFactory, TestSqlConnection);

var myCustomerQuery = DataModel<Customer>.NewQuery()
    .WhereColumn["customer_id"].IsEqualTo(153);
DataModel<Customer> customer = dbProvider.LoadModel(myCustomerQuery);

Multiple Records

• From a DataModelQuery<TModel> object you can invoke SelectFirst() or SelectMany().
• From the DataModel/DataModel<T> class's LoadMany() method.
• From the data provider's LoadModels() (plural) method.

var customersQuery = DataModel<Customer>.NewQuery()
    .WhereProperty["IsActive"].IsEqualTo(true);
var customers = customersQuery.SelectMany();

var customersQuery = DataModel<Customer>.NewQuery()
    .WhereProperty["IsActive"].IsEqualTo(true);
var customers = DataModel<Customer>.LoadMany(customersQuery);

var customersQuery = DataModel<Customer>.NewQuery()
    .WhereProperty["IsActive"].IsEqualTo(true);
var customers = dbProvider.LoadModels(customersQuery);

Transactions

Creating A Transaction

Rolling Back

Loading and Saving With Stored Procedures

• InsertParam, SelectParam, SelectManyParam, UpdateParam, DeleteParam - These properties are alternate database object member names that describe the names of the parameters that are passed into the stored procedure at runtime. By default, these values are "@ColumnName".

• ReturnOnInsert - If set to true, this field is included in INSERTs to the database as a return value. This property defaults to the value of the property IsIdentity. Note that setting this to true forces the IncludeOnInsert value to be false.

• IncludeOnInsert - If set to false, indicates that this field is excluded from the parameters list. This property defaults to the opposite value of the property IsIdentity. Note that setting this to true forces the ReturnOnInsert value to be false.

Relationships (Deep Loads and Deep Saves)

Executing Deep Loads and Deep Saves

DataProviderBase.DeepLoadModel()

n-Level Depth

Recursion Upon Same Records

DataProviderBase.DeepLoadModels()

DataProviderBase.DeepSaveModel()

DataProviderBase.DeepSaveModels()

DataModel.Save(deep, ...)

Declaring Relationships

ForeignDataModelAttribute

[DataModel("customer")]
public class Customer
{
    [DataModelColumn("customer_id")]
    public int ID { get; set; }

    [ForeignDataModel(
        ColumnName = "billing_address_id", 
        ForeignColumnName = "address_id",
        Relationship = Relationship.OneToOne]
    public Address MailingAddress { get; set; }

}

ForeignKeyAttribute

[DataModel("customer")]
public class Customer
{
    [DataModelColumn("customer_id")]
    public int ID { get; set; }

    public Address MailingAddress { get; set; }

    [ForeignKey(
        Relationship = Relationship.OneToOne,
        RelatesTo = typeof(Address),
        ForeignColumn = "address_id",
        AssignToMember = "MailingAddress"
        )]
    public int MailingAdressID { get; set; }
}

Many-To-Many Scenarios

-- A simple User table, doesn't know Groups
CREATE TABLE [dbo].[User](
	[user_id] [uniqueidentifier] NOT NULL,
	[user_name] [varchar](250) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- A simple Group table, doesn't know Users
CREATE TABLE [dbo].[Group](
	[group_id] [uniqueidentifier] NOT NULL,
	[group_name] [varchar](250) NULL,
 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
	[group_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- A Group-User mapping table, allows a user to have many groups,
-- allows a group to have many users.
CREATE TABLE [dbo].[GroupUser](
	[groupuser_id] [bigint] IDENTITY(1,1) NOT NULL,
	[user_id] [uniqueidentifier] NOT NULL,
	[group_id] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_GroupUser] PRIMARY KEY CLUSTERED 
(
	[groupuser_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupUser]  WITH CHECK ADD  CONSTRAINT [FK_GroupUser_Group] FOREIGN KEY([group_id])
REFERENCES [dbo].[Group] ([group_id])
GO

ALTER TABLE [dbo].[GroupUser] CHECK CONSTRAINT [FK_GroupUser_Group]
GO

ALTER TABLE [dbo].[GroupUser]  WITH CHECK ADD  CONSTRAINT [FK_GroupUser_User] FOREIGN KEY([user_id])
REFERENCES [dbo].[User] ([user_id])
GO

ALTER TABLE [dbo].[GroupUser] CHECK CONSTRAINT [FK_GroupUser_User]
GO

public class Group
{
    [DataModelColumn("group_id", IsPrimaryKey = true)]
    public Guid ID { get; set; }
    [DataModelColumn("group_name")]
    public string Name { get; set; }
    [ForeignDataModel(Relationship = Relationship.ManyToMany,
        MappingTable = "GroupUser")]
    public List<User> Users { get; set; }
}

public class User
{
    [DataModelColumn("user_id", IsPrimaryKey = true)]
    public Guid ID { get; set; }
    [DataModelColumn("user_name")]
    public string Name { get; set; }
    [ForeignDataModel(Relationship = Relationship.ManyToMany,
        MappingTable = "GroupUser")]
    public List<Group> Groups { get; set; }
}

Loading Mappings From XML

<?xml version="1.0"?>
<mappings>
  <mappingItem>
    <class>Tests.Gemli.Data.MemoryDataProviderTest+MockPoco, Tests.Gemli.Data</class>
    <dataModel>
      <entityType>Tests.Gemli.Data.MemoryDataProviderTest+MockPoco</entityType>
      <tableMapping>
        <table>mock_table</table>
        <schema>dbo</schema>
      </tableMapping>
      <columnMappings>
        <column>
          <!-- 
               The ownerType element should be the same as the class element above;
               unfortunately this is not optional in the current build.
          -->
          <ownerType>Tests.Gemli.Data.MemoryDataProviderTest+MockPoco</ownerType>
          <memberName>ID</memberName>
          <columnName>customentity_id</columnName>
          <sqlType>Int</sqlType>
          <isNullable>false</isNullable>
          <isPrimaryKey>true</isPrimaryKey>
          <isIdentity>true</isIdentity>
          <defaultValue>0</defaultValue>
        </column>
        <column>
          <ownerType>Tests.Gemli.Data.MemoryDataProviderTest+MockPoco</ownerType>
          <memberName>SomeValue</memberName>
          <columnName>some_value</columnName>
          <sqlType>NVarChar</sqlType>
          <isNullable>true</isNullable>
          <isPrimaryKey>false</isPrimaryKey>
          <isIdentity>false</isIdentity>
        </column>
      </columnMappings>
    </dataModel>
  </mappingItem>
</mappings>

Customizing Mappings At Runtime

DataModelMap mapping = DataModelMap.GetEntityMapping(typeof(YOUR_TYPE));

Table Mapping

Column Mappings

Foreign Mappings

Iterating And Modifying The DataModelMap Items

var oldMapping = DataModelMap.GetEntityMapping(typeof(MyClass));
var newMapping = new DataModelMap(typeof(MyClass));
newMapping.TableMapping.Table = "MyOtherTable";
DataModelMap.MapItems[typeof(MyClass)] = newMapping;