WangSql 1.0 source code sharing, wangsql1.0 source code

WangSql 1.0 source code sharing, wangsql1.0 source code
I. project background

Currently, many projects, including large and small projects, need to deal with databases. Naturally, database operations may occur in many places. However, the entire process of using traditional ADO. NET is a little troublesome, and output parameters must be manually converted to objects. Based on the above, we need an SQL Execution tool to simplify the appeal steps without losing control of SQL.

2. Core Technologies

The bottom layer is ADO. NET, a tool developed based on ADO. NET.

1. Support for multiple databases

2. Supports common SQL operations such as adding, deleting, modifying, and querying transactions

3. Support Parameter unification

4. diversified input parameters are supported and automatically converted to SQL parameters.

5. Supports diversified output parameters and automatically converts them to objects.

6. cache solution (unfinished)

7. Concurrency Control (unfinished)

8. Expression components supported (unfinished)

Iii. Detailed functions

The following content and source code are for reference only. Do not use the generated environment.

1. Multi-data support & expansion

Taking SQLite as an Example

 1     public class SQLiteConn : ISqlConn 2     { 3         public SQLiteConn(string connectionString) 4         { 5             this.DbType = DbType.SQLITE; 6             this.ConnectionString = connectionString; 7         } 8         public DbType DbType { get; set; } 9         public string ConnectionString { get; set; }10         public IDbConnection CreateDbConnection()11         {12             IDbConnection conn = new SQLiteConnection();13             conn.ConnectionString = this.ConnectionString;14             return conn;15         }16     }

 1     public class SQLiteFactory : SQLiteConn, IFactory 2     { 3         public SQLiteFactory(string connectionString) 4             : base(connectionString) 5         { 6             this.DbDataParameterPrefix = "@"; 7         } 8         public string DbDataParameterPrefix { get; set; } 9         public IDbDataParameter CreateDbDataParameter(string key, object value)10         {11             return new SQLiteParameter(key, value);12         }13         public string CreatePageSql(string sqlTag, int pageIndex, int pageSize)14         {15             string para = null;16             para = @"select wang.* from ({0}) wang limit {1},{2}";17             para = string.Format(para, sqlTag, pageSize * (pageIndex - 1), pageSize);18             return para;19         }20     }

 

Currently, Oracle, SqlServer, MySql, Access, and SQLite databases are supported. To add data support, you only need to implement ISqlConn and ISqlConn. The specific implementation is the same as above.

 

2. Test and operation code

1 var sqlExe = new WangSql. sqlExe ("SQLITE", conn); 2 3 Write ("SqlExe initialized"); 4 5 var site = new Site () 6 {7 Id = Guid. newGuid (). toString ("N"), 8 Name = "site1", 9 Domain = "www.baidu.com" + Guid. newGuid (). toString ("N"), 10 Area = "web", 11 Remark = "test" 12}; 13 14 // insert15 Write ("insert start "); 16 string sqlInsert = "insert into Site (Id, Name, Domain, Area, Remark) values (# Id #,# Name #,# Domain #,# Area #, # Remark #) "; 17 var resultInsert = sqlExe. nonQuery (sqlInsert, site); 18 Write ("insert end: Result:" + resultInsert); 19 20 // select21 Write ("select Start "); 22 string sqlSelect = "select * from Site"; 23 var resultSelect = sqlExe. queryObject <Site> (sqlSelect); 24 var resultSelect1 = sqlExe. queryList <Site> (sqlSelect); 25 int resultSelectIndex = new Random (). next (resultSelect1.Count); 26 Write ("select end: Result:" + resultSelect1 [resultSelectIndex]. remark); 27 28 // update29 Write ("START update"); 30 string sqlUpdate = "update Site set Remark = # Remark # where Id = # Id #"; 31 var site1 = new Site () 32 {33 Remark = "Test 1", 34 Id = resultSelect1 [resultSelectIndex]. id35}; 36 var resultUpdate = sqlExe. nonQuery (sqlUpdate, site1); 37 Write ("update ended: Result:" + resultUpdate); 38 39 // Transaction40 Write ("trans started "); 41 using (var trans = sqlExe. beginTransaction () 42 {43 string sqlTrans1 = "update Site set Remark = # Remark # where Id = # Id #"; 44 var site2 = new Site () 45 {46 Remark = "Test 2", 47 Id = resultSelect1 [resultSelectIndex]. id48}; 49 var result1 = trans. nonQuery (sqlTrans1, site1); 50 51 string sqlTrans2 = "update Site set Name = # Name # where Id = # Id #"; 52 var site3 = new Site () 53 {54 Name = "SASASA3", 55 Id = resultSelect1 [resultSelectIndex]. id56}; 57 var result2 = trans. nonQuery (sqlTrans2, site1); 58 59 trans. commit (); 60} 61 Write ("trans end ");

