Query a dataTable by large Query String

Question

I have a datatable that i want to query. the query is very large and complicated and it works when i run it in the SQl Server Editor - so i have the query text.

i need to query the datatable with this query String. To Translate the query into linq will take years, and also Select() method of DataTable won't handle it.

How can i operate a text query on a dataTable?

Answer 1

You can use a SqlCommand, like this:

using(var connection = new SqlConnection("connection string"))
using(var command = new SqlCommand(@"
your very long query
    ", connection)
using(var reader = command.ExecuteReader()) {
    while(reader.Read()) {
        //use reader[colIndex] to get a field from the current row
    }
}

---

You can load it into a DataTable using a SqlDataAdapter, like this:

var table = new DataTable();

using(var connection = new SqlConnection("connection string"))
using(var command = new SqlCommand(@"
your very long query
    ", connection)
using(var adapter = new SqlDataAdapter(command)) {
    adapter.Fill(table);
}

Answer 2

You may want to go ahead and make it a stored procedure, especially if it takes in a relatively fixed set of parameters. Then you can just add your stored procedure into your Linq2 (entitites, sql, whatever) command and map it to return the appropriate object.