WangSql 3.0源码共享(WangSql 1.0重大升级到3.0)

时间：2023-03-08 23:02:33
WangSql 3.0源码共享(WangSql 1.0重大升级到3.0)

WangSql 1.0博文阅读： http://www.cnblogs.com/deeround/p/6204610.html

基于1.0做了以下重大改动：

1.多数据实现方式调整

2.使用EmitMapper代替反射实现DbDataReader转实体

3.源码在文章最后提供下载地址

核心功能介绍

多数据支持，采用配置文件形式实现多种数据实现，只需要指定数据库驱动即可实现所支持所有数据库任意切换。

SQL自行管理，大大提高SQL可控性以及SQL性能优化。

高效实体转换，集成EmitMapper来实现DbDataReader转实体。

实现分页查询，可自行重写生成分页语句方法和获取总数语句方法。

接口列表（ISqlExe）

原始方法

int ExecuteNonQuery(string sql);

int ExecuteNonQuery(string sql, IDbDataParameter par);

int ExecuteNonQuery(string sql, IDbDataParameter[] par);

IDataReader ExecuteReader(string sql);

IDataReader ExecuteReader(string sql, IDbDataParameter par);

IDataReader ExecuteReader(string sql, IDbDataParameter[] par);

DataTable ExecuteTable(string sql);

DataTable ExecuteTable(string sql, IDataParameter par);

DataTable ExecuteTable(string sql, IDataParameter[] par);

基本方法

int NonQuery(string sql);

int NonQuery(string sql, object para);

T QueryObject<T>(string sql);

T QueryObject<T>(string sql, object para);

IList<T> QueryList<T>(string sql);

IList<T> QueryList<T>(string sql, object para);

DataTable QueryTable(string sql);

DataTable QueryTable(string sql, object para);

分页方法

IList<T> QueryPage<T>(string sql, int pageIndex, int pageSize, out int totalCount);

DataTable QueryPage(string sql, int pageIndex, int pageSize, out int totalCount);

IList<T> QueryPage<T>(string sql, object para, int pageIndex, int pageSize, out int totalCount);

DataTable QueryPage(string sql, object para, int pageIndex, int pageSize, out int totalCount);

string CreateSqlPageTag(SqlRequest sqlRequest, int pageIndex, int pageSize);

string CreateSqlCountTag(SqlRequest sqlRequest);

事务方法

bool Transaction(Hashtable sqlList);

IMyTransaction BeginTransaction();

其他方法

SqlRequest GetRunSqlRequest(string sql);

SqlRequest GetRunSqlRequest(string sql, object para);

事务接口列表（IMyTransaction）

void Commit();

void Rollback();

int NonQuery(string sql);

int NonQuery(string sql, object para);

T QueryObject<T>(string sql);

T QueryObject<T>(string sql, object para);

IList<T> QueryList<T>(string sql);

IList<T> QueryList<T>(string sql, object para);

DataTable QueryTable(string sql);

DataTable QueryTable(string sql, object para);

具体使用方法参考1.0或者下载源码，里面有测试项目。

WangSql 1.0博文阅读： http://www.cnblogs.com/deeround/p/6204610.html

