public void ExportTableData() { string dbname = ParamsData["database"]; string tablename = ParamsData["table"]; SqlServer server = (SqlServer)sessionData["server"]; server.Connect(); SqlDatabase database = server.Databases[dbname]; SqlTableCollection tables = database.Tables; SqlTable t = tables[tablename]; StringBuilder scriptResult = new StringBuilder(); scriptResult.Append(t.ScriptData(SqlScriptType.Comments)); server.Disconnect(); JsonResult = scriptResult.ToString(); }
private void FilterTablesButton_Click(object sender, System.EventArgs e) { SqlServer server = SqlServer.CurrentServer; try { server.Connect(); } catch (System.Exception ex) { //Response.Redirect("Error.aspx?errorPassCode=" + 2002); Response.Redirect(String.Format("error.aspx?errormsg={0}&stacktrace={1}", Server.UrlEncode(ex.Message), Server.UrlEncode(ex.StackTrace))); } SqlDatabase database = SqlDatabase.CurrentDatabase(server); SqlObjectType objectTypeFilter; switch (TableTypeDropDownList.SelectedIndex) { case 0: objectTypeFilter = SqlObjectType.User; break; case 1: objectTypeFilter = SqlObjectType.User | SqlObjectType.System; break; default: throw new Exception("Invalid TableType selected"); } // Get table list AddNewTableHyperLink.NavigateUrl = String.Format("createtable.aspx?database={0}", Server.UrlEncode(Request["database"])); SqlTableCollection tables = database.Tables; // Create DataSet from result DataSet ds = new DataSet(); ds.Tables.Add(); ds.Tables[0].Columns.Add("name"); ds.Tables[0].Columns.Add("encodedname"); ds.Tables[0].Columns.Add("owner"); ds.Tables[0].Columns.Add("type"); ds.Tables[0].Columns.Add("createdate"); ds.Tables[0].Columns.Add("rows"); for (int i = 0; i < tables.Count; i++) { SqlTable table = tables[i]; // Only add objects that we want (system or user) if ((table.TableType & objectTypeFilter) > 0) { ds.Tables[0].Rows.Add(new object[] { Server.HtmlEncode(table.Name), Server.UrlEncode(table.Name), Server.HtmlEncode(table.Owner), Server.HtmlEncode(table.TableType.ToString()), Server.HtmlEncode(table.CreateDate.ToString()), table.Rows }); } } // Show message if there are no tables, otherwise show datagrid if (ds.Tables[0].Rows.Count == 0) { TablesDataGrid.Visible = false; TableTypeErrorLabel.Visible = true; } else { TableTypeErrorLabel.Visible = false; TablesDataGrid.Visible = true; TablesDataGrid.DataSource = ds; TablesDataGrid.DataBind(); } server.Disconnect(); }
public void ExportAll() { string dbname = ParamsData["database"]; bool scriptDatabase = ParamsData["ScriptDatabase"] == "1"?true:false; bool scriptTableSchema = ParamsData["ScriptTableScheme"] == "1" ? true : false; bool scriptTableData = ParamsData["ScriptTableData"] == "1" ? true : false; bool scriptStoredProcedures = ParamsData["ScriptStoredProcedures"] == "1" ? true : false; bool scriptDrop = ParamsData["ScriptDrop"] == "1" ? true : false; bool scriptComments = ParamsData["ScriptComments"] == "1" ? true : false; SqlServer server = (SqlServer)sessionData["server"]; server.Connect(); SqlDatabase database = server.Databases[dbname]; SqlTableCollection tables = database.Tables; SqlStoredProcedureCollection sprocs = database.StoredProcedures; StringBuilder scriptResult = new StringBuilder(); scriptResult.Append(String.Format("/* Generated by Web Data on {0} */\r\n\r\n", DateTime.Now.ToString())); scriptResult.Append("/* Options selected: "); if (scriptDatabase) { scriptResult.Append("database "); } if (scriptDrop) { scriptResult.Append("drop-commands "); } if (scriptTableSchema) { scriptResult.Append("table-schema "); } if (scriptTableData) { scriptResult.Append("table-data "); } if (scriptStoredProcedures) { scriptResult.Append("stored-procedures "); } if (scriptComments) { scriptResult.Append("comments "); } scriptResult.Append(" */\r\n\r\n"); // Script flow: // DROP and CREATE database // use [database] // GO // DROP sprocs // DROP tables // CREATE tables without constraints // Add table data // Add table constraints // CREATE sprocs // Drop and create database if (scriptDatabase) { scriptResult.Append(database.Script( SqlScriptType.Create | (scriptDrop ? SqlScriptType.Drop : 0) | (scriptComments ? SqlScriptType.Comments : 0))); } // Use database scriptResult.Append(String.Format("\r\nuse [{0}]\r\nGO\r\n\r\n", dbname)); // Drop stored procedures if (scriptStoredProcedures && scriptDrop) { for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { scriptResult.Append(sprocs[i].Script(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Drop tables (this includes schemas and data) if (scriptTableSchema && scriptDrop) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Create table schemas if (scriptTableSchema) { // First create tables with no constraints for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Create table data if (scriptTableData) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptData(scriptComments ? SqlScriptType.Comments : 0)); } } } if (scriptTableSchema) { // Add defaults, primary key, and checks for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Defaults | SqlScriptType.PrimaryKey | SqlScriptType.Checks | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add foreign keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.ForeignKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add unique keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.UniqueKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add indexes for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Indexes | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Create stored procedures if (scriptStoredProcedures) { for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { scriptResult.Append(sprocs[i].Script(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0))); } } } server.Disconnect(); JsonResult = scriptResult.ToString(); }
public void GetDbTable() { try { string dbname = ParamsData["database"]; SqlServer server = (SqlServer)sessionData["server"]; server.Connect(); SqlDatabase database = server.Databases[dbname]; SqlTableCollection tables = database.Tables; SqlStoredProcedureCollection splist = database.StoredProcedures; List <treeNode> tree = new List <treeNode>(); treeNode rootnode = new treeNode(-1, dbname); rootnode.attributes = new Dictionary <string, object>(); rootnode.attributes.Add("type", "db"); rootnode.iconCls = "icon-db"; rootnode.children = new List <treeNode>(); treeNode rootnode2 = new treeNode(-2, "表"); if (tables.Count > 0) { rootnode2.state = "closed"; } rootnode2.attributes = new Dictionary <string, object>(); rootnode2.attributes.Add("type", ""); rootnode2.iconCls = ""; rootnode.children.Add(rootnode2);// //rootnode.children = new List<treeNode>(); treeNode rootnode3 = new treeNode(-3, "存储过程"); if (splist.Count > 0) { rootnode3.state = "closed"; } rootnode3.attributes = new Dictionary <string, object>(); rootnode3.attributes.Add("type", ""); rootnode3.iconCls = ""; rootnode.children.Add(rootnode3);// if (tables.Count > 0) { rootnode2.children = new List <treeNode>(); for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { SqlColumnCollection columns = tables[i].Columns; treeNode node = new treeNode(i, tables[i].Name); if (columns.Count > 0) { node.state = "closed"; } node.attributes = new Dictionary <string, object>(); node.attributes.Add("type", "table"); node.iconCls = "icon-table"; rootnode2.children.Add(node); if (columns.Count > 0) { node.children = new List <treeNode>(); for (int k = 0; k < columns.Count; k++) { string defaultvalue = columns[k].ColumnInformation.DefaultValue == null ? "null" : columns[k].ColumnInformation.DefaultValue; treeNode node2 = new treeNode(k, columns[k].ColumnInformation.Name + "(" + columns[k].ColumnInformation.DataType + "," + defaultvalue + ")"); node2.attributes = new Dictionary <string, object>(); node2.attributes.Add("type", "column"); if (columns[k].ColumnInformation.Key) { node2.iconCls = "icon-key"; } else { node2.iconCls = "icon-column"; } node.children.Add(node2); } } } } } if (splist.Count > 0) { rootnode3.children = new List <treeNode>(); for (int i = 0; i < splist.Count; i++) { if (splist[i].StoredProcedureType == SqlObjectType.StoredProcedure) { treeNode node = new treeNode(i, splist[i].Name); node.attributes = new Dictionary <string, object>(); node.attributes.Add("type", "sp"); rootnode3.children.Add(node); } } } tree.Add(rootnode); server.Disconnect(); JsonResult = ToTreeJson(tree); } catch (Exception err) { JsonResult = RetError("获取数据库相关数据错误://n//r" + err.Message); } }
private void ExportButton_Click(object sender, System.EventArgs e) { lblResult.Text = ""; string databaseName = (string)ExportDatabaseList.SelectedItem.ToString(); bool scriptDatabase = chkDatabase.Checked; bool scriptDrop = this.chkDropCommands.Checked; bool scriptTableSchema = this.chkTableSchemas.Checked; bool scriptTableData = this.chkTableData.Checked; bool scriptStoredProcedures = this.chkStoredProcs.Checked; bool scriptComments = this.chkDescriptiveComments.Checked; SqlServer server = new SqlServer(this.txtServer.Text, this.txtUserName.Text, this.txtPassword.Text); server.Connect(); SqlDatabase database = server.Databases[databaseName]; if (database == null) { server.Disconnect(); // Database doesn't exist - break out and go to error page MessageBox.Show("connection error"); return; } SqlTableCollection tables = database.Tables; SqlStoredProcedureCollection sprocs = database.StoredProcedures; StringBuilder scriptResult = new StringBuilder(); scriptResult.EnsureCapacity(400000); scriptResult.Append(String.Format("/* Generated on {0} */\r\n\r\n", DateTime.Now.ToString())); scriptResult.Append("/* Options selected: "); if (scriptDatabase) { scriptResult.Append("database "); } if (scriptDrop) { scriptResult.Append("drop-commands "); } if (scriptTableSchema) { scriptResult.Append("table-schema "); } if (scriptTableData) { scriptResult.Append("table-data "); } if (scriptStoredProcedures) { scriptResult.Append("stored-procedures "); } if (scriptComments) { scriptResult.Append("comments "); } scriptResult.Append(" */\r\n\r\n"); // Script flow: // DROP and CREATE database // use [database] // GO // DROP sprocs // DROP tables // CREATE tables without constraints // Add table data // Add table constraints // CREATE sprocs // Drop and create database if (scriptDatabase) { scriptResult.Append(database.Script( SqlScriptType.Create | (scriptDrop ? SqlScriptType.Drop : 0) | (scriptComments ? SqlScriptType.Comments : 0))); } // Use database scriptResult.Append(String.Format("\r\nuse [{0}]\r\nGO\r\n\r\n", databaseName)); progressBar1.Value = 20; progressBar1.Refresh(); // Drop stored procedures if (scriptStoredProcedures && scriptDrop) { for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { scriptResult.Append(sprocs[i].Script(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value = 30; progressBar1.Refresh(); // Drop tables (this includes schemas and data) if (scriptTableSchema && scriptDrop) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value = 40; progressBar1.Refresh(); // Create table schemas if (scriptTableSchema) { // First create tables with no constraints for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value = 50; progressBar1.Refresh(); // Create table data if (scriptTableData) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptData(scriptComments ? SqlScriptType.Comments : 0)); } } } progressBar1.Value = 60; progressBar1.Refresh(); if (scriptTableSchema) { // Add defaults, primary key, and checks for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Defaults | SqlScriptType.PrimaryKey | SqlScriptType.Checks | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add foreign keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.ForeignKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } progressBar1.Value = 70; progressBar1.Refresh(); // Add unique keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.UniqueKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add indexes for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Indexes | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value = 80; progressBar1.Refresh(); // Create stored procedures if (scriptStoredProcedures) { string tmpResult = String.Empty; for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { tmpResult = sprocs[i].Script(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0)); scriptResult.Append(tmpResult); tmpResult = ""; } } } server.Disconnect(); progressBar1.Value = 100; progressBar1.Refresh(); scriptResult.Append("/*-----END SCRIPT------*/"); saveFileDialog1.Filter = "Sql files (*.sql)|*.sql|All files (*.*)|*.*"; saveFileDialog1.RestoreDirectory = true; Stream myStream; string theContent = scriptResult.ToString(); if (saveFileDialog1.ShowDialog() == DialogResult.OK) { if ((myStream = saveFileDialog1.OpenFile()) != null) { StreamWriter wText = new StreamWriter(myStream); wText.Write(theContent); wText.Flush(); myStream.Close(); lblResult.Text = "File Saved!"; } } }
protected void ExportButton_Click(object sender, System.EventArgs e) { // Do the export - this will just pop open a Save As dialog box string databaseName = this.ExportDatabaseList.SelectedItem.Text; bool scriptDatabase = this.ScriptDatabaseCheckBox.Checked; bool scriptDrop = this.ScriptDropCheckBox.Checked; bool scriptTableSchema = this.ScriptTableSchemeCheckBox.Checked; bool scriptTableData = this.ScriptTableDataCheckBox.Checked; bool scriptStoredProcedures = this.ScriptStoredProceduresCheckBox.Checked; bool scriptComments = this.ScriptCommentsCheckBox.Checked; SqlServer server = SqlServer.CurrentServer; try { server.Connect(); } catch (System.Exception ex) { //Response.Redirect("Error.aspx?errorPassCode=" + 2002); Response.Redirect(String.Format("error.aspx?errormsg={0}&stacktrace={1}", Server.UrlEncode(ex.Message), Server.UrlEncode(ex.StackTrace))); } SqlDatabase database = server.Databases[databaseName]; if (database == null) { server.Disconnect(); // Database doesn't exist - break out and go to error page Response.Redirect(String.Format("error.aspx?error={0}", 1000)); return; } SqlTableCollection tables = database.Tables; SqlStoredProcedureCollection sprocs = database.StoredProcedures; StringBuilder scriptResult = new StringBuilder(); scriptResult.Append(String.Format("/* Generated by Web Data Administrator on {0} */\r\n\r\n", DateTime.Now.ToString())); scriptResult.Append("/* Options selected: "); if (scriptDatabase) { scriptResult.Append("database "); } if (scriptDrop) { scriptResult.Append("drop-commands "); } if (scriptTableSchema) { scriptResult.Append("table-schema "); } if (scriptTableData) { scriptResult.Append("table-data "); } if (scriptStoredProcedures) { scriptResult.Append("stored-procedures "); } if (scriptComments) { scriptResult.Append("comments "); } scriptResult.Append(" */\r\n\r\n"); // Script flow: // DROP and CREATE database // use [database] // GO // DROP sprocs // DROP tables // CREATE tables without constraints // Add table data // Add table constraints // CREATE sprocs // Drop and create database if (scriptDatabase) { scriptResult.Append(database.Script( SqlScriptType.Create | (scriptDrop ? SqlScriptType.Drop : 0) | (scriptComments ? SqlScriptType.Comments : 0))); } // Use database scriptResult.Append(String.Format("\r\nuse [{0}]\r\nGO\r\n\r\n", databaseName)); // Drop stored procedures if (scriptStoredProcedures && scriptDrop) { for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { scriptResult.Append(sprocs[i].Script(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Drop tables (this includes schemas and data) if (scriptTableSchema && scriptDrop) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Create table schemas if (scriptTableSchema) { // First create tables with no constraints for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Create table data if (scriptTableData) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptData(scriptComments ? SqlScriptType.Comments : 0)); } } } if (scriptTableSchema) { // Add defaults, primary key, and checks for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Defaults | SqlScriptType.PrimaryKey | SqlScriptType.Checks | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add foreign keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.ForeignKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add unique keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.UniqueKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add indexes for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Indexes | (scriptComments ? SqlScriptType.Comments : 0))); } } } // Create stored procedures if (scriptStoredProcedures) { for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { scriptResult.Append(sprocs[i].Script(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0))); } } } server.Disconnect(); Response.Clear(); Response.ClearHeaders(); Response.ClearContent(); // Set the filename to consist of only valid filename chars: [A-Za-z0-9] string filename = ""; for (int i = 0; i < databaseName.Length; i++) { if (IsValidChar(databaseName[i])) { filename += databaseName[i]; } } // This header (RFC 1806) lets us set the suggested filename Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(filename) + "_export.sql"); Response.Write(scriptResult.ToString()); Response.End(); }