Download demo solution https://sourceforge.net/projects/collectionmanager/files/
CCM (C# Collections Mapper) is a well working version of the handwritten mini ORM, working with the database and .Net collections. Productivity is quite high, resource consumption is minimal. This ORM works directly with SQL Server stored procedures. The code is accompanied by descriptions and example .Net Console project showing the technique of ORM using.
Introduction
Data is stored on the SQL server and when a large number of simultaneously connected users access it this creates a serious load on the database server. In addition, often database queries fetch the same data, which is not good in terms of server and applications load.
One of ways to optimize the database in ligament with client application is to reduce traffic and to store data already received from the server on the client or in the middle application tier. Or to store already received data on the application server, especially when the database server and client applications are distributed across the network.
For this purpose, the collections stored in the Session or at the application level are the best suited.
Below is a solution for the data management layer in the combination of
MS Sql Server with a
C# application, suitable for
.Net.
In whole this is a loosely bound entity mapper projecting the application's business logics onto database tables using T-SQL stored procedures. In this realization of ORM the tables, procedures, and entity constructors are written by hand. They are not generated automatically by a mapper.
At the same time, the entity code and stored procedures can be minimased and simplified and fully control by the developer. As well as a process of data obtaining. All client code is reduced to writing the simplest one string calls to the mappers methods and to creating the simplest constructors for entities by template.
CCM allows you to implement fairly complex data structures in your application, without bothering about difficult and tedious questions of data acquisition and conversion issue, allowing you to pay more attention to the develop of business logic and UI. There no need to worry about migrations, context, etc.
At the same time, ORM completely relieves the developer from direct communicating with ADO - datasets, tables, rows, and other data aceess component, to track connections, commands or operate with adapters because ORM works at a higher level of data abstraction. At the same time you will be completely protected from memory leaks and free to create complexity application logic at the primary data level.
General description and principles
As an example, here are all the necessary to work with collection client application classes using the library CollectionManager. The library is universal and operates with any types of collections. Entity classes developer manually creates in the application. They allows him to get data from SQL server through stored procedures. This data form instances of the entity class and form from these instances the collection.
In addition, the library has mechanisms for extracting a specific an instance of a class from the collection, modifying it, and inserting it back into the collection to the same place (sort by ID). The library contains methods for adding and removing instances of the collection (Not records in the tables!!! For this purposes used "ExecuteNonQuery" method of sqlManager class.
Developer should independently take care of maintaining the relevance of the tables. ORM only provides a convenient tool for this. "Book" is an example of a prototype class with instances of which an sample collections works.
This is the class describing the mapping of the database table structure to entity. Sets of fields in a class can arbitrarily relate to the fields of the tables generating the entity.
How to use this? First you should add to your code necessary namespaces.
using ...
using CS_Collections_Mapper;
Access to the library
CS_Collections_Mapper contains two user-accessible classes, CollectionManager and SQLManager. Both of them are implements in the client application via interfaces. Define and store them in application level
public sealed class Repository : Singleton<Repository>
{
public static ICollectionManager Mapper = new CollectionManager();
public static ISQLManager TsqlManager = new SQLManager();
}
Define mapper and sqlManager in client class
In client class it is convenient to organize access to the library via the short links. Define links to the Mapper and TsqlManager instances.
ICollectionManager mapper = Repository.Mapper;
ISQLManager sqlManager = Repository.TsqlManager;
How to connect to DB
ISQLManager interface has several methods to establish the connection to data base.
You should to create connection before you make any operations with mapper or sqlManager.
Here they are:
Add connections methods
string GetConfigPath(string pathKey, string configName, string subFolder)
void AddConnection(string connAlias, string connectionString)
void AddConfigConnections()
void AddCustomConfigConnections(string configPath)
Get connections methods
string GetConnectionString(string connAlias)
ConcurentDictionary<string, Db_Connection> GetConnections()
Remove connections methods
void RemoveConnection(string connAlias)
void RemoveConnections()
If your application is of classic .Net Framework type, you can use connection strings defined in web.config or app.config files.
In this case call the AddConfigConnections() method. After this you can use connection aliases to pass it as a parameter to call mapper or sqlManager to operate with DB.
You may have additional config files in application folders. If this files contains ConnectionStrings sections the connections may be added to connection collection of sqlManager and you can use it by its alias.
To add this connections call the AddCustomConfigConnections(string configPath) method, where configPath a phisical path to custom config file.
To get and pass the path for this config file to AddCustomConfigConnections method call the GetConfigPath(string pathKey, string configName, string subFolder) method with configName and subFolder parameters with empty pathKey parameter.
Or call GetConfigPath(string pathKey, string configName, string subFolder) with config key pathKey with physical path from app.config or web.config files and empty configName and subFolder parameters.
You can add connections manually by calling the AddConnection(string connAlias, string connectionString) method. This may be usefull if you have no config files in application, e.g. if your application is of .Net Core type.
Also you can get, check and remove connections by calling GetConnectionStringByAlias(string connAlias) and RemoveConnection(string connAlias) methods.
Declaring entity collections
UserBooks collection get it instanses from the heap Repository.AllBooks
public static ICollection<Book> UserBooks = new UserBooks() as ICollection<Book>;
AllBooks is the heap from which the rest of all child collections are assembled with the Book entity prototype
The heap collection declaration
public class BooksHeap : BaseCollection<Book>
{
public BooksHeap()
{
//Specify the collection key field (required!)
idFieldName = "BookID";
// secondaryID definition (optional)
idFieldName2 = "Position";
}
}
The child collection declaration
The relation between collections defined in child collection constructor
public class UserBooks : BooksHeap
{
public UserBooks()
{
// indicate that instances are stored on the heap
heapCollection = Repository.AllBooks;
}
}
How to get the collection
public ICollection<Book> GetBooks(string connAlias, Guid creatorID)
{
Hashtable parameters = new Hashtable();
parameters["@CreatorID"] = creatorID;
return mapper.GetCollection<Book> (
Repository.AllBooks, // destination collection
"GetTestBooks", //Stored procedure name
connAlias, //connection string alias(defined in config)
parameters //Stored procedure parameters
//,true *see explanation below
);
}
Initial filling of collections with elements
Pay attention to the last boolean parameter in the GetCollection method. If we omit the last optional bool parameter in the GetCollection call, the mapper will get instances from database by two passes. At first the stored procedure pulls out a list of entity Keys by which instances are searched for in database table.
After receiving the list of Key values, the mapper looks for instances with their keys in the heap collection. If no heap defined mapper search the item with key in the destination collection directly. The Key field usually is the primary id field of a table. This Key must be defined in collection constructor.
If the instances exists in the heap collection, mapper returns the instance of searched entity type with this Key to destination collection. And only if mapper is not found instance in the heap it take it from the database. There will be called entity's constructor and the new item will be added to the heap (if relation defined ) and to destination collection from database. See entity Book class example for details.
If the last boolean parameter in the GetCollection method is set to true (by default) the @ReturnAtOnce parameter will be passed to the SQL procedure, then the procedure returns the entire record set in one pass with all the entity fields. You can omit this logic and return the whole set of fields always. But you should add @ReturnAtOnce bit = null parameter to your Get procedure in anyway.
Collection item retrieval
If instance with this Key is not present in the collection, it will be added to collection from db table. Below the key field is bookId
public Book GetBook(int bookId, ICollection<Book> collection)
{
return mapper.GetItem<Book>(collection, bookId);
}
Getting item index
By default collections in .Net are not indexed. Mapper have the method to retrieve the item index. You can use it for sorting collections or in others purposes.
Here is an examle how to get collection index for Book with id=100
public void GetBookIndex(int bookID, ICollection<Book>collection)
{
int bookIndex = mapper.GetItemIndex(collection, bookID);
}
Sorting collection
public void SortBooks(ICollection<Book>collection)
{
//Reverse sorting
SortCollection<Book>(collection, false);
//Forward sorting
SortCollection<Book>(collection, true);
}
How to Delete item from db table.
To delete the row from the table, use the ExecuteNonQuery method of sqlManager. You should to create stored procedure for delete from table if it is still not present in database.
Create PROCEDURE [dbo].[DeleteBooks]
@BookID int
AS
IF @BookID is not null
-- delete specific book
Delete FROM Books
WHERE (BookID = @BookID)
RETURN
Calling this stored procedure
Hashtable parameters = new Hashtable();
parameters["@BookID"] = bookID;
string errMsg = string.Empty;
sqlManager.ExecuteNonQuery(connAlias, "DeleteBooks", parameters, out errMsg);
If you have changed the db tables on base of which the collection was built, you need to update collection with its items. Now we remove the item from collection for deleted table row.
Removing collec
tion item
Removing an item from collection does not remove it from the heap and does not affect the table row in the db.
public void RemoveBook(
int bookID,
ICollection<Book>collection)
{
mapper.RemoveItem(collection, bookID);
//The same action, more simple and quickly, if book defined collection.Remove(book);
}
Updating collection item
While updating, the collection item instance will be deleted and recreated with new one from db. If the heap for destination collection is defined its item will be modified too with db instance and then mapper will take the updated item to destination collection from the heap. The position of new instance in both collections will not changed and remain the same. If instance with item key is not present in collection, it will NOT be added to collection and no action will perform.
public void UpdateBook(int bookID, ICollection<Book>collection)
{
mapper.UpdateItem(collection, bookID);
}
If you have to change one or two items you may use mapper.UpdateItem() method. But if you source tables have changed significally the better way is to recreate the whole collection one more time by calling collection.Clear() and mapper.GetCollection() methods. The mapper.GetCollection() method adds new items to collection but not clear collection from existing items. Also you can clear collection by calling collection.Clear() and refill it with renewed instances by need later by calling mapper.GetItem() method. When you request the item which is not present in collection this item will be taken from db.
Implementation of an entity class
public class Book : Sortable
{
public int BookID { get; set; }
public double Rating { get; set; }
public DateCreation { get; set; }
public string Caption { get; set; }
public Guid CreatorID { get; set; }
public Book(..)
{
//create Book manually here if need
}
public Book(IDataReader reader, int sortID) : base(reader, sortID)
{
}
To use entity in collections you should specify the entity key field in collection constructor (not in the entity class) to tell mapper what is the key of entity in collection will be used for search its items. The key may have any name. Public field BookID in example above is differs from the default ID name. For convinence the key field may have the same name as primary key field or it alias, returned by stored procedure.
The BookID variable in constructor below is a stored procedure key parameter for seek the row in the Books table. It may have the same name as a class key field name but in this constructor definition it means another things. You can omit this parameter if in the Get stored procedure input key parameter named as "ID". The key parameter in GetTestBooks procedure has the name BookID, that is why we should point it also in a entity constructor signature.
// ...
Here "CmsDb" is a connection alias and "GetTestBooks" is a stored procedure name
public Book(int id_key, int sortID) : base(id_key, sortID, "CmsDb", "GetTestBooks", "BookID")
{
}
// Additional constructor for exotic situation when you need to pass a set of parameters to stored procedure
public Book(int id_key, int sortID)
: base(
id_key,
sortID,
"CmsDb",
"GetTestBooks",
new Hashtable() { { "sp_par1", P_1 }, .. , { "sp_parN", P_N} },
"BookID"
)
{
}
// The set of fields relevant for the entity is defined in the procedure
public override void MapEntityFields (IDataReader reader, int sortID)
{
//automatically filling entity fields that has the same names as reader returns
AutoMapEntity(reader, typeof(Book), this, sortID);
try
{
// filling entity fields manually. works faster than AutoMapEntityFields but require more code. here we can process data transformations
// here you can map and convert the fields you need or process some actions with entity instance while it is constructed
CreatorID = Guid.Parse(reader["CreatorID"].ToString());
}
catch (Exception ex)
{
}
}
}
In the example above, the 4 possible definition of an entity class constructor are shown. You may not need some of them. For example, the last constructor takes a hash table as a parameter for the base class constructor. In this hash table you can specify a list of any typed key value pairs used as a parameters for the stored procedure.
All work with the data is reduced to writing the simplest stored procedures for entity get, edit, delete, add operations and to create classes describing collections and methods for operate with them for storage, retrieval, modification, creation and removal. You may put all logic in one stored procedure for each entity type. It may take some time for coding but you will get abstracting business logic from the data layer, maximum performance and no need to change the code when changing the data structure.
Get stored procedure
Create PROCEDURE [dbo].[GetTestBooks]
@BookID int = null,
@ReturnAtOnce bit = null
AS
IF @BookID is not null
-- specific book
SELECT BookID, Caption, CreatorID, DateCreation, PhotoID, Published, Position, Modified, Rating, ForeignBook, TextID
FROM Books
WHERE (BookID = @BookID)
ELSE
-- all books
begin
if @ReturnAtOnce is not null
SELECT BookID, Caption, CreatorID, DateCreation, PhotoID, Published, Position, Modified, Rating, ForeignBook, TextID
FROM Books
ORDER BY DateCreation DESC
else
SELECT BookID
FROM Books
ORDER BY DateCreation DESC
end
RETURN
We may build any amount of collections with the same prototype entity class where the base class being used is the same. In the examples below, two different collections are used. It is important to note that instances of one collection we get from another.
With such approach we do not pull the same data from SQL server for several times.
For example, we have a store of books and it is divided into several shelfs. In one shelf is books for sale, and in other shelfs books for reading.
If you put a book in the wrong shelf (e.g. in BooksList collection), someone will be surprised with pleasure, and someone will loss it. In the store there will remain the changeless amount of books and it does not change (we operate only with links to instances).
It is important that in all shelfs there are only books and between them are no principal different.
My books I take from the shelf for reading and books come into this shelf from the store (heap).
If I have read all my books, I can go to the store (to the heap) and take another books for reading, or I can order the new ones if they not exists in the store (create new instanses in collection).
However, I don’t know if my book is in stock and if anyone else is using it. I am not dependent on anyone, and the store at the same time builds its own logistics depending on the needs of all visitors.
Storing collections for reuse By default collections stored at Application level class if they should be accessed from different places of application and reused. But you are free to define them anywhere by your own needs.
public static ICollection<Book> AllBooks = new BooksHeap() as ICollection<Book>;
Using SqlManager - ExecuteNonQuery for delete, create and update records in the database tables
- SQLExecuteScalar to retrieve value from stored procedure.
- And the same overloaded method for retrieving values from stored procedure.
Here is the declaration of these SQLManager methods:
void ExecuteNonQuery(string conStrAlias, string ProcName, Hashtable parameters, out string errMsg);
object SQLExecuteScalar(string conStrAlias, string ProcedureName, Hashtable inputParams);
Dictionary<string, object> SQLExecuteScalar(string conStrAlias, string ProcedureName, Hashtable inputParams, Hashtable outputParams);
Calling stored procedure for execute To update, delete or add new Book to db without recieve a result from stored procedure call
sqlManager.ExecuteNonQuery(dbAlias, procName, parameters, out errMsg);
Calling stored procedure to add new book with retrieving
NEW_BookID as a result
sqlManager.SQLExecuteScalar(dbAlias, procName, parameters)
Stored procedure to add row in the table
Create PROCEDURE [dbo].[AddBook]
@Caption nvarchar(100),
@CreatorID nvarchar(50)
AS
Declare @Position int
SELECT @Position = Count(Books.BookID) + 1
FROM Books
WHERE Books.CreatorID = @CreatorID
INSERT INTO Books (Caption, PhotoID, CreatorID, Published, DateCreation, Position, Modified, Rating, TextID)
VALUES (@Caption, 0, @CreatorID, 0, GetDate(), @Position, GetDate(), 0, 0)
Select scope_identity() AS NEW_BookID
RETURN
And how to execute it Hashtable parameters = new Hashtable();
parameters["@Caption"] = "My new book";
parameters["@CreatorID"] = Guid.NewGuid();
int newBookId = -1;
try
{
Int32.TryParse(sqlManager.SQLExecuteScalar("CmsDb", "AddBook", parameters).ToString(), out newBookId);
}
Catch (Exception ex)
{
}
if (newBookId > 0)
{
// some actions with newBookId
}
For some reasons we may need to execute stored procedure and retrieve
output value from it.
Calling with retrieving some result from procedure
You can call
SQLExecuteScalar for retrieve output value returned by stored procedure.
Hashtable inputParameters = new Hashtable();
inputParameters["@Published"] = true;
object result = sqlManager.SQLExecuteScalar("CmsDb", "GetAuthors", inputParameters);
Also you can get a set of output parameters from a procedure through an overloaded method that returns a dictionary with output parameters.
Getting a set of output parameters
public static void GetPublicationCaption(int objectID, int objectTypeID, out string caption, out string creatorID)
{
caption = string.Empty;
creatorID = string.Empty;
Hashtable parameters = new Hashtable();
parameters["@ObjectID"] = objectID;
parameters["@ObjectTypeID"] = objectTypeID;
Hashtable outputParameters = new Hashtable();
outputParameters["@CreatorID"] = creatorID;
outputParameters["@Caption"] = caption;
Dictionary<string, object> result = sqlManager.SQLExecuteScalar("CmsDb", "GetPublicationCaption",
parameters, outputParameters);
if (result != null)
{
caption = result["@Caption"].ToString();
creatorID = result["@CreatorID"].ToString();
}
}
In this case, you declare output parameters in a hash table and get their values from a dictionary procedural. The names of the output parameters in procedure and keys in the result dictionary will be the same. Accordingly, the extraction of parameters from the method is performed by the key
Stored procedure example with getting output parameters values
Create PROCEDURE [dbo].[GetPublicationCaption]
@ObjectID int,
@ObjectTypeID int,
@CreatorID nvarchar(50) OUTPUT,
@Caption nvarchar(200) OUTPUT
AS
If @ObjectTypeID=0
Set @Caption = 'text "' + (Select Caption From Texts Where TextID = @ObjectID) + '"' Set @CreatorID = (Select CreatorID From Texts Where TextID = @ObjectID)
If @ObjectTypeID=8
Set @Caption = 'blog "' + (Select FanClub From FanClubs Where ID = @ObjectID) + '"' Set @CreatorID = (Select CreatorID From FanClubs Where ID = @ObjectID)
RETURN
Thats all for today.
The approach described here is successfully used on the web sites e.g.
prozarium.ru,
probki.net,
PGMania program and some other projects, and showed the best performance results.
The application is free to download and is distributed under the terms of the MIT license.
How to say thanks? Don't hesitate to thank the developer using the Donate link.
Please read License Agreement for details for using CCM in your projects.
(c)2010-2022 Eugene Trifonov, aka p.v.
Saint Petersburg, Russia
mailto: pterodactilus@rambler.ru
Skype prozarium
Telegram @EugeneTrifonov
My CV is here
Specialized in C#, ASP.Net, MS SQL