Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

C# SQL Server reporting tool

Posted on 2006-11-14
9
Medium Priority
?
180 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

916 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