Category Archives: .Net

Generating a PDF file from a Reporting Services Report Viewer Control

In yesterday’s post, I demonstrated how to generate a SQL Server Reporting Services report without having to have SQL Server Reporting Services. The sample application used the Microsoft Report Viewer control to display the report. A common need in business is to generate documents, such as PDFs, that will later be archived. It turns out if you are using a report viewer control, this is easy to do programmatically.

First, you need to add one using statement to the top of your class, in addition to the others that were added yesterday.

using System.IO;

Next, we only need a few lines of code to generate the PDF.

      Warning[] warnings;

      string[] streamids;

      string mimeType;

      string encoding;

      string extension;

 

      byte[] bytes = reportViewer1.LocalReport.Render(

        "PDF", null, out mimeType, out encoding, out extension,

        out streamids, out warnings);

 

      FileStream fs = new FileStream(@"D:\ReportOutput.pdf", FileMode.Create);

      fs.Write(bytes, 0, bytes.Length);

      fs.Close();

This code snippet came right from the MSDN Books on Line, and is pretty simple. I could have selected another format by changing the first value passed into the Render method, for example “EXCEL” would have rendered it as a Microsoft Excel document.

In the code samples I placed the above sample in it’s own button, but I could just have easily placed it under one of the other demo buttons.

This ability brings up some interesting possibilities. For example, the report viewer control does not have to be visible to the user in order for this to work. Thus you could create an application that every night generated a series of reports and saved them as PDFs to some central location, such as a web server or document control server. All the user (assuming one was around) would have to see is a progress bar, the reports themselves never get displayed.

Advertisements

Using SQL Server Reporting Services in Client Mode

Recently I did a presentation at the March BSDA meeting. I showed how to use SQL Server Reporting Services without a SQL Server, or more specifically a SQL Server running Reporting Services. It got an enthusiastic response so I thought I’d add to it here by adding some reminder documentation, as much for myself as for all of you wonderful readers.

Using Reporting Services in Client, or Local mode is a 4 step process. First, you will need an XSD schema file to create the report on. Once you have the XSD you will be able to move to the second step, creating the report. Third you will need to place a Report Viewer control on your windows form, WPF form, or ASP.NET page. Finally you will need to write some code that generates an ADO.NET dataset, loads the report in the report viewer control, then binds it all together. Lets look at this step by step.

Normally when you create a report you connect to a database, then base it off of some object like a query, view, or stored procedure. The report is then uploaded to a Reporting Services server, which takes care of hosting it, displaying it, and generating the data for it. With client mode you have no server available, so we have to instead create a surrogate. That’s where our XSD file comes in.

Right click in Solution Explorer and “Add a new item”, and from the list of goodies select “XML Schema”. Name it something appropriate, letting the default extension be XSD. For this example I will be getting customer order data, so I’ve given it the name CustomerOrders.xsd. Visual Studio will think about it then add it to the project, and even helpfully open it for you. I don’t know about you, but hand typing XML Schema’s isn’t my idea of fun, so you should glace at it, go “that’s nice” then close it.

Now right click on the XSD file in Solution Explorer, and pick “Open with….”. In the dialog that appears, select “Dataset Editor”. When you do, Visual Studio presents a big scary warning message letting you know that you could lose contents, and that this will forever be a dataset XSD file. We have nothing in the file, so we’re cool with this, just click OK.

You will now be presented with a big surface area. In the middle it tells you to drag items from the server explorer or right click. If you have a table, view, or stored procedure you are free to drag it in, but most of the time you’ll want to base this off of a SQL query. Right click on the surface, and select Add…., Table Adapter from the menu. The first screen asks you for the database connection. This is the only time you’ll actually need a connection, in this example I am using the good old Northwind database. I pointed at Northwind and clicked next.

image

Next we are asked how we are going to access the data. Since we have a SQL Statement just pick the default of “Use SQL statements” and click Next.

image

Now take your SQL Statement and paste it in, and click Next.

image

OK, click Finish to wrap up the addition of the XSD. By default the adapter has a generic name, we should give ours something more meaningful. Click in the top bar, then enter a new name. Since my example report is for customer order data, I’ll name it CustomerOrders. I then went to the bottom bar and renamed the TableAdapter1 to CustomerOrderTableAdapter. You should now see something like:

image

Note that this will become your Data Source for the report. The data source will have the name of the XSD followed by the name of the source, in this case it will read CustomerOrders_CutomerOrders. OK, now it’s time to create the report.

