Share my database access help class EasyDBUtility. dll C #

Let's talk about the reason for writing this help class. Previously, when writing project code, we found that a lot of code is required to read database data to the List set or to add or update parameterized SQL statements, especially when there are many table fields, it is very obvious that it is not only a physical activity, but also a low programming efficiency. After Google and Baidu had a lot of help classes, they found a problem: many custom methods of help classes have a lot of names, which is not conducive to beginners to quickly grasp, and the performance flexibility is not very good, as a result, I made a lot of improvements to the Database Help class that my teacher gave me in the past, making it easy to use and more efficient. When writing this help class, in fact, the ORM has already appeared, but I personally feel that the efficiency of ORM is not very good. In addition, for new employees, there are a lot of it, and I have to learn it again, this has a great impact on project development. One of the reasons for sharing this help class is that the Code is basically stable now (it has been used for two and a half years), and many new recruits are repeating my previous pains, including my classmates and colleagues. Now, we will introduce EasyDBUtility. I hope you can understand the limited personal language organization skills.

EasyDBUtility
1. Supports. net frmework3.0 and later versions;
2. Supports SqlServer, SqlServerCe, Access, Sqlite, MySql, Oracle, PostgreSQL, Sybase, Db2, and Firebird databases;
3. Support addition, deletion, modification, query, parameterization, storage process, transactions, distributed transactions, etc;
4. Multiple databases can be called at the same time. You only need to input different connection strings;
5. During addition, deletion, modification, and query, You can automatically parameterize the variables in SQL statements based on the input objects to reduce the compilation of parameterized code. This advantage is obvious when many fields are inserted and updated;
6. query the collection of objects from which data is bound to the List using Emit. The cached Emit is more efficient than the handwritten code for obtaining data through non-indexing, developers no longer need to write a large amount of code to read data from DataReader, which effectively saves development time;
7. the database connection is automatically closed after the SQL command is executed. Unless the AutoClose attribute is set to false, the help class implements the IDisposable interface, even if you forget to close the database, when GC is collected, unused database connections are also disabled;
8. simple and flexible to use, basically no self-created syntax or method name, CreateCommand (), AddParameter (), CommandText (), BeginTransaction (), Commit (), RollBack (), Close () executeNonQuery (), ExecuteReader (), ExecuteScalar (), and ExecuteDataAdapter. net frmework has similar usage of the built-in Database handler class. For the use of ExecuteMultipleReader (), refer to the small point (4) in the third point of the example for reading multiple query result sets;
9. The help class provides external attributes of database operation objects, allowing you to easily set database parameters, such as helper. Cmd. CommandTimeout = 60.

/// <Summary> // database connection object // </summary> public SqlConnection Conn {get {return _ conn ;}} /// <summary> /// execute the SQL command object /// </summary> public SqlCommand Cmd {get {return _ cmd;} set {_ cmd = value ;}} /// <summary> // transaction // </summary> public SqlTransaction Trans {get {return _ trans;} set {_ trans = value ;}}

Note: SQL statements must be passed in for calling. ORM and log functions are not supported. An exception is thrown directly up after an error is executed and must be processed at the outermost layer.

To be improved and disadvantages:
1. the query result set is automatically filled in to the object set. The Attribute name of the object must be the same as that of the database column (Case sensitivity can be ignored if implemented), and feature Attribute is not implemented;
2. the name of the parameterized variable in the SQL statement that is automatically parameterized during addition, deletion, modification, and query must be the same as the attribute name in the object, that is, it must be the same as the field name in the database (case-insensitive can be implemented ), feature Attribute not implemented;
3. Failed to assign values to the attributes of objects;
4. You still need to write a lot of code before writing the code generator;
5. A large number of tests are required for the help class;

 

Attributes and methods in the help class

Attribute:
Conn: External database connection object, such as SqlConnection
Cmd: External execution of SQL command objects, such as SqlCommand
Trans: External transaction object, such as SqlTransaction
AutoClose: Sets whether to automatically close the database connection. The database connection is automatically closed by default. If it is set to false, the database connection will not be closed after an SQL command is executed.

