A while back I posted a review of Stephen Walther's ASP.NET 2.0 Unleashed. It's a fantastic book that I've used more times than I can count. The release of the .NET 3.5 framework means there are lots of new things to learn. Thankfully, Mr. Walther has released an amazing follow up-book titled ASP.NET 3.5 Unleashed.

At 1890 pages this book has just about everything you need to know to start building complex ASP.NET applications. While the book assumes that you have some familiarity with using ASP.NET the first few chapters are still devoted to the basics. I encourage everyone to read them, even the experts. There are many tips and tricks in the book so you may learn something new or pick up on something you'd long forgotten. Did you know the asp:Literal control has a build in Mode property that can be set to HTML encode it's content? I'd honestly forgotten about that and had been doing my encoding on the back-end.

This book provides an in-depth look at just about all of the core ASP.NET features building on many of the techniques we used in 2.0. For the new features specific to ASP.NET 3.5 , Walther devotes an entire chapter to the new ListView and DataPager controls. These controls can be thought of as a GridView or Repeater on steroids. There's also a chapter on data access with LINQ to SQL and a 3-chapter section devoted to working with AJAX.NET and the AJAX Control Toolkit.

There are many books out there that focus on the "how" but what I like most about Mr. Walther's books is that he devotes a great deal of time to the "why". For example, the book explains how to use the SqlDataSource control but then also explains why you'll want to avoid it for most complex applications and use the ObjectDatasourceControl instead. With this book you'll not only learn how to get things done, you'll learn how to get things done right. For that reason it's an invaluable resource for your library. Every ASP.NET developer should have this book on his/her shelf.

Note: While the code samples in the previous 1.1 and 2.0 Unleashed books were written in VB.NET, this new 3.5 book has them written in C#. Walther cites the fact that there are now more C# developers than there are VB.NET developers as the reason for the switch. I would've liked to have seen two different versions of the book but all code samples are also provided in VB on the included CD-ROM so everyone can easily follow along.

Permalink | Posted On March 31, 2008 Comments (0) | TrackBacks (0)

I'm building an ASP.NET application this weekend and ran into the following error.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

The error message itself is somewhat vague and it took me a little while to troubleshoot it so I thought I'd post my solution here. There are many reasons you could get this error, the most obvious being that you may have data in your database that contains a null value or that violates a foreign-key constraint.

I spent a while checking my database and everything seemed to be in order. There were no null values where there shouldn't be, my relationships were setup properly, and I'd double-checked that I wasn't enforcing foreign-key constraints.

After wracking my brain for a while, I started Googling and found some suggestions in a few different .NET forums which helped fix the problem. It turns out that during my data-importing I'd decided to change a field in one of my tables from VARCHAR(50) to VARCHAR(100) because some of the data was larger than I'd originally expected. However, I'd neglected to update the MaxLength property of the same field in my DataSet file. This disparity is what caused the problem. Updating the MaxLength property to 100 instantly fixed the issue.

So this error message can be somewhat misleading. If you find yourself faced with this error and you've checked all the things that the error message suggests and you're still having problems, check to see that the sizes of your database fields match the sizes in your dataset.

Permalink | Posted On March 8, 2008 Comments (0) | TrackBacks (0)

We were recently faced with a challenge at work where the client wanted to provide an Excel file of data which could be imported into SQL and used to update data within certain tables. While this is a snap via Enterprise Manager, it's trickier when the client wants it all to happen on the back-end so they can do it themselves via a web form.

While there are numerous tutorials for exporting data into Excel format, there didn't seem to be many on how to get the data in, so I'm posting this function in the hopes that it helps someone else.

I've stripped out most of the code so that this is JUST the part that imports the data from Excel. This example assumes that the.xls file is already on your server in the same location as this script. You can write your own upload functions to get it there. (If you need help with uploading, let me know and I'll post an article on that as well) This also assumes that you have a table in SQL that matches the schema of your spreadsheet.

This uses the new SqlBulkCopy class in ASP.NET 2.0 which lets you easily copy bulk amounts of data from one source to another.

NOTE: I left out any error handling functionality for the sake of brevity but it would be a good idea to use a Try/Catch block to handle any exceptions

Permalink | Posted On June 26, 2006 Comments (9) | TrackBacks (0)

