Saturday, December 12, 2009

Entity Framework IEnumerable Lambda Join Sample

I figured this out from a sample, but it wasn't at all clear. The docs provide no sample, and the parameter names are totally confusing. If you're using EF, and you're wondering how the Join IEnumerable extension method works with your entities, here is the simplest example:


var parent_child = entities.Parent.Join

 (

   entities.Child,            // child entity

   parent => parent.ParentId, // function providing parent key

   child => child.ChildId,    // function providing child key

   ( parent, child ) => new   // projection (anonymous object returning what you want)

   {

      ChildId = child.ChildId,

      WhateverElseFromChild = child.WhateverElse,

      WhateverElseFromParent = parent.WhateverElse,

      ParentId = parent.ParentId

   }

       // pc here is anonymous object created above

 ).Where( pc => pc.Condition == "some condition" ).FirstOrDefault();


Tracing this against SqlServer showed very clean SQL. The exact same SQL produced by this linq equivalent:



var parent_child = ( from parent in entities.Parent

                                join child in entities.Child on parent.ParentId equals child.ChildId

                                where parent.Condition == "some condition"

                                select new

                                {

                                        ChildId = child.ChildId,

                                        WhateverElseFromChild = child.WhateverElse,

                                        WhateverElseFromParent = parent.WhateverElse,

                                        ParentId = parent.ParentId

                                }).FirstOrDefault();

Thursday, November 19, 2009

Handling Extensionless URIs in a WebForms Application

[Edit: I wrote this just a few days before finding out that WebForms 4 does routing out of the box]

ASP.Net MVC is all the rage, but I just needed to add extensionless functionality to a WebForms application. I'm going to host this application in IIS7, so that's how I'm describing it; there is more work involved for IIS6.

This all seems obvious now, but it took me awhile to figure out.

I started by adding a class that supported IHttpHandler. I need to have a handler (rather than a module) because I want to set a value in session, and that's not yet available within a module (at that phase in the IIS pipeline).


public class OnDemandHandler : IHttpHandler, System.Web.SessionState.IRequiresSessionState
{
public void ProcessRequest( System.Web.HttpContext context )
{
; // add something to session
}

public bool IsReusable { get { return true; } }
}

<httphandlers>
<add verb="GET" path="*" type="MyNamespace.OnDemandHandler, MyDll" validate="false">



That works fine, but it handles everything. How do I handle aspx pages, or images, or anything else? Copy in all the base handlers from the web.config at the machine level to my web.config? Move my httpHandler config element from my web.config to the machine web.config so it can fall last in order there and only run after all the base handlers have run? Use a module instead of a handler? I was having a hard time figuring out what to do until I decided to go check out a config of an MVC project. After all, they are doing what I want to do:


<httphandlers>
<add verb="*" path="*.mvc" validate="false" type="System.Web.Mvc.MvcHttpHandler, System.Web.Mvc, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
</httphandlers>
<httpmodules>
<add name="UrlRoutingModule" type="System.Web.Routing.UrlRoutingModule, System.Web.Routing, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
</httpmodules>



