public void ScriptAsCreate(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.GenerateTableScript(menuInfo.Name);
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("TableScriptAsCreate");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
void item_Click(object sender, EventArgs e)
{
try
{
var menuItem = BuildMenuItemForCommandHandler();
if (menuItem == null)
{
return;
}
var menuInfo = menuItem.CommandParameter as MenuCommandParameters;
using (IRepository repository = new ServerDBRepository(menuInfo.DatabaseInfo.ConnectionString, true))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateTableContent(menuItem.Tag as string, false, Properties.Settings.Default.IgnoreIdentityInInsertScript);
if (!Properties.Settings.Default.IgnoreIdentityInInsertScript)
{
generator.GenerateIdentityReset(menuItem.Tag as string, false);
}
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(generator.GeneratedScript);
doc.DTE.ActiveDocument.Saved = true;
}
DataConnectionHelper.LogUsage("TableScriptAsData");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer, false);
}
}
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);
}
}
public void GenerateModelCodeInProject(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 dteH = new EnvDteHelper();
var project = dteH.GetProject(dte);
if (project == null)
{
EnvDteHelper.ShowError("Please select a project in Solution Explorer, where you want the DataAccess.cs to be placed");
return;
}
if (!dteH.AllowedProjectKinds.Contains(new Guid(project.Kind)))
{
EnvDteHelper.ShowError(string.Format("The selected project type {0} does not support sqlite-net (please let me know if I am wrong)", project.Kind));
return;
}
if (project.CodeModel != null && project.CodeModel.Language != CodeModelLanguageConstants.vsCMLanguageCSharp)
{
EnvDteHelper.ShowError("Unsupported code language, only C# is currently supported");
return;
}
if (databaseInfo.DatabaseInfo.DatabaseType != DatabaseType.SQLite)
{
EnvDteHelper.ShowError("Sorry, only SQLite databases are supported");
return;
}
try
{
var defaultNamespace = "Model";
if (project.Properties.Item("DefaultNamespace") != null)
{
defaultNamespace = project.Properties.Item("DefaultNamespace").Value.ToString();
}
var projectPath = project.Properties.Item("FullPath").Value.ToString();
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, databaseInfo.DatabaseInfo.DatabaseType);
generator.GenerateSqliteNetModel(defaultNamespace);
var fileName = Path.Combine(projectPath, "DataAccess.cs");
if (File.Exists(fileName))
{
File.Delete(fileName);
}
var warning = @"//This code was generated by a tool.
//Changes to this file will be lost if the code is regenerated."
+ Environment.NewLine +
"// See the blog post here for help on using the generated code: http://erikej.blogspot.dk/2014/10/database-first-with-sqlite-in-universal.html"
+ Environment.NewLine;
File.WriteAllText(fileName, warning + generator.GeneratedScript);
project.ProjectItems.AddFromFile(fileName);
EnvDteHelper.ShowMessage("DataAccess.cs generated.");
DataConnectionHelper.LogUsage("DatabaseSqliteNetCodegen");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, databaseInfo.DatabaseInfo.DatabaseType);
}
}
public void GenerateDiffScript(object sender, ExecutedRoutedEventArgs e)
{
try
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
var databaseList = DataConnectionHelper.GetDataConnections(package, true, false);
foreach (var info in DataConnectionHelper.GetOwnDataConnections())
{
if (!databaseList.ContainsKey(info.Key))
{
databaseList.Add(info.Key, info.Value);
}
}
foreach (var info in databaseList)
{
var sourceType = string.Empty;
switch (info.Value.DatabaseType)
{
case DatabaseType.SQLCE35:
sourceType = "3.5";
break;
case DatabaseType.SQLCE40:
sourceType = "4.0";
break;
case DatabaseType.SQLServer:
sourceType = "Server";
break;
}
info.Value.Caption = string.Format("{0} ({1})", info.Value.Caption, sourceType);
}
var cd = new CompareDialog(databaseInfo.DatabaseInfo.Caption, databaseList);
var result = cd.ShowModal();
if (!result.HasValue || !result.Value || (cd.TargetDatabase.Key == null))
{
return;
}
var target = cd.TargetDatabase;
var swap = cd.SwapTarget;
var source = new KeyValuePair <string, DatabaseInfo>(databaseInfo.DatabaseInfo.ConnectionString, databaseInfo.DatabaseInfo);
if (swap)
{
source = target;
target = new KeyValuePair <string, DatabaseInfo>(databaseInfo.DatabaseInfo.ConnectionString, databaseInfo.DatabaseInfo);
}
var editorTarget = target;
if (editorTarget.Value.DatabaseType == DatabaseType.SQLServer)
{
editorTarget = source;
}
using (var sourceRepository = Helpers.RepositoryHelper.CreateRepository(source.Value))
{
var generator = DataConnectionHelper.CreateGenerator(sourceRepository, databaseInfo.DatabaseInfo.DatabaseType);
using (var targetRepository = Helpers.RepositoryHelper.CreateRepository(target.Value))
{
try
{
SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, generator, Properties.Settings.Default.DropTargetTables);
var sqlEditorWindow = package.CreateWindow <SqlEditorWindow>();
var editorControl = sqlEditorWindow.Content as SqlEditorControl;
if (editorControl != null)
{
editorControl.ExplorerControl = _parentWindow.Content as ExplorerControl;
Debug.Assert(editorControl != null);
editorControl.DatabaseInfo = editorTarget.Value;
var 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, unless that option is set)
-- - 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;
if (swap)
{
explain += "-- ** Please note that the soruce and target have been swapped! ** " + Environment.NewLine + Environment.NewLine;
}
editorControl.SqlText = explain + generator.GeneratedScript;
}
DataConnectionHelper.LogUsage("DatabaseScriptDiff");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35);
}
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35);
}
}
public void ScriptDatabase(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var scope = (Scope)menuItem.Tag;
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
try
{
int totalCount;
var ptd = new PickTablesDialog();
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
ptd.Tables = repository.GetAllTableNamesForExclusion();
totalCount = ptd.Tables.Count;
}
var res = ptd.ShowModal();
if (res == true && (ptd.Tables.Count < totalCount))
{
var fd = new SaveFileDialog
{
Title = "Save generated script as",
Filter =
"SQL Server Compact Script (*.sqlce)|*.sqlce|SQL Server Script (*.sql)|*.sql|All Files(*.*)|*.*"
};
if (scope == Scope.SchemaDataSQLite || scope == Scope.SchemaSQLite || databaseInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLite)
{
fd.Filter = "SQLite Script (*.sql)|*.sql|All Files(*.*)|*.*";
}
fd.OverwritePrompt = true;
fd.ValidateNames = true;
var result = fd.ShowDialog();
if (!result.HasValue || result.Value != true)
{
return;
}
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, fd.FileName, databaseInfo.DatabaseInfo.DatabaseType);
generator.ExcludeTables(ptd.Tables);
EnvDteHelper.ShowMessage(generator.ScriptDatabaseToFile(scope));
DataConnectionHelper.LogUsage("DatabaseScriptCe");
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, databaseInfo.DatabaseInfo.DatabaseType, false);
}
}
public void EditTableData(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
var dbProviderPresent = menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLCE35 && DataConnectionHelper.IsV35DbProviderInstalled() ||
menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLCE40 && DataConnectionHelper.IsV40DbProviderInstalled() ||
menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLite;
if (!dbProviderPresent)
{
EnvDteHelper.ShowError("The required DbProvider registration is not present, please re-install/repair the SQL Server Compact runtime");
return;
}
try
{
var readOnly = false;
var readOnlyColumns = new List <int>();
string sqlText;
using (var repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var tpks = repository.GetAllPrimaryKeys()
.Where(pk => pk.TableName == menuInfo.Name)
.ToList();
if (tpks.Count == 0)
{
readOnly = true;
}
var cols = repository.GetAllColumns();
cols = cols
.Where(c => c.TableName == menuInfo.Name)
.ToList();
var x = 0;
foreach (var col in cols)
{
if (col.AutoIncrementBy > 0 || col.RowGuidCol)
{
readOnlyColumns.Add(x);
}
x++;
}
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
generator.GenerateTableSelect(menuInfo.Name, Properties.Settings.Default.MakeSQLiteDatetimeReadOnly);
sqlText = generator.GeneratedScript.Replace(";" + Environment.NewLine + "GO", "");
sqlText = sqlText.Replace(";" + Environment.NewLine, "");
if (menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLite)
{
sqlText = sqlText + string.Format(" LIMIT {0}", Properties.Settings.Default.MaxRowsToEdit);
}
else
{
sqlText = sqlText.Replace(Environment.NewLine + "SELECT ", string.Format(Environment.NewLine + "SELECT TOP({0}) ", Properties.Settings.Default.MaxRowsToEdit));
}
}
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 <DataGridViewWindow>(Math.Abs(menuInfo.Name.GetHashCode() - dbName.GetHashCode()));
if (window == null)
{
return;
}
window.Caption = menuInfo.Name + " (" + dbName + ")";
pkg.ShowWindow(window);
var control = window.Content as DataEditControl;
if (control != null)
{
control.DatabaseInfo = menuInfo.DatabaseInfo;
control.TableName = menuInfo.Name;
control.ReadOnly = readOnly;
control.ReadOnlyColumns = readOnlyColumns;
control.SqlText = sqlText;
control.ShowGrid();
}
}
DataConnectionHelper.LogUsage("TableEdit");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void GenerateDataDiffScript(object sender, ExecutedRoutedEventArgs e)
{
try
{
var menuInfo = ValidateMenuInfo(sender);
var package = ParentWindow.Package as SqlCeToolboxPackage;
if (menuInfo == null)
{
return;
}
Dictionary <string, DatabaseInfo> databaseList = DataConnectionHelper.GetDataConnections(package, true, false);
foreach (KeyValuePair <string, DatabaseInfo> info in DataConnectionHelper.GetOwnDataConnections())
{
if (!databaseList.ContainsKey(info.Key))
{
databaseList.Add(info.Key, info.Value);
}
}
foreach (KeyValuePair <string, DatabaseInfo> info in databaseList)
{
string sourceType = string.Empty;
switch (info.Value.DatabaseType)
{
case DatabaseType.SQLCE35:
sourceType = "3.5";
break;
case DatabaseType.SQLCE40:
sourceType = "4.0";
break;
case DatabaseType.SQLServer:
sourceType = "Server";
break;
}
info.Value.Caption = string.Format("{0} ({1})", info.Value.Caption, sourceType);
}
var cd = new CompareDialog(menuInfo.DatabaseInfo.Caption, databaseList, menuInfo.Name);
var result = cd.ShowModal();
if (!result.HasValue || !result.Value || (cd.TargetDatabase.Key == null))
{
return;
}
var target = cd.TargetDatabase;
var source = new KeyValuePair <string, DatabaseInfo>(menuInfo.DatabaseInfo.ConnectionString, menuInfo.DatabaseInfo);
var editorTarget = target;
if (editorTarget.Value.DatabaseType == DatabaseType.SQLServer)
{
editorTarget = source;
}
using (var sourceRepository = DataConnectionHelper.CreateRepository(source.Value))
{
using (var targetRepository = DataConnectionHelper.CreateRepository(target.Value))
{
var generator = DataConnectionHelper.CreateGenerator(targetRepository, target.Value.DatabaseType);
try
{
var script = SqlCeDiff.CreateDataDiffScript(sourceRepository, menuInfo.Name, targetRepository, menuInfo.Name, generator);
if (package != null)
{
var sqlEditorWindow = package.CreateWindow <SqlEditorWindow>();
var editorControl = sqlEditorWindow.Content as SqlEditorControl;
if (editorControl != null)
{
editorControl.ExplorerControl = ParentWindow.Content as ExplorerControl;
editorControl.DatabaseInfo = editorTarget.Value;
editorControl.SqlText = script;
}
}
DataConnectionHelper.LogUsage("TableScriptDataDiff");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, source.Value.DatabaseType, false);
}
}
}
}
catch (ArgumentException ae)
{
EnvDteHelper.ShowError(ae.Message);
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35, false);
}
}
public void GenerateDataContextInProject(object sender, ExecutedRoutedEventArgs e)
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
var isDesktop = (bool)((MenuItem)sender).Tag;
if (package == null)
{
return;
}
var dte = package.GetServiceHelper(typeof(DTE)) as DTE;
if (dte == null)
{
return;
}
if (dte.Mode == vsIDEMode.vsIDEModeDebug)
{
EnvDteHelper.ShowError("Cannot generate code while debugging");
return;
}
var helper = Helpers.RepositoryHelper.CreateEngineHelper(databaseInfo.DatabaseInfo.DatabaseType);
if (!helper.IsV35DbProviderInstalled())
{
EnvDteHelper.ShowError("This feature requires the SQL Server Compact 3.5 SP2 DbProvider to be properly installed");
return;
}
var dteH = new EnvDteHelper();
var project = dteH.GetProject(dte);
if (project == null)
{
EnvDteHelper.ShowError("Please select a project in Solution Explorer, where you want the DataContext to be placed");
return;
}
if (!isDesktop && !dteH.AllowedWpProjectKinds.Contains(new Guid(project.Kind)))
{
EnvDteHelper.ShowError("The selected project type does not support Windows Phone (please let me know if I am wrong)");
return;
}
if (isDesktop && !dteH.AllowedProjectKinds.Contains(new Guid(project.Kind)))
{
EnvDteHelper.ShowError("The selected project type does not support LINQ to SQL (please let me know if I am wrong)");
return;
}
if (project.Properties.Item("TargetFrameworkMoniker") == null)
{
EnvDteHelper.ShowError("The selected project type does not support Windows Phone - missing TargetFrameworkMoniker");
return;
}
if (!isDesktop)
{
if (project.Properties.Item("TargetFrameworkMoniker").Value.ToString() == "Silverlight,Version=v4.0,Profile=WindowsPhone71" ||
project.Properties.Item("TargetFrameworkMoniker").Value.ToString() == "WindowsPhone,Version=v8.0" ||
project.Properties.Item("TargetFrameworkMoniker").Value.ToString() == "WindowsPhone,Version=v8.1"
)
{
}
else
{
EnvDteHelper.ShowError("The selected project type does not support Windows Phone 7.1/8.0 - wrong TargetFrameworkMoniker: " + project.Properties.Item("TargetFrameworkMoniker").Value);
return;
}
}
if (isDesktop && !project.Properties.Item("TargetFrameworkMoniker").Value.ToString().Contains(".NETFramework"))
{
EnvDteHelper.ShowError("The selected project type does not support .NET Desktop - wrong TargetFrameworkMoniker: " + project.Properties.Item("TargetFrameworkMoniker").Value);
return;
}
if (!isDesktop && databaseInfo.DatabaseInfo.DatabaseType != DatabaseType.SQLCE35)
{
EnvDteHelper.ShowError("Sorry, only version 3.5 databases are supported for now");
return;
}
var sqlMetalPath = ProbeSqlMetalRegPaths();
if (string.IsNullOrEmpty(sqlMetalPath))
{
EnvDteHelper.ShowError("Could not find SQLMetal file location");
return;
}
var sdfFileName = string.Empty;
try
{
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var tables = repository.GetAllTableNames();
var pks = repository.GetAllPrimaryKeys();
var checkedTables = string.Empty;
foreach (var tableName in tables)
{
var pk = pks.Where(k => k.TableName == tableName).FirstOrDefault();
if (pk.TableName == null)
{
checkedTables += tableName + Environment.NewLine;
}
}
if (!string.IsNullOrEmpty(checkedTables))
{
var 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);
EnvDteHelper.ShowError(message);
}
var dbInfo = repository.GetDatabaseInfo();
foreach (var kvp in dbInfo)
{
if (kvp.Key == "Database")
{
sdfFileName = kvp.Value;
break;
}
}
sdfFileName = Path.GetFileName(sdfFileName);
}
var fileNameWithoutExtension = Path.GetFileNameWithoutExtension(databaseInfo.DatabaseInfo.Caption);
if (fileNameWithoutExtension != null)
{
var model = fileNameWithoutExtension.Replace(" ", string.Empty).Replace("#", string.Empty).Replace(".", string.Empty).Replace("-", string.Empty);
model = model + "Context";
var dcDialog = new DataContextDialog();
dcDialog.ModelName = model;
dcDialog.IsDesktop = isDesktop;
dcDialog.ProjectName = project.Name;
dcDialog.NameSpace = project.Properties.Item("DefaultNamespace").Value.ToString();
if (EnvDteHelper.VbProject == new Guid(project.Kind))
{
dcDialog.CodeLanguage = "VB";
}
else
{
dcDialog.CodeLanguage = "C#";
}
var result = dcDialog.ShowModal();
if (!result.HasValue || result.Value != true || string.IsNullOrWhiteSpace(dcDialog.ModelName))
{
return;
}
if (dcDialog.AddRowversionColumns)
{
AddRowVersionColumns(databaseInfo);
}
var sdfPath = databaseInfo.DatabaseInfo.ConnectionString;
//If version 4.0, create a 3.5 schema sdf, and use that as connection string
if (isDesktop && databaseInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLCE40)
{
var tempFile = Path.GetTempFileName();
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, tempFile, databaseInfo.DatabaseInfo.DatabaseType);
generator.ScriptDatabaseToFile(Scope.Schema);
}
if (sdfFileName != null)
{
sdfPath = Path.Combine(Path.GetTempPath(), sdfFileName);
}
using (Stream stream = new MemoryStream(Resources.SqlCe35AddinStore))
{
// Create a FileStream object to write a stream to a file
using (var fileStream = File.Create(sdfPath, (int)stream.Length))
{
// Fill the bytes[] array with the stream data
var bytesInStream = new byte[stream.Length];
stream.Read(bytesInStream, 0, bytesInStream.Length);
// Use FileStream object to write to the specified file
fileStream.Write(bytesInStream, 0, bytesInStream.Length);
}
}
var info = new DatabaseInfo
{
ConnectionString = "Data Source=" + sdfPath,
DatabaseType = DatabaseType.SQLCE35
};
using (var repository = Helpers.RepositoryHelper.CreateRepository(info))
{
var script = File.ReadAllText(tempFile);
repository.ExecuteSql(script);
}
sdfPath = info.ConnectionString;
}
var versionNumber = GetVersionTableNumber(databaseInfo.DatabaseInfo, isDesktop);
model = dcDialog.ModelName;
var dcPath = Path.Combine(Path.GetTempPath(), model + ".cs");
if (dcDialog.CodeLanguage == "VB")
{
dcPath = Path.Combine(Path.GetTempPath(), model + ".vb");
}
var 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 DataContextHelper();
var sqlmetalResult = dcH.RunSqlMetal(sqlMetalPath, parameters);
if (!File.Exists(dcPath))
{
EnvDteHelper.ShowError("Error during SQL Metal run: " + sqlmetalResult);
return;
}
if (!isDesktop)
{
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
if (dcDialog.CodeLanguage == "VB")
{
DataContextHelper.FixDataContextVB(dcPath, model, dcDialog.NameSpace, sdfFileName, repository);
}
else
{
DataContextHelper.FixDataContextCS(dcPath, model, dcDialog.NameSpace, sdfFileName, repository);
}
}
}
if (dcDialog.MultipleFiles)
{
var classes = DataContextHelper.SplitIntoMultipleFiles(dcPath, dcDialog.NameSpace, model);
var projectPath = project.Properties.Item("FullPath").Value.ToString();
foreach (var item in classes)
{
var fileName = Path.Combine(projectPath, item.Key + ".cs");
if (File.Exists(fileName))
{
File.Delete(fileName);
}
File.WriteAllText(fileName, item.Value);
var classItem = dteH.GetProjectDataContextClass(project, fileName);
if (classItem != null)
{
classItem.Delete();
}
project.ProjectItems.AddFromFile(fileName);
}
}
else
{
var extension = ".cs";
if (dcDialog.CodeLanguage == "VB")
{
extension = ".vb";
}
var dcItem = dteH.GetProjectDc(project, model, extension);
if (dcItem == null)
{
project.ProjectItems.AddFromFileCopy(dcPath);
}
else
{
if (EnvDteHelper.ShowMessageBox("The Data Context class already exists in the project, do you wish to replace it?", OLEMSGBUTTON.OLEMSGBUTTON_YESNO, OLEMSGDEFBUTTON.OLEMSGDEFBUTTON_SECOND, OLEMSGICON.OLEMSGICON_QUERY) == System.Windows.Forms.DialogResult.Yes)
{
dcItem.Delete();
project.ProjectItems.AddFromFileCopy(dcPath);
}
}
}
EnvDteHelper.AddReference(project, "System.Data.Linq");
if (dcDialog.AddConnectionStringBuilder)
{
var projectPath = project.Properties.Item("FullPath").Value.ToString();
var fileName = "LocalDatabaseConnectionStringBuilder.cs";
var filePath = Path.Combine(projectPath, fileName);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
using (Stream stream = new MemoryStream(Resources.LocalDatabaseConnectionStringBuilder))
{
// Create a FileStream object to write a stream to a file
using (var fileStream = File.Create(filePath, (int)stream.Length))
{
// Fill the bytes[] array with the stream data
var bytesInStream = new byte[stream.Length];
stream.Read(bytesInStream, 0, bytesInStream.Length);
// Use FileStream object to write to the specified file
fileStream.Write(bytesInStream, 0, bytesInStream.Length);
}
}
project.ProjectItems.AddFromFile(filePath);
}
// Creates __Version table and adds one row if desired
if (dcDialog.AddVersionTable)
{
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
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));
}
}
}
DataConnectionHelper.LogUsage("DatabaseCreateDC");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, databaseInfo.DatabaseInfo.DatabaseType, false);
}
}
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 DatabaseMenuCommandParameters;
if (databaseInfo == null)
{
return;
}
var originalValue = Properties.Settings.Default.KeepSchemaNames;
var dte = _package.GetServiceHelper(typeof(DTE)) as DTE;
try
{
var connectionString = databaseInfo.DatabaseInfo != null
? databaseInfo.DatabaseInfo.ConnectionString :
DataConnectionHelper.PromptForConnectionString(_package);
if (string.IsNullOrEmpty(connectionString))
{
return;
}
string fileName;
var ptd = new PickTablesDialog();
using (var repository = DataConnectionHelper.CreateRepository(new DatabaseInfo {
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
ptd.Tables = repository.GetAllTableNamesForExclusion();
}
var res = ptd.ShowModal();
if (res.HasValue && res.Value)
{
var fd = new SaveFileDialog();
fd.Title = "Save generated DGML file as";
fd.Filter = "DGML (*.dgml)|*.dgml";
fd.OverwritePrompt = true;
fd.ValidateNames = true;
var result = fd.ShowDialog();
if (!result.HasValue || result.Value != true)
{
return;
}
Properties.Settings.Default.KeepSchemaNames = true;
fileName = fd.FileName;
using (var repository = DataConnectionHelper.CreateRepository(new DatabaseInfo {
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
try
{
var generator = DataConnectionHelper.CreateGenerator(repository, fileName, DatabaseType.SQLServer);
generator.GenerateSchemaGraph(connectionString, ptd.Tables);
if (dte != null)
{
dte.ItemOperations.OpenFile(fileName);
dte.ActiveDocument.Activate();
}
DataConnectionHelper.LogUsage("DatabasesScriptDGML");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer, false);
}
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer, false);
}
finally
{
Properties.Settings.Default.KeepSchemaNames = originalValue;
}
}
public void ScriptServerDatabase(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var scope = (Scope)menuItem.Tag;
var databaseInfo = menuItem.CommandParameter as DatabaseMenuCommandParameters;
if (databaseInfo == null)
{
return;
}
try
{
var connectionString = databaseInfo.DatabaseInfo != null
? databaseInfo.DatabaseInfo.ConnectionString :
DataConnectionHelper.PromptForConnectionString(_package);
if (string.IsNullOrEmpty(connectionString))
{
return;
}
var ptd = new PickTablesDialog();
int totalCount;
using (var repository = DataConnectionHelper.CreateRepository(new DatabaseInfo {
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
ptd.Tables = repository.GetAllTableNamesForExclusion();
totalCount = ptd.Tables.Count;
}
var res = ptd.ShowModal();
if (!res.HasValue || res.Value != true || (ptd.Tables.Count >= totalCount))
{
return;
}
{
var fd = new SaveFileDialog
{
Title = "Save generated database script as",
Filter =
"SQL Server Compact Script (*.sqlce)|*.sqlce|SQL Server Script (*.sql)|*.sql|All Files(*.*)|*.*"
};
if (scope == Scope.SchemaDataSQLite || scope == Scope.SchemaSQLite)
{
fd.Filter = "SQL Script (*.sql)|*.sql|All Files(*.*)|*.*";
}
fd.OverwritePrompt = true;
fd.ValidateNames = true;
var result = fd.ShowDialog();
if (!result.HasValue || result.Value != true)
{
return;
}
using (var repository = DataConnectionHelper.CreateRepository(new DatabaseInfo {
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
try
{
var generator = DataConnectionHelper.CreateGenerator(repository, fd.FileName, DatabaseType.SQLServer);
generator.ExcludeTables(ptd.Tables);
EnvDteHelper.ShowMessage(generator.ScriptDatabaseToFile(scope));
DataConnectionHelper.LogUsage("DatabasesScriptServer");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer, false);
}
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer, false);
}
}
public void GenerateModelCodeInProject(object sender, ExecutedRoutedEventArgs e)
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
if (package == null)
{
return;
}
var dteH = new EnvDteHelper();
var project = dteH.GetProject();
if (project == null)
{
EnvDteHelper.ShowError("Please select a project in Solution Explorer, where you want the DataAccess.cs to be placed");
return;
}
if (!dteH.ContainsAllowed(project))
{
EnvDteHelper.ShowError("The selected project type does not support sqlite-net (please let me know if I am wrong)");
return;
}
if (databaseInfo.DatabaseInfo.DatabaseType != DatabaseType.SQLite)
{
EnvDteHelper.ShowError("Sorry, only SQLite databases are supported");
return;
}
try
{
var defaultNamespace = "Model";
var projectNamespace = ThreadHelper.JoinableTaskFactory.Run(() => project.GetAttributeAsync("DefaultNamespace"));
if (projectNamespace != null)
{
defaultNamespace = projectNamespace;
}
var projectPath = Path.GetDirectoryName(project.FullPath);
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, databaseInfo.DatabaseInfo.DatabaseType);
generator.GenerateSqliteNetModel(defaultNamespace);
var fileName = Path.Combine(projectPath, "DataAccess.cs");
if (File.Exists(fileName))
{
File.Delete(fileName);
}
var warning = @"//This code was generated by a tool.
//Changes to this file will be lost if the code is regenerated."
+ Environment.NewLine +
"// See the blog post here for help on using the generated code: http://erikej.blogspot.dk/2014/10/database-first-with-sqlite-in-universal.html"
+ Environment.NewLine;
File.WriteAllText(fileName, warning + generator.GeneratedScript);
ThreadHelper.JoinableTaskFactory.Run(() => project.AddExistingFilesAsync(fileName));
EnvDteHelper.ShowMessage("DataAccess.cs generated.");
DataConnectionHelper.LogUsage("DatabaseSqliteNetCodegen");
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, databaseInfo.DatabaseInfo.DatabaseType);
}
}
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 = Helpers.RepositoryHelper.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 = Helpers.RepositoryHelper.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_0001.sqlce
{
var count = Directory.GetFiles(Path.GetDirectoryName(scriptRoot), Path.GetFileName(scriptRoot) + "*", SearchOption.AllDirectories).Count();
for (var i = 0; i < count + 1; 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);
}
else
{
if (i > 0)
{
break;
}
}
}
_package.SetStatus(null);
}
}
}
_package.SetStatus("Import complete");
}
public void ScriptServerDatabase(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var scope = (Scope)menuItem.Tag;
var databaseInfo = menuItem.CommandParameter as DatabaseMenuCommandParameters;
if (databaseInfo == null)
{
return;
}
try
{
//var serverList = new ObjectExplorerManager(_package).GetAllServers();
var connectionString = databaseInfo.DatabaseInfo != null
? databaseInfo.DatabaseInfo.ConnectionString :
DataConnectionHelper.PromptForConnectionString(_package);
if (string.IsNullOrEmpty(connectionString))
{
return;
}
var ptd = new PickTablesDialog(true);
int totalCount;
using (var repository = Helpers.RepositoryHelper.CreateRepository(new DatabaseInfo {
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
ptd.Tables = repository.GetAllTableNamesForExclusion();
totalCount = ptd.Tables.Count;
}
var res = ptd.ShowModal();
if (!res.HasValue || res.Value != true || (ptd.Tables.Count >= totalCount))
{
return;
}
{
if (ptd.ToWindow)
{
using (var repository = Helpers.RepositoryHelper.CreateRepository(databaseInfo.DatabaseInfo))
{
var fileName = Path.GetTempFileName();
var generator = DataConnectionHelper.CreateGenerator(repository, fileName, databaseInfo.DatabaseInfo.DatabaseType);
generator.ExcludeTables(ptd.Tables);
generator.ScriptDatabaseToFile(scope);
if (generator.GeneratedFiles.Count > 1)
{
EnvDteHelper.ShowError("Multiple files were generated, cannot script to window, script to file(s) instead.");
}
SpawnSqlEditorWindow(databaseInfo.DatabaseInfo, File.ReadAllText(generator.GeneratedFiles[0], Encoding.UTF8));
DataConnectionHelper.LogUsage("DatabaseScriptServer");
}
}
else
{
var fd = new SaveFileDialog
{
Title = "Save generated script as",
Filter =
"SQL Server Compact Script (*.sqlce)|*.sqlce|SQL Server Script (*.sql)|*.sql|All Files(*.*)|*.*"
};
if (scope == Scope.SchemaDataSQLite || scope == Scope.SchemaSQLite)
{
fd.Filter = "SQL Script (*.sql)|*.sql|All Files(*.*)|*.*";
}
fd.OverwritePrompt = true;
fd.ValidateNames = true;
var result = fd.ShowDialog();
if (!result.HasValue || result.Value != true)
{
return;
}
using (var repository = Helpers.RepositoryHelper.CreateRepository(new DatabaseInfo {
ConnectionString = connectionString, DatabaseType = DatabaseType.SQLServer
}))
{
try
{
var generator = DataConnectionHelper.CreateGenerator(repository, fd.FileName, DatabaseType.SQLServer);
generator.ExcludeTables(ptd.Tables);
EnvDteHelper.ShowMessage(generator.ScriptDatabaseToFile(scope));
DataConnectionHelper.LogUsage("DatabasesScriptServer");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer, false);
}
}
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLServer, false);
}
}