How to convert Inline SQL to a stored procedure in SQL Server

Question

My code shown below is create as an inline SQL statement. How can this code be written as a stored procedure??

The code is:

public Stream SelectEmployeeImageByID(int theID)
{
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString());
        string sql = "SELECT Image FROM Employees WHERE EmployeeId = @EmployeeId";
        SqlCommand cmd = new SqlCommand(sql, connection);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@EmployeeId", theID);

        connection.Open();
        object theImg = cmd.ExecuteScalar();

        try
        {
            return new MemoryStream((byte[])theImg);
        }
        catch
        {
            return null;
        }
        finally
        {
            connection.Close();
        }
    }

http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program — Debopam Chanda, Nov 11 '14 at 05:23
Do some research. Check this URL: http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program. Apart from this, there are many examples out there, use google. — Paresh J, Nov 11 '14 at 05:29

score 1 · Accepted Answer · answered Nov 11 '14 at 05:19

you can do this

create procedure SelectEmployeeImage(@employee int)
as
begin
   SELECT Image FROM Employees WHERE EmployeeId = @EmployeeId 
end

then your code will be this form

public Stream SelectEmployeeImageByID(int theID)
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString());
        string sql = "SelectEmployeeImage";
        SqlCommand cmd = new SqlCommand(sql, connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@EmployeeId", theID);
        connection.Open();
        object theImg = cmd.ExecuteScalar();
        try
        {
            return new MemoryStream((byte[])theImg);
        }
        catch
        {
            return null;
        }
        finally
        {
            connection.Close();
        }
    }

hope this will help you

Dgan · Answer 2 · 2014-11-11T05:23:21.743

Creating Stored Procedure

Create procedure SP_InsertEmployee
as
@EmployeeId int 
BEGIN

SELECT Image FROM Employees WHERE EmployeeId=@EmployeeId

END

You Should Set CommandType=StoredProcedure and Rest of will be same

cmd.CommandType = CommandType.StoredProcedure;

Recommendations

Always use using which automatically disposes connections

using (SqlConnection con = new SqlConnection())
{
con.open();
using (SqlCommand cmd = new SqlCommand(sql, connection))
{

//object theImg = cmd.ExecuteScalar();

}

con.Dispose();
}

How to convert Inline SQL to a stored procedure in SQL Server

2 Answers2