I'm posting this from the Orlando International Airport which apparently has free wireless internet access. That's just fantastic. DevConnections is officially over. Here's my take on the last day.

Session 1 - Managing Memberships and Roles
One of the things ASP.NET 2.0 was designed for was to increase productivity and save developers time. To that end, Microsoft has added a suite of controls and components that allow for easily setting up login forms, creating users, managing permissions, etc. These components and methods take much of the work out of some of the more tedious tasks such as hashing password values, enforcing password creation rules, and locking users out after too many failed login attempts.

Since most of us will be using this with the SqlMembershipProvider for storing the data in SQL Server, it should be noted that by default, this is setup in Visual Studio to work with SQL 2005 Express. For use in a production environment with a full version of SQL, you'll need to prep the database first. There's a utility called aspnet_regsql.exe included with Visual Studio that will do just that. It can be run from the command line or there's a nice GUI if you run it in Windows.

The presenter, Bill Evjen, is also a member of INETA.org, which is an organization of Microsoft user groups that can be a great asset to continue learning.

Session 2 - ASP.NET 2.0 Essentials for building Professional Websites
This was a decent if somewhat repetitive session, which seemed to just recap some best practices from earlier sessions such as:

• Use master pages to control page templates.
• Create user controls to reduce complex and duplicated code
• Plan for localization when you build your site so it's easy to add in later if needed
• Always provide custom error messages to your users. Use the global.asax to create a custom error handler to log errors and/or alert administrators.
• Design and test a caching strategy to improve overall site efficiency.

Session 3 - The Atlas Application Framework
More good stuff on Microsoft's new AJAX-enabling framework for ASP.NET 2.0 by Dino Esposito. This session contained a lot more technical information that I don't have the space to recount here. I plan on trying out a lot of the Atlas demos in the coming weeks and will post some more in-depth info at that time. This is pretty bleeding-edge so I recommend that anyone who's interested simply download the Atlas framework and start playing with it.

Session 4 - Improving .NET Application Performance and Scalability
There is always a bottleneck. Find it. Remove it. Repeat.
That was the overall message of this last session of the conference. For any application that manages data and/or supports multiple users, tuning your application for performance should play an important role. Here are some good tips and tricks:

• Minimize round trips for data. Write stored procedures that retrieve multiple recordsets rather than running multiple queries
• Acquire late - release early - open your connection at the last possible moment and close it immediately after you're done. In most cases it's better to have multiple open/close statements than it is to open it once, do all your processing, and close it at the end. ALWAYS make sure to close your connections, SqlDataReaders, etc.
• Put the processing closer to the resources it needs - If you need to sort or filter data, do it in SQL instead of returning a large recordset and doing it in code
• Defrag and rebuild database indexes regularly
• Use the StringBuilder class for string concatenations within loops

Parting Thoughts
I am exhausted. I've crammed more new information into my brain in the past four days than I have in the past four months. Although very tired, I'm also extremely encouraged. With tight budgets and tighter deadlines, often the emphasis is on getting the project done and not necessarily on getting the project done right. Conferences like this provide a great venue for developers to learn in-depth details about the new technologies being offered and how to effectively utilize them. I have several projects coming up this summer that should be able to take advantage of things like Atlas, localization, fragment caching, and web parts. The knowledge I learned this week should help prevent a lot of late nights in the office and allow me to catch up on something I've been missing for a while... sleep.

Permalink | Posted On April 5, 2006 Comments (0) | TrackBacks (0)

I am not a morning person. I officially decided that these events start too early in the morning when I was stumbling around in the dark, half-asleep, tripped, fell into the wall, and nearly broke my nose. Yes, I am that awesome. Thankfully the rest of the day was not as awful as the beginning.

Session 1 - Accessing Data with the ASP.NET 2.0 Data Controls
This session was a decent introduction to the new data source controls in ASP.NET 2.0. Data source controls are non-UI helper controls that enable 2-way databinding with little or no code. I've already been using ASP.NET 2.0 for a couple months now so unfortunately there wasn't much new here. If you're new to ASP.NET you can view the slides from this session at www.deeptraining.com/litwin