Again, it seems obvious now; use a module that checks for extensionless URIs, tack on my own pre-determined extension (just like they're using *.mvc), and send it off to my handler. For URIs with extensions, let them pass on normally.

So here's how it looks in the end:


public class OnDemandModule: IHttpModule
{

#region IHttpModule Members
public void Dispose()
{
}

public void Init( HttpApplication app )
{
app.BeginRequest += new EventHandler( OnBeginRequest );
}
#endregion

private void OnBeginRequest( object src, EventArgs e )
{
HttpContext context = ( src as HttpApplication ).Context;
var uriPath = context.Request.Path;
if ( UriIsExtensionless( uriPath ) )
{
/// Add the .inst extension so our handler will process it
context.Response.Redirect( context.Response.ApplyAppPathModifier( uriPath + ".inst" + context.Request.Url.Query));
}
}

/// <param name="uriPath">like /blah or /something.aspx. Does not contain possible query params</param>
/// <returns></returns>
private bool UriIsExtensionless( string uriPath )
{
var result = false;
if ( uriPath[0].Equals( '/' ) )
{
var restOfURI = uriPath.Substring( 1 );
if ( restOfURI.Length > 0 )
{
return !restOfURI.Contains( '.' );
}
}
return result;
}

}

public class OnDemandHandler : IHttpHandler, System.Web.SessionState.IRequiresSessionState
{
public void ProcessRequest( System.Web.HttpContext context )
{
context.Response.Write("OnDemandHandler fired");
context.Response.End();
}

public bool IsReusable { get { return true; } }
}

<!-- for cassini -->
<system.web>
<httphandlers>
<add verb="GET">
path="*.inst"
type="MyNamespace.OnDemandHandler, MyAssembly"
validate="false" />
</httphandlers>
<httpmodules>
<add name="OnDemandModule" type="MyNamespace.OnDemandModule, MyAssembly">
</httpmodules>
</system.web>

<!-- for IIS7 -->
<system.webserver>
<modules runallmanagedmodulesforallrequests="true">
<add name="OnDemandModule">
type="MyNamespace.OnDemandModule, MyAssembly" />
</modules>
<handlers>
<add name="OnDemandSurvey">
verb="GET"
path="*.inst"
preCondition="integratedMode"
type="MyNamespace.OnDemandModule, MyAssembly" />
</handlers>
</system.webServer>

Monday, November 16, 2009

Getting the SQL (and other goodness) from a running transaction

I had run a bunch of SQL scripts against a SQL Server database during a deployment. All tests after the updates checked out. I went to close SSMS and got the message, "There are uncommitted transactions. Do you wish to commit these transactions before closing the window"?

WTF? How could I have failed to commit a transaction yet testing all checked out? Well that's a mystery I haven't yet figured out, but I did find a good query (that I had to alter somewhat to make work) for getting the text of the command that had not yet been committed:


SELECT
  st.Session_id, req.Blocking_Session_ID [Blocker], req.Wait_Type,
  req.Wait_Time [WaitTimeMS], req.Wait_Resource, req.open_transaction_count,
  req.percent_complete, dt.transaction_id, dt.database_transaction_begin_time,
  case when dt.database_transaction_type = 1 then 'RW'
    when dt.database_transaction_type = 2 then 'R'
    when dt.database_transaction_type = 3 then 'Sys'
    else 'Unknown'
end [TranType],
  case when dt.database_transaction_state = 1 then 'Not Initialized'
    when dt.database_transaction_state = 3 then 'Initialized, but no logs'
    when dt.database_transaction_state = 4 then 'Generated logs'
  when dt.database_transaction_state = 5 then 'Prepared'
    when dt.database_transaction_state = 10 then 'Committed'
    when dt.database_transaction_state = 11 then 'Rolled Back'
    when dt.database_transaction_state = 12 then 'In process of committing'
  else 'Unknown'
end [TranState],
  req.Status, req.Command, stxt.objectid [ExecObjID],
(SUBSTRING(stxt.text, req.statement_start_offset/2,( CASE WHEN req.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), stxt. text)) * 2
        ELSE req.statement_end_offset
      end -req.statement_start_offset)/2)) [SubText],
  stxt.text, req.statement_start_offset
FROM
  sys.dm_tran_database_transactions dt (nolock)
  inner join sys.dm_tran_session_transactions st (nolock) on dt.transaction_id = st.transaction_id
  inner join sys.dm_exec_requests req (nolock) on st.transaction_id = req.transaction_id
  CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) [stxt]
where
  dt.database_id = db_id() and st.is_user_transaction = 1


This query wouldn't work in my situation (would barf "Incorrect syntax near '.'" at the second to last dot), but it will work on a database with compatibility level 90 or better.

Friday, October 09, 2009

IE8 Text Alignment Bug

Below demonstrates a bug in the current version of IE8 (8.0.6001.18702). Two duplicate tables one on top of the other, but the text is not aligning correctly in the second (text-align:left is ignored). This is admittedly an edge case since it only seems to happen with an image in the th element, and the image must apparently be of certain dimensions.












36
This sentence should left align with bob below.











41
bob

Wednesday, September 30, 2009

F# Forward composition operator

I was trying to understand the difference between the plain pipe operator (|>) and the forward composition operator (>>) in F# when the F# forum pointed me to this blog post. After reading the blog post, I still didn't quite understand. I distilled it down to perhaps the simplest possible sample which I think is much easier to understand what it does and why:

let squares wrist =
List.map (fun x -> x*x) wrist

let sum wrist =
List.fold (+) 0 wrist

