Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

Once your application is connected to SQL Server, you should be ready to start making intelligent use of the server. This means asking intelligent questions that return intelligent answers. Remember that SQL Server does not make a particularly good "file" server, although too many developers treat it as such. Database table data belongs on the servernot in client memory, where the data gets stale faster than cheap Swiss cheese drawing flies on the kitchen counter. Unfortunately, too many examples are dumbed down and written to "simplify" the process of making queriesyou and I often see "SELECT * FROM Customers" as the example query. Too many drag-and-drop demonstrations show how easy it is to take a data table or two and generate a strongly typed class (with relationships) in a matter of seconds. That makes a cool demo, but unless you take the extra steps to parameterize these queries, you'll end up with an unscalable application that's slower than a Volkswagen bug towing a 65-foot boat trailer up Mt. Rainer.

As I lead you through this chapter, I'll point out security risksdark alleys and tough neighborhoods that can ravage your data and batter your career. I'll also make it clear the routes that yield the best performance and productivity. As a result, your application should be faster, safer, and more scalable than your competition's.

In a nutshell, this chapter shows you the mechanics of building ADO.NET SqlCommand objects and a Parameters collection, and how to use these to execute ad-hoc queries, stored procedures, maintenance queries, or whatever you want to execute. Remember that SQL Server is tuned to run most efficiently when you execute stored procedures, so I'll spend quite a bit of time talking about the special issues you'll face when executing stored proceduresthings like multiple resultsets, OUTPUT parameters, and handling Parameter collections. I'll also talk about using the new SqlBulkCopy class to quickly move data from any data source to SQL Serverthat's because it makes more sense than using any SqlCommand approach.

ADO.NET Command objects play a crucial role when you choose to use the SqlDataAdapter or TableAdapter classes. In this chapter, I'll also discuss how to leverage these classes using Visual Studiogenerated code and custom hand-rolled code. I'll provide enough detail so you can decide which makes more sense for your development needs.