Go back to Solution Explorer, right click and pick Add New Item. Navigate to the Reporting area and pick Report Wizard. Note the file extension should end in RDLC. If you have used Reporting Services before, you will know that reports typically end in RDL. However, client mode reports have just a slightly different syntax to them, thus the RDLC extension to differentiate the two. While you can modify an RDL to become an RDLC and vice versa, you have to do so by hacking the XML behind the report.

Note you can also choose just Report, but then you’ll have to setup everything manually. For this simple example though, we’ll just use the Report Wizard.

image

Give your report a meaningful name and click Add. The report wizard then shows you a welcoming screen if you’ve never run it before, just click Next.

Now we need to pick the data source. In this example, you want the CustomerOrders branch, so select it and click Next.

image

The next screen asks if we want a Tabular or Matrix report. Select the one for you, in my example I picked Tabular and clicked Next. The next screen asks how we want to display the data. For my example, I opted to group by the customers company name and contact name, then the order data went into the details area. Fill out as appropriate for your report and click Next.

image

The next screen asks how we want things laid out. This affects the look and feel of the report. For my example I just took the default and clicked next, however you are free to play with this to experiment with the different looks and feels your reports might have.

Likewise the next screen is also a look and feel one, asking what colorings we want to apply. Pick one that makes you happy and click next. You can always change it later, many times I pick the Generic one (which adds no colors) then fix it up afterward.

The final screen is the wrap up. Give your report a meaningful name and click Finish.

image

OK, you have a report, now you need a container. Open up the user interface you want to place the report viewer control on. In my example I went with a very simple Windows Forms application.

In my toolbox, I navigated to the Reporting section, where I only found one control, the MicrosoftReportViewer control. (Note I am using Visual Studio 2008 SP1, if you are on an earlier version your names may differ slightly). Grab it and drop it onto your design surface. I also added a Button control to the form to kick off the report display process.

image

Now it’s time for the last step, adding some code. In this example I’ve used a Windows Form. Opening it, the first thing we find in the form load area is::

      this.reportViewer1.RefreshReport();

(Note I left my report viewer control named reportViewer1.) Delete it, we’ll have it refresh elsewhere.

Now we need to add some using statements to the top of our class.

//Add these to the standard list above

using System.Data.Sql;

using System.Data.SqlClient;

using Microsoft.Reporting.WinForms;

The first two will be used in accessing our Northwind database, you may need to use different libraries if you were going to another database. I’ve also included a referenced to the Reporting.WinForms library so we can manipulate the report programmatically.

Now let’s go to the code for the button click event. First, we need to reset the report viewer in case we’d been using it to host another report.

      // Reset in case report viewer was holding another reportViewer1

      reportViewer1.Reset();

Next We need to set the report viewer to local mode. This tells it we’ll be supplying the report name from a local file, and binding the report to a local ADO.NET datasource.

      // Set the processing mode for the ReportViewer to Local

      reportViewer1.ProcessingMode = ProcessingMode.Local;

Our third step is to create a local report variable, and set it’s reference to the report viewer’s local report. This will make it easier to work with. Then we’ll set the location of the report we want to use.

      LocalReport localReport = reportViewer1.LocalReport;

      localReport.ReportPath = @"D:\Presentations\SQL Server\SSRS RDLC\SSRS_RDLC\Report2.rdlc";

Now we need to create an ADO.Net dataset, and populate it. I implemented most of that functionality in a method called GetCustomerOrders, which I’ll append at the bottom of these instructions. It’s very straight forward code.

      DataSet dataset = new DataSet("Northwind");

 

      // Get the sales order data

      GetCustomerOrders(ref dataset);

At this stage we have told it where our report is, and have created the dataset. Now we need to create a datasource for the report itself. We’ll use the ReportDataSource object. For the name, we’ll use the same name as the XSD schema, CustomerOrders_CustomerOrders. Then for the value we will give it the table from the dataset we created in code. It’s possible for a report to have multiple datasets, in the report we’d give each one it’s own name (based on the XSD) then here we’d bind the dataset table to the name we’d used in the report. Once done we will then add the new ReportDataSource to the local reports DataSources collection. Finally, we’ll referesh the report viewer to make it generate the report.

      // Create a report data source for the sales order data

      ReportDataSource dsCustomers = new ReportDataSource();

      dsCustomers.Name = "Customers_Customers";

      dsCustomers.Value = dataset.Tables["Customers"];

 

      localReport.DataSources.Add(dsCustomers);

 

      // Refresh the report

      reportViewer1.RefreshReport();

