c# object filter and change order

Posted on 2013-11-11
Last Modified: 2013-11-14
I have the following employee object.

Emp_Number            Name          Designation      salary
1                                   aa               Employee         30000
2                                   a1               Manager           60000
3                                    b1              Employee         35000
6                                   b2              Employee          32000
5                                   c1               Manager           40000

This is the code to get the details.

 public List<Employee> GetEmployeeDetailsAllDesc()
            List<Employee> employeeDetailAllDesc = new List<Employee>();

            string connectionString = GetConnection();
            string queryString = "SELECT * "
              + " FROM employee  order by Emp_Number desc";
            using (SqlConnection connection = new SqlConnection(
                SqlCommand command = new SqlCommand(
                    queryString, connection);
                SqlDataReader reader = command.ExecuteReader();

                if (reader == null || !reader.HasRows)
                    employeeDetailAllDesc = null;
                    return employeeDetailAllDesc ;

                    while (reader.Read())

                        Employee   employeeAllDesc = new Employee   ();
                        employeeAllDesc.Emp_Number = (int)reader["Emp_Number"];
                        employeeAllDesc.Name = reader["Name"].ToString();
                        employeeAllDesc.Designation= reader["Designation"].ToString();
                        employeeAllDesc.Salary= reader["Salary"];

            return  employeeDetailAllDesc

I need to get the 3 different types of result from this  object.

Result 1:  Employee Detail order by Emp_Number    Ascending order

Result 2:  Employee Detail order by Emp_Number    Descending order

Result 3:  Employee Detail  Designation ='Employee'

I do not want to use the three different method to get the  above details and it will hit the DB three times. So how can I achieve this in single hit ?
Question by:Varshini S
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 44

Expert Comment

ID: 39638968
string queryString;
queryString = "SELECT * " + " FROM employee  order by Emp_Number desc";
   queryString = ...
    queryString = ...

Just put in what you require as the conditions and the different (order by clauses) SQL statements and then your function can cope with all three cases you require.
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 250 total points
ID: 39639054

You can send all three commands to SQL server and return all three result sets in one call. Make your queryString as follows:

 string queryString = 
       "SELECT * FROM employee ORDER BY Emp_Number ASC;"
     + "SELECT * FROM employee ORDER BY Emp_Number DESC;" 
     + "SELECT * FROM employee WHERE Designation = 'Employee'";

Open in new window


Then process the results of the data reader as normal in your code. When you reach the end of the records in the data reader then you can execute the following command on the reader to get to the next result set.


Open in new window

Which will place you just before the first record in the second result set. Then do the same for the last result set.

Author Comment

by:Varshini S
ID: 39639254
Is it possible to this operation in object level ?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Varshini S
ID: 39639261
FernandoSoto- Instead of doing in SQL server can I do it on the object level using linq ?
LVL 63

Expert Comment

by:Fernando Soto
ID: 39639343
Sure it can be but that means that you will need to bring the complete employee table down to local system first.
LVL 44

Accepted Solution

AndyAinscow earned 250 total points
ID: 39640825
>>Is it possible to this operation in object level ?

Yes, no problems.  The initial code you showed brings all the records back (you didn't use any filter, just a sort).  So you can re-order the list without having to requery the database.  I would use a custom sort function but LINQ should do the job as well.

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question