public void ImportDataFromSQLServerToOracle(string sqlcon, string oraclecon)
{
string[] TableNameFilter = new[] { "Table1", "Table2" };
SchemaMapper.Exporters.OracleExport expOralce = new SchemaMapper.Exporters.OracleExport();
using (SchemaMapper.Converters.SqlServerCeImport ssImport = new SchemaMapper.Converters.SqlServerCeImport(sqlcon))
{
ssImport.getSchemaTable();
foreach (DataRow drRowSchema in ssImport.SchemaTable.AsEnumerable().Where(x =>
TableNameFilter.Contains(x["TABLE_NAME"].ToString())).ToList())
{
string SQLTableName = drRowSchema["TABLE_NAME"].ToString();
string SQLTableSchema = drRowSchema["TABLE_SCHEMA"].ToString();
DataTable dtSQL = ssImport.GetDataTable(SQLTableSchema, SQLTableName);
using (SchemaMapper.SchemaMapping.SchemaMapper sm = new SchemaMapper.SchemaMapping.SchemaMapper(SQLTableSchema, SQLTableName))
{
foreach (DataColumn dc in dtSQL.Columns)
{
SchemaMapper_Column smCol = new SchemaMapper_Column();
smCol.Name = dc.ColumnName;
smCol.Name = dc.ColumnName;
smCol.DataType = smCol.GetCorrespondingDataType(dc.DataType.ToString(), dc.MaxLength);
sm.Columns.Add(smCol);
}
expOralce.CreateDestinationTable(sm, oraclecon);
expOralce.InsertUsingOracleBulk(sm, dtSQL, oraclecon);
//there are other methods such as :
//expOralce.InsertIntoDb(sm, dtSQL, oraclecon);
//expOralce.InsertIntoDbWithParameters(sm, dtSQL, oraclecon);
}
}
}
}
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);
}
