.gif) f you use stored procedures a lot, I'm sure you know how painful it is to write the same mundane code to describe each parameter's name, type, and size every time you set up a command object to call it. If you ever change the interface of the stored procedure, chances are good that you'll have to go back and review your data service layer and update the stored procedure calls accordingly. Everyone goes to great lengths to make this less painful by centralizing the stored procedure call in the data service layer, writing wrappers around them, and even implementing generators. I am going to demonstrate how you can use attributes in Microsoft® .NET to define a stored procedure just as if you were writing a function in your favorite language—be it C#, Visual Basic® .NET, or any language targeting the common language runtime (CLR) that supports decorating declarations with attributes.
In classic ADO, calling even a trivial stored procedure that takes a single parameter means creating a command object, setting up the parameters collection, and then executing it. Consider a stored procedure from the IBuySpy Portal sample application (see https://www.ibuyspy.com), shown in Figure 1.
It doesn't really matter for the purpose of this discussion if you don't know the IBuySpy portal sample and its database design. What's important here is the interface of the stored procedure. Using ADO and Visual Basic for Applications (VBA), a wrapper function to call this stored procedure might look like Figure 2.
Apart from keeping the stored procedure definition centralized in a project, an additional benefit of writing wrapper functions like this is to make the stored procedure appear as a casual VBA function to the caller. So calling the stored procedure becomes as simple as the following:
Dim Connection As ADODB.Connection
Set Connection = OpenConnection()
Dim Recordset As ADODB.Recordset
Set Recordset = GetAnnouncements(Connection, 13)
Connection.Close
It also adds type safety because while the Value property of a Parameter object is defined as a Variant, the ModuleID parameter of the GetAnnouncements function is defined as an integer.
When you write code like the GetAnnouncements function to call even the simplest stored procedure, all kinds of "anti-productivity" alarm bells should ring in your head. You start to write some helper functions to return a disconnected recordset object given any Command object. This makes your stored procedure wrapper functions smaller, though setting up the parameters manually and keeping them synchronized with the definitions in the database remains a tedious and error-prone task. Your next instinct may be to write a generator that automatically produces the stored procedure wrapper functions in Visual Basic using the SQLDMO object library. Many developers even maintain XML definitions of stored procs, then use an XSLT transformation as the code generator.
When you move your code to .NET, the situation remains largely the same. Instead of using an ADODB.Connection object, you use the SqlConnection object from the System.Data.SqlClient namespace (assuming you are working with SQL Server™). Instead of the Command object from ADO, you instantiate a SqlCommand. Instead of a Parameter, you create a SqlParameter. The process remains mechanically the same. In fact, Figure 3 shows you the C# version of IBuySpy's wrapper of GetAnnouncements. Notice how in the greater scheme of things it is doing largely the same stuff as the ADO code in VBA.
The good news is that porting your data access code from a classic client/server or Windows® DNA solution is going to be fairly straightforward. The bad news is that you still have to maintain stored procedure definitions in code and write lengthy wrapper functions in C# or Visual Basic .NET.
Time to roll up your sleeves and have that generator produce C# and Visual Basic .NET code instead of plain old VBA? That would be one way, but a more modern approach would be to take advantage of some of the features and innovations in the CLR to automatically derive stored procedure calls from function signatures. I am going to demonstrate how you can do that and present a library that you can use as a starting point.
A compiler targeting the CLR emits rich metadata describing all aspects of a type and its members. The metadata usually ends up in an assembly (statically on disk or even dynamically in memory) and can be queried at run time via reflection services found under the System.Reflection namespace. Figure 4 shows how to do this in C#. The PortalDatabase class contains the C# equivalent of the GetAnnouncements function you saw earlier in VBA. Since I am only interested in demonstrating the reflection services at this point, I didn't put any real code in the function itself. The output of the sample is the function's name, return type, parameter names, and the type of each parameter, as shown here:
PortalDatabase.GetAnnouncements [System.Data.DataSet]
System.Data.SqlClient.SqlConnection connection
System.Int32 moduleId
The Main function begins by getting all the methods of the PortalDatabase class. The BindingFlags mask allows you to specify which methods will get included in the array returned from GetMethods. BindingFlags.Static and BindingFlags.Public are required because GetAnnouncements is declared as public and static; otherwise, the returned array would be empty. The BindingFlags.DeclaredOnly flag ensures that only methods from the requested type will be included in the returned array. Remember that PortalDatabase is a class and therefore implicitly inherits from System.Object. If you don't want methods of your base classes to be included, then BindingFlags.DeclaredOnly screens them out. The rest of the code in Main then walks through each MethodInfo, enumerating the parameters and displaying the information of interest to us.
Given this simple example, you can imagine how you could easily walk the parameters of a given function and create a SqlCommand object with respectively mapped SqlParameter objects. All you have to do is define functions in C# or Visual Basic .NET that are representations of your stored procedures. The rest could be handled by a single utility function that dynamically creates and configures SqlCommand objects at run time to make the call on the database. Such a function could look like Figure 5. Given a connection object, a method's metadata, and the actual values to be used in the stored procedure invocation, GenerateCommand generates a fully populated and ready to execute SqlCommand object. Careful readers probably noticed that I didn't use the data type of the method's parameter to infer the SqlDbType and set the Type of the SqlParameter accordingly. This is because the SqlParameter object already provides this feature when I set the Value property. It infers the SqlDbType from the type of the value. In fact, if you do set the Type property explicitly, the SqlParameter object internally switches off its "guessing" mechanism. Figure 6 shows which SqlDbType maps to which type in the .NET Framework (System), C#, or Visual Basic .NET.
Next, you need to place a call to GenerateCommand from inside PortalDatabase.GetAnnouncements and then use the generated command. To do this, the GetAnnouncements function must pass a MethodInfo object supplying its metadata, obtained using Type.GetMethod. As shown in Figure 7, one of the Type.GetMethod overloads does the work of finding a suitable match given a method's name and parameter types. In reality, it forwards the call to the protected abstract method Type.GetMethodImpl, supplying missing pieces with default values. So, in effect, my simple Type.GetMethod call internally gets translated into the following call to Type.GetMethodImpl:
Type.GetMethodImpl("GetAnnouncements",
BindingFlags.Public | BindingFlags.Instance |
BindingFlags.Static,
null, CallingConventions.Any,
new Type[] { typeof(SqlConnection), typeof(int) }, null);
It's interesting to note that the Type class and its method GetMethodImpl are abstract. Who eventually provides the concrete implementation for them? The answer is the System.RuntimeType class that inherits from System.Type. RuntimeType is a private class of the MSCORLIB.DLL assembly, so although you can't access it directly, the CLR dispenses System.RuntimeType objects whenever you request type information via the typeof operation in C# or GetType in Visual Basic .NET. So Type.GetMethod ends up calling RuntimeType's implementation of GetMethodImpl, which in turn uses the SelectMethod function of the Binder object to do the final matching and selection. Since you never passed a custom Binder implementation in this call chain, RuntimeType.GetMethodImpl obtains the default binder implementation using the static Type.DefaultBinder property. The binder's implementation of Binder.SelectMethod is subsequently responsible for finding a method that most closely matches a given set of dense criteria.
Although the code in Figure 7 does the job, it is still far from ideal, because now you have to keep the C# function declaration and the parameters to Type.GetMethod in sync. If you change the function name or a parameter type, you need to update the call to Type.GetMethod. This problem of getting the method's metadata can ultimately be solved by simply walking up the stack and just grabbing the frame belonging to the method in question. As with all things in the Framework Class Library (FCL), there's a very handy function to do this, but it's hidden and you have to guess where it is. That function is MethodBase.GetCurrentMethod from the System.Reflection namespace. It takes no arguments and returns a MethodBase implementation (currently either a MethodInfo for a regular method or a ConstructorInfo for a type constructor) representing the function calling it. To send it to GenerateCommand, you will obviously have to cast the return value into MethodInfo. The final call looks like this:
public static DataSet GetAnnouncements(SqlConnection connection,
int moduleId)
{
SqlCommand command = SqlCommandGenerator.GenerateCommand
(connection, (MethodInfo) MethodBase.GetCurrentMethod(),
new object[] { moduleId });
// rest of the code omitted for brevity ...
}
As an aside, I've often found MethodBase.GetCurrentMethod extremely useful in debugging, call-tracing, and otherwise writing plain old test code. How many times have you written a simple test function to try out some idea that prints something along the lines of "Inside FooBar"? Well, MethodBase.GetCurrentMethod().ToString will probably be your new best friend. The string representation of a MethodBase-derived class includes the entire method signature, including the return value. This turns out to be a real lifesaver for the carpal tunnel-prone, especially when you are using method overloading and don't want to bother dumping output like "Inside Foo1" and "Inside Foo2" out of laziness.
Later, I'll demonstrate yet another way of getting at the metadata of a method without even resorting to Type.GetMethod or MethodBase.GetCurrentMethod.
One more issue. What if your stored procedure and its parameters are not named the way you want them named in your code? For instance, many projects adopted a naming scheme where the convention is to prefix stored procedure names with "sp." Moreover, there may be times when you want to send parameters to your C# function as a payload that should not be forwarded to the stored procedure. Consider the GetAnnouncements example again, where the first parameter is a SqlConnection object. If you don't take care to exclude it from the SqlCommand object's parameter list, you will end up with an exception somewhere down the road.
Finally, you could come across cases when a parameter type in your C# function needs to be slightly different from the corresponding parameter in the stored procedure. So it would be nice if there were some way of accommodating these small differences and consequently gaining flexibility around how you want to express relationships between the database and your application. Fundamentally, you want to associate additional metadata with the function and its parameters.
The Sample Library
The library available in the code download (at the link at the top of this article) solves all the aforementioned issues. You would probably want to use it as a starting point and then adapt or enhance it to fit your needs more specifically. For illustration purposes, the library assumes that you are using SQL Server, so it works mainly with objects from the System.Data.SqlClient namespace and the data types enumerated in System.Data.SqlDbType. If you are using another managed provider, then you'll certainly have to adapt my sample library.
There are four basic classes in the library: SqlCommandMethodAttribute, SqlParameterAttribute, NonCommandParameterAttribute, and SqlCommandGenerator. As you can guess from the names, the first three are custom attributes. SqlCommandGenerator is a utility class that contains static functions for generating a SqlCommand object based on a method's metadata. It is a more complex implementation than the one shown in Figure 5 because it accounts for customization via attributes.
SqlCommandMethodAttribute
The SqlCommandMethodAttribute attribute serves three purposes. First, it tags a C# or Visual Basic .NET function as targeting a database command. To be on the safe side, I've made the presence of this attribute mandatory such that SqlCommandGenerator will stall with an assertion failure if it is accidentally supplied a method without this custom attribute. (You will probably want to replace the assertions with custom exceptions.) Second, because SqlCommandMethodAttribute does not have a default constructor, it forces you to at least specify the type of the command being represented by the function. The two supported values from the System.Data.CommandType enumeration are CommandType.StoredProcedure for a stored procedure or CommandType.Text for a parameterized SQL. Third and finally, SqlCommandMethodAttribute has a CommandText property that lets you specify the name of the target stored procedure or a SQL statement. In the former case, you only need to supply the stored procedure name if it happens to be different from the wrapper function in your code. For example, if the IBuySpy Portal stored procedure were called spGetAnnouncements and you wanted to name your function simply GetAnnouncements, then you would apply this attribute as follows:
[ SqlCommandMethod
(CommandType.StoredProcedure, "spGetAnnouncements") ]
public static DataSet GetAnnouncements
(SqlConnection connection, int moduleId)
{
•••
}
If the names happen to be identical, then you would apply the attribute like this:
[ SqlCommandMethod(CommandType.StoredProcedure) ]
public static DataSet GetAnnouncements
(SqlConnection connection, int moduleId)
{
•••
}
If your function represents a parameterized SQL statement, then specify CommandType.Text as the first parameter and the SQL as the second parameter to the attribute constructor. Here's how you would get the announcements from the portal database using a parameterized SQL query instead of the stored procedure:
[ SqlCommandMethod(CommandType.Text, "SELECT * FROM Announcements
WHERE ModuleID = @ModuleID AND ExpireDate > GetDate()") ]
public static DataSet GetAnnouncements(SqlConnection connection,
int moduleId)
{
•••
}
The actual implementation of SqlCommandMethodAttribute is so straightforward that the only aspect of it worth mentioning is the AttributeUsage attribute on the class itself. By default it is set to AttributeTargets.Method to prevent it from being used on anything except a method. Compilers will issue an error if you attempt to apply a SqlCommandMethodAttribute instance on, say, an event, a property, or even a constructor.
NonCommandParameterAttribute
There is absolutely no code for the NonCommandAttribute class; apart from inheriting from System.Attribute, its definition is empty. This is typical of attributes that merely act as tags. Just about the only member they contain is the default constructor issued by the compiler in the absence of one. NonCommandParameterAttribute is useful when you want to prevent certain payload parameters in your C# or Visual Basic .NET function from being included in the generated SqlCommand:
[ SqlCommandMethod(CommandType.StoredProcedure) ]
public static DataSet GetAnnouncements(
[ NonCommandParameter ] SqlConnection connection,
int moduleId)
{
•••
}
This is how I take care of the SqlConnection object being passed as the first parameter. It is part of the function's interface, but not of the stored procedure in the database or a parameter in a parameterized SQL statement. The SqlCommandGenerator class simply skips any parameter tagged with this attribute; otherwise it is processed by default. Most examples that I've been discussing so far use static functions, but if you were to make the connection object a member of the class containing your wrapper functions, then you wouldn't even need to apply this attribute.
Like SqlCommandMethodAttribute, the NonCommandParameterAttribute class has an AttributeUsage attribute applied, except this time the usage is indicated to be suitable for parameters only. By the way, I placed this attribute in the Sample.Data namespace as opposed to the Sample.Data.Sql namespace because it is not specific to any provider. Although this requires you to include two namespaces, it is also what encourages you to reuse the attribute for similar purposes elsewhere.
SqlParameterAttribute
SqlParameterAttribute allows you to resolve any discrepancies between a function's parameter and the target command's parameter. Unlike SqlCommandMethodAttribute and SqlNonParameterAttribute, SqlParameterAttribute is not used only for tagging a parameter—even though that is possible using its default constructor. It is really only required when there are differences involved; otherwise SqlCommandGenerator tries to get as much information as possible from the metadata of a method's parameter.
SqlParameterAttribute has six properties that can be controlled individually: the Name, SqlDbType, Size, Precision, Scale, and Direction of a parameter. Its constructor is overloaded in several ways to allow you to specify the Name, SqlDbType, Size, or a combination of these depending on what's different. If you need to specify the direction, precision, or scale of a parameter, you have to manually set the Direction, Precision, and Scale properties. Figure 8 shows a few examples of how to use SqlParameterAttribute.
An important aspect of a complex attribute's design is to determine which properties seem natural to express in the constructor and which should be explicitly set through property assignment. Since SqlParameterAttribute carries quite a few properties, allowing all of them to be expressed through all combinations of overloaded constructors would have only led to confusion and code that's hard to read. This is why I chose only the most popular properties of a parameter like the name, data type, and size to be initialized by means of the constructor. When designing your own attributes, keep this thought process in mind.
The implementation of SqlParameterAttribute requires a little explanation. There are six read-only properties called IsNameDefined, IsSizeDefined, IsPrecisionDefined, IsScaleDefined, IsTypeDefined, and IsDirectionDefined. SqlParameterAttribute maintains two states for every property, either defined or undefined. All states are always initialized to undefined by default, meaning that the name, size, precision, scale, type, and direction of a command parameter are not explicitly specified and therefore must be inferred from their context. For SqlCommandGenerator, the context is the metadata of a function's parameter. The Name, Size, Precision, and Scale properties bear the undefined state as an integral part of their value type. That is, a name is undefined if the private _name field is null or is occupied by an empty string; otherwise, it is defined. This same rule applies to the size, precision, and scale properties. Since _paramType and _direction each happen to be an enum, there is no value that can be safely used to represent an undefined state. Consequently their defined and undefined states are dictated by the separate _typeDefined and _directionDefined fields, respectively.
SqlCommandGenerator
The SqlCommandGenerator class is what finally takes the metadata of a method, applies overrides through the custom attributes, and generates a ready-to-execute SqlCommand object. It has only one public method, GenerateCommand, which is just a more thorough implementation of the one seen earlier in Figure 5. As before, the second parameter of GenerateCommand identifies the function whose metadata should be used to generate the command, except this time I've made it optional. If you just send in a null (or Nothing in Visual Basic .NET), GenerateCommand will automatically discover and use the immediate caller's metadata. It starts off by using the StackTrace class from the System.Diagnostics namespace to initiate a stack trace. Then it grabs the method from the preceding stack frame by supplying an index of one to StackTrace.GetFrame (an index of zero would correspond to the GenerateCommand call itself):
if (method == null)
method = (MethodInfo) (new StackTrace().GetFrame(1).GetMethod());
Now you can query the stack frame for the caller's metadata in one full swing. There's no need to make a complicated call to Type.GetMethod and unnecessarily create another point of maintenance. Heck, you don't even need to call MethodBase.GetCurrentMethod. Could things get any simpler? The only two requirements are that the immediate caller be the database command proxy and that it must not be a constructor. There are three reasons for the latter requirement. First, it simply does not make sense and I would therefore discourage it even though it is technically possible. Second, the AttributeUsage on SqlCommandMethodAttribute disallows application of the attribute to a constructor anyway. And third, although StackTrace.GetFrame returns a MethodBase (which is the superclass of MethodInfo and abstracts the methods and constructors), GenerateCommand casts it into a MethodInfo. So in a case where a constructor attempts to call GenerateCommand, the runtime will throw an InvalidCastException at this point.
With this new caller-detection feature added to the generator, you might be tempted to always send in a null for the second parameter, but beware, this ease-of-use also comes with a significant performance price tag. According to tests on my machine, Type.GetMethod provides the best performance, with MethodBase.GetCurrentMethod being only a bit slower for very large iterations. A stack walk using StackTrace on the other hand was over 12 times slower. The difference between the first two is, for all practical purposes, negligible.
MethodBase.GetCurrentMethod still shines as the preferred technique and you should use it whenever possible instead of sending NULL. Incidentally, you would think that MethodBase.GetCurrentMethod also performs some kind of stack trace, and indeed it does, but the Framework designers seemed to have optimized it for frequent use. This makes sense when you consider the number of times that FCL itself has to consult the stack for the caller to check on permissions.
With that bit out of the way, GenerateCommand begins its core work by looking to see if the method was decorated with SqlCommandMethodAttribute. If not, an assertion failure will point out the culprit. GenerateCommand then goes on to create a SqlCommand object and initializes its Connection, CommandType, and CommandText properties. To set the command object's CommandText property, it consults the length of the attribute's CommandText property. If it is an empty string, the command object's CommandText property is set to the name of the method; otherwise the one from the attribute is used. If the string is empty, there's an additional check to make sure that the type of command is indeed a stored procedure because it doesn't make sense to use the method name for a parameterized SQL. The GenerateCommandParameters method that is called next is a private function whose sole purpose is division of labor. It is responsible for processing the method's parameters and adding corresponding SqlParameter objects to the SqlCommand object passed in. The code is thoroughly commented, so I'll only summarize the loop:
- Get the next parameter of the method.
- If it has a NonCommandParameterAttribute applied, then skip it entirely.
- If it has a SqlParameterAttribute applied, then use those defined values to set up the corresponding properties of the SqlParameter object.
- If a SqlParameterAttribute was not present, then a temporary one is created using the default constructor. All aspects of the SqlParameter are then configured from the metadata of the method's parameter.
In addition, there are two assertions that trigger when there is a mismatch in the count of parameters (excluding those marked with NonCommandParameterAttribute) declared on the method and the values sent to GenerateCommandParameters. I have found this helpful in cases where you declare a parameter on a method but forget to forward its value to the generator.
The Challenge of Output Parameters
One limitation worth noting concerns the direction of a SqlParameter object. When it is undefined in the attribute, the setting is inferred from the method parameter. In C#, a parameter can be passed by value (no keyword), by reference (ref keyword), or for output only (out keyword), so the direction is set accordingly to ParameterDirection.Input, ParameterDirection.InputOutput, or ParameterDirection.Output, respectively. Visual Basic .NET makes no distinction between the latter two, so the direction is either ParameterDirection.Input for ByVal parameters or ParameterDirection.InputOutput for ByRef parameters.
Setting the direction for output parameters takes care of getting the values back from the stored procedure or parameterized SQL where needed, but the problem is that they can't be propagated to the caller without additional work. This is because the values sent to GenerateCommand are passed by value and as a result cannot affect the caller. This is even truer for value types, which get boxed and copied since the values array is an array of objects. Hence you need to manually copy all outgoing parameters from the SqlParameter object to the caller after execution of the command.
Here's an example. Take the AddAnnouncements stored procedure from IBuySpy Portal shown in Figure 9. Like any stored procedure of its kind, it inserts a row into a table and returns the auto-assigned identity in an output parameter. Figure 10 shows how you would propagate the output parameter from the stored procedure back to the caller's out parameter in C#. So although SqlCommandGenerator takes care of setting the right direction based on by-reference parameters, it cannot automatically transmit values to them.
By the way, the very same issue arises when using reflection to invoke a method that takes parameters by reference. Figure 11 shows a Swap method being called by a proxy using late binding. After Swap has done its job, SwapProxy needs to copy values out of the parameters array back to the caller's allocated variables.
While I'm on the subject, it is interesting to see how Visual Basic .NET handles this situation, because it allows late binding (in Option Strict Off mode) through a much more natural and elegant syntax than C#. Figure 12 shows the Visual Basic .NET version of the code in Figure 11. The SwapProxy function is gone because Visual Basic .NET does all the heavy lifting involved in invoking the member via reflection. Eventually, it ends up calling Type.InvokeMember, which is just another way of doing MethodInfo.Invoke.
Although the code looks deceivingly simple, behind the scenes Visual Basic .NET is internally generating the same intermediate language (IL) code the C# version produces. Figure 13 is a dump of the Main method as seen through the ILDASM tool. For those who don't want to go through the headache of reading the IL, take my word for it: the instructions essentially copy elements from the temporary _Vb_t_array_2 back to the variables a and b.
Ultimately you could even solve the problem of output parameters completely by generating code just as Visual Basic .NET does using facilities of the System.Reflection.Emit namespace, but that is a topic for another article.
Handling NULL Values
An item that I haven't discussed yet is the handling of NULL values. If your stored procedure allows one or more parameters to be NULL, your proxy method cannot use predefined types like int in C# and integer in Visual Basic .NET. Instead, you would declare your method as accepting one of the value types from the System.Data.SqlTypes namespace. Say you have the following stored procedure in the Pubs database:
CREATE PROCEDURE [GetEmployeesByJob](@job_id SMALLINT = NULL) AS
SELECT *
FROM [employee]
WHERE [job_id] = ISNULL(@job_id, [job_id])
Since the @job_id parameter can be NULL, you would declare your C# method using a SqlInt16 rather than a short, as shown in this snippet:
[ SqlCommandMethod (CommandType.StoredProcedure)]
public static DataSet GetEmployeesByJob(
[ NonCommandParameter ] SqlConnection connection,
[ SqlParameter("job_id") ] SqlInt16 jobId)
{
SqlCommand command = SqlCommandGenerator.GenerateCommand
(connection, null, new object[] { jobId });
DataSet dataSet = new DataSet();
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);
return dataSet;
}
Here's how you would call the method with a NULL and a non-NULL value for the jobId parameter:
DataSet dataSet;
dataSet = GetEmployeesByJob(connection, new SqlInt16(5));
dataSet = GetEmployeesByJob(connection, 5);
// implicit cast to SqlInt16
dataSet = GetEmployeesByJob(connection, SqlInt16.Null);
The second invocation of GetEmployeesByJob is only possible in C# since Visual Basic .NET does not directly support conversion operators. You can still call SqlInt16.op_Implicit yourself even if IntelliSense® hides it from you in Visual Studio®, but the call looks so murky that you might as well use the constructor version. Here's the Visual Basic .NET version of the previous code:
Dim dataSet As DataSet
dataSet = GetEmployeesByJob(connection, New SqlInt16(5))
dataSet = GetEmployeesByJob(connection, SqlInt16.op_Implicit(5))
dataSet = GetEmployeesByJob(connection, SqlInt16.Null)
User-defined Parameter Data Types Via Inheritance
Of all the custom attributes in the same library, only the SqlParameterAttribute class is defined without the sealed keyword in C# (NonInheritable in Visual Basic .NET). This is by design, even though there are no public or protected members that a subclass could override. More often than not, attributes are designed to store only additional metadata, and don't exhibit any behavior beyond what is defined by Attribute, so you should always aim to seal them. Besides, sealing also makes it faster for the runtime to look up attributes when queried at run time. In the case of SqlParameterAttribute, leaving the attribute open for specialization through inheritance enables you to create your own user-defined types that reduce maintenance and errors. This is similar to the result of employing user-defined types in SQL Server.
Suppose you store e-mail addresses in several tables within a database. If an e-mail address is defined as NVARCHAR(100), then creating a user-defined type for it in SQL Server can ensure a consistent definition across all database objects. If you need to alter the length of e-mail addresses at some stage in the future then having a user-defined data type for it usually means a single point of change. Likewise, you can subclass SqlParameterAttribute to centralize the definition of parameters that are used across many commands. Here's how to define a new attribute based on SqlParameterAttribute that wraps the SQL Server system-supplied user-defined type sysname. Note that SysNameParameterAttribute itself is sealed:
sealed class SysNameParameterAttribute : SqlParameterAttribute
{
public SysNameParameterAttribute() : base(SqlDbType.NVarChar, 128) {}
public SysNameParameterAttribute(string name) :
base(name, SqlDbType.NVarChar, 128) {}
}
Bonus Items
So far you've seen how attributes can be used to automatically generate commands at run time, but you can also use them to write reporting and maintenance tools to assist during development. To give you an idea of how easy it would be to write a simple reporting tool, consider the program that's shown in Figure 14. Given an assembly as a command-line argument, it traverses all exported types and their methods to report those that are decorated with SqlCommandMethodAttribute, where the CommandType property is set to CommandType.StoredProcedure. So in a few lines of code, you can discover all stored procedure proxies exported by an assembly.
As a last item, I've included a sample SQL script (see Figure 15) that will generate the C# proxy code to call a stored procedure, including attributes on parameters where needed. It is especially useful where you need dumb and direct wrappers to your stored procedures. The script by no means encompasses all cases (for one, it doesn't cover output parameters), but for the most common class of stored procedures, it does the job right out of the box. In fact, I keep improving on it each time I come across any peculiar scenarios previously unhandled (like a data type).
To use the script, simply load it into isqlw (SQL Query Analyzer), switch to your database if required, set the @sp variable to the stored procedure name, and go! Take the output code from the result pane, plug it into your solution, and tweak it if necessary. The script assumes that you will be returning a SqlCommand object from your function, which is mainly useful for queries where you want to leave the data acquisition method up to the caller. In some situations the caller may want to connect the command to a SqlDataAdapter whereas in other situations a SqlDataReader would be a sufficient choice.
To get the right results, you will also need to tell isqlw to remove headers and show results in text rather than the grid. Both of these settings can be controlled from the Results tab of the Options dialog, which is shown in Figure 16. I should mention that the script has been only tested on SQL Server 2000, though it should run fine against version 7.0, too.
.gif)
Figure 16 Remove Headers and Show Results in Text
Getting the script to generate Visual Basic .NET should be straightforward and so it is left as an exercise for the reader.
Conclusion
The ability to write your own custom attributes, tie them to various program elements, and query metadata via reflection generates tremendous opportunities for automation and a whole new class of dynamic solutions. Hopefully through my demonstration of attributes and reflection in the context of a real problem, such as simplifying calls to stored procedures, you can see how they will be useful in other situations. The sample library can be used as is in any CLS-compliant language or adapted. There is even room for much improvement there. For example, you could implement some caching so that frequently used and complex commands with lots of parameters do not become a performance bottleneck. However, I wouldn't go too far down that road without first profiling the code and identifying the generator as the problem. In general, SqlCommandGenerator should only be marginally slower than setting up the command object by hand.
|
.gif)