C# how to Parse stored procedure String

Question

Hi.

I have a Query string from client application. It comes with all parameters like

string query="PROCS.DBO.APP_2370_ANALYST_S 'ABC' , 'TESTDATA' , 100";  

In Server, I made a function(Util.getParametersFromString) to parse string from client application to make parameter object Array using string.Split function. I used ',' and ' ' as separator to make object array.

And I execute db procedure by using below code

 object[] parameters = Util.getParametersFromString(query); 
 DbCommand cmd = dbconnection.GetStoredProcCommand("PROCS.DBO.APP_2370_ANALYST_S",     parameters);

I works well if parameter string doesn't contain comma or single quotation mark. If one of parameter string have one or more comma or single quotaion mark. Like below

string query="PROCS.DBO.APP_2370_ANALYST_S 'A,B,C' , 'Hi, Sam 'The Legend' Brown  was here ' , 100";  

parameter array didn't come correctly. I didn't know how to parse string correctly in this

situation. Please give me advice to solve this problem

I am not good at english. So I am so sorry If I didn't write my question correctly

Regards, Park

Answer 1

You can escape the single quotes - ' becomes '':

string query="PROCS.DBO.APP_2370_ANALYST_S 'A,B,C' , 'Hi, Sam ''The Legend'' Brown  was here ' , 100"; 

As for the problem with a comma - it depends on how your function is written. You will have to escape the comma and make sure your function is aware of this escape sequence.

Answer 2

If both parameters of your query string are as flexible as your example, and you cannot change the way this string is generated as suggested in Oded's answer, you have a problem.

The query "PROCS.DBO.APP_2370_ANALYST_S 'ABC' , 'ABC' , 'ABC' , 100" for example, could be interpreted as having the first parameter "'ABC' , 'ABC'" and second parameter "ABC" or vice versa.

If, on the other hand, your first parameter may not contain 's, then you could identify the first parameter by looking between the first two 's, and the second parameter by falling between the third and the last '.