Click here to Skip to main content
Click here to Skip to main content

muSQLe - SQL Server Query Invocation Framework

, 6 Sep 2006
Rate this:
Please Sign up or sign in to vote.
An easy and strongly-typed way to invoke queries and stored procedures from your code
Sample Usage Screenshot

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Messir
Web Developer
Russian Federation Russian Federation
Alex
.NET Developer
Russian Federation
rakemaker@gmail.com

Comments and Discussions

 
QuestionInfo about the example PinmemberManuel Salvatore2-Oct-06 3:56 
AnswerRe: Info about the example PinmemberMessir2-Oct-06 4:52 
GeneralPossible change to Invoke method PinmemberJamie Nordmeyer11-Sep-06 5:04 
General2 improvement ideas PinmemberJamie Nordmeyer8-Sep-06 17:56 
GeneralRe: 2 improvement ideas PinmemberMessir9-Sep-06 4:36 
GeneralRe: 2 improvement ideas PinmemberMessir9-Sep-06 4:59 
GeneralRe: 2 improvement ideas PinmemberJamie Nordmeyer9-Sep-06 6:34 
GeneralQuestion about return parameters PinmemberJamie Nordmeyer6-Sep-06 12:12 
GeneralRe: Question about return parameters PinmemberMessir6-Sep-06 12:25 
GeneralRe: Question about return parameters PinmemberJamie Nordmeyer6-Sep-06 12:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 6 Sep 2006
Article Copyright 2006 by Messir
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid