CODEWITHSUNDEEP
c#.netsql-serverentity-framework
Legion
Legion

Reputation: 3427

DbContext ExecuteSQLCommand with no parameters

I'm trying to pass in a SQL string to delete some records. I'm using the DbContext.Database.ExecuteSqlCommand function, however in addition to the SQL string it expects a parameter list of the form: params Object[] parameters

Since I don't need to send any parameters I tried passing null instead but this generates an error saying null is not a valid parameter. I can't find an overload of the method. How do you send a SQL command requiring no additional parameters? Here's an example of my code:

db.Database.ExecuteSqlCommand("DELETE FROM SignoffCommentAttachment 
WHERE SignoffCommentAttachment.SignoffCommentID 
IN (SELECT [SignoffCommentID] FROM [SignoffComments] 
WHERE SignoffID = " + signOffID.ToString() + ")", null);

Upvotes: 2

Views: 6380

Answers (2)

Dustin Hodges
Dustin Hodges

Reputation: 4195

The parameters is a params parameter, so you can pass 0 to many objects for it. This should work

db.Database.ExecuteSqlCommand("DELETE FROM SignoffCommentAttachment WHERE SignoffCommentAttachment.SignoffCommentID IN (SELECT [SignoffCommentID] FROM [SignoffComments] WHERE SignoffID = " + signOffID.ToString() + ")");

You should really parameterize signOffID though. Something like

db.Database.ExecuteSqlCommand("DELETE FROM SignoffCommentAttachment WHERE SignoffCommentAttachment.SignoffCommentID IN (SELECT [SignoffCommentID] FROM [SignoffComments] WHERE SignoffID = {0})", signOffID);

Upvotes: 7

Jasmine
Jasmine

Reputation: 4029

In order to get a little SQL Injection Protection, you SHOULD use parameters here. You have one parameter. You should probably also be using a stored procedure, but that's another question.

I would code it like this...

var parms = new ParameterCollection();

parms.Add("signOffId", signOffID);

db.Database.ExecuteSqlCommand("DELETE FROM SignoffCommentAttachment 
WHERE SignoffCommentAttachment.SignoffCommentID 
IN (SELECT [SignoffCommentID] FROM [SignoffComments] 
WHERE SignoffID = @signOffID)", parms);

I didn't check my syntax there but that's the method I would use. Only I would use a stored procedure.

Upvotes: 3

Related Questions