Solved

C# SQL Server reporting tool

Posted on 2006-11-14
9
174 Views
Last Modified: 2010-04-16
Hi Experts,

I have to create a windows service in C# that will be used to run reports on SQL Server 2000. The reports will be output in either .txt, .xls or xml and delivered via, ftp, email or SMS. Has any body done anything similar and can give advice on how to do efficiently.

Regards,

Pete
0
Comment
Question by:Pete_Burke
  • 5
  • 4
9 Comments
 
LVL 15

Expert Comment

by:ozymandias
ID: 17951404
I have implemented such a thing.
What do you need to know ?
0
 

Author Comment

by:Pete_Burke
ID: 17954115
Hi Ozymandias,

I'm a relative newbie to C# and for this application the customer requirements are vague. Basically what they want is to run reports on SQL Server that are basically selects in stored procedures (about 100 reports).  They want to be able to schedule reports as well as run them on demand.  These reports will be run / scheduled by users in about 10 different locations. They want the reports in various formats such as csv, xml .txt and to be notified via sms that the report has finished.  The user will download the report via ftp.

After discussion with the guys I work with a windows service seems to be the way to do this.  With a GUI that allows the users to select which report to run, enter time parameters and specifiy the output format etc.

Is a windows service the best way to go for this?
If I go with a windows service is it best to do all the processing inside the service possibly multithreaded?
Or to have the service start an application that does the processing?
Are there likely to be problems with 10 or more concurrent users?

Any comments on a better approach or pit falls you ran into would be appreciated.

Regards,

Pete
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 17954346
OK. This is possible but before you begin have you considered that SQL Server has its own job scheduler and mail system.
Could you not write the reports, schedule them and use SQLMail to notify people when the jobs are complete ?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Pete_Burke
ID: 17954833
They want to go down the windows service road.

From my limited knowledge of services it seems that I'll have to write a service control application (user GUI) that will communicate with my service via the windows service control manager.  

From what I've read windows services have a limited number of methods that can be called such as start, stop, pause, OnCustomCommand etc.   OnCustomCommand only accepts an int between 128 and 256, which is very limiting.  

Is there a way I can pass in username, password, sql server name, database name, report name, start time for the report and end time, report format, sms details etc.  to the service and then get the service to use the parameters to run reports on SQL Server.
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 17955423
I would not do that.
I would hava a database that contains instructions to the service in a job table with these fields :

username, password, sql server name, database name, report name, start time for the report and end time, report format, sms details ...etc

The service is configured with timer and periodically checks the job talke to see if there are any jobs to be run and if there are it runs them.

The control applicatoin therefore has to comminucate with the service very little , it just edits the job table, stops, starts, pauses the service and perhaps notifies it if and when changes are made to the job table.

0
 

Author Comment

by:Pete_Burke
ID: 17955553
OK, I think I understand.  

The control application (GUI) would be used by the user to logon, select the report to run and set the start / end datetime of the report. The control application then updates a job table, with these details.  Then the control application starts the service which runs the reports, once complete the control application stops the service.

I guess as you mentioned above it would be more straight forward to use the SQL Server job scheduler and SQLMail controlled via a GUI.  We have a meeting with the customer this pm. Maybe I can talk them out of the windows service idea.
0
 
LVL 15

Expert Comment

by:ozymandias
ID: 17955624
>>>The control application (GUI) would be used by the user to logon, select the report to run and set the start / end datetime of the report. The control application then updates a job table, with these details.  Then the control application starts the service which runs the reports, once complete the control application stops the service.


No. I would not do this.
The service runs all the time.

In the job table you have the details of the job like server, username, password, reportname, email_address, transmission format etc but you also have when the report shuld first run and a recurrence schdule :

e.g. FirstRun = DateTime
       RecurrencePeriod = every hour, day, week etc
       RecurrenceValue = integer
       LastRun = DateTime

The service checks every minute to see if there are any jobs to be run by calculating if the spcificed period has elapsed since the LastRun date.

The jobs could also have  bool field that says whther they are currently active so you can turn them on and off without having to delete them and re-add them etc
0
 

Author Comment

by:Pete_Burke
ID: 17955743
OK, thanks for that.

The users will need to specifiy datetimes to be used in the reports stored procedures selects e.g.

select * from orders where modif_dttm between @StartDttm and @EndDttm.

Can the control application (GUI) be used to update the job table with the @StartDttm and @EndDttm supplied by the user and then run the jobs via the service?
0
 
LVL 15

Accepted Solution

by:
ozymandias earned 500 total points
ID: 17955804
Yes, you can have a parameters field(s) in the job table so that the required paramnters for the report can be provided.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question