Posts
202
Comments
1112
Trackbacks
51
July 2008 Entries
LINQ to SQL - Return Scalar Value from a Stored Procedure

One of the best things about LINQ to SQL is that is really does have solid support for stored procedures.  However, it's not quite as friendly as dynamic LINQ to SQL with scalar values because you can't take advantage of anonymous types.  Instead you must return an actual known type.  Furthermore, this can't be a primitive so you can't return string or bool directly.  You also can't use a class that doesn't have a default (parameterless) constructor so that also rules out nullable types like Nullable<bool>.

Consider a trival stored procedure that looks like this:

   1:  CREATE PROCEDURE [dbo].[GetFirstName]
   2:      @ID int
   3:  AS
   4:  BEGIN
   5:      SELECT FirstName
   6:      FROM   Contacts
   7:      WHERE  ContactID = @id
   8:  END

We just want to return a single scalar string here. So we have to create a dummy class that looks like this:

   1:  public class DummyClass
   2:  {
   3:      public string FirstName { get; set; }
   4:  }

Then our DataContext method can look like this:

   1:  [Function(Name = "dbo.GetFirstName")]
   2:  public ISingleResult<DummyClass> GetFirstName(int id)
   3:  {
   4:      IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);
   5:      return (ISingleResult<DummyClass>)result.ReturnValue;
   6:  }

And add to that, we'll need an extra consuming method that defensively checks for an empty resultset:

   1:  public static string GetFirstName(int id)
   2:  {
   3:      using (MyDataContext dataContext = new MyDataContext())
   4:      {
   5:          var result = dataContext.GetFirstName(id).FirstOrDefault();
   6:          if (result != null)
   7:          {
   8:              return result.FirstName;
   9:          }
  10:          else
  11:          {
  12:              return null;
  13:          }
  14:      }
  15:  }

So all this works just fine but it seems like a lot of work to go through just to get a simple scalar value from a stored procedure.  Not to mention that I don't want to have to go through this every single time I need a scalar value from a stored procedure in my app.  Visual Studio will essentially generate the DummyClass for me but what if I'm hand-crafting my LINQ to SQL and am not currently using Visual Studio code generation?  I don't want to have to go through that every time.  So what can we do to make this slightly more developer-friendly?

First off, I want to avoid code duplication if at all possible.  Not only if I return multiple scalars at different places in my app but also I'd prefer to avoid the duplication if these scalars are different data types (e.g., sometimes strings, sometimes Boolean, etc.).  What we can do is to create a simple generic class called Scalar that looks like this:

   1:  public class Scalar<T>
   2:  {
   3:      public T Value { get; set; }
   4:  }

This means I can re-write my DataContext method like this:

   1:  [Function(Name = "dbo.GetFirstName")]
   2:  public ISingleResult<Scalar<string>> GetFirstName(int id)
   3:  {
   4:      IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);
   5:      return (ISingleResult<Scalar<string>>)result.ReturnValue;
   6:  }

So since I'm using generics here, I never have to write another dummy class again - regardless of whether I've got a string or Boolean or whatever.  I can always just re-use Scalar<T>.

Additionally, I had to write a consuming method that checked to see if DummyClass was null and if not return its value; otherwise return null.  Now that I know I'm always going to use the Scalar class for all my scalar queries, I can move this into a re-usable extension method like this:

   1:  public static T GetScalar<T>(this Scalar<T> scalar)
   2:  {
   3:      if (scalar != null)
   4:      {
   5:          return scalar.Value;
   6:      }
   7:      else
   8:      {
   9:          return default(T);
  10:      }
  11:  }

So basically, all I've had to do is create that trivial Scalar<T> class and that 1 extension method which consisted of 1 IF statement and now anytime I need a scalar value from a stored procedure in my app, it becomes as trivial as writing my data context method with the re-usable Scalar<T> and this:

   1:  public static string GetFirstName(int id)
   2:  {
   3:      using (MyDataContext dataContext = new MyDataContext())
   4:      {
   5:          return dataContext.GetFirstName(id).FirstOrDefault().GetScalar();
   6:      }
   7:  }

Now, the one catch - the one thing to notice here is that the property name in Scalar<T> is "Value".  That means that I'd have to change my stored procedure to alias my scalar data like this:

   1:  SELECT FirstName as Value

Although this is a downside, I believe it's well worth it in order to realize all the other benefits of quick implementation and avoiding duplicate and unnecessary code.

Posted On Tuesday, July 8, 2008 10:55 PM | Comments (18)
MVC ComponentController vs. ViewUserControl

UPDATE: The component controller was removed from the MVC framework before the RTM release. For an updated version of this post, click here.

At some point when creating a web app, you're going to want some reusable UI components.  This might be because you want the same visual UI snippet repeated more than once on a single page or it might be because you want to use the same component on multiple pages.  In a traditional ASP.NET web app, typically you would use a User Control for this type of thing.  In MVC you still have the option of using a User Control but you also have the option of rendering your UI snippet with a ComponentController.  So the question is, when do you use which?  There are pros and cons to each.

The most common scenario for a user control is that you pass in the Model from the containing view page.  For example, consider a AddressEditor user control that you use multiple times on a page to edit Contacts - once for Home address and once for Work address.  It might look like this:

<%=Html.RenderUserControl("~/Views/Home/AddressEditor.ascx", new AddressViewData(ViewData.Model.Contact.HomeAddress))%>

This is fine for those simple cases but what happens when you want your user control to have its own self contained logic?  For example, what if you're buildng a portal or a portal-like website where you want to have several self-contained "widgets" in your page.  You don't want the parent controller having to keep track of the models for 10 different widgets and then have to pass them in to each one.  A good answer to this is to use a ComponentController rather than a View user control.  A ComponentController can render its own views and the containing page can just look like this:

   1:  <div id="firstWidget" class="widget">
   2:      <%=Html.RenderComponent<MvcWidget.Controllers.WidgetCompController>(c => c.Widget1()) %>
   3:  </div>

In this case, the controller class basically looks like most any other controller class with the exception that it inherits from ComponentController:

   1:  public class WidgetCompController : ComponentController
   2:  {
   3:      public void Widget1()
   4:      {
   5:          IWidgetManager widgetManager = new Widget1Manager();
   6:          List<Foo> list = widgetManager.GetFooData();
   7:          RenderView("Widget1", list);
   8:      }
   9:  }

However, there still are some uses for view user controls here.  Let's say you want to render your widgets via an AJAX call.  One feature of the "normal" MVC controllers is that the views rendered can be either aspx or ascx.  Rendering an ascx user control from an MVC Controller would be a good choice when you want to implement the AJAX HTML Message Design Pattern where you're returning only an HTML snippet from the server rather than an entire page.  In this case, we can render our user controls with an AJAX jQuery call.  So if we had a couple of HTML divs (called "secondWidget" and "thirdWidget") and a WidgetController class with actions methods Widget2() and Widget3(), we could simply implement this simple jQuery function:

   1:  <script type="text/javascript">
   2:      $(function() {
   3:          $('#secondWidget').load('/Widget/Widget2');
   4:          $('#thirdWidget').load('/Widget/Widget3');
   5:      });
   6:  </script>

These are some rough ideas to get you started.  The complete code sample can be downloaded here.

Posted On Sunday, July 6, 2008 9:29 PM | Comments (2)

View Steve Michelotti's profile on LinkedIn

profile for Steve Michelotti at Stack Overflow, Q&A for professional and enthusiast programmers




Google My Blog

Tag Cloud