muSQLe - SQL Server query invocation framework

• Download source files - 6 Kb
• Download library - 15 Kb

Introduction

With coming of ADO.NET we have an extremely powerful way to communicate with MS SQL Server. But invocation of SQL stored procs, functions and even simple queries makes you to 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 cource, 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 method as SQL method. It also may 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 methods 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 check are passed, SqlParameterCacheEntry instance is stored in cache and passed back to proxy. If some of checks are failed, 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. Also it provides a Row property to access first row (if there is no one, 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 shound 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 on 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 either. 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

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

Msgs 1 to 15 of 15 (Total in Forum: 15) (Refresh) FirstPrevNext

Info about the example Manuel Salvatore 4:56 2 Oct '06   Hi, My name's Manuel, i'm from Italy, i'm really interested 'bout the sample you posted. I found it very clear and interested. Very nice job as many people said to u!! but now my question is about another thing, I wish to use you example in a open source project, 'cause i think your idea could be used in a project as a good news! what u think? can i use your code/idea? are u interested in collaborating at this project?? Many thanks Manuel Salvatore Martone Sign In·View Thread·PermaLink Re: Info about the example Messir 5:52 2 Oct '06   Hi, Manuel. Sure, you can use it. When your project will be published, I'd be interested to take a look at it. Sign In·View Thread·PermaLink Possible change to Invoke method Jamie Nordmeyer 6:04 11 Sep '06   I looked over the code changes you recently made, and personally, I'm not too sure about having to use the To<> and From<> methods, simply because I could do the same with Enum.ToObject directly in my code. It looks good, but I thought an alternate idea might be in order. It is, of course, your code, so use it or toss in the recycle bin as you see fit. The change I'm proposing is the following, and it's to the Invoke method: if (entry.ReturnsValue) { object value = cmd.Parameters[entry.ReturnParameter].Value; if (value == DBNull.Value) value = null; // Change starts here... Type tt = mi.ReturnType.GetGenericArguments()[0]; if (tt.IsEnum) value = Enum.ToObject(tt, value); // Change ends here... return new ReturnMessage(value, outParams.ToArray(), outParams.Count, method.LogicalCallContext, method); } This should be safe, as you're already checking to be sure that the return type is a Nullable<>. Just a thought. Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink 2 improvement ideas Jamie Nordmeyer 18:56 8 Sep '06   First, I think it'd be great to have the library be able to open and close its internal SqlConnection object just before and just after the actual call to ExecuteNonQuery and its siblings. For Example: SqlConnection conn = new SqlConnection(someConnectionString); IQuery query = SqlQuery<IQuery>.Create(conn); DataTable tbl = query.GetSomeDataTable(); // In here, conn.Open is called JUST before the data needs to be queried, and conn.Close is called as soon as possible. This way, the disconnected data model is maintained. Maybe a property or constructor parameter can turn off auto opening and closing. Secondly, I'd like to see something like this if possible: [SqlImport] [return: SqlReturn, SqlType<SomeCustomEnum>] SomeCustomEnum? GetIntValueFromDBThatMapsToEnum(); What do you think? Maybe not that exact syntax, but hopefully I got my thought process across. I want to be able to take a stored procedure that returns an int, and map it to an Enum. Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink Re: 2 improvement ideas Messir 5:36 9 Sep '06   Well, internal connection is a good idea! I decided to add one more Create overload, smth like Create(string connectionString). When query interface is constructed from connection string, is openes and closes connection for each query. What about enums... I think, it'll be easier to create a generic static class, which will convert values to/from enums. Smth like: MyEnum? enumValue = SqlEnum<MyEnum>.From<int>(query.MySqlMethodReturningInt()); or backward conversion: int? intValue = SqlEnum<MyEnum>.To<int>(enumValue); Thanks for your ideas! Sign In·View Thread·PermaLink Re: 2 improvement ideas Messir 5:59 9 Sep '06   And SqlEnum class may be declared as follows: static class SqlEnum where T : struct { public static T? From(V? value) where V : struct { if (!typeof(T).IsEnum) throw new ArgumentException("T must be enum type", "T"); if (!typeof(V).IsPrimitive) throw new ArgumentException("V must be primitive type", "value"); if (!value.HasValue) return null; return (T)Enum.ToObject(typeof(T), value.Value); } public static V? To(T? value) where V : struct { if (!typeof(T).IsEnum) throw new ArgumentException("T must be enum type", "value"); if (!typeof(V).IsPrimitive) throw new ArgumentException("V must be primitive type", "V"); if (!value.HasValue) return null; return (V)Convert.ChangeType(value.Value, typeof(V)); } } Sign In·View Thread·PermaLink Re: 2 improvement ideas Jamie Nordmeyer 7:34 9 Sep '06   Cool! I look forward to seeing the changes published. Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink Question about return parameters Jamie Nordmeyer 13:12 6 Sep '06   I've got the following IQuery definition: public interface IQuery { [SqlImport("uspGetMaxBase")] [return: SqlReturn("MaxBase"), SqlType(SqlDbType.Float)] float? GetMaxBase(); } And the following code to call it: SqlConnection conn = new SqlConnection("Data Source=ICO-JNORDMEYER;Initial Catalog=TestMuSQLe;Integrated Security=True; Pooling=False"); conn.Open(); IQuery query = SqlQuery<IQuery>.Create(conn); float? maxBase = query.GetMaxBase(); The code errors out on the call to GetMaxBase with the following error message: InvalidCastException: Return argument has an invalid type. Any ideas? Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink Re: Question about return parameters Messir 13:25 6 Sep '06   Hi. Could you please give a definition of your uspGetMaxBase stored procedure? Sign In·View Thread·PermaLink Re: Question about return parameters Jamie Nordmeyer 13:26 6 Sep '06   Sure. It's just a test proc against a quick database that I created for playing with the library: ALTER Procedure uspGetMaxBase ( @MaxBase float output ) As Select @MaxBase = MAX(Base) From tblCustomers; Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink Re: Question about return parameters Messir 13:55 6 Sep '06   Oh, I've got it! This is because you are using wrong type. SQL Server's Float is actually .NET double. So, you should change method declaration to [SqlImport("uspGetMaxBase")] [return: SqlReturn("MaxBase"), SqlType(SqlDbType.Float)] double? GetMaxBase(); Sign In·View Thread·PermaLink Re: Question about return parameters Jamie Nordmeyer 14:03 6 Sep '06   Duh. Oh well. Thanks for the help. Again, awesome library! Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink Very nice! Jamie Nordmeyer 12:11 5 Sep '06   Well, I've only played with a couple of simple select queries, as well as some update/insert/delete queries, and such, but so far, VERY nice! I've been thinking about doing something like this for a while, but I really like the simplicity of this. Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink Re: Very nice! Messir 12:40 5 Sep '06   Thanks! By the way, I've discovered some problems with output parameters and fixed them. I'll update the article tomorrow... Sign In·View Thread·PermaLink Re: Very nice! Jamie Nordmeyer 12:47 5 Sep '06   Awesome! I hadn't had a chance to play with output params yet with this library. I look forward to the update, though. Kyosa Jamie Nordmeyer - Taekwondo Yi (2nd) Dan Portland, Oregon, USA Sign In·View Thread·PermaLink

Last Visit: Monday, December 1, 2008 4:46 PM     Last Update:Monday, December 1, 2008 4:47 PM