CODEWITHSUNDEEP
c#asp.netsql-servert-sql
Ashirvad
Ashirvad

Reputation: 2377

How to convert c # inline queries into stored procedure written in transact sql?

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

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.

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?

Upvotes: 1

Views: 2633

Answers (1)

Bryan
Bryan

Reputation: 17703

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.

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

Upvotes: 1

Related Questions