测试代码

        static void TestStart()

        {

            Write("================测试开始==================");

            string rootPath = System.AppDomain.CurrentDomain.BaseDirectory;

            rootPath = rootPath.TrimEnd('\\');

            rootPath = rootPath.Substring(, rootPath.LastIndexOf('\\'));

            rootPath = rootPath.Substring(, rootPath.LastIndexOf('\\'));

            string conn = string.Format(@"Data Source={0}\db3;", rootPath);

            var sqlExe = new TestSqlExe("SQLite3", conn);

            sqlExe.Factory.ProviderConfig(rootPath + "\\providers.config");

            ////Sql Server

            //conn = "Data Source = WIN-L8TOD2M74CD\\SQLEXPRESS;Initial Catalog = test;User Id = sa;Password = 123456;";

            //sqlExe = new TestSqlExe("sqlServer1.1", conn);

            Write("初始化完成");

            string id = string.Empty;

            #region insert

            Write("*********** insert 开始 *************");

            for (int i = ; i < ; i++)

            {

                var site = new Site()

                {

                    Id = Guid.NewGuid().ToString("N"),

                    Name = "site1",

                    DomainEWQ = "www.baidu.com" + Guid.NewGuid().ToString("N"),

                    Area = "web",

                    Remark = "测试"

                };

                id = site.Id;

                string sqlInsert = "insert into Site(Id,Name,Domain,Area,Remark) values('$Id$',#Name#,'$Domain$',#Area#,#Remark#)";

                var resultInsert = sqlExe.NonQuery(sqlInsert, site);

                Write("插入第" + (i + ) + "条，插入结果：" + resultInsert);

            }

            Write("*********** insert 通过 *************");

            #endregion

            #region update

            Write("*********** update 开始 *************");

            string sqlUpdate = "update Site set Remark=#Remark# where Id=#Id#";

            var site1 = new Site()

            {

                Remark = "测试1",

                Id = id

            };

            var resultUpdate = sqlExe.NonQuery(sqlUpdate, site1);

            Write("更新结果：" + resultUpdate);

            Write("*********** update 通过 *************");

            #endregion

            #region select

            Write("*********** select 开始 *************");

            string sqlSelect = "select * from Site";

            var resultSelect = sqlExe.QueryObject<Site>(sqlSelect);

            Write("查询结果：" + resultSelect.DomainEWQ);

            var resultSelect1 = sqlExe.QueryList<Site>(sqlSelect);

            Write("查询结果：" + resultSelect1.Count);

            int count = ;

            var resultSelect2 = sqlExe.QueryPage<Site>(sqlSelect, , , out count);

            Write("分页查询结果：" + resultSelect2.Count + "，总数：" + count);

            Write("*********** select 通过 *************");

            #endregion

            #region trans

            Write("*********** trans 开始 *************");

            using (var trans = sqlExe.BeginTransaction())

            {

                try

                {

                    string sqlTrans1 = "update Site set Remark=#Remark# where Id=#Id#";

                    var site2 = new Site()

                    {

                        Remark = "测试2",

                        Id = id

                    };

                    var result1 = trans.NonQuery(sqlTrans1, site1);

                    Write("trans NonQuery1结果:" + result1);

                    string sqlTrans2 = "update Site set Name=#Name# where Id=#Id#";

                    var site3 = new Site()

                    {

                        Name = "SASASA3",

                        Id = id

                    };

                    var result2 = trans.NonQuery(sqlTrans2, site1);

                    Write("trans NonQuery2结果:" + result2);

                    trans.Commit();

                }

                catch

                {

                    trans.Rollback();

                }

            }

            Write("*********** trans 通过 *************");

            #endregion

            #region delete

            Write("*********** delete 开始 *************");

            string sqlDelete = "delete from Site where Id=#Id#";

            var resultDelete = sqlExe.NonQuery(sqlDelete, id);

            Write("删除一条结果：" + resultDelete);

            string sqlDelete1 = "delete from Site";

            var resultDelete1 = sqlExe.NonQuery(sqlDelete1);

            Write("删除全部结果：" + resultDelete1);//SQLite3删除全部返回是0，不知道是不是这个驱动的BUG，其他数据库不会

            Write("*********** delete 通过 *************");

            #endregion

            Write("================测试通过==================");

        }

驱动配置文件

<?xml version="1.0" encoding="utf-8"?>

