Episode 252

Chris Woodruff on Data Experience

Last week, I demonstrated how to embed code directly into a SQL Server Reporting Service (SSRS) report.

In this article, I will explain how to reference code in an external assembly from an SSRS report. The basic steps are

1. Create External Code
2. Create Unit Tests
3. Deploy the assembly to the Report Server
4. Add a reference to the assembly
5. Call external functions in Expression Editor
6. Deploy Report

Create External Code

The first step is to create and compile the external code. The project type will be a Class Library and you will add a public class with a public static method. This code can be in C#, Visual Basic, or F#.
A sample is shown below in Listing 1.

          using System; namespace ReportFunctions
{ publicclass ReportLib { publicstaticstring FormatAs2Digits(decimal?
input) { if (input == null) return"N/A"; elsereturn String.Format("{0:##,##0.00;(##,##0.00)}",
input); } } }

Compile this code in Release mode

Create Unit Tests

It's a good idea to create unit tests around this code because it can be difficult to test it on the Report Server.
At a minimum, write tests that mimic how you expect to call the function within your reports.

Deploy Assembly to Report Server

In order to use the functions, you must deploy the compiled DLL to the report server. You can either create a Setup  project to create an MSI package or you can simply copy the DLL to the drive where SQL Server Reporting Services is installed in the following folder on the SQL Server installation drive:

\Program Files\Microsoft SQL Server\Instance_Name\Reporting Services\ReportServer\bin

where Instance_Name is the name of the instance of SQL Server on which SSRS is running.

Add a reference to the assembly

Open your Report project and open the report that will call the custom function. From the menu, select Report | Report Properties. Select the References tab (Fig. 1).

Fig. 1 – “Reference” tab of Report Properties

Browse to select the deployed assembly containing the code you want to call.

After adding the reference, you will need to compile the Report project before you  can use the assembly functions. To compile the report, select Buld | Build Solution from the menu.

Call external functions in Expression Editor

Open an expression editor and call a function in the external assembly. You will need to include the entire namespace and classname. In our example, this be

An example is shown in Fig. 2.

Fig. 2 – Expression Editor

You can test that the expression works by clicking the Preview tab of the report.

Deploy Report

The final step is to deploy the report. Assuming you have permissions on the Report Server and the report sever is set in the project properties, the easiest way to deploy is to right-click the report in the Solution Explorer and select Deploy.

Now you can test the report and the function on the Report Server.

Conclusion

In this article, we described how to call code in an external assembly from a SQL Server Reporting Services report.

Sometimes I find myself applying the same formatting or performing the same task to many elements within a SQL Server Reporting Services (SSRS) report.

When this happens, I consider writing a reusable function to perform this task or formatting. Functions can be added directly to an SSRS report.

To add code to an SSRS report, open the report and select Report | Report Properties from the menu. The Report Properties dialog displays. Select the Code tab to bring this tab forward (Fig. 1).

Fig 1: The Code tab of the SSRS Report Properties dialogue

This tab contains a single edit box into which you can type Visual Basic code. The editor is very limited in that you can only use Visual Basic (no C#, F#, or JavaScript), and it provides no IntelliSense. Still, it does allow you to create functions that can be called elsewhere within your report. Type your function within this edit box.

Note: If, like me, you are having trouble writing valid code with the IntelliSense or other syntax-checking, It might be helpful to create a console application in Visual Studio and type the same function in order to validate the function's syntax and functionality.

In SSRS, you assign an expression to an object’s property using the Expression Editor (Fig. 2)

Fig2: The SSRS Expression Editor

In an Expression editor of any object on the report, you can call the function with the word "Code", followed by ".", followed by the name of the function.

For example, I found that I had a number of textboxes that displayed numeric data. I wanted every number to display with the following rules:

• Format the numeric output so exactly 2 digits appear to the right of the decimal point
• Print "N/A" for null values.

I could accomplish this by doing the following:

1. Set the formatting of every textbox to "##,##0.00;(##,##0.00)"
2. Change the expression in each textbox to something like:
   =Iif(IsNothing (Fields!Price), "N/A", Fields!Price)

But this is inefficient because one needs to perform the above steps for every textbox where this change is needed.

Instead, I created the following function and embedded it into the report:

          Public
          Shared
          Function FormatAs2Digits(ByVal input as Nullable(
of decimal)) asstringreturn Iif(IsNothing
(input), "N/A", Format(input, "##,##0.00;(##,##0.00)")) End Function

Then, I could set the expression of each textbox to something similar to the following

=Code.FormatAs2Digits(Fields!Price)

Be aware that your report runs under a specific user context and that user will likely have very limited access rights, so your functions will be limited in what that user context can do. Generally, I only use functions to format data and perform other tasks encapsulated within the report.

Despite its limitations, an embedded function is an excellent way to create reusable code and consume it in multiple objects within an SSRS report.

Here is a video of Jason Follas's Using Geospatial Data With SQL Azure presentation at the 2012 Detroit Day of Azure.

In .NET applications, Connection Strings are easily stored in and retrieved from a web.config file or an app.config file. Add a <connectionStrings> section to the config file, directly within the <configuration> section. Within the <connectionStrings> section, place an <add> tag for each connection string you wish to store and retrieve. This <add> tag contains two important attributes: name and connectionString. The name attribute will be used to look up an entry, so each <add> tag should have a unique name. The connectionString attribute contains the connection string you will use to connect to a database. If your application needs to connect to a number of different data types, it is worth setting the Provider property as well. This is the name of a database provider installed on the current machine, that will allow you to connect to a database.
An example is shown below:

<configuration>
  <connectionStrings>
    <add name="MyApp_Dev"

        connectionString="Data
Source=Server01;Initial Catalog=AwesomeDB_Dev;Integrated Security=True"/>
<add name="MyApp_QA"

        connectionString="Data
Source=Server01;Initial Catalog=AwesomeDB_QA;Integrated Security=True"/>
<add name="MyApp_Prod"

        connectionString="Data
Source=Server01;Initial Catalog=AwesomeDB;Integrated Security=True"/>
</connectionStrings> </configuration>

To retrieve a connection string by its associated name attribute, use the utilities found in the System.Configuration namespace of the System.Configuration assembly. First, set a reference to System.Assembly by selecting the menu option Project | Add Reference, selecting the System.Assembly in the Component Name column of the .NET tab of the Add Reference dialog; and, clicking the OK button. Then, add the following using statement to the top of your class file.

This namespace contains the static ConfigurationManager class, which exposes a number of methods for retrieving configuration information. ConfigurationManager.ConnectionStrings returns a collection containing all connection strings settings in the config file of the current assembly. Once we have this collection, we can retrieve a single item by specifying its name. Doing so returns a ConnectionStringSettings object that contains properties retrieved from a single <add> element of the config file. The important property is ConnectionString, which can be used with ADO.NET to connect to a database. Knowing the Provider property may also be useful if your application connects to different types of data.
Sample code is below.

ConnectionStringSettingsCollection cssCollection =
    ConfigurationManager.ConnectionStrings;
ConnectionStringSettings devCsSettings = cssCollection["MyApp_Dev"]; string devCs
= devCsSettings.ConnectionString;

The SQL Server master database contains many dynamic views that contain information about the current state of the SQL Server. One such view is dm_os_workers, which lists all active threads run by SQL Server and information about each thread. Of particular interest are the error columns:
One or more of the following bit columns will return 1 if there is anything is going wrong with a thread:

• is_sick
• is_in_cc_exception
• is_fatal_exception
• is_inside_catch

One limitation of this view is that it does not retain any history. If you want to keep a history of threads, you can create a Database to hold that history and the following SQL to copy the results of dm_os_workers to a table in that database. The following SQL copies the dm_os_workers view results to the dbo.ThreadsHistory table in the Instrumentation database.

          IF
          NOT
          EXISTS (SELECT * FROM Instrumentation.sys.objects WHERE object_id
= OBJECT_ID(N'[Instrumentation].[dbo].[ThreadsHistory]') AND type IN (N'U')) BEGINSELECT GETDATE() AS TimeLogged,
worker_address, status, is_preemptive, is_fiber, is_sick, is_in_cc_exception, is_fatal_exception,
is_inside_catch, is_in_polling_io_completion_routine, context_switch_count, pending_io_count,
pending_io_byte_count, pending_io_byte_average, wait_started_ms_ticks, wait_resumed_ms_ticks,
task_bound_ms_ticks, worker_created_ms_ticks, exception_num, exception_severity, exception_address,
locale, affinity, state, start_quantum, end_quantum, last_wait_type,
return_code, quantum_used, max_quantum, boost_count, tasks_processed_count, fiber_address,
task_address, memory_object_address, thread_address, signal_worker_address, scheduler_address,
processor_group INTO [Instrumentation].[dbo].[ThreadsHistory] FROM sys.dm_os_workers WHERE 1=0 ENDDECLARE @TimeNow AS DATETIME SELECT @TimeNow
= GETDATE() INSERT INTO Instrumentation.dbo.ThreadsHistory
( TimeLogged, worker_address, status, is_preemptive, is_fiber, is_sick, is_in_cc_exception,
is_fatal_exception, is_inside_catch, is_in_polling_io_completion_routine, context_switch_count,
pending_io_count, pending_io_byte_count, pending_io_byte_average, wait_started_ms_ticks,
wait_resumed_ms_ticks, task_bound_ms_ticks, worker_created_ms_ticks, exception_num,
exception_severity, exception_address, locale, affinity, state,
start_quantum, end_quantum, last_wait_type, return_code, quantum_used, max_quantum,
boost_count, tasks_processed_count, fiber_address, task_address, memory_object_address,
thread_address, signal_worker_address, scheduler_address, processor_group ) ( SELECT @TimeNow,
worker_address, status, is_preemptive, is_fiber, is_sick, is_in_cc_exception, is_fatal_exception,
is_inside_catch, is_in_polling_io_completion_routine, context_switch_count, pending_io_count,
pending_io_byte_count, pending_io_byte_average, wait_started_ms_ticks, wait_resumed_ms_ticks,
task_bound_ms_ticks, worker_created_ms_ticks, exception_num, exception_severity, exception_address,
locale, affinity, state, start_quantum, end_quantum, last_wait_type,
return_code, quantum_used, max_quantum, boost_count, tasks_processed_count, fiber_address,
task_address, memory_object_address, thread_address, signal_worker_address, scheduler_address,
processor_group FROM sys.dm_os_workers )

SQL Injection  is one of the most frequently-exploited vulnerabilities in the software world. It refers to user-entered data making its way into commands sent to back-end systems. It is common because so many developers are unaware of the risk and how to mitigate it.

Most of the applications I work with read from and write to a relational database, such as Microsoft SQL Server.  I frequently run across ADO.NET code like the following:

          string lastName = "'Adams'"; string sql
= "Select * from dbo.Customer where LastName = '" +
lastName + "'"; string connString
= ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString; using (var
conn = new SqlConnection(connString)) { conn.Open(); var
cmd = conn.CreateCommand(); cmd.CommandText = sql; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read())
{ Console.WriteLine("Bad Name: {0} {1}", reader["FirstName"],
reader["LastName"]); } }

This code is designed to call a stored procedure like the following:

          CREATE
          PROCEDURE [dbo].[GetCustomersByFirstName]
@FirstName NVARCHAR(50) ASBEGIN--
SET NOCOUNT ON added to prevent extra result sets fromSET NOCOUNT ON; SELECT Id,
FirstName, LastName FROM dbo.Customer WHERE FirstName
= @FirstName ORDERBY Id END GO

This method of code has several disadvantages

1. This code is not optimal because SQL Server does not have a chance to reuse a cached query plan unless the user happens to send the exact same text into SQL Server.
2. The string concatenation opens the system to SQL Injection attacks.

A SQL Injection Attack is an attempt by an unscrupulous user to pass malicious commands to a database. In the above example, imagine that the variable x was provided by a user inputting text into a text box on a web age. An evil user might type something like

          "Smith';DROP TABLE Customer;//"
        

Select * from dbo.Customer where LastName
= 'Smith';DROPTableCustomer;//'

Clearly, dropping the customer table is not what your code is intended to do.

Many of you will read the above example and decide that you are safe because

1. Your web code runs under a context with insufficient privileges to drop a table; and
2. You are validating all user inputs to ensure a user cannot enter anything bad.

There are problems with this reasoning.

1. A clever hacker can sometimes trick a user into running code under elevated privileges. Often there are multiple steps to an attack.
2. Even if you have caught every possible injection possibility in your user interface, you cannot guarantee that every call to this API will be made only from your UI for all eternity. You may open up the API to the public or you may subcontract writing a mobile application that calls this API or you may hire a new programmer who doesn't know better.

The point is that you need to check security at every level of your application. And part of checking security is to not trust your inputs.

A far better approach than concatenating strings to form a SQL statement is to create parameter instances; set the value of each parameter; and add these parameters to a Parameters collection.

The code below shows how to do this.

string lastName = "Adams"; string sql
= "Select * from dbo.Customer where LastName = @LastName"; string connString
= ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString; using (var
conn = new SqlConnection(connString)) { conn.Open(); var
cmd = conn.CreateCommand(); cmd.CommandText = sql; SqlParameter lnParam = cmd.CreateParameter();
lnParam.ParameterName = "@LastName"; lnParam.Value
= lastName; cmd.Parameters.Add(lnParam); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read())
{ Console.WriteLine("Good Name: {0} {1}", reader["FirstName"],
reader["LastName"]); } Console.WriteLine();

Pass an unexpected parameter here and it will no t be executed on the end of the query because SQL Server is expecting a parameter for a specific use.

The same pattern works if I want to pass in a dynamic string of SQL. Passing Parameter instances is more secure than concatenating SQL and passing that string to SQL Server.

Below is a console application that uses the vulnerable string concatenation method to call SQL Server via ADO.NET

using System; using System.Configuration; using System.Data.SqlClient; namespace PassingSql_WrongWay
{ class Program { staticvoid Main(string[]
args) { CallSqlQuery(); CallStoredProc(); Console.ReadLine(); } privatestaticvoid CallSqlQuery()
{ string lastName = "'Adams'"; //string
lastName = "Adams';DROP TABLE dbo.ExtraTable;--";string sql
= "Select * from dbo.Customer where LastName = '" +
lastName + "'"; string connString
= ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString; using (var
conn = new SqlConnection(connString)) { conn.Open(); var
cmd = conn.CreateCommand(); cmd.CommandText = sql; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read())
{ Console.WriteLine("Bad Name: {0} {1}", reader["FirstName"],
reader["LastName"]); } } Console.WriteLine(); } privatestaticvoid CallStoredProc()
{ string firstName = "James"; string sql
= "EXEC GetCustomersByFirstName '" + firstName
+ "'"; string connString
= ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString; using (var
conn = new SqlConnection(connString)) { conn.Open(); var
cmd = conn.CreateCommand(); cmd.CommandText = sql; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read())
{ Console.WriteLine("Bad Name: {0} {1}", reader["FirstName"],
reader["LastName"]); } Console.WriteLine(); } }
} }

Below is a similar console app, using the more secure parameters pattern

using System; using System.Configuration; using System.Data.SqlClient; namespace PassingSql_RightWay
{ class Program { staticvoid Main(string[]
args) { CallSqlQuery(); CallStoredProc(); Console.ReadLine(); } privatestaticvoid CallSqlQuery()
{ string lastName = "Adams"; //string
lastName = "Adams;DROP TABLE dbo.ExtraTable;--";string sql
= "Select * from dbo.Customer where LastName = @LastName"; string connString
= ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString; using (var
conn = new SqlConnection(connString)) { conn.Open(); var
cmd = conn.CreateCommand(); cmd.CommandText = sql; SqlParameter lnParam = cmd.CreateParameter();
lnParam.ParameterName = "@LastName"; lnParam.Value
= lastName; cmd.Parameters.Add(lnParam); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read())
{ Console.WriteLine("Good Name: {0} {1}", reader["FirstName"],
reader["LastName"]); } Console.WriteLine(); } } privatestaticvoid CallStoredProc()
{ string firstName = "James"; string storedProcName
= "GetCustomersByFirstName"; string connString
= ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString; using (var
conn = new SqlConnection(connString)) { conn.Open(); var
cmd = conn.CreateCommand(); cmd.CommandText = storedProcName; cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter lnParam = cmd.CreateParameter(); lnParam.ParameterName = "@FirstName";
lnParam.Value = firstName; cmd.Parameters.Add(lnParam); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read())
{ Console.WriteLine("Good Name: {0} {1}", reader["FirstName"],
reader["LastName"]); } Console.WriteLine(); } }
} }

If you wish to use the above code, create a new database named TestData and run the following SQL DDL to create the database objects.

USE [TestData] GO /****** Object: Table [dbo].[ExtraTable] SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATETABLE [dbo].[ExtraTable](
[foo] [nchar](10) NULL, [bar]
[nchar](10) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[Customer] SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATETABLE [dbo].[Customer](
[Id] [int] IDENTITY(1,1) NOTNULL,
[FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NOTNULL ) ON [PRIMARY] GO INSERT INTO dbo.Customer
(FirstName, LastName) VALUES ('George', 'Washington') GO INSERT INTO dbo.Customer
(FirstName, LastName) VALUES ('John', 'Adams') GO INSERT INTO dbo.Customer
(FirstName, LastName) VALUES ('Thomas', 'Jefferson') GO INSERT INTO dbo.Customer
(FirstName, LastName) VALUES ('James', 'Madison') GO INSERT INTO dbo.Customer
(FirstName, LastName) VALUES ('James', 'Monroe') GO INSERT INTO dbo.Customer
(FirstName, LastName) VALUES ('John Quincy', 'Adams') GO /****** Object:
StoredProcedure [dbo].[GetCustomersByFirstName] SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATEPROCEDURE [dbo].[GetCustomersByFirstName]
@FirstName NVARCHAR(50) ASBEGINSET NOCOUNT ON; SELECT Id,
FirstName, LastName FROM dbo.Customer WHERE FirstName
= @FirstName ORDERBY Id ENDGO

With a little bit of thought and a few lines of code, you can significantly reduce the risk of SQL injection in your ADO.NET code.

SQL Server 2008 R2 provides some new tools to help deploy database schemas.

Compiled code and database schemas are frequently dependent on one another and must therefore be deployed simultaneously to a given environment. If a developer writes code that accesses a new table, that table must be available when the code runs. If the code is deployed and run before the table is deployed to the database, a runtime error occurs and the user experience will be unsatisfactory.

In the past, Microsoft has released tools to help developers manage the deployment of database schema changes. Visual Studio Database Edition (aka “Data Dude”) was bundled into Visual Studio 2010. But these tools are geared toward developers – not to DBAs. Most DBAs don’t work in Visual Studio. And in large enterprise applications, it is the DBAs who are managing the deployment of the database schemas. Without tools, this coordination requires a lot of manual steps.

SQL Server 2008 R2 includes support for Data Tier Applications. A Data Tier Application (mysteriously abbreviated “DAC”) is a package containing instructions for deploying a database schema. DBAs and developers can use SQL Server Management Studio to create “DACPAC” files for a given database. They can then use that DACPAC file to create or update a database with an existing schema.

DAC does not support all database objects. Standard objects, such as tables, columns, views, indexes are supported. Security objects such as users roles and logins are supported. Extended stored procedures and full text searches are not supported. The list of supported items is similar to the list of items supported in SQL Azure.

To create a DAC in SQL Server 2008 R2, launch SQL Server Management Studio, connect to a server, and expand the Databases node. Right-click the name of the database you want to deploy and select Tasks | Extract Data Tier Application… from the context menu. The Extract Data Tier Application wizard launches and an introduction window (Figure 1) displays.

Figure 1

Click the Next button. The Set Properties screen (Figure 2) displays.

Figure 2

At the Application Name field, enter a name for this DAC.

At the Version field, enter a version number. SQL Server does not auto-increment version numbers, so you will need to manage this yourself. It’s a good idea to keep major and minor versions of database schemas in sync with the version of the application that consumes them.

At the Description field, you may optionally enter a brief description of the schema.

At the Save to DAC package file field, enter the full path (including file name) where you want to store the deployment file. You may click the Browse button to select a folder. If you enter the name of a file that already exists, you must check the Overwrite existing file checkbox in order to proceed.

Click the Next button to perform a validation. The validation checks if DAC can handle all objects in the database. When the validation is complete, the Validation screen (Figure 3) displays. This screen displays items up to three sets of items: Items that can be deployed with DAC are displayed beneath a green Check icon; items that are not supported are displayed under a red X icon; and items that are supported, but rely on unsupported items are displayed under a yellow Exclamation icon.

Figure 3

Supported items present no problem to DAC.

Unsupported items appear as errors. Unfortunately, you will not be allowed to proceed until these items are removed from the database. Fortunately, the wizard is non-modal, so you can minimize it and switch back to Management Studio to remove these items before re-running the Validation.

Click the Next button to start building the DAC package.

When complete, the Build Package screen (Figure 4) displays, indicating success or listing problems that occurred.

Figure 4

Click the Finish button to close the wizard. A file with the extension DACPAC is created.

Deploying the Schema in a DAC

After creating a DAC, you can use SQL Server Management Studio to deploy the DAC’s schema to a new database. To begin, launch SQL Server Management Studio, expand the Management node, and right-click the Data-tier applications node. From the context menu, select Deploy Data-tier application. The The Introduction screen (Figure 5) of the Deploy Data-tier application wizard displays.

Figure 5

Click the Next button to display the Select Package screen (Figure 6).

Figure 6

Click the Browse button and select the DACPAC file created above. Click Open. Click the Next button to display the Update Configuration screen (Figure 7).

Figure 7

At the Name field, enter a name for the database you wish to create. This field defaults to the name of the database from which the DAC originated. If you are running this deployment on the same server instance on which you created the DACPAC, this will cause a conflict and SQL Server will notify you with a red exclamation icon next to the Name field.

Click Next to begin the deployment. When the deployment completes, the Summary screen (Figure 8) displays.

You should notice the new database appears beneath the Database node and the DAC is registered and appears beneath the Management \ Data-tier applications node. You may need to refresh these nodes in order to view them.

Note that the new database contains no objects – only database objects, such as tables, views, stored procedures, and users.

In this article, we discussed Data-tier applications and described how to use SQL Server Management Studio to create and deploy a DACPAC file.

Episode 89

In this interview, Dane Morgridege describes the Entity Framework - an object relational mapping tool from Microsoft.

Episode 75

Sam Corder is the founder of the MongoDB-CSharp open source project In this interview, he describes the use of MongoDB and other document database

When writing .Net code (or code in any language for that matter) that updates a database, you need to be cognizant of the fact that it takes a finite amount of time to connect to a database and process any commands sent to the database.

ADO.Net permits you to set a TimeOut value on a Connection object and on a Command object.

The Command TimeOut property allows you to configure how long a command waits to successfully complete execution of a query. By default, a Command object will timeout after 30 seconds

It’s important to strike a good balance when setting timeout values.

Sometimes we expect a database action to take a long time and we want to give it time to complete before we pull the rug out, so to speak.

On the other hand, if a problem prevents a command from executing properly, it's useful to know this sooner so our application can handle it.

Changing a command timeout is simple. The Command object exposes a read/write ConnectionTimeout property. Set it to the number of seconds you wish the comand to wait on executing before aborting.

After the Command TimeOut period, if the command has not completed, an exception is thrown. However, the database server does not know this, so the command will continue to execute on the server - your application just won't know the results.

The Connection TimeOut is the amount of time the Connection will spend attempting to connect to a database before giving up and throwing an exception. The default Connection Timeout value is 15 seconds. On a slow network, it may take longer to connect, so you may wish to increase this value. However, if the application is unable to connect to the database - if the server is unavailable, for example - it's best to find this out sooner rather than later.

Changing the Connection Timeout is less obvious than changing the Command Timeout. The Connection class exposes a ConnectionTimeout property; But this property is read-only, so you cannot use it to change the timeout. To change a timeout, you must modify the connection string. Add or update the following to your connection string:
    Connection Timeout=XXX
where XXX is the number of seconds to wait for a connection to remain open before aborting all pending operations on that connection.

In your applications, it is important to strike the right balance when setting timeout properties.

Episode 53

Joe Kunk is writing a chapter covering XTraReports for Paul Kimmel's upcoming DevExpress tools book. In this interview, Joe describes XTraReports and how to use it.

Episode 52

Often, an application experiences a bottleneck retrieving and delivering data from a database. In this interfview, Jeremiah Peschka discusses ways to troubleshoot performance issues within a database.

This series of articles provides an introduction to relational databases in general and Microsoft SQL Server in particular.

Part 1: What is a Database?

Part 2: Relationships

Part 3: Selecting data from a table

Part 4: Aggregating data

Part 5: Joins

Part 6: Inserts, Updates and Deletes

Episode 45

In this interview, Jessica Moss describes SQL Server Integration Services (SSIS): What it is; how it works; and how to get started learning and using it.

7 mins, 48 secs

Episode 32

In this video, author and speaker Josef Finsel describes moving databases to "The Cloud" using Azure Table storage.  You can read more of his thoughts on the subject at http://azuredba.com/blog.aspx

11 mins, 9 secs

Episode 31

In this interview, Jim Wooley discusses the relevance of stored procedures and best practices for accessing a database.

13 mins, 32 secs

We can use the INSERT command to add a new row to this table. The syntax of the INSERT command is

          
            INSERT
            INTO [TableName]
    (     [Column List]     ) VALUES     (
    [Values List]     ) 
        

We can insert a row for a new Customer - Brad Van Pelt - with the following code.

          
            INSERT
            INTO Customer
    (     FirstName,     LastName,
    StreetAddress,     City,     State,
    ZipCode,     TotalSales     ) VALUES     (
    'Brad',
    'Van
Pelt',     '99
Linebaker Ln',     'Owosso',
    'MI',
    '47777',
    4000     ) 
        

Notice that each column name in the first set of parentheses matches a value in the second set of parentheses: 'Brad' with FirstName, 'Van Pelt' with LastName and so on. Notice also that we did not provide a value for the CustID column. This is because CustID is an identity column and, therefore, gets populated with an incremented number when a new row is added.

After executing the above INSERT statement, our data should look like this:

In this article, we showed how to use T-SQL's INSERT, UPDATE, and DELETE commands to modify the data in a table.

The new customer was automatically assigned a CustID value of 3. Because this value uniquely identifies the newly-added row, we can use it to find and update that row. The syntax to update a row in SQL Server is

          
            UPDATE [Table]
    SET [Column1]
= [New Value 1],         [Column2] = [New
Value 2],         [Column3] = [New Value 3],
        etc...     WHERE [Filter
Condition] 
        

Only rows that match the filter condition will be updated and only those columns specified in the SET clause will be updated.  We will use the following command to update the StreetAddress, City, State and ZipCdoe columns of Customer 3:

          
            UPDATE Customer
    SET StreetAddress
= '100
Safety St',         City='New
York',         State='NY'         ZipCode='01111'     WHERE CustID
= 3 
        

After executing the above UPDATE command, our data should like this:

We use the DELETE command to delete rows in a SQL Server table. The DELETE syntax is

          
            DELETE Customer
    WHERE CustID
= 3 
        

The following code will delete Customer 3

          
            DELETE Customer
    WHERE CustID
= 3 
        

After executing the above DELETE command, our data will look like this:

In this article, we showed how to use the INSERT, UPDATE and DELETE commands to modify data in a SQL Server table.

In a previous article, I explained how we can (and often should) split a table into multiple tables in order to eliminate data redundancy - a process known as "normalization".

In this article, I'll explain how to retrieve related data from multiple tables and return them in a single result set.

Recall from the Normalization article that - in order to relate to tables - we add a key to each table.  The Primary key in the parent table is a column that is unique for each row and, therefore, servers to uniquely identify a row.  The child table contains a foreign key which is the same value as a Primary key in the parent table, so it points to a given row in the parent.

To retrieve data from multiple tables into a single result set, we do something called a "JOIN".  In SQL, there are two ways to JOIN tables:

• Using the JOIN keyword
• Adding the join condition on a WHERE clause

JOIN keyword

The syntax for joining tables with the JOIN keyword is

The syntax for joining tables with the JOIN keyword is

SELECT [List of Columns]
    FROM [Table 1]
    JOIN [Table 2]
        ON [Join Condition]

  For example, to retrieve the Name, SalesOrder Date and SalesOrder Amount of each customer in our sample tables, use the following query:

          
            SELECT         FirstName,
        LastName,         OrderDate
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID
= SalesOrder.CustID
        

Notice that we need to prefix the CustID column name with the table name in our filter condition.  This is because the CustID column name is not unique.  We need to tell SQL to which column we are referring.

The results of this query are

WHERE clause

The syntax for joining two tables with the WHERE clause is

SELECT [List of Columns]
    FROM [Table 1], [Table 2]
    WHERE [Join Condition]

The syntax to return the same result set as above is

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID

Recall that the WHERE clause is also used to filter your result set.  In fact, you can use it for both filtering and joining.  The following two queries yield the same results (showing only those records that match customer 1.

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    WHERE Customer.CustID = 1

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID
        AND Customer.CustID = 1

Here is the result set for either of the above two queries:

You can use these same techniques to join more than two tables. Here is the syntax to add the OrderLine table to our queries

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    JOIN OrderLine
        ON SalesOrder.OrderID = OrderLine.OrderID

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer, SalesOrder, OrderLine
    WHERE Customer.CustID = SalesOrder.CustID
        AND SalesOrder.OrderID = OrderLine.OrderID

Here is the result set of either of these 3-table queries

I prefer to use the JOIN keyword syntax when joining tables together because it is more clear what part of the query is a filter and what part of a query is a join. 

In this article, we showed the ways to use SQL Server to join multiple tables into a single result set.

In the last article in this series, I expleined the basic functionality of the SELECT statement. I showed how to select columns from a table and sort or filter the results.

In this article, I'll show some more things you can do with the SELECT statemnts.

Sometimes we want our results to aggregate data. When aggregating data, we use functions that consolidate multiple rows and return the result of this aggregate function applied to many rows. The most common aggregate functions I use that SQL Server supports are:

The MIN, MAX, SUM, and AVG functions accept a parameter - the name of the column on which to calculate these values. The column must hold a numeric data type, such as an INT, FLOAT or MONEY.

You may pass a column name as a parameter to the COUNT function but it doesn't matter which column because - in SQL Server - every column appears exactly once in each row and the COUNT function is used to count rows. By convention, we pass "*" as the parameter to the COUNT aggregate function to represent all rows.

A few examples will help clarify this. Since our table contains only one numeric column - TotalSales - we will use this in most of our aggregate functions.

The following query returns the maximum value of the TotalSales column.

          
            SELECT
            MAX (TotalSales) AS MaxSales
    FROM Customer 
        

Here are the results - one row with one column containing the highest numerical value in the TotalSales column.

Similarly, the following query returns the sum of all values in the TotalSales column.

          
            SELECT
            SUM (TotalSales) AS SumSales
    FROM Customer 
        

This returns 14,750 which is the sum of 1000 + 1500 + 1000 + 2000 + 5000 + 250 + 4000

We can filter before applying an aggregate function. If we only want to sum of TotalSales for customers in michigan, we simply add a WHERE clause to our query.

          
            SELECT
            SUM (TotalSales) AS SumSales
    FROM Customer
    WHERE State
= 'MI'
        

This returns 12,500, which is 1500 + 2000 + 5000 + 4000, or the sum of the TotalSales column for only those customers in Michigan.

As mentioned before, we do not need to specify a particular column for the COUNT function.

          
            SELECT
            COUNT (*) AS NumCusts
    FROM Customer 
        

returns the a row and column containing number 7, which is how many rows are in our table.

So far, each query we have written has returned only one row. Often, however, we want to calculate an aggregation for each distinct value in a column or columns. We can do this by adding a GROUP BY clause to the query. For example, we may want to see the SUM of TotalSales for each state.

          
            SELECT         State,
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUPBY State 
        

The above query returns a row for each distinct value in the State column and calculates the sum of TotalSales of all rows corresponding to that state.

It's important to note that, when using the GROUP BY clause, you cannot return a column that is not part of the grouping. So

          
            SELECT         State,
        City,         SUM (TotalSales) AS SumSales
    FROM Customer
    GROUPBY State 
        

results in an error because we are trying to return the city column, but we are not grouping on that column. Because a given state can have multiple cities, SQL does not know which one to display for the row returned.

You can group on multiple columns as in the following query

          
            SELECT         State,
        City,         SUM (TotalSales) AS SumSales
    FROM Customer
    GROUPBY State,
City 
        

In this case, we get a row with a sum for each combination of state and city.

By using the GROUP BY clause on a large table, we may end up with so many rows that it becomes difficult to find relevant data. Sometimes, we are only interested in those times when the aggregate value exceeds some threshhold. In these cases, it would be nice to only show aggregate rows that exceed that threshhold. This sounds like a good place to use a filter. Unfortunately, we cannot use the WHERE clause to accomplish this task because the WHERE clause filters data before the aggregation. We have to wait until after calculating the aggregate values becuase it is the aggregate values on which we want to filter. The HAVING clause is used to filter on aggregate values.

We can run our query to get the TotalSales sum for each state, but show only those states that have total sales of more than 2000, using the following query

          
            SELECT         State,
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUPBY State
    HAVINGSUM (TotalSales)
>= 1000 
        

In this case, we don't see the row for Kentucky and New York because they had total sales summing less than $1000

In this article, we showed how to use T-SQL's grouping and aggregate functions to return summary data from a database.

Saturday April 4 at the Robert C. Pew Grand Rapids Campus of Grand Valley State Universityin Grand Rapids, Shane Jordan and I will be delivering a session on the basics of SQL Server.  This will cover many of the same topics as the Back To Basics: SQL 101 series I have writton on this blog.

The session is part of the West Michigan .Net University event.  You can get more informaiton and register at http://www.dayofdotnet.org/WestMichiganDotNetU/

Retrieving data is one of the most common tasks performed on a relational database.

Fortunately, SQL Server includes a language that allows users (and programs) to retrieve the data they want. This language is called Structured Query Language. It is usually abbreviated "SQL" and often pronounced "SEE-kwuhl".

Getting data from a database is known as querying the database. The code to retrieve that data is a query. When this code is written in SQL (as most of my queries are), it is known as a SQL query.

The basic syntax for a SQL query that retrieves data from a single table is

SELECT [List of Columns]
    FROM [Table Name]
    WHERE [Filter Condition]
    ORDER BY [Sort column or columns]

In SQL, line breaks and extra spaces are not important. The language parser is smart enough to figure out when a statement or command ends, so I usually try to format my SQL statements to make them easy to read.

Only the SELECT and FROM parts of the query are required, so let's start with those.

EvenEven though the "SELECT" keyword typically comes first in this type of query, I'll start by explaining the "FROM" keyword. When getting data from only one table, follow the keyword FROM with the name of that table. For example, the clause

              FROM Customer 
        

indicates that we are getting data from a table named Customer. We'll talk later about how to get data from multiple tables in the same query.

It is possible to provide an alias for a table by following the table name with a space, and the alias. For example

              FROM Customer
cust
        

The above SQL clause says that we will get data from the Customer table, but that we will use the string “cust” to refer to this table elsewhere in our query.

This is useful in the following situations

• You want to provide a shorter name for the table in order to avoid retyping a long name elsewhere in the query
• You want to avoid ambiguity when listing the same table name twice in the FROM clause.

SELECT is the first keyword of this type of query and tells SQL Server that we want to retrieve data from the database. The word "SELECT" is followed by a list of columns that the query will return. If the column list contains duplicate column names (as when you are getting data from two different tables and they each have a column with the same name), you should precede the column name with the table name or alias.

You can also use the special character "*" in place of (or in addition to) the list of column names in order to return all columns in the tables.

The following query returns all columns and rows in the Customer table.

          
            SELECT *
    FROM Customer 
        

We can return onWe can return only the FirstName and LastName columns from the Customer table with the following table

          
            SELECT         FirstName,
        LastName     FROM Customer 
        

In the queries above, it is possible to qualify the column names, explicitly indicating that they are from the Customer table. Of course, in this case it is unnecessary because the column names are unique within the Customer table. The following examples qualify the column names and returns the same data.

SELECT
        Customer.FirstName,
        Customer.LastName
    FROM Customer

SELECT
        cu.FirstName,
        cu.LastName
    FROM Customer cu

By default, each column returned by the query retains the name of the corresponding column in the source table. you want to change the name of a If you want to change the name of a column in the query, alias that column by appending the keyword " AS " followed by the alias you want. For example

SELECT
        FirstName AS FirstNm,
        LastName AS LastNm
    FROM Customer

If wIf we want to sort the output we can add the ORDER BY clause to our query. The syntax for this clause is

ORDERBY[List of Columns on which to sort]

For example, we can sort our output on Last Name by changing our query to

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDERBY LastName

We can add more columns to the list of sort columns if we separate each with a comma. The second column is only appropriate in our sort if two rows have identical values for the first column.

For example

SELECT FirstNameFor example

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDERBY LastName, FirstName

The above result set contains 2 rows with the last name "Smith".  These two rows were sorted in order of their FirstName column.

In many cases, we may not want to return eIn many cases, we may not want to return every row in a table. We can use the WHERE clause to filter data. The syntax of the WHERE clause is

   WHERE[Filter Condition]

The Filter condition is a Boolean expression, meaning it evaluates to either TRUE or FALSE for every row. The query will return only those rows for which this condition evaluates to TRUE.

For example, if we want to get only those customers in Michigan we can use the query

SELECT * 
   FROM Customer 
   WHERE State = 'MI'

This query only returned those rows that match our filter condition.

Of course, we can combine several of these clauses as in the following

SELECT
        cu.FirstName,
        cu.LastName,
        cu.State AS ResidencyState
    FROM Customer cu
    WHERE cu.State = 'MI'
    ORDER BY cu.LastName

As you can see, we can use the SELECT commmand to retrieve data from a table in a database and customize the way that data comes back.  We've just scratched the surface of this command.  In the next article, we'll look at more options of the SELECT command

In this article, we will define a database, a table and the main parts of a table - rows and columns.

A database is an organized (or structured) collection of information.

Most companies spend a lot of time and effort collecting information and storing it somewhere, but not all that information is organized, which makes it difficult to retrieve anything relevant later on.  A database adds structure to the information making it easier to maintain and query it.

Database engines like SQL Server provide a structure to organize data in a way that makes sense to a user.  Specifically, SQL Server uses a relational model* to organize its data.

In a relational database, data is partitioned into tables.  Tables are a way of data storing data in rows and columns, kind of like in an Excel worksheet. 

Figure 1 - Data in an Excel workbook

I've always found this rectangular view of data very intuitive.

Just as in a workbook, each table row represents a discrete record.  All information in that row serves to describe the row.  

Similarly, a table column is a placeholder that describes a single attribute for each row.  The advantage SQL Server has over Excel is that you can easily place rules onto a column, restricting the type of data that can be stored there. 

If a SQL Server column is designed to hold a date, a property of that column can be set to throw an error if a person or program tries to store a string.   We can set up such restrictions for many data types, so that a column can be restricted to allow only integers, only TRUE/FALSE values, or only binary objects.  We can even restrict the maximum length of a string or require users to always enter a value into a column - all simply by setting properties on the column.**

For example, a table named "Customers" might be used to store information about your company's customers.  Each row in this table would represent a single customer.  Each column would hold an attribute of that customer, so you could create columns such as FirstName, LastName and StreetAddress that would hold the appropriate values for each customer. 

Figure 2 - Data in a SQL Server table

Looking at the first row, gives us information about the customer.  It should be obvious that this customer has a first name of "David", a last name of "Giard" and an address of "123 Main St".

*SQL Server does provide some non-relational ways of storing data but those are beyond the scope of this article.
** It is possible to configure Microsoft Excel to restrict data input, but this task is relatively advanced and far more easily accomplished in SQL Server.

Episode 3

In this interview, Jason Follas explains spatial data types, which were introduced in SQL Server 2008

Below is a list of my recent series of articles on Visual Studio 2008 Database Edition (aka “Data Dude”)

The Goodness of Data Dude

Data Dude tutorial 1 - Creating a database project

Data Dude tutorial 2 - Using Schema Compare tool to update a database project

Data Dude tutorial 3 - Using Schema Compare tool to deploy database changes

Data Dude tutorial 4 - Writing Database Unit Tests

Data Dude tutorial 5 - Using the Data Compare tool

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases. 

In this article, we will discuss how to migrate data from one database to another.   Data Dude provides the Data Compare tool for this purpose.

In order to use the Data Compare tool, the following conditions must be true

1.        Data exists in a source table.  You want to migrate that data to a table of the same name in a different database.

2.        Both tables must have the same structure.

3.        Both tables must have a primary key to uniquely identify rows.

Follow the steps below to migrate data with the Data Compare tool.

1.        Launch Visual Studio 2008

2.        Select Data | Data Compare | New Data Compare.  The New Data Comparison dialog displays

  Figure 1

a.        When migrating data from a table in one database to another, the database you intend to update is known as the “Target Database”.  The other database is known as the “Source Database”.  In the New Data Comparison dialog, select the Source Database and Target Database connections.  If you have not created a Visual Studio connection to these databases in Visual Studio, you can click the New Connection button to create them.

b.       The New Data Comparison dialog contains checkboxes that allow you to specify which rows you want to see and compare.  I don’t usually change these (they are all checked by default) but it may speed up the process to clear the Identical Records checkbox.

c.        Click the Next button to advance to the second screen of the wizard.

    Figure 2
On this screen, you can choose which tables to compare.  Usually I am only interested in one or two tables, so I clear the rest of the checkboxes.  If I have a million rows in my customer table and I’m not interested in migrating any of those rows, I can save a lot of processing time by un-checking the customer table.

d.       Click the Finish button to display the Data Compare window.

3.        The Data Compare window consists of two panes: the Object List on top and the Record Details at the bottom.

    Figure 3

a.        The object list displays each table or view as a single row with columns summarizing the number of new, removed, changed and identical rows.  Rows are matched on their primary key.

b.       Click a row in the object list to display details in the record details pane.  Here you can click a tab to view the rows that are new, missing or changed.  Checking the checkbox next to a record flags it to the Data Compare tool, meaning you want to update the target database to match the same row in the source database.  This may result in an INSERT, UPDATE, or DELETE statement depending on the tab on which the record is listed.  

c.        For a record to be flagged for update, both the table and the record must be checked.

4.        After checking all the rows you wish to update, click the Write Updates toolbar button to commit your changes to the target database.  

5.        Alternatively, you can click the Export To Editor toolbar button to generate a SQL script that you can run in the SQL Server query editor.  This method requires an extra step but has the following advantages

a.        You can modify the script before running it.

b.       You can send the script to someone else to run.

c.        You can view the script to learn what Data Dude is doing.  It’s interesting to note that constraints on each table are dropped before copying data, then created after the data is copied.  This speeds up the process.  Also, note the use of transactions to prevent incomplete data copies.  Below is a sample script updating data in one table.
/*
This script was created by Visual Studio on 8/15/2008 at 8:57 AM.
Run this script on dgiard.Test_QA.dbo to make it the same as dgiard.Test_Dev.dbo.
This script performs its actions in the following order:
1. Disable foreign-key constraints.
2. Perform DELETE commands.
3. Perform UPDATE commands.
4. Perform INSERT commands.
5. Re-enable foreign-key constraints.
Please back up your target database before running this script.
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLSON
GO
/*Pointer used for text / image updates. This might not be needed, but is declared here just in case*/
DECLARE @pv binary(16)
BEGINTRANSACTION
ALTERTABLE [dbo].[OrderDetails] DROPCONSTRAINT [FK_OrderDetails_Orders]
ALTERTABLE [dbo].[OrderDetails] DROPCONSTRAINT [FK_OrderDetails_Products]
ALTERTABLE [dbo].[Orders] DROPCONSTRAINT [FK_Orders_Customers]
DELETEFROM [dbo].[ProductTypes] WHERE [ProductTypeID]=N'5646953f-7b89-4862-bcf3-bf53450d28bb'
INSERTINTO [dbo].[ProductTypes] ([ProductTypeID], [ProductTypeName]) VALUES (N'7beb0d99-d034-41b9-bbf7-f9cdcdbedc30', N'Furniture')
INSERTINTO [dbo].[ProductTypes] ([ProductTypeID], [ProductTypeName]) VALUES (N'abc19a14-5968-4c5f-9f0f-4debc034cb90', N'Hardware')
INSERTINTO [dbo].[ProductTypes] ([ProductTypeID], [ProductTypeName]) VALUES (N'b9e446ed-eeb1-4334-b191-c70a55ef1a05', N'Books')
ALTERTABLE [dbo].[OrderDetails] ADDCONSTRAINT [FK_OrderDetails_Orders] FOREIGNKEY ([OrderID]) REFERENCES [dbo].[Orders] ([OrderID])
ALTERTABLE [dbo].[OrderDetails] ADDCONSTRAINT [FK_OrderDetails_Products] FOREIGNKEY ([ProductID]) REFERENCES [dbo].[Products] ([ProductID])
ALTERTABLE [dbo].[Orders] ADDCONSTRAINT [FK_Orders_Customers] FOREIGNKEY ([CustomerID]) REFERENCES [dbo].[Customers] ([CustID])
COMMITTRANSACTION

The Data Compare tool is a simple tool for accomplishing a useful task.  Since I discovered it, it has saved me a lot of time setting up new data environments.

Writing Unit Tests is an essential step in developing robust, maintainable code.  Unit Tests increase quality and mitigate the risk of future code changes.  However, relatively few developers take the time to write unit tests for their stored procedures.  The primary reason for this is that few tools exist to test stored procedures.

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools to help developers write unit tests against SQL Server stored procedures.  The tool integrates with MSTest, wich is a testing framework many developers are already using for their other unit tests.

In order to write unit tests for your stored procedures, those stored procedures must be in a database project.  For information on how to create a database project from a SQL Server database see: http://www.davidgiard.com/2008/08/11/DataDudeTutorial1CreatingADatabaseProject.aspx

This document describes how to create a database unit test.

1.        Launch Visual Studio and open your Database Project.

2.        Open the Schema View.  Select View | Schema View .

3.        Right-click a stored procedure and select Create Unit Test from the context menu.  The Create Unit Tests dialog displays.

    Figure 1

4.        Check the checkboxes next to all the stored procedures for which you wish to create unit tests.  Select the .Net language (Visual Basic .Net or C#) in which you want the automatic code to be generated.  You won’t be modifying this code so it isn’t that important, but I tend to keep all my code in the same language, so you may as well choose your favorite language here.  Enter a meaningful name for the Unit Test Project and class.  I like to name my Unit Test projects the same as my database project, followed by “Tests” or “UnitTests”.  If this is a new Database Unit Test Project, the Database Unit Test Configuration dialog displays.

    Figure 2

5.        The Database Unit Test Configuration dialog allows you to specify what you want to occur when you run these unit tests.  The dialog is organized into the following sections.

a.        Database connections

                                                               i.       Execute unit tests using the following data connection
This is the database against which tests will run.  Typically I set this to my Development or QA database.

                                                             ii.       Use a secondary data connection to validate unit tests
You may specify a different database to validate the syntax of your unit tests and test that all the objects you refer to exist.  I can only think this might be good if you are writing tests while disconnected from your testing database, but I never set this option.

b.       Deployment

                                                               i.       Automatically deploy the database project before unit tests are run
To save manual steps, you may wish to check this box and deploy the database project to the database each time you run your unit tests.  This slows down the testing step so I do not select this option.  I prefer to deploy my changes once; then run my unit tests – sometimes several times.

c.        Database state

                                                               i.       Generate Test data before Unit tests are run
It is often useful to populate your database with some test data prior to your test run.  Use this button to do this.

6.        After creating your unit tests, you need to modify each one and specify what you are testing.  Open the Solution Explorer (View | Solution Explorer ).

7.        Double-click the unit test class to open it in the unit test designer.  

    Figure 2

8.        The Unit Test Designer contains some controls and two panes as described below.  

a.        A class can contain multiple tests.  The first control is a dropdown that allows you to select which test you are designing.

b.       To the right of the Test Name dropdown is another dropdown that allows you to specify what part of the test you are writing.  You can choose between the test itself, the “Pre-test” (which runs before the test is executed) and the “Post-test” (which runs after the test has completed – successfully or unsuccessfully).

   Figure 3

c.        Further to the right are three buttons that allow you to add a new test or to delete or rename the currently active test.

d.       Below the controls is the test editor.  This is where you will write your test.  You will write your test in T-SQL and Data Dude provides some stub code to get you started.  Write SQL statements that call your stored procedure and return one or more results.

e.       Below the test editor is the Test Conditions pane.  It is here that you enter your assertions.  

    Figure 4

                                                               i.       You can test for a given result set having 0 rows, 1 or more rows, or an exact number of rows.  

                                                             ii.       You can also test if a specific column and row in a given result set evaluates to a given value.  

                                                            iii.       Click the “+” button to add new assertions.  Highlight an existing assertion row and edit the row or click the “x” button to remove the assertion.  

                                                           iv.       Use the properties window to modify properties of the assertion.  Many assertions are based on a given resultset.  When I first started writing unit tests, I found it difficult to determine which resultset was which.  Basically, any line in your SQL script that begins with the word “SELECT” creates a resultset.  Each resultset is numbered, beginning with 1, in the order it is created in your script.  I sometimes find it useful to copy the SQL code and paste it into SQL Management Studio query window and run it.  Each resultset then appears in a separate grid in the Results pane.  Looking at these grids allows me to more easily see a sample result set and in what order they are created.

f.         You run your database unit tests the same ways you run any MS Test unit test.  One way to run the tests is to open the Test List Editor (Test | Windows | Test List Window), check the tests you want to run, and click the Run Checked Test toolbar button.  Tests in which all assertions prove true are passed; all others are failed.

Using Data Dude, you can extend your unit tests to cover your database objects and, therefore, improve the overall quality and maintainability of your code.

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases. 

In our last tutorial , we described how a database developer would use the Schema Compare tool to update a database project with changes to a SQL Server database.

This article describes how to use the Schema Compare tool to push those changes out to a different SQL Server database.  There are two scenarios where you would do this. 

In Scenario 1, a developer has a local copy of the development database and wishes to get the latest updates to the database. 

In Scenario 2, a database administrator (DBA) or build master who is charged with migrating database changes from one environment to the next.  Just as .Net and web code gets regularly migrated from a development environment to a QA or Production environment, database object code must also be migrated, and that migration generally must be kept in sync with all code that depends on those database objects.

We start this process by launching Visual Studio and opening the database project.  If a source code repository such as TFS is used, we need to get the latest code from the repository.

The database to which we wish to write the changes is known to Data Dude as the “target database”.  We need to make sure that a connection exists in Visual Studio to the target database.  This is a one-time step and you can use the Server Explorer (View | Server Explorer) to create a connection.

The following steps describe how to propagate changes to the database.

1.        Launch Visual Studio and open the database project.  Get the latest source code from your source code repository.

2.        From the Visual Studio menu, select Data | Schema Compare | New Schema Compare.  The New Schema Compare dialog displays.

    Figure 1

3.        Under Source Schema, select the Project radio button and select your database project from the dropdown list.

4.        Under Target Schema, select the Database radio button and select the connection to your database from dropdown list.

5.        Click the OK button to display the Schema Compare window.

    Figure 2

6.        The Schema Compare window lists every object that exists in either the database or the database project.  The objects are grouped in folders by object type (Tables, views, stored procedures, etc.)  You can expand or collapse a folder to view or hide objects of that type.  The important column is “Update Action” which describes what will happen if you write the updates to the target.

a.        Objects that exist in the source (the project) but not in the target (the database) were likely recently added after the last synchronization.  By default, the Update Action will be “Create” meaning the object will be created in the target database.

b.       Objects that exist in both the source and the target will have an Update Action of “Update” if they have been modified in the database since the last synchronization or “Skip” if they have not.

c.        Objects that exist in the destination (the database) but not in the source (the project) were likely dropped after the last synchronization. By default, the Update Action will be “Drop” meaning the object will be removed from the database.

7.        On a database with many objects, it is useful to view only the objects that have changed since the last synchronization.  To do this, click the Filter toolbar button and select Non Skip Objects .  

    Figure 3

8.        If you wish, you can modify the Update Action on objects by selecting the dropdown in the “Update Action” column.  Some actions are grayed out because Data Dude will not allow you to perform any action that would violate referential integrity rules.  

9.        After you have set the “Update Action” of every object appropriately, you have a couple options.

a.        You can migrate your changes immediately to the target database by clicking the “Write Updates” toolbar button.  Click Yes at the confirmation to write the updates to the database project.

b.       Alternatively, you can export your changes to a SQL script by clicking the Export To Editor toolbar button.  This will create a single text file containing SQL script that you can run from a query window of SQL Server Management Studio.  This is useful if you need to make changes to the script prior to executing.  I have used this technique when my database contains views or stored procedures that refer to remote servers and I want to modify the name of the server before migrating the object.

Alternatively, you can deploy changes from a database project to a database by “Deploying” the project (select Build | Deploy Solution).  This deploys your changes using the settings found on the Build tab of the project properties page.  This method requires fewer steps, but it is less flexible than the method described above.  In particular, it does not allow you to select which objects are deployed or export and modify the script of database changes.

In the next article, we will discuss how to use Data Dude to write Unit Tests against SQL Server stored procedures.

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases. 

In our last tutorial , we described how to create a new database project based on an existing SQL Server database.  As the source database changes, you will want to update the database project to reflect those changes.  This article describes how to use the Schema Compare tool to import database schema changes into your database project.  The steps in this article are typically performed by a database administrator (DBA) or database developer who is charged with creating tables, views, functions and stored procedures.

The Schema Compare tool can be used to display and manage differences between two databases, between two database projects, or between a database and a database project.  Most of the time, I use it to compare a database with a database project. 

After making a change to a database schema (for example, adding a new table or adding a new column to a table), use the Schema Compare tool as described below to update an existing database project with these changes.

1.        Launch Visual Studio and open your Database Project. (For info on how to create a database project from a SQL Server database see: http://www.davidgiard.com/2008/08/11/DataDudeTutorial1CreatingADatabaseProject.aspx )

2.        From the Visual Studio menu, select Data | Schema Compare | New Schema Compare.  The New Schema Compare dialog displays.

    Figure 1

3.        Under Source Schema, select the Database radio button and select the connection to your database from dropdown list.

4.        Under Target Schema, select the Project radio button and select your database project from the dropdown list.

5.        Click the OK button to display the Schema Compare window.

    Figure 2

6.        The Schema Compare window lists every object that exists in either the database or the database project.  The objects are grouped in folders by object type (Tables, views, stored procedures, etc.)  You can expand or collapse a folder to view or hide objects of that type.  The important column is “Update Action” which describes what will happen if you write the updates to the target.

a.        Objects that exist in the source (the database) but not in the target (the project) were likely added to the database after the last synchronization.  By default, the Update Action will be “Create” meaning the object will be created in the database project.

b.       Objects that exist in both the source and the target will have an Update Action of “Update” if they have been modified in the database since the last synchronization or “Skip” if they have not.

c.        Objects that exist in the destination (the project) but not in the source (the database) were likely dropped from the database after the last synchronization. By default, the Update Action will be “Drop” meaning the object will be removed from the database project.

7.        If you are updating your database project frequently, most of the objects will be unchanged and marked “Skip”.  On a database with many objects, it is useful to view only the objects that have changed since the last synchronization.  To do this, click the Filter toolbar button and select Non Skip Objects .   

    Figure 3

8.        At this point, you can view differences and you may wish to modify the Update Action of some objects.

a.        If you click on an object row in the Schema Compare window, the SQL definition code of both the source and destination version appears in the Object Definition window.  Any differences between the two versions will be highlighted (changed lines in darker blue; new lines in darker green).

b.       If you like, you can modify the Update Action any object by selecting the dropdown in the “Update Action” column.  Some actions are grayed out because Data Dude will not allow you to perform any action that would violate referential integrity rules.  If several developers are sharing the same development database, you may wish to skip those objects on which you are not working.  You may also decide that some objects are ready to share with the rest of the team and others are not fully tested and should be skipped.  It is possible to change the Update Action of every object of a given type by right-clicking the type folder and selecting the desired action to apply to all objects of that type.

9.        After you have set the “Update Action” of every object appropriately, you can migrate your changes to the database project by clicking the Write Updates toolbar button.  Click Yes at the confirmation to write the updates to the database project.

    Figure 4

10.    If you are using a source control repository, such as TFS, you will want to check in your changes.

In the next article, we will discuss how to use the Schema Compare tool to write changes to a new database environment.

Microsoft Visual Studio Team System 2008 Database Edition (aka “Data Dude”) provides tools for managing and deploying SQL Server databases.  In order to use Data Dude to manage an existing SQL Server database, the first step is to create a database project. 

There are a couple key points you will need to know before using Data  Dude.

1.        The current version of Data Dude only works on SQL Server 2000 and SQL Server 2005.  Visual Studio 2008 Service Pack 1 should provide support for SQL Server 2008.  I will describe an example using SQL Server 2005.

2.        The validation engine in Data Dude requires that you install either SQL Server or SQL Express on the same machine on which Data Dude is installed.  

3.        You must grant “Create database” rights in this database engine to the currently logged-in user.

Now, let’s discuss how to create a database project to manage an existing SQL Server 2005 database.

1.        Open Visual Studio.  

2.        Select File | New Project… The New Project dialog displays

3.        Under Project Type, select Database Projects\Microsoft SQL Server

4.        Under Templates, select SQL Server 2005 Wizard.

5.        Enter a meaningful name and location for this project.  
Typically, my databases have names like “AdventureWorks_Dev” and “AdventureWorks_QA” which describe both the data and the environment to which the data belongs.  Because a single database project is used for all environments, I name my database project to describe the data and follow it with “DB” to make it obvious it is a database project.  In the above example, I would name my database project “AdventureWorksDb”.  In this exercise, I’ll create a project named “TestDB”.

  Figure 1

6.        The New Database Project Wizard displays with the Welcome screen active.

7.        At the Welcome screen, click the Next button to advance to the Project Properties screen.

  Figure 2

8.        I almost never change the options on the Project Properties screen.   If my database contains any stored procedures or functions written in C# or VB.Net, I will check the Enable SQLCLR checkbox.  

9.        Click the Next button to advance to the Set Database Options screen.

  Figure 3

10.    The options on the Set Database Options screen correspond to the settings you will find in SQL Server Management Studio when you right-click a database and select Properties.  The defaults in the database project wizard are also the defaults in SQL Server.  Since I seldom override these defaults in SQL Server, there is usually no reason to change them on this screen.

11.    Click the Next button to advance to the Import Database Schema screen.

  Figure 4

12.    On the Import Database Schema screen, check the Import Existing Schema checkbox.  This enables the Source database connection dropdown.  If you already have created a connection to your database, select it from the dropdown.   If you have not yet created a connection, click the New Connection button to create one now.  The process for creating a database connection in Visual Studio hasn’t changed for several versions of the product so I won’t repeat it here.  However it is worth noting that, although Data Dude requires that you have a local installation of SQL Server, the database you connect to here can be located on any server to which you have access.  I always usually connect to the Development database because this is the first database I create for an application.

13.    Click the Next button to advance to the Configure Build and Deploy screen.

  Figure 5

14.    The Configure Build and Deploy screen contains settings that will take effect when you “deploy” your database project.  Deploying a database project writes changes to the schema of a target database (specified in the Target database name field of this screen) and is accomplished by selecting the menu options Build | Deploy with the database project open and selected.  Deploying is most useful when each developer has his own copy of the development database and needs a quick way to synchronize his schema with a master copy.  

15.    Click the Finish button to create the database project initialized with schema objects found in the source database and with the settings you chose in the wizard screens.

16. After Visual Studio finishes creating the database project, view the objects in the Solution Explorer (Select View | Solution Explorer).  You should see a "Schema Objects" folder in the project containing a subfolder for each type of database object.  Open the "tables" subfolder to view you will see files containing scripts for each table in your database.  Double-click one of these script files to see the SQL code generated for you.

    Figure 6

17. If you use a source control repository, such as TFS, you will want to check this project into the repository to make it easier to share with others.

As you can see, when you use the wizard to create your project, most of the work is done for you.  You are able to change the default settings, but in most cases this is not necessary.  Often, the only change I make on the wizard screens is when I select a database connection.

In the next article, we will discuss how to use the Schema Compare tool to bring data changes into or out of your database project.

Visual Studio Team System 2008 Database Edition is a mouthful to say, so a lot of people affectionately call it “Data Dude”.

Data Dude provides a set of tools integrated into Visual Studio that assist developers in managing and deploying SQL Server database objects.

There are four tools in this product that I have found particularly useful: the Database Project; the Schema Compare tool; the Data Compare Tool; and Database Unit Tests.

A Database Project is a Visual Studio project just as a class library or ASP.Net web project is.  However, instead of holding .Net source code, a Database Project holds the source code for database objects, such as tables, views and stored procedures.  This code is typically written in SQL Data Definition Language (DDL).  Storing this code in a Database Project makes it easier to check it into a source code repository such as Team Foundation Server (TFS); and simplifies the process of migrating database objects to other environments.

The Schema Compare tool is most useful when comparing a database with a Visual Studio Database Project.  Developers can use this tool after adding, modifying or deleting objects from a database in order to propagate those changes to a Database Project.  Later, a Database Administrator (DBA) can compare the Database Project to a different database to see what objects have been added, dropped or modified since the last compare.  The DBA can then deploy those changes to the other database.  This is useful for migrating data objects from one environment to another, for example when moving code changes from a Development database to a QA or Production database.

The Data Compare is another tool for migrating from one database environment to the next.  This tool facilitates the migration of records in a given table from one database to another.  The table in both the source and destination database must have the same structure.  I use this when I want to seed values into lookup tables, such as a list of states or a list of valid customer types that are stored in database tables.

Unit tests have increased in popularity the last few years as developers have come to realize their importance in maintaining robust, error-free code.  But unit testing stored procedures is still relatively rare, even though code in stored procedures is no less important than code in .Net assemblies.  Data Dude provides the ability to write unit tests for stored procedures using the same testing framework (MS Test) you used for unit tests of .Net code.  The tests work the same as your other unit tests – you write code and assert what you expect to be true.  Each test passes only if all its assertions are true at runtime.  The only difference is that your code is written in T-SQL, instead of C# or Visual Basic.Net. 

There are some limitations.  In order to use Data Dude, you must have either SQL Server 2008 or SQL Express installed locally on your development machine and you (the logged-in user) must have "Create Database" rights on that local installation.  To my knowledge, Data Dude only works with SQL Server 2000 and 2005 databases.  Plans to integrate with SQL Server 2008 have been announced but I don't know Microsoft's plans for other database engines.   I also occasionally find myself wishing Data Dude could accomplish its tasks more easily or in a more automated fashion.  I wish, for example, I could specify that I always want to ignore database users in a database and always want to migrate everything else when using the Schema Compare tool.  But overall, the tools in this product have increased my productivity significantly.  Nearly every application I write has a database element to it and anything that can help me with database development, management and deployment improves the quality of my applications.