CODEWITHSUNDEEP
linqasp.net-coremodel-view-controllersqldatareadersqlcommand
Jeff
Jeff

Reputation: 125

Linq query С# ASP.NET Core

I'm executing a sql script using:

SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if(reader.HasRows)
{
   while(reader.Read())
   {
     ViewModel vm = new ViewModel();
     vm.Name = reader.GetString(1);
     vm.Price = reader.GetDecimal(2);
     vm.ReferenceNumber = reader.GetInt32(5);

   returnList.Add(vm);
   }
}

The above will produce result similar to this:

Name        Price      RefNumber
----------| --------|-----------
ABC       | 93.45   | 1000
esd       | 32.32   | 1002
def       | 332.32  | 1000

In short, it's possible to have non-unique RefNumber so I want to disguise these number with a string. For example all refNumber with say: 1000 should be called to 'Ref 1' 1002 should be called to 'Ref 2' 1003 should be called to 'Ref 3 and so on.

How do I check this in the SqlDataReader to disguise the name and save to view model? I've tried something like this:

if(reader.HasRows)
{
   while(reader.Read())
   {
     ViewModel vm = new ViewModel();
     vm.Name = reader.GetString(1);
     vm.Price = reader.GetDecimal(2);
     vm.ReferenceNumber = reader.GetInt32(5);

    if(vm.ReferenceNumber == 1000)
    {
      vm.RefNameAsString = "Ref 1"
    }
    else if(vm.ReferenceNumber == 1002){
      vm.RefNameAsString = "Ref 2"
    }
    else {
      vm.RefNameAsString = "Ref 3"
    }

    returnList.Add(vm);
   }
}

Something like that will produce the desire outcome but the problem is that the RefNumber may be long (100s) so I just want to put these as list and be able to change the name for each distinct reference number?

May be something like this:

 foreach (var i in returnList.Select(x => x.ReferenceNumber).Distinct().ToList())
 {
   //how do I change the name for each unique value?
 }

Upvotes: 1

Views: 121

Answers (1)

Mateus Schneiders
Mateus Schneiders

Reputation: 4903

Assuming 1000 is always the base for the reference number, and it will never exceed 1999, then you could do something like:

var referenceNumberBase = 1000;
if(reader.HasRows)
{
   while(reader.Read())
   {
     ViewModel vm = new ViewModel();
     vm.Name = reader.GetString(1);
     vm.Price = reader.GetDecimal(2);
     vm.ReferenceNumber = reader.GetInt32(5);

    if(vm.ReferenceNumber == referenceNumberBase)
      vm.RefNameAsString = "Ref 1"
    else 
      vm.RefNameAsString = $"Ref {vm.ReferenceNumber-referenceNumberBase}";

    returnList.Add(vm);
   }
}

Both 1000 and 1001 would generate "Ref 1", which might be a problem. If that's an issue, you could change 1000 to be "Ref 0" with just:

   vm.RefNameAsString = $"Ref {vm.ReferenceNumber-referenceNumberBase}";

You can also try ignoring the first number for different bases, but there might be edge cases:

   vm.RefNameAsString = $"Ref {Convert.ToInt32(vm.ReferenceNumber.ToString()[1..])};

Upvotes: 2

Related Questions