Streaming from Developer Academy 3!

17 בדצמבר 2008

תגיות: ,
אין תגובות

Hi everyone,

Developer Academy 3 was a great success. The convention center was packed … and all my demos worked 🙂

You can now view all the lectures online on the DevAcademy3 website.

If you scroll down to DEV305 you can view my presentation, “Leverage SQL Server 2008 in your .Net Code with Visual Studio 2008 SP1”. You can also download the code for the demos.

In the fourth demo you will see how FILESTREAM allowed me to stream high quality HD videos directly from the server.

Following is a description of all the demos.

If you have any questions or problems with the demos, please post your comments and I will take a look.



In my session in Developer Academy 3 I demonstrated four new features of SQL Server 2008 that you can easily leverage in your .Net code with Visual Studio 2008 SP1.

To remind you, these features are:

  1. Table Valued Parameters (TVP)

  2. The new MERGE SQL command

  3. Change Tracking


When you open the zip you will find two folders: “Session Demos” and “Performance Tests”.

“Session Demos” contains the demos that I presented in the session. “Performance Tests” are the two projects that I discussed earlier here and here.

In order to run these projects, do make sure you have a running instance of SQL Server 2008 which you will find here and that you have installed Visual Studio 2008 SP1 which you will find here.

Session Demos

Under “Session Demos” you will find two folders: “SQL Server Management Studio” and “Visual Studio 2008”. “SQL Server Management Studio” contains the SSMS projects for the demos and “Visual Studio 2008” contains the corresponding Visual Studio solutions.

  1. First, open “Demos.ssmssln” from the “SQL Server Management Studio” folder with SQL Server Management Studio. In Solution Explorer this is what you will see.


  2. Now open “Demos.sln” from the “Visual Studio 2008” folder with Visual Studio 2008. In Solution Explorer this is what you will see.


  3. Note that in both environments we have solution folders corresponding to two demo environments: “Schedule” and “Album”. The “Schedule” demo environment demonstrates the Table Valued Parameters, Merge and Change Tracking features. The “Album” demo environment demonstrates FILESTREAM.

  4. Run the Setup.sql script in the Schedule folder under SSMS. This creates the DevAcademy database with one table, ‘Courses’, representing the presentation schedule in the Tavor hall where I presented.

Starting Point

  1. Compile the “1. DataAdapter” project in Visual Studio 2008. This project creates a simple WPF application binding a strongly typed dataset to a DataGrid.
    I am using the DataGrid provided in the latest release of the WPF control kit. If you want, you can download the control kit with source code from here, but you do not have to as I have included the required dll in this package.

  2. Check and, if necessary, modify the connection strings to point to the SQL Server 2008 instance you are working with.

  3. Run the application. You can make changes to the data in the grid and use the Update and Fill buttons to send and retrieve data to and from the database respectively.

  4. Browse the Schedule.xaml.cs file and you will see how I use the strongly typed data adapter to perform these tasks.

Table Valued Parameters

  1. In SSMS run the “Setup TVP and Merge.sql” to define the CourseTableType and the stored procedures that use it.

  2. Browse the script and note how the stored procedures InsertCourses, DeleteCourses, and UpdateCourses use their single table valued parameter in set operations as if it were a local table.

  3. In VS2008 compile the “2. Table Valued Parameters” project.

  4. Run the application. Edit the data in the grid and use the Update and Fill buttons to verify that the functionality has not changed.

  5. Now browse the Schedule.xaml.cs file in this project. The Fill button click event handler still uses the data adapter to retrieve data from the database. However, the Update button click event handler now uses an SQL Command with a Table Valued Parameter to invoke the InsertCourses, UpdateCourses and DeleteCourses stored procedures.

The MERGE command

  1. In SSMS browse the “Setup TVP and Merge.sql” again and note how the MERGE command is used in the MergeCourses stored procedure.

  2. In VS2008 compile the “3. Merge” project.

  3. Run the application. Edit the data in the grid and use the Update and Fill buttons to verify that the functionality has not changed.

  4. Browse the Schedule.xaml.cs file in this project. Note that the Fill button click event handler still uses the data adapter to retrieve data from the database. However, the Update button click event handler now uses an SQL Command with a Table Valued Parameter to invoke the MergeCourses stored procedure.

  5. Note also that the table passed to the MergeCourses stored procedure is defined as follows:


As you can see we are passing unchanged rows to the MergeCourses stored procedure. This corresponds to the ‘when not matched’ clause in the stored procedure:


