c# - 如何将内联 SQL 转换为 SQL Server 中的存储过程

Question

下面显示的代码是作为内联 SQL 语句创建的。这段代码怎么写成存储过程？？

代码是:

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

Answer 1

你可以这样做

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

那么你的代码就是这个表单

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

希望对你有帮助