Calling a stored procedure using DataContext.ExecuteCommand

This article provides example code showing how to call a SQL Server T-SQL stored procedure using DataContext ExecuteCommand. This technique can be used when the DataContext does not have the stored procedure mapped to a method.

Sample Code

using (var dc = new MyDataContext(MyConnectionString))
{
  dc.ExecuteCommand("dbo.MyStoredProcedure {0} {1} {2} {3}", "abc", 123, 5.5, 'c');
}

LINQ to SQL Output

dbo.MyStoredProcedure @p0, @p1, @p2, @p3
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [abc]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [123]
-- @p2: Input Float (Size = -1; Prec = 0; Scale = 0) [5.5]
-- @p3: Input NChar (Size = 1; Prec = 0; Scale = 0) [c]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.8.4084.0

Performance

LINQ to SQL automatically determines the input parameter types, so it requires far less code than using SqlCommand.

Since Entity Framework 5 (.NET 4.0, VS 2012), the query is automatically compiled and cached for re-use. This makes the above techique high performance even when the code is called repeatedly.

Syntax Variations and Errors

Including EXEC

If you include EXEC at the beginning of the SQL command string, like this:

"EXEC dbo.MyStoredProcedure {0} {1} {2} {3}"

It will work the same.

Including Parenthesis

If you use parenthesis in the SQL command string like this:

"dbo.MyStoredProcedure({0} {1} {2} {3})"

Then you will get this error: "Incorrect syntax near '@p0'."

Missing Parameter Placeholders

If you leave out the parameter placeholders like this:

dc.ExecuteCommand("dbo.MyStoredProcedure", "abc", 123, 5.5, 'c');

Then you will get this error: "Procedure or function 'MyStoredProcedure ' expects parameter '@Parameter1', which was not supplied."

Refer to this link to see more examples of how to use DataContext.ExecuteCommand.