首页 > > 网络编程 > 其它 >

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

2018-06-17 22:39:46来源：未知阅读 ()

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(0, rootPath.LastIndexOf('\\'));
            rootPath = rootPath.Substring(0, 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 = 0; i < 10; 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 + 1) + "条，插入结果：" + 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 = 0;
            var resultSelect2 = sqlExe.QueryPage<Site>(sqlSelect, 1, 4, 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("================测试通过==================");
        }

View Code

驱动配置文件

<?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 0)) 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>