What is the correct syntax for using Database.ExecuteSqlCommand with parameters?

entity-framework sql sql-server

Question

I want to call a stored method with input parameters using Entity Framework 4.2. I useDatabase.ExecuteSqlCommand to invoke the stored command.

The proper call syntax, nevertheless, is missing from the documentation in order to properly translate the arguments. I need assistance because my google-foo isn't working.

I.e. I must follow a process.

procedure SetElementFrequency
  @ElementTypeID integer,
  @Frequency float
as ...

I've attempted to call it with

Database.ExecuteSqlCommand("exec SetElementFrequency @p0 @p1", 
                            elementType, frequency);

and

Database.ExecuteSqlCommand("exec SetElementFrequency {0} {1}", 
                            elementType, frequency);

nonetheless, they both make the mistake Uncorrected syntax close to "@p1"..

1
10
3/1/2013 7:34:36 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

You may use one of the following methods, depending on the underlying database supplier.

Database.ExecuteSqlCommand(
    "exec SetElementFrequency {0}, {1}",
    elementType, frequency);

or

Database.ExecuteSqlCommand("exec SetElementFrequency ?, ?", elementType, frequency);

You may add details.elementType and frequency by using the ParameterName attribute to offer your own names as DbParameter-based objects.

16
6/5/2013 5:08:45 PM

Popular Answer

var sql = @"Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
Gho5t

Related Questions

With the output argument, run SQLCommand.

I had to use this in the end, but there must be a better approach:...var p = new SqlParameter { ParameterName = "token", DbType = System.Data.DbType.String, Size = 100, Direction = System.Data.ParameterDirection.Output }; var resp = this.Database.SqlQuery<String>("exec dbo.usp_GetRequestToken @token", p); return resp.First();
asp.net-mvc asp.net-mvc-3 entity-framework
asked by Sam Huggill

Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax

Recursive CTEs are not supported by EF or LINQ, as far as we know. The CTE should be made available as a view as a fix. How to deploy such a view using EF code first migrations is shown in the article on ...Migrations with EF Code First queries that are recursive or hierarchical.......Recursive client side iterations used to simulate CTEs do not sc...
c# entity-framework linq sql-server
asked by Remus Rusanu

When executing a command, parameters must be exclusively database parameters or values

The output value is not retrievable by me. I obtain the subsequent exception:...InvalidOperationException exception type:...A command's parameters must only be values or parameters from the database....Code Method in C#...public Student InsertNewStudentApplication(Student student) { Student newStudent = null; SqlParameter returnParam = new ...
c# entity-framework entity-framework-6 entity-framework-6.1 stored-procedures
asked by Ernie R.

Database.ExecuteSqlCommand returns incorrect rowcount

To update a table, I'm using context.Database.ExecuteSql. The record is updated and the update with where clause is correctly carried out. But rather than returning 1 for rowcount, the procedure returns 2. The result rowcount provided when I run the update command in SSMS is 1. Can somebody provide some light on this?... string query = ...
dml entity-framework-6
asked by Charles Arthur Wuthrich III

Incorrect Syntax Near '0' - when using sp_executeSQL to execute stored procedure with output parameter

It is the actual stored procedure....ALTER Procedure [dbo].[ApplyList] @oldlist int, @username varchar(50), @newlist int, @errormessage varchar(2000) output AS BEGIN SET NOCOUNT ON; SET @errormessage = ''; END ...This SQL code was produced by Entity Framework 6.1 using the Database First methodology....declare @p6 varchar(2000); se...
entity-framework-6 sp-executesql sql-server
asked by manu97

Get return value from stored procedure using ExecuteSqlCommand (using Entity Framework)

ZZZ_tmp
azure-sql-database c# entity-framework sql-server stored-procedures
asked by Zain Rizvi

Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

The parameters in the SQL string you are performing are missing. Attempt setting up your parameters with a "@" before the name, and then alter the ExecuteSqlCommand call to:...context.Database.ExecuteSqlCommand("exec MySchema.MyProc @customerId, @indicatorTypeId, @indicators, @startDate, @endDate", customerIdParam, typeIdParam, tableParameter, star...
c# entity-framework sql-server stored-procedures
asked by strickt01

is it possible to use a range variable from a linq (query syntax) as a parameter?

This is not how you can do it. Think about having Entity Framework analyse your LINQ query and turn it into a SQL query. It examines everything, even down to...funstuff(table2)... call. It notices that it's a method call expression that it is unfamiliar with (so it's not anything like...SqlFunctions.GetDate()... ). Okay, thus in order to acquire ex...
c# entity-framework-6 linq
asked by Evk

Entitiy Framework context.Database.ExecuteSqlCommand won't save changes to DB

My online application uses EF, and I've come across an odd issue. In my database, I'm attempting to edit a record, however even if the...context.Database.ExecuteSqlCommand... Whenever call returns 1, I then call...context.SaveChanges()... , it returns 0, and the database does not reflect the modifications....This is the key:...List<int> ids = new L...
entity-framework-6 sql-server
asked by Stefan

Why stored procedure out parameter is not getting read by entity framework's ExecuteSqlCommand method?

ZZZ_tmp
c# entity-framework entity-framework-6 sql sql-server
asked by azure boy

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow