private string GenPodTotalSql(TotalPODEntity SearchCondition) { StringBuilder sb = new StringBuilder(); sb.Append("and CustomerID=14"); if (SearchCondition.StatUpLoadTime != null) { sb.Append("and ActualDeliveryDate>='" + SearchCondition.StatUpLoadTime + "'"); } if (SearchCondition.EndUpLoadTime != null) { sb.Append("and ActualDeliveryDate<='" + SearchCondition.EndUpLoadTime + "'"); } if (SearchCondition.StateID.HasValue) { if (SearchCondition.StateID == 0) { sb.Append(" and PodTrack.CreateTime is null"); } if (SearchCondition.StateID == 1) { sb.Append(" and PodTrack.CreateTime is not null"); } } return(sb.ToString()); }
public IEnumerable <Pod> GetPODTotalInfoReport(TotalPODEntity SearchCondition, int PageIndex, int PageSize, out int RowCount) { string sqlWhere = this.GenPodTotalSql(SearchCondition); using (SqlConnection conn = new SqlConnection(BaseAccessor.SMSSqlConnection)) { DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand("proc_Total_QueryPodHasTracks", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Where", sqlWhere); cmd.Parameters[0].SqlDbType = SqlDbType.NVarChar; cmd.Parameters.AddWithValue("@PageIndex", PageIndex); cmd.Parameters[1].SqlDbType = SqlDbType.Int; cmd.Parameters.AddWithValue("@PageSize", PageSize); cmd.Parameters[2].SqlDbType = SqlDbType.Int; cmd.Parameters.AddWithValue("@RowCount", 0); cmd.Parameters[3].Direction = ParameterDirection.Output; cmd.Parameters[3].SqlDbType = SqlDbType.Int; conn.Open(); SqlDataAdapter Adp = new SqlDataAdapter(cmd); Adp.Fill(dt); RowCount = Convert.ToInt32(cmd.Parameters[3].Value); return(dt.ConvertToEntityCollection <Pod>()); } }