Executing a Stored Procedure....The Proper Way

As we develop in Microsoft's Entity Framework family, we will at times run into a situation, or by design, the need to execute a Stored Procedure from the database (most likely Microsoft SQL Server).  As we have discovered, they (Microsoft) evidently do not want us to do this inherently.  They hid the direct SProc execution well down into the Database object (off the DbContext object) and even then it has a few names: SqlQuery and ExecuteSqlCommand.  Of course, both of these have their equivalent ASync execution versions.

To make matters even more fun and interesting, is the problem of Ordinal execution.  Any proper DBA will tell you, if asked, that Ordinal execution is a bad implementation pattern.  And you developers, will simply respond: Well then don't change the order in which the Parameters exist.  Then the DBA responds, and the banter battle begins between a Developer and a DBA.

My simple solution to avoid this whole needless verbal battle and disagreement is a very simple Extension built off the DbContext object being developed.

1:  public static IEnumerable<T> Execute<T>(this DungeonContext ctx, string schema , string sproc , params SqlParameter[] parms ) where T : class {  
2:     if( parms.Count() > 0 ) {  
3:        for( int i = 0 ; i < parms.Count() ; i++ ) {  
4:           if( i >= 1 )  
5:              sproc += ", ";  
6:           sproc += parms[ i ].ParameterName + "=@p" + ( i + 1 ).ToString( "D2" );  
7:           parms[ i ].ParameterName = "@p" + ( i + 1 ).ToString( "D2" );  
8:        }  
9:     }  
10:     return ctx.Database.SqlQuery<T>( "Execute " + schema + "." + sproc , parms );  
11:  }

This simple extension, allows you to make a SProc call, the way developer like and executes the design the way DBA's want Explicit Parameter value assignment

1:  using(var ctx = new DungeonContext())  
2:  {  
3:       ctx.Execute<Object>("DBO", "Get_SomeText",   
4:            new SqlParameter("@Parameter1", "value"),  
5:            new SqlParameter("@Parameter2", "value2")  
6:  }

Popular posts from this blog

SysInternals - BgInfo for ALL Users

Image
I have seen various sites that have been pretty good at getting close to the point of getting Background Information created by the tool BgInfo but none of them have actually solved my specific problem. Solutions currently out there include: Placing into the "All Users" folder This did not work for me, as our implementation does not contain an "All Users" folder in the "Users" folder for profiles. Reg-Hacking and HKCU or HKLM entry This is the worst of all the bad choices available.  I am experienced with Registry Editing and I still would not want or find a need to do this. Placing into the "Default" folder This is good going forward but what about those 20+ user profiles that are already logged in.  FYI, if your server has 20+ user account profiles in the "Users" directory you have larger problems than I am willing to assist with in this posting. The solution that worked for me, will be described below in pain-staki
Read more

JSON/AJAX Helpers

Recently, had an implementation where I needed to implement ASP.Net Web API 2 and accompanied it with Javascript/JQuery AJAX communication.  In doing this i found myself making the same pattern calls with some varying value for certain properties for the AJAX data calls.  As such, i ended up building a few helper functions to simply the inclusion of these various minor quirks based on what was needed.  Below  you will find the various forms. 1: /********************************************************************* 2: @type - dataType, such as 'json', 'xml', 'script', 'jsonp' or 'html' 3: @contentType - string value, represents the HTML type of content 4: IE: 'application/json; charset=utf-8' 5: @url - API URL, such as 'api/tasks' should be constrainted to the 6: Web API Controller 7: @success - Function(data, status, xhr) handler, The logic to execute 8: on a 200 res
Read more

Acquiring List of controls - Classic JS

Recently ran into a scenario where I need to use the low-level JavaScript parser, instead of JQuery.  During this venture, I ran across a situation where it would be easier for me to select all the HTML Elements with an ID, assign it to a variable as JSON, and then operate on it from that point. So using my previous short-hand experience with JQuery, I started constructing a similar type of format to be used. *********** JQuery *************** // One line and your done, pretty much var cntrls = $('[id]') // Later on, you simply access the element from the variable var val = cntrls['someID'].value *********** JavaScript *********** // Not so one-liner; put in the DOM.Ready event for JS var cntrlIDs = {} var root = document.getElementById('rootID').querySelectorAll('*'); for (i = 0, n = root.length; i < n; ++i) { var el = root[i]; if (el.id) { cntrlIDs[el.id] = el; } } // Later
Read more