<providers>

  <provider

    name="sqlServer1.0"

    description="Microsoft SQL Server 7.0/2000, provider V1.0.3300.0 in framework .NET V1.0"

    enabled="false"

    assemblyName="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.SqlClient.SqlConnection"

    commandClass="System.Data.SqlClient.SqlCommand"

    parameterClass="System.Data.SqlClient.SqlParameter"

    parameterDbTypeClass="System.Data.SqlDbType"

    parameterDbTypeProperty="SqlDbType"

    dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

    commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"/>

  <provider

    name="sqlServer1.1"

    description="Microsoft SQL Server 7.0/2000, provider V1.0.5000.0 in framework .NET V1.1"

    enabled="false"

    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.SqlClient.SqlConnection"

    commandClass="System.Data.SqlClient.SqlCommand"

    parameterClass="System.Data.SqlClient.SqlParameter"

    parameterDbTypeClass="System.Data.SqlDbType"

    parameterDbTypeProperty="SqlDbType"

    dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

    commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"

    queryPageTag="select wang1.*

                  from(

                    select row_number() over(order by (select )) as rowno,wang.*

                    from

                    (

                      {sqlTag}

                    ) wang

                  ) wang1

                  where wang1.rowno between {pageStart} and {pageEnd}"/>

  <provider

    name="sqlServer2.0"

    enabled="false"

    description="Microsoft SQL Server 2005, provider V2.0.50215.0 in framework .NET V2.0"

    assemblyName="System.Data, Version=2.0.50215.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

    connectionClass="System.Data.SqlClient.SqlConnection"

    commandClass="System.Data.SqlClient.SqlCommand"

    parameterClass="System.Data.SqlClient.SqlParameter"

    parameterDbTypeClass="System.Data.SqlDbType"

    parameterDbTypeProperty="SqlDbType"

    dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

    commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"

    usePositionalParameters = "false"

    useParameterPrefixInSql = "true"

    useParameterPrefixInParameter = "true"

    parameterPrefix="@"/>

  <provider name="OleDb1.1"

    description="OleDb, provider V1.0.5000.0 in framework .NET V1.1"

    enabled="false"

    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OleDb.OleDbConnection"

    commandClass="System.Data.OleDb.OleDbCommand"

    parameterClass="System.Data.OleDb.OleDbParameter"

    parameterDbTypeClass="System.Data.OleDb.OleDbType"

    parameterDbTypeProperty="OleDbType"

    dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"

    commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="false"

    useParameterPrefixInParameter="false"

    parameterPrefix=""/>

  <provider

    name="Odbc1.1"

    description="Odbc, provider V1.0.5000.0 in framework .NET V1.1"

    enabled="false"

    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.Odbc.OdbcConnection"

    commandClass="System.Data.Odbc.OdbcCommand"

    parameterClass="System.Data.Odbc.OdbcParameter"

    parameterDbTypeClass="System.Data.Odbc.OdbcType"

    parameterDbTypeProperty="OdbcType"

    dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"

    commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="false"

    useParameterPrefixInParameter="false"

    parameterPrefix="@"/>

  <provider

    name="oracle9.2"

    description="Oracle, Oracle provider V9.2.0.401"

    enabled="false"

    assemblyName="Oracle.DataAccess, Version=9.2.0.401, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection"

    commandClass="Oracle.DataAccess.Client.OracleCommand"

    parameterClass="Oracle.DataAccess.Client.OracleParameter"

    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"

    parameterDbTypeProperty="OracleDbType"

    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"

    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="false"

    parameterPrefix=":"

    useDeriveParameters="false"/>

  <provider

    name="oracle10.1"

    description="Oracle, oracle provider V10.1.0.301"

    enabled="false"

    assemblyName="Oracle.DataAccess, Version=10.1.0.301, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection"

    commandClass="Oracle.DataAccess.Client.OracleCommand"

    parameterClass="Oracle.DataAccess.Client.OracleParameter"

    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"

    parameterDbTypeProperty="OracleDbType"

    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"

    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix=":"

    useDeriveParameters="false"

    queryPageTag="select wang1.*

                  from(

                    select rownum as rowno,wang.*

                    from

                    (

                      {sqlTag}

                    ) wang

                  ) wang1

                  where wang1.rowno between {pageStart} and {pageEnd}"/>

  <provider

    name="oracleClient1.0"

    description="Oracle, Microsoft provider V1.0.5000.0"

    enabled="false"

    assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OracleClient.OracleConnection"

    commandClass="System.Data.OracleClient.OracleCommand"

    parameterClass="System.Data.OracleClient.OracleParameter"

    parameterDbTypeClass="System.Data.OracleClient.OracleType"

    parameterDbTypeProperty="OracleType"

    dataAdapterClass="System.Data.OracleClient.OracleDataAdapter"

    commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="false"

    parameterPrefix=":"/>

  <provider

    name="ByteFx"

    description="MySQL, ByteFx provider V0.7.6.15073"

    enabled="false"

    assemblyName="ByteFX.MySqlClient, Version=0.7.6.15073, Culture=neutral, PublicKeyToken=f2fef6fed1732fc1" connectionClass="ByteFX.Data.MySqlClient.MySqlConnection"

    commandClass="ByteFX.Data.MySqlClient.MySqlCommand"

    parameterClass="ByteFX.Data.MySqlClient.MySqlParameter"

    parameterDbTypeClass="ByteFX.Data.MySqlClient.MySqlDbType"

    parameterDbTypeProperty="MySqlDbType"

    dataAdapterClass="ByteFX.Data.MySqlClient.MySqlDataAdapter"

    commandBuilderClass="ByteFX.Data.MySqlClient.MySqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"/>

  <provider

    name="MySql"

    description="MySQL, MySQL provider V1.0.5.13785"

    enabled="false"

    assemblyName="MySql.Data, Version=1.0.5.13785, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionClass="MySql.Data.MySqlClient.MySqlConnection"

    commandClass="MySql.Data.MySqlClient.MySqlCommand"

    parameterClass="MySql.Data.MySqlClient.MySqlParameter"

    parameterDbTypeClass="MySql.Data.MySqlClient.MySqlDbType"

    parameterDbTypeProperty="MySqlDbType"

    dataAdapterClass="MySql.Data.MySqlClient.MySqlDataAdapter"

    commandBuilderClass="MySql.Data.MySqlClient.MySqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="?"

    queryPageTag="select wang.* from ({sqlTag}) wang limit {pageIndex},{pageSize}"/>

  <provider name="SQLite3"

    description="SQLite, System.Data.SQLite provider V1.0.60.0"

    enabled="false"

    assemblyName="System.Data.SQLite, Version=1.0.60.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"

    connectionClass="System.Data.SQLite.SQLiteConnection"

    commandClass="System.Data.SQLite.SQLiteCommand"

    parameterClass="System.Data.SQLite.SQLiteParameter"

    parameterDbTypeClass="System.Data.DbType, System.Data"

    parameterDbTypeProperty="DbType"

    dataAdapterClass="System.Data.SQLite.SQLiteDataAdapter"

    commandBuilderClass="System.Data.SQLite.SQLiteCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"

    setDbParameterPrecision="false"

    setDbParameterScale="false"

    queryPageTag="select wang.* from ({sqlTag}) wang limit {pageIndex},{pageSize}"/>

  <provider name="Finisar"

    description="SQLite, SQLite.NET provider V0.21.1869.3794"

    enabled="false"

    assemblyName="SQLite.NET, Version=0.21.1869.3794, Culture=neutral, PublicKeyToken=c273bd375e695f9c"

    connectionClass="Finisar.SQLite.SQLiteConnection"

    commandClass="Finisar.SQLite.SQLiteCommand"

    parameterClass="Finisar.SQLite.SQLiteParameter"

    parameterDbTypeClass="System.Data.DbType, System.Data"

    parameterDbTypeProperty="DbType"

    dataAdapterClass="Finisar.SQLite.SQLiteDataAdapter"

    commandBuilderClass="Finisar.SQLite.SQLiteCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"

    setDbParameterPrecision="false"

    setDbParameterScale="false"/>

  <provider

    name="Firebird1.7"

    description="Firebird, Firebird SQL .NET provider V1.7.0.33200"

    enabled="false"

    assemblyName="FirebirdSql.Data.Firebird, Version=1.7.0.33200, Culture=neutral, PublicKeyToken=fa843d180294369d" connectionClass="FirebirdSql.Data.Firebird.FbConnection"

    commandClass="FirebirdSql.Data.Firebird.FbCommand"

    parameterClass="FirebirdSql.Data.Firebird.FbParameter"

    parameterDbTypeClass="FirebirdSql.Data.Firebird.FbDbType"

    parameterDbTypeProperty="FbDbType"

    dataAdapterClass="FirebirdSql.Data.Firebird.FbDataAdapter"

    commandBuilderClass="FirebirdSql.Data.Firebird.FbCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"/>

  <provider

    name="PostgreSql0.7"

    description="PostgreSql, Npgsql provider V0.7.0.0"

    enabled="false"

    assemblyName="Npgsql, Version=0.7.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

    connectionClass="Npgsql.NpgsqlConnection"

    commandClass="Npgsql.NpgsqlCommand"

    parameterClass="Npgsql.NpgsqlParameter"

    parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"

    parameterDbTypeProperty="NpgsqlDbType"

    dataAdapterClass="Npgsql.NpgsqlDataAdapter"

    commandBuilderClass="Npgsql.NpgsqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix=":"/>

  <provider

    name="PostgreSql0.7.1"

    description="PostgreSql, Npgsql provider V0.7.1.0"

    enabled="false"

    assemblyName="Npgsql, Version=0.7.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

    connectionClass="Npgsql.NpgsqlConnection"

    commandClass="Npgsql.NpgsqlCommand"

    parameterClass="Npgsql.NpgsqlParameter"

    parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"

    parameterDbTypeProperty="NpgsqlDbType"

    dataAdapterClass="Npgsql.NpgsqlDataAdapter"

    commandBuilderClass="Npgsql.NpgsqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix=":"/>

  <provider

    name="iDb2.10"

    description="IBM DB2 Provider, V 10.0"

    enabled="false"

    assemblyName="IBM.Data.DB2.iSeries, Version=10.0.0.0,Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26, Custom=null" connectionClass="IBM.Data.DB2.iSeries.iDB2Connection"

    commandClass="IBM.Data.DB2.iSeries.iDB2Command"

    parameterClass="IBM.Data.DB2.iSeries.iDB2Parameter"

    parameterDbTypeClass="IBM.Data.DB2.iSeries.iDB2DbType"

    parameterDbTypeProperty="iDB2DbType"

    dataAdapterClass="IBM.Data.DB2.iSeries.iDB2DataAdapter"

    commandBuilderClass="IBM.Data.DB2.iSeries.iDB2CommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="false"

    useParameterPrefixInParameter="false"

    parameterPrefix=""/>

  <provider

    name="Informix"

    description="Informix NET Provider, 2.81.0.0"

    enabled="false"

    assemblyName="IBM.Data.Informix, Version=2.81.0.0, Culture=neutral, PublicKeyToken=7c307b91aa13d208"

    connectionClass="IBM.Data.Informix.IfxConnection"

    commandClass="IBM.Data.Informix.IfxCommand"

    parameterClass="IBM.Data.Informix.IfxParameter"

    parameterDbTypeClass="IBM.Data.Informix.IfxType"

    parameterDbTypeProperty="IfxType"

    dataAdapterClass="IBM.Data.Informix.IfxDataAdapter"

    commandBuilderClass="IBM.Data.Informix.IfxCommandBuilder"

    usePositionalParameters = "true"

    useParameterPrefixInSql = "false"

    useParameterPrefixInParameter = "false"

    useDeriveParameters="false"/>

</providers>

源码下载：WangSql3.0源码_千年.rar