protected void SaveButton_Click(object sender, System.EventArgs e)
{
SqlServer server = SqlServer.CurrentServer;
server.Connect();
SqlDatabase database = SqlDatabase.CurrentDatabase(server);
string sprocName = Request["sproc"];
bool success = true;
Exception ex = null;
try
{
// Check to see if SProc is new or it already exists
SqlStoredProcedureCollection sprocs = database.StoredProcedures;
SqlStoredProcedure sproc = sprocs[sprocName];
if (sproc == null)
{
// SProc is new, so create entirely new SProc (don't care about return value)
sprocs.Add(sprocName, SProcTextTextbox.Text);
}
else
{
// SProc already exists, just update its text
sproc.Text = SProcTextTextbox.Text;
}
}
catch (Exception ex2)
{
ex = ex2;
success = false;
}
server.Disconnect();
if (success)
{
// Redirect back to SProc page
Response.Redirect("StoredProcedures.aspx?database=" + Server.UrlEncode(Request["database"]));
}
else
{
// show error
ErrorLabel.Visible = true;
ErrorLabel.Text = "There was an error saving the stored procedure.<br>" + Server.HtmlEncode(ex.Message).Replace("\n", "<br>");
}
}
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();
}
protected void FilterSProcsButton_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);
string databaseName = database.Name;
SqlObjectType objectTypeFilter;
switch (SProcTypeDropDownList.SelectedIndex)
{
case 0:
objectTypeFilter = SqlObjectType.User;
break;
case 1:
objectTypeFilter = SqlObjectType.User | SqlObjectType.System;
break;
default:
throw new Exception("Invalid SProcType selected");
}
// Get stored procedure list
AddNewSProcHyperLink.NavigateUrl = String.Format("createstoredprocedure.aspx?database={0}", Server.UrlEncode(Request["database"]));
SqlStoredProcedureCollection sprocs = database.StoredProcedures;
server.Disconnect();
// 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");
for (int i = 0; i < sprocs.Count; i++)
{
SqlStoredProcedure sproc = sprocs[i];
// Only add objects that we want (system or user)
if ((sproc.StoredProcedureType & objectTypeFilter) > 0)
{
ds.Tables[0].Rows.Add(new object[] { Server.HtmlEncode(sproc.Name), Server.UrlEncode(sproc.Name), Server.HtmlEncode(sproc.Owner), Server.HtmlEncode(sproc.StoredProcedureType.ToString()), Server.HtmlEncode(sproc.CreateDate.ToString()) });
}
}
// Show message if there are no tables, otherwise show datagrid
if (ds.Tables[0].Rows.Count == 0)
{
SProcsDataGrid.Visible = false;
SProcTypeErrorLabel.Visible = true;
}
else
{
SProcTypeErrorLabel.Visible = false;
SProcsDataGrid.Visible = true;
SProcsDataGrid.DataSource = ds;
SProcsDataGrid.DataBind();
}
}
