How to get all the values from an SQL Query C#

Question

I have used String Builder to generate a RAW SQL QUERY in C#.

List<string> columns = new List<string>();
columns.Add("id");
columns.Add("Temp");

StringBuilder SqlStatement = new StringBuilder();
SqlStatement.Append("Select ");
for (int i = 0; i < columns.Count; i++)
{
   if (i == columns.Count - 1)
   {
      SqlStatement.Append(columns[i]);
   }
   else
   {
      SqlStatement.Append(columns[i]);
      SqlStatement.Append(",");
   }
}
SqlStatement.Append(" FROM graph_update");

var ctx = new graphDBContext();
var result = ctx.Database.SqlQuery<graphDBContext>(SqlStatement.ToString()).ToList();

This translates into SELECT id,Temp FROM graph_update

And the result gives me

id = 1, temp = 20 

id = 2 temp = 30

How do I get all these values????

I'm too use to:

foreach(var item in result)
{
   item.id =  id;
   item.temp = temp;
}

But it won't let me.

EDIT: Sorry but I'm not sure what you mean. Here is my debugger

What result you've done so far? Try to debug your code while running — Vijunav Vastivch
– Vijunav Vastivch, Commented Mar 3, 2017 at 6:33
all i have is 'var result = ctx.Database.SqlQuery<graphDBContext>(SqlStatement.ToString()).ToList();' but I don't know how to grab all the values — Johnathan Logan
– Johnathan Logan, Commented Mar 3, 2017 at 6:35
You are just building a query then debug it. Is this content query is correct "SqlStatement.ToString()" if not then you have to make some action to become a true query. — Vijunav Vastivch
– Vijunav Vastivch, Commented Mar 3, 2017 at 6:39
@reds hi reds, i'm not sure what you mean sorry, I attached contents in question now. — Johnathan Logan
– Johnathan Logan, Commented Mar 3, 2017 at 6:44

Vijunav Vastivch · Accepted Answer · 2017-03-03 06:51:49Z

2

Try to use foreach like this if theres no error return

foreach(var v in result)
{
    String v0 = v[0].ToString();
    String v1 = v[1].ToString();
}

answered Mar 3, 2017 at 6:51

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihail Stancescu · Accepted Answer · 2017-03-03 06:57:26Z

1

Assuming you have EF > 6, then the ctx.Database.SqlQuery, according to the method documentation:

Creates a raw SQL query that will return elements of the given generic type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type. The type does not have to be an entity type. The results of this query are never tracked by the context even if the type of object returned is an entity type.

With that in mind you can do something like this:

public class GraphUpdateResult 
{
  public int Id {get; set;}
  public decimal Temp {get; set;}
}

Then in your current method:

var result = ctx.Database.SqlQuery<GraphUpdateResult>SqlStatement.ToString()).ToList();
foreach (var graphResult in result)
{
   Console.WriteLine(graphResult.Id);
   Console.WriteLine(graphResult.Temp);
}

You can add more columns to the GraphUpdateResult class for EF to bind to, even if in some queries you don't specify them in the select statement.

answered Mar 3, 2017 at 6:57

Mihail Stancescu

4,1681 gold badge18 silver badges22 bronze badges

2 Comments

Johnathan Logan Over a year ago

Thank you so much for your detailed explanation and example!! I updated my EF to >6 and Also it turns out there was a problem with me declaring GraphUpdateResult Model. But it works now !! Cheers x10000 !

Mihail Stancescu Over a year ago

Glad I could help!

Venu · Accepted Answer · 2017-03-03 06:39:13Z

1

I hope this helps.

foreach(var item in result)
{
   var id =  item.id;
   var temp = item.temp;
}

in your code above, you are trying to assign the values to the item, instead of retrieving.

answered Mar 3, 2017 at 6:39

Venu

4752 silver badges8 bronze badges

Comments

Wolfgang Feneberg · Accepted Answer · 2017-03-03 07:57:08Z

1

You can use a ORM-Mapper like

https://stormy.codeplex.com/SourceControl/latest#Stormy.cs

It is a very light Mapper and you can look how it works.

It maps the reader Data to the Object data:

public class CatMapper : ISelectable<Cat>
{
    public Cat ApplySelect(IDataReader reader)
    {
        return new Cat()
            {
                Name = reader["name"].ToString(),
                Weight = (float)reader["weight"]
            };
    }
}

answered Mar 3, 2017 at 7:57

Wolfgang Feneberg

2361 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to get all the values from an SQL Query C#

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related