Retrieving and printing data from a dynamic SQL query using linq

Question

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

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

A FULL sql query MUST be provided via the command line, since I want to limit the selection of rows. This is the only way I want to do this, so if you have a method that can work this way, I cannot use it. No one will have access to the program except me, so security is NOT a problem.

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 do not see the value when moving to LINQ here. The beauty of LINQ is that you can write your query in the language of your domain objects, and not in T-SQL. In your case, you only want to accept the actual SQL commands. It seems like it would be more work to deconstruct the command, turn it into LINQ - using reflection to determine the corresponding tables in the data context - and then run the query only to print the properties of the resulting objects.

Answer 2

You can add stored procedures to your database and then add them to linq. Linq provides excellent code generation to handle your stored procedures for retrieving and returning strings using deferred execution.

If you have a dynamic query that you want to execute that is not a stored procedure, you need to either insert ADO.Net or learn how to express the query in LINQ. Perhaps if you provide a request, we could help you do this using your data context.

PS. When we switched to LINQ, I found that the amount of ADO.Net code we were supposed to write was drastically reduced. I wrote that SPRoc matched it with sql and used it right away :).

Answer 3

There is a dynamic LINQ from Scott Gu, which can be found here , but since tvanfosson has already said that you really are not going to decide a lot with switching to Linq and, most likely, more effort than the result.