public void ReadAccessIntoSQL()
{
using (SchemaMapper.Converters.MsAccessImport smAccess = new SchemaMapper.Converters.MsAccessImport(@"G:\Passwords.mdb"))
{
//Read Access
smAccess.BuildConnectionString();
smAccess.getSchemaTable();
string con = @"Data Source=.\SQLInstance;Initial Catalog=tempdb;integrated security=SSPI;";
using (SchemaMapper.SchemaMapping.SchemaMapper SM = new SchemaMapper.SchemaMapping.SchemaMapper("dbo", "Passwords"))
{
SM.CreateDestinationTable(con);
foreach (DataRow schRow in smAccess.SchemaTable.Rows)
{
string strTablename = schRow["TABLE_NAME"].ToString().Trim('\'');
DataTable dt = smAccess.GetTableByName(strTablename);
bool result = SM.ChangeTableStructure(ref dt);
if (result == true)
{
SM.InsertToSQLUsingSQLBulk(dt, con);
}
}
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
SchemaMapper.SchemaMapping.SchemaMapper smPasswords = new SchemaMapper.SchemaMapping.SchemaMapper("dbo", "Passwords");
//Define Server_Name , User_Name, Password columns
SchemaMapper_Column smServerCol = new SchemaMapper_Column("Server_Name", SchemaMapper_Column.ColumnDataType.Text);
SchemaMapper_Column smUserCol = new SchemaMapper_Column("User_Name", SchemaMapper_Column.ColumnDataType.Text);
SchemaMapper_Column smPassCol = new SchemaMapper_Column("Password", SchemaMapper_Column.ColumnDataType.Text);
//Define AddedDate column and fill it with a fixed value = Date.Now
SchemaMapper_Column smAddedDate = new SchemaMapper_Column("AddedDate", SchemaMapper_Column.ColumnDataType.Date, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
//Define UserAndPassword column with and expression = [User_Name] + '|' + [Password]
SchemaMapper_Column smUserPasswordCol = new SchemaMapper_Column("UserAndPassword", SchemaMapper_Column.ColumnDataType.Text, true, "[User_Name] + '|' + [Password]");
//Add columns to SchemaMapper
smPasswords.Columns.Add(smServerCol);
smPasswords.Columns.Add(smUserCol);
smPasswords.Columns.Add(smPassCol);
smPasswords.Columns.Add(smAddedDate);
smPasswords.Columns.Add(smUserPasswordCol);
//Add all possible input Columns Names for each Column
smServerCol.MappedColumns.AddRange(new[] { "server", "SQL Instance", "Server Name" });
smUserCol.MappedColumns.AddRange(new[] { "username", "user", "Login" });
smPassCol.MappedColumns.AddRange(new[] { "Password", "pass", "password" });
//Sys_SheetName and Sys_ExtraFields are an auto generated columns while reading Excel file
smPasswords.IgnoredColumns.AddRange(new[] { "ID", "AddedBy", "AddedDate", "Sys_Sheetname", "Sys_ExtraFields" });
//Excel file
DataTable dtExcel;
DataTable dtText;
DataTable dtAccess;
//Excel worksheet
using (SchemaMapper.Converters.MsExcelImport smExcel = new SchemaMapper.Converters.MsExcelImport(@"D:\SchemaMapperTest\Password_Test.xlsx"))
{
//Read Excel
smExcel.BuildConnectionString();
var lst = smExcel.GetSheets();
//Read only from the first worksheet and consider the first row as header
dtExcel = smExcel.GetTableByName(lst.First(), true, 0);
}
//Flat file
using (SchemaMapper.Converters.FlatFileImportTools smFlat = new SchemaMapper.Converters.FlatFileImportTools(@"D:\SchemaMapperTest\Password_Test.txt", true, 0))
{
//Read flat file structure
smFlat.BuildDataTableStructure();
//Import data from flat file
dtText = smFlat.FillDataTable();
}
//Access database
using (SchemaMapper.Converters.MsAccessImport smAccess = new SchemaMapper.Converters.MsAccessImport(@"D:\SchemaMapperTest\Password_Test.accdb"))
{
//Build connection string and retrieve Access metadata
smAccess.BuildConnectionString();
smAccess.getSchemaTable();
//Read data from Passwords table
dtAccess = smAccess.GetTableByName("Passwords");
}
smPasswords.ChangeTableStructure(ref dtExcel);
smPasswords.ChangeTableStructure(ref dtText);
smPasswords.ChangeTableStructure(ref dtAccess);
string connectionstring = @"Data Source=vaio\dataserver;Initial Catalog=tempdb;integrated security=SSPI;";
smPasswords.CreateDestinationTable(connectionstring);
smPasswords.InsertToSQLUsingSQLBulk(dtExcel, connectionstring);
smPasswords.InsertToSQLUsingSQLBulk(dtText, connectionstring);
smPasswords.InsertToSQLUsingSQLBulk(dtAccess, connectionstring);
}