// Forward composition operator
// don't need to declare any value: it's implied.
let functionComp = squares >> sum

// Equivalent function as above using pipe operator.
// Note how above is more terse
let pipage wrist = wrist |> squares |> sum

let wrist = [1;2;3]

// results are the same
let fc = functionComp wrist
let p = pipage wrist

Friday, June 19, 2009

The Question for .Net Web Developers

Here's what I think is the most important question right now for the .Net developer considering new applications. It may be invalid, but here goes:

Silverlight, or MVC?

To me, silverlight turns the browser into a desktop application; when I worked on silverlight, I no longer thought about GET/POST whatsoever. Yes, I still get data from services, but I'm almost completely unaware of HTTP. Moreover, it seemed to lend itself to very clean applications from a pure OO perspective; same exact OO style on client and server.

With MVC, I'm thinking GET/POST over HTTP all the while. I'm getting back to basics in that regard (stepping back to the way things were before MS took desktop development and jammed it into the Internet with WebForms).

IMO, blending silverlight with MVC is a bastardization (don't take it personally if you're doing it). URLs need only have meaning in silverlight applications as a shortcut to start your application. In any case, I consider WebForms legacy for non-trivial applications. Still very useful when needed, but legacy.

So this is my question for today's .Net developer: "should we think about our new application like we did with desktop apps, or should we consider it RESTful?" Obviously, there are myriad other considerations; I just think this may be the most pertinent.

Wednesday, April 15, 2009

Workaround for changes to DataSet.Merge

We have a formerly open source system (nSurvey) running in production. None of us know too much about it, but we had to learn a little something when we recently upgraded a parent site to (finally) run against .Net framework 2.0. We tried to set the nSurvey site (which had to be a child site) to use framework 1.1 which is possible depending on what you have in the web.config of the parent site (parent as in parent in IIS). In our case, it had AJAX entries which have attributes causes framework 1.1 to blow up. As such we had to upgrade the nSurvey project to Visual Studio 2005 (framework 2.0).

DataSet.Merge changed between .Net frameworks 1.1 and 2.0 when DataSets have namespaces causing the merge to fail. There is a workaround, but the place where it's described on MS docs is totally freaking hosed and makes no sense.

