public static DbService Create()
{
var result = new DbService
{
ResourceID = Guid.Empty,
Source = { ResourceID = Guid.Empty },
};
return result;
}
public void MySqlMapDbOutputs(IOutputDescription outputDescription, ref DbService theService, bool addFields)
{
// only fetch paths with valid data to map ;)
var outputsToMap = outputDescription.DataSourceShapes[0].Paths.Where(p => !string.IsNullOrEmpty(p.DisplayPath) && p.DisplayPath != "DocumentElement");
var rsFields = new List<RecordsetField>(theService.Recordset.Fields);
#pragma warning disable 219
int recordsetIndex = 0;
#pragma warning restore 219
foreach (var path in outputsToMap)
{
// Remove bogus names and dots
var name = path.DisplayPath.Replace("NewDataSet", "").Replace(".Table.", "").Replace("DocumentElement", "");
var alias = path.DisplayPath.Replace("NewDataSet", "").Replace(".Table.", "").Replace(".", "").Replace("DocumentElement", "");
var idx = name.IndexOf("()", StringComparison.InvariantCultureIgnoreCase);
if (idx >= 0)
{
name = name.Remove(0, idx + 3);
}
idx = alias.IndexOf("()", StringComparison.InvariantCultureIgnoreCase);
if (idx >= 0)
{
alias = alias.Remove(0, idx + 2);
}
var field = new RecordsetField { Name = name, Alias = string.IsNullOrEmpty(path.OutputExpression) ? alias : path.OutputExpression, Path = path };
RecordsetField rsField;
if (!addFields && (rsField = rsFields.FirstOrDefault(f => f.Path != null ? f.Path.ActualPath == path.ActualPath : f.Name == field.Name)) != null)
{
field.Alias = rsField.Alias;
}
theService.Recordset.Fields.Add(field);
// 2013.12.11 - COMMUNITY BUG - 341463 - data with empty cells displays incorrectly
var data = path.SampleData.Split(new[] { GlobalConstants.AnytingToXmlCommaToken }, StringSplitOptions.None);
var recordIndex = 0;
foreach (var item in data)
{
theService.Recordset.SetValue(recordIndex, recordsetIndex, item);
recordIndex++;
}
recordsetIndex++;
}
}
// ReSharper disable InconsistentNaming
public void DbService_Constructor_CorrectDbService()
// ReSharper restore InconsistentNaming
{
// ReSharper disable InconsistentNaming
const string xmlDataString = @"<Service ID=""af8d2d38-22b5-4599-8357-adce196beb83"" Name=""TravsTestService"" ResourceType=""DbService"">
<Actions>
<Action Name=""dbo.InsertDummyUser"" Type=""InvokeStoredProc"" SourceID=""ebba47dc-e5d4-4303-a203-09e2e9761d16"" SourceName=""testingDBSrc"" SourceMethod=""dbo.InsertDummyUser"">
<Inputs>
<Input Name=""fname"" Source=""fname"" EmptyToNull=""false"" DefaultValue="""" NativeType=""System.Object"" />
<Input Name=""lname"" Source=""lname"" EmptyToNull=""false"" DefaultValue="""" NativeType=""System.Object"" />
<Input Name=""username"" Source=""username"" EmptyToNull=""false"" DefaultValue="""" NativeType=""System.Object"" />
<Input Name=""password"" Source=""password"" EmptyToNull=""false"" DefaultValue="""" NativeType=""System.Object"" />
<Input Name=""lastAccessDate"" Source=""lastAccessDate"" EmptyToNull=""false"" DefaultValue="""" NativeType=""System.Object"" />
</Inputs>
<Outputs />
<OutputDescription><![CDATA[<z:anyType xmlns:i=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:d1p1=""http://schemas.datacontract.org/2004/07/Unlimited.Framework.Converters.Graph.Ouput"" i:type=""d1p1:OutputDescription"" xmlns:z=""http://schemas.microsoft.com/2003/10/Serialization/""><d1p1:DataSourceShapes xmlns:d2p1=""http://schemas.microsoft.com/2003/10/Serialization/Arrays""><d2p1:anyType i:type=""d1p1:DataSourceShape""><d1p1:Paths /></d2p1:anyType></d1p1:DataSourceShapes><d1p1:Format>ShapedXML</d1p1:Format></z:anyType>]]></OutputDescription>
</Action>
</Actions>
<AuthorRoles />
<Comment />
<Tags />
<HelpLink />
<UnitTestTargetWorkflowService />
<BizRule />
<WorkflowActivityDef />
<XamlDefinition />
<DataList />
<TypeOf>InvokeStoredProc</TypeOf>
<DisplayName>TravsTestService</DisplayName>
<Category>WEBPART_WIZARDS</Category>
</Service>";
// ReSharper restore InconsistentNaming
XElement testElm = XElement.Parse(xmlDataString);
DbService dbService = new DbService(testElm);
Assert.AreEqual("TravsTestService", dbService.ResourceName);
Assert.AreEqual(ResourceType.DbService, dbService.ResourceType);
Assert.AreEqual("af8d2d38-22b5-4599-8357-adce196beb83", dbService.ResourceID.ToString());
Assert.AreEqual("WEBPART_WIZARDS", dbService.ResourcePath);
}
public static DbService CreateCountriesDbService()
{
var service = new DbService
{
ResourceID = Guid.NewGuid(),
ResourceName = "CountriesService",
ResourceType = ResourceType.DbService,
ResourcePath = "Test",
Method = new ServiceMethod
{
Name = "dbo.spGetCountries",
Parameters = new List<MethodParameter>(new[]
{
new MethodParameter { Name = "@Prefix", EmptyToNull = false, IsRequired = true, Value = string.Empty, DefaultValue = "b" }
})
},
Recordset = new Recordset
{
Name = "Countries",
},
Source = new DbSource
{
ResourceID = Guid.NewGuid(),
ResourceName = "CitiesDB",
ResourceType = ResourceType.DbSource,
ResourcePath = "Test",
Server = "RSAKLFSVRGENDEV",
DatabaseName = "Cities",
AuthenticationType = AuthenticationType.Windows,
}
};
service.Recordset.Fields.AddRange(new[]
{
new RecordsetField { Name = "CountryID", Alias = "CountryID" },
new RecordsetField { Name = "Description", Alias = "Name" }
});
return service;
}
// ReSharper disable InconsistentNaming
public void SqlDatabaseBroker_TestService_ValidDbServiceThatReturnsNull_RecordsetWithNullColumn()
// ReSharper restore InconsistentNaming
{
var service = new DbService
{
ResourceID = Guid.NewGuid(),
ResourceName = "NullService",
ResourceType = ResourceType.DbService,
ResourcePath = "Test",
Method = new ServiceMethod
{
Name = "Pr_GeneralTestColumnData",
Parameters = new List<MethodParameter>(),
ExecuteAction = "Pr_GeneralTestColumnData"
},
Recordset = new Recordset
{
Name = "Collections",
},
Source = SqlServerTests.CreateDev2TestingDbSource()
};
var broker = new SqlDatabaseBroker();
IOutputDescription outputDescription = broker.TestService(service);
Assert.AreEqual(1, outputDescription.DataSourceShapes.Count);
IDataSourceShape dataSourceShape = outputDescription.DataSourceShapes[0];
Assert.IsNotNull(dataSourceShape);
Assert.AreEqual(3, dataSourceShape.Paths.Count);
StringAssert.Contains(dataSourceShape.Paths[2].DisplayPath, "TestTextNull"); //This is the field that contains a null value. Previously this column would not have been returned.
}
// ReSharper disable InconsistentNaming
public void SqlDatabaseBroker_TestService_SqlUserWithValidUsername_ReturnsValidResult()
// ReSharper restore InconsistentNaming
{
var dbSource = SqlServerTests.CreateDev2TestingDbSource();
var serviceConn = new DbService
{
ResourceID = Guid.NewGuid(),
ResourceName = "DatabaseService",
ResourceType = ResourceType.DbService,
ResourcePath = "Test",
AuthorRoles = "",
Dependencies = new List<IResourceForTree>(),
FilePath = null,
IsUpgraded = true,
Method = new ServiceMethod("dbo.fn_diagramobjects", "\r\n\tCREATE FUNCTION dbo.fn_diagramobjects() \r\n\tRETURNS int\r\n\tWITH EXECUTE AS N'dbo'\r\n\tAS\r\n\tBEGIN\r\n\t\tdeclare @id_upgraddiagrams\t\tint\r\n\t\tdeclare @id_sysdiagrams\t\t\tint\r\n\t\tdeclare @id_helpdiagrams\t\tint\r\n\t\tdeclare @id_helpdiagramdefinition\tint\r\n\t\tdeclare @id_creatediagram\tint\r\n\t\tdeclare @id_renamediagram\tint\r\n\t\tdeclare @id_alterdiagram \tint \r\n\t\tdeclare @id_dropdiagram\t\tint\r\n\t\tdeclare @InstalledObjects\tint\r\n\r\n\t\tselect @InstalledObjects = 0\r\n\r\n\t\tselect \t@id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'),\r\n\t\t\t@id_sysdiagrams = object_id(N'dbo.sysdiagrams'),\r\n\t\t\t@id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'),\r\n\t\t\t@id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'),\r\n\t\t\t@id_creatediagram = object_id(N'dbo.sp_creatediagram'),\r\n\t\t\t@id_renamediagram = object_id(N'dbo.sp_renamediagram'),\r\n\t\t\t@id_alterdiagram = object_id(N'dbo.sp_alterdiagram'), \r\n\t\t\t@id_dropdiagram = object_id(N'dbo.sp_dropdiagram')\r\n\r\n\t\tif @id_upgraddiagrams is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 1\r\n\t\tif @id_sysdiagrams is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 2\r\n\t\tif @id_helpdiagrams is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 4\r\n\t\tif @id_helpdiagramdefinition is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 8\r\n\t\tif @id_creatediagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 16\r\n\t\tif @id_renamediagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 32\r\n\t\tif @id_alterdiagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 64\r\n\t\tif @id_dropdiagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 128\r\n\t\t\r\n\t\treturn @InstalledObjects \r\n\tEND\r\n\t", null, null, null, "dbo.fn_diagramobjects"),
Recordset = new Recordset(),
Source = dbSource
};
var broker = new SqlDatabaseBroker();
var result = broker.TestService(serviceConn);
Assert.AreEqual(OutputFormats.ShapedXML, result.Format);
}
// ReSharper disable InconsistentNaming
public void SqlDatabaseBroker_TestService_WindowsUserWithoutDbAccess_ReturnsInvalidResult()
// ReSharper restore InconsistentNaming
{
Exception exception = null;
Impersonator.RunAs("NoDBAccessTest", "DEV2", "One23456", () =>
{
var dbSource = SqlServerTests.CreateDev2TestingDbSource(AuthenticationType.Windows);
var serviceConn = new DbService
{
ResourceID = Guid.NewGuid(),
ResourceName = "DatabaseService",
ResourceType = ResourceType.DbService,
ResourcePath = "Test",
AuthorRoles = "",
Dependencies = new List<IResourceForTree>(),
FilePath = null,
IsUpgraded = true,
Method = new ServiceMethod("dbo.fn_diagramobjects", "\r\n\tCREATE FUNCTION dbo.fn_diagramobjects() \r\n\tRETURNS int\r\n\tWITH EXECUTE AS N'dbo'\r\n\tAS\r\n\tBEGIN\r\n\t\tdeclare @id_upgraddiagrams\t\tint\r\n\t\tdeclare @id_sysdiagrams\t\t\tint\r\n\t\tdeclare @id_helpdiagrams\t\tint\r\n\t\tdeclare @id_helpdiagramdefinition\tint\r\n\t\tdeclare @id_creatediagram\tint\r\n\t\tdeclare @id_renamediagram\tint\r\n\t\tdeclare @id_alterdiagram \tint \r\n\t\tdeclare @id_dropdiagram\t\tint\r\n\t\tdeclare @InstalledObjects\tint\r\n\r\n\t\tselect @InstalledObjects = 0\r\n\r\n\t\tselect \t@id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'),\r\n\t\t\t@id_sysdiagrams = object_id(N'dbo.sysdiagrams'),\r\n\t\t\t@id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'),\r\n\t\t\t@id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'),\r\n\t\t\t@id_creatediagram = object_id(N'dbo.sp_creatediagram'),\r\n\t\t\t@id_renamediagram = object_id(N'dbo.sp_renamediagram'),\r\n\t\t\t@id_alterdiagram = object_id(N'dbo.sp_alterdiagram'), \r\n\t\t\t@id_dropdiagram = object_id(N'dbo.sp_dropdiagram')\r\n\r\n\t\tif @id_upgraddiagrams is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 1\r\n\t\tif @id_sysdiagrams is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 2\r\n\t\tif @id_helpdiagrams is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 4\r\n\t\tif @id_helpdiagramdefinition is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 8\r\n\t\tif @id_creatediagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 16\r\n\t\tif @id_renamediagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 32\r\n\t\tif @id_alterdiagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 64\r\n\t\tif @id_dropdiagram is not null\r\n\t\t\tselect @InstalledObjects = @InstalledObjects + 128\r\n\t\t\r\n\t\treturn @InstalledObjects \r\n\tEND\r\n\t", null, null, null, null),
Recordset = new Recordset(),
Source = dbSource
};
var broker = new SqlDatabaseBroker();
try
{
broker.TestService(serviceConn);
}
catch(Exception ex)
{
// Need to do this because exceptions get swallowed by impersonator
exception = ex;
}
Assert.IsNotNull(exception);
Assert.IsInstanceOfType(exception, typeof(SqlException));
Assert.AreEqual("Login failed for user 'DEV2\\NoDBAccessTest'.", exception.Message);
});
}
// ReSharper disable InconsistentNaming
public void AbstractDataBaseBroker_TestService_InvokesDbServiceMethodInsideTransaction_Done()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var dbService = new DbService
{
Method = new ServiceMethod
{
Name = "TestMethod",
Parameters = new List<MethodParameter>
{
new MethodParameter { Name = "Param1", Type = typeof(string), Value = "Hello" },
new MethodParameter { Name = "Param2", Type = typeof(int), Value = "99" }
},
ExecuteAction = "BobTestMethod"
}
};
var testServiceResults = new DataTable("TestTableName");
testServiceResults.Columns.Add("Col1", typeof(int));
testServiceResults.Columns.Add("Col2", typeof(string));
testServiceResults.Rows.Add(new object[] { 1, "row1" });
testServiceResults.Rows.Add(new object[] { 2, "row2" });
testServiceResults.Rows.Add(new object[] { 3, null });
IDbCommand fetchDataTableCommand = null;
var dbServer = new Mock<TestDbServer>();
dbServer.Setup(s => s.Connect(It.IsAny<string>())).Verifiable();
dbServer.Setup(s => s.BeginTransaction()).Verifiable();
dbServer.Setup(s => s.FetchDataTable(It.IsAny<IDbCommand>()))
.Callback((IDbCommand command) => fetchDataTableCommand = command)
.Returns(testServiceResults)
.Verifiable();
dbServer.Setup(s => s.RollbackTransaction()).Verifiable();
dbServer.Setup(s => s.CreateCommand()).Returns(new SqlCommand());
var broker = new TestDatabaseBroker(dbServer.Object);
//------------Execute Test---------------------------
var result = broker.TestService(dbService);
//------------Assert Results-------------------------
dbServer.Verify(s => s.Connect(It.IsAny<string>()));
dbServer.Verify(s => s.BeginTransaction());
dbServer.Verify(s => s.FetchDataTable(It.IsAny<IDbCommand>()));
dbServer.Verify(s => s.RollbackTransaction());
Assert.IsNotNull(fetchDataTableCommand);
Assert.AreEqual("BobTestMethod", fetchDataTableCommand.CommandText);
Assert.AreEqual(dbService.Method.Parameters.Count, fetchDataTableCommand.Parameters.Count);
for(var i = 0; i < dbService.Method.Parameters.Count; i++)
{
var methodParam = dbService.Method.Parameters[i];
var commandParam = (IDbDataParameter)fetchDataTableCommand.Parameters[i];
Assert.AreEqual("@" + methodParam.Name, commandParam.ParameterName);
Assert.AreEqual(methodParam.Value, commandParam.Value);
Assert.AreEqual(DbType.String, commandParam.DbType);
}
Assert.IsNotNull(result);
Assert.IsNotNull(result.DataSourceShapes);
Assert.AreEqual(1, result.DataSourceShapes.Count);
Assert.IsNotNull(result.DataSourceShapes[0]);
Assert.AreEqual(2, result.DataSourceShapes[0].Paths.Count);
Assert.AreEqual("TestTableName().Col1", result.DataSourceShapes[0].Paths[0].ActualPath);
Assert.AreEqual("TestTableName().Col1", result.DataSourceShapes[0].Paths[0].DisplayPath);
Assert.AreEqual("1__COMMA__2__COMMA__3", result.DataSourceShapes[0].Paths[0].SampleData);
Assert.AreEqual("TestTableName().Col2", result.DataSourceShapes[0].Paths[1].ActualPath);
Assert.AreEqual("TestTableName().Col2", result.DataSourceShapes[0].Paths[1].DisplayPath);
Assert.AreEqual("row1__COMMA__row2__COMMA__", result.DataSourceShapes[0].Paths[1].SampleData);
}
// ReSharper disable InconsistentNaming
public void AbstractDataBaseBroker_TestService_DbServiceWithNullSource_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var broker = new TestDatabaseBroker();
var dbService = new DbService { Source = null };
//------------Execute Test---------------------------
broker.TestService(dbService);
//------------Assert Results-------------------------
}
// ReSharper disable InconsistentNaming
public void DbService_Constructor_BlankRecordsetName_UsesMethodName()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
const string ExpectedName = "TestMethod";
var dbService = DbService.Create();
dbService.Method = new ServiceMethod { Name = ExpectedName };
var xml = dbService.ToXml();
//------------Execute Test---------------------------
var actual = new DbService(xml);
//------------Assert Results-------------------------
Assert.AreEqual(ExpectedName, actual.Recordset.Name);
}
// ReSharper disable InconsistentNaming
public void DbService_ToXml_WhenRecordSetHasBlankFields_ExpectNotPartOfOutputDescription()
// ReSharper restore InconsistentNaming
{
//------------Setup for test--------------------------
var dbService = new DbService();
var dbSource = new DbSource { ResourceName = "Source" };
var resourceId = Guid.NewGuid();
dbSource.ResourceID = resourceId;
dbService.Source = dbSource;
var serviceMethod = new ServiceMethod { Name = "Method" };
dbService.Method = serviceMethod;
var recordset = new Recordset { Name = "SomeRecSet" };
var recordsetField = new RecordsetField { Alias = "SomeAlias", Name = "" };
recordset.Fields.Add(recordsetField);
dbService.Recordset = recordset;
// ReSharper disable InconsistentNaming
const string expected = @"<Service ID=""00000000-0000-0000-0000-000000000000"" Name="""" ResourceType=""DbService"" IsValid=""false"">
<Actions>
<Action Name=""SomeRecSet"" Type=""InvokeStoredProc"" SourceID=""{0}"" SourceName=""Source"" ExecuteAction="""" SourceMethod=""Method"">
<Inputs />
<Outputs />
<OutputDescription><![CDATA[<z:anyType xmlns:i=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:d1p1=""http://schemas.datacontract.org/2004/07/Unlimited.Framework.Converters.Graph.Ouput"" i:type=""d1p1:OutputDescription"" xmlns:z=""http://schemas.microsoft.com/2003/10/Serialization/""><d1p1:DataSourceShapes xmlns:d2p1=""http://schemas.microsoft.com/2003/10/Serialization/Arrays""><d2p1:anyType i:type=""d1p1:DataSourceShape""><d1p1:_x003C_Paths_x003E_k__BackingField /></d2p1:anyType></d1p1:DataSourceShapes><d1p1:Format>ShapedXML</d1p1:Format></z:anyType>]]></OutputDescription>
</Action>
</Actions>
<AuthorRoles />
<Comment />
<Tags />
<HelpLink />
<UnitTestTargetWorkflowService />
<BizRule />
<WorkflowActivityDef />
<XamlDefinition />
<DataList />
<TypeOf>InvokeStoredProc</TypeOf>
<DisplayName></DisplayName>
<Category></Category>
<AuthorRoles></AuthorRoles>
<ErrorMessages />
</Service>";
// ReSharper restore InconsistentNaming
//------------Execute Test---------------------------
var xElement = dbService.ToXml();
//------------Assert Results-------------------------
Assert.AreEqual(string.Format(expected, resourceId), xElement.ToString());
}
public virtual Recordset FetchRecordset(DbService dbService, bool addFields)
{
if(dbService == null)
{
throw new ArgumentNullException("dbService");
}
var source = dbService.Source as DbSource;
if(source != null)
{
switch(source.ServerType)
{
case enSourceType.SqlDatabase:
{
var broker = CreateDatabaseBroker();
var outputDescription = broker.TestService(dbService);
if (outputDescription == null || outputDescription.DataSourceShapes == null || outputDescription.DataSourceShapes.Count == 0)
{
throw new Exception("Error retrieving shape from service output.");
}
dbService.Recordset.Name = dbService.Recordset.Name.Replace(".", "_");
dbService.Recordset.Fields.Clear();
ServiceMappingHelper smh = new ServiceMappingHelper();
smh.MapDbOutputs(outputDescription, ref dbService, addFields);
return dbService.Recordset;
}
case enSourceType.MySqlDatabase:
{
var broker = new MySqlDatabaseBroker();
var outputDescription = broker.TestService(dbService);
if (outputDescription == null || outputDescription.DataSourceShapes == null || outputDescription.DataSourceShapes.Count == 0)
{
throw new Exception("Error retrieving shape from service output.");
}
dbService.Recordset.Name = dbService.Recordset.Name.Replace(".", "_");
dbService.Recordset.Fields.Clear();
ServiceMappingHelper smh = new ServiceMappingHelper();
smh.MySqlMapDbOutputs(outputDescription, ref dbService, addFields);
return dbService.Recordset;
}
default: return null;
}
}
return null;
// Clear out the Recordset.Fields list because the sequence and
// number of fields may have changed since the last invocation.
//
// Create a copy of the Recordset.Fields list before clearing it
// so that we don't lose the user-defined aliases.
//
}