One off-topic remark that speaker Paul Litwin made was about the new ways to encrypt your connection strings (and anything else you want) inside of the web.config file. This is a great habit to get into since it easily adds another layer of security to your application. Mr. Litwin has a great blog on the subject that will help.
Encrypting/Decrypting Web.config Sections

Session 2 - Localizing ASP.NET 2.0 Applications
Let's face it. Not everyone speaks English. Localization in ASP.NET 2.0 provides easy mechanisms for adding multi-language support to your projects. I have to say that I'm incredibly impressed by how easy this is. Using ASP.NET 2.0 localization allows you to maintain one core set of your website files and then use standard resource (.resx) files to implement however many languages you'd like. You can also setup a global resources folder for content that is repeated on multiple pages.

Most of the built in controls are localizable and there's even an asp:Localize control which can be used to wrap static content, making it localizable as well. Creating a method for the user to select a locale is simple and can be easily stored in a database for authenticated users or via a cookie using the Profile object for anonymous users. There's even a pretty easy way to add a custom caching parameter to vary by locale.

If you have any intention of creating a website in more than one language, the ASP.NET 2.0 localization features should not be overlooked.

Though not there at the time of this posting, speaker Dave Sussman has promised to post the slides and demos on his site at http://www.ipona.com/samples/

Session 3 - Maximizing ASP.NET Performance with Data Caching
ASP.NET 2.0 has made some major improvements to it's caching model which this session delved into. Caching can be set at the page level, selectively turned on and off for specific user controls, and the new data source controls have configurable parameters to apply caching to individual datasets. The web.config file now also supports caching profiles which can be configured and set for each page so different types of pages can have different caching durations and parameters.

Perhaps the most significant improvement though is the addition of sqlCacheDependency. Many sites pull content from databases, which is then cached. If the content in the database is updated, the page will still show the old data until the cache expires. SqlCacheDependency provides a mechanism for the database to notify the webserver of the change to automatically expire the cache. This can function both in SQL2000 and SQL2005, although 2005 enhances the process with what's called the Broker Service.
The code and slides for this session can be found at www.daveandal.com/download

Session 4 - SQL 2005 Indexing Strategies: Finding the Right Balance
I took a break from the ASP.NET 2.0 sessions and attended this one on SQL Server Indexing. I'm not a DBA but as a web developer I'm often stuck doing those tasks anyways. Indexing is a topic I'm planning to cover in more detail in a later blog and I gathered a lot of information here that will be helpful for that.

For now, here're a few quick notes from speaker Kimberly L. Tripp.
3 Things you need for doing database development:

1. Know your data - Knowing exactly what data you're storing helps you design for performance
2. Know your users - Knowing how people interact with the data helps you index for optimal performance
3. Know what really goes on - Users often lie or are uniformed about how things actually work so profiling the user requests and server responses will help you figure out an efficient strategy

4 Steps for creating indexes:

1. Create your clustered index
2. Create indexes on primary key (PK) and unique keys (UK)
3. Manually index foreign keys (FK) - This improves key relationship management and may improve join performance
4. Manually create your extra indexes for AND, OR, JOIN, and aggregate queries

Full-text indexing has been significantly improved in SQL Server 2005 and catalog indexes now build 400% faster. The Index Tuning Wizard (ITW) has also been replaced by the newer Database Tuning Advisor (DTA), which possesses many new algorithms to help SQL optimize and run more efficient queries.

Session 5 - Building N-Tier ASP.NET 2.0 Applications
About half of this session was interesting. The other half admittedly went over my head. I'm most definitely not an application architect but, like not being a DBA, this responsibility often falls on my head. N-Tier is "a software architecture methodology that defines distinct physical or process boundaries between application modules." When you hear people talk about separating the data-access layer from the business logic from the presentation layer, their talking about N-Tier architecture.

One of the main benefits of this is flexibility and ability to re-use code. If you build your app on SQL server and then need to port it to Oracle, you can do so very easily by only modifying your data-access layer. There'd be no need to actually change any of your .aspx pages.

This concept is still pretty new to me. I'm lucky if I have time to actually build the project. Who has time to create an architecture?

That's all for today. 4 more sessions tomorrow and then it's back to the office.

Permalink | Posted On April 4, 2006 Comments (1) | TrackBacks (0)

