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);
}
}
}
}
}
public override int CreateDestinationTable(SchemaMapper.SchemaMapping.SchemaMapper schmapper)
{
string cmd = BuildCreateTableQuery(schmapper);
int result = 0;
try
{
using (SqlConnection sqlcon = new SqlConnection(ConnectionString))
{
if (sqlcon.State != ConnectionState.Open)
{
sqlcon.Open();
}
using (SqlCommand cmdCreateTable = new SqlCommand(cmd))
{
cmdCreateTable.CommandTimeout = 0;
cmdCreateTable.Connection = sqlcon;
result = cmdCreateTable.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public void ReadFromXml(string path)
{
System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(SchemaMapper));
string strObject = string.Empty;
SchemaMapper smResult = new SchemaMapper();
using (StreamReader sr = new StreamReader(path))
{
strObject = sr.ReadToEnd();
sr.Close();
}
smResult = (SchemaMapper)xs.Deserialize(new StringReader(strObject));
TableName = smResult.TableName.ToString();
SchemaName = smResult.SchemaName.ToString();
Columns.Clear();
IgnoredColumns.Clear();
Variables.Clear();
Columns.AddRange(smResult.Columns);
IgnoredColumns.AddRange(smResult.IgnoredColumns);
foreach (var variable in smResult.Variables)
{
Variables.Add(new Variable(variable.Name, variable.Value));
}
}
public string CreateStoredProcedureQuery(SchemaMapper.SchemaMapping.SchemaMapper schmapper)
{
string cmd = "CREATE PROCEDURE [dbo].[InsertTable]" + Environment.NewLine +
" @myTableType MyTableType readonly" + Environment.NewLine +
" AS" + Environment.NewLine +
" BEGIN" + Environment.NewLine +
" insert into [" + schmapper.SchemaName + "].[" + schmapper.TableName + " ] select * from @myTableType" + Environment.NewLine +
" END";
return(cmd);
}
public SchemaMapper.SchemaMapping.SchemaMapper InitiateTestSchemaMapper(string schema, string table)
{
SchemaMapper.SchemaMapping.SchemaMapper smResult = new SchemaMapper.SchemaMapping.SchemaMapper();
smResult.TableName = table;
smResult.SchemaName = schema;
//Add variables
smResult.Variables.Add(new SchemaMapper.SchemaMapping.Variable("@Today", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
//Add Columns
SchemaMapper.SchemaMapping.SchemaMapper_Column smServerCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("Server_Name",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text);
SchemaMapper.SchemaMapping.SchemaMapper_Column smUserCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("User_Name",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text);
SchemaMapper.SchemaMapping.SchemaMapper_Column smPassCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("Password",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text);
//// Add column with Fixed Value
SchemaMapper.SchemaMapping.SchemaMapper_Column smFixedValueCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("AddedDate",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text,
"@Today");
//// Add Column with Expression
SchemaMapper.SchemaMapping.SchemaMapper_Column smExpressionCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("UserAndPassword",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text,
true,
"[User_Name] + '|' + [Password]");
smResult.Columns.Add(smServerCol);
smResult.Columns.Add(smUserCol);
smResult.Columns.Add(smPassCol);
smResult.Columns.Add(smFixedValueCol);
smResult.Columns.Add(smExpressionCol);
//Add all possible input Columns for each Column
smServerCol.MappedColumns.AddRange(new[] { "server", "server name", "servername", "Server", "Server Name", "ServerName" });
smUserCol.MappedColumns.AddRange(new[] { "UserName", "User", "login", "Login", "User name" });
smPassCol.MappedColumns.AddRange(new[] { "Password", "pass", "Pass", "password" });
//Added columns to ignore if found
//Sys_SheetName and Sys_ExtraFields is an auto generated column when reading Excel file
smResult.IgnoredColumns.AddRange(new[] { "Column1", "Sys_Sheetname", "Sys_ExtraFields", "Center Name" });
//Save Schema Mapper into xml
smResult.WriteToXml(Environment.CurrentDirectory + "\\SchemaMapper\\1.xml", true);
return(smResult);
}
public override string BuildInsertStatementWithParameters(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt)
{
string strQuery = "INSERT INTO [" + schmapper.SchemaName + "].[" + schmapper.TableName + "] (";
string strValues = "";
foreach (DataColumn dc in dt.Columns)
{
strQuery = strQuery + "[" + dc.ColumnName + "],";
strValues += "@" + dc.ColumnName + ",";
}
strQuery = strQuery.TrimEnd(',') + ") VALUES (" + strValues + ")";
return(strQuery);
}
public void InsertToSQLUsingStoredProcedure(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt)
{
try
{
string cmdCreateTableType = CreateTableTypeQuery(schmapper);
string cmdCreateStoredProcdure = CreateStoredProcedureQuery(schmapper);
using (SqlConnection sqlcon = new SqlConnection(ConnectionString))
{
if (sqlcon.State != ConnectionState.Open)
{
sqlcon.Open();
}
//Create Table Type
using (SqlCommand sqlcmd = new SqlCommand(cmdCreateTableType, sqlcon))
{
sqlcmd.CommandTimeout = 300;
sqlcmd.ExecuteNonQuery();
}
//CreateDestinationTable stored procedure
using (SqlCommand sqlcmd = new SqlCommand(cmdCreateStoredProcdure, sqlcon))
{
sqlcmd.CommandTimeout = 300;
sqlcmd.ExecuteNonQuery();
}
//Execute Store procedure
using (SqlCommand sqlcmd = new SqlCommand("InsertTable"))
{
sqlcmd.Connection = sqlcon;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.Add(new SqlParameter("@myTableType", dt));
sqlcmd.ExecuteNonQuery();
}
//Drop Table type and stored procedure if exists
DropObjects(sqlcon);
}
}
catch (Exception ex)
{
throw ex;
}
}
public string CreateTableTypeQuery(SchemaMapper.SchemaMapping.SchemaMapper schmapper)
{
string cmd = "CREATE TYPE [dbo].[MyTableType] AS TABLE(";
foreach (var Col in schmapper.Columns)
{
//only supported data types are text (nvarchar(255)) and memo (nvarchar(4000))
cmd += "[" + Col.Name + "] [" + (Col.DataType == SchemaMapper_Column.ColumnDataType.Text ? "nvarchar](255)" : "nvarchar](4000)") + " NULL ,";
}
cmd = cmd.TrimEnd(',');
cmd += ")";
return(cmd);
}
public override void InsertIntoDb(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt, int rowsperbatch = 10000)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(ConnectionString))
{
if (sqlcon.State != ConnectionState.Open)
{
sqlcon.Open();
}
int totalcount = dt.Rows.Count;
int currentindex = 0;
while (currentindex < totalcount)
{
string strQuery = "";
if ((currentindex + rowsperbatch) >= totalcount)
{
rowsperbatch = totalcount - currentindex - 1;
}
strQuery = BuildInsertStatement(schmapper, dt, currentindex, rowsperbatch);
using (SqlCommand sqlcmd = new SqlCommand(strQuery, sqlcon))
{
sqlcmd.ExecuteNonQuery();
}
currentindex = currentindex + rowsperbatch;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public override string BuildInsertStatement(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt, int startindex, int rowscount)
{
string strQuery = "INSERT INTO [" + schmapper.SchemaName + "].[" + schmapper.TableName + "] (";
foreach (DataColumn dc in dt.Columns)
{
strQuery = strQuery + "[" + dc.ColumnName + "],";
}
strQuery = strQuery.TrimEnd(',') + ") VALUES ";
int i = startindex;
int lastrowindex = startindex + rowscount;
for (i = startindex; i <= lastrowindex; i++)
{
strQuery = strQuery + "(";
foreach (var Col in schmapper.Columns)
{
switch (Col.DataType)
{
case SchemaMapper_Column.ColumnDataType.Date:
strQuery += "'" + ((DateTime)dt.Rows[i][Col.Name]).ToString("yyyy-MM-dd HH:mm:ss") + "',";
break;
case SchemaMapper_Column.ColumnDataType.Text:
case SchemaMapper_Column.ColumnDataType.Memo:
strQuery += "'" + dt.Rows[i][Col.Name].ToString() + "',";
break;
case SchemaMapper_Column.ColumnDataType.Number:
strQuery += dt.Rows[i][Col.Name].ToString() + ",";
break;
}
}
strQuery = strQuery.TrimEnd(',') + "),";
}
strQuery = strQuery.TrimEnd(',');
return(strQuery);
}
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);
}
}
}
}
public void InsertUsingSQLBulk(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt)
{
try
{
using (var bulkCopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = "[" + schmapper.SchemaName + "].[" + schmapper.TableName + "]";
bulkCopy.WriteToServer(dt);
}
}
catch (Exception ex)
{
throw ex;
}
}
public override string BuildCreateTableQuery(SchemaMapper.SchemaMapping.SchemaMapper schmapper)
{
string strQuery = "if not exists(select * from information_schema.tables where table_name = '" + schmapper.TableName +
"' and table_schema = '" + schmapper.SchemaName + "')" +
"create table [" + schmapper.SchemaName + "].[" + schmapper.TableName + "](";
foreach (var Col in schmapper.Columns)
{
switch (Col.DataType)
{
case SchemaMapper_Column.ColumnDataType.Boolean:
strQuery += "[" + Col.Name + "] BIT NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Date:
strQuery += "[" + Col.Name + "] DATETIME NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Text:
strQuery += "[" + Col.Name + "] [nvarchar](255) NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Number:
strQuery += "[" + Col.Name + "] BIGINT NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Memo:
strQuery += "[" + Col.Name + "] [nvarchar](4000) NULL ,";
break;
}
}
strQuery = strQuery.TrimEnd(',');
strQuery += ") ON [PRIMARY]";
return(strQuery);
}
public override string BuildCreateTableQuery(SchemaMapper.SchemaMapping.SchemaMapper schmapper)
{
string strQuery = "create table if not exists `" + schmapper.SchemaName + "`.`" + schmapper.TableName + "`(";
foreach (var Col in schmapper.Columns)
{
switch (Col.DataType)
{
case SchemaMapper_Column.ColumnDataType.Boolean:
strQuery += "\"" + Col.Name + "\" tinyint(1) NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Date:
strQuery += "`" + Col.Name + "` datetime NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Text:
strQuery += "`" + Col.Name + "` varchar(255) NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Number:
strQuery += "`" + Col.Name + "` bigint NULL ,";
break;
case SchemaMapper_Column.ColumnDataType.Memo:
strQuery += "`" + Col.Name + "` varchar(4000) NULL ,";
break;
}
}
strQuery = strQuery.TrimEnd(',');
strQuery += ")";
return(strQuery);
}
public override void InsertIntoDbWithParameters(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt)
{
try
{
using (SqlConnection sqlcon = new SqlConnection(ConnectionString))
{
if (sqlcon.State != ConnectionState.Open)
{
sqlcon.Open();
}
string strQuery = BuildInsertStatementWithParameters(schmapper, dt);
using (SqlTransaction trans = sqlcon.BeginTransaction())
{
using (SqlCommand sqlcmd = new SqlCommand(strQuery, sqlcon, trans))
{
sqlcmd.CommandType = CommandType.Text;
foreach (var Col in schmapper.Columns)
{
switch (Col.DataType)
{
case SchemaMapper_Column.ColumnDataType.Date:
sqlcmd.Parameters.Add("@" + Col.Name, SqlDbType.DateTime);
break;
case SchemaMapper_Column.ColumnDataType.Text:
sqlcmd.Parameters.Add("@" + Col.Name, SqlDbType.VarChar);
break;
case SchemaMapper_Column.ColumnDataType.Memo:
sqlcmd.Parameters.Add("@" + Col.Name, SqlDbType.VarChar, 4000);
break;
case SchemaMapper_Column.ColumnDataType.Number:
sqlcmd.Parameters.Add("@" + Col.Name, SqlDbType.BigInt);
break;
}
}
foreach (DataRow drrow in dt.Rows)
{
foreach (var Col in schmapper.Columns)
{
sqlcmd.Parameters["@" + Col.Name].Value = drrow[Col.Name];
}
sqlcmd.ExecuteNonQuery();
}
trans.Commit();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
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);
}
