C# SQL Server reporting tool

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
Pete_BurkeAsked:
Who is Participating?
 
ozymandiasConnect With a Mentor Commented:
Yes, you can have a parameters field(s) in the job table so that the required paramnters for the report can be provided.
0
 
ozymandiasCommented:
I have implemented such a thing.
What do you need to know ?
0
 
Pete_BurkeAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ozymandiasCommented:
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
 
Pete_BurkeAuthor Commented:
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
 
ozymandiasCommented:
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
 
Pete_BurkeAuthor Commented:
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
 
ozymandiasCommented:
>>>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
 
Pete_BurkeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.