Getting Closure Calling SQL Stored Procedures – Keeping it DRY
(Please pardon some of the code formatting; I intend to clean it up)
When a new technology, tool, or concept emerges on my horizon, I will note it for future reference, but typically not take the time to learn it unless and until I have a real world problem to solve. Such was the case with lambda expressions and generics. I read about them but never fully appreciated their usefulness and expressiveness. When I found a real world use in my own code, I finally felt that the concepts were sinking in; I don’t think I could have gotten there with a contrived example.
For me, the opportunity to solve a problem presented itself during the coding of a Data Access Layer (DAL) for my latest project. Although the latest technologies from Microsoft – Linq to SQL and the Entity Framework – should eliminate much of the programmer’s burden with respect to developing a DAL, for one large project, I am still using the time-tested technique of passing strongly typed DataTable and DataRow objects from my DAL to my business layer. In addition, SQL stored procedures are used throughout the DAL.
Visual Studio has some terrific, easy-to-use features that make creating a DAL simple. Need a stored procedure to return a DataTable object? No problem. Drop a SqlDataAdapter onto the design surface, create a quick query – visually if you like – and you’re a few clicks away from generated code both for the stored procedure itself and for its invocation complete with parameter passing and the code necessary to fill your strongly typed DataTable. However, I felt the generated code lacked one important feature.
When my business layer makes a call into the DAL, I find it useful to signal various error conditions with custom exceptions. I typically write my DAL’s stored procedures so that I can signal various error conditions by returning distinct numeric values via the procedure’s return statement. The general pattern is to first call RAISERROR with a meaningful message for the user, and then return a status code as illustrated in this example:
When the procedure signals an error condition in this manner, the calling code will get a SqlException exception. I write my DAL code to catch SqlExceptions, retrieve the procedure’s return value through the SqlCommand object – available if you pass in a parameter with its Direction property set to ParameterDirection.ReturnValue -- and potentially use that value to re-throw a more meaningful custom exception with the original SqlException embedded within the innerException property. Here is the catch block with two helper methods:
I find that adhering to the discipline of throwing custom exceptions leads to a more robust interface between the business layer and the DAL. The code generated by Visual Studio to invoke stored procedures does not provide a way to get to the procedure’s return value and thus no opportunity to throw anything other than the original SqlException. (If I’m wrong about that, I’d love to be corrected). Consequently, I write the code to invoke the stored procedures, and while doing it for the umpteenth time, it became apparent to me that there must be a better way.
These DAL procedures come in two flavors – those that take input parameters and return a rowset used to fill either a DataTable or a DataRow object, and those that take input parameters, do some work, and return one or more output parameters. In each case, the code that invokes the procedure is very repetitive. And repetitive code has a “smell” – it violates the D-R-Y principle – Don’t Repeat Yourself, and it cries out for refactoring.
To illustrate, take the case of calling a stored procedure to return a single row of data. The DAL code should return a strongly typed DataRow object or throw a custom exception indicating what the problem is. Here is a sample of such a method that takes two input arguments and is responsible for returning a DataRow object encapsulating a single row.
But what is truly variable here? There’s a lot of boilerplate code. The using blocks for the allocation of a SqlConnection, a SqlCommand, and a SqlDataAdapter are common to all these type of methods. And so is the exception block handling. The main variability in the code is for setting the stored procedure’s parameter values. And even there, by convention, I always set the @RETURN_VALUE parameter. The other piece of variability is the return type – in this case a strongly typed DataRow descendent type – JobAssignmentRow. I needed to find a way to factor out the variant from the invariant code; and for this, two concepts worked perfectly – generics and lambda expressions.
Generics allow me to generalize the return type. In the use case in question, the code uses a SqlDataAdapter to fill a DataTable object and return its first row – a DataRow object. So it was clear that I had to specify two type parameters. My first attempt at refactoring looked like this.
I created a helper method called ReturnDataObject that declares two type parameters and a regular parameter for the caller to specify the name of the strored procedure to invoke. Aside from the issue of setting parameters (discussed below), this looks pretty clean. However it didn’t compile and displayed the following four errors:
The errors are all complaints from the compiler about my usages of the type parameters – essentially assumptions about these types that I’m making but not telling the compiler about. The first line of my code:
Is calling a parameterless constructor of the TableType type parameter. The compiler wants to ensure that there is an such a constructor to call for the concrete type that will be specified by the caller of this method. Additionally, the call to the SqlDataAdapter’s fill method requires a DataTable object. We have to assue the compiler that we are giving it one by telling it to restrict usage of this method to callers that specify a compatible concrete type. Similarly, it doesn’t know that there is a valid method called Rows because, again, we haven’t indicated that the second type parameter is a DataTable descendent. All of these problems are solved by using c#’s class restrictor feature specified in the a where clause. The declaration of this method now looked like this:
This indicates to the compiler that the RowType type must descend from DataRow and the TableType type must descend from DataTable. The new() clause ensures that the TableType class has an accessible parameterless--or default-- constructor. It now compiled cleanly.
But what about the last piece of the puzzle? How was the caller going to specify the input parameters unique to the invoked procedure? And in the case of a procedure that sets output parameters, how was the caller going to get at them?
It was apparent that I had to pass in a delegate type for a method that takes a SqlCommand object as a parameter. In my new ReturnDataObject implementation, I would call back through the delegate to allow the caller to set the input parameters appropriately. A second delegate could be used to call back when output parameters might be accessed as would be the case after a call to SqlCommand.ExecuteNonQuery (although that technique is not used in the ReturnDataObject method).
The delegate type I used looked like this:
and my ReturnDataObject method now took its final form:
Since I always create a parameter to retrieve the procedure’s return value, I put this into the ReturnDataObject method and relieved that burden from all the method’s callers. The CmdAccessProc delegate – parameter FillParamsMethod -- is used after the SqlCommand object is constructed and before it is used to construct the SqlDataAdpater that is used to fill the result table. This method now looks totally generic.
What would the code look like to call this method? Although you could create a delegate method and pass it into this method, I found that using the new c# lambda construct to be the most natural fit.
Lambdas provide a concise way to define and pass an anonymous delegate into a method. I see three advantages to using them:
1. You can define the method consisely where the delegate would normally be passed as an argument.
2. The code is smaller and tighter because the compiler makes inferences about the lambda expression’s parameter types.
3. They provide the powerful closure property (more on that below).
Here is the code to invoke a stored procedure that takes two input parameters and returns a DataRow descendent type – JobAssignmentRow.
Note that the two concrete class types are specified in the angle brackets in the call to ReturnDataObject. The first argument to the method is the string procedure name, and the second argument is the delegate – defined inline using the new lambda expression syntax. The compiler treats the argument(s) to the left of the => as inputs to the method. The return type is inferred from the method’s implementation which appears to the right of the =>. The compiler is smart enough to infer that Cmd is the input argument to the delegate method and is of type SqlCommand. It does this by examing the arguments types for the ReturnDataObject method and noting that the second argument is a CmdAccessProc delegate, and that a method with that signature takes a SqlCommand argument as its first (and only) parameter. So the compiler creates this anonymous delegate, and when it is called, assigns the passed SqlCommand object to the Cmd variable.
As you can see in the example, the purpose of the lambda’s body, which appears to the right of the =>, is used to set the input parameters – in this case for a stored procedure called “GetJobAssignment”. Something subtle but very powerful is going on here. When setting the parameters of the SqlCommand object, the anonymous delegate is referencing variables EngineId and OfficeAbbr – both input variables to the GetJobAssignment method. These variables have local scope – that is, they are only accessible within the scope of the GetJobAssignment method. Yet they are being referenced within the anonymous delegate method. This is possible because lambda expressions have the property of closure. This property allows the scope of the creating method to be passed through to the lambda expression’s implementation. Any local variables in the outer method become accessible within the body of the lambda expression.
As a final example, consider the second flavor of stored procedures – those that take input parameters and set one or more output parameters. To handle this case, I use this method:
As I’m not returning a DataTable or DataRow objects, there is no need for generics. But the method now accepts two delegate methods when called – one to set the input parameters and one to optionally access output parameters. The first delegate, FillParamsMethod, serves the same purpose as it did in the ReturnDataObject method. It is called prior to invoking the stored procedure with the SqlCommand’s ExecuteNonQuery method. The second delegate is optional. If non-null, it is used to give the caller an opportunity to access the procedure’s output parameters. This is illustrated in this sample call to InvokeNonTabularStoredProcedure:
Not counting the @RETURN_VALUE parameter automatically supplied by InvokeNonTabularStoredProcedure, seven input parameters are created and added to the SqlCommand object. The eighth is an output parameter that must be referenced after the call to ExecuteNonQuery. Once again, you can see the power of the closure property. Notice in the body of the second delegate, the value of the output parameter is assigned to a variable called PushByTagRequest_ID. That variable is in the scope of the SubmitDocPushByTag method – but it is available to be set within the body of the lambda expression. In this particular instance, the method simply returns that value as the return value of the method, but you can of course use the technique to do more elaborate things – perhaps constructing some object with the values from the procedure’s output parameters and returning that.
I have used generics before having grown to love and rely upon the System.Collections.Generics namespace. But it wasn’t until I found a use for them in my own code that I came to appreciate their power. Similarly, lambda expressions an their esoteric closure property were simply interesting concepts in the many Linq examples I had seen. Again, finding a place for them in my own code was the key to gaining an appreciation of their expressiveness. So be on the lookout for opportunities to try out these and other powerful constructs in your own code. You’ll be a better programmer for it.
When a new technology, tool, or concept emerges on my horizon, I will note it for future reference, but typically not take the time to learn it unless and until I have a real world problem to solve. Such was the case with lambda expressions and generics. I read about them but never fully appreciated their usefulness and expressiveness. When I found a real world use in my own code, I finally felt that the concepts were sinking in; I don’t think I could have gotten there with a contrived example.
For me, the opportunity to solve a problem presented itself during the coding of a Data Access Layer (DAL) for my latest project. Although the latest technologies from Microsoft – Linq to SQL and the Entity Framework – should eliminate much of the programmer’s burden with respect to developing a DAL, for one large project, I am still using the time-tested technique of passing strongly typed DataTable and DataRow objects from my DAL to my business layer. In addition, SQL stored procedures are used throughout the DAL.
Visual Studio has some terrific, easy-to-use features that make creating a DAL simple. Need a stored procedure to return a DataTable object? No problem. Drop a SqlDataAdapter onto the design surface, create a quick query – visually if you like – and you’re a few clicks away from generated code both for the stored procedure itself and for its invocation complete with parameter passing and the code necessary to fill your strongly typed DataTable. However, I felt the generated code lacked one important feature.
When my business layer makes a call into the DAL, I find it useful to signal various error conditions with custom exceptions. I typically write my DAL’s stored procedures so that I can signal various error conditions by returning distinct numeric values via the procedure’s return statement. The general pattern is to first call RAISERROR with a meaningful message for the user, and then return a status code as illustrated in this example:
if (not exists(select * from dbo.Repositories where Repository_ID = @Repository_ID))
begin
raiserror('Repository %d does not exist.', 16, 1, @Repository_ID)
return 1001 --Special return value – NO_SUCH_REPOSITORY
end
When the procedure signals an error condition in this manner, the calling code will get a SqlException exception. I write my DAL code to catch SqlExceptions, retrieve the procedure’s return value through the SqlCommand object – available if you pass in a parameter with its Direction property set to ParameterDirection.ReturnValue -- and potentially use that value to re-throw a more meaningful custom exception with the original SqlException embedded within the innerException property. Here is the catch block with two helper methods:
catch (SqlException E) {
Exception TransformedException;
if ((TransformedException = TransformSqlException(E, Cmd)) != null)
throw TransformedException;
else
throw;
}
//Get the procedure’s return value and if it is one of our custom values, return a
//more expressive exception type
private static Exception TransformSqlException(SqlException E, SqlCommand Cmd) {
Exception TransformedException = null;
if (HasValidReturnValue(Cmd)) {
int ReturnValue = (int)Cmd.Parameters["@RETURN_VALUE"].Value;
switch (ReturnValue) {
case (int)StoredProcCodes.NO_SUCH_REPOSITORY:
TransformedException = new NoSuchRepositoryException(E.Message, E);
break;
case (int)StoredProcCodes.NO_JOBS_ELIGIBLE:
TransformedException = new NoJobsEligibleException(E.Message, E);
break;
case (int)StoredProcCodes.NO_SUCH_REQUEST:
TransformedException = new NoSuchRequestException(E.Message, E);
break;
}
}
return TransformedException;
}
//See if a return value parameter is available. By convention, I always
//name mine @RETURN_VALUE
public static bool HasValidReturnValue(SqlCommand Cmd) {
SqlParameter Param;
Param = Cmd.Parameters["@RETURN_VALUE"];
return ((Param != null) && (Param.Value != null));
}
I find that adhering to the discipline of throwing custom exceptions leads to a more robust interface between the business layer and the DAL. The code generated by Visual Studio to invoke stored procedures does not provide a way to get to the procedure’s return value and thus no opportunity to throw anything other than the original SqlException. (If I’m wrong about that, I’d love to be corrected). Consequently, I write the code to invoke the stored procedures, and while doing it for the umpteenth time, it became apparent to me that there must be a better way.
These DAL procedures come in two flavors – those that take input parameters and return a rowset used to fill either a DataTable or a DataRow object, and those that take input parameters, do some work, and return one or more output parameters. In each case, the code that invokes the procedure is very repetitive. And repetitive code has a “smell” – it violates the D-R-Y principle – Don’t Repeat Yourself, and it cries out for refactoring.
To illustrate, take the case of calling a stored procedure to return a single row of data. The DAL code should return a strongly typed DataRow object or throw a custom exception indicating what the problem is. Here is a sample of such a method that takes two input arguments and is responsible for returning a DataRow object encapsulating a single row.
private static JobAssignmentRow GetJobAssignment(int EngineId, string OfficeAbbr) {
var ResultTable = new JobAssignmentDataTable ();
using (SqlConnection Connection = GetConnection()) {
using (SqlCommand Cmd = new SqlCommand(“dbo.GetJobAssignment”, Connection)) {
Connection.Open();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
Cmd.Parameters.Add("@EngineId", SqlDbType.Int).Value = EngineId;
Cmd.Parameters.Add("@OfficeAbbr", SqlDbType.Varchar, 3).Value = OfficeAbbr;
try {
using (SqlDataAdapter Adapter = new SqlDataAdapter(Cmd)) {
Adapter.Fill(ResultTable);
}
return (JobAssignmentRow)ResultTable.Rows[0];
}
catch (SqlException E) {
Exception TransformedException;
if ((TransformedException = TransformSqlException(E, Cmd)) != null)
throw TransformedException;
else
throw;
}
}
}
}
But what is truly variable here? There’s a lot of boilerplate code. The using blocks for the allocation of a SqlConnection, a SqlCommand, and a SqlDataAdapter are common to all these type of methods. And so is the exception block handling. The main variability in the code is for setting the stored procedure’s parameter values. And even there, by convention, I always set the @RETURN_VALUE parameter. The other piece of variability is the return type – in this case a strongly typed DataRow descendent type – JobAssignmentRow. I needed to find a way to factor out the variant from the invariant code; and for this, two concepts worked perfectly – generics and lambda expressions.
Generics allow me to generalize the return type. In the use case in question, the code uses a SqlDataAdapter to fill a DataTable object and return its first row – a DataRow object. So it was clear that I had to specify two type parameters. My first attempt at refactoring looked like this.
private static RowType ReturnDataObject<RowType,TableType>(string StoredProcedure) {
var ResultTable = new TableType();
using (SqlConnection Connection = GetConnection()) {
using (SqlCommand Cmd = new SqlCommand(StoredProcedure, Connection)) {
Connection.Open();
Cmd.CommandType = CommandType.StoredProcedure;
//PARAMETERS need to be set here
try {
using (SqlDataAdapter Adapter = new SqlDataAdapter(Cmd)) {
Adapter.Fill(ResultTable);
}
return (RowType)ResultTable.Rows[0];
}
catch (SqlException E) {
Exception TransformedException;
if ((TransformedException = TransformSqlException(E, Cmd)) != null)
throw TransformedException;
else
throw;
}
}
}
}
I created a helper method called ReturnDataObject that declares two type parameters and a regular parameter for the caller to specify the name of the strored procedure to invoke. Aside from the issue of setting parameters (discussed below), this looks pretty clean. However it didn’t compile and displayed the following four errors:
error CS0304: Cannot create an instance of the variable type 'TableType' because it does not have the new() constraint
error CS1502: The best overloaded method match for 'System.Data.Common.DbDataAdapter.Fill(System.Data.DataTable)' has some invalid arguments
C:\TFSSource\Portal\Version 1.0\DocumentsDAL\DocProcs.cs(218,42):
error CS1503: Argument '1': cannot convert from 'TableType' to 'System.Data.DataTable'
C:\TFSSource\Portal\Version 1.0\DocumentsDAL\DocProcs.cs(220,53):
error CS1061: 'TableType' does not contain a definition for 'Rows' and no extension method 'Rows' accepting a first argument of type 'TableType' could be
The errors are all complaints from the compiler about my usages of the type parameters – essentially assumptions about these types that I’m making but not telling the compiler about. The first line of my code:
var ResultTable = new TableType();
Is calling a parameterless constructor of the TableType type parameter. The compiler wants to ensure that there is an such a constructor to call for the concrete type that will be specified by the caller of this method. Additionally, the call to the SqlDataAdapter’s fill method requires a DataTable object. We have to assue the compiler that we are giving it one by telling it to restrict usage of this method to callers that specify a compatible concrete type. Similarly, it doesn’t know that there is a valid method called Rows because, again, we haven’t indicated that the second type parameter is a DataTable descendent. All of these problems are solved by using c#’s class restrictor feature specified in the a where clause. The declaration of this method now looked like this:
private static RowType ReturnDataObject<RowType,TableType>(string StoredProcedure)
where RowType : DataRow
where TableType : DataTable, new() {
This indicates to the compiler that the RowType type must descend from DataRow and the TableType type must descend from DataTable. The new() clause ensures that the TableType class has an accessible parameterless--or default-- constructor. It now compiled cleanly.
But what about the last piece of the puzzle? How was the caller going to specify the input parameters unique to the invoked procedure? And in the case of a procedure that sets output parameters, how was the caller going to get at them?
It was apparent that I had to pass in a delegate type for a method that takes a SqlCommand object as a parameter. In my new ReturnDataObject implementation, I would call back through the delegate to allow the caller to set the input parameters appropriately. A second delegate could be used to call back when output parameters might be accessed as would be the case after a call to SqlCommand.ExecuteNonQuery (although that technique is not used in the ReturnDataObject method).
The delegate type I used looked like this:
delegate void CmdAccessProc(SqlCommand Cmd);
and my ReturnDataObject method now took its final form:
private static RowType ReturnDataObject<RowType,TableType>(string StoredProcedure, CmdAccessProc FillParamsMethod)
where RowType : DataRow
where TableType : DataTable, new() {
var ResultTable = new TableType();
using (SqlConnection Connection = GetConnection()) {
using (SqlCommand Cmd = new SqlCommand(StoredProcedure, Connection)) {
Connection.Open();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
FillParamsMethod(Cmd);
try {
using (SqlDataAdapter Adapter = new SqlDataAdapter(Cmd)) {
Adapter.Fill(ResultTable);
}
return (RowType)ResultTable.Rows[0];
}
catch (SqlException E) {
Exception TransformedException;
if ((TransformedException = TransformSqlException(E, Cmd)) != null)
throw TransformedException;
else
throw;
}
}
}
}
Since I always create a parameter to retrieve the procedure’s return value, I put this into the ReturnDataObject method and relieved that burden from all the method’s callers. The CmdAccessProc delegate – parameter FillParamsMethod -- is used after the SqlCommand object is constructed and before it is used to construct the SqlDataAdpater that is used to fill the result table. This method now looks totally generic.
What would the code look like to call this method? Although you could create a delegate method and pass it into this method, I found that using the new c# lambda construct to be the most natural fit.
Lambdas provide a concise way to define and pass an anonymous delegate into a method. I see three advantages to using them:
1. You can define the method consisely where the delegate would normally be passed as an argument.
2. The code is smaller and tighter because the compiler makes inferences about the lambda expression’s parameter types.
3. They provide the powerful closure property (more on that below).
Here is the code to invoke a stored procedure that takes two input parameters and returns a DataRow descendent type – JobAssignmentRow.
public static JobAssignmentRow GetJobAssignment(int EngineId, string OfficeAbbr) {
return ReturnDataObject<JobAssignmentRow,JobAssignmentDataTable>
("dbo.GetJobAssignment",
Cmd => {
Cmd.Parameters.Add("@EngineId", SqlDbType.Int).Value = EngineId;
Cmd.Parameters.Add("@OfficeAbbr", SqlDbType.Varchar, 3).Value = OfficeAbbr;
}
);
}
Note that the two concrete class types are specified in the angle brackets in the call to ReturnDataObject. The first argument to the method is the string procedure name, and the second argument is the delegate – defined inline using the new lambda expression syntax. The compiler treats the argument(s) to the left of the => as inputs to the method. The return type is inferred from the method’s implementation which appears to the right of the =>. The compiler is smart enough to infer that Cmd is the input argument to the delegate method and is of type SqlCommand. It does this by examing the arguments types for the ReturnDataObject method and noting that the second argument is a CmdAccessProc delegate, and that a method with that signature takes a SqlCommand argument as its first (and only) parameter. So the compiler creates this anonymous delegate, and when it is called, assigns the passed SqlCommand object to the Cmd variable.
As you can see in the example, the purpose of the lambda’s body, which appears to the right of the =>, is used to set the input parameters – in this case for a stored procedure called “GetJobAssignment”. Something subtle but very powerful is going on here. When setting the parameters of the SqlCommand object, the anonymous delegate is referencing variables EngineId and OfficeAbbr – both input variables to the GetJobAssignment method. These variables have local scope – that is, they are only accessible within the scope of the GetJobAssignment method. Yet they are being referenced within the anonymous delegate method. This is possible because lambda expressions have the property of closure. This property allows the scope of the creating method to be passed through to the lambda expression’s implementation. Any local variables in the outer method become accessible within the body of the lambda expression.
As a final example, consider the second flavor of stored procedures – those that take input parameters and set one or more output parameters. To handle this case, I use this method:
private static void InvokeNonTabularStoredProcedure(string StoredProcedure, CmdAccessProc FillParamsMethod,
CmdAccessProc AccessOutputParamsMethod) {
using (SqlConnection Connection = GetConnection()) {
using (SqlCommand Cmd = new SqlCommand(StoredProcedure, Connection)) {
Connection.Open();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
FillParamsMethod(Cmd);
try {
Cmd.ExecuteNonQuery();
if (AccessOutputParamsMethod != null)
AccessOutputParamsMethod(Cmd);
}
catch (SqlException E) {
Exception TransformedException;
if ((TransformedException = TransformSqlException(E, Cmd)) != null)
throw TransformedException;
else
throw;
}
}
}
}
As I’m not returning a DataTable or DataRow objects, there is no need for generics. But the method now accepts two delegate methods when called – one to set the input parameters and one to optionally access output parameters. The first delegate, FillParamsMethod, serves the same purpose as it did in the ReturnDataObject method. It is called prior to invoking the stored procedure with the SqlCommand’s ExecuteNonQuery method. The second delegate is optional. If non-null, it is used to give the caller an opportunity to access the procedure’s output parameters. This is illustrated in this sample call to InvokeNonTabularStoredProcedure:
//Submit a document and return the assigned request id.
public static int SubmitDocPushByTag(string Requestor, int Case_ID, int DBId,
string TagName, int Category_ID, int ExpectedDocCount, int Priority) {
int PushByTagRequest_ID = 0;
InvokeNonTabularStoredProcedure ("dbo.usp_Documents_SubmitDocPushByTag",
Cmd => {
Cmd.Parameters.Add("@Requestor", SqlDbType.VarChar, 255).Value = Requestor;
Cmd.Parameters.Add("@Case_ID", SqlDbType.Int).Value = Case_ID;
Cmd.Parameters.Add("@DBId", SqlDbType.Int).Value = DBId;
Cmd.Parameters.Add("@TagName", SqlDbType.VarChar, 255).Value = TagName;
Cmd.Parameters.Add("@Category_ID", SqlDbType.Int).Value = Category_ID;
Cmd.Parameters.Add("@ExpectedDocCount", SqlDbType.Int).Value = ExpectedDocCount;
Cmd.Parameters.Add("@Priority", SqlDbType.Int).Value = Priority;
Cmd.Parameters.Add("@PushByTagRequest_ID", SqlDbType.Int).Direction = ParameterDirection.Output;
},
Cmd => {
PushByTagRequest_ID = (int)Cmd.Parameters["@PushByTagRequest_ID"].Value;
}
);
return PushByTagRequest_ID;
}
Not counting the @RETURN_VALUE parameter automatically supplied by InvokeNonTabularStoredProcedure, seven input parameters are created and added to the SqlCommand object. The eighth is an output parameter that must be referenced after the call to ExecuteNonQuery. Once again, you can see the power of the closure property. Notice in the body of the second delegate, the value of the output parameter is assigned to a variable called PushByTagRequest_ID. That variable is in the scope of the SubmitDocPushByTag method – but it is available to be set within the body of the lambda expression. In this particular instance, the method simply returns that value as the return value of the method, but you can of course use the technique to do more elaborate things – perhaps constructing some object with the values from the procedure’s output parameters and returning that.
I have used generics before having grown to love and rely upon the System.Collections.Generics namespace. But it wasn’t until I found a use for them in my own code that I came to appreciate their power. Similarly, lambda expressions an their esoteric closure property were simply interesting concepts in the many Linq examples I had seen. Again, finding a place for them in my own code was the key to gaining an appreciation of their expressiveness. So be on the lookout for opportunities to try out these and other powerful constructs in your own code. You’ll be a better programmer for it.
posted by Howard Pinsley at 5:27 PM
0 Comments:
Post a Comment
<< Home