public void DbTable_FullName_HasNullSchema_ShouldContainTableName()
{
//------------Setup for test--------------------------
var dbTable = new DbTable { TableName = "Test", Schema = null };
//------------Execute Test---------------------------
var fullName = dbTable.FullName;
//------------Assert Results-------------------------
Assert.AreEqual("Test", fullName);
}
public void DbTable_FullName_HasTableNameNull_ShouldEmptyString()
{
//------------Setup for test--------------------------
var dbTable = new DbTable { Schema = "Test", TableName = null };
//------------Execute Test---------------------------
var fullName = dbTable.FullName;
//------------Assert Results-------------------------
Assert.AreEqual("", fullName);
}
/// <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);
}
}