I learned how to use an OracleDataAdapter Parameter as an "out parameter" by setting it's direction = ParameterDirection.ReturnValue. Using this with an insert statement is a great way to return the primary key value of the newly inserted row.
Here is a simple example...
OracleDataAdapter oda = new OracleDataAdapter();
oda.InsertCommand = RemedyMD.CreateCommand();
oda.InsertCommand.CommandType = CommandType.Text;
oda.InsertCommand.CommandText =
"INSERT INTO users (name, email) VALUES (:name, :email) " +
"returning userID into :out";
oda.InsertCommand.Parameters.Add("name", OracleType.VarChar);
oda.InsertCommand.Parameters["name"].Value = userName;
oda.InsertCommand.Parameters.Add("email", OracleType.VarChar);
oda.InsertCommand.Parameters["email"].Value = userEmail;
oda.InsertCommand.Parameters.Add("out", OracleType.Number);
oda.InsertCommand.Parameters["out"].Direction = ParameterDirection.ReturnValue;
oda.InsertCommand.ExecuteNonQuery();
After executing the query, you can then pull the returned value out like this...
int newUserId = -1;
if(int.TryParse(oda .InsertCommand.Parameters["out"].Value.ToString(), out newUserId)){
// Perform additional query that required the newly inserted row's primary key
}
It was fairly hard for me to find (google) this information, so I figured I'd post it in hopes that it would help someone else.
1 comment:
Thanks for posting thhis
Post a Comment