CODEWITHSUNDEEP
c#asp.netsql-serversql-server-2008
user3825493
user3825493

Reputation: 31

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

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

Upvotes: 2

Views: 1082

Answers (2)

Dgan
Dgan

Reputation: 10295

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

Upvotes: 0

Monah
Monah

Reputation: 6794

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

Upvotes: 1

Related Questions