I try to implement the 'AsyncPattern' within a WCF data service. I define the 2 methods BeginGetExperiments(...) and EndGetExperiments(...) in the interface and implement the methods as can be seen below.
public class GmdProfileService : IGmdProfileService
{
IAsyncResult IGmdProfileService.BeginGetExperiments(AsyncCallback callback, object state)
{
//IAsyncResult res = Experiment.GetExperimentsAsync(callback, state, Properties.Settings.Default.gmdConnectionString);
//return res;
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder(Properties.Settings.Default.gmdConnectionString);
csb.AsynchronousProcessing = true;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(csb.ConnectionString);
conn.Open();
System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, name, comment, date, doi FROM tf.TagList WITH(NOLOCK) WHERE proprietary=0;";
cmd.CommandType = System.Data.CommandType.Text;
return new SqlCommandAsyncResult(cmd, callback, state);
}
public List<Experiment> EndGetExperiments(IAsyncResult result)
{
List<Experiment> res = new List<Experiment>();
SqlCommandAsyncResult myresult = result as SqlCommandAsyncResult;
using (System.Data.SqlClient.SqlDataReader reader = myresult.cmd.EndExecuteReader(myresult.originalState as IAsyncResult))
{
try
{
while (reader.Read())
{
res.Add(new Experiment(reader));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
// Closing the reader also closes the connection, because this reader was created using the CommandBehavior.CloseConnection value.
if (reader != null)
{
reader.Close();
}
}
}
return res;
}
BeginGetExperiments returns a class SqlCommandAsyncResult implementing the IAsyncResult interface in addition to holding a reference to my SqlCommand for later access.
public class SqlCommandAsyncResult : IAsyncResult
{
public SqlCommand cmd { get; private set; }
public IAsyncResult originalState { get; private set; }
public SqlCommandAsyncResult(SqlCommand cmd, AsyncCallback callback, object state)
{
this.cmd = cmd;
this.originalState = cmd.BeginExecuteReader(callback,
state,
System.Data.CommandBehavior.SequentialAccess | // doesn't load whole column into memory
System.Data.CommandBehavior.CloseConnection // close connection immediately after read
);
}
public object AsyncState
{
get { return originalState.AsyncState; }
}
public WaitHandle AsyncWaitHandle
{
get { return originalState.AsyncWaitHandle; }
}
public bool CompletedSynchronously
{
get { return false; }
}
public bool IsCompleted
{
get { return AsyncWaitHandle.WaitOne(0); }
}
}
The difficulties I face are in the EndGetExperiments method. I dont know how to access the SqlCommand to call EndExecuteReader(...).
Normally I would use the state object in the BeginExecutereader to pass on the command. But if I do so, I get the exception:
"IAsyncResult's State must be the state argument passed to your Begin call."
So I try to use the IAsyncResult to pass the SqlCommand forward to EndGetExperiments. Here, the point I don’t understand is that in EndGetExperiments the variable result is either of type IAsyncResult or of type SqlCommandAsyncResult depending on the value of CompletedSynchronously in the SqlCommandAsyncResult class.
Setting CompletedSynchronously = false makes my code fail because I cant't access the SqlCommand whereas setting CompletedSynchronously = true the code works like a charm but I have an odd feeling that something might go wrong under the hood.
I appreciate any help, guidance and example code how to make this code working and even more important in helping me to understand the problem at hand.
Thank you very much. Jan