public (int Count, List <UserVM> UsersList) GetUsers(UsersQM qm)
{
try
{
StringBuilder selectCountCommand = new StringBuilder(@" SELECT Count([ID]) FROM [RABC].[dbo].[Users] ");
StringBuilder where = new StringBuilder("where 1 = 1");
List <SqlParameter> parameters = new List <SqlParameter>();
if (!string.IsNullOrEmpty(qm.Account))
{
SqlParameter param = new SqlParameter("@Account", string.Format("%{0}%", qm.Account));
where.Append(" and Account like @Account");
parameters.Add(param);
}
selectCountCommand.Append(where.ToString());
StringBuilder selectDataCommand = new StringBuilder(string.Format(@"
select * from
(
select row_number() over (order by a.ID ) as rowNum, a.*
from (
SELECT TOP ({0})
[ID],[Account] ,[Password] FROM [RABC].[dbo].[Users]
{1}
) as a
)c
where c.rowNum>{2}", qm.Take + qm.Skip, where.ToString(), qm.Skip));
// DataPagingInfo dataPagingInfo = new DataPagingInfo();
var list = DataTableToList.DataSetToList <UserVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), parameters.ToArray()));
// dataPagingInfo.DataSoure = JsonConvert.SerializeObject(list);
// dataPagingInfo.SearchAllFlg = info.SearchAllFlg;
// dataPagingInfo.PageCount = pageCount;
int count = (int)SqlHelper.ExecuteScalar(SqlHelper.GetConnSting(), CommandType.Text, selectCountCommand.ToString(), parameters.ToArray());;
// retJson = JsonHelper.SerializeObject(dataPagingInfo);
return(count, list);
}
catch (Exception ex)
{
return(0, null);
}
}
public UsersQM Execute()
{
var response = new UsersQM();
using (var sqlCommand = CreateCommand())
{
sqlCommand.Connection = Connection;
sqlCommand.CommandText = "[dbo].[uspGetUsers]";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(AddParameter("@RecordCount", SsDbType.Int, ParameterDirection.Output, default(int)));
using (var reader = SsDbCommandHelper.ExecuteReader(sqlCommand))
{
response.userList = reader.ToList <UserResponseModel>();
}
}
return(response);
}
public (int Count, List <UserVM> UsersList) GetUsers(UsersQM qm)
{
return(_dal.GetUsers(qm));
}
