Stored Procedures + Informix
Hello!
I'm trying to execute a stored procedure (on Informix 9.3/4) that returns a value from a c# class. So far, I'm able to execute procedure that doesn't return values, but somehow we can't get a call to a procedure that returns a value to work. I hope some of you can help me.
Here's my stored procedure, it's for adding in a new user in my user table, with creating a record in the history table. The procedure returns the id of the newly created user (the procedure works when run directly on informix)
Code:
CREATE FUNCTION addUser ( puserid VARCHAR(15), pname VARCHAR(30), ppasswort VARCHAR(50), pemail VARCHAR(50), pid INTEGER)
RETURNING INTEGER;
DEFINE iid INTEGER;
DEFINE datum CHAR(14);
INSERT INTO adm_benutzer (userid, name, passwort, email) VALUES (puserid, pname, ppasswort, pemail);
SELECT id INTO iid FROM adm_benutzer where id = (SELECT max(id) FROM adm_benutzer);
INSERT INTO adm_historie (fkbenutzerid, typ, vorgang, datum) VALUES (pid, 0, "Neuen Benutzer angelegt: ", TODAY);
RETURN iid;
END FUNCTION;
Now about my c# class or better the function of my DB access class:
Code:
public int addUser(string userid, string nachname, string passwort, string email, int execUserid)
{
OleDbConnection myCon = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
OleDbCommand myCommand= new OleDbCommand("addUser", myCon);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
// BenutzerId
OleDbParameter paruserid = new OleDbParameter("",OleDbType.VarChar,15);
paruserid.Value = userid;
myCommand.Parameters.Add(paruserid);
// Name
OleDbParameter parname = new OleDbParameter("",OleDbType.VarChar,30);
parname.Value = nachname;
myCommand.Parameters.Add(parname);
// Passwort
OleDbParameter parpw = new OleDbParameter("",OleDbType.VarChar,50);
parpw.Value = passwort;
myCommand.Parameters.Add(parpw);
// Email
OleDbParameter paremail = new OleDbParameter("",OleDbType.VarChar,50);
paremail.Value = email;
myCommand.Parameters.Add(paremail);
// Return Value - neue Id des Benutzers
OleDbParameter parId = new OleDbParameter("iid",OleDbType.Integer,4);
parId.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(parId);
try
{
// Open the connection and execute the Command
myCon.Open();
myCommand.ExecuteNonQuery();
}
catch(Exception ex)
{
string a = ex.Message;
// failed to create a new user
return -1;
}
finally
{
// Close the Connection
if (myCon.State == ConnectionState.Open)
myCon.Close();
}
return (int) parId.Value;
}
Any help would be greatly appreciated!!! Thanks a lot!
Nina