1

I am using VS2010, C#, .Net Framework 4, (DB provider Microsoft SQL Server 2008 R2), to write an add-in for Excel. The main idea is to let a user execute a pre-defined query against the DB, so that the data is pulled into Excel.

Say I have hard-coded queries stored in a table [Queries] in the DB. So I can use Entity Framework to get to the table Queries. These queries listed in the table, could return anything from a single value to multiple records.

I am fairly ignorant regarding Entity Framework. Now, I've read that one can execute T-SQL directly against the database here. This has been useful, but I struggle with getting the results back. 

using (SYMNHM_DEVEntities dataContext = new SYMNHM_DEVEntities())
        {

            var query = "Select [Query Name] from [SYM XLS Queries] where [Query ID] = 2";
            str = dataContext.ExecuteStoreCommand(query) + "";

        }

This gives a result of -1, which is then roughly made into a string. Okay, I know this is not good coding, but just see it as an example. How do I get the actual [Query Name] returned?

In this case, it's just a single value, but if the query would return more values (and I might not even know whether it's strings or not) how do I create an appropriate return type?

Igavshne
  • 699
  • 7
  • 33
  • In EF ypu would creaye a *Complex Type* and *project the results in it with ExecuteQuery [T] but if you don't wknow what columbs to ezpect this could be fun... – Mathieu Guindon May 28 '13 at 10:49

2 Answers2

1

Here is an answer for the simple example regarding a string:

using (MyEntities dataContext = new MyEntities())
    {
      var query = (from q in dataContext.Queries
                     where q.Query_Name == queryName
                     select q.Query).Single();



      queryResults = dataContext.ExecuteStoreQuery<string>(query);
      List<string> list = new List<string>(queryResults.ToArray<string>());
      return list; }

I do not yet have a solution for substituting the string type with something else, although I am looking into the matter of returning a Datatable if the query would have more than one string result.

Igavshne
  • 699
  • 7
  • 33
  • 1
    Above you can see that I get the SQL query from another table in the same DB. It is also more appropriate to use the method ExecuteStoreQuery, than ExecuteStoreCommand. – Igavshne May 30 '13 at 11:30
  • 1
    I ended up using the answer of chuck in this post: http://stackoverflow.com/questions/4586834/returning-a-datatable-using-entityframe-executestorequery – Igavshne May 31 '13 at 08:59
0

It's not a good pratice use SQL command directly, why you don't use LINQ ? In your example you can use:

var result = (from q in dataContext.SYMXLSQueries
             where q.ID == 2
             select q.QueryName).ToArray();

This return an array of strings (If QueryName is a varchar...)

Max
  • 6,821
  • 3
  • 43
  • 59
  • I do not use LINQ because I will have hard-coded SQL queries in the [Query] column of my [Queries] table. The above is simply an example of what such a SQL query could possible look like. – Igavshne May 28 '13 at 12:52
  • The matter regarding the type of the return values becomes an issue when I have to write code that executes any of these hardcoded SQL queries, and I can not know before hand whether it will return varchar or int, to give the simplest example. – Igavshne May 28 '13 at 12:55