The morning started with keynotes touting many of the new and improved features of Visual Studio 2005. You can find some good demos, as well as a sneak-peak at the new ad campaign at www.400plusdifferences.com. After that it was off to the individual sessions...

Session 1 - Creating Dynamic Web Sites with ASP.NET 2.0 Web Parts
Web parts are a set of new controls in ASP.NET 2.0 that allow us to provide users with flexible display options and personalized content. The most common application of this interface would be for portal-type sites like MyYahoo and Google's personalized homepage but you can really leverage this technology for any kind of site.

The best part about web parts is that they work with existing ASP.NET controls and custom user controls with little or no effort. Just by dragging some controls onto the page you can quickly and easily give your users the ability to personalize the content. This could be a great feature for allowing your users to customize the homepage of any database-driven web application. By default the controls integrate seamlessly with Microsoft SQL server to persist data for authenticated users but that can also be modified to use any data store such as Oracle, MySQL, or XML.

Note: The drag and drop functionality to customize the display parts will only work in Internet Explorer at the moment but there are controls in the new Atlas framework that enable it for other browsers such as Firefox and Opera.

Session 2 - New Enhancements for Web Developers in Visual Studio 2005
This was an informative albeit sales-pitchy session about the improvements made to VS2005. My team has already been using VS2005 for a couple of months so a lot of the information I already had, but here's a quick breakdown of just a few of the cooler new features:

• Built in development server. No need to have IIS installed
• Better integration with SourceSafe for versioning and code control
• WYSIWYG DataSet designer
• Customizable tag formatting options
• Better Intellisense
• XHTML and 508/WCAG compliant code and built in validators

Another thing I should point out is that Microsoft has also released Visual Web Developer Express along with SQL Server 2005 Express. Both products are free to download and provide a great platform for developers to test-drive and learn ASP.NET 2.0 and the Visual Studio environment without having to plunk down hard-earned cash.

Lunch
I didn't plan on blogging about lunch but I had the pleasure of eating with Microsoft guru Joe Stagner who happens to be an exceptionally nice guy. We chatted a bit about Microsoft and general development stuff and he also pointed me to The Code Room, which is a "1/2 hour internet TV show that exposes technologists to the latest tools and technologies for tackling real-world software development issues". He wrote and produced an episode in Las Vegas last December that's worth checking out.

Session 3 - Using Visual Studio Team System to Build Enterprise ASP.NET Web Applications
Visual Studio Team System is a new version of VS that enables project management and collaboration functionality right within the developer environment. It also integrates directly with Excel, Project, and SharePoint to allow multiple developers and project managers to work together. While it seems useful for large teams working on an enterprise-sized project, I can't envision it being all that helpful for smaller web apps. They system is divided into parts for system architects, developers, and testers. Most of the developers I know (myself included) usually get stuck doing all three so this almost seems more bother than what it's worth.

One part I did find especially interesting however, was the testing features available for web applications. You're able to simulate any number of incrementally increasing users using multiple browsers to effectively stress test your applications before deployment.

Session 4 - An In-Depth Look at Building Enhanced Web Apps Using Atlas
By far the biggest draw so far, the Atlas session was packed with people standing and sitting in the aisles. For good reason it would appear. Atlas seems to be the most interesting surprise in Microsoft's bag of tricks.

Atlas is a cross-browser JavaScript library and server-side assembly that provides AJAX functionality on top of ASP.NET 2.0. The most impressive part about it is that it enables AJAX-style partial-page rendering without a lot of work and without having to write any complex JavaScript. The coolest part for me was the ability to extend existing controls within the framework such as adding drag and drop functionality or auto-complete for textbox controls.

Session 5 - Cool Tips & Tricks in ASP.NET 2.0
The day concluded with a session by keynote speaker Scott Guthrie who is not only brilliant but also a great presenter. It was designed to provide 10 tips and tricks to help increase overall productivity and it was exceptionally beneficial. I won't bother to recap the tips and tricks Mr. Guthrie provided since he's made them available on his blog. I highly recommend downloading this session.
ASP.NET Connections Tips and Tricks Slides+Demos Posted

Permalink | Posted On April 3, 2006 Comments (0) | TrackBacks (0)

