Solved

C# SQL Server reporting tool

Posted on 2006-11-14
9
171 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
Comment Utility
I have implemented such a thing.
What do you need to know ?
0
 

Author Comment

by:Pete_Burke
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Pete_Burke
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 15

Expert Comment

by:ozymandias
Comment Utility
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
Comment Utility
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
Comment Utility
>>>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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now