使用linq从动态sql查询中检索和打印数据

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

我有一些现有的代码,使用我想转换为linq的ADO.NET从数据库中检索数据。

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.

代码所做的是通过命令行接收SQL查询,执行它,返回行及其列名,然后将它们打印到屏幕上。我想知道如何在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.

ENTIRE sql查询必须通过命令行给出,因为我想限制从哪里选择行。这是我想要它完成的唯一方法,所以除非你有一个可以这样工作的方法,否则我不能使用它。除了我自己,没有人可以访问该程序,因此安全性不是问题。

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();
    }
}

3 个解决方案

#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.

我没有看到迁移到LINQ的价值。 LINQ的优点在于您可以使用域对象的语言编写查询,而不是使用T-SQL编写查询。在您的情况下,您只想接受实际的SQL命令。似乎解构命令,将其转换为LINQ - 使用反射来识别数据上下文中的适当表格 - 然后仅执行查询以打印出结果对象的属性似乎更有效。

#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.

您可以将存储过程添加到数据库,然后将它们添加到linq中。 Linq提供了很好的代码生成来处理使用延迟执行获取和返回行的存储过程。

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.

如果您希望执行的动态查询不是存储过程,那么您需要坚持使用ADO.Net或学习如何在LINQ中表达您的查询。也许如果您提供查询,我们可以帮助您使用您的数据上下文。

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 :).

PS。当我们转移到LINQ时,我发现我们必须编写的样板ADO.Net代码的数量急剧下降。我写了我的SPRoc映射到sql并直接使用它:)。

#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.

Scott Gu的动态LINQ可以在这里找到,但是由于tvanfosson已经说过你真的不会解决很多问题,因为转移到Linq并且最有可能的是努力然后结果。

#1

#2