Olukayode Oluwole
asked on
How do constitute a parameter string for a stored procedure
I have a c# application in which i need to read datat from a postgresql database
using Dapper.
I have my parameter field names from the stored procedure and the value
i want to pass in from the application. They are stated below: I have tested the store procedure
with the parameter values and it worked
PARAMETER NAME VALUE
locationidx "x"
locdescription "x"
companycodex "NAUTILUS"
idx 0
dowhatx "AR"
The Line calling the method from my c# application is shown below
newList2 = NewDataAccess.ReadData<LocationModel>("public.splocationcode_getall", r);
The called method is shown below
[code]public static List<T> ReadData<T>(string sql, object p)
{
// var output = default(List<T>); //This will return null in the case of an exception.
var output = new List<T>(); // If you want to return an empty list instead, use this
try
{
using (var conn = new NpgsqlConnection(LoginDetails.staticconnectionstring))
{
output = conn.Query<T>(sql, p, commandType: CommandType.StoredProcedure).ToList();
}
//using (var conn = new NpgsqlConnection(LoginDetails.staticconnectionstring))
//{
// output = conn.Query<T>(sql, new {locationidx = "x", locdescriptionx = "x", companycodex = "NAUTILUS",idx = 0,dowhatx = "AR" }, commandType: CommandType.StoredProcedure).ToList();
//}
}
}[/code]
I need to get parameter r constituted exactly as
locationidx = "x", locdescriptionx = "x", companycodex = "NAUTILUS",idx = 0,dowhatx = "AR"
with all quotation marks and commas in place .
If I the pass it into the method as object p then my ReadData would work correctly.
The portion of the method that is commented out shows a manually constituted r
parameter that worked.
The problem i have is how to constitute the r parameter from my c# application given that i know all the parameter names and their values as stated above.
I will be grateful for any assistance
Olukay
using Dapper.
I have my parameter field names from the stored procedure and the value
i want to pass in from the application. They are stated below: I have tested the store procedure
with the parameter values and it worked
PARAMETER NAME VALUE
locationidx "x"
locdescription "x"
companycodex "NAUTILUS"
idx 0
dowhatx "AR"
The Line calling the method from my c# application is shown below
newList2 = NewDataAccess.ReadData<LocationModel>("public.splocationcode_getall", r);
The called method is shown below
[code]public static List<T> ReadData<T>(string sql, object p)
{
// var output = default(List<T>); //This will return null in the case of an exception.
var output = new List<T>(); // If you want to return an empty list instead, use this
try
{
using (var conn = new NpgsqlConnection(LoginDetails.staticconnectionstring))
{
output = conn.Query<T>(sql, p, commandType: CommandType.StoredProcedure).ToList();
}
//using (var conn = new NpgsqlConnection(LoginDetails.staticconnectionstring))
//{
// output = conn.Query<T>(sql, new {locationidx = "x", locdescriptionx = "x", companycodex = "NAUTILUS",idx = 0,dowhatx = "AR" }, commandType: CommandType.StoredProcedure).ToList();
//}
}
}[/code]
I need to get parameter r constituted exactly as
locationidx = "x", locdescriptionx = "x", companycodex = "NAUTILUS",idx = 0,dowhatx = "AR"
with all quotation marks and commas in place .
If I the pass it into the method as object p then my ReadData would work correctly.
The portion of the method that is commented out shows a manually constituted r
parameter that worked.
The problem i have is how to constitute the r parameter from my c# application given that i know all the parameter names and their values as stated above.
I will be grateful for any assistance
Olukay
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
var parameters = new { locationidx = "x", locdescriptionx = "x", companycodex = "NAUTILUS", idx = 0, dowhatx = "AR" };
var newList2 = NewDataAccess.ReadData<LocationModel>("public.splocationcode_getall", parameters);
It worked and is ok to implement
Can i confirm your suggestion of not littering this code is the same as checking it into a library and calling it from there or you have a better way
Thanks