View on GitHub

MicroDBHelper

A friendly interface library to use SQLHelper

icon

MicroDBHelper

This is a friendly interface library to use SQLHelper.

I know there are a lot of database libraries in the world and they are incredibly awesome, but I decided to create and maintain our own library to match our demanding needs:

Currently it is focus on MsSql databases (not support MySql at this moment).

The MsSqlHepler.cs sourcecode was taken from the Microsoft .NET Data Access Application Block v2.0 and improved to support the latest .net Frameworks and asynchronous programming.

Note please: Asynchronous methods(async/await) of this library was only supported on .Net framework 4.5 and +.

Generated for Framework Version:

Usage

Initialization

Firstly, set the connection string once, when your assembly is initing.

var dbConnection = string.Format("Data Source={0};uid={1};password={2};database={3}", 
                                  DB_IP, 
                                  DB_USER, DB_PSW, 
                                  DB_DATABASE);
MicroDBHelper.SetConnection(dbConnection);

Then the subsequent operations, the library will use the specified connection string for other interfaces.

(Need to support multiple connection ? Okay,the library support it as well. Search by keywords of “multiple connection support” in this document, then you can get it o(∩_∩)o )

Query Data

The library offer different overloaded methods for developer to easy pass parameters.Like CommandType(text / storedProcedure), use transaction or not, and so on.

Execute

The library offer different overloaded methods for these.

Transaction

      using (var tran = MicroDBHelper.UseTransaction(!IsolationLevel!)) 
      {
         MicroDBHelper.ExecuteNonQuery(sql_INSERT, paramValues, tran);
         MicroDBHelper.ExecuteNonQuery(sql_UPDATE, paramValues, tran);
         tran.MarkSuccess();
      }

As you see, you need to call MicroDBHelper.UseTransaction to get the transaction object, and write your code inside using block. Please note, Call the MarkSuccess() method at last.

When the context leave the using block, the transaction will decide to run COMMIT or ROLLBACK automatically:

Additionally, the MicroDBTransaction in addition to maintaining the operation of atomic, it can also be used to share the connection. For example, you want to read two table in the same time and want’t to connecte to DB twice, then you can use MicroDBTransaction to get result in one connection:

      using (var tran = MicroDBHelper.UseTransaction(IsolationLevel.ReadUncommitted)) 
      {
         var retA = MicroDBHelper.ExecuteDataTable(sql_SELECT_A, paramValues, tran);
         var retB = MicroDBHelper.ExecuteDataTable(sql_SELECT_A, paramValues, tran);
      }

Use the low level of Isolation,pass same MicroDBTransaction Object and with no need for call MarkSuccess().

BTW, the IsolationLevel value, you can choose according to the actual situation. It is a .Netframework enum from System.Data. It’s important to choose when you design your High-performance data access layer.

You can get authoritative explanations about isolation level via Google, however I still hope to introduce them to you via my insights:

Isolation Level Dirty Read Non Repeatable Read Phantom
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Snapshot No No No

In brief, a lower isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects, such as dirty reads or lost updates, that users might encounter. Conversely, a higher isolation level reduces the types of concurrency effects that users might encounter, but requires more system resources and increases the chances that one transaction will block another.
Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level :)

Multiple Connection Support

In a specific application scenario, if you need to use multiple different databases, you can set multiple Connection String with the Alias Name. All the interface is offer the overloaded methods to use Alias Name. For example:

      MicroDBHelper.SetConnection(Connection1, "DB-Common");
      MicroDBHelper.SetConnection(Connection2, "DB-Private");

      using (var tran = MicroDBHelper.UseTransaction(IsolationLevel.ReadUncommitted, "DB-Common"))
      {
          MicroDBHelper.ExecuteDataTable("select * from User", null, "DB-Common");
      }

BTW, it is advise the use CONST STRING to keep and use your Alias Name more easy use and maintainable. o(∩_∩)o

Language

This library was written with Simplified Chinese code comments as first. Therefore in the source code, you will see there are two language( English and Simplified Chinese ) of xml summary comments for all PUBLIC interface.

For build and output expectant language version, you can just change the context in Directory.Build.props file:
snapshot
snapshot

Download compiled binary file

If you needn’t to got the code and bulid by yourself for the moment, I also offer the newest compiled file in the BUILD directiory for your convenience.

Build Directory

NuGet

Choose your expected language package :

Expansion packs

MicroDBHelper itself is focus on how to make SqlHelper lightweight and friendly.

In order to enjoy your development, you may need some related features (such as Entity Conversion, Paging Query , etc.), then you can get these useful Optional Expansion Packs .

LICENSE

This library is open-source and non-restrictive for personal or businesses.

However, I choose LGPL not MIT, in order to make this library grow up.
If you really have idea or requirement to modify the source code to let the interface more and more friendly, I am willing to see it! I am happy to recive your pull request or you can fork a new repository, instead of Closed source. Just share your bright ideas to developers include me o(∩_∩)o