ygongdev ygongdev - 5 months ago 45
SQL Question

How to select data from database and return it via a WCF service?

I have a stored procedure that gets the

Filename
and
Path
(2 columns) from the database. I need to make a WCF service that returns the data into a format that any application that uses the service can use like a
List<string>
or something along the lines.

What's the best way to go about this (design and implementation-wise)?

Here's what I tried doing via Dapper ORM, but I'm not sure if it will work as intended.

public IEnumerable<dynamic> RetrieveIncompletedFiles(int type)
{
try
{
using (_connection = new SqlConnection(_sqlConnectionString))
{
_connection.Open();
var files = _connection.Query("SelectIncompletedFilesByFiletypeFromAsyncFileProcessingQueue", new { Filetype = type }, commandType: CommandType.StoredProcedure);
return files;
}
}
catch (Exception ex)
{
return null;
}
}

Answer

That will almost certainly not work as expected. WCF wants to work against a known schema and a simple data type, typically a List<SomeType> where SomeType is a public class that is marked as a [DataContract] with [DataMember] elements. Fortunately, the dapper part of this should be as easy as:

 public List<SomeType> RetrieveIncompletedFiles (int type)
{
    using (connection = new SqlConnection(_sqlConnectionString))
    {
        return connection.Query<SomeType>("SelectIncompletedFilesByFiletypeFromAsyncFileProcessingQueue", new { Filetype = type }, commandType: CommandType.StoredProcedure).AsList();
    }
}