Don't forget about Parameterized SQL

It's common to see .NET developers and SQL Server DBAs arguing over the merits of stored procedures versus inline (ad hoc) SQL. It's un­for­tu­nate that these folks are so polarised since there is a solution that meets somewhere in the middle. It's called pa­ra­me­terised SQL and it's similar to inline SQL, except that it's based on templates. You ef­fec­tive­ly have the SQL that exists in a stored procedure, and you specify input/output parameters in the same way as you do for stored procedures. This SQL is then placed in the data access layer of your ap­pli­ca­tion.

From what I understand, Microsoft are using this for DLinq and have dropped their rec­om­men­da­tion on the use of stored procedure. I'm all in favour of this method since it makes upgrading ap­pli­ca­tions so much simpler, and reduces your dependency on the DBA whilst main­tain­ing a level of protection from SQL injection attacks. There is the point about setting security on individual stored procedures - but how many people really do that? Even when they do they often leave themselves open to other attack vectors.

Tagged with performance, security and sql.

Are data grid controls good for end users?

Rod Paddock recently posted on his ex­pe­ri­ences with WPF. Something that jumped out at me was his criticism of Microsoft for omitting a Datagrid control, although a basic grid control is included. The lack of a tra­di­tion­al Datagrid doesn't bother me too much, but I can see how it might impact a lot of developers. WPF is at least six months from release so it's likely a third-party will fill this gap, if Microsoft aren't pressured into writing one.

In case you don't know, the data grid is a UI control for Web and Windows de­vel­op­ment included with every release of .NET so far. It's a staple for UI control vendors and has been replicated in many other de­vel­op­ment en­vi­ron­ments. At a base level it displays data in a tabular format. However most in­cor­po­rate in-place editing and control hosting features.

Perhaps the lack of this control in WPF is a blessing in disguise since developers might stop to think for once. From my experience the data grid control is:

1. Often misused by developers
2. Too heavy­weight on the client
3. Complex for the developer to implement and maintain

As an aside, I'm a big fan of FogBugz and it includes a grid UI for listing cases. However, it also includes a list view, which must be popular with some end users for FogCreek to include this func­tion­al­i­ty.

Onto point two which is only valid when you really care about your users (most line of business developers need not apply!). Grids are heavy on the client if thought is not put into their operation in production. I'm all for avoiding premature op­ti­mi­sa­tion, but avoiding op­ti­mi­sa­tion altogether is something else entirely. Costs for grids on Windows include the memory for the control and data. On the Web you often produce a lot of HTML tables and other junk which has to be delivered to the browser. If ASP.NET is used there is also likely to be su­per­flu­ous viewstate in­for­ma­tion. As you cram more features in you are penalised for each one unless you can se­lec­tive­ly control their use.

My final point, number three, relates to the complexity that you heap upon yourself as you strive to make your ap­pli­ca­tion more complex for end users. Why try to combine editing, deletion and creation of records into one screen? It's often a false economy on behalf of the developer that all of this can be rolled into a single ap­pli­ca­tion form. If I had a penny for the number of a reasonable developer struggles with getting access to the value cell in an ASP.NET Datagrid, I would be very rich.

Getting back to WPF and Vista, I think that developers like Rod need to start looking at how they visualise in­for­ma­tion. Until now, .NET pro­gram­ming has been about ramping up pro­duc­tiv­i­ty on the parts of an ap­pli­ca­tion a user shouldn't experience directly. With WPF it's about how the user interacts with the ap­pli­ca­tion, and this requires the attention to user experience that Web ap­pli­ca­tions have had for years. If the current generation of developers don't wise up and appreciate the im­pli­ca­tions of this technology they will deservedly end up on the scrap heap.

Tagged with datagrid, usability and wpf.

Support proxy servers in your applications

Much of the software I use on a day-to-day basis requires a HTTP connection to the Internet. Un­for­tu­nate­ly, not all of this software includes reliable Web proxy support for Windows Au­then­ti­ca­tion (NTLM). Whilst many people are connecting to the Internet from networks without proxy servers, I'm often connecting from corporate networks through Microsoft ISA Server.

Here is some advice for anyone writing software that uses that needs uses the Internet:

• Include proxy support in your ap­pli­ca­tion. You'll not believe how many ap­pli­ca­tions get un-installed because they don't support proxy servers.
• Ensure that your proxy supports auto-con­fig­u­ra­tion (.pac) files. If you don't go this far make it clear how the proxy host name should be specified, whether to include "http://" at the beginning and what port number to use.
• Provide support for various au­then­ti­ca­tion mechanisms. Many corporate networks use NTLM au­then­ti­ca­tion. If your ap­pli­ca­tion runs on the Microsoft CLR you have support for this au­then­ti­ca­tion with the Cre­den­tial­Cache class. Native ap­pli­ca­tions can use the support available in WinInet or the more recent WinHttp. The latter includes a proxy con­fig­u­ra­tion tool to make life a little easier.
• Respect user cre­den­tials. If a user has to explicitly provide their NT logon cre­den­tials to your ap­pli­ca­tion make sure to store them securely.
• When requests fail provide useful error messages and server names to the user. This will help them figure out how to make con­nec­tions work. A lot of times setup is a process of trial and error for users who aren't provided in­for­ma­tion by network ad­min­is­tra­tors.

Tagged with authentication, ntlm and webproxy.

Data Access Pain

One of the things that I find most frus­trat­ing about on .NET projects is working with relational data sources. My experience with DataSets in the 1.x days was far from positive. They proved too in­ef­fi­cient and difficult to debug. This has changed in 2.0 with the many im­prove­ments to the API and the in­tro­duc­tion of vi­su­al­iz­ers to the integrated debugger. I'm still not sold on this solution, but at least things are improving ;)

My preference has been to develop a layer of custom objects which get called from the upper layers of the ap­pli­ca­tion. This is very flexible and easy to debug. In addition, you can create these objects without having any back end developed so that pro­to­typ­ing is simpler. To be fair this can be a bit time consuming, and I have tried to augment this with code generation using CodeSmith. Working this way lets me deal with objects in a fashion native to the .NET platform, take advantage to in­tel­liense and simplify unit testing.

I'm looking at two other solutions - LLBLGen Pro and NHibernate. LLBLGen seems to be better suited to my needs at present since it has a better user experience. Both of these tools map generated objects to the tables in the database, so you can avoid switching back and forth between pro­gram­ming models. Complex queries are expressed using custom syntax and this is where the story sours for NHibernate and LLBGen to a lesser extent. LLBGen makes it simple to wrap existing stored procedures so this is po­ten­tial­ly useful when the the SQL gets complex. Ideally I'd like to rid myself of the relational model and SQL altogether but I guess we're going to have to live with it forever.

On this topic it's worth reading a paper by Ted Neward on the object-relational divide and various tech­nolo­gies that have been developed to bridge it. The paper was for MSDN so it covers the LINQ technology that will likely be part of C# 3.0.

Tagged with databases, llblgen and nhibernate.