private void AgregarButton_Click(object sender, EventArgs e)
{
Comando nuevoComando = (Comando)((Comando)ComandoComboBox.SelectedItem).Clone();
nuevoComando.Valor = ValorTextBox.Text;
nuevoComando.Objetivo = ObjetivoTextBox.Text;
nuevoComando.Descripcion = DescripcionTextBox.Text;
ScriptTextBox.AppendText(nuevoComando.Escribir() + "\n");
}
private void AddVirtualKey(Keys keyCode)
{
if (string.IsNullOrWhiteSpace(ScriptTextBox.Text))
{
ScriptTextBox.AppendText(keyCode.ToString());
}
else
{
ScriptTextBox.AppendText(", ");
ScriptTextBox.AppendText(keyCode.ToString());
}
}
private void DropStoredProcedure(string procName, Schema spSchema)
{
Database db = (Database)DatabasesComboBox.SelectedItem;
if (db.StoredProcedures.Contains(procName, spSchema.Name) == true)
{
ScriptTextBox.AppendText(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.DroppingStoredProcedure,
spSchema.ToString(), BracketObjectName(procName)) +
Environment.NewLine);
ScrollToBottom();
// Drop the existing stored procedure
db.StoredProcedures[procName, spSchema.Name].Drop();
}
}
private void Log(string message)
{
ScriptTextBox.AppendText(message + Environment.NewLine);
}
private void ExecuteButton_Click(object sender, EventArgs e)
{
Cursor csr = null;
Database db;
Schema spSchema;
// Timing
DateTime start = DateTime.Now;
if (System.Windows.Forms.MessageBox.Show(this, Properties.Resources.ReallyCreate,
this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2, 0) == DialogResult.No)
{
return;
}
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Clear control
ScriptTextBox.Clear();
db = (Database)DatabasesComboBox.SelectedItem;
// Create SmoDemo schema to contain stored procedures
if (db.Schemas.Contains("SmoDemo") == false)
{
spSchema = new Schema(db, "SmoDemo");
spSchema.Create();
}
else
{
spSchema = db.Schemas["SmoDemo"];
}
// Limit the table properties returned to just those that we use
SqlServerSelection.SetDefaultInitFields(typeof(Table),
new String[] { "Name" });
// Limit the column properties returned to just those that we use
SqlServerSelection.SetDefaultInitFields(typeof(Column),
new String[] { "Name", "DataType", "Length", "InPrimaryKey" });
// Limit the stored procedure properties returned to just those that we use
SqlServerSelection.SetDefaultInitFields(typeof(StoredProcedure),
new String[] { "Name", "Schema" });
// Create a SELECT stored procedure for each table
foreach (Table tbl in db.Tables)
{
if (db.IsSystemObject == false)
{
CreateSelectProcedure(spSchema, tbl);
}
}
ScriptTextBox.AppendText(Properties.Resources.Completed);
ScrollToBottom();
sbrStatus.Text = Properties.Resources.Ready;
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
// Clean up.
this.Cursor = csr; // Restore the original cursor
// Timing
TimeSpan diff = DateTime.Now - start;
ScriptTextBox.AppendText(String.Format(
System.Threading.Thread.CurrentThread.CurrentCulture,
Environment.NewLine + Properties.Resources.ElapsedTime,
(diff.Minutes * 60) + diff.Seconds, diff.Milliseconds));
}
}
private void CreateSelectProcedure(Schema spSchema, Table tbl)
{
String procName;
StringBuilder sbSQL = new StringBuilder();
StringBuilder sbSelect = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
StoredProcedure sp;
StoredProcedureParameter parm;
try
{
// Create stored procedure name from user entry and table name
procName = PrefixTextBox.Text + tbl.Name + @"Select";
if (DropOnlyCheckBox.CheckState == CheckState.Checked)
{
DropStoredProcedure(procName, spSchema);
}
else
{
DropStoredProcedure(procName, spSchema);
ScriptTextBox.AppendText(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.CreatingStoredProcedure,
spSchema.ToString(), BracketObjectName(procName))
+ Environment.NewLine);
ScrollToBottom();
// Create the new stored procedure object
sp = new StoredProcedure(tbl.Parent, procName, spSchema.Name);
sp.TextMode = false;
foreach (Column col in tbl.Columns)
{
// Select columns
if (sbSelect.Length > 0)
{
sbSelect.Append(", " + Environment.NewLine);
}
// Note: this does not fix object names with embedded brackets
sbSelect.Append("\t[");
sbSelect.Append(col.Name);
sbSelect.Append(@"]");
// Create parameters and where clause from indexed fields
if (col.InPrimaryKey == true)
{
// Parameter columns
parm = new StoredProcedureParameter(sp, "@"
+ col.Name);
parm.DataType = col.DataType;
parm.DataType.MaximumLength
= col.DataType.MaximumLength;
sp.Parameters.Add(parm);
// Where columns
if (sbWhere.Length > 0)
{
sbWhere.Append(" " + Environment.NewLine + "\tAND ");
}
// Note: this does not fix object names with embedded brackets
sbWhere.Append(@"[");
sbWhere.Append(col.Name);
sbWhere.Append(@"] = @");
sbWhere.Append(col.Name);
}
}
// Put where clause into string
if (sbWhere.Length > 0)
{
sbWhere.Insert(0, @"WHERE ");
}
sbrStatus.Text = string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Creating, procName);
sbSQL.Append("SELECT ");
sbSQL.Append(sbSelect);
sbSQL.Append(" " + Environment.NewLine + "FROM ");
sbSQL.Append(tbl.ToString());
sbSQL.Append(" " + Environment.NewLine);
sbSQL.Append(sbWhere);
sp.TextBody = sbSQL.ToString();
sp.Create();
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
// Clean up.
sbSQL = null;
sbSelect = null;
sbWhere = null;
sp = null;
parm = null;
}
}
private void ScriptTableButton_Click(System.Object sender,
System.EventArgs e)
{
Cursor csr = null;
Database db;
StringCollection strColl;
Scripter scrptr;
SqlSmoObject[] smoObjects;
Table tbl;
Int32 count;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Use the selected database
db = (Database)DatabasesComboBox.SelectedItem;
if (db.Name.Length == 0)
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = Properties.Resources.NoDatabaseSelected;
emb.Show(this);
return;
}
// Create scripter object
scrptr = new Scripter(SqlServerSelection);
if (ScriptDropCheckBox.CheckState == CheckState.Checked)
{
scrptr.Options.ScriptDrops = true;
}
else
{
scrptr.Options.ScriptDrops = false;
}
scrptr.DiscoveryProgress +=
new ProgressReportEventHandler(
this.ScriptTable_DiscoveryProgressReport);
scrptr.ScriptingProgress +=
new ProgressReportEventHandler(
this.ScriptTable_ScriptingProgressReport);
if (TablesComboBox.SelectedIndex >= 0)
{
// Get selected table
smoObjects = new SqlSmoObject[1];
tbl = (Table)TablesComboBox.SelectedItem;
if (tbl.IsSystemObject == false)
{
smoObjects[0] = tbl;
}
if (DependenciesCheckBox.CheckState == CheckState.Checked)
{
scrptr.Options.WithDependencies = true;
}
else
{
scrptr.Options.WithDependencies = false;
}
strColl = scrptr.Script(smoObjects);
// Clear control
ScriptTextBox.Clear();
count = 0;
foreach (String str in strColl)
{
count++;
sbrStatus.Text = string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.AppendingScript, count,
strColl.Count);
ScriptTextBox.AppendText(str);
ScriptTextBox.AppendText(Properties.Resources.Go);
}
}
else
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = Properties.Resources.ChooseTable;
emb.Show(this);
}
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
// Clean up
sbrStatus.Text = Properties.Resources.Done;
// Restore the original cursor
this.Cursor = csr;
}
}