public void ScriptAsDrop(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.GenerateTableDrop(menuInfo.Name);
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
}
}
}
catch (Exception ex)
{
MessageBox.Show(DataConnectionHelper.ShowErrors(ex));
}
}
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 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));
}
}
public void ExportServerDatabaseTo40(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var databaseInfo = menuItem.CommandParameter as DatabasesMenuCommandParameters;
if (databaseInfo == null)
{
return;
}
var treeViewItem = databaseInfo.DatabasesTreeViewItem;
try
{
DataSource sqlDataSource = new DataSource("MicrosoftSqlServer", "Microsoft SQL Server");
sqlDataSource.Providers.Add(DataProvider.SqlDataProvider);
DataConnectionDialog dcd = new DataConnectionDialog();
dcd.DataSources.Add(sqlDataSource);
dcd.SelectedDataProvider = DataProvider.SqlDataProvider;
dcd.SelectedDataSource = sqlDataSource;
if (DataConnectionDialog.Show(dcd) == System.Windows.Forms.DialogResult.OK && !string.IsNullOrEmpty(dcd.ConnectionString))
{
PickTablesDialog ptd = new PickTablesDialog();
int totalCount = 0;
using (IRepository repository = RepoHelper.CreateServerRepository(dcd.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))
{
string sdfName;
SaveFileDialog fd = new SaveFileDialog();
fd.Title = "Export as";
fd.Filter = "SQL Server Compact Database (*.sdf)|*.sdf|All Files(*.*)|*.*";
fd.OverwritePrompt = true;
fd.ValidateNames = true;
bool?result = fd.ShowDialog();
if (result.HasValue && result.Value == true)
{
sdfName = fd.FileName;
using (IRepository repository = RepoHelper.CreateServerRepository(dcd.ConnectionString))
{
try
{
string scriptRoot = System.IO.Path.GetTempFileName();
string tempScript = scriptRoot + ".sqlce";
var generator = RepoHelper.CreateGenerator(repository, tempScript);
generator.ExcludeTables(ptd.Tables);
SetStatus("Scripting server database...");
generator.ScriptDatabaseToFile(Scope.SchemaData);
SetStatus("Creating SQL Server Compact database...");
ISqlCeHelper helper = RepoHelper.CreateHelper();
string sdfConnectionString = string.Format("Data Source={0};Max Database Size=4091", sdfName);
if (System.IO.File.Exists(sdfName))
{
File.Delete(sdfName);
}
helper.CreateDatabase(sdfConnectionString);
BackgroundWorker bw = new BackgroundWorker();
List <string> parameters = new List <string>();
parameters.Add(sdfConnectionString);
parameters.Add(tempScript);
parameters.Add(scriptRoot);
bw.DoWork += new DoWorkEventHandler(bw_DoWork);
bw.RunWorkerCompleted += (s, ea) =>
{
try
{
if (ea.Error != null)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ea.Error));
}
else
{
MessageBox.Show("Database successfully exported");
}
}
finally
{
bw.Dispose();
}
};
bw.RunWorkerAsync(parameters);
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}
}
}
dcd.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}
public void ScriptServerDatabase(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
Scope scope = (Scope)menuItem.Tag;
var databaseInfo = menuItem.CommandParameter as DatabasesMenuCommandParameters;
if (databaseInfo == null)
{
return;
}
var treeViewItem = databaseInfo.DatabasesTreeViewItem;
try
{
DataSource sqlDataSource = new DataSource("MicrosoftSqlServer", "Microsoft SQL Server");
sqlDataSource.Providers.Add(DataProvider.SqlDataProvider);
DataConnectionDialog dcd = new DataConnectionDialog();
dcd.DataSources.Add(sqlDataSource);
dcd.SelectedDataProvider = DataProvider.SqlDataProvider;
dcd.SelectedDataSource = sqlDataSource;
if (DataConnectionDialog.Show(dcd) == System.Windows.Forms.DialogResult.OK)
{
string connectionString = dcd.ConnectionString;
string fileName;
PickTablesDialog ptd = new PickTablesDialog();
int totalCount = 0;
using (IRepository repository = RepoHelper.CreateServerRepository(dcd.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))
{
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)
{
fileName = fd.FileName;
using (IRepository repository = RepoHelper.CreateServerRepository(connectionString))
{
var generator = RepoHelper.CreateGenerator(repository, fd.FileName);
generator.ExcludeTables(ptd.Tables);
System.Windows.Forms.MessageBox.Show(generator.ScriptDatabaseToFile(scope));
}
}
}
}
dcd.Dispose();
}
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 GenerateServerDgmlFiles(object sender, ExecutedRoutedEventArgs e)
{
// http://www.mztools.com/articles/2007/MZ2007011.aspx
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var databaseInfo = menuItem.CommandParameter as DatabasesMenuCommandParameters;
if (databaseInfo == null)
{
return;
}
var treeViewItem = databaseInfo.DatabasesTreeViewItem;
bool originalValue = Properties.Settings.Default.KeepServerSchemaNames;
try
{
DataSource sqlDataSource = new DataSource("MicrosoftSqlServer", "Microsoft SQL Server");
sqlDataSource.Providers.Add(DataProvider.SqlDataProvider);
DataConnectionDialog dcd = new DataConnectionDialog();
dcd.DataSources.Add(sqlDataSource);
dcd.SelectedDataProvider = DataProvider.SqlDataProvider;
dcd.SelectedDataSource = sqlDataSource;
if (DataConnectionDialog.Show(dcd) == System.Windows.Forms.DialogResult.OK)
{
string connectionString = dcd.ConnectionString;
string fileName;
PickTablesDialog ptd = new PickTablesDialog();
int totalCount = 0;
using (IRepository repository = RepoHelper.CreateServerRepository(dcd.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))
{
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)
{
Properties.Settings.Default.KeepServerSchemaNames = true;
fileName = fd.FileName;
#if V35
using (IRepository repository = new ServerDBRepository(connectionString, Properties.Settings.Default.KeepServerSchemaNames))
#else
using (IRepository repository = new ServerDBRepository4(connectionString, Properties.Settings.Default.KeepServerSchemaNames))
#endif
{
var generator = RepoHelper.CreateGenerator(repository, fileName);
generator.GenerateSchemaGraph(connectionString, ptd.Tables);
MessageBox.Show(string.Format("Saved {0}", fileName));
}
}
}
dcd.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
finally
{
Properties.Settings.Default.KeepServerSchemaNames = originalValue;
}
}
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));
}
}