SnapObjects ORM

Simple, Powerful, Ultra-Fast ORM for .NET

SnapObjects ORM makes .NET development a snap!  It is unbelievably simple yet does not skimp on features.  It gives you full control over generated SQL.  And it performs practically like ADO.NET.

Use it Free Get started
SnapObjects ORM

Benefits

Simple, Minimal Coding

Simple, Minimal Coding

Simple to code advanced functionality, and data access is in models to minimize maintenance effort.

Type-Safe Queries

Type-Safe Queries

.NET objects are used as query criteria in a type-safe manner, and testing APIs are provided to verify SQL.

Flexible SQL Generation

Flexible SQL Generation

SQL generation is controlled through a comprehensive set of model attributes, or code raw SQL if preferred.

Loose Relationships

Loose Relationships

Relationships are defined while coding (not pre-defined) and only persist for a particular query.

Transaction-Oriented

Transaction-Oriented

Queries, updates, and actions can be tracked, and then transaction management is automatically applied.

Ultra-Fast Performance

Ultra-Fast Performance

There is little overhead on top of ADO.NET, and queries, updates, and actions are executed in bulk.

Code Snippets

Generating complex SQL queries is controlled by intuitive attributes applied to the model.

namespace Appeon.SqlModelMapperDemo.Models
{
//Generates the underlying SQL query according to the attributes
    [Top(1000)]
    [SqlParameter("custId", typeof(int))]
    [SqlParameter("stratOrderDate", typeof(DateTime))]
    [SqlParameter("endOrderDate", typeof(DateTime))]
    [Table("SalesOrderHeader",Schema = "Sales")]
    [SqlWhere("(CustomerId = :custId) and (Orderdate Between :stratOrderDate and :endOrderDate)")]
    public class SalesOrder
    {
        [Key]
        [Identity]
        public int SalesOrderID { get; set; }

        [Required]
        public DateTime? OrderDate { get; set; }

        [Required]
        public byte? Status { get; set; }

        [Required]
        public bool? OnlineOrderFlag { get; set; }

        [SqlCompute("(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***'))")]
        public string SalesOrderNumber { get; set; }

        [Required]
        public int? CustomerID { get; set; }

        [SqlCompute("(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))")]
        public decimal? TotalDue { get; set; }

        public DateTime? ModifiedDate { get; set; }

//Nests a model and applies various attributes
        [JsonIgnore]
        [SetValue("$SalesOrderID", "$SalesOrderID", SetValueStrategy.Always)]
        [ModelEmbedded(typeof(SalesOrderDetail), ParamValue = "$SalesOrderID",
            CascadeCreate =true, CascadeDelete = true)]
        public IList<salesorderdetail> OrderDetails { get; set; }
    }
}

Retrieving data and loading it into the model only requires parameters – no unproductive query language is necessary.

//Retrieves multiple rows of data, loads it into the model, and then puts it into a list
_context.SqlModelMapper.Load<salesorderquery>(startDate, endDate, customerId)
                       .ToList();


//Retrieves a single row of data and loads it into the model
_context.SqlModelMapper.LoadByKey<salesorder>(orderId)
                       .FirstOrDefault();

Deleting data (including data of a master-detail) only requires a key or the model.

//Deletes data corresponding with the specified key – either a single row of data or if a nested model then the cascaded data
_context.SqlModelMapper.TrackDeleteByKey<salesorder>(orderId)
                      .SaveChanges()

// Deletes data corresponding with the specified model – either a single row of data or if a nested model then the cascaded data
_context.SqlModelMapper.TrackDelete(salesOrder)
                      .SaveChanges()

Saving data is controlled by intuitive tracking functions, with granular control whether to save the entire master-detail relationship or portion thereof (by including/excluding the appropriate TrackDetails).

//Saves data with a master-detail-detail relationship
_context.SqlModelMapper.TrackMaster(header)
                       .TrackDetails(o => o.SalesReasons, reasons)
                       .TrackDetails(o => o.Details, details);

_context.SqlModelMapper.SaveChanges();

Managing transactions is made effortless by powerful implicit capability – comprehensive tracking functions (that go way beyond tracking just the model) determine what is included.

//Tracks various models, raw SQL, and actions
_context.SqlModelMapper.TrackCreate(salesOrder)
         .Track((saveContext) =>
{
  //C# code block
  ……
})  //The C# code block will be executed when SaveChanges is called
                       .TrackUpdate(salesPerson)
                       .TrackSqlCUD(rawSql)
                       .SaveChanges()   //Executes all tracked items in one transaction and automatically commits or rolls back
Download Sample App

Features

Model


The Model maps columns to database tables and contains the related SQL.  The SQL of the Model is generated based on various attributes, which gives developers control over the SQL.  And the Model can represent complex relationships, such as Master-Detail-Detail through nesting of the Model.

Model

SQLModelMapper


The SQLModelMapper is a transaction-oriented data manipulation component.  It provides objects and methods to simplify your database CRUD operations, and it applies automatic transaction management to tracked items.  Tracked items can be executed in bulk to boost performance.

Query

Executes queries and loads the result set to a temporary object for further processing or returns the calculated result set.

 Plain Load

Executes queries defined in the model and loads result set into a self-generated object.

 Aggregate & Scalar Load

Executes the query defined in the model and returns the result set with aggregate or scalar calculation.

Tracking

Tracks model changes, SQLs, and Actions for the purpose of transaction management.

 Track Model

Tracks and caches the database Insert/Update/Delete operation in the model(s).

 Track SQL CUD

Tracks database table Insert/Update/Delete operations.

  Track Action

Tracks more complex situations that are not straightforward CUD operations. For example, you can track a particular value retrieved from the database. Or as another example, track whether the “Save” operation has been cancelled.

 Track Master/Detail

Tracks and caches the data state in the model(s) that are in Master-Detail relationship.

Execute

Submit all the tracked database operations in models, SQLs or actions to database in one shot to execute and let ModelMapper manage the transaction for you.

 SaveChanges

Saves all changes to the database by executing the tracked items (model changes, SQLs, and Actions). Tracked items can be executed in bulk to boost performance.

 Validate

Validates all data in models that have been tracked.

Async

Executes CRUD operations in asynchronous manner.

 Async Queries

Asynchronously retrieves data and loads the plain or processed result set.

 Async Execution

Asynchronously save all changes to the database.

SQLExecutor


SQLExecutor is a set of database manipulation interfaces that encapsulate the core components of ADO.NET.  With SQLExecutor you can directly execute dynamic SQL statements, perform SQLBuilder object functions, and do model queries.

 Execute

Executes a command and returns the number of affected rows.

 Query

Executes a query and maps the result.

SQLBuilder


The SQLBuilder constructs SQL statements in a programmatic and database-agnostic way.  Specifically, it builds the syntax structure and works together with SnapObjects’ DataContext object to generate database-specific SQL statements.

 Validate

Validates the raw SQL string generated for the specified database.