Retrieve and print data from dynamic sql query with linq

Question

I have some existing code that retrieves data from a database using ADO.NET that I want to convert to linq.

What the code does is receives an SQL query via command line, executes it, returns the rows and their column names, then prints them to the screen. I would like to know how to write this code in linq.

The ENTIRE sql query MUST be given via command line since I want to limit where I select rows from. This is the only way I want it done, so unless if you have a method that can work this way, I cannot use it. No one will have access to the program aside from myself, so security is NOT an issue.

private static SqlConnection sqlConnection = new SqlConnection();

private static void OConnection()
{
    sqlConnection = new SqlConnection();
    sqlConnection.ConnectionString = MyConsoleApp.Properties.Settings.Default.ConnStr;
    sqlConnection.Open();
}

...

string query = Console.ReadLine();
OpenConn();
SqlCommand command = new SqlCommand(query, sqlConnection);
SqlDataReader reader = command.ExecuteReader();

if (reader != null)
{
    while (reader.Read())
    {
        for (int i = 0; i < reader.FieldCount; i++)
        {
            Console.Write("| {0}: {1}", reader.GetName(i), reader.GetValue(i));
        }
        Console.WriteLine();
    }
}

Answer 1

I don't see the value in moving to LINQ here. The beauty of LINQ is that you can write your query in the language of your domain objects, rather than in T-SQL. In your case you only want to accept actual SQL commands. It seems like it would be more work to deconstruct the command, turn it into LINQ -- using reflection to identify the appropriate tables in the data context -- and then execute the query only to print out the properties of the resulting objects.

Answer 2

You can add stored procedures to your database, and then add them into linq. Linq provides nice code generation to deal with your stored procedures getting and returning rows using deferred execution.

If you have a dynamic query that you wish to execute that isn't a stored procedure then you will need to either stick with ADO.Net or learn how to express your query in LINQ. Perhaps if you provide the query we could help you do this with your data context.

PS. When we moved to LINQ I found that the amount of boilerplate ADO.Net code we had to write dropped dramatically. I wrote my SPRoc mapped it to sql and used it straight away :).

Answer 3

There is Dynamic LINQ by Scott Gu which can be found here but as tvanfosson has already said you really aren't going to solve much with moving to Linq and most likely is more effort then result.