Crystal Reports' -- Auto-gen'd code files

After designing a Crystal report file (.rpt), a corresponding code file (.cs in my case) will be generated. Typically, if your report is "Inventory.rpt" then the code file will be "Inventory.cs" Sometimes, however, you will get code file pollution (due to VSS issues, copying between machines, etc.) Crystal's engine will auto-gen another file ("Inventory1.cs" in this case) and the original code file will simply be abandoned. Here's the best way I've found to remedy this problem and reset all the code files to the original names:

• Delete all code files associated with report files (in this case, delete Inventory*.cs)
• In Visual Studio, R-click each report file and select the "Run Custom Tool" context menu item
• Rebuild your solution

Pretty simple.

Setting Crystal Report's Database Info at Run-Time

In previous posts I have discussed issues regarding Crystal Reports programming in ASP.NET applications. My most recent problem was, "How can I change a report's data source at run-time?" At design-time, you can use the CR designer's "Set Location" option, but this is impractical because:

• Modifying even just a few reports with this mechanism is time consuming
• Changing a report's data source, although infrequent, is not a one-time event

On my current project, I need to change the data source on reports for these reasons:

• Each developer has different test databases to run the reports against
• Moving the ASP.NET app through Development, Testing, and Production phases requires using different data stores for the reports

These are pretty standard circumstances for any dev team. Fortunately, it was very easy to leverage my EnterpriseDataStore class and set the appropriate information on the report.

SqlConnection conn = EnterpriseDataStore.GetConnection(<DataStore Name>);
CrystalDecisions.Shared.ConnectionInfo connInfo = new ConnectionInfo();
connInfo.DatabaseName = conn.Database;
connInfo.ServerName = conn.DataSource;
// Leave UserID & Password alone if report was designed for Trusted Security (NT Integrated)
connInfo.UserID = <user name>;
connInfo.Password = <user password>;
foreach(CrystalDecisions.CrystalReports.Engine.Table tbl in rpt.Database.Tables) {
    CrystalDecisions.Shared.TableLogOnInfo logOnInfo = tbl.LogOnInfo;
    logOnInfo.ConnectionInfo = connInfo;
    tbl.ApplyLogOnInfo(logOnInfo);
}

The combination is powerful. Now I can change the connection string info as part of the app config and have the reports point to the right data store dynamically. Each developer and app environment (Test, Production) has the config setting in machine.config or web.config, so the code can move seamlessly among all environments.

Crystal Reports: Navigation in ASP.NET

I created a general purpose ASP.NET page for rendering all of my Crystal Reports. This aspx page has a simple toolbar for setting a date range for the report and selections for output type (HTML, Acrobat (PDF), Excel (XLS)). Some time after developing this page I realized that navigating through a multi-page report was not working -- every time I would navigate (using Crystal's toolbar), I would end up on page 1.
To resolve this problem, I only had to make slight modifications: detect the navigation event & skip a bit of code during navigation.

On the page's PreRender event:

• Load the Report Document (.rpt)
• If not navigating (use CRV's Navigate event & set flag>...
  • Set the date range info on the Report Doc
• Set CRV.ReportSource to the Report Doc
• And, of course, call base.OnPreRender to give CRV it's opportunity to do prerender processing

The primary issue of the viewer always rendering page 1 was caused by setting the date info again.

Stored Procedures for DAAB's SqlHelper.UpdateDataset

The Microsoft Data Access Application Block (DAAB) provides a great way to keep .NET DataSet's synchronized with a SQL Server database without tons of code. However, there are a few gotchas, etc. along the way. One of the hurdles is how to persist a new DataSet row (DataRow) to the backend. In particular, if you are using an identity column as the primary key, you'll run into a few problems:

• You can't add a row to the DataTable with a null value for the identity column (since you should have set the DataTable's PrimaryKey to the identity column)
• Using a flag value (e.g., -1) for the new row's identity column works fine until you try to add more than one row. (An exception occurs announcing a primary key violation due to the 2nd new row's identity column being -1)
• Trying to manually update the persisted new row's identity column in the DataSet, although a good first guess, is not a good way to go

The big trick for inserts is to structure the stored procedure (sproc) to update the data, and then return the entire row for the updated item. (Just returning the new identity value doesn't suffice). Consider a simple table: Phonelist (EntryId, Name, PhoneNumber). This table contains three columns:

1. EntryId -- an auto-generated identity column for uniqueness.
2. Name -- a person's name
3. PhoneNumber -- the person's phone number

An appropriate sproc for adding new rows to this table would look like this:

create stored procedure Phonelist
   (@name varchar(50), @phoneNumber varchar(15))
as
   insert into Phonelist (Name, PhoneNumber)
      values (@name, @phoneNumber)

select EntryId, Name, PhoneNumber
   from Phonelist
      where EntryId = @@IDENTITY
return

Notice that each of the columns used to originally populate the DataTable in the DataSet are selected and returned filled with the data just inserted (including the auto-generated identity column). Once you set up the correct SqlCommands for each of the three types of operations (insert, delete, update), then just call SQLHelper.UpdateDataset. The underlying DataSet will auto-magically populate the identity column for the new row based on the row returned from the sproc. Very nice!

One last point: Don't forget to grant Execute permission to the appropriate user account (i.e., \ASPNET).