Creating methods for making Generic DB calls

Question

I am trying to write some methods which should be DB independent, so that the same code can be used irrespective of DB (Oracle, SQL server)

I am using IDbConnection and IDbCommand interfaces for this.. While calling the procedure it gives error saying Illegal Variable name/number error. Though I am able to call the inline query directly(Directly specifying the query as the command Text) Here is the sample call..

using (IDbConnection connection = this._providerFactory.CreateConnection())
                {
                    // create the command object using the conneciton object
                    IDbCommand command = connection.CreateCommand();
                    //start
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "GetResponse";

                   IDbDataParameter menuParam = this._providerFactory.CreateParameter();
                   menuParam.DbType = DbType.String;
                   menuParam.Direction = ParameterDirection.Input;
                   menuParam.ParameterName = "@V_USER_ID";
                   command.Parameters.Add(menuParam);

                   IDbDataParameter menuParam2 = this._providerFactory.CreateParameter();
                   menuParam2.Size = 20;

                   menuParam2.DbType = DbType.String;
                   menuParam2.Size = 20;
                   menuParam2.Direction = ParameterDirection.Output;
                   menuParam2.ParameterName = "@V_ROLE_ID";
                   menuParam2.Value = DBNull.Value;
                   command.Parameters.Add(menuParam2);
                    //end



                    command.ExecuteNonQuery();

}

Chris Gessler · Answer 1 · 2012-07-15T17:23:38.837

0

No value is being supplied here (and drop the @ symbol):

             IDbDataParameter menuParam = this._providerFactory.CreateParameter(); 
               menuParam.DbType = DbType.String; 
               menuParam.Direction = ParameterDirection.Input; 
               menuParam.ParameterName = "V_USER_ID"; 
               command.Parameters.Add(menuParam);

edited Jul 15 '12 at 17:23

answered Jul 15 '12 at 15:59

Chris Gessler

22,727
7
57
83

@Nits - updated my answer.. you're not providing a value for the first param. – Chris Gessler Jul 15 '12 at 16:54
still no luck :( getting the same error ORA-01036: illegal variable name/number – Nits Jul 15 '12 at 17:19
that is working actually, but again when the OUT parameter in proc is REf_Cursor it doesn't work. I am using menuParam2.DbType = DbType.Object; As did not geother option like Cursor – Nits Jul 15 '12 at 17:32
Not sure on the cursor... read this post, they use RefCursor http://stackoverflow.com/questions/6360244/how-to-call-an-oracle-function-with-a-ref-cursor-as-out-parameter-from-c – Chris Gessler Jul 15 '12 at 17:48
by using OracleParameter explicitly then it works, but I have to use menuParam.DbType = DbType.String; like thing to make it generic, but there is no option like Cursor inthis enum – Nits Jul 15 '12 at 18:09
@Nits - just remove the second param as it's created automatically and called cur_OUT. See here: http://www.codeproject.com/Articles/37079/Returning-Multiple-Ref-Cursors-from-Oracle-using-D – Chris Gessler Jul 15 '12 at 18:18
this give me error saying mismatch in number of parameter calling the stored proc.. – Nits Jul 16 '12 at 08:41
hmmm... See if you can discover the param and reverse engineer it. `Database db = DatabaseFactory.CreateDatabase(); DbCommand dbCommand = b.GetStoredProcCommand( "MyPLSQLfunction"); db.DiscoverParameters( dbCommand );` – Chris Gessler Jul 16 '12 at 08:54

Creating methods for making Generic DB calls

1 Answers1