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!
The application doesn't need much preliminaries, so just drive your car!
Using the Code
In here, we have two main classes:
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
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)
OleDbConnection dbConnection = new OleDbConnection();
data source=" + fileName + ";Extended Properties=Excel 8.0;";
StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT * FROM [" + sheetName + "$" + beginCell + ":"
+ endCell + "]");
OleDbDataAdapter adp = new OleDbDataAdapter
DataTable dsXLS = new DataTable();
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()
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)
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
public static IList<string> GetTables
public static IList<string> GetColumns(string serverName, string databaseName,
string userId,string password, bool windowsAuthentication, string tableName)
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:
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.
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.
- 25th May, 2008: Initial version