public List <PracticeInfo> GetUser(SqlConnection connection, string id, PracticeCriteria criteria)
{
var result = new List <PracticeInfo>();
using (var command = new SqlCommand("Select ltrim(rtrim(ID)) as ID, Name " +
" from dbo.test where id=@id" +
" where 1=1", connection))
{
AddSqlParameter(command, "@id", id, System.Data.SqlDbType.VarChar);
if (criteria.pageSize == 0)
{
criteria.pageSize = 10;
}
var offSet = criteria.pageIndex * criteria.pageSize;
command.CommandText += " order by U.Name ";
command.CommandText += " OFFSET @OFFSET ROWS FETCH NEXT @PAGESIZE ROWS ONLY ";
AddSqlParameter(command, "@OFFSET", offSet, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@PAGESIZE", criteria.pageSize, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var info = new PracticeInfo();
info.UserName = GetDbReaderValue <string>(reader["Name"]);
info.UserID = GetDbReaderValue <string>(reader["ID"]);
// info.Disable = GetDbReaderValue<byte>(reader["GroupID"]);
result.Add(info);
}
}
return(result);
}
}
public int getTotalRecords(PracticeCriteria conditions)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
return(PracticeDataLayer.GetInstance().GetTotalRecords(connection, conditions));
}
}
public List <PracticeInfo> GetList(PracticeCriteria conditions)
{
SqlConnectionFactory sqlConnection = new SqlConnectionFactory();
using (SqlConnection connection = sqlConnection.GetConnection())
{
List <PracticeInfo> ListUserInfo = PracticeDataLayer.GetInstance().Getlist(connection, conditions);
return(ListUserInfo);
}
}
public int GetTotalRecords(SqlConnection connection, PracticeCriteria criteria)
{
if (criteria != null)
{
var result = new List <PracticeInfo>();
using (var command = new SqlCommand("Select count(*) as TotalRecords " +
" from dbo.test U " +
" where 1=1 ", connection))
{
if (criteria.UserName != "" && criteria.UserName != null)
{
command.CommandText += " and U.Name like '%" + criteria.UserName + "%' ";
}
if (criteria.UserID != "" && criteria.UserID != null)
{
command.CommandText += " and ID = @ID ";
AddSqlParameter(command, "@ID", criteria.UserID, System.Data.SqlDbType.VarChar);
}
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
}
else
{
using (var command = new SqlCommand("Select count(*) as TotalRecords from dbo.test where 1 = 1 ", connection))
{
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
}
return(0);
}
public ListResponeMessage <PracticeInfo> GetList([FromQuery] PracticeCriteria criteria)
{
ListResponeMessage <PracticeInfo> ret = new ListResponeMessage <PracticeInfo>();
try
{
ret.isSuccess = true;
ret.data = PracticeService.GetInstance().GetList(criteria);
ret.totalRecords = PracticeService.GetInstance().getTotalRecords(criteria);
}
catch (Exception ex)
{
ret.isSuccess = false;
ret.err.msgCode = "005";
ret.err.msgString = ex.ToString();
}
return(ret);
}
