static DbTableList TryExecute(DbSource dbSource)
{
Dev2Logger.Info("Get Database Tables. " + dbSource.DatabaseName, GlobalConstants.WarewolfInfo);
var tables = new DbTableList();
Common.Utilities.PerformActionInsideImpersonatedContext(Common.Utilities.OrginalExecutingUser, () =>
{
DataTable columnInfo = null;
switch (dbSource.ServerType)
{
case enSourceType.SqlDatabase:
{
using (var connection = new SqlConnection(dbSource.ConnectionString))
{
connection.Open();
columnInfo = connection.GetSchema("Tables");
}
break;
}
default:
{
using (var connection = new MySqlConnection(dbSource.ConnectionString))
{
connection.Open();
columnInfo = connection.GetSchema("Tables");
}
break;
}
}
if (columnInfo != null)
{
foreach (DataRow row in columnInfo.Rows)
{
var tableName = row["TABLE_NAME"] as string;
var schema = row["TABLE_SCHEMA"] as string;
tableName = '[' + tableName + ']';
var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema);
if (dbTable == null)
{
dbTable = new DbTable {
Schema = schema, TableName = tableName, Columns = new List <IDbColumn>()
};
tables.Items.Add(dbTable);
}
}
}
if (tables.Items.Count == 0)
{
tables.HasErrors = true;
const string ErrorFormat = "The login provided in the database source uses {0} and most probably does not have permissions to perform the following query: "
+ "\r\n\r\n{1}SELECT * FROM INFORMATION_SCHEMA.TABLES;{2}";
if (dbSource.AuthenticationType == AuthenticationType.User)
{
tables.Errors = string.Format(ErrorFormat,
"SQL Authentication (User: '******')",
"EXECUTE AS USER = '******';\r\n",
"\r\nREVERT;");
}
else
{
tables.Errors = string.Format(ErrorFormat, "Windows Authentication", "", "");
}
}
});
return(tables);
}
/// <summary>
/// Executes the service
/// </summary>
/// <param name="values">The values.</param>
/// <param name="theWorkspace">The workspace.</param>
/// <returns></returns>
public StringBuilder Execute(Dictionary<string, StringBuilder> values, IWorkspace theWorkspace)
{
Dev2JsonSerializer serializer = new Dev2JsonSerializer();
if(values == null)
{
throw new InvalidDataContractException("No parameter values provided.");
}
string serializedSource = null;
StringBuilder tmp;
values.TryGetValue("SharepointServer", out tmp);
if(tmp != null)
{
serializedSource = tmp.ToString();
}
if(string.IsNullOrEmpty(serializedSource))
{
ExecuteMessage message = new ExecuteMessage();
message.HasError = true;
message.SetMessage("No sharepoint server set.");
Dev2Logger.Log.Debug("No sharepoint server set.");
return serializer.SerializeToBuilder(message);
}
SharepointSource source;
SharepointSource runtimeSource = null;
try
{
source = serializer.Deserialize<SharepointSource>(serializedSource);
if(source.ResourceID != Guid.Empty)
{
runtimeSource = ResourceCatalog.Instance.GetResource<SharepointSource>(theWorkspace.ID, source.ResourceID);
if(runtimeSource == null)
{
var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, source.ResourceID);
runtimeSource = new SharepointSource(contents.ToXElement());
}
}
}
catch(Exception e)
{
Dev2Logger.Log.Error(e);
var res = new DbTableList("Invalid JSON data for sharepoint server parameter. Exception: {0}", e.Message);
return serializer.SerializeToBuilder(res);
}
if(runtimeSource == null)
{
var res = new DbTableList("Invalid sharepoint server source");
Dev2Logger.Log.Debug("Invalid sharepoint server source");
return serializer.SerializeToBuilder(res);
}
if(string.IsNullOrEmpty(runtimeSource.Server))
{
var res = new DbTableList("Invalid sharepoint server sent {0}.", serializedSource);
Dev2Logger.Log.Debug(String.Format("Invalid sharepoint server sent {0}.", serializedSource));
return serializer.SerializeToBuilder(res);
}
try
{
Dev2Logger.Log.Info("Get Sharepoint Server Lists. " + source.Server);
List<SharepointListTo> lists = runtimeSource.LoadLists();
return serializer.SerializeToBuilder(lists);
}
catch(Exception ex)
{
var tables = new DbTableList(ex);
return serializer.SerializeToBuilder(tables);
}
}
public override StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace)
{
var serializer = new Dev2JsonSerializer();
if (values == null)
{
throw new InvalidDataContractException(ErrorResource.NoParameter);
}
string database = null;
values.TryGetValue("Database", out StringBuilder tmp);
if (tmp != null)
{
database = tmp.ToString();
}
if (string.IsNullOrEmpty(database))
{
var res = new DbTableList("No database set.");
Dev2Logger.Debug("No database set.", GlobalConstants.WarewolfDebug);
return(serializer.SerializeToBuilder(res));
}
DbSource dbSource;
DbSource runtimeDbSource = null;
try
{
dbSource = serializer.Deserialize <DbSource>(database);
if (dbSource.ResourceID != Guid.Empty)
{
runtimeDbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID);
}
}
catch (Exception e)
{
Dev2Logger.Error(e, GlobalConstants.WarewolfError);
var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message);
return(serializer.SerializeToBuilder(res));
}
if (runtimeDbSource == null)
{
var res = new DbTableList("Invalid Database source");
Dev2Logger.Debug("Invalid Database source", GlobalConstants.WarewolfDebug);
return(serializer.SerializeToBuilder(res));
}
if (string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server))
{
var res = new DbTableList("Invalid database sent {0}.", database);
Dev2Logger.Debug($"Invalid database sent {database}.", GlobalConstants.WarewolfDebug);
return(serializer.SerializeToBuilder(res));
}
try
{
return(serializer.SerializeToBuilder(TryExecute(dbSource)));
}
catch (Exception ex)
{
var tables = new DbTableList(ex);
return(serializer.SerializeToBuilder(tables));
}
}
static Dictionary<DbSource, DbTableList> CreateDatabases(int count, bool varcharNullable = false, bool intAsIdentity = false)
{
var result = new Dictionary<DbSource, DbTableList>();
for(var i = 0; i < count; i++)
{
var dbName = "Db" + i;
var tables = new List<DbTable>();
for(var j = 0; j < 10; j++)
{
var columns = new List<IDbColumn>();
var colCount = ((j % 4) + 1) * (i + 1);
for(var k = 0; k < colCount; k++)
{
var t = k % 4;
switch(t)
{
case 0:
columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.VarChar, MaxLength = 50, IsNullable = varcharNullable });
break;
case 1:
columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.Int, IsAutoIncrement = intAsIdentity });
break;
case 2:
columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.Money });
break;
case 3:
columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.Float });
break;
}
}
tables.Add(new DbTable { Schema = "MySchema", TableName = dbName + "_Table_" + j, Columns = columns });
}
var tableList = new DbTableList();
tableList.Items.AddRange(tables);
result.Add(new DbSource
{
ResourceID = Guid.NewGuid(),
ResourceName = dbName,
}, tableList);
}
return result;
}
/// <summary>
/// Executes the service
/// </summary>
/// <param name="values">The values.</param>
/// <param name="theWorkspace">The workspace.</param>
/// <returns></returns>
public StringBuilder Execute(Dictionary<string, StringBuilder> values, IWorkspace theWorkspace)
{
Dev2JsonSerializer serializer = new Dev2JsonSerializer();
if(values == null)
{
throw new InvalidDataContractException("No parameter values provided.");
}
string database = null;
StringBuilder tmp;
values.TryGetValue("Database", out tmp);
if(tmp != null)
{
database = tmp.ToString();
}
if(string.IsNullOrEmpty(database))
{
var res = new DbTableList("No database set.");
Dev2Logger.Log.Debug("No database set.");
return serializer.SerializeToBuilder(res);
}
DbSource dbSource;
DbSource runtimeDbSource = null;
try
{
dbSource = serializer.Deserialize<DbSource>(database);
if(dbSource.ResourceID != Guid.Empty)
{
runtimeDbSource = ResourceCatalog.Instance.GetResource<DbSource>(theWorkspace.ID, dbSource.ResourceID);
}
}
catch(Exception e)
{
Dev2Logger.Log.Error(e);
var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message);
return serializer.SerializeToBuilder(res);
}
if(runtimeDbSource == null)
{
var res = new DbTableList("Invalid Database source");
Dev2Logger.Log.Debug("Invalid Database source");
return serializer.SerializeToBuilder(res);
}
if(string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server))
{
var res = new DbTableList("Invalid database sent {0}.", database);
Dev2Logger.Log.Debug(String.Format("Invalid database sent {0}.", database));
return serializer.SerializeToBuilder(res);
}
try
{
Dev2Logger.Log.Info("Get Database Tables. " + dbSource.DatabaseName);
var tables = new DbTableList();
DataTable columnInfo;
switch(dbSource.ServerType)
{
case enSourceType.SqlDatabase:
{
using (var connection = new SqlConnection(dbSource.ConnectionString))
{
connection.Open();
columnInfo = connection.GetSchema("Tables");
}
break;
}
default:
{
using (var connection = new MySqlConnection(dbSource.ConnectionString))
{
connection.Open();
columnInfo = connection.GetSchema("Tables");
}
break;
}
}
if(columnInfo != null)
{
foreach(DataRow row in columnInfo.Rows)
{
var tableName = row["TABLE_NAME"] as string;
var schema = row["TABLE_SCHEMA"] as string;
tableName = '[' + tableName + ']';
var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema);
if(dbTable == null)
{
dbTable = new DbTable { Schema = schema, TableName = tableName, Columns = new List<IDbColumn>() };
tables.Items.Add(dbTable);
}
}
}
if(tables.Items.Count == 0)
{
tables.HasErrors = true;
const string ErrorFormat = "The login provided in the database source uses {0} and most probably does not have permissions to perform the following query: "
+ "\r\n\r\n{1}SELECT * FROM INFORMATION_SCHEMA.TABLES;{2}";
if(dbSource.AuthenticationType == AuthenticationType.User)
{
tables.Errors = string.Format(ErrorFormat,
"SQL Authentication (User: '******')",
"EXECUTE AS USER = '******';\r\n",
"\r\nREVERT;");
}
else
{
tables.Errors = string.Format(ErrorFormat, "Windows Authentication", "", "");
}
}
return serializer.SerializeToBuilder(tables);
}
catch(Exception ex)
{
var tables = new DbTableList(ex);
return serializer.SerializeToBuilder(tables);
}
}
static TestSqlBulkInsertDesignerViewModel CreateViewModel(ModelItem modelItem, Dictionary<DbSource, DbTableList> sources, IEventAggregator eventAggregator, IResourceModel resourceModel, bool configureFindSingle = false, string columnListErrors = "")
{
var sourceDefs = sources == null ? null : sources.Select(s => s.Key.ToXml().ToString());
var envModel = new Mock<IEnvironmentModel>();
envModel.Setup(e => e.Connection.WorkspaceID).Returns(Guid.NewGuid());
var resourceRepo = new Mock<IResourceRepository>();
envModel.Setup(e => e.Connection.ExecuteCommand(It.Is<StringBuilder>(s => s.Contains("FindSourcesByType")), It.IsAny<Guid>(), It.IsAny<Guid>()))
.Returns(new StringBuilder(string.Format("<XmlData>{0}</XmlData>", sourceDefs == null ? "" : string.Join("\n", sourceDefs))));
// return the resource repository now ;)
envModel.Setup(e => e.ResourceRepository).Returns(resourceRepo.Object);
// setup the FindSourcesByType command
if(sources != null)
{
var dbs = sources.Keys.ToList();
resourceRepo.Setup(r => r.FindSourcesByType<DbSource>(It.IsAny<IEnvironmentModel>(), enSourceType.SqlDatabase)).Returns(dbs);
}
var tableJson = new DbTableList();
// ReSharper disable ImplicitlyCapturedClosure
resourceRepo.Setup(r => r.GetDatabaseTables(It.IsAny<DbSource>())).Callback((DbSource src) =>
// ReSharper restore ImplicitlyCapturedClosure
{
if(sources != null)
{
var tableList = sources[src];
tableJson = tableList;
}
// ReSharper disable ImplicitlyCapturedClosure
}).Returns(() => tableJson);
// ReSharper restore ImplicitlyCapturedClosure
var columnsJson = new DbColumnList();
// ReSharper disable ImplicitlyCapturedClosure
resourceRepo.Setup(r => r.GetDatabaseTableColumns(It.IsAny<DbSource>(), It.IsAny<DbTable>())).Callback((DbSource src, DbTable tbl) =>
// ReSharper restore ImplicitlyCapturedClosure
{
var tableName = tbl.TableName;
if(sources != null)
{
var tables = sources[src];
var table = tables.Items.First(t => t.TableName == tableName.Trim(new[] { '"' }));
var columnList = new DbColumnList();
columnList.Items.AddRange(table.Columns);
if(!string.IsNullOrEmpty(columnListErrors))
{
columnList.HasErrors = true;
columnList.Errors = columnListErrors;
}
columnsJson = columnList;
}
// ReSharper disable ImplicitlyCapturedClosure
}).Returns(() => columnsJson);
// ReSharper restore ImplicitlyCapturedClosure
if(configureFindSingle)
{
envModel.Setup(e => e.ResourceRepository.FindSingle(It.IsAny<Expression<Func<IResourceModel, bool>>>(), false)).Returns(resourceModel);
}
return new TestSqlBulkInsertDesignerViewModel(modelItem, envModel.Object, eventAggregator);
}
static void VerifyTables(DbTableList expectedTables, List<DbTable> actualTables)
{
for(var i = 0; i < expectedTables.Items.Count; i++)
{
var expected = expectedTables.Items[i];
var actual = actualTables[i];
Assert.AreEqual(expected.TableName, actual.TableName);
Assert.AreEqual(expected.Columns.Count, actual.Columns.Count);
}
}
/// <summary>
/// Executes the service
/// </summary>
/// <param name="values">The values.</param>
/// <param name="theWorkspace">The workspace.</param>
/// <returns></returns>
public StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace)
{
Dev2JsonSerializer serializer = new Dev2JsonSerializer();
if (values == null)
{
throw new InvalidDataContractException("No parameter values provided.");
}
string database = null;
StringBuilder tmp;
values.TryGetValue("Database", out tmp);
if (tmp != null)
{
database = tmp.ToString();
}
if (string.IsNullOrEmpty(database))
{
var res = new DbTableList("No database set.");
Dev2Logger.Log.Debug("No database set.");
return(serializer.SerializeToBuilder(res));
}
DbSource dbSource;
DbSource runtimeDbSource = null;
try
{
dbSource = serializer.Deserialize <DbSource>(database);
if (dbSource.ResourceID != Guid.Empty)
{
runtimeDbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID);
}
}
catch (Exception e)
{
Dev2Logger.Log.Error(e);
var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message);
return(serializer.SerializeToBuilder(res));
}
if (runtimeDbSource == null)
{
var res = new DbTableList("Invalid Database source");
Dev2Logger.Log.Debug("Invalid Database source");
return(serializer.SerializeToBuilder(res));
}
if (string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server))
{
var res = new DbTableList("Invalid database sent {0}.", database);
Dev2Logger.Log.Debug(String.Format("Invalid database sent {0}.", database));
return(serializer.SerializeToBuilder(res));
}
try
{
Dev2Logger.Log.Info("Get Database Tables. " + dbSource.DatabaseName);
var tables = new DbTableList();
DataTable columnInfo;
using (var connection = new SqlConnection(dbSource.ConnectionString))
{
connection.Open();
columnInfo = connection.GetSchema("Tables");
}
if (columnInfo != null)
{
foreach (DataRow row in columnInfo.Rows)
{
var tableName = row["TABLE_NAME"] as string;
var schema = row["TABLE_SCHEMA"] as string;
tableName = '[' + tableName + ']';
var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema);
if (dbTable == null)
{
dbTable = new DbTable {
Schema = schema, TableName = tableName, Columns = new List <IDbColumn>()
};
tables.Items.Add(dbTable);
}
}
}
if (tables.Items.Count == 0)
{
tables.HasErrors = true;
const string ErrorFormat = "The login provided in the database source uses {0} and most probably does not have permissions to perform the following query: "
+ "\r\n\r\n{1}SELECT * FROM INFORMATION_SCHEMA.TABLES;{2}";
if (dbSource.AuthenticationType == AuthenticationType.User)
{
tables.Errors = string.Format(ErrorFormat,
"SQL Authentication (User: '******')",
"EXECUTE AS USER = '******';\r\n",
"\r\nREVERT;");
}
else
{
tables.Errors = string.Format(ErrorFormat, "Windows Authentication", "", "");
}
}
return(serializer.SerializeToBuilder(tables));
}
catch (Exception ex)
{
var tables = new DbTableList(ex);
return(serializer.SerializeToBuilder(tables));
}
}
/// <summary>
/// Executes the service
/// </summary>
/// <param name="values">The values.</param>
/// <param name="theWorkspace">The workspace.</param>
/// <returns></returns>
public StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace)
{
Dev2JsonSerializer serializer = new Dev2JsonSerializer();
if (values == null)
{
throw new InvalidDataContractException("No parameter values provided.");
}
string serializedSource = null;
StringBuilder tmp;
values.TryGetValue("SharepointServer", out tmp);
if (tmp != null)
{
serializedSource = tmp.ToString();
}
if (string.IsNullOrEmpty(serializedSource))
{
ExecuteMessage message = new ExecuteMessage();
message.HasError = true;
message.SetMessage("No sharepoint server set.");
Dev2Logger.Log.Debug("No sharepoint server set.");
return(serializer.SerializeToBuilder(message));
}
SharepointSource source;
SharepointSource runtimeSource = null;
try
{
source = serializer.Deserialize <SharepointSource>(serializedSource);
if (source.ResourceID != Guid.Empty)
{
runtimeSource = ResourceCatalog.Instance.GetResource <SharepointSource>(theWorkspace.ID, source.ResourceID);
if (runtimeSource == null)
{
var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, source.ResourceID);
runtimeSource = new SharepointSource(contents.ToXElement());
}
}
}
catch (Exception e)
{
Dev2Logger.Log.Error(e);
var res = new DbTableList("Invalid JSON data for sharepoint server parameter. Exception: {0}", e.Message);
return(serializer.SerializeToBuilder(res));
}
if (runtimeSource == null)
{
var res = new DbTableList("Invalid sharepoint server source");
Dev2Logger.Log.Debug("Invalid sharepoint server source");
return(serializer.SerializeToBuilder(res));
}
if (string.IsNullOrEmpty(runtimeSource.Server))
{
var res = new DbTableList("Invalid sharepoint server sent {0}.", serializedSource);
Dev2Logger.Log.Debug(String.Format("Invalid sharepoint server sent {0}.", serializedSource));
return(serializer.SerializeToBuilder(res));
}
try
{
Dev2Logger.Log.Info("Get Sharepoint Server Lists. " + source.Server);
List <SharepointListTo> lists = runtimeSource.LoadLists();
return(serializer.SerializeToBuilder(lists));
}
catch (Exception ex)
{
var tables = new DbTableList(ex);
return(serializer.SerializeToBuilder(tables));
}
}
public override StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace)
{
var serializer = new Dev2JsonSerializer();
if (values == null)
{
throw new InvalidDataContractException(ErrorResource.NoParameter);
}
string serializedSource = null;
values.TryGetValue("SharepointServer", out StringBuilder tmp);
if (tmp != null)
{
serializedSource = tmp.ToString();
}
if (string.IsNullOrEmpty(serializedSource))
{
var message = new ExecuteMessage();
message.HasError = true;
message.SetMessage(ErrorResource.NoSharepointServerSet);
Dev2Logger.Debug(ErrorResource.NoSharepointServerSet, GlobalConstants.WarewolfDebug);
return(serializer.SerializeToBuilder(message));
}
SharepointSource source;
SharepointSource runtimeSource = null;
try
{
source = serializer.Deserialize <SharepointSource>(serializedSource);
if (source.ResourceID != Guid.Empty)
{
runtimeSource = ResourceCatalog.Instance.GetResource <SharepointSource>(theWorkspace.ID, source.ResourceID);
if (runtimeSource == null)
{
var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, source.ResourceID);
runtimeSource = new SharepointSource(contents.ToXElement());
}
}
}
catch (Exception e)
{
Dev2Logger.Error(e, GlobalConstants.WarewolfError);
var res = new DbTableList("Invalid JSON data for sharepoint server parameter. Exception: {0}", e.Message);
return(serializer.SerializeToBuilder(res));
}
if (runtimeSource == null)
{
var res = new DbTableList(ErrorResource.InvalidSharepointServerSource);
Dev2Logger.Debug(ErrorResource.InvalidSharepointServerSource, GlobalConstants.WarewolfDebug);
return(serializer.SerializeToBuilder(res));
}
if (string.IsNullOrEmpty(runtimeSource.Server))
{
var res = new DbTableList(ErrorResource.InvalidSharepointServerSent, serializedSource);
Dev2Logger.Debug(string.Format(ErrorResource.InvalidSharepointServerSent, serializedSource), GlobalConstants.WarewolfDebug);
return(serializer.SerializeToBuilder(res));
}
try
{
Dev2Logger.Info("Get Sharepoint Server Lists. " + source.Server, GlobalConstants.WarewolfDebug);
var lists = runtimeSource.LoadLists();
return(serializer.SerializeToBuilder(lists));
}
catch (Exception ex)
{
var tables = new DbTableList(ex);
return(serializer.SerializeToBuilder(tables));
}
}