Method:
Void CreateCommand (string SQL ):
Initialize the Command object, which is equivalent to SqlCommand cmd = new SqlCommand (SQL, conn );
Void CreateStoredProcedureCommand (string name ):
Initialize the Command object that calls the stored procedure, which is equivalent to SqlCommand cmd = new SqlCommand (SQL, conn); cmd. CommandType = CommandType. StoredProcedure;
Void CommandText (string SQL ):
Void CommandText (string SQL, CommandType commandType ):
Set the SQL statement to be executed again. Set AutoClose to false when calling this method, and call Close () to Close the database link after all SQL commands are executed.
Void AddParameter (string name, object value ):
Void AddParameter (params IDataParameter [] param ):
Void AddParameter (string name, SqlDbType type, int size, object value, ParameterDirection direction ):
Void AddParameter (object data ):Automatically Set parameter values of parameterized SQL statements based on objects
Object GetValue (string name ):Obtain the Output or Return parameter value returned by calling the stored procedure.
Int ExecuteNonQuery ():Add, delete, and modify operations
Object ExecuteScalar ():Run the query command and return the value of the first column in the first row of the result set.
DataReader ExecuteReader ():Run the query command to return the DataReader result set and traverse it cyclically.
List <ClassName> ExecuteReader <ClassName> ():Run the query command to return the result set of the ClassName type and automatically read the result to the object.
List <ClassName> ExecuteMultipleReader <ClassName> ():Run the query command to return multiple result sets.
DataSet ExecuteDataAdapter ():Use SqlDataAdapter to obtain the data set of DataSet
Void BeginTransaction ():Enable transactions
Void Commit ():Transaction commit
Void RollBack ():Transaction rollback
Void Close ():Close database connection

 

Example

How to associate a database?
Configure the database connection string in web. config or App. config.
The default names are SqlConnectionString, SqlCeConnectionString, OleDbConnectionString, SqliteConnectionString, MySqlConnectionString, OracleConnectionString,
PostgreConnectionString, SybaseConnectionString, Db2ConnectionString, and FirebirdConnectionString. The naming rule for this name is to remove Helper from the help class name and add ConnectionString

<ConnectionStrings> <add name = "SqlConnectionString" connectionString = "database connection string"/> </connectionStrings>

 

The following is an example of accessing the SqlServer2005 database.

Define an object class User

public class User{    public int Id { get; set; }    public string Name { get; set; }    public bool Sex { get; set; }    public int NationId{get;set;}    public string NationName{get;set;}}

Without parameter addition, deletion, modification, and query, you can call the static class SimpleDBHelper <T>. T is the help class of the database to be called, such as SqlHelper;
You do not need to add, delete, modify, and query parameters with only one line of code. You only need 3-5 lines of code for most parameters;

1. Add
(1). No parameter Addition

Int result = SimpleDBHelper <SqlHelper>. ExecuteNonQuery ("insert into [User] values ('rationale ', 1 )");

(2) parameterized Addition

SqlHelper helper = new SqlHelper();helper.CreateCommand("insert into [User] values(@Name,@Sex)");helper.AddParameter("@Name",name);helper.AddParameter("@Sex",sex);helper.ExecuteNonQuery();

(3). Automatic parameterization based on input objects

