Tip 4: SQL Server 2005 Stored Procedures, HTTP Endpoints, and Web Services
While this next tip may seem like "old news" to black-belt SQL developers, many people are just now getting into the new features in SQL Server 2005. Here's a feature that doesn't receive as much attention as it deserves—exposing stored procedures through HTTP endpoints. This means you can make SQL Server 2005 an HTTP listener, and consume your stored procedures as a web service.
I'll do two things to make this happen. First, I'll define an HTTP endpoint in SQL Server 2005. The following code shows where I've exposed the AdventureWorks stored procedure uspGetEmployeeManagers
. Note the names provided for the endpoint, the web method, and the namespace:
// Create HTTP endpoint for a stored procedure
-- Set Endpoint for any request
CREATE ENDPOINT TestEmployeeEndPoint
STATE = STARTED
-- Specify as HTTP
-- Name of Path (virtual path)
PATH = '/Employees', AUTHENTICATION = (INTEGRATED),
-- If using HTTPS, change CLEAR to SSL
PORTS = (CLEAR), site='localhost' )
FOR SOAP (
-- Name of stored proc
WSDL = DEFAULT,
BATCHES = ENABLED,
DATABASE = 'AdventureWorks',
-- endpoint namespace
Second, I'll create a web service reference using the URL http://localhost/Employees?wsdl
. After I do that, I can call the stored procedure as a web service as shown below:
// Test C# code to consume the web service
// .NET code to call stored procedure via the HTTP Endpoint
// Make sure the project contains a .NET web service reference
// to the same endpoint definition from the HTTP endpoint
WS_Employee.TestEmployeeEndPoint oEmployee =
// Must pass in credentials to authenticate to use the service.
// Result comes back as an object array, must cast result
object oResult = oEmployee.uspGetEmployeeManagers(1);
DataSet dtTest = (DataSet)oResult;
In the preceding code, note that it may be necessary to set Credentials
based on the current network credentials. Additionally, the account associated with the web application must have connection permissions to the endpoint.
Tip 5: Executing SSIS Packages from .NET
When I started using SQL Server Integration Services (SSIS) 2005, it was love at first sight. With each passing day, I love it more.
Sometimes I've needed to execute an SSIS 2005 package from within .NET. Listing 1
shows the code you need to execute an SSIS package, regardless of whether the package was deployed to the file system or to SQL Server. Note that you must add a .NET reference to Microsoft.SQLServer.ManagedDTS.dll
Tip 6: Dynamic Email Generation in SSIS 2005
A frequent SSIS question is how to set properties dynamically. For example, SSIS contains an email task so that you can send emails and email attachments after a task has completed. Oftentimes, the email name and attachment file locations will be dynamic, and must be read from variables. Fortunately, this is easy to do and I'll strip it down to the basics.
I'll start by creating SSIS variables for email attachment location and file, as well as Contact information (see Figure 1
). Then in the SSIS Send Mail task, I need to do two things (both in Figure 2
|Figure 1. SSIS Variables: These variables define email attachment location, file names, and contact information.||
|Figure 2. Overriding Send Mail Task Properties: Set DelayValidation to true and map variables to the corresponding properties.||
First, because I want to set email properties for runtime evaluation, I need to set the DelayValidation
property to true
. Second, I can go into the Expression mode and map my variables to the corresponding properties. Note that the email subject line is a concatenation of two variables.
|Figure 3. Script Task Editor in SSIS 2008: As you can see, SSIS 2008 adds the option to change the script language.|
Before leaving the topic of SSIS, here's some important news on SSIS 2008. I've had to write a few SSIS scripts over the last year. In SSIS 2005 scripts support only VB. Alas, I am a C# developer, and while I can write VB code, I (and others) have longed for the ability to write C# code in SSIS scripts. Fortunately, SSIS 2008 allows you to choose which language you want for scripts! Figure 3
demonstrates the language option in SSIS 2008.
Tip 7: Crystal Reports.NET Redux (redux)
No matter how many times I've blogged or written about reusable classes to set data sources in Crystal Reports .NET, many developers still aren't sure about setting runtime data sources for reports in Crystal.
(Before I continue, I want to clarify that by Crystal Reports.NET, I'm referring to the version of Crystal Reports that comes with the higher-end versions of Visual Studio 2005 and 2008.)
In the January/February 2007 issue of CoDe Magazine, I wrote an article that presented a reusable library (cgsCrystalTools
) with some productivity classes for working with Crystal Reports.NET. One of the functions in the main class shows how to push the contents of any DataSet into a Crystal Reports object—the function takes care of all the tables in the main report object, as well as any tables in any sub-report objects. I've reprinted it here:
// Code to return a Crystal Reports.NET object with data
// Primary method, PushReportData, to push a dataset
// into a report object
// Method will handle any subreport objects automatically
public ReportDocument PushReportData(
DataSet DsReportData, ReportDocument oReport)
foreach(ReportDocument oSubReport in oReport.Subreports)
private void SetData(DataSet DsReportData, ReportDocument oReport)
foreach (Table oTable in oReport.Database.Tables)
You can simply create your own class and include this code, and then call the PushReportData
function from your application.
The following code shows how you can push a .NET collection (i.e. a List<>
class) into Crystal Reports:
// Code to supply a custom list to Crystal Reports
// First, create an instance of a List collection
// for the entity
List<TestReportInfo> oList = new List<TestReportInfo>();
// Add some sample data
oList.Add(new TestReportInfo("Kevin Goff", 90000));
oList.Add(new TestReportInfo("John Goff", 95000));
oList.Add(new TestReportInfo("Mike Smith", 99000));
// Create an instance of the report
CrystalReport1 oRpt = new CrystalReport1();
// Finally, set the report data source to the list
Despite the fact that it's possible to build reports in Crystal Reports against custom lists, I still recommend strongly-typed ADO.NET DataSets as the best practice for applications that make moderate to heavy use of Crystal Reports. I've written about the methodology that I use in my book, Pro VS 2005 Reports with Crystal Reports and .NET
. In a nutshell, here's the approach:
- I'll start by designing a physical model for my typed DataSets, in a separate DLL (e.g. DataSets.DLL). If I have many typed DataSets, sometimes I will generate the XSD schemas from SQL Server.
- I'll design the reports using the Crystal Reports designer and point to the XSD for the design-time data source.
- I'll build the reports as a separate DLL (Reports.DLL).
- At run time, I'll set references in my .NET application to both DataSets.DLL and Reports.DLL.
- I'll also set a reference to the Crystal productivity library cgsCrystalTools that I mentioned earlier in this section. This allows me to generate reports to the screen if I'm working in a client/UI tier, or directly to a PDF or other storage if the report is executing in an application layer unattended.
I'll grant that other methodologies will work, though at the cost of a workaround. For instance, using a custom entity class as a design-time data source means that the class (or a proxy) must reside in the same project as the report. So to this day, I continue to recommend strongly-typed DataSets as the best overall approach for reporting applications in .NET.