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); }