Can someone tell me the syntax for doing this? I can't find anything on https://github.com/StackExchange/dapper-dot-net
Here's what I have so far:
using (con = new SqlConnection(connectionString))
{
DynamicParameters param = new DynamicParameters();
Debug.WriteLine(Int32.Parse(ConfigurationManager.AppSettings["max-files-limit"]));
param.Add("@NumOfFiles", Int32.Parse(ConfigurationManager.AppSettings["max-files-limit"]));
con.Open();
IList<AsyncFileProcessingQueue> fileList = SqlMapper.Query<AsyncFileProcessingQueue>(con, "GetMostRecentFileStatus", param).ToList();
return fileList.ToList();
}
I'm getting this error,
Procedure or function 'GetMostRecentFileStatus' expects parameter '@NumOfFiles', which was not supplied.
GetMostRecentFileStatus
is a stored Procedure. I don't know if this might be the issue. but I was thinking that maybe it doesn't know I'm using a stored procedure?
I saw an example like this
var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);
int b = p.Get<int>("@b");
int c = p.Get<int>("@c");
But how can I get a List instead of outputs?
The stored procedure looks like this
CREATE PROCEDURE GetMostRecentFileStatus
(
@NumOfFiles INT
)
AS
BEGIN
SELECT TOP (@NumOfFiles) Filename, Status
FROM AsyncFileProcessingQueue
ORDER BY UpdatedDate DESC
END
Make sure you pass the parameters with the Query method.
This should work!
int numberOfFiles=2;
var q = @"exec GetMostRecentFileStatus @NumOfFiles";
using (con = new SqlConnection(connectionString))
{
return con.Query<YourFileTypeDto>(q, new { @NumOfFiles=numberOfFiles }).ToList();
}
You can replace the hardcoded value of numberOfFiles
variable with your an argumenet value of your method/read from somewhere
This should work:
int numFiles = ...
var list = connection.Query<SomeType>(
"GetMostRecentFileStatus",
new { NumberOfFiles = numFiles },
commandType:CommandType.StoredProcedure).AsList();