Зарегистрировано: 328

Помощь  Карта сайта

О чем пишут?


Хинкали в подробностях и мелких деталях Хинкали – одно из лучших изобретений грузинской кухни, даже и не всей грузинской, поскольку она весьма пестра и разнообразна, а конкретно берёт начало из Тушети (Ананури) и Мохеви (Тианети). К нашему времени хинкали расползлись по всей Грузии, Абхазии и ..

Я так вижу!



Мы делаем сайты с CMS. И бесплатно тоже!! Сайт: http://www.prozarium.ru
Мы делаем сайты. Качественно, быстро, профессионально и недорого. Сроки от 3-х дней. Мы предоставляем тех. поддержку, хостинг, ракрутку. Все сайты делаются на CMS. Этот сайт тоже сделан нами. Мы предлагаем вам много вариантов различной сложности и функциональности. Подробнее...
Prozarium CMS | Реклама, сотрудничество | Разработка, продажа сайтов

Для добавления вашего собственного контента, а также для загрузки текстов целиком, загрузки текстов без разбиения на страницы, загрузки книг без разбиения на тексты, для работы с закладками необходима авторизация. Если вы зарегистрированы на сайте, введите свой логин и пароль. Если нет, пожалуйста, пройдите на регистрацию

Опубликовано в: Клуб: c#
<--IT Информационные технологии
<--Бизнес по сферам деятельности
Клуб: Программное обеспечение
<--IT Информационные технологии
<--Бизнес по сферам деятельности


CCM (C# Collections Mapper)
/pterodactilus vulgaris/

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.

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>
static ICollectionManager Mapper = new CollectionManager();
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)

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>

          //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
        // indicate that instances are stored on the heap

= Repository.AllBooks;     

How to get the collection

public ICollection<Book> GetBooks(string connAlias, Guid creatorID)
parameters = new Hashtable();     
    parameters["@CreatorID"] = creatorID;     
mapper.GetCollection<Book> (
AllBooks,      // destination collection            
,            //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)
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)
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


IF @BookID is not null

  -- delete specific book
   Delete FROM Books
   WHERE (BookID = @BookID)


Calling this stored procedure

Hashtable parameters = new Hashtable();
parameters["@BookID"] = bookID;
errMsg = string.Empty;
.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


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)
.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
int BookID { get; set; }
double Rating { get; set; }
DateCreation { get; set; }
string Caption { get; set; }
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

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
Book(int id_key, int sortID)
        : base(
Hashtable() { { "sp_par1", P_1 }, .. , { "sp_parN", P_N} },

    // The set of fields relevant for the entity is defined in the procedure 

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);     
            // 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());        
(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


IF @BookID is not null   
     -- specific book    
        SELECT BookID, Caption, CreatorID, DateCreation, PhotoID, Published, Position, Modified, Rating, ForeignBook, TextID
        FROM Books
        WHERE (BookID = @BookID)
        -- all books
            if @ReturnAtOnce is not null                
                 SELECT BookID, Caption, CreatorID, DateCreation, PhotoID, Published, Position, Modified, Rating, ForeignBook, TextID                
                 FROM Books
                 ORDER BY DateCreation DESC
                 SELECT BookID
                 FROM Books                 
                 ORDER BY DateCreation DESC

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);
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)


   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

And how to execute it

Hashtable parameters = new Hashtable();
parameters["@Caption"] = "My new book";
parameters["@CreatorID"] = Guid.NewGuid();
int newBookId = -1; 

.TryParse(sqlManager.SQLExecuteScalar("CmsDb", "AddBook", parameters).ToString(), out newBookId);
Catch (Exception ex)
(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;
 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

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)

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