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); }