As an alternative to this approach you can delete the highlighted code in the .Net code and the highlighted clause in the MergeCourses definition. Then, you might use the DeleteCourses stored procedure as in the “2. Table Valued Parameters” project to perform deletes. This alternative approach would be more efficient if there are many unchanged rows in the table.

Change Tracking

In VS compile and run the “4. Change Tracking” project. This project uses Synchronized Services with Change Tracking. I advise you to read about Local Database Cache here before studying this demo.

You can create the demo for yourself as follows:

  1. Copy the original “1. DataAdapter” project and rename it as “4. Change Tracking”.

  2. Delete the DevAcademyDataSet.xsd. (The project won’t compile now).

  3. Add a new item of type “Local Database Cache” named LocalScheduleCache.sync. (You can find this item under the Data category) and complete the wizard as follows:


    In my session I created this application twice: first, without checking the “Use SQL Server change tracking” check box, and a second time, checking it. This check box only appears in Visual Studio 2008 SP1 and when the remote server is SQL Server 2008. Checking the checkbox does not affect the next steps at all, nor does it change the functionality of the application, but, as I noted during the session, it makes a big difference on the database server.
    Without change tracking, Sync Services for ADO.NET changes the schema of the DevAcademy database, adding two columns to the Courses table and an additional table called Courses_Tombstone. These are required to enable correct management of changes in the data. However, with “Use SQL Server change tracking” enabled, no schema changes are required.

  4. After completing the wizard you will find some new items in the project:

    1. A Local Database Cache item named LocalScheduleCache.sync.

    2. SQL scripts to undo and redo the changes in the database.

    3. A new local database (SQL Compact) named ‘DevAcademy.sdf’.

    Note the difference between the contents of those SQL scripts when change tracking is enabled and when it is not. When not enabled the scripts remove columns and tables that were added by the wizard. When enabled the scripts only disable change tracking at the database and table levels.

  5. Next, add a new Data Source of type Database (Data -> Add New Data Source). This time select the new connection string called “ClientDevAcademyConnectionString” that represents the local database. We are now binding the client application to the local database and not to the database on SQL Server 2008.

  6. As in the original “1. DataAdapter” project, the Fill and Update button click event handlers use the data adapter to send data to update and retrieve data from the database. However, as we are using the dataset from the previous step, we are now accessing the local database and not the one on the SQL Server.

  7. Now to add Sync Services for ADO.NET. Add the Sync method that you can find in the Schedule.xaml.cs file of the downloaded project. Add also the invocations of the Sync method as I did in the button event handlers (and in the constructor). In this (rather contrived) scenario we are downloading updates from the remote database to the local database immediately before filling the dataset from the local database. Similarly, we are uploading updates from the local database to the remote database immediately after updating the local database from the dataset. This demonstrates how you can implement Sync Services for ADO.NET in your application. In a real world scenario you would not apply the Sync method in this way, however the example does demonstrate how you can generate the code you need for that. Synchronization will usually be performed “occasionally”, that is, when a connection is available and not every time the GUI updates or needs to be updated locally.


Now let’s run through the FILESTREAM demo. We will see how FILESTREAM enables us to HD video directly from the database!

The FILESTREAM demo was inspired by this sample at Codeplex, but I made a few changes to simplify the demo. In particular, I made use of an .ashx HttpHandler to stream the video, instead of streaming from an .asmx page and I used Windows Media Player instead of the WPF MediaElement control.

  1. In SSMS, delete the DevAcademy database. We will rebuild it in the next step.

  2. In SSMS, open the solution folder “Album” and run the “Setup Filestream.sql” script. Note how enabling FILESTREAM needs to be enabled at three levels.

    1. The “filestream access level” configuration of the server must be ‘2’

    2. The database must have a secondary filegroup that is declared as containing a FILESTREAM.

    3. A table with a FILESTREAM column must have a ROWGUIDCOL column.

  3. In VS2008 Open the “Album” Solution folder. You will find three projects here.

    • AlbumDataAccess
      AlbumDataAccess is a class library that creates an SqlFileStream object to represent a FILESTREAM blob in a row in the database.

    • AlbumUploader
      AlbumUploader is a console application that copies files from a specified folder to a blob in the database. It also calculates the bitrate at which the data is uploaded.

    • AlbumStreamer
      AlbumStream contains an HttpHandler that simply reads a blob and writes it to the http response stream.

  4. Rather than uploading the video files to the website, I invite you to download some excellent HD footage for yourselves from here.

  5. Compile the AlbumDataAccess application.

  6. Compile and run the AlbumUploader. When your uploads are complete, open Windows Media Player and open a url such as this: http://localhost:55555/AlbumStreamer/Handler.ashx?title=<name of file>

Voila ! You are streaming video

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *