Pre-conference: Pragmatic Application Design

Monday Dec. 1st, 09:00 - 17:00
Location: Hilton Toronto
Room: Carmichael 
Cost: 399.00$ CDN

Assuming you’ve come to this course to learn about SQL Server, you can’t really get started building an intelligent application if you don’t understand how SQL Server works. This next session makes that abundantly clear—going into just enough detail to help you build successful, high-performance applications with the least amount of hardware. This session also provides an overview of the tools included with SQL Server to help make the process of configuring, tuning, administering and testing your application easier. (75 minutes—Summarizes Chapter 2)

• Which version of SQL Server makes sense for your application—today and a year from now?
• Installing, configuring and running SQL Server instances.
• Understanding what’s different about SQL Express installations.
• Understanding how the query processor works—what makes queries fast and efficient?
• Understanding SQL Server security and the tools to configure it.
• Exposing SQL Server to the network—and why this is not always a good idea.
• Using SQL Server Management Studio including the object explorer and query editor.
• Optimizing queries with SQL Server Management Studio tools.
• Understanding how Stored Procedures can help (and hurt) performance.
• Using the SQL Profiler to monitor and tune application queries.

Many developers are “encouraged” to leverage existing databases when applications are first created but you’ll also need to know how to create new databases and populate the schema. This session shows how to use Visual Studio as well as SQL Server Management Studio to do so. We’ll also make it clear when the Visual Studio or Visual Basic Express Editions fall short. One of the most pervasive design decisions made by developers, architects and DBAs that use SQL Server is the use of Stored Procedures to help manage their data access applications. The next session makes it clear why this approach continues to be taken by over 90% of the serious applications in use today. Using Visual Studio’s Server Explorer we’ll see how to create, manage and debug Stored Procedures. (60 minutes–Summarizes Chapter 4 and 5.)

• Creating new databases, populating schema, setting rights and importing data.
• Creating and exploring databases via Data Connections.
• Using the Query Designer to build, test and tune queries.
• Creating, tuning and using step-through debugging with Stored Procedures and functions.

Visual Studio provides the developer with a number of code generators that try to make the process of accessing data easy. We’ll see how these IDE-based generators work and when they fail to complete the job. We’ll also see what happened to the last generation of code generators and the ones before that. (60 minutes—Summarizes Chapter 6.)

• Understanding the disconnected data model.
• Leveraging the DataAdapter and other code-generating wizards.
• Implementing strongly typed data classes using Visual Studio.
• Understanding the role of the Data Source, DataSet, DataTable, DataAdapter and TableAdapter.
• Using the Visual Studio IDE, wizards and designers to build and tune these classes.
• Building CRUD operations based on stored procedures.
• Building a simple application using drag-and-drop.
• Building a stored-procedure-based hierarchical application using the TableAdapter.

In a given month, there seems to be a never-ending flow of questions on getting connected to SQL Server. Given the newer, tighter security constraints I can understand why. This session focuses on the SqlConnection class and how to get it connected to SQL Server. None of the operations we’ve discussed so far can be accomplished without a working connection. It also spends considerable time on the Connection Pooling mechanism which seems to bring out a number of design and coding flaws. We’ll see how to eliminate both. (60 minutes—Summarizes Chapter 9.)

• Connectivity strategies—connect once or just in time?
• Understanding Multiple Active Resultsets and where (or if) it makes sense.
• Coding the ConnectionString and using the SqlConnectionStringBuilder class.
• Opening and closing connections—correctly.
• Understanding, tuning and monitoring the connection pool.
• Managing the SQL Server service instances in code.

Sometimes the tasks you assign to a TSQL executable are too much for SQL Server to handle on its own—especially considering that TSLQ is a query language that’s not really designed to perform sophisticated string or array manipulation or complex math calculations. This is where SQL Server CLR executables can be used to improve code and developer performance. This session walks through the process of deciding where CLR executables make sense and where they don’t. We’ll step through the process of building, testing and scripting CLR stored procedures, functions, user-defined types and aggregates. Along the way we’ll leverage what we’ve learned about tuning TSQL code to evaluate the performance of each technique. (75 minutes—Summarizes Chapter 13.)

• Where do CLR executables make sense?
• Choosing the right problem to solve.
• Building stored procedures and functions.
• Handling input and output parameters.
• Setting up a test environment using Visual Studio and SQL Server Management Studio.
• Executing CLR executables from TSQL and ADO.NET.
• Implementing a CLR User-defined type.
• Understanding CLR executable security.

Most of our applications create reports one way or another. Since the inception of SQL Server Reporting Services developers have had a new (and far easier) way to visualize the data stored in the database. This session shows how to setup and manage a suite of Reporting Services reports and invoke them from your Visual Studio-coded applications using the Report Viewer control. (60 minutes—Summarizes Chapter 14.)

• Understanding how Reporting Services works.
• Creating RDL reports with the Visual Studio Business Intelligence wizards.
• Managing Reporting Services reports with the Report Designer and SharePoint Services.
• Managing report security.
• Creating RDLC reports with the Visual Studio Report designer.
• Using the Report Viewer control to launch Reporting Services and locally hosted reports.
• Adding code to manage report and query parameters.

Agenda:
8:30 AM – 9:00 AM
Welcome and Registration

9:00 AM – 12:00 AM

• Exploring Application and Data Access Architectures
• How Does SQL Server Work?
• Leveraging the Visual Studio and SQL Server Data Explorers
• Building Data Sources, DataSet and TableAdapters

• Getting Connected
• Managing SQL Server CLR Executables
• Creating and Managing RDL-based Reports Using Visual Studio

Every attendees will get a free book from William R. Vaughn (Hitchiker's Guide to Visual Studio and SQL Server).

Monday Dec. 1st, 2008, 09:00 - 17:00
Location: Hilton Toronto
Room: Jackson
Cost: 399.00$ CDN

Attendees can bring their own computers to this workshop and work through samples that demonstrate the use of new .NET 3.0/3.5 features. You will need the following software loaded on your computer:

• .NET Framework 3.0
• Visual Studio 2005
• Visual Studio 2005 Extensions for the .NET Framework 3.0
• .NET Framework 3.5 – Beta 2 (optional)
• Visual Studio 2008 – Beta 2(optional)

Agenda:
8:30 AM – 9:00 AM
Welcome and Registration
9:00 AM – 12:00 AM
Introduction to WPF,WCF,WF,WCS
12:00 PM – 1:00 PM
Lunch (Not included)
1:00 PM – 5:00 PM
Sample Code and example with .NET 3.0

Monday Dec. 1st, 2008, 09:00 - 17:00
Location: Hilton Toronto
Room: Casson
  Cost: 399.00$ CDN

• Introducing SQLCLR via UDFs
  Creating your first SQLCLR method
  CREATE ASSEMBLY and related statements
  Intro to permission sets
  SqlTypes library
  SqlFunction attribute
  Determinism, precision, and system data access
  Dealing with Visual Studio deployment issues
  IEnumerable and table-valued UDFs
  Using UDFs to enhance performance

• SQLCLR Stored Procedures
  Stored procedures vs. functions
  Introduction to in-proc data access model
  The context connection
  SqlContext class
  SqlPipe class
  Exceptions and exception handling
  When does it make sense to use a CLR stored procedure?
  When should a CLR UDF be used instead?
  Performance examples

• Security, reliability, and design considerations
  What do the permission sets REALLY mean?
  HostProtection
  Code Access Security
  Database trustworthiness
  Strong named assemblies
  Designing for least privilege
  Designing for reuse
  Creating robust utility classes

• User-defined types
  Why complex types?
  SqlUserDefinedType attribute
  INullable interface
  Serialization and ordering
  Static and instance methods
  Data validation and domain rules checking
  The Parse method
  Eliminating the Parse method: Factory type patterns and strongly-typed methods
  Design considerations
  Use cases for types and static methods
  Helping to bridge the app/database gap

• User-defined aggregates
  Where do UDAs fit and what problems do they solve?
  What are their limitations?
  SqlUserDefinedAggregate attribute
  The various methods that make up a UDA
  How to hack the UDA: Getting around the 8000-byte limitation
  Solving real business problems using UDAs

• Troubleshooting and ongoing maintenance
  Deployment concerns
  The SQLCLR management UDAs
  ALTER ASSEMBLY
  Assembly versioning
  Making changes to method and class signatures (without breaking everything)