1

I have a stored procedure that has many parameters and I've been using the following to return the results:

db.Database.SqlQuery<GetListCamera_Result>("Camera.sp_get_list_camera @room_name, @disk_status, 
    @signal_loss_reason, @department_id , @sortColumnName, @sortDirection, @start, @length",
                    new SqlParameter("room_name", room_name),
                    new SqlParameter("disk_status", disk_status),
                    new SqlParameter("department_id", department),
                    new SqlParameter("signal_loss_reason", reason),
                    new SqlParameter("sortColumnName", sortColumnName),
                    new SqlParameter("sortDirection", sortDirection),
                    new SqlParameter("start", start),
                    new SqlParameter("length", length)).ToList();

I saw one of my senior using these much more cleaner than mine:

db.Database.SqlQuery<GetLiquidbooks_Result>("sp_get_liquidbooks {0}, {1}, {2}, {3}, {4}",
    new object[] { LiquidCode, LibID, LocPrefix, LocID, UserID }).ToList();

What are the differences and is there anything I need to be aware of if I'm switching to his

Improve this question

1 Answer 1

Reset to default
1

I think yours is a much safer way. However, if you'd like to make it simpler, you can refer to this article, you don't have to initialize a SqlParameter instance, but the @p1 @p2 syntax is still essential. In the first section of the article, it also mentions that this care should be taken.

AFAIK, SqlQuery doesn't prevent SQL Injection, which means if I pass the DROP command into your second sample, the table may be deleted permanently. Therefore, the one which the senior wrote might expose potential security risks, you should make sure that you use parameters in your query in the correct way to guard against such attacks.

About the second sample, consider using ObjectContext.ExecuteStoreQuery<T>(), it allows you to pass the query string with {0} {1} syntax and object array as the parameter into the method. This method actually invokes CreateStoreCommand which transforms your query and objects into a parameterized query. But SqlQuery seems not.

FYI:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.