none
Running a SQL query on DataSet RRS feed

  • Question

  • Is there a way to run a SQL query on a DataSet? I know the DataTable class provides a Select method that can filter data but I would like to run a query that transforms data. For example, I'd like to do something like

    DataSet ds = new DataSet();
    ds.Tables.Add();

    foreach(var column in buffer.Columns)
    {
         ds.Tables[0].Columns.Add(column.Name, column.DataType);
    }
    string query = "select field1*field2, field2"; //this is a simplified query just for the sake of the example
    SqlCommand xFormCommand = new SqlCommand(query, myConnection);

    Is there a way to do something like ds.RunQuery(xFormCommand) OR ds.Tables[0].RunQuery(xFormCommand)? Or is there another way to do this? I would like to avoid persisting the data to a database if possible and to run this query in memory.


     

     

     

    • Moved by eryang Thursday, May 19, 2011 1:42 AM (From:.NET Base Class Library)
    Wednesday, May 18, 2011 2:05 PM

Answers

  • Hi Alan,

    Unfortunately, none of the suggestions worked. Dynamic linq doesn't support my use case and we can't use a view in the database because

    1) we don't know what the view defintion or query will be until run time and even then, it changes with each call to the method
    2) we want to avoid issuing a query against the database each time we analyze a row in the buffer.

    The data is already in memory. The buffer contains the data values needed to run any of the queries passed into the method. It just needs to be transformed according to whatever query is provided. As an alternative, we're simply replacing the column names in the query with the corresponding values from the buffer resulting in a simple query that doesn't contain any from, where, join, etc clauses. As a simplified example, a query may end up looking like "select 5, 'hello' + 'test', 6*17" which we then run with a SqlDataAdapter. The column replacement and transformed value retrieval code is a little messy and we would have preferred a different solution but it seems to be working for us.

    Thanks,
    Bill

    Friday, June 3, 2011 3:19 PM

