CODEWITHSUNDEEP
c#mysqlstringodbcprocedure
Neurodefekt
Neurodefekt

Reputation: 909

Calling stored procedures through ODBC without declaring parameter placeholders

I'm currently trying to abstract the calling of stored procedures through ODBC by making a function that requires a procedure name as string and a set of keys and values as a dictionary. Because of this, I have to build up a string for the ODBC Command which is.. yeah, silly.

CALL myProcedure(?, ?, ?, ?)

and then with the ODBC command object:

odbcCommand.Parameters.AddWithValue("key", "param");

Now for adding the parameters I can easily do a foreach. Or even LINQ-style with Select. But I also have to create the CALL-string with the question marks where I have to be careful that something like this never happens:

CALL myProcedure(?, ?, ?, )

So I have to loop one time through the dictionary for adding the parameters and a second time for joining the keys and values to a string (String.Join) or I loop through everything with a FOR-loop and have to check if the current KeyValuePair is the last one just to prevent accidentally commata at the end of the string.

Isn't there a way where I just can declare "CALL myProcedure" or even something like

odbcCommand.CommandType = CommandTypes.StoredProcedure;
odbcCommand.ProcedureName = "myProcedure";
[LOOP]
     - add Params damn comfortable
[/LOOP]

I hope there is.. it would help me reduce an extra step required because of nonesense!

Thanks

Upvotes: 1

Views: 313

Answers (1)

Al Harrison
Al Harrison

Reputation: 85

After adding the parameters, use this:

int paramCount = odbcCommand.Parameters.Count();
string[] placeholders = Enumerable.Repeat("?", paramCount).ToArray();
string callParams = String.Join(", ", placeholders);

Then you no longer have to be careful to avoid the trailing comma referred to in the CALL-string. (I realize this doesn't fully answer your question of eliminating the placeholders.)

Upvotes: 1

Related Questions