public string GenerateDataScript() { DataGenType dtype = DataGenType.Insert; if (rbDInsert.Checked) { dtype = DataGenType.Insert; } else if (rbDUpdate.Checked) { dtype = DataGenType.Update; } else if (rbDTruncate.Checked) { dtype = DataGenType.Truncate; } else if (rbDTruncateInsert.Checked) { dtype = DataGenType.TruncateInsert; } ScriptBusiness scriptBusiness = new ScriptBusiness(); var server = new EntityBusiness().GetServerList().Where(x => x.ServerID == Convert.ToInt32(tscomserver.ComboBox.SelectedValue)).First(); string query = scriptBusiness.GenerateTableData(server, dtype, selectedtables); return(query); }
public string GenerateTableData(ServerModel server, DataGenType gentype, List <TableModel> tables) { try { var output = new StringBuilder(); if (gentype == DataGenType.Truncate) { foreach (var t in tables) { var scpt = GetDataHeaderQuery(gentype, t.TableName); output.AppendLine(scpt); } } else { Server srv = new Server(); srv.ConnectionContext.LoginSecure = false; srv.ConnectionContext.Login = server.Username; srv.ConnectionContext.Password = server.Password; srv.ConnectionContext.ServerInstance = server.ServerName; Database genDb = srv.Databases[server.Database]; ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.ScriptData = true; scriptOptions.ScriptSchema = false; Scripter scripter = new Scripter(srv) { Options = scriptOptions }; foreach (var t in tables) { var gen = new StringBuilder(); var tbl = genDb.Tables[t.TableName, "dbo"]; var script = scripter.EnumScript(new SqlSmoObject[] { tbl }); foreach (var line in script) { gen.AppendLine(line); } var scpt = GetDataHeaderQuery(gentype, t.TableName); scpt = scpt.Replace("{query}", gen.ToString()); output.AppendLine(scpt); } } return(output.ToString()); } catch (Exception ex) { throw new Exception(ex.Message); } }
public string GenerateTableData(ServerModel server, DataGenType gentype, List <TableModel> tables) { try { return(scriptData.GenerateTableData(server, gentype, tables)); } catch (Exception ex) { throw new Exception(ex.Message); } }
public string GenerateTableData(ServerModel server, DataGenType gentype, List<TableModel> tables) { try { return scriptData.GenerateTableData(server, gentype, tables); } catch (Exception ex) { throw new Exception(ex.Message); } }
public string GenerateTableData(ServerModel server, DataGenType gentype, List<TableModel> tables) { try { var output = new StringBuilder(); if (gentype == DataGenType.Truncate) { foreach (var t in tables) { var scpt = GetDataHeaderQuery(gentype, t.TableName); output.AppendLine(scpt); } } else { Server srv = new Server(); srv.ConnectionContext.LoginSecure = false; srv.ConnectionContext.Login = server.Username; srv.ConnectionContext.Password = server.Password; srv.ConnectionContext.ServerInstance = server.ServerName; Database genDb = srv.Databases[server.Database]; ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.ScriptData = true; scriptOptions.ScriptSchema = false; Scripter scripter = new Scripter(srv) { Options = scriptOptions }; foreach (var t in tables) { var gen = new StringBuilder(); var tbl = genDb.Tables[t.TableName, "dbo"]; var script = scripter.EnumScript(new SqlSmoObject[] { tbl }); foreach (var line in script) gen.AppendLine(line); var scpt = GetDataHeaderQuery(gentype, t.TableName); scpt = scpt.Replace("{query}", gen.ToString()); output.AppendLine(scpt); } } return output.ToString(); } catch (Exception ex) { throw new Exception(ex.Message); } }
public string GetDataHeaderQuery(DataGenType gentype, string tablename) { StringBuilder sb = new StringBuilder(); try { if (gentype == DataGenType.Insert) { sb.Append("--- Table " + tablename); sb.Append("\n{query}\n\nGO\n"); //sb.Append("--- Table " + tablename); //sb.Append("\n"); //sb.Append("IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); //sb.Append("\nBEGIN\n{query}\nEND\n\nGO\n"); } else if (gentype == DataGenType.Update) { //sb.Append("--- Table " + tablename); //sb.Append("\n"); //sb.Append("IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); //sb.Append("\nBEGIN\n\tDROP TABLE " + tablename); //sb.Append("\nEND\nGO\n\n"); //sb.Append("--- Table " + tablename); //sb.Append("\n"); //sb.Append("\n{query}\n\nGO\n"); } else if (gentype == DataGenType.Truncate) { sb.Append("--- Table " + tablename); sb.Append("\n"); sb.Append("IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); sb.Append("\nBEGIN\n"); sb.Append("\tTRUNCATE TABLE [dbo].[" + tablename + "]"); sb.Append("\nEND\n\nGO\n"); } else if (gentype == DataGenType.TruncateInsert) { sb.Append("--- Table " + tablename); sb.Append("\n"); sb.Append("IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); sb.Append("\nBEGIN\n"); sb.Append("\tTRUNCATE TABLE [dbo].[" + tablename + "]"); sb.Append("\nEND\n\nGO\n"); sb.Append("\n{query}\nGO\n"); } } catch (Exception ex) { throw new Exception(ex.Message); } return(sb.ToString()); }
public string GetDataHeaderQuery(DataGenType gentype, string tablename) { StringBuilder sb = new StringBuilder(); try { if (gentype == DataGenType.Insert) { sb.Append("--- Table " + tablename); sb.Append("\n{query}\n\nGO\n"); //sb.Append("--- Table " + tablename); //sb.Append("\n"); //sb.Append("IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); //sb.Append("\nBEGIN\n{query}\nEND\n\nGO\n"); } else if (gentype == DataGenType.Update) { //sb.Append("--- Table " + tablename); //sb.Append("\n"); //sb.Append("IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); //sb.Append("\nBEGIN\n\tDROP TABLE " + tablename); //sb.Append("\nEND\nGO\n\n"); //sb.Append("--- Table " + tablename); //sb.Append("\n"); //sb.Append("\n{query}\n\nGO\n"); } else if (gentype == DataGenType.Truncate) { sb.Append("--- Table " + tablename); sb.Append("\n"); sb.Append("IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); sb.Append("\nBEGIN\n"); sb.Append("\tTRUNCATE TABLE [dbo].[" + tablename + "]"); sb.Append("\nEND\n\nGO\n"); } else if (gentype == DataGenType.TruncateInsert) { sb.Append("--- Table " + tablename); sb.Append("\n"); sb.Append("IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'AND TABLE_NAME = '" + tablename + "')"); sb.Append("\nBEGIN\n"); sb.Append("\tTRUNCATE TABLE [dbo].[" + tablename + "]"); sb.Append("\nEND\n\nGO\n"); sb.Append("\n{query}\nGO\n"); } } catch (Exception ex) { throw new Exception(ex.Message); } return sb.ToString(); }