我们需要新建一个存储过程:
-- DBO.SP_ACCOUNTS_USER_LOGIN
CREATE PROCEDURE DBO.SP_ACCOUNTS_USER_LOGIN
AS
--表头
SELECT '姓名,登录次数'
--报表数据
SELECT A.UserName,CASE WHEN ISNULL(B.UserId,'')='' THEN 0 ELSE 1 END AS FLAG
INTO #TEMP
FROM Accounts_Users A LEFT JOIN Accounts_Login_Record B ON A.UserId = B.UserId
SELECT UserName,'<a href=view.aspx?UsenName='+UserName+' target=_blank>'+CONVERT(VARCHAR(100),SUM(FLAG))+'</a>' AS LOGIN_COUNT FROM #TEMP GROUP BY UserName ORDER BY LOGIN_COUNT DESC
DROP TABLE #TEMP
下面用到了以前讲解的一些知识:数据库操作通用类、分层结构设计
数据层
using System;
using System.Data;
using System.Data.SqlClient;
using Utility.Data;
namespace HR.Test.Report.Data
{
/// <summary>
/// ReportTest 的摘要说明。
/// </summary>
public class ReportTest:MsSql
{
public ReportTest()
{
}
public DataSet GetReport()
{
using(DataSet list = RunProcedure("SP_ACCOUNTS_USER_LOGIN",new IDataParameter[]{},"list"))
{
return list;
}
}
}//
}//
商务层
using System;
using System.Data;
namespace HR.Test.Report.Business
{
/// <summary>
/// ReportTest 的摘要说明。
/// </summary>
public class ReportTest
{
public static DataSet GetReport()
{
Data.ReportTest report = new Data.ReportTest();
return report.GetReport();
}
}
}