private void ShowPlanButton_Click(object sender, RoutedEventArgs e) { if (string.IsNullOrWhiteSpace(SqlTextBox.Text)) { return; } using (var repository = RepoHelper.CreateRepository(Database)) { var textBox = new TextBox(); textBox.FontFamily = new System.Windows.Media.FontFamily("Consolas"); textBox.FontSize = 14; try { string sql = GetSqlFromSqlEditorTextBox(); string showPlan = repository.ParseSql(sql); if (!string.IsNullOrWhiteSpace(showPlan)) { var fileName = System.IO.Path.GetTempFileName(); fileName = fileName + ".sqlplan"; System.IO.File.WriteAllText(fileName, showPlan); System.Diagnostics.Process.Start(fileName); } } catch (SqlCeException sqlException) { ParseSqlErrorToResultsBox(sqlException); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
private void GetTableItems(object sender, RoutedEventArgs args, KeyValuePair <string, string> database) { var viewItem = sender as DatabaseTreeViewItem; // Prevent loading again and again if (viewItem != null && (viewItem.Items.Count > 0 && viewItem.Items[0].ToString() == "Loading...")) { IList <string> nameList; try { using (var _repository = RepoHelper.CreateRepository(database.Value)) { nameList = _repository.GetAllTableNames(); } DescriptionCache = new Helpers.DescriptionHelper().GetDescriptions(database.Value); } catch (Exception e) { MessageBox.Show("Exception getting table list: " + e.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); return; } Dispatcher.BeginInvoke(new FillTableItemsHandler(FillTableItems), database, viewItem, nameList); } args.Handled = true; }
public void ScriptAsAlter(object sender, ExecutedRoutedEventArgs e) { try { var menuInfo = ValidateMenuInfo(sender); if (menuInfo != null) { using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository, null); List <Column> columns = repository.GetAllColumns(); var col = columns.Where(c => c.TableName == menuInfo.Description && c.ColumnName == menuInfo.Name).SingleOrDefault(); if (col == null) { MessageBox.Show("Could not find the column in the table, has it been dropped?"); } else { generator.GenerateColumnAlterScript(col); } _handler.OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript); } } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } }
private static void TrySave(string connStr) { using (var _repository = RepoHelper.CreateRepository(connStr)) { } DataConnectionHelper.SaveDataConnection(connStr); }
public List <DbDescription> GetDescriptions(string connectionString) { var list = new List <DbDescription>(); string res = string.Empty; using (IRepository repo = RepoHelper.CreateRepository(connectionString)) { var tlist = repo.GetAllTableNames(); if (tlist.Contains(tableName)) { var ds = repo.ExecuteSql(selectScript); if (ds.Tables.Count > 0) { foreach (DataRow row in ds.Tables[0].Rows) { var dbDesc = new DbDescription(); dbDesc.Object = row[0] == DBNull.Value ? null : row[0].ToString(); dbDesc.Parent = row[1] == DBNull.Value ? null : row[1].ToString(); dbDesc.Description = row[2] == DBNull.Value ? null : row[2].ToString(); list.Add(dbDesc); } } } } return(list); }
private int GetVersionTableNumber(string databaseInfo, bool isDesktop) { if (isDesktop) { return(0); } int version = 0; using (IRepository repository = RepoHelper.CreateRepository(databaseInfo)) { var list = repository.GetAllTableNames(); if (list.Contains("__VERSION")) { System.Data.DataSet 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_DoWork(object sender, DoWorkEventArgs e) { SetStatus("Importing data..."); var parameters = e.Argument as List <string>; string sdfConnectionString = parameters[0]; string tempScript = parameters[1]; string scriptRoot = parameters[2]; using (IRepository ce4Repository = RepoHelper.CreateRepository(sdfConnectionString)) { //Handles large exports also... if (File.Exists(tempScript)) // Single file { ce4Repository.ExecuteSqlFile(tempScript); } else // possibly multiple files - tmp2BB9.tmp_0.sqlce { for (int i = 0; i < 400; i++) { string testFile = string.Format("{0}_{1}{2}", scriptRoot, i.ToString("D4"), ".sqlce"); if (File.Exists(testFile)) { ce4Repository.ExecuteSqlFile(testFile); } } } } SetStatus("Database file saved"); }
public void GenerateCeDgmlFiles(object sender, ExecutedRoutedEventArgs e) { var databaseInfo = ValidateMenuInfo(sender); if (databaseInfo == null) { return; } SaveFileDialog fd = new SaveFileDialog(); fd.Title = "Save generated DGML file as"; fd.Filter = "DGML (*.dgml)|*.dgml"; fd.OverwritePrompt = true; fd.ValidateNames = true; bool?result = fd.ShowDialog(); if (result.HasValue && result.Value == true) { var fileName = fd.FileName; try { using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository, fileName); generator.GenerateSchemaGraph(databaseInfo.Caption, Properties.Settings.Default.IncludeSystemTablesInDocumentation, false); MessageBox.Show(string.Format("Saved {0}", fileName)); } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } } }
public void Rename(object sender, ExecutedRoutedEventArgs e) { var menuInfo = ValidateMenuInfo(sender); if (menuInfo != null) { try { using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { RenameDialog ro = new RenameDialog(menuInfo.Name); ro.Owner = Application.Current.MainWindow; ro.ShowDialog(); if (ro.DialogResult.HasValue && ro.DialogResult.Value && !string.IsNullOrWhiteSpace(ro.NewName)) { repository.RenameTable(menuInfo.Name, ro.NewName); if (_parent != null) { _parent.BuildDatabaseTree(); } } } } catch (Exception ex) { MessageBox.Show(DataConnectionHelper.ShowErrors(ex)); } } }
private void ParseButton_Click(object sender, RoutedEventArgs e) { if (string.IsNullOrWhiteSpace(SqlTextBox.Text)) { return; } using (var repository = RepoHelper.CreateRepository(Database)) { var textBox = new TextBox(); textBox.FontFamily = new System.Windows.Media.FontFamily("Consolas"); textBox.FontSize = 14; try { string sql = GetSqlFromSqlEditorTextBox(); string showPlan = repository.ParseSql(sql); textBox.Text = "Statement(s) in script parsed and seems OK!"; this.Resultspanel.Children.Clear(); this.Resultspanel.Children.Add(textBox); } catch (SqlCeException sqlException) { ParseSqlErrorToResultsBox(sqlException); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
public void BuildTable(object sender, ExecutedRoutedEventArgs e) { var databaseInfo = ValidateMenuInfo(sender); if (databaseInfo == null) { return; } try { using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository); TableBuilderDialog tbd = new TableBuilderDialog(null); if (tbd.ShowDialog() == true) { generator.GenerateTableCreate(tbd.TableName, tbd.TableColumns); var script = generator.GeneratedScript.ToString(); if (!string.IsNullOrEmpty(tbd.PkScript)) { script += tbd.PkScript; } OpenSqlEditorToolWindow(databaseInfo, script); } } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } }
public void AddDescription(object sender, ExecutedRoutedEventArgs e) { try { var menuInfo = ValidateMenuInfo(sender); if (menuInfo != null) { using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var desc = ExplorerControl.DescriptionCache.Where(d => d.Object == menuInfo.Name && d.Parent == menuInfo.Description).Select(d => d.Description).SingleOrDefault(); DescriptionDialog ro = new DescriptionDialog(desc); ro.Owner = Application.Current.MainWindow; ro.ShowDialog(); if (ro.DialogResult.HasValue && ro.DialogResult.Value == true && !string.IsNullOrWhiteSpace(ro.Description) && ro.Description != desc) { new Helpers.DescriptionHelper().SaveDescription(menuInfo.Connectionstring, ExplorerControl.DescriptionCache, ro.Description, menuInfo.Description, menuInfo.Name); } } } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } }
public void ScriptDatabase(object sender, ExecutedRoutedEventArgs e) { var menuItem = sender as MenuItem; if (menuItem == null) { return; } var databaseInfo = menuItem.CommandParameter as DatabaseMenuCommandParameters; if (databaseInfo == null) { return; } Scope scope = (Scope)menuItem.Tag; SaveFileDialog fd = new SaveFileDialog(); fd.Title = "Save generated database script as"; fd.Filter = "SQL Server Compact Script (*.sqlce)|*.sqlce|SQL Server Script (*.sql)|*.sql|All Files(*.*)|*.*"; fd.OverwritePrompt = true; fd.ValidateNames = true; bool?result = fd.ShowDialog(); if (result.HasValue && result.Value == true) { var fileName = fd.FileName; int totalCount = 0; PickTablesDialog ptd = new PickTablesDialog(); using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { ptd.Tables = repository.GetAllTableNamesForExclusion(); totalCount = ptd.Tables.Count; } ptd.Owner = Application.Current.MainWindow; bool?res = ptd.ShowDialog(); if (res.HasValue && res.Value == true && (ptd.Tables.Count < totalCount)) { try { using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository, fd.FileName); generator.ExcludeTables(ptd.Tables); System.Windows.Forms.MessageBox.Show(generator.ScriptDatabaseToFile(scope)); } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } } } }
private void ExecuteWithPlanButton_Click(object sender, RoutedEventArgs e) { if (string.IsNullOrWhiteSpace(SqlTextBox.Text)) { return; } try { using (var repository = RepoHelper.CreateRepository(Database)) { var textBox = new TextBox(); textBox.FontFamily = new System.Windows.Media.FontFamily("Consolas"); textBox.FontSize = 14; string sql = GetSqlFromSqlEditorTextBox(); string showPlan = string.Empty; Stopwatch sw = new Stopwatch(); sw.Start(); var dataset = repository.ExecuteSql(sql, out showPlan); sw.Stop(); FormatTime(sw); if (dataset != null) { ParseDataSetResultsToResultsBox(dataset); } try { if (!string.IsNullOrWhiteSpace(showPlan)) { // Just try to start SSMS var fileName = System.IO.Path.GetTempFileName(); fileName = fileName + ".sqlplan"; System.IO.File.WriteAllText(fileName, showPlan); System.Diagnostics.Process.Start(fileName); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } catch (SqlCeException sqlException) { ParseSqlErrorToResultsBox(sqlException); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void UpdateDescription(string description, string parentName, string objectName, string connectionString) { if (string.IsNullOrWhiteSpace(description)) { return; } description = description.Replace("'", "''"); using (IRepository repo = RepoHelper.CreateRepository(connectionString)) { string where = (objectName == null ? "[ObjectName] IS NULL AND " : "[ObjectName] = '" + objectName + "' AND"); where += (parentName == null ? "[ParentName] IS NULL AND [Type] = 0" : "[ParentName] = '" + parentName + "' AND [Type] = 0"); var ds = repo.ExecuteSql(string.Format(updateScript, description, where)); } }
/// <summary> /// This will only be called if the caller (the tree list) knows that the table exists /// </summary> /// <param name="databaseInfo"></param> /// <returns></returns> private string GetDescription(string parentName, string objectName, string connectionString) { string res = string.Empty; using (IRepository repo = RepoHelper.CreateRepository(connectionString)) { string where = (objectName == null ? "[ObjectName] IS NULL" : "[ObjectName] = '" + objectName + "' AND"); where += (parentName == null ? "[ParentName] IS NULL" : "[ParentName] = '" + parentName + "' AND [Type] = 0"); var ds = repo.ExecuteSql(string.Format(selectSingleScript, where)); if (ds.Tables.Count > 0) { return(ds.Tables[0].Rows[0][0].ToString()); } } return(res); }
private void FillTableItems(KeyValuePair <string, string> database, DatabaseTreeViewItem parentItem, IList <string> nameList) { parentItem.Items.Clear(); if (DescriptionCache != null) { var dbdesc = DescriptionCache.Where(dc => dc.Parent == null && dc.Object == null).Select(dc => dc.Description).SingleOrDefault(); if (!string.IsNullOrWhiteSpace(dbdesc)) { if (parentItem.Parent is DatabaseTreeViewItem) { var dbItem = (DatabaseTreeViewItem)parentItem.Parent; dbItem.ToolTip = dbItem.ToolTip.ToString() + Environment.NewLine + dbdesc; } } } using (var _repository = RepoHelper.CreateRepository(database.Value)) { var columns = _repository.GetAllColumns(); foreach (var table in nameList) { if (!Properties.Settings.Default.DisplayDescriptionTable && table.Equals("__ExtendedProperties")) { continue; } var item = TreeViewHelper.CreateTreeViewItemWithImage(table, "../Resources/table.png", true, null, true); item.ContextMenu = new TableContextMenu(new MenuCommandParameters { Connectionstring = database.Value, Name = table, MenuItemType = MenuCommandParameters.MenuType.Table, Caption = database.Key }, this); item.ToolTip = table; if (DescriptionCache != null) { var desc = DescriptionCache.Where(dc => dc.Parent == null && dc.Object == table).Select(dc => dc.Description).SingleOrDefault(); if (!string.IsNullOrWhiteSpace(desc)) { item.ToolTip = desc; } } var tableColumns = (from col in columns where col.TableName == table select col).ToList <Column>(); parentItem.Items.Add(item); item.Expanded += (s, e) => GetTableColumns(s, e, tableColumns, database); } } }
private void AddDescription(string description, string parentName, string objectName, string connectionString) { if (string.IsNullOrWhiteSpace(description)) { return; } using (IRepository repo = RepoHelper.CreateRepository(connectionString)) { CreateExtPropsTable(repo); string sql = string.Format(insertScript, (parentName == null ? "NULL" : "'" + parentName + "'"), (objectName == null ? "NULL" : "'" + objectName + "'"), description.Replace("'", "''")); repo.ExecuteSql(sql); } }
private static void AddRowVersionColumns(DatabaseMenuCommandParameters databaseInfo) { using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { var list = repository.GetAllTableNames(); var allColumns = repository.GetAllColumns(); foreach (var table in list) { if (!table.StartsWith("__")) { var rowVersionCol = allColumns.Where(c => c.TableName == table && c.DataType == "rowversion").SingleOrDefault(); if (rowVersionCol == null) { repository.ExecuteSql(string.Format("ALTER TABLE {0} ADD COLUMN VersionColumn rowversion NOT NULL;{1}GO", table, Environment.NewLine)); } } } } }
public void ImportData(object sender, ExecutedRoutedEventArgs e) { var menuInfo = ValidateMenuInfo(sender); if (menuInfo != null) { try { using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository, string.Empty); ImportDialog imo = new ImportDialog(); imo.SampleHeader = generator.GenerateTableColumns(menuInfo.Name); imo.Separator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator.ToCharArray()[0]; imo.Owner = Application.Current.MainWindow; if (imo.ShowDialog() == true) { using (var reader = new CsvReader(imo.File, System.Text.Encoding.UTF8)) { reader.ValueSeparator = imo.Separator; HeaderRecord hr = reader.ReadHeaderRecord(); if (generator.ValidColumns(menuInfo.Name, hr.Values)) { int i = 1; foreach (DataRecord record in reader.DataRecords) { generator.GenerateTableInsert(menuInfo.Name, hr.Values, record.Values, i); i++; } } } OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript); } } } catch (Exception ex) { MessageBox.Show(DataConnectionHelper.ShowErrors(ex)); } } }
public void ScriptAsStatistics(object sender, ExecutedRoutedEventArgs e) { var menuItem = sender as MenuItem; if (menuItem != null) { var menuInfo = menuItem.CommandParameter as MenuCommandParameters; if (menuInfo != null) { using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository, null); generator.GenerateIndexStatistics(menuInfo.Name, menuInfo.Caption); OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript); } } } }
public void ScriptAsData(object sender, ExecutedRoutedEventArgs e) { try { var menuInfo = ValidateMenuInfo(sender); if (menuInfo != null) { using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository); generator.GenerateTableContent(menuInfo.Name, false, Properties.Settings.Default.IgnoreIdentityInInsertScript); OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript); } } } catch (Exception ex) { MessageBox.Show(DataConnectionHelper.ShowErrors(ex)); } }
public void ScriptAsSelect(object sender, ExecutedRoutedEventArgs e) { try { var menuInfo = ValidateMenuInfo(sender); if (menuInfo != null) { using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository); generator.GenerateTableSelect(menuInfo.Name); OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript); } } } catch (Exception ex) { MessageBox.Show(DataConnectionHelper.ShowErrors(ex)); } }
private void ExecuteSqlScriptInEditor() { Debug.Assert(!string.IsNullOrEmpty(Database), "Database property of this control has not been set by parent window or control"); using (var repository = RepoHelper.CreateRepository(Database)) { try { var sql = GetSqlFromSqlEditorTextBox(); if (string.IsNullOrWhiteSpace(sql)) { return; } sql = sql.Replace("\r", " \r"); sql = sql.Replace("GO \r", "GO\r"); sql = sql.Replace("GO \r", "GO\r"); Stopwatch sw = new Stopwatch(); sw.Start(); var dataset = repository.ExecuteSql(sql); sw.Stop(); FormatTime(sw); if (dataset != null) { ParseDataSetResultsToResultsBox(dataset); } } catch (SqlCeException sqlException) { ParseSqlErrorToResultsBox(sqlException); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }
private void GetTableColumns(object sender, RoutedEventArgs args, List <Column> columns, KeyValuePair <string, string> database) { var viewItem = sender as DatabaseTreeViewItem; // Prevent loading again and again if (viewItem != null && (viewItem.Items.Count > 0 && viewItem.Items[0].ToString() == "Loading...")) { var tableName = viewItem.MetaData; viewItem.Items.Clear(); using (var _repository = RepoHelper.CreateRepository(database.Value)) { // If the node is being refreshed by the user, make sure to reload the columns instead of using the cached ones from the // previous load of the entire database tree if (viewItem.IsRefreshing) { columns = _repository.GetAllColumns().Where(x => x.TableName == tableName).ToList(); } var pkList = _repository.GetAllPrimaryKeys().Where(p => p.TableName == tableName); var pks = (from pk in pkList select pk.ColumnName).ToList <string>(); var fkList = _repository.GetAllForeignKeys().Where(fk => fk.ConstraintTableName == tableName).ToList(); foreach (var column in columns) { var display = column.ShortType; var image = "../Resources/column.png"; var constraints = (from fk in fkList where fk.Columns.Contains(column.ColumnName) select fk); if (constraints.Count() > 0) { display = "FK, " + display; image = "../Resources/fk.png"; } if (pks.Contains(column.ColumnName)) { display = "PK, " + display; image = "../Resources/key.png"; } string nullable = " not null)"; if (column.IsNullable == YesNoOption.YES) { nullable = " null)"; } display = column.ColumnName + " (" + display + nullable; var i = TreeViewHelper.CreateTreeViewItemWithImage(display, image, false); i.ContextMenu = new ColumnContextMenu(new MenuCommandParameters { Description = tableName, Connectionstring = database.Value, Name = column.ColumnName, MenuItemType = MenuCommandParameters.MenuType.Table }, this); i.ToolTip = column.ColumnName; if (DescriptionCache != null) { var desc = DescriptionCache.Where(dc => dc.Parent == tableName && dc.Object == column.ColumnName).Select(dc => dc.Description).SingleOrDefault(); if (!string.IsNullOrWhiteSpace(desc)) { i.ToolTip = desc; } } viewItem.Items.Add(i); } var indexesItem = TreeViewHelper.CreateTreeViewItemWithImage("Indexes", "../Resources/folder.png", true); indexesItem.Items.Clear(); string oldName = string.Empty; foreach (var primaryKey in pkList) { if (oldName != primaryKey.KeyName) { var display = primaryKey.KeyName + " (Primary Key)"; var indexItem = TreeViewHelper.CreateTreeViewItemWithImage(display, "../Resources/index.png", false); indexItem.ContextMenu = new IndexContextMenu(new MenuCommandParameters { Connectionstring = database.Value, Name = viewItem.MetaData, MenuItemType = MenuCommandParameters.MenuType.Table, Caption = primaryKey.KeyName }, this); indexItem.ToolTip = primaryKey.KeyName; indexesItem.Items.Add(indexItem); oldName = primaryKey.KeyName; } } oldName = string.Empty; var indexes = _repository.GetIndexesFromTable(viewItem.MetaData); foreach (var index in indexes) { if (oldName != index.IndexName) { var display = string.Empty; if (index.Unique) { display = index.IndexName + " (Unique)"; } else { display = index.IndexName + " (Non-Unique)"; } var indexItem = TreeViewHelper.CreateTreeViewItemWithImage(display, "../Resources/index.png", false); indexItem.ContextMenu = new IndexContextMenu(new MenuCommandParameters { Connectionstring = database.Value, Name = viewItem.MetaData, MenuItemType = MenuCommandParameters.MenuType.Table, Caption = index.IndexName }, this); indexItem.ToolTip = index.IndexName; indexesItem.Items.Add(indexItem); oldName = index.IndexName; } } viewItem.Items.Add(indexesItem); } } args.Handled = true; }
public void GenerateDiffScript(object sender, ExecutedRoutedEventArgs e) { var databaseInfo = ValidateMenuInfo(sender); if (databaseInfo == null) { return; } try { SortedDictionary <string, string> databaseList = Helpers.DataConnectionHelper.GetDataConnections(); foreach (KeyValuePair <string, string> info in databaseList) { if (!databaseList.ContainsKey(info.Key)) { databaseList.Add(info.Key, info.Value); } } CompareDialog cd = new CompareDialog(databaseInfo.Caption, databaseList); bool?result = cd.ShowDialog(); if (result.HasValue && result.Value == true && (cd.TargetDatabase.Key != null)) { var target = cd.TargetDatabase.Value; var source = databaseInfo.Connectionstring; var editorTarget = target; using (IRepository sourceRepository = RepoHelper.CreateRepository(source)) { var generator = RepoHelper.CreateGenerator(sourceRepository); using (IRepository targetRepository = RepoHelper.CreateRepository(target)) { try { SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, generator, Properties.Settings.Default.DropTargetTables); string explain = @"-- This database diff script contains the following objects: -- - Tables: Any that are not in the destination -- - (tables that are only in the destination are not dropped) -- - Columns: Any added, deleted, changed columns for existing tables -- - Indexes: Any added, deleted indexes for existing tables -- - Foreign keys: Any added, deleted foreign keys for existing tables -- ** Make sure to test against a production version of the destination database! ** " + Environment.NewLine + Environment.NewLine; databaseInfo.Connectionstring = cd.TargetDatabase.Value; databaseInfo.Caption = cd.TargetDatabase.Key; OpenSqlEditorToolWindow(databaseInfo, explain + generator.GeneratedScript); } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } } } } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } }
public void GenerateDataContext(object sender, ExecutedRoutedEventArgs e) { var databaseInfo = ValidateMenuInfo(sender); if (databaseInfo == null) { return; } bool isDesktop = false; if ((bool)((MenuItem)sender).Tag == true) { isDesktop = true; } SqlCeHelper helper = new SqlCeHelper(); if (!helper.IsV35DbProviderInstalled()) { MessageBox.Show("This feature requires the SQL Server Compact 3.5 SP2 runtime & DbProvider to be installed"); return; } string sqlMetalPath = (string)Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v7.0A\WinSDK-NetFx40Tools", "InstallationFolder", null); if (string.IsNullOrEmpty(sqlMetalPath)) { sqlMetalPath = (string)Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v8.0A", "InstallationFolder", string.Empty) + "bin\\NETFX 4.0 Tools\\"; if (string.IsNullOrEmpty(sqlMetalPath)) { MessageBox.Show("Could not find SQLMetal location in registry"); return; } } sqlMetalPath = Path.Combine(sqlMetalPath, "sqlmetal.exe"); if (!File.Exists(sqlMetalPath)) { MessageBox.Show("Could not find SqlMetal in the expected location: " + sqlMetalPath); return; } string sdfFileName = string.Empty; string fileName = string.Empty; SaveFileDialog fd = new SaveFileDialog(); fd.Title = "Save Data Context as"; fd.Filter = "C# code (*.cs)|*.cs|VB code|*.vb"; fd.OverwritePrompt = true; fd.ValidateNames = true; bool?fdresult = fd.ShowDialog(); if (fdresult.HasValue && fdresult.Value == true) { fileName = fd.FileName; } if (string.IsNullOrEmpty(fileName)) { return; } try { using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { var tables = repository.GetAllTableNames(); var pks = repository.GetAllPrimaryKeys(); string checkedTables = string.Empty; foreach (string tableName in tables) { var pk = pks.Where(k => k.TableName == tableName).FirstOrDefault(); if (pk.TableName == null) { checkedTables += tableName + Environment.NewLine; } } if (!string.IsNullOrEmpty(checkedTables)) { string message = string.Format("The tables below do not have Primary Keys defined,{0}and will not be generated properly:{1}{2}", Environment.NewLine, Environment.NewLine, checkedTables); MessageBox.Show(message); } List <KeyValuePair <string, string> > dbInfo = repository.GetDatabaseInfo(); foreach (var kvp in dbInfo) { if (kvp.Key == "Database") { sdfFileName = kvp.Value; break; } } sdfFileName = Path.GetFileName(sdfFileName); } string model = Path.GetFileNameWithoutExtension(databaseInfo.Caption).Replace(" ", string.Empty).Replace("#", string.Empty).Replace(".", string.Empty).Replace("-", string.Empty); model = model + "Context"; DataContextDialog dcDialog = new DataContextDialog(); dcDialog.ModelName = model; dcDialog.IsDesktop = isDesktop; dcDialog.NameSpace = string.Empty; dcDialog.CodeLanguage = "C#"; bool?result = dcDialog.ShowDialog(); if (result.HasValue && result.Value == true && (!string.IsNullOrWhiteSpace(dcDialog.ModelName))) { if (dcDialog.AddRowversionColumns) { AddRowVersionColumns(databaseInfo); } string sdfPath = databaseInfo.Connectionstring; #if V35 #else //If version 4.0, create a 3.5 schema sdf, and use that as connection string if (isDesktop) { var tempFile = Path.GetTempFileName(); using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository, tempFile); generator.ScriptDatabaseToFile(Scope.Schema); } sdfPath = Path.Combine(Path.GetTempPath(), sdfFileName); if (File.Exists(sdfPath)) { File.Delete(sdfPath); } sdfPath = "Data Source=" + sdfPath; helper.CreateDatabase(sdfPath); using (IRepository repository = new DBRepository(sdfPath)) { string script = File.ReadAllText(tempFile); repository.ExecuteSql(script); } } #endif int versionNumber = GetVersionTableNumber(databaseInfo.Connectionstring, isDesktop); model = dcDialog.ModelName; string dcPath = fileName; string parameters = " /provider:SQLCompact /code:\"" + dcPath + "\""; parameters += " /conn:\"" + sdfPath + "\""; parameters += " /context:" + model; if (dcDialog.Pluralize) { parameters += " /pluralize"; } if (!string.IsNullOrWhiteSpace(dcDialog.NameSpace)) { parameters += " /namespace:" + dcDialog.NameSpace; } var dcH = new ErikEJ.SqlCeScripting.DataContextHelper(); string sqlmetalResult = dcH.RunSqlMetal(sqlMetalPath, parameters); if (!File.Exists(dcPath)) { MessageBox.Show("Error during SQL Metal run: " + sqlmetalResult); return; } if (!isDesktop) { using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { if (dcDialog.CodeLanguage == "VB") { DataContextHelper.FixDataContextVB(dcPath, model, dcDialog.NameSpace, sdfFileName, repository); } else { DataContextHelper.FixDataContextCS(dcPath, model, dcDialog.NameSpace, sdfFileName, repository); } } } // Creates __Version table and adds one row if desired if (!isDesktop && dcDialog.AddVersionTable) { using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring)) { var list = repository.GetAllTableNames(); if (!list.Contains("__VERSION")) { repository.ExecuteSql(string.Format(@" CREATE TABLE [__VERSION] ( [SchemaVersion] int NOT NULL , [DateUpdated] datetime NOT NULL DEFAULT (GETDATE()) ); GO CREATE INDEX [IX_SchemaVersion] ON [__VERSION] ([SchemaVersion] DESC); GO INSERT INTO [__VERSION] ([SchemaVersion]) VALUES ({0}); GO", versionNumber)); } } } MessageBox.Show("DataContext class successfully created"); } } catch (Exception ex) { MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex)); } }
public void SpawnDataEditorWindow(object sender, ExecutedRoutedEventArgs e) { try { var menuItem = sender as MenuItem; if (menuItem == null) { return; } var menuInfo = menuItem.CommandParameter as MenuCommandParameters; if (menuInfo == null) { return; } WindowsFormsHost wh = new WindowsFormsHost(); ResultsetGrid rg = new ResultsetGrid(); List <int> readOnlyColumns = new List <int>(); using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var tpks = repository.GetAllPrimaryKeys().Where(pk => pk.TableName == menuInfo.Name).ToList(); if (tpks.Count == 0) { rg.ReadOnly = true; } List <Column> cols = repository.GetAllColumns(); cols = cols.Where(c => c.TableName == menuInfo.Name).ToList(); int x = 0; foreach (Column col in cols) { if (col.AutoIncrementBy > 0 || col.RowGuidCol) { readOnlyColumns.Add(x); } x++; } } var sqlText = string.Format(Environment.NewLine + "SELECT TOP({0}) * FROM [{1}]", Properties.Settings.Default.MaxRowsToEdit, menuInfo.Name); rg.TableName = sqlText; rg.ConnectionString = menuInfo.Connectionstring; rg.Tag = wh; rg.ReadOnlyColumns = readOnlyColumns; wh.Child = rg; string tabTitle = System.IO.Path.GetFileNameWithoutExtension(menuInfo.Caption) + "-" + menuInfo.Name + "-Edit"; if (rg.ReadOnly) { tabTitle = System.IO.Path.GetFileNameWithoutExtension(menuInfo.Caption) + "-" + menuInfo.Name + "-ReadOnly"; } bool alreadyThere = false; int i = -1; foreach (var item in _parent.FabTab.Items) { i++; if (item is FabTabItem) { FabTabItem ftItem = (FabTabItem)item; if (ftItem.Header.ToString() == tabTitle) { alreadyThere = true; } } } if (alreadyThere) { _parent.FabTab.SelectedIndex = i; _parent.FabTab.Focus(); } else { FabTabItem tab = new FabTabItem(); tab.Content = wh; tab.Header = tabTitle; _parent.FabTab.Items.Add(tab); _parent.FabTab.SelectedIndex = _parent.FabTab.Items.Count - 1; rg.Focus(); } } catch (Exception ex) { MessageBox.Show(DataConnectionHelper.ShowErrors(ex)); } }
public void SpawnReportViewerWindow(object sender, ExecutedRoutedEventArgs e) { try { var menuItem = sender as MenuItem; if (menuItem == null) { return; } var menuInfo = menuItem.CommandParameter as MenuCommandParameters; if (menuInfo == null) { return; } WindowsFormsHost wh = new WindowsFormsHost(); ReportGrid rg = new ReportGrid(); DataSet ds; using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var generator = RepoHelper.CreateGenerator(repository); generator.GenerateTableSelect(menuInfo.Name); var sqlText = generator.GeneratedScript; ds = repository.ExecuteSql(sqlText); } rg.DataSet = ds; rg.TableName = menuInfo.Name; wh.Child = rg; string tabTitle = System.IO.Path.GetFileNameWithoutExtension(menuInfo.Caption) + "-" + menuInfo.Name + "-Report"; bool alreadyThere = false; int i = -1; foreach (var item in _parent.FabTab.Items) { i++; if (item is FabTabItem) { FabTabItem ftItem = (FabTabItem)item; if (ftItem.Header.ToString() == tabTitle) { alreadyThere = true; } } } if (alreadyThere) { _parent.FabTab.SelectedIndex = i; _parent.FabTab.Focus(); } else { FabTabItem tab = new FabTabItem(); tab.Content = wh; tab.Header = tabTitle; _parent.FabTab.Items.Add(tab); _parent.FabTab.SelectedIndex = _parent.FabTab.Items.Count - 1; rg.Focus(); } } catch (System.IO.FileNotFoundException) { MessageBox.Show("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"); } catch (Exception ex) { MessageBox.Show(DataConnectionHelper.ShowErrors(ex)); } }