c# - Returning a value from a stored procedure to a method -

i have stored procedure returns whether student locked or not:

return @islocked 

i execute stored procedure like:

 public int isstudentlocked(string studentname, int lockouttime) { sqlconnection connobj = new sqlconnection(); connobj.connectionstring = util.studentdatainsert(); connobj.open(); sqlcommand comm = new sqlcommand("uspchecklockout", connobj); comm.commandtype = commandtype.storedprocedure; comm.parameters.add(new sqlparameter("@studentname", studentname)); comm.parameters.add(new sqlparameter("@lockouttime", lockouttime)); comm.executenonquery(); connobj.close(); //how can return @islocked value below? return ((int)(@islocked)); } 

to use return statement in t-sql (which can return integer values), have add parameter retrieve it:

public int isstudentlocked(string studentname, int lockouttime) { sqlconnection connobj = new sqlconnection(); connobj.connectionstring = util.studentdatainsert(); connobj.open(); sqlcommand comm = new sqlcommand("uspchecklockout", connobj); comm.commandtype = commandtype.storedprocedure; comm.parameters.add(new sqlparameter("@studentname", studentname)); comm.parameters.add(new sqlparameter("@lockouttime", lockouttime)); var returnparam = new sqlparameter { parametername = "@return", direction = parameterdirection.returnvalue }; comm.parameters.add(returnparam); comm.executenonquery(); var islocked = (int)returnparam.value; connobj.close(); return islocked; } 

however, kinda messy (imo). in case select value want last statement in stored procedure. use executescalar on command object retrieve value instead of executenonquery.

proc:

... sql ... select @islocked 

method:

public int isstudentlocked(string studentname, int lockouttime) { using(sqlconnection connobj = new sqlconnection()) { connobj.connectionstring = util.studentdatainsert(); connobj.open(); sqlcommand comm = new sqlcommand("uspchecklockout", connobj); comm.commandtype = commandtype.storedprocedure; comm.parameters.add(new sqlparameter("@studentname", studentname)); comm.parameters.add(new sqlparameter("@lockouttime", lockouttime)); return (int)comm.executescalar(); } }