I spent the majority of my day at the Microsoft launch event for SQL Server 2005, Visual Studio 2005, and BizTalk Server 2006. Microsoft does events like this periodically across the country and they're often free (as this one was) or relatively inexpensive. They provide great insight into some of their new products and services and they often give away software, books, magazines, etc. At the very least it's free food and a day away from the office so I recommend them to anyone in the field.

This particular conference was pretty good, although I don't use BizTalk and I'll admit that some of the SQL stuff went over my head. I, like most of my readers, user SQL Server for small to medium web applications like content management systems, not things like massive enterprise-level transaction based banking systems.

I'd already been playing with Visual Studio 2005 and ASP.NET 2.0 so I was mainly interested in the SQL Server 2005 info. Some of the new features were pretty interesting so here's a few of my random notes on that topic:

• SQL 2005 now has an XML data type which you can use to store XML data. You can also store a schema for the data to validate against and use XQuery to query it within a SQL statement
• End user ad-hoc reporting is now built into the Reporting Services allowing users to create reports on the fly
• Improved error handling features have been added to T-SQL in the form of Try/Catch blocks
• SQL 2005 now has built in data encryption functions using both symmetric and asymmetric keys
• SQL-CLR allows you to write things like Stored Procedures, Functions, and Triggers in VB.NET or C#
• New LOB support with varchar(MAX) and varbinary(MAX) allowing you to store large amounts of data without using text and image fields.
• CacheSync - allows data from the database to be cached by IIS. When the data is updated, SQL Sever will automatically let IIS know to expire the cache.

One last thing, not SQL related, is that the new Team Foundation Server allows developers to work together and integrates new source code control functionality into Visual Studio as an alternative to SourceSafe. SourceSafe (even the new 2005 version) isn't exactly everyone's favorite application so let's hope this will be better.

That's it for now. I want to give a quick thank you to everyone who reads this blog for the positive feedback I've received so far. I love comments so keep posting them and/or send any ideas, stories, etc. to jay@codescene.com.

Permalink | Posted On January 11, 2006 Comments (3) | TrackBacks (0)

A cautionary tale about data recovery

Your giant database application is up and running. Hundreds of users from across the globe are managing the data within. Months go by. Productivity is up. Users are happy. They love you. Mission accomplished. Then you get the call...

"We're in the database and it looks like a couple weeks ago so-and-so accidentally deleted one of the key records in the database. We didn't notice it until today but now we need the information for a presentation in a few hours. How do we get it back?"

They no longer love you. You didn't cause this problem but you'd damned well better fix it... and fast.

It's for this reason that I don't much care for the SQL DELETE function. Running it will wipe the record in question making it difficult to recover. Sure, you can retrieve it from your backups (you do have a backup plan right?) but that's often tedious. You'll have to locate the backup file (possibly offline on a tape), restore it to a separate location on the server so you don't affect any of the data in the live database, retrieve the record and restore it.

As an alternative to flat out deleting data, I like to use a stored procedure that will transfer the record to a separate DELETED table within the database before removing it from the actual live table. This way the data is removed from the live application but easily retrieved when the client calls in a panic. This also has the added benefit of being able to mark the deleted record with a timestamp and the name of the user who deleted it.

Here's the stored procedure I use to do this:

Note that using this method has obvious performance and disk space implications. I don't advocate it's use for systems where lightning-fast response time is mission critical or for extremely large systems where this method could cripple the disk space. For most smaller intranet-style applications though, this procedure has worked very well for me. You can experiment with different procedures but the principle is the same: Users will delete things they shouldn't and you'll have to fix it. Do yourself a favor and put a plan into place before you get that phone call.

Permalink | Posted On January 4, 2006 Comments (3) | TrackBacks (0)

(or how to insert null values into SQL using ASP.NET)
I've built several online applications using Microsoft SQL Server as the backend database and one thing that's always given me trouble was inserting dates, or more specifically NOT inserting dates.
Users can enter a date or use a calendar control to select one and, assuming it's valid, it goes into the database with no problems. But what happens when the user leaves the date field blank? If SQL is expecting a date, and you pass it a null value instead, it ends up in the database as the SQL default date of 1/1/1900 when most often you just want it left blank.

So how do you insert a blank value into the database? Here's an easy trick using VB.NET (assuming txtDate is an asp:TextBox)

Permalink | Posted On January 1, 2006 Comments (0) | TrackBacks (0)