SQL Calls with Returned Value

In my last post, SQL Calls from ASP.NET, I talked about how to make a call to a stored procedure to query a database. But what do you do when the stored procedure uses an output parameters, such as SQL Stored Procedure: Save Data. Here is a sample method for making this database call:

using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

const string DBConn = "MyConnectionString";
public static int User_SaveProfile(int userID, string userFirstName, string userLastName)
{
Database db = DatabaseFactory.CreateDatabase(DBConn);
DbCommand cmd = db.GetStoredProcCommand("UserSave");

db.AddOutParameter(cmd, "@NewID", DbType.Int32, sizeof(Int32));
db.AddInParameter(cmd, "@UserID", DbType.Int32, userID);
db.AddInParameter(cmd, "@UserFirstName", DbType.String, userFirstName);
db.AddInParameter(cmd, "@UserLastName", DbType.String, userLastName);

db.ExecuteNonQuery(cmd);

return (Convert.ToInt32(db.GetParameterValue(cmd, "@NewID")));

}

Again, this is fairly straight forward code. It’s creating a Database object using the Database Factory, then building the a Database command with the appropriate parameters.

Once this is done, it makes the call to the database. This time however, it uses the method ExecuteNonQuery, which doesn’t return anything from the database, other than the output parameter of course. Lastly, it converts the output parameter into an integer, and returns it.

With this, the calling method can handle the returned value. If the stored procedure does error checking, it would be reflected in the @NewID that was returned. The calling method would then handle the error. Also, if zero (0) was passed in as the userID, and the returned @NewID is a positive number, a new record was added to the database. And if the @newID is equal to the userID, then an existing record was modified.

Advertisements
%d bloggers like this: