Suggestions Needed on Database dump to Excel file

ComputerNovice

New member
Joined
Sep 18, 2013
Messages
2
Programming Experience
1-3
Hi,

I'm hoping to get a suggestion or more about a project. All comments welcome.

We have a simple database table, housed on a SQL Server, that holds inventory and location of physical servers. The contents of this database table is usually accessible by our team on an internal website.

Unfortunately, when our site or network is down, our team doesn't have access to this inventory. So we need a solution, when this happens.

I'm wanting to write a C# script, which will be executed from Control-M only once per day, that does the following:

1. Connects to the database and writes the contents of our inventory table (about 1100 records) to an Excel file (preferably csv file.
2. Saves this file to 4 different locations (3 user workstations and one network location).
3. Performs clean-up on files older than 3 days, in the targeted locations, to maintain folder space.

I'll be using Visual Studio 2010 to write the C# script.

Because this is an automated script, I certainly won't be writing a Windows Form Application. So, I'm assuming this script will be written as a Console Application project in Visual Studio? Problem is, I've never written a console app that connects to a database. Most of my labs in school were GUI apps, when connecting and working with databases. And I've certainly never written code that dumps or formats anything into an Excel file (I'm only familiar with doing that in Notepad).

What is the easiest way to connect to the database and pull the data through this automated script? Is it difficult to format the data into Excel?

Any and all suggestions would be appreciated. Thank you!
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,274
Location
Sydney, Australia
Programming Experience
10+
Firstly, there's no such thing as a C# script. C# is not a scripting language. You write C# code and it gets compiled into an executable and that gets executed.

Secondly, I'm not quite sure what you mean by Control-M. You talk about this task being automated so I would think that you'd use the Windows Task Scheduler to execute it.

As for the question, ADO.NET is ADO.NET no matter the type of application. If you know how to access data in a WinForms app then you know how to do it in any .NET app.

With regards to writing the data out, it's got nothing to do with Excel specifically. A CSV file is just a text file. The fact that it will likely be read using Excel is irrelevant to your application. To actually write the data, you could use nested 'for' loops to go through each column of each row and write out the field values followed by commas using a StreamWriter. It really is that easy.
 

ComputerNovice

New member
Joined
Sep 18, 2013
Messages
2
Programming Experience
1-3
Thank you very much. It's very helpful.

I may look into using Task Scheduler instead of setting it up as a Control-M job. Control-M is a job scheduling application that runs on a central server.
 
Top Bottom