Passing Parameters when doing a Post in C# to call a stored Procedure

REST API
1

First of all, I am new, so please allow myself to introduce myself… .ok… :wink:

I am trying to call a store procedure using this call

http://localhost:8080/rest/MyDb/_proc/sp_GetUsersWithinKms

I tried it with the Live API… Works well, then in my C# code using HttpClient and GetAsync… works well too.

I then added a simple parameters to my stored procedure in sql server. I was able to add the parameter in the Post Call in the live API

{“params”:[{“name”:“Kms”,“param_type”:“IN”,“value”:“50”,“type”:“float”,“length”:""}],“schema”:null,“wrapper”:null,“returns”:null}

and again it worked.

I then tried to use the same logic in the C# application. but I keep getting a 500 error, referring to

Failed to call database stored procedure.
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Procedure or function ‘sp_GetUsersWithinKms’ expects parameter ‘@Kms’, which was not supplied.

My entire code is here

static async void getit2()
{
const string URL = “http://localhost:8080/”;
HttpClient client = new HttpClient();
client.BaseAddress = new Uri(URL);
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));

var username = "xxxxxxx@gmail.com";
var password = "xxxxxxxx";

var byteArray = Encoding.ASCII.GetBytes(string.Format("{0}:{1}",username, password));

client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));


var ro = new RootObject();

ro.@params = new List<Param>();


var param = new Param();
param.name = "Kms";
param.value = "50";
param.type = "float";
param.length = "";
param.param_type = "IN";
ro.@params.Add(param);

var json = new JavaScriptSerializer().Serialize(ro);
 


var sc = new StringContent(json, Encoding.UTF8, "application/json");  // GetAsync("rest/TradeMeDb/_proc/sp_GetUsersWithinKms?app_name=TradeMe";

sc.Headers.ContentLength.Dump();


client.DefaultRequestHeaders.Add("X-DreamFactory-Application-Name", "TradeMe");

HttpResponseMessage response = await client.PostAsJsonAsync("rest/TradeMeDb/_proc/sp_GetUsersWithinKms", sc);

HttpContent content = response.Content;

Console.WriteLine(response.StatusCode);

string result = await content.ReadAsStringAsync();

result.Dump();
return;

	JavaScriptSerializer ser = new JavaScriptSerializer();
		
		RootObject RootObject = ser.Deserialize<RootObject>(result);
		
		RootObject.Dump();
	

result.Dump();

}

There are a lot of returns to do some tests, but i get far enough to catch the error.

Does anyone have any ideas what i am doing wrong. The parameters are not passed, but the json i generate, if i take it a use it in the live api, it works.

EDIT

I have also noted that the name of the parameters passed to the live api does not matter, i can put in whatever name, it works… that feels like a bug

2

OK I Figured out a way to get it to work. I am sure I can develop around that…

static async void getit3()
{

var ro = new RootObject();
ro.@params = new List<Param>();

var param = new Param();
param.name = "Kms2";
param.value = "100";
param.type = "float";
param.length = "";
param.param_type = "IN";
ro.@params.Add(param);


var param2 = new Param();
param2.name = "Kms12";
param2.value = "B9FD1807-1122-448E-AEEE-123B4B3F55E4";
param2.type = "uniqueidentifier";
param2.length = "";
param2.param_type = "IN";
ro.@params.Add(param2);

var json = new JavaScriptSerializer().Serialize(ro);


 var myclass = Unirest.post("http://localhost:8080/rest/TradeMeDb/_proc/sp_GetUsersWithinKms")
.basicAuth("fred.morin.cubi@gmail.com", "stack123")
.header("accept", "application/json")
.header("X-DreamFactory-Application-Name", "TradeMe")
.body(json)
.asJsonAsync<List<ReturnValue>>();

await myclass.Dump();

}

I use the UniRest DLL

I still have the issue where the parameter name is not used…

3

You are correct, this is a limitation of the driver implementation in DSP 1.x. You will need to always provide the stored procedure’s parameters in the order in which they appear in the procedure, because parameter names are not being matched. A new implementation of this, which will match parameter names provided from the client with parameters in the stored procedure, is planned for inclusion in DSP 2.0.