SQL 横转竖 、竖专横 （转载）
普通行列转换
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
/*
普通行列转换
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/
-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
createtable tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insertinto tb(Name , Subject , Result) values('张三' , '语文' , 74)
insertinto tb(Name , Subject , Result) values('张三' , '数学' , 83)
insertinto tb(Name , Subject , Result) values('张三' , '物理' , 93)
insertinto tb(Name , Subject , Result) values('李四' , '语文' , 74)
insertinto tb(Name , Subject , Result) values('李四' , '数学' , 84)
insertinto tb(Name , Subject , Result) values('李四' , '物理' , 94)
go
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when'语文'then result else0end) 语文,
max(case subject when'数学'then result else0end) 数学,
max(case subject when'物理'then result else0end) 物理
from tb
groupby name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare@sqlvarchar(8000)
set@sql='select Name as '+'姓名'
select@sql=@sql+' , max(case Subject when '''+ Subject +''' then Result else 0 end) ['+ Subject +']'
from (selectdistinct Subject from tb) as a
set@sql=@sql+' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/
-------------------------------------------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when'语文'then result else0end) 语文,
max(case subject when'数学'then result else0end) 数学,
max(case subject when'物理'then result else0end) 物理,
cast(avg(result*1.0) asdecimal(18,2)) 平均分,
sum(result) 总分
from tb
groupby name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare@sql1varchar(8000)
set@sql1='select Name as '+'姓名'
select@sql1=@sql1+' , max(case Subject when '''+ Subject +''' then Result else 0 end) ['+ Subject +']'
from (selectdistinct Subject from tb) as a
set@sql1=@sql1+' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/
droptable tb
---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/
createtable tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insertinto tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insertinto tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
select*from
(
select 姓名 as Name , Subject ='语文' , Result = 语文 from tb1
unionall
select 姓名 as Name , Subject ='数学' , Result = 数学 from tb1
unionall
select 姓名 as Name , Subject ='物理' , Result = 物理 from tb1
) t
orderby name , case Subject when'语文'then1when'数学'then2when'物理'then3when'总分'then4end
--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/
select*from
(
select 姓名 as Name , Subject ='语文' , Result = 语文 from tb1
unionall
select 姓名 as Name , Subject ='数学' , Result = 数学 from tb1
unionall
select 姓名 as Name , Subject ='物理' , Result = 物理 from tb1
unionall
select 姓名 as Name , Subject ='平均分' , Result =cast((语文 + 数学 + 物理)*1.0/3asdecimal(18,2)) from tb1
unionall
select 姓名 as Name , Subject ='总分' , Result = 语文 + 数学 + 物理 from tb1
) t
orderby name , case Subject when'语文'then1when'数学'then2when'物理'then3when'平均分'then4when'总分'then5end
droptable tb1
转载来源:https://www.cnblogs.com/luowende2012/archive/2012/07/19/2599436.html
使用Dapper.Contrib 开发.net core程序，兼容多种数据库
关于Dapper的介绍，我想很多人都对它有一定的了解，这个类似一个轻型的ORM框架是目前应用非常火的一个东西，据说各方面的性能都不错，而且可以支持多种数据库，在开始介绍这个文章之前，我花了不少功夫来学习了Dapper 的相关使用。Dapper.Contrib是对Dapper的进一步封装，使对象的基本增删改查等操作进一步简化，我做了一个案例使用Dapper.Contrib 开发.net core程序，测试它对多种数据库的处理。
1、Dapper.Contrib的使用
前面介绍过，Dapper.Contrib是对Dapper的进一步封装，使对象的基本增删改查等操作进一步简化。
它主要是通过特性映射的方式实现自定义类和数据库之间的关系处理，如下是实体类的定义信息。
[Table("T_Customer")]
public class CustomerInfo
{
[ExplicitKey]//非自增长的用此标识
public virtual string ID { get; set; }
public virtual string Name { get; set; }
public virtual int Age { get; set; }
public virtual string Creator { get; set; }
public virtual DateTime CreateTime { get; set; }
}
Dapper.Contrib的所有实体配置选项
- Table：指定实体对应地数据库表名，如果类名和数据库表名不同，需要设置（如案例所示）
- Key：指定此列为自动增长主键
- ExplicitKey：指定此列为非自动增长主键（例如guid，字符串列）
- Computed：计算属性，此列不作为更新
- Write：指定列是否可写
通过定义好实体类和数据库表的映射关系，就可以通过强类型处理相关的接口了，如下所示。
T Get<T>(id); IEnumerable<T> GetAll<T>(); int Insert<T>(T obj); int Insert<T>(Enumerable<T> list); bool Update<T>(T obj); bool Update<T>(Enumerable<T> list); bool Delete<T>(T obj); bool Delete<T>(Enumerable<T> list); bool DeleteAll<T>();
这样通过映射指定表名或者字段信息后，就可以知道类和表之间的关系，可以封装对应的强类型处理接口了。
2、Dapper.Contrib 开发.net core程序
我们创建一个空白的.net core程序框架后，就在它的基础上做一些Dapper的数据库测试。
首先为了考虑多数据库的处理，我们需要创建一个配置文件，并可以动态配置不同的数据库，配置文件appSettings.json如下所示。
上面我配置了多种数据库的连接字符串，并且通过动态指定节点名称和数据库类型，来实现对项目指向不同数据库的访问。
例如我们准备需要让Dapper支持我们常见的数据库类型，如下定义数据库类型。
/// <summary>
/// 数据库类型定义
/// </summary>
public enum DatabaseType
{
SqlServer, //SQLServer数据库
MySql, //Mysql数据库
Npgsql, //PostgreSQL数据库
Oracle, //Oracle数据库
Sqlite, //SQLite数据库
DB2 //IBM DB2数据库
}
对于不同的数据库信息，我们需要根据不同的配置连接字符串，并创建对应的数据库连接对象供Dapper使用，如对于SQLServer的数据库，那么创建的是SqlConnection对象，对于Mysql，创建的是MySqlConnection连接对象，对于PostgreSQL对应的是NpgsqlConnection，以此类推。而Dapper则通过对连接对象的扩展实现了多种数据请求。
对于多数据库的支持，我们需要统一解析配置内容appSetting.json的内容，并返回不同数据库的连接对象，如下是连接工厂的统一处理方式，通过 CreateConnection() 返回配置的连接对象。
/// <summary>
/// 数据库连接辅助类
/// </summary>
public class ConnectionFactory
{
/// <summary>
/// 转换数据库类型
/// </summary>
/// <param name="databaseType">数据库类型</param>
/// <returns></returns>
private static DatabaseType GetDataBaseType(string databaseType)
{
DatabaseType returnValue = DatabaseType.SqlServer;
foreach (DatabaseType dbType in Enum.GetValues(typeof(DatabaseType)))
{
if (dbType.ToString().Equals(databaseType, StringComparison.OrdinalIgnoreCase))
{
returnValue = dbType;
break;
}
}
return returnValue;
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public static IDbConnection CreateConnection()
{
IDbConnection connection = null;
//获取配置进行转换
var type = AppConfig.GetConfig("ComponentDbType");
var dbType = GetDataBaseType(type);
//DefaultDatabase 根据这个配置项获取对应连接字符串
var database = AppConfig.GetConfig("DefaultDatabase");
if (string.IsNullOrEmpty(database))
{
database = "sqlserver";//默认配置
}
var strConn = AppConfig.Configuration.GetConnectionString(database);
switch (dbType)
{
case DatabaseType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(strConn);
break;
case DatabaseType.MySql:
connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
break;
case DatabaseType.Npgsql:
connection = new Npgsql.NpgsqlConnection(strConn);
break;
case DatabaseType.Sqlite:
connection = new SQLiteConnection(strConn);
break;
case DatabaseType.Oracle:
connection = new Oracle.ManagedDataAccess.Client.OracleConnection(strConn);
//connection = new System.Data.OracleClient.OracleConnection(strConn);
break;
case DatabaseType.DB2:
//connection = new System.Data.OleDb.OleDbConnection(strConn);
break;
}
return connection;
}
}
有了数据库对象工厂，我们的配置就可以动态化了。
下面我们来看看，获得这些连接对象后，如何通过Dapper.Contrib来获取对应的对象了，下面的类是常规的对数据库信息的处理，包括常规的增删改查等基础接口。
/// <summary>
/// 常规的数据访问层
/// </summary>
public class Customer
{
public IDbConnection Connection
{
get
{
var connection = ConnectionFactory.CreateConnection();
connection.Open();
return connection;
}
}
public IEnumerable<CustomerInfo> GetAll()
{
using (IDbConnection dbConnection = Connection)
{
return dbConnection.GetAll<CustomerInfo>();
//return dbConnection.Query<CustomerInfo>("SELECT * FROM T_Customer");
}
}
public CustomerInfo FindByID(string id)
{
using (IDbConnection dbConnection = Connection)
{
return dbConnection.Get<CustomerInfo>(id);
//string query = "SELECT * FROM T_Customer WHERE ID = @Id";
//return dbConnection.Query<CustomerInfo>(query, new { Id = id }).FirstOrDefault();
}
}
public bool Insert(CustomerInfo info)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Insert(info) > 0;
result = true;
//string query = "INSERT INTO T_Customer (ID, Name, Age, Creator, CreateTime)"
// + " VALUES(@ID, @Name, @Age, @Creator, @CreateTime)";
//result = dbConnection.Execute(query, info) > 0;
}
return result;
}
public bool Update(CustomerInfo prod)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Update(prod);
//string query = "UPDATE T_Customer SET Name = @Name,"
// + " Age = @Age, Creator= @Creator, CreateTime=@CreateTime"
// + " WHERE ID = @ID";
//result = dbConnection.Execute(query, prod) > 0;
}
return result;
}
public bool Delete(string id)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Delete(new CustomerInfo { ID = id });
//string query = "DELETE FROM T_Customer WHERE ID = @Id";
//result = dbConnection.Execute(query, new { ID = id }) > 0;
}
return result;
}
public bool DeleteAll()
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.DeleteAll<CustomerInfo>();
//string query = "DELETE FROM T_Customer WHERE ID = @Id";
//result = dbConnection.Execute(query, new { ID = id }) > 0;
}
return result;
}
}
其中的备注部分的代码是等同于上面的执行代码的，是Dapper 的SQL版本的一种处理方式。
我们看到，对于Customer表来说，使用对象的接口处理，我们已经隔离了很多硬编码的SQL处理，不过我们还可以对它进行进一步的优化处理。
我们定义一个通用的BaseDAL来剥离常规的增删改查处理，并且把同步和异步的操作分来两个文件来管理，同步处理的基类如下代码所示。
/// <summary>
/// 数据库访问基类
/// </summary>
/// <typeparam name="T">实体类类型</typeparam>
public partial class BaseDAL<T> where T : class
{
/// <summary>
/// 对象的表名
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 主键属性对象
/// </summary>
public PropertyInfo PrimaryKey { get; set; }
public BaseDAL()
{
this.TableName = EntityHelper.GetTableName(typeof(T));
this.PrimaryKey = EntityHelper.GetSingleKey<T>();
}
/// <summary>
/// 数据库连接
/// </summary>
protected IDbConnection Connection
{
get
{
var connection = ConnectionFactory.CreateConnection();
connection.Open();
return connection;
}
}
/// <summary>
/// 返回数据库所有的对象集合
/// </summary>
/// <returns></returns>
public IEnumerable<T> GetAll()
{
using (IDbConnection dbConnection = Connection)
{
return dbConnection.GetAll<T>();
}
}
/// <summary>
/// 查询数据库,返回指定ID的对象
/// </summary>
/// <param name="id">主键的值</param>
/// <returns></returns>
public T FindByID(object id)
{
using (IDbConnection dbConnection = Connection)
{
return dbConnection.Get<T>(id);
}
}
/// <summary>
/// 插入指定对象到数据库中
/// </summary>
/// <param name="info">指定的对象</param>
/// <returns></returns>
public bool Insert(T info)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
dbConnection.Insert(info);
result = true;
}
return result;
}
/// <summary>
/// 插入指定对象集合到数据库中
/// </summary>
/// <param name="list">指定的对象集合</param>
/// <returns></returns>
public bool Insert(IEnumerable<T> list)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Insert(list) > 0;
}
return result;
}
/// <summary>
/// 更新对象属性到数据库中
/// </summary>
/// <param name="info">指定的对象</param>
/// <returns></returns>
public bool Update(T info)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Update(info);
}
return result;
}
/// <summary>
/// 更新指定对象集合到数据库中
/// </summary>
/// <param name="list">指定的对象集合</param>
/// <returns></returns>
public bool Update(IEnumerable<T> list)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Update(list);
}
return result;
}
/// <summary>
/// 从数据库中删除指定对象
/// </summary>
/// <param name="info">指定的对象</param>
/// <returns></returns>
public bool Delete(T info)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Delete(info);
}
return result;
}
/// <summary>
/// 从数据库中删除指定对象集合
/// </summary>
/// <param name="list">指定的对象集合</param>
/// <returns></returns>
public bool Delete(IEnumerable<T> list)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.Delete(list);
}
return result;
}
/// <summary>
/// 根据指定对象的ID,从数据库中删除指定对象
/// </summary>
/// <param name="id">对象的ID</param>
/// <returns></returns>
public bool Delete(object id)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
string query = string.Format("DELETE FROM {0} WHERE {1} = @id", TableName, PrimaryKey.Name);
var parameters = new DynamicParameters();
parameters.Add("@id", id);
result = dbConnection.Execute(query, parameters) > 0;
}
return result;
}
/// <summary>
/// 从数据库中删除所有对象
/// </summary>
/// <returns></returns>
public bool DeleteAll()
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
result = dbConnection.DeleteAll<T>();
}
return result;
}
}
异步类的代码如下所示。
/// <summary>
/// 数据库访问基类
/// </summary>
/// <typeparam name="T">实体类类型</typeparam>
public partial class BaseDAL<T> where T : class
{
/// <summary>
/// 返回数据库所有的对象集合
/// </summary>
/// <returns></returns>
public virtual async Task<IEnumerable<T>> GetAllAsync()
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.GetAllAsync<T>();
}
}
/// <summary>
/// 查询数据库,返回指定ID的对象
/// </summary>
/// <param name="id">主键的值</param>
/// <returns></returns>
public virtual async Task<T> FindByIDAsync(object id)
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.GetAsync<T>(id);
}
}
/// <summary>
/// 插入指定对象到数据库中
/// </summary>
/// <param name="info">指定的对象</param>
/// <returns></returns>
public virtual async Task<bool> InsertAsync(T info)
{
bool result = false;
using (IDbConnection dbConnection = Connection)
{
await dbConnection.InsertAsync(info);
result = true;
}
return await Task<bool>.FromResult(result);
}
/// <summary>
/// 插入指定对象集合到数据库中
/// </summary>
/// <param name="list">指定的对象集合</param>
/// <returns></returns>
public virtual async Task<bool> InsertAsync(IEnumerable<T> list)
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.InsertAsync(list) > 0;
}
}
/// <summary>
/// 更新对象属性到数据库中
/// </summary>
/// <param name="info">指定的对象</param>
/// <returns></returns>
public virtual async Task<bool> UpdateAsync(T info)
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.UpdateAsync(info);
}
}
/// <summary>
/// 更新指定对象集合到数据库中
/// </summary>
/// <param name="list">指定的对象集合</param>
/// <returns></returns>
public virtual async Task<bool> UpdateAsync(IEnumerable<T> list)
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.UpdateAsync(list);
}
}
/// <summary>
/// 从数据库中删除指定对象
/// </summary>
/// <param name="info">指定的对象</param>
/// <returns></returns>
public virtual async Task<bool> DeleteAsync(T info)
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.DeleteAsync(info);
}
}
/// <summary>
/// 从数据库中删除指定对象集合
/// </summary>
/// <param name="list">指定的对象集合</param>
/// <returns></returns>
public virtual async Task<bool> DeleteAsync(IEnumerable<T> list)
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.DeleteAsync(list);
}
}
/// <summary>
/// 根据指定对象的ID,从数据库中删除指定对象
/// </summary>
/// <param name="id">对象的ID</param>
/// <returns></returns>
public virtual async Task<bool> DeleteAsync(object id)
{
using (IDbConnection dbConnection = Connection)
{
string query = string.Format("DELETE FROM {0} WHERE {1} = @id", TableName, PrimaryKey.Name);
var parameters = new DynamicParameters();
parameters.Add("@id", id);
return await dbConnection.ExecuteAsync(query, parameters) > 0;
}
}
/// <summary>
/// 从数据库中删除所有对象
/// </summary>
/// <returns></returns>
public virtual async Task<bool> DeleteAllAsync()
{
using (IDbConnection dbConnection = Connection)
{
return await dbConnection.DeleteAllAsync<T>();
}
}
}
这样，我们如果需要增加一个如客户信息表的管理类，就很简单的继承基类就可以了，代码很少，但是增删改查接口一个也少不了。
/// <summary>
/// 继承基类对象管理
/// </summary>
public class CustomerDAL :BaseDAL<CustomerInfo>
{
}
为了测试一下数据访问层的处理接口，我创建了一个.net core的控制台程序进行测试，如下项目视图所示。
主要目的是确认数据处理的效果。
我们在Program.cs类里面增加相关的测试代码，为了简便和处理效果没有用UnitTest处理。
//创建管理对象，并测试接口
var customer = new CustomerDAL();
var list = customer.GetAll();
foreach (var item in list)
{
Console.WriteLine(item.ToJson());
var info = customer.FindByID(item.ID);
Console.WriteLine(info.ToJson());
Console.WriteLine();
}
//插入记录
var insertInfo = new CustomerInfo() { Name = "test", Age = 30, Creator = "test" };
var insertList = new List<CustomerInfo>() { insertInfo };
var flag = customer.Insert(insertList);
Console.WriteLine("插入操作" + (flag ? "成功" : "失败"));
Console.WriteLine("插入的新内容");
insertInfo = customer.FindByID(insertInfo.ID);
Console.WriteLine(insertInfo.ToJson());
Console.WriteLine("更新内容");
insertInfo.Name = "Test" + DateTime.Now.ToShortDateString();
flag = customer.Update(insertInfo);
Console.WriteLine("更新操作" + (flag ? "成功" : "失败"));
Console.WriteLine("更新的新内容");
insertInfo = customer.FindByID(insertInfo.ID);
Console.WriteLine(insertInfo.ToJson());
Console.WriteLine("删除内容");
flag = customer.Delete(insertInfo.ID);
Console.WriteLine("删除操作" + (flag ? "成功" : "失败"));
Console.WriteLine("所有内容");
list = customer.GetAll();
foreach (var item in list)
{
Console.WriteLine(item.ToJson());
Console.WriteLine();
}
Console.ReadLine();
测试Mysql、SQLite数据库同样没有问题
Mysql配置信息如下
处理的Mysql记录信息如下。
SQLite配置信息如下
处理SQLite数据信息如下
而在处理PostgreSQL的信息（配置节点npgsql里面）的时候，查询的主键好像和大小写有关系，导致插入记录出错。
而Oracle我采用的是Oracle.ManagedDataAccess.Core进行访问，由于我本地Oracle数据库侦听处理有点问题，因此没有测试成功，暂不予置评。
而对于数据库的支持问题，导致我重新审核一下是否采用Dapper.Contrib还是其他Dapper方式来构建数据库访问基类的问题，我需要兼容多种数据库的信息，并且能够尽可能的封装常规的增删改查等操作，其中目前的基类还没有加入更加复杂的查询操作，分页操作等功能，在解决这些困惑问题，才会继续考虑把底层支持的接口全部完善。
C# 读取PDF多级书签
在PDF中，书签作为一种导航的有效工具，能帮助我们快速地定位到文档中的指定段落。同时，书签也能让人对文档结构一目了然，在某种程度上也可作为目录使用。对于C#操作PDF中的书签，在上一篇文章中介绍了具体的如何添加书签、修改已有书签以及删除书签的操作，在本篇文章中，将介绍C#如何读取PDF中的多级书签。
工具
下载安装该类库后，注意在编辑代码时，在程序中添加引用Spire.Pdf.dll（如下图），dll文件可在安装路径下的Bin文件夹中获取。
示例代码（供参考）
源文档：
步骤1 ：加载文档
PdfDocument doc = new PdfDocument();
doc.LoadFromFile("test.pdf");
步骤2 ：获取文档中的书签（这里需要自定义方法来获取文档中的所有书签内容）
//获取文档的书签 PdfBookmarkCollection bookmarks = doc.Bookmarks; //声明一个可变字符串 StringBuilder stringbuilder = new StringBuilder(); //获取父书签和子书签 GetBookmarkTitle(bookmarks, stringbuilder);
自定义方法：
//自定义方法获取书签
static void GetBookmarkTitle(PdfBookmarkCollection bookmarks, StringBuilder stringbuilder)
{
//遍历书签
if (bookmarks.Count > 0)
{
foreach (PdfBookmark parentBookmark in bookmarks)
{
stringbuilder.AppendLine(parentBookmark.Title);
//获取书签
GetBookmarkTitle(parentBookmark, stringbuilder);
}
}
}
步骤 3 ：将书签写入到.txt文档
String fileName = "output.txt"; File.WriteAllText(fileName, stringbuilder.ToString());
完成代码后，调试程序，生成文档。下图是读取结果：
全部代码：
using Spire.Pdf;
using Spire.Pdf.Bookmarks;
using System;
using System.IO;
using System.Text;
namespace ReadBookmark_PDF
{
class Program
{
static void Main(string[] args)
{
//实例化PdfDocument类的对象，并加载含有多级书签的PDF文件
PdfDocument doc = new PdfDocument();
doc.LoadFromFile("test.pdf");
//获取文档的书签
PdfBookmarkCollection bookmarks = doc.Bookmarks;
//声明一个可变字符串
StringBuilder stringbuilder = new StringBuilder();
//获取父书签和子书签
GetBookmarkTitle(bookmarks, stringbuilder);
//声明txt文件，并将获得的多级书签写入到文件.txt
String fileName = "output.txt";
File.WriteAllText(fileName, stringbuilder.ToString());
}
//自定义方法获取书签
static void GetBookmarkTitle(PdfBookmarkCollection bookmarks, StringBuilder stringbuilder)
{
//遍历书签
if (bookmarks.Count > 0)
{
foreach (PdfBookmark parentBookmark in bookmarks)
{
stringbuilder.AppendLine(parentBookmark.Title);
//获取书签
GetBookmarkTitle(parentBookmark, stringbuilder);
}
}
}
}
}
本文完
Json.net日期格式化设置
Json.net默认的时间格式化后带T，不符合一般的业务要求，重新设置JSON.NET的默认日期格式化方式，代码如下：
/// <summary>
/// Json.net默认转换设置
/// </summary>
private static void DefaultJsonConvertSetting()
{
JsonSerializerSettings setting = new JsonSerializerSettings();
JsonConvert.DefaultSettings = new Func<JsonSerializerSettings>(() =>
{
//日期类型默认格式化处理
setting.DateFormatHandling = DateFormatHandling.MicrosoftDateFormat;
setting.DateFormatString = "yyyy-MM-dd HH:mm:ss";
//空值处理
//setting.NullValueHandling = NullValueHandling.Ignore;
return setting;
});
}
ASPNET 下载共享文件
执行
public static void Run()
{
var state = ConnectState(@"\\192.168.10.160\excel\", "fish", "12345");
if (state)
{
// 共享文件夹的目录
TransportRemoteToLocal(@"\\192.168.10.160\excel\1ff79391090d4e8fa507ada85bae31ec.xlsx", @"D:\Fish-a1.xlsx");
}
}
链接
/// <summary>
/// 连接远程共享文件夹
/// </summary>
/// <param name="path">远程共享文件夹的路径</param>
/// <param name="userName">用户名</param>
/// <param name="passWord">密码</param>
/// <returns></returns>
public static bool ConnectState(string path, string userName, string passWord)
{
bool Flag = false;
Process proc = new Process();
try
{
proc.StartInfo.FileName = "cmd.exe";
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardInput = true;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.CreateNoWindow = true;
proc.Start();
string dosLine = "net use " + path + " " + passWord + " /user:" + userName;
proc.StandardInput.WriteLine(dosLine);
proc.StandardInput.WriteLine("exit");
while (!proc.HasExited)
{
proc.WaitForExit(1000);
}
string errormsg = proc.StandardError.ReadToEnd();
proc.StandardError.Close();
if (string.IsNullOrEmpty(errormsg))
{
Flag = true;
}
else
{
throw new Exception(errormsg);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
proc.Close();
proc.Dispose();
}
return Flag;
}
下载
/// <summary>
/// 从远程服务器下载文件到本地
/// </summary>
/// <param name="saveSrc">保存到本地的路径：下载到本地后的文件路径，包含文件的扩展名</param>
/// <param name="sourceSrc">远程服务器路径（共享文件夹路径）+ 远程服务器（共享文件夹）中的文件名称，包含扩展名</param>
public static void TransportRemoteToLocal(string sourceSrc, string saveSrc)
{
// 远程服务器文件 此处假定远程服务器共享文件夹下确实包含本文件，否则程序报错
if (!File.Exists(sourceSrc))
return;
FileStream inFileStream = File.OpenRead(sourceSrc);
// 从远程服务器下载到本地的文件
FileStream outFileStream = new FileStream(saveSrc, FileMode.OpenOrCreate);
byte[] buf = new byte[inFileStream.Length];
int byteCount;
while ((byteCount = inFileStream.Read(buf, 0, buf.Length)) > 0)
{
outFileStream.Write(buf, 0, byteCount);
}
inFileStream.Flush();
inFileStream.Close();
outFileStream.Flush();
outFileStream.Close();
}
ASPNET 文件批量下载
HTML
<a class="btn btn-warning" id="btnDownload">选中下载</a>
JS
/*
批量下载
*/
// li 列表的文件下载
$("#btnDownload").on('click', function() {
var arr = [];
var urls = escape(arr.join(','));
$(this).attr('href', '@Url.Action("BatchDownloadFiles")?str=' + urls + '&r=' + Math.random());
});
API
public ActionResult BatchDownloadFiles(string str, int type)
{
var idList = str.Split(',').ToList().ConvertAll(x => int.Parse(x));
MemoryStream ms = new MemoryStream();
ZipOutputStream zos = new ZipOutputStream(ms);
zos.IsStreamOwner = false;
zos.SetLevel(1);//设置压缩级别
var rsp = new GetListByDetailIDListRequest
{
UserID = CurrentUserId,
JobTypeID = type,
IDList = idList
}.GetResponse();
if (rsp.IsSuccess)
{
rsp.Data.ForEach(dto =>
{
var filebyte = ByteOfGetOrderFiles(dto); //byte类型的数据
ZipEntry entry = new ZipEntry(fileName); //定义新的压缩数据对象
zos.PutNextEntry(entry);
zos.Write(filebyte, 0, filebyte.Length); //写入
});
}
zos.Finish();
zos.Close();
ms.Position = 0;
return File(ms, "application/x-zip-compressed", string.Format("批量下载文件-{0}.zip", DateTime.Now.ToString("yyyy年MM月dd HH时mm分ss秒")));
}
public byte[] ByteOfGetOrderFiles(ExtractRecordDetailDTO dto)
{
var stream = DownloadFile(dto.SourceFile);
byte[] buffur = new byte[stream.Length];
stream.Read(buffur, 0, (int)stream.Length);
return buffur;
}
public static Stream DownloadFile(string path)
{
using (var client = new WebClient())
{
var stream = client.DownloadData(path);
var outStream = new MemoryStream(stream);
return outStream;
}
}
递归，循环，尾递归
2018-11-13 10:55 by 沉睡的木木夕, 59 阅读, 0 评论, 收藏, 编辑
递归，循环，尾递归
概念
方法递归，简而言之就是方法本身自己调用自己；
咬文嚼字的分析就是两个过程：“递“过程和”归“过程，所有的递归问题都能用地推公式标识.例如斐波拉契数列就能用递推公式表示：
$$
f(n) = f(n-1) +f(n-2)其中fn(0)=1,f(1)=1
$$
转换成代码就是
public static int FibonacciRecursively(int n){
if(n<2) return n;
return FibonacciRecursively(n-1) + FibonacciRecursively(n-2);
}递归问题要满足三个条件：
- 一个问题可以分解成多个子问题的解；子问题就是规模更小的问题（逻辑不变）
- 这些被分解的子问题，除了规模不一样之外，解决思路一样
- 存在条件来终止递归；这个好理解，因为自己调用自己总不能无线循环下去，所以必须有终止条件。
我们来切换一个思考场景：假如这里有N(>1)个台阶，人上台阶每次只能跨一个或者两个，那么有多少种走法能到顶上呢？
我们先分解问题，第一个台阶的走法只有两种，第一种是走一个台阶，第二种是走两个台阶；那么n个台阶的走法就是等于先走1阶后，n-1个台阶的走法加上先走2阶后，n-2个台阶的走法；
所以用公式表示就是
$$
f(n) = f(n-1)+f(n-2)
$$
满足终止条件的就是当只有一个台阶的时候就只有一种可能那就是f(1)=1，f(2)=2
所以这个时候就很容易看出这种走楼梯的思想也是斐波拉契数列的体现。
递归的陷阱
线程在执行方法的时候，都会分配一定尺寸的栈空间。方法调用时，其中的成员信息（临时变量，参数，返回地址等）等信息都会存储在线程栈里，所以这些信息没有及时被GC，返回大深度的循环调用方法，这些内存累加起来就会超出该线程分配的栈空间了，自然就报内存超出的错误。
如何避免内存超出这个问题
- 固定方法调用的深度；当超出设定的深度时，显示报异常，这种方法局限性很大，总有不满足这个深度值的时候，这个方法就不奏效了。
- 把方法改成非递归模式（while,for循环）这样就不会存在栈内存堆积
- 在2的基础之上改写成“尾递归”形式（函数式编程思想）
具体实现方法在后面拓展会具体讲到。
递归代码所产生的重复计算
从走台阶的递推公式我们发现，其实有很多值被重复计算了多次。例如计算f(5)，需要先计算f(4)和f(3)，而计算f(4)要计算f(3)和f(2)。其中f(3)就被重复计算了，那么为了避免这种情况，缩减重复计算带来的时间损耗，我们可以用一个对象结构（散列表等）来记录已经计算的值，我们就可以避免这个问题了
上述代码改成如下：
public static int FibonacciRecurisivelyAvoidRepeat (int n) {
if (n < 2) return n;
if (dic.ContainsKey (n)) return dic[n];
int ret = FabonacciRecurisively (n - 1) + FabonacciRecurisively (n - 2);
dic.Add (n, ret);
return ret;
}这种方式是典型的“空间换时间”，并且空间复杂度是O(n)。
递归函数拓展理解
在前面谈如何避免内存超出这个问题时，就谈到了可以把递归模式的方式改成一个方法中的循环体模式
那是不是所有的递归方法都能改成这样呢？答案是可以这么说。
那么我们把 f(n)=f(n-1)+f(n-2) 改成非递归形式是什么样子的呢？请看代码
public static int FibonacciGeneral(n){
if(n < 2) return n;
int acc1 = 0; //prevprev
int acc2 = 1; //prev
while(n != 0){
acc2 = acc1 + acc2;
acc1 = acc2 - acc1;
n--;
}
return acc1
}这里while循环是关键，主要是实现以下过程
f(5) = f(4) + f(3)
f(5) = [(f(3) + f(2))] + [(f(2) + f(1))]
f(5) = [((f(2) + f(1) )+ f(2))] + [(f(2) + f(1))] f(1) = 1,f(0) =0 或者 f(1) = 1,f(2) = 1,n>2
f(5) = [(((f(1) + f(0)) + f(1))) + (f(1) + f(0))] + [((f(1) + f(0)) + f(1))]
我们可以这么理解，f(5)是要求的当前值，所以上述公式改成文字公式则为：
f(4)当前值 = f(3)上一个值 + f(2)上上一个值
(f5)当前值 = (f4)上一个值 + (f3)上上一个值
(f6)当前值 = f(5)上一个值 + f(4)上上一个值
..
上一个值 = 求上一个值时参与的上一个值 + 求上一个值时参与的上上一个值
...
所以我们只需要循环把单次循环体计算的值记录下来参与下一次循环体计算。如此反复达到结束条件即可，这样就不会存在栈空间堆积超出内存异常了。
我还讲了最后一点，是在循环遍历基础上改写成的一种尾递归方法调用，改写方式很简单，把这个方法所用到的变量提取出来当参数使用，就变成下面的方法
public static int FibonacciTailRecurisively(int n, int acc1, int acc2){
if(n == 0) return acc1;
return FibonacciTailRecurisively(n - 1, acc2, acc1 + acc2)
}这种形式的调用方式就是尾递归，在方法最后被调用时，线程栈里面的临时变量与参数此时已经没任何用了，可以被GC回收，所以理论上就是同上面的循环方法是一致的，无论有多深，都不会发生内存异常。
练习
结合业务场景给定一个菜单结构数据源，如何查找某个菜单的最大父菜单？
普通方法，递归，尾递归。
利用IDisposable接口构建包含非托管资源对象
托管资源与非托管资源
在.net中，对象使用的资源分为两种：托管资源与非托管资源。托管资源由CLR进行管理，不需要开发人员去人工进行控制，.NET中托管资源主要指“对象在堆中的内存”；非托管资源指对象使用到的一些托管内存之外的内资源（例如操作系统的资源），CLR不会管理这些资源，需要开发人员去控制。.NET对象使用到的非托管资源主要有I/O流、数据库连接、Socket连接、窗口句柄等直接与操作系统操作的相关资源。
管理非托管资源
当一个对象不再使用时，我们应该将它使用的非托管资源释放掉，归还给操作系统，不然等到CLR将它在队中的内存回收之后。这部分内存就会变成不可达状态。只能等到整个应用程序运行结束后才能归还给系统。所以我们应当在该对象实例处于“可达”状态时，既有对象引用指向它时释放非托管资源。
利用IDisposable接口构造含有非托管资源类型的对象
在.net类库中有一个IDisposed的接口。几乎每一个使用非托管资源的类型都应该实现这个接口。那么如果我们看到实现此接口的类型，也应该第一时间想到该类型中包含非托管资源。IDispose接口是管理对象非托管资源的一种原则。代码如下：
interface IDisposable
{
void Dispose();
}
class ABase:IDisposable
{
bool _disposed = false;
public bool Disposed
{
get
{
return _disposed;
}
}
public ABase(){}
public void Dispose()
{
if(_disposed)
{
Dispose(true);
GC.SuppressFinalize(this);
_disposed = true;
}
}
protected virtual void Dispose(bool disposing)
{
if(disposing)
{
//release member's unmanaged resource
}
// release ABase's unmanaged resource
}
~ABase
{
Dispose(false);
}
}
class A : ABase
{
public A()
{
}
protected override void Dispose(bool disposing)
{
if(disposing)
{
// release member's unmanaged resource
}
// release A's unmanaged resource
// release base class's unmanaged resource
base.Dispose(disposing);
}
}
class B:A
{
public B()
{
}
public void Dosomething()
{
if(Disposed)// if released, throw exception
{
throw new ObjectDisposedException(...);
}
// do something here
}
protected override void Dispose(bool disposing)
{
if(disposing)
{
// release member's unmanaged resource
}
// release B's Unmanaged resource
base.Dispose(disposing);
}
}《.NET 进阶指南》读书笔记2------定义不可改变类型
不可改变对象的定义
一个类型的对象在创建后，它的状态就不能再改变，知道它死亡，它的状态一直维持与创建时相同。这时候称该对象具有不可改变性。这样的类型为不可改变类型。
不可改变对象在创建的时候，必须完全初始化。因为创建完成后就不能再改变了。任何对不可改变对象进行的操作都会生成一个新的对象。例如String类型就是一个不可改变类型。任何对String类型实例的改变，如String.Trim()、String.Replace()都不会影响原有的String对象，这些方法都会生成一个全新的String对象。如何定义一个不可变类型
定义一个不可改变类型时，需要注意以下三点：
- 类型的构造方法一定要设计好，能够充分的初始化对象，因为对象创建好后，就无法再进行改变，构造方法是唯一能够改变对象状态的地方；
- 涉及改变对象状态的方法均不能真正的改变对象的本身，而都应该返回一个全新的对象；
- 类型的所有的公开属性都应该是只读的，并且注意一些引用类型虽然是只读的，但是可以通过只读的引用去更改堆中的实例从而更改了原对象的状态
class ImmutableType
{
private int _val;
private int[] _refs;
public int Val
{
get
{
return _val;
}
}
// 对于引用类型，返回一个新的对象，防止被改变
public int[] Refs
{
get
{
int b = new int[_refs.Length];
for(int i = 0; i < b.Length;i++)
{
b[i] = _refs[i];
}
return b;
}
}
//构造函数
public ImmutableType(int val,int[] refs)
{
_val = val;
_refs = new int[refs.Length];
for(int i = 0; i < _refs.Length;i++)
{
_refs[i] = refs[i];
}
}
// 对属性进行更新时返回了一个新的对象
public ImmutableType UpdateVal(int val)
{
return new ImmutableType(this._val + val,this._refs);
}
}
class Program
{
static void Main()
{
ImmutableType a = new ImmutableType(1,new int[]{1,2,3,4});
a = a.UpdateVal(2);// 这里返回了一个新的对象
}
}