muSQLe - SQL Server Query Invocation Framework

• Download source files - 6.25 KB
• Download library - 14.87 KB

Introduction

With the coming of ADO.NET, we have an extremely powerful way to communicate with Microsoft SQL Server. But invocation of SQL stored procs, functions and even simple queries make you write something like...

using (SqlCommand cmd = connection.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "MyProcName"; cmd.Parameters.Add ( ... ); .... cmd.Parameters.Add ( ... ); cmd.ExecuteNonQuery(); // or cmd.ExecuteReader() and more lines of code // to read results // and even more lines if Output parameters are present. }

... every time you need it. Also you have to use DBNull.Value instead of nice C# null or VB's Nothing (and this requires additional checks and conversions). Of course, you can write a lot of wrappers for each query and use them as built-in functions, but, I think, it's not very handy.

Background

The core part of this project is a SqlQuery class. It is derived from RealProxy and is used for wrapping interfaces with declared SQL functions. SqlImportAttribute attribute is used to mark a method as SQL method. It may also have function name or query type and text specified.

When method is called, proxy looks it up in the cache represented by internal static SqlParameterCache class. When cache entry is found (i.e. this method was already parsed), it is returned back to proxy. Otherwise, it is parsed from MethodInfo definition using reflection. Also some basic checks are performed:

• SqlReturnAttribute attribute can be used only once per method definition.
• SqlReturnAttribute-marked parameter must be out and have a class or Nullable<> type.
• If method's return type is marked with SqlReturnAttribute, it must be a class or Nullable<> type.
• ref parameters are interpreted as InputOutput SQL parameters, out - as Output. Both they must have class or Nullable<> type.
• SqlTypeAttribute must be specified for Output parameters.

When all these checks are passed, SqlParameterCacheEntry instance is stored in cache and passed back to proxy. If some of the checks fail, ArgumentException is thrown.

Then proxy creates and initializes an instance of SqlCommand class, fills out all needed parameters from passed arguments and executes command. Type of execution is determined from method definition:

• If method has void, int or SqlReturnAttribute-marked return type, ExecuteNonQuery() is used.
• If method has DataSet return type, ExecuteReader() is used.
• If method has DataTable return type, ExecuteReader(CommandBehavior.SingleResult) is used.
• If method has SingleRowDataTable return type, ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow) is used.

SingleRowDataTable class is derived from DataTable and used mainly for type distinguish. It also provides a Row property to access the first row (if there is none, null is returned).

Usage

First of all, you need to declare an Interface containing method definitions. General method description:

1. Every method definition starts with [SqlImport]. It can be passed without parameters.
   • If method name differs from stored procedure/function name, real name must be specified in parameter: [SqlImport("realname")]
   • If method is used to wrap another type of query (Table direct query, SQL query), query type must be also specified: [SqlImport(CommandType.TableDirect, "tableName")]
2. Then return type definition comes:
   • For stored procedures, that does not return any table data (or we are not interested with it), we should use one of these types:
     • void
     • int (returns number of rows affected by call)
     • [return: SqlReturn]-marked nullable type - return value for stored procedure or function. For example: [return: SqlReturn] int?
     • [return: SqlReturn("ParamName")]-marked nullable type - output parameter of stored procedure. For example: [return: SqlReturn("ParamName"), SqlType(SqlDbType.Int)] int?
   • When stored procedure returns table data, DataSet, DataTable or SingleRowDataTable type should be used.
3. Method name to call from your code.
4. Parameters. [SqlReturn] attribute can also be applied to out parameter. [SqlType] attribute must be applied to each out parameter.

Help text and description could be applied to method via XML comments (as shown in the picture).

Example

// Interface definition: interface IQuerySample { /// <summary> /// Returns a list of active sessions on server /// </summary> /// <param name="LogiName">Show sessions only for this user, /// null - for all users</param> /// <returns>DataTable with session info</returns> [SqlImport("sp_who")] DataTable ShowSessions(string LogiName); } // Usage: // Let's fill DataGridView named 'grid' with session information. // Don't forget to turn on AutoGenerateColumns property. // ... IQuerySample q = SqlQuery<IQuerySample>.Create(connection); grid.DataSource = q.ShowSessions(null); // ... Here we could use 'q' more and more times ...

Nice and easy, huh?

Version History

• 9.09.06. 
• New overload for Create method added to allow creating query from connection string. When using this overload, connection is opened just before executing query and is closed after this. Also NullableEnum<T> static class is added to help converting nullable numeric query results to/from enum values.
• 7.09.06. 
• Fixed bug with NULL value in output parameter returned as method result. More detailed exception descriptions added.
• 6.09.06.
• Fixed bug with out parameters. Added SqlTypeAttribute attribute for explicit type specifying. Added optional parameter to SqlReturnAttribute to allow method return not only return value, but out parameter also. So, you may write [return: SqlReturn("ParamName")] to return value of output parameter ParamName, that is very useful for stored procs returning in the output parameter, for example, Id of just created record.
• 1.09.06.
• Initial release