/// <summary>
/// SysSample Inquiry
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <param name="paging"></param>
/// <returns></returns>
public List <SysSampleInquiry> SysSampleInquiry(string queryStr, DataGridPagingInformation paging)
{
string sortExpression = paging.SortExpression;
if (paging.SortDirection != string.Empty)
{
sortExpression = sortExpression + " " + paging.SortDirection;
}
var sysSampleQuery = dbConnection.SysSamples.AsQueryable();
if (queryStr != null && queryStr.Trim().Length > 0)
{
sysSampleQuery = sysSampleQuery.Where(c => c.Id.StartsWith(queryStr));
}
var sysSamples = from c in sysSampleQuery
select new { c.Id, c.Name, c.Age, c.Bir, c.Photo, c.Note, c.CreateTime };
int numberOfRows = sysSamples.Count();
sysSamples = sysSamples.OrderBy(sortExpression);
var sysSampleList = sysSamples.Skip((paging.CurrentPageNumber - 1) * paging.PageSize).Take(paging.PageSize);
paging.TotalRows = numberOfRows;
paging.TotalPages = Utilities.CalculateTotalPages(numberOfRows, paging.PageSize);
List <SysSampleInquiry> sysSampleInquiry = new List <SysSampleInquiry>();
foreach (var sysSample in sysSampleList)
{
SysSampleInquiry sysSampleData = new SysSampleInquiry();
sysSampleData.Id = sysSample.Id;
sysSampleData.Name = sysSample.Name;
sysSampleData.Age = sysSample.Age;
sysSampleData.Bir = sysSample.Bir;
sysSampleData.Photo = sysSample.Photo;
sysSampleData.Note = sysSample.Note;
sysSampleData.CreateTime = sysSample.CreateTime;
sysSampleInquiry.Add(sysSampleData);
}
return(sysSampleInquiry);
}
/// <summary>
/// Get SysSampleInquiry
/// </summary>
/// <returns></returns>
public List <SysSampleInquiry> SysSampleInquiry(string queryStr, DataGridPagingInformation paging)
{
List <SysSample> sysSamples = new List <SysSample>();
//string sortExpression = paging.SortExpression;
int maxRowNumber;
int minRowNumber;
minRowNumber = (paging.PageSize * (paging.CurrentPageNumber - 1)) + 1;
maxRowNumber = paging.PageSize * paging.CurrentPageNumber;
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder sqlWhereBuilder = new StringBuilder();
string sqlWhere = string.Empty;
if (queryStr != null && queryStr.Trim().Length > 0)
{
sqlWhereBuilder.Append(" c.Id LIKE @Id AND ");
}
if (sqlWhereBuilder.Length > 0)
{
sqlWhere = " WHERE " + sqlWhereBuilder.ToString().Substring(0, sqlWhereBuilder.Length - 4);
}
sqlBuilder.Append(" SELECT COUNT(*) as total_records FROM SysSample c ");
sqlBuilder.Append(sqlWhere);
sqlBuilder.Append(";");
sqlBuilder.Append(" SELECT* FROM(");
sqlBuilder.Append(" SELECT (ROW_NUMBER() OVER (ORDER BY " + paging.SortExpression + " " + paging.SortDirection + ")) as record_number,");
sqlBuilder.Append(" c.*");
sqlBuilder.Append(" FROM SysSample c");
sqlBuilder.Append(sqlWhere);
sqlBuilder.Append(" ) Rows");
sqlBuilder.Append(" where record_number between " + minRowNumber + " and " + maxRowNumber);
string sql = sqlBuilder.ToString();
//string sql = "SELECT * FROM SysSample ORDER BY Id";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = dbConnection;
sqlCommand.CommandText = sql;
if (queryStr != null && queryStr.Trim().Length > 0)
{
sqlCommand.Parameters.Add("@Id", System.Data.SqlDbType.NVarChar);
sqlCommand.Parameters["@Id"].Value = queryStr + "%";
}
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
paging.TotalRows = Convert.ToInt32(reader["Total_Records"]);
paging.TotalPages = Utilities.CalculateTotalPages(paging.TotalRows, paging.PageSize);
reader.NextResult();
List <SysSampleInquiry> sysSampleList = new List <SysSampleInquiry>();
//SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
DataReader dataReader = new DataReader(reader);
SysSampleInquiry sysSample = new SysSampleInquiry();
sysSample.Id = dataReader.GetString("Id");
sysSample.Name = dataReader.GetString("Name");
sysSample.Age = dataReader.GetString("Age");
sysSample.Bir = dataReader.GetString("Bir");
sysSample.Photo = dataReader.GetString("Photo");
sysSample.Note = dataReader.GetString("Note");
sysSample.CreateTime = dataReader.GetDateTime("CreateTime");
sysSampleList.Add(sysSample);
}
reader.Close();
return(sysSampleList);
}