Here is what we did which works fine for us (we're not about to go making big changes in this monster of a system):
string cachedNamespace = dataSet1.Namespace;
dataSet1.Namespace = string.Empty;

string cachedNamespaceChild = dataSet2.Namespace;
answerDataSet.Namespace = string.Empty;

dataSet1.Merge( dataSet2 );

dataSet1.Namespace = cachedNamespace;
dataSet2.Namespace = cachedNamespaceChild;

Wednesday, February 11, 2009

A Functional Deployment Template

We had been using Excel spreadsheets to build out our deployment plans. The biggest chunk of the plan was usually all the database scripts (I prefer listing them separately for better troubleshooting if one fails). Perhaps there is a better way to deploy database changes (SQL Server), but I'm not aware of it.
Anyhow, spreadsheets were not up to the task for a couple reasons:
  • We wanted to be able to have plain written documentation (paragraphs) in addition to some kind of "steps" checklist (we needed to start making complete deployment documents). It's possible to to this in Excel, but certainly not optimal.
  • Editing large scripts in Excel cells can prove clunky. A line length limit makes it nearly impossible (if you try to paste in a script with a line too long nothing happens - no error and no pastey) to edit some large scripts.
We thought about several possible tools, but what we really wanted was a Word template with some tables in it (for steps). But one of the columns of the table needed to have a button (or something) that would pop up a separate editing window for editing the scripts. The edited script would then "collapse" back into it's cell (basically like an Excel cell but with a better mechanism for editing large scripts).

I searched for a fair amount of time and came up with absolutely nothing. I did find a few deployment templates, but nothing with any functionality to them. So I went ahead and implemented such a beast using VSTO over a Word template. Perhaps someone will stumble upon this and find it useful. Implementing it was more challenging than one might think. Just like anything else, if you're not very familiar with VSTO, even small things are going to be difficult, but dynamically creating and persisting WinForms controls is actually sorta tricky.

Anyway, here's what it looks like:


Thursday, February 05, 2009

Stranahan's Colorado Whiskey [off topic]

I was going to put this in an email, but I figured, what the heck. Maybe some whiskey connoisseur will find it.

I have gift cards from Christmas, and I keep going back to Tipsy's (the mega-liquor-store near my house) to attempt to get some Leopold's (whose flavored whiskeys are still awesome) but they never have it - Leopold's production is low and demand high. So I asked an employee if I could get a rain-check or something. He asked if I wanted Stranahan's explaining that some just came in but it's going fast. He directed me to the front desk where I asked for a little form to place a request and the next employee said, "for Stranahan's?" Then a customer came up with a bottle of Stranahan's Colorado Whiskey. The manager and customer started raving about it. I asked them what it compares to, and they couldn't tell me. The manager said, "it tastes like something that costs way, way more." I said, "it already costs $60." He just said, "I know" and then said you can't believe how good it is. The customer said he'd open it and let me try if he could.

So I figured what the heck, I got a gift card, I'll try some. They were right. I've never had 250 y/o whiskey, but I'd imagine it tastes something like this stuff. Toward the middle of my glass I swear there was no burn on the tongue whatsoever. And it doesn't compare with anything (the first sip tasted completely foreign). It just tastes... clean and fresh. It's amazing. Apparently, there's much more to making smooth whiskey than age.

IMO this stuff is like the next level. Nothing I've had is anything close to as good.

Friday, January 30, 2009

Silverlight the Right Choice for Web Applications

I've written about how I think that web applications are a hack. In that post I didn't think Silverlight was fully the answer, but at this point I think it's the best alternative.

I couldn't be more strongly in favor of using Silverlight for web applications (as opposed to web sites which only provide content) wherever possible. Of course even content can be a good use of Silverlight as the NYT reader shows. If an MS shop's target audience doesn't include IT goons who somehow put Silverlight in a different category than Acrobat Reader, I don't think you have any other choice for new applications.

My coworkers are spending many hours trying to make one AJAX codebase work on different browsers. After awhile it just gets demoralizing (and my employer is wasting tons of maintenance time). My mind is boggled by all the hackage necessary to make AJAX popup "windows" drag/drop via HTML (sure Javascript makes it happen, but it's still just HTML). With Silverlight you're working on a platform designed for this kind of thing. It's designed for dragging and dropping and animations and all the other things needed for applications (not to mention streaming).

Yes, you can make all those things happen with Javascript, and I fully understand the power of functional languages, but who would rather maintain a line of business application written in Javascript in favor of C#? Not to mention the aforementioned ever growing browser compatibility issues.

How do loosely typed Javascript objects compare with full featured Silverlight controls with UI and logic contained in one distinct entity combined with design time configuration? Not very well on the maintainability scale.

That's how I cast my vote, anyway. Long live Silverlight. I doubt it will be a technology that fades away, but I hope it's user base grows and it continues to stay healthy in the future.

SSIS Gotchas

SQL Server Integration Services is very finicky (read “gives indecipherable errors and will make your life miserable”). I wrote some packages using VS2008 / SQL Server 2008. Following is a list of gotchas I ran across. Note that there’s no frigging way you can figure any of these out from the error messages alone:
  • Restoring a database renders connections in packages unusable (even though everything looks good and testing the connection is fine). Thus, every time you restore a database that a package is pointing to (which I was doing repeatedly during testing), you must go into the package, add a new connection from a data source (by right clicking in the connection manager area of each package) and add in the same source that’s already being used (giving it a different name). Then you must go into each task item in the package and point to the new connection. If that task had mappings (as from a flat file to a table), you must re-map everything. Enjoy!
  • If you’re executing a package from .Net code, only the .Net connection types will run.
    • There are 3 different forms of essentially the same connection type: “Native OLE DB”, “.Net providers for OleDb”, and “.Net Providers”
    • The “.Net Providers” didn’t seem to work at all; the “Native OLE DB” providers would not execute when attempting to run the packages from a web page on a different server; both the “Native OLE DB” and “.Net providers for OleDb” worked from my local machine.
    • It seems the proper choice for calling packages from code is “.Net providers for OleDb”.
    • The “.Net providers for OleDb” will not execute from code directly from within visual studio (i.e., you can’t right click on the package and click execute) so you can't debug directly.
    • The .Net connections ignore their timeout property. They simply won't work when running a long query.
    • Enjoy!
  • You can't include a package that deals with a table that doesn't yet exist.
    • So you can’t have a package that refers to a table that hasn’t yet been created. The package will give errors all over the place. So, for instance (IIRC), you can’t deploy a package that refers to a table that will be created by another package b/c it won’t validate on deployment.
  • Deployment of packages is a nightmare. There are myriad issues surrounding the demonic property of packages, “ProtectionLevel”.
    • Under most circumstances, you would want to make your connection string properties configurable at the server. That way, you can move the packages around or point them to different servers w/o having to redeploy them. In my case, I had no end of problems with this (and the packages need only run annually) so I just deployed and ran this way:
      • For deployment, I would just do file | Save copy of [package] as and copy the package to the server running integration services. This would just embed the connection settings and not rely on any package configuration files. Then, for executing the packages from code, I would just refer to the server I copied the packages to.
    • At one point, I had a package that had a bad path in one of its connection’s properties. This seemed to prevent all packages from working.
    • Most destructive: Changing the connections and re-deploying (as described above) doesn't overwrite the connections. So I changed the connections from production back to QA, verified everything multiple times, redeployed (which prompts for confirmation whether I want to overwrite the existing packages), and ran a package. Much to my surprise & chagrin, the package executed against production! In this case, the existing packages must be deleted. In Reporting Services, there is some “overwrite data sources” property, but I couldn’t find this in SSIS.
  • Another disturbing issue: packages run where they’re called from. If, in code, you execute some SQL against a particular server, that SQL will execute on the server. Confusingly (b/c I was importing and exporting data), when you execute a package, it executes on the machine where it was called, not on the server where the package exists!
  • Warnings on package objects show up all over the place. There's no figuring out why they don't consistently work. E.G., login failure when there is nothing wrong with the connection. I think setting the property “BypassPrepare” to true helped.
  • Unlike the predecessor technology, DTS, there is no way to switch a connection vis-a-vis data flow objects w/o losing mappings. Enjoy!
  • Names of all new packages are Package1 – you must rename them and delete file Package1 manually.
  • If you have an extra line break at the end of a script task, it crashes. You can copy/paste that script into management studio and it will work fine.
  • Closing / reopening the project sometimes causes connection references from tasks to be lost.
  • Yet another disturbing issue: I was not able to get it down to a reproducible set of steps, but the project was reverting my script changes. I found that I had to check my script changes into sourcesafe immediately after saving every time (I suspect that the reverting took place somewhere in deploying).
The fun part is that you can’t compare versions of packages in sourcesafe b/c a simple change to a script task changes all sorts of stuff in the dtsx file.

Thursday, January 08, 2009

Best way to get record count using ADO.Net?

I think this may apply to the entity framework, but I'm not sure.

I was modifying a stored procedure which needed to run a query and also return the row count of that query. So the same (fairly long) query was pasted again using select count instead of select by which it set the value of an out parameter. I didn't like this for a few reasons:
  • Dual maintenance of the query.
  • The stored procedure is twice as long.
  • Inefficiency of calling the query twice. The count is already known by SqlServer after running the first query.
  • Having another parameter to configure and hard-code the name of in the calling code.
At first I was thinking that the count of any query is explicitly returned to any ADO call (unless the stored procedure includes "set nocount on"). The count I'm thinking of is the count of records affected that shows in SqlServer's management studio after running any query (N row(s) affected). Rows affected in this case includes rows merely selected.
So I looked at the properties of the SqlDataReader. It has a "RecordsAffected" property. Sounds a lot like "rows affected" no? Well, in order to get that count you must iterate through all the rows in the reader and close it. Obviously, not what I'm looking for.

Next I looked at the SqlCommand object. When you call ExecuteNonQuery, you get records affected! Cool, must be what I'm looking for. Nope - when this object says "records affected" it means just that (unlike what SqlServer means by "rows affected"). It will only return the count of rows updated, deleted, inserted.

So what seems to be the best way is to include a secondary recordset in the stored procedure: "select @@rowcount." My coworker verified that this count is connection specific (you don't get someone else's count). It's the count that management studio displays.
Accessing this count from a reader goes like yay:

_reader.NextResult();
_reader.Read();
_reader[0];


The call to NextResult advances the reader to the next result set (the set that only includes the result of "select @@rowcount"). We know that result set contains only the count so we don't need any explicit column names.