如何将c＃内联查询转换为用transact sql编写的存储过程？

I have lots of SQL queries written inline in C# using .net framework. For Example

我有很多使用.net框架在C＃中内联编写的SQL查询。例如

string sMysql = @"
SELECT
[Something]
from Person where person_uid=12"

I want it to convert it to stored procedures which will be written in transact SQL.

我希望它将它转换为将用transact SQL编写的存储过程。

Something like this:

像这样的东西：

CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables]
    @uid int
AS
BEGIN
SELECT
    [Something]
    from Person where person_uid=@uid
END

I can do it manually but I have lots of inline queries to convert. Is there a way to do this hectic job programmatically?

我可以手动完成，但我有很多内联查询要转换。有没有办法以编程方式完成繁忙的工作？

1 个解决方案

#1

For your specific example, throw out the stored procedure idea, and change your code snippet to the following:

对于您的具体示例，请抛弃存储过程的想法，并将您的代码段更改为以下内容：

// initialize UID value and SQL query with parameter placeholder
int uid = 12;
sql = "SELECT [Something] FROM [Person] WHERE [person_uid] = @UID";

// initialize connection and open
using (SqlConnection connection = new SqlConnection("<connection string>")
{     
    SqlCommand command = new SqlCommand(sql, connection)
    // add UID parameter
    command.Parameters.Add(new SqlParameter("UID", uid);

    try
    {
        connection.Open();        
        // execute and read results
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            // process results        
        }
    }
    catch (Exception ex)
    {
        // handle exceptions
    }
}

As stated in question comments, stored procedures aren't necessarily faster than inline parameterized queries. SQL Server will even cache execution plans for queries that aren't parameterized, but I prefer the explicit declaration of parameters.

如问题评论中所述，存储过程不一定比内联参数化查询更快。 SQL Server甚至会为未参数化的查询缓存执行计划，但我更喜欢显式声明参数。

Take a look at this article on execution plan caching and reuse for SQL Server 2008 if you want more information.

如果您想了解更多信息，请查看有关SQL Server 2008的执行计划缓存和重用的文章。

#1