You can download a copy of these instructions, along with the entire sample project including code and reports, at the Microsoft Code Gallery site http://code.msdn.microsoft.com/SqlServerRSClient . As promised, below is a copy of the GetCustomerOrders routine, for your reference.

    private void GetCustomerOrders(ref DataSet dsNorthwind)

    {

      string sqlCustomerOrders = "SELECT c.[CustomerID]"

        + " ,c.[CompanyName]"

        + " ,c.[ContactName]"

        + " ,c.[ContactTitle]"

        + " ,c.[Address]"

        + " ,c.[City]"

        + " ,c.[Region]"

        + " ,c.[PostalCode]"

        + " ,c.[Country]"

        + " ,c.[Phone]"

        + " ,c.[Fax]"

        + " ,o.[OrderID]"

        + " ,o.[CustomerID]"

        + " ,o.[EmployeeID]"

        + " ,o.[OrderDate]"

        + " ,o.[RequiredDate]"

        + " ,o.[ShippedDate]"

        + " ,o.[ShipVia]"

        + " ,o.[Freight]"

        + " ,o.[ShipName]"

        + " ,o.[ShipAddress]"

        + " ,o.[ShipCity]"

        + " ,o.[ShipRegion]"

        + " ,o.[ShipPostalCode]"

        + " ,o.[ShipCountry]"

        + "  FROM [Northwind].[dbo].[Customers] c"

        + "  join [Northwind].[dbo].[Orders] o on c.CustomerID = o.CustomerID";

 

      SqlConnection connection = new

        SqlConnection("Data Source=(local); " +

                      "Initial Catalog=Northwind; " +

                      "Integrated Security=SSPI");

 

      SqlCommand command =

          new SqlCommand(sqlCustomerOrders, connection);

 

      SqlDataAdapter customerOrdersAdapter = new

          SqlDataAdapter(command);

 

      customerOrdersAdapter.Fill(dsNorthwind, "CustomerOrders");

 

    }

Alabama Code Camp with Speaker Idol

Update: Due to a scheduling conflict, the date of the Alabama Code Camp has been pushed back to January 31st. We apologize for the inconvenience but hope the extra time will make it easier for everyone to plan to attend.

The next Alabama Code Camp will be Saturday January 17th 31st, 2009 in Montgomery Alabama. Call for speakers is open, so check back to the site soon for registration, session schedule, and more.

Also at this event we’ll be having the Alabama Speaker Idol contest. The object of this contest is to encourage new speakers to step up to the Code Camp level. During lunch we’ll be giving new speakers each five minutes to do a presentation. The detailed rules are below, but the big news is the grand prize: a 1 year MSDN Premium Subscription!

Update: Registration for the event is now open, you can register at http://www.clicktoattend.com/?id=134437

Alabama Speaker Idol Rules

1. Presentations are targeted to be five minutes in length, presentations can vary from 4:50 to 5:10 without penalty. Presentations under 4:50 or over 5:10 will be penalized.

2. As the object of the contest is to encourage new speakers, professional speakers, MVPs, and speakers at previous code camps are not eligible. However, someone may participate and also be a speaker at the Jan. 17th 31st, 2009 code camp as long as this is their first time speaking at a code camp.

3. In addition to length, speakers will also be judged on clarity, technical accuracy and content.

4. In addition to the judges, code camp attendees and other members of the pubic may be in the audience during presentations.

5. Judges will be composed of attending MVPs and other notable attendees. As with most of these things, scoring is largely subjective. The result of the judges is final. No whining, crying, or complaining.

6. To participate in the contest you must e-mail your name, title of your presentation, and contact cell number (just in case) to arcanecode@gmail.com with Alabama Speaker Idol in the subject header. All submissions must be received by noon, central standard time, Friday January 16th 30th. (edit to reflect date change)

7. Due to time restrictions, the contest will be limited to 15 participants. In the event more than 15 submissions are received, 15 contestants will be randomly drawn from the pool of submissions. All others will be placed on the stand by list. In the event of a no show on the day of the contest, we will randomly draw a replacement speaker from the standby list.

8. The order of the speakers will be drawn at random just before the presentations begin. All presentations will take place during lunch at the code camp.

9. The winner will be announced at the end of the code camp day.

10. These rules may be amended as the Code Camp Committee deems fit in order to facilitate a more orderly, better code camp.

Oslo – Not just for Norwegians any more

I’ve been looking heavily into Oslo, the new technology announced at PDC 2008. So what exactly is Oslo? Well I couldn’t find a simple explanation, so after digging into it all weekend let me see if I can take a stab at it.

If you are familiar with Sharepoint, you know that it provides you a bunch of web templates. You can take these and create certain types of lists. Documents, lists, forums, etc. What many don’t realize is that all of this gets stored in a “repository” that is in SQL Server.