View Code

 

3. Usage and interface code

3.1 create SqlExe

var sqlExe = new WangSql.SqlExe("SQLITE", conn);

The first parameter is the database type, and the second parameter is the database connection string. The ISqlExe interface object instance is returned.

 

3.2 ISqlExe is the core class that provides all data operation methods. All operations call the methods in this interface.

 1     public interface IMySqlExe 2     { 3         int ExecuteNonQuery(string sql); 4         int ExecuteNonQuery(string sql, global::System.Data.IDbDataParameter par); 5         int ExecuteNonQuery(string sql, global::System.Data.IDbDataParameter[] par); 6         object ExecuteProcedure(string sql, global::System.Collections.Generic.Dictionary<string, object> inpara, ref global::System.Collections.Generic.Dictionary<string, object> outpara, global::WangSql.ExcuteType excuteType = ExcuteType.Query); 7         object ExecuteProcedure(string sql, global::System.Collections.Generic.Dictionary<string, object> inpara, global::WangSql.ExcuteType excuteType = ExcuteType.Query); 8         object ExecuteProcedure(string sql, global::WangSql.ExcuteType excuteType = ExcuteType.Query); 9         global::System.Data.DataTable ExecuteReader(string sql);10         global::System.Data.DataTable ExecuteReader(string sql, global::System.Data.IDataParameter par);11         global::System.Data.DataTable ExecuteReader(string sql, global::System.Data.IDataParameter[] par);12         int NonQuery(string sql);13         int NonQuery(string sql, object para);14         global::System.Collections.Generic.IList<T> QueryList<T>(string sql);15         global::System.Collections.Generic.IList<T> QueryList<T>(string sql, object para);16         T QueryObject<T>(string sql);17         T QueryObject<T>(string sql, object para);18         global::System.Data.DataTable QueryPage(string sql, int pageIndex, int pageSize, out int totalCount);19         global::System.Data.DataTable QueryPage(string sql, object para, int pageIndex, int pageSize, out int totalCount);20         global::System.Collections.Generic.IList<T> QueryPage<T>(string sql, int pageIndex, int pageSize, out int totalCount);21         global::System.Collections.Generic.IList<T> QueryPage<T>(string sql, object para, int pageIndex, int pageSize, out int totalCount);22         bool Transaction(global::System.Collections.Hashtable sqlList);23     }

View Code

1     public interface ISqlExe : IMySqlExe2     {3         IMyTransaction BeginTransaction();4     }

View Code

 

3.3 IMyTransaction is a transaction operation interface that contains this object in ISqlExe.

 1     public interface IMyTransaction : IDisposable 2     { 3         void Commit(); 4         int NonQuery(string sql); 5         int NonQuery(string sql, object para); 6         System.Collections.Generic.IList<T> QueryList<T>(string sql); 7         System.Collections.Generic.IList<T> QueryList<T>(string sql, object para); 8         T QueryObject<T>(string sql); 9         T QueryObject<T>(string sql, object para);10         void Rollback();11     }

View Code

 

4. SQL statements and transaction instructions

4.1 SQL statements

insert into Site(Id,Name,Domain,Area,Remark) values(#Id#,#Name#,#Domain#,#Area#,#Remark#)

The above is a complete insert SQL statement. # This is the support for parameter unification mentioned at the beginning. All parameters are wrapped in # For internal conversion.

4.2 transaction description

// Transaction Write ("trans start"); using (var trans = sqlExe. beginTransaction () {try {string sqlTrans1 = "update Site set Remark = # Remark # where Id = # Id #"; var site2 = new Site () {Remark = "Test 2", Id = resultSelect1 [resultSelectIndex]. id}; var result1 = trans. nonQuery (sqlTrans1, site1); string sqlTrans2 = "update Site set Name = # Name # where Id = # Id #"; var site3 = new Site () {Name = "SASASA3", Id = resultSelect1 [resultSelectIndex]. id}; var result2 = trans. nonQuery (sqlTrans2, site1); trans. commit ();} catch {trans. rollback () ;}} Write ("trans end ");

 

 

Iv. Summary

6. cache solution (unfinished)

7. Concurrency Control (unfinished)

8. Expression components supported (unfinished)

The above three items have not been completed yet. By the way, the code is simple and crude. Do not use them to generate environments. They are only used for learning and communication.

5. Download source code

Address: Download source code

..................

 

..................