C# abstraction and database layer and delegates

Question

Could I get some help explaining this answer below and how it works with the delegate. Its the answer from here: C# abstraction and database layer

...if you are stuck on the idea of using a DataReader, you could pass a delegate to the helper, which gets invoked inside of the using statements:

public string GetMySpecId(string dataId)
{
    return _dbHelper.ExecuteQuery(
        dr => 
           {
               if(dr.Read())
               {
                   return dr[0].ToString();
               }
               // do whatever makes sense here.
           },
        @"select ""specId"" from ""MyTable"" where ""dataId"" = :dataId",
        new SqlParameter("dataId", dataId));
    return result.Rows[0][0].ToString();
}

You could also use a lightweight tool like Dapper to simplify some of the syntax and take care of mapping to your data types. (You'd still need to deal with opening a connection and such.)

If you look at the example in the other question, how do you declare the ExecuteQuery and why are there 2 return statements here? — cdub, Dec 04 '12 at 23:24
as far as the 2 return statements go it looks like a typo, what i think he meant to put where the first return is is `var result`. I'll see if I can find you an example real quick on how to use a delegate. — EJC, Dec 04 '12 at 23:27
no worries, just trying to locate a good example, digging through some old code — EJC, Dec 04 '12 at 23:31
While I try to find a good example, this reading should get you started on delegates: http://msdn.microsoft.com/en-us/library/aa288459(v=vs.71).aspx — EJC, Dec 04 '12 at 23:33
I posted an answer, let me know if you need more help understanding. I don't know if there's anything that a delegate equates to in PHP. I'm sure there is, and I just don't know it. It's just a way to pass a method call into another method. — EJC, Dec 04 '12 at 23:58

score 0 · Accepted Answer · edited May 23 '17 at 11:48

Declaring the ExecuteQuery Method from above should look something like this:

public DataTable ExecuteQuery(Func<DataReader, DataTable> delegateMethod, string sqlQuery, SqlParameter param)
    {
        using (SqlConnection conn = new SqlConnection(this.MyConnectionString))
        {
            conn.Open();

            // Declare the parameter in the query string
            using (SqlCommand command = new SqlCommand(sqlQuery, conn))
            {
                // Now add the parameter to the parameter collection of the command specifying its type.
                command.Parameters.Add(param);

                command.Prepare();

                // Now, add a value to it and later execute the command as usual.
                command.Parameters[0].Value = dataId;


                using (SqlDataReader dr = command.ExecuteReader())
                {
                   return delegateMethod(dr);
                }
            }
        }
    }

That should be right, you may have to swap the DataReader and the DataTable in the Func, I can't remember which comes first the param types or the return type.

Here's another example of using the Func delegate, there's also the Action Delegate if you don't need a return type.

Func Delegate Reading Normal Delegate Reading

C# abstraction and database layer and delegates

1 Answers1