Oslo takes this concept to the next level. It allows you to create your own “lists” if you will, of fairly complex data types. These are stored in a repository in SQL Server. Along with your data is a lot of meta-data. Oslo also provides a query tool to easily get data back out of the repository, along with runtime components you can use with your favorite programming language. Or, because it’s all in SQL Server you can bypass Oslo runtimes and go directly into the SQL Server repository using traditional tools like ADO.NET or Linq To SQL.

So how does Oslo accomplish this? By providing several tools to us: M the programming language; Quadrant, the graphical tool; and the Repository itself. Lets take a brief look at each one.

M is a new programming language that has three components: MSchema, MGrammer, and MGraph. MSchema is used to define a new chunk of data, it is a representation of how you want the data stored. The product of an MSchema definition is directly translated into T-SQL as a Create Table statement and stored in the Repository.

MGrammer is used to create a translation between one layout of information and the schema created with MSchema. Let’s say you had created an MSchema definition for album names, artists, and ratings. Then let’s say you had an input file that looked something like:

The Thirteenth Hour by Midnight Syndicate rates 5 stars.

Greatest Tuba Hits of 1973 by The Tuba Dudes rates 1 star.

You could create a language template in MGrammer that looked for the words “by” and “rates” and divides up the input into the appropriate fields in your schema. Then run the input file through the MGrammer layout and you’ve now got all that data into a format known as MGraph.

MGraph is a tree like structure that represents the transformed data. If I understand it correctly, you take your data, run it through the DSL you setup with MGrammer and it produces an MGraph. This MGraph can then be loaded into a database schema created with MSchema, passed off to a calling routine, and more.

Quadrant is the tool used to look at data once it’s in the Repository. You can browse data, and create different representations of the data in a tool similar to what you see with Office. For example, you can render a table created by MSchema as a tree, as a grid, as a list, or even as a graph. You can use it to show relationships between MSchemas, and write queries with it. Quadrant could be used by developers or advanced users to create a template representation of the data that could be given to other users to do their data analysis.

Quadrant is also highly extensible and customizable. You can write your own modules to add to it. Although to do so you have to write them in Python, which I have to admit leaves me scratching my head. I don’t have anything against Python, but I would have to imagine most developers who work with Microsoft tools are much more familiar with VB.Net or C#. I have to wonder why they picked a language most Microsoft developers are unfamiliar with and would have to learn in order to extend the Quadrant tool.

The final piece of the puzzle I have mentioned several times, it’s the Repository. The Repository is a database that holds everything about your schemas and data. Currently Oslo only supports SQL Server as the database for a Repository. Interesting thing though, Microsoft will be distributing Oslo under the OSP (Open Specifications Promise). This means a third party vendor could develop a back-end Repository engine so that an Oslo Repository could be stored in something like MySQL or Oracle.

Finally I will mention Oslo will be callable from your favorite .Net language, indeed the Runtime components as they are called are a critical piece of Oslo. There are .Net APIs which can be used to get and retrieve data from the Repository.

Microsoft is serious about Oslo. In a Channel 9 interview about M, I believe it was Chris Anderson who said there were 180 folks working on the Oslo team. Even though it’s early in its development, I get the strong impression Oslo will be a key factor in future of Microsoft development technologies, which is why I intend to invest time now to get up and running with it.

For more information about Oslo, and to download the current Oslo SDK CTP, see the site at http://msdn.microsoft.com/oslo .

SQL Server Compact Edition Connection Strings

In my recent presentation I talked about an important but subtle difference with connection strings when using SQL Server Compact Edition. It was so important I thought I’d make a special blog post out of it.

There are two methods for programmatically accessing data in SQL Server Compact Edition (SSCE). The first method is using the System.Data.SqlServerCe library. When you create an instance of the SqlCeEngine, you need to pass a connection string formatted like so:

DataSource=”mydatabasename.sdf”; Password=’mypassword’

This method is valid, by the way, for version 3.1 or 3.5 of SSCE. The second method, available with Visual Studio 2008 and the 3.5 version of SSCE is to use LINQ to SQL. When creating the DataContext object, you also need a connection string formatted like so:

Data Source=mydatabasename.sdf; Password=’mypassword’

Note very carefully the two differences. First, the name of the sdf file lacks the double quote marks in the LINQ to SQL version. Second, note the Data Source phrase has a space between the words in the LINQ version, where the SqlCeEngine version lacks the space.

It’s a small distinction, but it’ll drive you nuts if you don’t catch it. I drove myself nuts for quite a while because I didn’t notice the extra space in Data Source when I began experimenting with LINQ to SQL! Hopefully my pain will save others some hair pulling.