1.首先我们建立一个保存数据的实体类
public class AttendanceSummary
{
public int ID { get ; set ; }
public string Name { get ; set ; }
public string IDCardNo { get ; set ; }
public int Age { get ; set ; }
public string Phone { get ; set ; }
public string TypeName { get ; set ; }
public string DeptName { get ; set ; }
public string NameMaleSpecies { get ; set ; }
public string CompanyName { get ; set ; }
public string CompanyTypeName { get ; set ; }
public int Normal { get ; set ; }
public int Late { get ; set ; }
}
2.建立一个SqlParameter的数组用来存储存储过程参数数组
SqlParameter[ ] sqlParameters = new SqlParameter [ 6 ] ;
sqlParameters[ 0 ] = new SqlParameter ( "@EmployeeName" , Name) ;
sqlParameters[ 1 ] = new SqlParameter ( "@nameOfTheCompany" , CId) ;
sqlParameters[ 2 ] = new SqlParameter ( "@TeamName" , DeptName) ;
sqlParameters[ 3 ] = new SqlParameter ( "@QueryDate" , StartTime) ;
sqlParameters[ 4 ] = new SqlParameter ( "@RegisteredStatus" , RecordStatus) ;
sqlParameters[ 5 ] = new SqlParameter ( "@ProjectID" , ProjectID) ;
3.调用EF的存储过程
-- MonthlySummary 是存储过程名称
using ( var context = new DBEntities ( ) )
{
var PersonnelAttendanceList = context. Database. SqlQuery < AttendanceSummary > ( "exec MonthlySummary @EmployeeName,@nameOfTheCompany,@TeamName,@QueryDate,@RegisteredStatus,@ProjectID" , sqlParameters) . Cast < AttendanceSummary > ( ) . ToList ( ) ;
}
* * 注意: 如果参数为null 时需要给参数赋值为" " ; * *
4.集合
public List< Object> GetPersonnelAttendanceExport ( string StartTime, int RecordStatus, int ProjectID, string Name = "" , int CId = 0 , string DeptName = "" )
{
using ( var context = new DBEntities ( ) )
{
SqlParameter[ ] sqlParameters = new SqlParameter [ 6 ] ;
sqlParameters[ 0 ] = new SqlParameter ( "@EmployeeName" , Name) ;
sqlParameters[ 1 ] = new SqlParameter ( "@nameOfTheCompany" , CId) ;
sqlParameters[ 2 ] = new SqlParameter ( "@TeamName" , DeptName) ;
sqlParameters[ 3 ] = new SqlParameter ( "@QueryDate" , StartTime) ;
sqlParameters[ 4 ] = new SqlParameter ( "@RegisteredStatus" , RecordStatus) ;
sqlParameters[ 5 ] = new SqlParameter ( "@ProjectID" , ProjectID) ;
var PersonnelAttendanceList = context. Database. SqlQuery < AttendanceSummary > ( "exec MonthlySummary @EmployeeName,@nameOfTheCompany,@TeamName,@QueryDate,@RegisteredStatus,@ProjectID" , sqlParameters) . Cast < AttendanceSummary > ( ) . ToList ( ) ;
return Test. ToList < Object > ( ) ;
}
}