Public bool Add (UserData data) {SqlHelper helper = new SqlHelper (); helper. createCommand ("insert into [User] values (@ Name, @ Sex)"); // can be used for addition, deletion, modification, and query, the premise is that the parameterized variable name in the SQL statement must be the same as the attribute name in the object, and there are cache object parameters in the case-insensitive // method. The second call will read from the cache, to improve the efficiency of automatic parameterization based on objects, refer to the parameter cache helper in Pet Shop 5.0. addParameter (data); helper. executeNonQuery ();}

Yes.

SqlHelper helper = new SqlHelper (); helper. CreateCommand ("insert into [User] values ('rationale ', 1)"); helper. ExecuteNonQuery ();

 

II. The code for deleting updates is similar to that for adding codes. Pass in the CreateCommand SQL statement as the update or delete statement.

 

Iii. Query
(1). No parameterized Query

List<User> list = SimpleDBHelper<SqlHelper>.ExecuteReader<User>("select * from [User]");

(2) parameterized Query

Public IList <User> Get (UserData data) {// do not write the connection string, in the web. sqlConnectionString SqlHelper helper = new SqlHelper (); helper must be configured in config. createCommand ("select * from [User] where Name = @ Name and Id = @ Id"); helper. addParameter (data); // use Emit to bind the data in DataReader to the object, which is highly efficient after caching, you do not need to write complex writes to read DataReader data to the code List of the object <User> list = helper. executeReader <User> (); return list;} public IList <User> Get (UserData data) {// to connect to multiple databases, you can directly specify different connection strings to SqlHelper helper = new SqlHelper (System. configuration. configurationManager. connectionStrings ["SQLConnectionString2"]. connectionString); helper. createCommand ("select * from [User] where Name = @ Name"); helper. addParameter (data); List <User> list = helper. executeReader <User> (); return list ;}

(3) If the read data has multiple columns (Multi-table join query) without corresponding entity classes, you can use the DataReader class to traverse the data.

SQLHelper helper = new SQLHelper (); helper. autoClose = false; // set that the database connection helper is not automatically closed after the SQL statement is executed. createCommand ("select * from [User]"); DataReader dr = helper. executeReader (); // var list = helper. executeReader <User> (); List <User> list = new List <User> (); while (dr. read () {User data = new User (); data. id = dr. getInt ("Id"); datat. name = dr. getString ("Name"); data. sex = dr. getBoolean ("Sex"); data. nationId = dr. getInt ("N AtionId "); // use the CommandText method helper. commandText ("select NationName from Nation where NationId =" + data. nationId); object obj = helper. executeScalar (); data. nationName = obj! = Null? Obj. ToString (): string. Empty; list. Add (data) ;}helper. Close (); // Close the database

(4) If you want to read multiple SQL statements and execute the returned result set at the same time, you can use. ExecuteMultipleReader <string> () to read the results, which can be used for Article paging.

string sql="select content from News where Id=12;select Author from Author where NewId=12;";SqlHelper helper = new SqlHelper();helper.CreateCommand(sql);List<List<string>> list = helper.ExecuteMultipleReader<string>();StringBuilder content = new StringBuilder();foreach (List<string> item in list){    foreach (string data in item)    {        content.Append(data);    }}

 

4. Calling paging stored procedures. The differences between common SQL commands and stored procedures are different in CreateStoredProcedureCommand.
If TableDirect is called, you can change the type by helper. CreateCommand (SQL); helper. Cmd. CommandType = CommandType. TableDirect;

Public static List <ClassName> GetAll <ClassName> (DataPage dataPage) {SqlHelper helper = new SqlHelper (); helper. createStoredProcedureCommand ("[proc_DataPagination]"); helper. addParameter ("@ Table", dataPage. tableName); helper. addParameter ("@ Fields", dataPage. fields); helper. addParameter ("@ Where", dataPage. where); helper. addParameter ("@ OrderBy", dataPage. orderBy); helper. addParameter ("@ CurrentPage", dataPage. currentPage); helper. addParameter ("@ PageSize", dataPage. pageSize); helper. addParameter ("@ GetCount", 0); helper. addParameter ("@ Count", SqlDbType. int, 0, null, ParameterDirection. output); // List of Output parameters <ClassName> list = helper. executeReader <ClassName> (); dataPage. recordSum = Convert. toInt32 (helper. getValue ("@ Count"); return list ;}

 

V. Transaction usage
(1). General transactions

SqlHelper helper = new SqlHelper (); helper. beginTransaction (); helper. createCommand ("delete from [user] where Id = @ Id"); for (int I = 0; I <ids. length; I ++) {if (I = 0) {helper. addParameter ("@ Id", ids [I]); helper. cmd. prepare ();} else {helper. cmd. parameters [0]. value = ids [I];} int result = helper. executeNonQuery (); if (result <= 0) {helper. rollBack (); break ;}// transaction commit helper. commit ();

 

(2) distributed transactions

// If you are prompted that MSDTC is unavailable, You need to enable the Service, control panel -- Management Tools -- service -- Distributed Transaction Coordinator
// Http://tangjun141.blog.163.com/blog/static/56462350200963073824318/
// Http://hi.baidu.com/mytot/item/2a2acd333fd1cf20b2c0c596
// Enable the remote http://www.cnblogs.com/haoyi0418/articles/1039543.html of the database
// Configure the http://hi.baidu.com/yyylajzwwfbegpe/item/1a426c0add1f596dd55a119f for Remote Transaction access
/*
-- Isolation level
Level 1 read uncommitted System. Transactions. IsolationLevel. ReadUncommitted -- uncommitted read

Level 2 read committed System. Transactions. IsolationLevel. ReadCommitted -- submit read

Level 3 repeatable read System. Transactions. IsolationLevel. RepeatableRead -- repeatable

Level 4 serializable System. Transactions. IsolationLevel. Serializable -- serializable read
*/

TransactionOptions op = new TransactionOptions (); // op. isolationLevel = System. transactions. isolationLevel. readCommitted; // submit read op. isolationLevel = System. transactions. isolationLevel. repeatableRead; // submit read using (TransactionScope scope = new TransactionScope (transactionscospontion. required, op) {try {SimpleDBHelper <SqlHelper>. executeNonQuery ("insert into [User] values ('lily', 1)"); SqlHelper helper2 = new SqlHelper (System. configuration. configurationManager. connectionStrings ["SqlConnectionString2"]. connectionString); // helper2.CreateCommand ("insert into [User] values ('rationale 2', 1 )"); // successful data helper2.CreateCommand ("insert into [User] values (affair)"); // insert an error. If adding fails and no data exists in the table above, the distributed transaction is successfully helper2.ExecuteNonQuery (); scope. complete (); // submit} catch {} MessageBox. show ("finished ");

Help class reference:
1. Timeout
2. http://automapper.codeplex.com/,automappersource project, very powerful
3. Refer to DBUtitlity in Pet Shop 5.0, an open-source Microsoft Project.

If you have good suggestions or bugs, please send an email to your qin_shili@qq.com or to my blog posts

 

Source code download

Http://pan.baidu.com/s/1hq3ijkw

Unzip the password: shili