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));
}
}