All replies

  • You can use Linq to filter in memory.

    Add a reference to System.Data.DataSetExtensions and you can do things like:

    datatable.AsEnumerable().Where(item => item.Field<string>("name") == "John" && item.Field<int>("age") < 40);
    
    


    To transform data you can use something like:

    datatable.AsEnumerable().Select(item => new { Fullname = item.Field<string>("name") + " " + item.Field<string>("lastname"), Age = item.Field<int>("age") });
    Wednesday, May 18, 2011 2:25 PM
  • I thought about that. But taking this approach would require me to dynamically break down a complex sql query into linq. Unfortunately, I don't have any control over the sql query or any foreknowledge of its contents (other than the query corresponds to the contents of the buffer). It's provided to me by an external component. Any other ideas?
    Wednesday, May 18, 2011 3:43 PM
  •  

    Hi Foshay,

    Welcome!

    I think LINQ to Dataset is a good way. We couldn't run T-SQL as in SQL Server in dataset, there is not engine doing this. LINQ can do this.

    I think you can have a  try dynamic LINQ:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

     DataSet1TableAdapters.TableATableAdapter A = new DataSet1TableAdapters.TableATableAdapter();

                A.Fill(ds.TableA);

                var test=   ds.TableA.AsQueryable().Where("ID=1");

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 19, 2011 7:18 AM
    Moderator
  • Hi Alan,

    I looked at the link you provided but I'm not quite sure I understand how I can use Dynamic LINQ? I have a function like the one below whose responsibilty is to update a buffer according to a SQL query. I don't know the contents of sqlTransformationQuery or the buffer at compile time. They're both provided by an external component at runtime. The buffer could refer to any SQL table or view and the query could refer to any number of fields. I do know that the query is not a filter query. Its purpose is to transform one or more of the fields in the buffer. Can Dynamic LINQ support the bold line of code below? This is what I'm looking for. Thanks for the help!

    void ProcessInput(string sqlTransformationQuery, PipelineBuffer buffer)
    {
     DataSet ds = new DataSet();
     ds.Tables.Add();
    
     foreach(var column in buffer.Columns)
     {
      ds.Tables[0].Columns.Add(column.Name, column.DataType);
     }
    
     while(buffer.NextRow())
     {
      ds.Tables[0].Clear();
    
      object[] columnValues = new object[buffer.Columns.Length];
    
      for(int i=0; i<buffer.Columns.Length; i++)
      {
        columnValues[i] = buffer[i];
      }
    
      ds.Tables[0].Rows.Add(columnValues);
    
       DataRow transformedRow = ds.Tables[0].AsQueryable().Select(sqlTransformationQuery).FirstOrDefault();
    
      for(int i=0; i<buffer.Columns.Length; i++)
      {
       object columnValue = dataSetRow[buffer.Columns[i].Name];
    
       //Do something with the updated column value
      }
     }
    }
    
    
    
    

     

     



    Thursday, May 19, 2011 9:13 PM
  • Hi,

    I think you should Where() method instead of Select() method.

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/b09c3955-934a-4fd1-be66-a0739e7538ae

    You can pass in where condition but the whole T-SQL.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 20, 2011 9:19 AM
    Moderator
  • Hi Alan,

    Thanks for your response but unfortunately, I don't think this will work for me. The where clause is just a filtering mechanism but the query being passed into my function is more than just a filter. The T-SQL could contain any select clause which will select some dynamic set of fields. This effectively, as mentioned in the link you provided, creates some new unknown structure every time my function is called. I don't know what this structure may be at compile time. The first time my function is called the SQL might be "select field1, field2", the next time it might be "select field2, field3+'_temp', field4*field5", the next time something different. Can Dynamic LINQ support this? Thanks!

    Bill

    Friday, May 20, 2011 3:36 PM
  • Hi Foshay,

    Thanks for your feedback.

    I think there is not a good way to handle your scenario, T-sql is the syntax of SQL Server, it is hard to let dataset(in C#) excute the complex T-SQL, If you want to improve the performance, I think you use View in database and ADO.NET's Command Text to run out your records. Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 24, 2011 7:43 AM
    Moderator
  • Hi Bill,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 2, 2011 12:42 PM
    Moderator
  • Hi Alan,

    Unfortunately, none of the suggestions worked. Dynamic linq doesn't support my use case and we can't use a view in the database because

    1) we don't know what the view defintion or query will be until run time and even then, it changes with each call to the method
    2) we want to avoid issuing a query against the database each time we analyze a row in the buffer.

    The data is already in memory. The buffer contains the data values needed to run any of the queries passed into the method. It just needs to be transformed according to whatever query is provided. As an alternative, we're simply replacing the column names in the query with the corresponding values from the buffer resulting in a simple query that doesn't contain any from, where, join, etc clauses. As a simplified example, a query may end up looking like "select 5, 'hello' + 'test', 6*17" which we then run with a SqlDataAdapter. The column replacement and transformed value retrieval code is a little messy and we would have preferred a different solution but it seems to be working for us.

    Thanks,
    Bill

    Friday, June 3, 2011 3:19 PM
  • Hi Bill,

    >> As a simplified example, a query may end up looking like "select 5, 'hello' + 'test', 6*17" which we then run with a SqlDataAdapter.

    I know the records are all in memory, we can get the records by the positions and columns' name. but here you mean SqlDataAdapter, I'm not very clear about that, would you please share some with us, thanks!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 6, 2011 9:45 AM
    Moderator
  • I just meant, that once I have a simple select query I can run it to fill a DataSet with the transformed results using a SqlDataAdapter.

    DataSet ds = new DataSet("TransformedValues");
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    SqlCommand selectCommand = new SqlCommand("select 5, 'hello' + 'test', 6*17", sqlConnection);
    sqlDataAdapter.SelectCommand = selectCommand;
    sqlDataAdapter.Fill(ds);
    

    Then I can retrieve the transformed values from the dataset.

    Tuesday, June 7, 2011 1:38 PM
  • Hi Foshay,

    Thanks for sharing your experience here.

    Have  a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 8, 2011 2:19 AM
    Moderator