public void AddColumn(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var tbd = new TableBuilderDialog(menuInfo.Name, menuInfo.DatabaseInfo.DatabaseType);
tbd.Mode = 1;
if (tbd.ShowModal() != true || tbd.TableColumns.Count != 1)
{
return;
}
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateColumnAddScript(tbd.TableColumns[0]);
var script = generator.GeneratedScript;
OpenSqlEditorToolWindow(menuInfo, script);
DataConnectionHelper.LogUsage("TableBuildColumnAdd");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void ScriptAsData(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateTableContent(menuInfo.Name, false, Properties.Settings.Default.IgnoreIdentityInInsertScript);
if (!Properties.Settings.Default.IgnoreIdentityInInsertScript)
{
generator.GenerateIdentityReset(menuInfo.Name, false);
}
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("TableScriptAsData");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void AddForeignKey(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var fkDlg = new ForeignKeyDialog(menuInfo.Name);
fkDlg.AllColumns = repository.GetAllColumns().ToList();
fkDlg.AllPrimaryKeys = repository.GetAllPrimaryKeys();
if (fkDlg.ShowModal() != true)
{
return;
}
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateForeignKey(fkDlg.NewKey);
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("TableKeyAdd");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
private void GetViews(object sender, RoutedEventArgs args, KeyValuePair <string, DatabaseInfo> database)
{
var viewItem = sender as DatabaseTreeViewItem;
// Prevent loading again and again
if (viewItem != null && (viewItem.Items.Count > 0 && viewItem.Items[0].ToString() == "Loading...") ||
args == null && viewItem != null)
{
List <View> viewList;
try
{
using (var repository = DataConnectionHelper.CreateRepository(database.Value))
{
viewList = repository.GetAllViews();
}
}
catch (Exception e)
{
DataConnectionHelper.SendError(e, database.Value.DatabaseType);
return;
}
Dispatcher.BeginInvoke(new FillViewItemsHandler(FillViewItems), database, viewItem, viewList);
}
if (args != null)
{
args.Handled = true;
}
}
private void GetTableItems(object sender, RoutedEventArgs args, KeyValuePair <string, DatabaseInfo> database)
{
var viewItem = sender as DatabaseTreeViewItem;
Exception ex = null;
// Prevent loading again and again
var doShow = args != null && viewItem != null && (viewItem.Items.Count > 0 && viewItem.Items[0].ToString() == "Loading...") ||
args == null && viewItem != null;
if (doShow)
{
try
{
using (var repository = DataConnectionHelper.CreateRepository(database.Value))
{
repository.ExecuteSql("SELECT 1" + Environment.NewLine + "GO");
}
}
catch (Exception e)
{
ex = e;
}
Dispatcher.BeginInvoke(new FillTableItemsHandler(FillTableItems), database, viewItem, ex, args);
}
if (args != null)
{
args.Handled = true;
}
}
public void ScriptAsSelect(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateTableSelect(menuInfo.Name);
//TODO Something like this for intellisense (maybe a single object)
//OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript, false, repository.GetAllTableNames(), repository.GetAllColumns());
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("TableScriptAsSelect");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
internal void UpdateDescriptions(MenuCommandParameters menuInfo, string name)
{
try
{
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var tableDesc = ExplorerControl.DescriptionCache.Where(d => d.Object == name && d.Parent == null).Select(d => d.Description).SingleOrDefault();
var ro = new DescriptionDialog(tableDesc)
{
ColumnsInfo = GetSiblingColumnInfo(repository, name)
};
ro.ShowModal();
if (!ro.DialogResult.HasValue || ro.DialogResult.Value != true)
{
return;
}
//save table description
new Helpers.DescriptionHelper().SaveDescription(menuInfo.DatabaseInfo, ExplorerControl.DescriptionCache, ro.TableDescription, null, menuInfo.Name);
//save all columns
foreach (var item in ro.ColumnsInfo)
{
new Helpers.DescriptionHelper().SaveDescription(menuInfo.DatabaseInfo, ExplorerControl.DescriptionCache, item.Description, name, item.Name);
}
ExplorerControl.DescriptionCache = new Helpers.DescriptionHelper().GetDescriptions(menuInfo.DatabaseInfo);
((ExplorerControl)ParentWindow.Content).RefreshTables(menuInfo.DatabaseInfo);
DataConnectionHelper.LogUsage("TableUpdateDescriptions");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
private void ParseButton_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrWhiteSpace(SqlText))
{
return;
}
DataConnectionHelper.LogUsage("EditorParse");
try
{
using (var repository = DataConnectionHelper.CreateRepository(DatabaseInfo))
{
var textBox = new TextBox();
textBox.FontFamily = _fontFamiliy;
textBox.FontSize = _fontSize;
string sql = GetSqlFromSqlEditorTextBox();
repository.ParseSql(sql);
textBox.Text = "Statement(s) in script parsed and seems OK!";
ClearResults();
Resultspanel.Children.Add(textBox);
tab2.Focus();
}
}
catch (Exception ex)
{
ParseSqlErrorToResultsBox(DataConnectionHelper.CreateEngineHelper(DatabaseInfo.DatabaseType).FormatError(ex));
}
}
private void ShowPlanButton_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrWhiteSpace(SqlText))
{
return;
}
DataConnectionHelper.LogUsage("EditorShowPlan");
try
{
using (var repository = DataConnectionHelper.CreateRepository(DatabaseInfo))
{
string sql = GetSqlFromSqlEditorTextBox();
string showPlan = repository.ParseSql(sql);
try
{
TryLaunchSqlplan(showPlan, true);
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35);
}
}
}
catch (Exception sqlException)
{
ParseSqlErrorToResultsBox(DataConnectionHelper.CreateEngineHelper(DatabaseInfo.DatabaseType).FormatError(sqlException));
}
}
public void GenerateDatabaseInfo(object sender, ExecutedRoutedEventArgs e)
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
if (package == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, databaseInfo.DatabaseInfo.DatabaseType);
generator.GenerateDatabaseInfo();
SpawnSqlEditorWindow(databaseInfo.DatabaseInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("DatabaseScriptInfo");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, databaseInfo.DatabaseInfo.DatabaseType, false);
}
}
private int GetVersionTableNumber(DatabaseInfo databaseInfo, bool isDesktop)
{
if (isDesktop)
{
return(0);
}
var version = 0;
using (var repository = DataConnectionHelper.CreateRepository(databaseInfo))
{
var list = repository.GetAllTableNames();
if (list.Contains("__VERSION"))
{
var ds = repository.ExecuteSql(@"
SELECT MAX([SchemaVersion]) FROM __VERSION;
GO");
if (ds != null && ds.Tables.Count > 0)
{
version = int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
repository.ExecuteSql(@"
DROP TABLE [__VERSION];
GO");
}
}
return(version);
}
void bw_DoExportWork(object sender, DoWorkEventArgs e)
{
var parameters = e.Argument as List <object>;
if (parameters != null &&
parameters.Count == 5)
{
var dbConnectionString = parameters[0] as string;
var connectionString = parameters[1] as string;
var tables = parameters[2] as List <string>;
// ReSharper disable once AssignNullToNotNullAttribute
var dbType = (DatabaseType)Enum.Parse(typeof(DatabaseType), parameters[3] as string);
// ReSharper disable once AssignNullToNotNullAttribute
var scope = (Scope)Enum.Parse(typeof(Scope), parameters[4] as string);
var scriptRoot = Path.GetTempFileName();
var tempScript = scriptRoot + ".sqlce";
_package.SetStatus("Starting import");
using (var repository = DataConnectionHelper.CreateRepository(new DatabaseInfo {
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
var generator = DataConnectionHelper.CreateGenerator(repository, tempScript, DatabaseType.SQLServer);
generator.ExcludeTables(tables);
_package.SetStatus("Scripting server database...");
generator.ScriptDatabaseToFile(scope);
}
_package.SetStatus("Importing data...");
using (var dbRepository = DataConnectionHelper.CreateRepository(new DatabaseInfo {
ConnectionString = dbConnectionString, DatabaseType = dbType
}))
{
//Handles large exports also...
if (File.Exists(tempScript)) // Single file
{
dbRepository.ExecuteSqlFile(tempScript);
TryDeleteFile(tempScript);
}
else // possibly multiple files - tmp2BB9.tmp_0.sqlce
{
var count = Directory.GetFiles(Path.GetDirectoryName(scriptRoot), Path.GetFileName(scriptRoot) + "*", SearchOption.AllDirectories).Count();
for (var i = 0; i < 400; i++)
{
var testFile = string.Format("{0}_{1}{2}", scriptRoot, i.ToString("D4"), ".sqlce");
if (File.Exists(testFile))
{
dbRepository.ExecuteSqlFile(testFile);
_package.SetProgress("Importing data...", (uint)i + 1, (uint)count - 1);
TryDeleteFile(testFile);
}
}
_package.SetStatus(null);
}
}
}
_package.SetStatus("Import complete");
}
private static void SyncFxGetObjectsForSync(SyncFxDialog sfd, DatabaseMenuCommandParameters databaseInfo)
{
using (var repository = DataConnectionHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
sfd.Tables = repository.GetAllTableNames().Where(t => !t.EndsWith("scope_info") && !t.EndsWith("scope_config") && !t.EndsWith("schema_info") && !t.EndsWith("_tracking")).ToList();
sfd.Columns = repository.GetAllColumns().Where(t => !t.TableName.EndsWith("scope_info") && !t.TableName.EndsWith("scope_config") && !t.TableName.EndsWith("schema_info") && !t.TableName.EndsWith("_tracking")).ToList();
sfd.PrimaryKeyColumns = repository.GetAllPrimaryKeys().Where(t => !t.TableName.EndsWith("scope_info") && !t.TableName.EndsWith("scope_config") && !t.TableName.EndsWith("schema_info") && !t.TableName.EndsWith("_tracking")).ToList();
}
}
public void ImportData(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
var imo = new ImportDialog
{
SampleHeader = generator.GenerateTableColumns(menuInfo.Name),
Separator =
System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator.ToCharArray()[0]
};
if (imo.ShowModal() != true)
{
return;
}
if (string.IsNullOrWhiteSpace(imo.File) || !System.IO.File.Exists(imo.File))
{
return;
}
using (var reader = new CsvReader(imo.File, Encoding.UTF8))
{
reader.ValueSeparator = imo.Separator;
var hr = reader.ReadHeaderRecord();
if (generator.ValidColumns(menuInfo.Name, hr.Values))
{
var i = 1;
foreach (var record in reader.DataRecords)
{
generator.GenerateTableInsert(menuInfo.Name, hr.Values, record.Values, i);
i++;
}
}
}
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("TableImport");
}
}
catch (System.IO.IOException iox)
{
EnvDteHelper.ShowError(iox.Message);
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void ReportTableData(object sender, ExecutedRoutedEventArgs e)
{
string sqlText;
var menuItem = sender as MenuItem;
var ds = new DataSet();
if (menuItem == null)
{
return;
}
var menuInfo = menuItem.CommandParameter as MenuCommandParameters;
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
sqlText = string.Format(Environment.NewLine + "SELECT * FROM [{0}]", menuInfo.Name)
+ Environment.NewLine + "GO";
ds = repository.ExecuteSql(sqlText);
}
var pkg = ParentWindow.Package as SqlCeToolboxPackage;
Debug.Assert(pkg != null, "Package property of the Explorere Tool Window should never be null, have you tried to create it manually and not through FindToolWindow()?");
string dbName = System.IO.Path.GetFileNameWithoutExtension(menuInfo.DatabaseInfo.Caption);
if (dbName != null)
{
var window = pkg.CreateWindow <ReportWindow>(Math.Abs(menuInfo.Name.GetHashCode() - dbName.GetHashCode()));
window.Caption = menuInfo.Name + " (" + dbName + ")";
pkg.ShowWindow(window);
var control = window.Content as ReportControl;
if (control != null)
{
control.DatabaseInfo = menuInfo.DatabaseInfo;
control.TableName = menuInfo.Name;
control.DataSet = ds;
control.ShowReport();
}
}
DataConnectionHelper.LogUsage("ViewReport");
}
catch (System.IO.FileNotFoundException)
{
EnvDteHelper.ShowError("Microsoft Report Viewer 2010 not installed, please download and install to use this feature http://www.microsoft.com/en-us/download/details.aspx?id=6442");
return;
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
ds.Dispose();
}
public void ReportTableData(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
var ds = new DataSet();
if (menuInfo == null)
{
return;
}
try
{
if (!DataConnectionHelper.IsMsReportViewer10Installed())
{
EnvDteHelper.ShowError("Microsoft Report Viewer 2010 not installed, please download and install to use this feature http://www.microsoft.com/en-us/download/details.aspx?id=6442");
return;
}
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateTableSelect(menuInfo.Name);
var sqlText = generator.GeneratedScript;
ds = repository.ExecuteSql(sqlText);
}
var pkg = ParentWindow.Package as SqlCeToolboxPackage;
Debug.Assert(pkg != null, "Package property of the Explorere Tool Window should never be null, have you tried to create it manually and not through FindToolWindow()?");
var dbName = System.IO.Path.GetFileNameWithoutExtension(menuInfo.DatabaseInfo.Caption);
if (dbName != null)
{
var window = pkg.CreateWindow <ReportWindow>(Math.Abs(menuInfo.Name.GetHashCode() - dbName.GetHashCode()));
if (window == null)
{
return;
}
window.Caption = menuInfo.Name + " (" + dbName + ")";
pkg.ShowWindow(window);
var control = window.Content as ReportControl;
if (control != null)
{
control.DatabaseInfo = menuInfo.DatabaseInfo;
control.TableName = menuInfo.Name;
control.DataSet = ds;
control.ShowReport();
}
}
DataConnectionHelper.LogUsage("TableReport");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
ds.Dispose();
}
private void bw_DoExportWork(object sender, DoWorkEventArgs e)
{
var parameters = e.Argument as List <object>;
if (parameters == null)
{
return;
}
var source = parameters[0] as DatabaseInfo;
var target = parameters[1] as DatabaseInfo;
package.SetStatus("Starting export");
using (var repository = DataConnectionHelper.CreateRepository(source))
{
var scriptRoot = Path.GetTempFileName();
var tempScript = scriptRoot + ".sqltb";
var generator = DataConnectionHelper.CreateGenerator(repository, tempScript, DatabaseType.SQLCE40);
package.SetStatus("Scripting local database...");
if (source != null && (source.DatabaseType == DatabaseType.SQLite && Properties.Settings.Default.TruncateSQLiteStrings))
{
generator.TruncateSQLiteStrings = true;
}
generator.ScriptDatabaseToFile(Scope.SchemaData);
using (var serverRepository = DataConnectionHelper.CreateRepository(target))
{
package.SetStatus("Exporting to server...");
//Handles large exports also...
if (File.Exists(tempScript)) // Single file
{
serverRepository.ExecuteSqlFile(tempScript);
}
else // possibly multiple files - tmp2BB9.tmp_0.sqlce
{
var count = Directory.GetFiles(Path.GetDirectoryName(scriptRoot), Path.GetFileName(scriptRoot) + "*", SearchOption.AllDirectories).Count();
for (var i = 0; i < 400; i++)
{
package.SetProgress(null, 0, 0);
var testFile = string.Format("{0}_{1}{2}", scriptRoot, i.ToString("D4"), ".sqltb");
if (File.Exists(testFile))
{
serverRepository.ExecuteSqlFile(testFile);
package.SetProgress("Exporting to server...", (uint)i + 1, (uint)count - 1);
TryDeleteFile(testFile);
}
}
package.SetStatus(null);
}
package.SetStatus("Export complete");
}
}
}
private async void ExecuteSqlScriptInEditor()
{
try
{
StartQuerying();
using (var repository = DataConnectionHelper.CreateRepository(DatabaseInfo))
{
var sql = GetSqlFromSqlEditorTextBox();
var schemaChanged = false;
if (sql.Length == 0)
{
return;
}
var sbSql = new StringBuilder(sql);
sbSql = sbSql.Replace("\r", " \r");
sbSql = sbSql.Replace("GO \r", "GO\r");
sql = sbSql.Replace("GO \r", "GO\r").ToString();
var sw = new Stopwatch();
sw.Start();
var dataset = await Task.Run(()
// ReSharper disable once AccessToDisposedClosure
=> repository.ExecuteSql(sql, out schemaChanged, _ignoreDdlErrors));
sw.Stop();
FormatTime(sw);
if (dataset == null)
{
return;
}
ParseDataSetResultsToResultsBox(dataset);
if (!schemaChanged)
{
return;
}
ExplorerControl?.RefreshTables(DatabaseInfo);
}
}
catch (Exception sqlException)
{
ParseSqlErrorToResultsBox(
DataConnectionHelper.CreateEngineHelper(DatabaseInfo.DatabaseType).FormatError(sqlException));
}
finally
{
StopQuerying();
}
}
public void AddIndex(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
IndexDialog idxDlg = new IndexDialog(menuInfo.Name);
idxDlg.Columns = repository.GetAllColumns().Where(c => c.TableName == menuInfo.Name).ToList();
if (idxDlg.ShowModal() == true)
{
//var generator = Helpers.DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
Index idx = idxDlg.NewIndex;
StringBuilder sbScript = new StringBuilder();
sbScript.Append("CREATE ");
if (idx.Unique)
{
sbScript.Append("UNIQUE ");
}
sbScript.AppendFormat("INDEX [{0}] ON [{1}] (", idx.IndexName, idx.TableName);
sbScript.AppendFormat("[{0}] {1}", idx.ColumnName, idx.SortOrder.ToString());
sbScript.AppendLine(");");
//foreach (Index col in indexes)
//{
// _sbScript.AppendFormat("[{0}] {1},", col.ColumnName, col.SortOrder.ToString());
//}
//// Remove the last comma
//_sbScript.Remove(_sbScript.Length - 1, 1);
//_sbScript.AppendLine(");");
sbScript.Append("GO" + Environment.NewLine);
OpenSqlEditorToolWindow(menuInfo, sbScript.ToString());
DataConnectionHelper.LogUsage("TableIndexAdd");
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void GenerateCeDgmlFiles(object sender, ExecutedRoutedEventArgs e)
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
if (package == null)
{
return;
}
var dte = package.GetServiceHelper(typeof(DTE)) as DTE;
var fd = new SaveFileDialog
{
Title = "Save generated DGML file as",
Filter = "DGML (*.dgml)|*.dgml",
OverwritePrompt = true,
ValidateNames = true
};
var result = fd.ShowDialog();
if (!result.HasValue || result.Value != true)
{
return;
}
var fileName = fd.FileName;
try
{
using (var repository = DataConnectionHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, fileName, databaseInfo.DatabaseInfo.DatabaseType);
generator.GenerateSchemaGraph(databaseInfo.DatabaseInfo.Caption, Properties.Settings.Default.IncludeSystemTablesInDocumentation, false);
if (dte != null)
{
dte.ItemOperations.OpenFile(fileName);
dte.ActiveDocument.Activate();
}
DataConnectionHelper.LogUsage("DatabaseScriptDGML");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35, false);
}
}
private async void ExecuteWithPlanButton_Click(object sender, RoutedEventArgs e)
{
DataConnectionHelper.LogUsage("EditorExecuteWithPlan");
if (string.IsNullOrWhiteSpace(SqlText))
{
return;
}
try
{
StartQuerying();
using (var repository = DataConnectionHelper.CreateRepository(DatabaseInfo))
{
var sql = GetSqlFromSqlEditorTextBox();
string showPlan = string.Empty;
Stopwatch sw = new Stopwatch();
sw.Start();
var dataset = await Task.Run(()
// ReSharper disable once AccessToDisposedClosure
=> repository.ExecuteSql(sql, out showPlan));
sw.Stop();
FormatTime(sw);
if (dataset != null)
{
ParseDataSetResultsToResultsBox(dataset);
}
try
{
TryLaunchSqlplan(showPlan, false);
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35);
}
}
}
catch (Exception ex)
{
ParseSqlErrorToResultsBox(
DataConnectionHelper.CreateEngineHelper(DatabaseInfo.DatabaseType).FormatError(ex));
}
finally
{
StopQuerying();
}
}
public void ModifyColumn(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var menuInfo = menuItem.CommandParameter as MenuCommandParameters;
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
List <Column> columns = repository.GetAllColumns();
var col = columns.SingleOrDefault(c => c.TableName == menuInfo.Description && c.ColumnName == menuInfo.Name);
if (col == null)
{
EnvDteHelper.ShowError("Could not find the column in the table, has it been dropped?");
return;
}
TableBuilderDialog tbd = new TableBuilderDialog(menuInfo.Description, menuInfo.DatabaseInfo.DatabaseType);
tbd.TableColumns = new List <Column> {
col
};
tbd.Mode = 2;
if (tbd.ShowModal() == true && tbd.TableColumns.Count == 1)
{
generator.GenerateColumnAlterScript(tbd.TableColumns[0]);
var script = generator.GeneratedScript;
OpenSqlEditorToolWindow(menuInfo, script);
DataConnectionHelper.LogUsage("TableBuildColumnEdit");
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType);
}
}
public void BuildTable(object sender, ExecutedRoutedEventArgs e)
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, databaseInfo.DatabaseInfo.DatabaseType);
var tbd = new TableBuilderDialog(null, databaseInfo.DatabaseInfo.DatabaseType);
if (tbd.ShowModal() == true)
{
generator.GenerateTableCreate(tbd.TableName, tbd.TableColumns);
var script = generator.GeneratedScript;
if (databaseInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLite)
{
script = script.Remove(script.Length - 6);
if (!string.IsNullOrEmpty(tbd.PkScript))
{
script += tbd.PkScript;
}
script += string.Format("{0});{1}", Environment.NewLine, Environment.NewLine);
}
else
{
if (!string.IsNullOrEmpty(tbd.PkScript))
{
script += tbd.PkScript;
}
}
SpawnSqlEditorWindow(databaseInfo.DatabaseInfo, script);
DataConnectionHelper.LogUsage("TableBuild");
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, databaseInfo.DatabaseInfo.DatabaseType);
}
}
private static void AddRowVersionColumns(DatabaseMenuCommandParameters databaseInfo)
{
using (var repository = DataConnectionHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var list = repository.GetAllTableNames();
var allColumns = repository.GetAllColumns();
foreach (var table in list)
{
if (table.StartsWith("__"))
{
continue;
}
var rowVersionCol = allColumns.SingleOrDefault(c => c.TableName == table && c.DataType == "rowversion");
if (rowVersionCol == null)
{
repository.ExecuteSql(string.Format("ALTER TABLE {0} ADD COLUMN VersionColumn rowversion NOT NULL;{1}GO", table, Environment.NewLine));
}
}
}
}
private void ExecuteSqlScriptInEditor()
{
try
{
using (var repository = DataConnectionHelper.CreateRepository(DatabaseInfo))
{
var sql = GetSqlFromSqlEditorTextBox();
bool schemaChanged;
if (sql.Length == 0)
{
return;
}
var sbSql = new StringBuilder(sql);
sbSql = sbSql.Replace("\r", " \r");
sbSql = sbSql.Replace("GO \r", "GO\r");
sql = sbSql.Replace("GO \r", "GO\r").ToString();
var sw = new Stopwatch();
sw.Start();
var dataset = repository.ExecuteSql(sql, out schemaChanged, _ignoreDdlErrors);
sw.Stop();
FormatTime(sw);
if (dataset == null)
{
return;
}
ParseDataSetResultsToResultsBox(dataset);
if (!schemaChanged)
{
return;
}
if (ExplorerControl != null)
{
ExplorerControl.RefreshTables(DatabaseInfo);
}
}
}
catch (Exception sqlException)
{
ParseSqlErrorToResultsBox(DataConnectionHelper.CreateEngineHelper(DatabaseInfo.DatabaseType).FormatError(sqlException));
}
}
public void ScriptAsCreate(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var menuInfo = menuItem.CommandParameter as MenuCommandParameters;
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
List <Column> columns = repository.GetAllColumns();
var col = columns.SingleOrDefault(c => c.TableName == menuInfo.Description && c.ColumnName == menuInfo.Name);
if (col == null)
{
EnvDteHelper.ShowError("Could not find the column in the table, has it been dropped?");
return;
}
else
{
generator.GenerateColumnAddScript(col);
}
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("ColumnScriptAsCreate");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
// ReSharper disable once InconsistentNaming
public void ScriptAsSQLCLRSample(object sender, ExecutedRoutedEventArgs e)
{
#if SSMS
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
var package = ParentWindow.Package as SqlCeToolboxPackage;
if (package == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateTableCreate(menuInfo.Name);
var sqlClrScript = BuildSqlClrScript(menuInfo, generator.GeneratedScript);
//Add new script to SSMS editor with content
ScriptFactory.Instance.CreateNewBlankScript(ScriptType.Sql);
var dte = package.GetServiceHelper(typeof(DTE)) as DTE;
if (dte != null)
{
var doc = (TextDocument)dte.Application.ActiveDocument.Object(null);
doc.EndPoint.CreateEditPoint().Insert(sqlClrScript);
doc.DTE.ActiveDocument.Saved = true;
}
DataConnectionHelper.LogUsage("TableScriptAsSQLCLRSample");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
#endif
}
public void ScriptAsDelete(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateTableDelete(menuInfo.Name);
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("TableScriptAsDelete");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
private void ExecuteWithPlanButton_Click(object sender, RoutedEventArgs e)
{
DataConnectionHelper.LogUsage("EditorExecuteWithPlan");
if (string.IsNullOrWhiteSpace(SqlText))
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(DatabaseInfo))
{
var sql = GetSqlFromSqlEditorTextBox();
string showPlan;
Stopwatch sw = new Stopwatch();
sw.Start();
var dataset = repository.ExecuteSql(sql, out showPlan);
sw.Stop();
FormatTime(sw);
if (dataset != null)
{
ParseDataSetResultsToResultsBox(dataset);
}
try
{
TryLaunchSqlplan(showPlan, false);
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35);
}
}
}
catch (Exception ex)
{
ParseSqlErrorToResultsBox(DataConnectionHelper.CreateEngineHelper(DatabaseInfo.DatabaseType).FormatError(ex));
}
}
public void ScriptAsSelect(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
var menuInfo = menuItem?.CommandParameter as MenuCommandParameters;
if (menuInfo == null)
{
return;
}
try
{
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateViewSelect(menuInfo.Name);
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("ViewScriptAsSelect");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}