Excel Converter To Microsoft SQL SERVER

• Download source - 23.85 KB

Introduction

This article is aimed to enrich access to two databases. Through this application, you are able to feasibly convert the Excel database table to the associated table in Microsoft SQL Server. The interface is designed like a wizard that would direct you to the destination very easily. So, let's take off!

Background

The application doesn't need much preliminaries, so just drive your car!

Using the Code

In here, we have two main classes:

1. ExcelManager.cs
2. ServerExplorer.cs

First, we will look at ExcelManager and later jump to ServerExplorer. So fasten your belts, guys!!

Now, this is what we use for managing the Excel side. Let's dip inside and dissect the code. In the code below, there is a method which gets the database filename in Excel and the sheetName, beginCell, endCell in the associated table in our Excel database. The code takes advantage of OLeDb data Provider for accessing the Excel DBMS. That's all what the ExcelManager class does.

public static DataTable GetExcelTable
    (string fileName,string sheetName,string beginCell,string endCell)
        {
            try
            {
                OleDbConnection dbConnection = new OleDbConnection();
                dbConnection.ConnectionString= "provider=Microsoft.Jet.OLEDB.4.0;
                            data source=" + fileName + ";Extended Properties=Excel 8.0;";

                StringBuilder stbQuery = new StringBuilder();
                stbQuery.Append("SELECT * FROM [" + sheetName + "$" + beginCell + ":" 
                                 + endCell + "]");
                OleDbDataAdapter adp = new OleDbDataAdapter
                                       (stbQuery.ToString(), dbConnection);
                DataTable dsXLS = new DataTable();
                adp.Fill(dsXLS);
                return dsXLS;
            }
            catch
            {
                return null;
            }
        }

Now let's take a look at the ServerExplorer class. Actually, this class performs the main operation in our app. Take a look below:

public static IList<string> GetActiveServers()

The method GetActiveServers() takes advantage of SqlDataSourceEnumerator for accessing the Microsoft SQL Server names in a typical LAN Network.

public static IList<string> GetDatabases(string serverName, string userId, 
    string password,bool windowsAuthentication)

The GetDatabases in fact tries to connect to the associated ServerName chosen in the previous method. You are able to connect either Windows Authentication or appropriate UserName and Password.

public static IList<string> GetTables 

public static IList<string> GetColumns(string serverName, string databaseName, 
    string userId,string password, bool windowsAuthentication, string tableName)

The GetTables and GetColumns which are defined above as their names imply do nothing but get the tables in the SQL Server associated database and columns of the tables of the previous ones.

Summary Of This App

In here, I have developed three WinForms:

1. frmConfigExcel
2. frmConfigSql
3. frmConfigTransfer

In frmConfigExcel, by using ExcelManager class I have retrieved the database which we need in Excel and the associated SheetName and Beginning Cell and the End Cell too.

In frmConfigSql, I actually make a connection to the SQL Server database. Eventually, in frmConfigTransfer, I have used a gridview intended for viewing the data retrieved from the associated ExcelDatabase. Now the role is that of selecting the table from the SQL Server database and in the last scene, I chose a column of my SQL Server side and its associated column on the Excel side for converting by clicking the Add button for each. And that's the end of the film. You hit the button "Finish" and can have a coffee now!

About the Author

I am Hamid Moghaddam, .NET Programmer, MCP and own a company, Kahroba Inc, Iran, Yazd.

History

• 25^th May, 2008: Initial version