In this tutorial, we will be creating a console application to export a database in a readable Microsoft Excel format. One of the most readable data file formats is Tab Delimited. We will be converting a SQL database into this format.
A console application is a good project to create when you need to make a quick tool to modify or display information from a database. Databases contain a lot of information. Unfortunately, this information is in a format that makes it easiest for web applications to manipulate it. SQL databases aren't easy to collect from unless you have the correct knowledge and software. Exporting the data allows users to read the raw database information in a more common database software such as Microsoft Excel.
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
First you can start a new Console Application in Visual Studio 2008. Most people will probably have their own external database they'd like to access, but for this tutorial we will be creating a local one. Make sure you add a Service-Based database or the connection string below will not work correctly. For our example we will add a Table named planets to the database with the columns name varchar(50) and size int. Feel free to enter your own information so we will have something to output at the end of the tutorial. Here is a visual example of our table.
| planets |
| name |
varchar(50) |
| size |
int |
Now we can start working on the programming to export the data. You will need to include several namespaces for this project. I'll list which ones below you will need at the top of your project.
|
using System;
using System.IO;
using System.Data.SqlClient;
|
Next we need to store the path for the exported data file. We will use an if statement with the File.Exists() method to check if the file already exists. This way we won't overwrite anything important by accident. Once the file is known not to exist, we can create a StreamWriter object. This object will allow us to write out to the newly created file made by the File.CreateText() method.
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
|
static void Main(string[] args)
{
string path = @"C:\db.xls";
if (!File.Exists(path))
{
// Create a file to write to.
using (StreamWriter sw = File.CreateText(path))
{
//creating the file contents
}
}
}
|
Next we will need to connect to the database. You can connect to the local database by using the connection string I have listed in the code snippet below. If you are connecting to your own external database make sure your connection string has the correct datasource, username, password, etc. The connection string will be stored in the SqlConnection object cn.
Normally the connection string would be placed in the Web.config file for security reasons, but we have displayed it here to make things easier. Now we need to create the SqlCommand object cmd with our SELECT statement. The open() method is placed inside of a try catch statement in case there is something wrong with our database. Once the connection is established, we will store the results from our SQL statement in the SqlDataReader object dr.
|
using (StreamWriter sw = File.CreateText(path))
{
SqlConnection cn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand("SELECT * FROM planets", cn);
try
{
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
}
catch (Exception excpt)
{
Console.WriteLine(excpt.Message);
}
}
|
Our final steps involve converting the raw database information into a readable Tab Delimited file. The format is extremely simple. Each row is placed on its own line. Each column in each row is separated by tabs. The character symbol for a tab is \t. We will run a loop to go through each row of the database and export each row on its own line with each column separated by \t. This code snippet will be placed directly after the SqlDataReader object dr is created (inside of the try statement).
|
while (dr.Read())
{
sw.WriteLine(dr["name"].ToString() + "\t" + dr["size"].ToString());
}
Console.WriteLine("Database has been exported.");
|
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
Now the exported file will be saved to the path you specified. In our example the file should appear in the C:\ directory under the name db.xls. Now you can open and read the data in Excel. In this example we didn't add the column headers to the Tab Delimited file, but if you had a lot of different columns you might want to write that out to the file before you start the while loop.
Looking for more ASP.NET Database Tutorials? Click Here!