c# - What is happening when dbcontext.Database.ExecuteSqlCommand is called? -


i using entity framework code first database. i'm trying run existing unmapped stored procedure, using dbcontext.database.executesqlcommand, dynamically creating query , parameter array based on values passed in json (there many optional parameters). not expecting return value.

when test service, sqlexceptions being thrown within stored proc:

cannot insert value null column 'yyyyww', table 'dbname.dbo.table'; column not allow nulls.

but when run same query using same values ssms '12, there no errors. table allow nulls column.

exec [dbname].[dbo].[storedproc] @yyyymm = 201409, @s_id = 75 

passing optional string parameter causes exception converting nvarchar int, doesn't happen in stored proc. i've tried few different ways set call, throw same exceptions.

version 1:

string query1 = "exec [dbname].[dbo].[storedproc] @yyyymm, @s_id";  list<sqlparameter> parms1 = new list<sqlparameter>(); parms1.add(new sqlparameter("@yyyymm", obj.yyyymm)); parms1.add(new sqlparameter("@s_id", obj.s_id));  db.database.executesqlcommand(query, parms1.toarray()); 

version 2:

string query2 = "exec [dbname].[dbo].[storedproc] @yyyymm, @s_id";  var month = new sqlparameter("yyyymm", sqldbtype.int); month.value = obj.yyyymm; var s_id = new sqlparameter("s_id", sqldbtype.int); s_id.value = obj.s_id;  list<sqlparameter> parms2 = new list<sqlparameter>(); parms2.add(month); parms2.add(s_id);  db.database.executesqlcommand(query, parms2.toarray()); 

version 3:

string query3 = "exec [dbname].[dbo].[storedproc] @yyyymm = {0}, @s_id = {1}"; db.database.executesqlcommand(query3, obj.yyyymm, obj.s_id); 

any thoughts on happening - why values being treated differently? there better way this?

here working example code have used:

public int salesbycategory(string categoryname, string ordyear)     {         var categorynameparameter = categoryname != null ?             new sqlparameter("@categoryname", categoryname) :             new sqlparameter("@categoryname", typeof (string));          var ordyearparameter = ordyear != null ?             new sqlparameter("@ordyear", ordyear) :             new sqlparameter("@ordyear", typeof (string));          return database.executesqlcommand("salesbycategory @categoryname, @ordyear", categorynameparameter, ordyearparameter);     } 

if structure doesn't work reason, try turning sql profiler on see happening.


edit:
got code this awesome project long le. i've been using framework while , fantastic.


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -