Is there a more cleaner or elegant way to pass multiple parameters to SQL stored procedure using Entity Framework?

Question

Right now I am passing multiple parameters to a stored procedure with Entity Framework and it looks like this..

public long AddDealer(Dealer dealer)
    {
        return Convert.ToInt32(AWJE.Database.SqlQuery<Dealer>(
            "usp_InsertDealer @Name, @Description",
            new SqlParameter("@DealerID", dealer.DealerID).Value,
            new SqlParameter("@Name", dealer.Name),
            new SqlParameter("@Description", dealer.Description)
            ));
    }

is there a more elegant or cleaner way to pass multiple parameters, instead of the way shown? If I come across other stored procedures that have many more parameters to be passed then this way looks like it will get messy real quick.

Answer 1

Your code example is clearly not a working example:

1. I don't see how you can convert an object of type Dealer to an int.
2. If your method is expected to return a long, then why convert the result of the stored procedure to an int?
3. You are passing a @DealerID parameter, but it's not part of the SP call.
4. Not sure why one of the SqlParameters has a call to .Value add onto it.

So, let me adjust it, and assume that your starting point is instead something like this:

public long AddDealer(Dealer dealer)
{
    return AWJE.Database.SqlQuery<Dealer>(
        "usp_InsertDealer @Name, @Description",
        new SqlParameter("@Name", dealer.Name),
        new SqlParameter("@Description", dealer.Description)
        ).DealerID;
}

Or maybe you decide that the SP returns a long directly, like this:

public long AddDealer(Dealer dealer)
{
    return AWJE.Database.SqlQuery<long>(
        "usp_InsertDealer @Name, @Description",
        new SqlParameter("@Name", dealer.Name),
        new SqlParameter("@Description", dealer.Description)
        );
}

Either way, the only simplification I see is that you can change the invocation to SqlQuery so that positional parameters are used instead of named parameters. Making that change allows you to forego the creation of explicit SqlParameter instances. The call could then be simplified to this:

public long AddDealer(Dealer dealer)
{
    return AWJE.Database.SqlQuery<long>(
        "usp_InsertDealer @Name, @Description",
        dealer.Name,
        dealer.Description
        );
}

... where how you name @Name or @Description no longer has any importance, but you have to make sure you pass in the parameter values in the right order.

Apart from this, I don't know that you can make this cleaner or more elegant.