public ArtDevBCS(string ModelName)
{
this.ModelName = ModelName;
// Get the Catalog for the SharePoint site
BdcService service = SPFarm.Local.Services.GetValue <BdcService>(String.Empty);
SPAdministrationWebApplication centralWeb = SPAdministrationWebApplication.Local;
SPSite AdminSite = new SPSite(centralWeb.Sites.FirstOrDefault <SPSite>().Url);
SPServiceContext context = SPServiceContext.GetContext(AdminSite);
this.catalog =
service.GetAdministrationMetadataCatalog(context);
this.catalog.GetModels(ModelName)?.ToList().ForEach(m => m.Delete());
// Create a new Model
// NOTE: Assume that the "ModelName" Model
// does not already exist.
this.Model = Model.Create(ModelName, true, catalog);
}
// Uncomment the method below to handle the event raised after a feature has been activated.
public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
// Get the Catalog for the SharePoint site
BdcService service =
SPFarm.Local.Services.GetValue <BdcService>(String.Empty);
SPSite site = new SPSite("http://sp2016:2016/");
SPServiceContext context = SPServiceContext.GetContext(site);
AdministrationMetadataCatalog catalog =
service.GetAdministrationMetadataCatalog(context);
catalog.GetModels("EmployeeModel")?.ToList().ForEach(m => m.Delete());
// Create a new Employee Model
// NOTE: Assume that the "EmployeeModel" Model
// does not already exist.
Model EmployeeModel = Model.Create("EmployeeModel", true, catalog);
// Make a new Employee LobSystem
// NOTE: Assume that the "AdventureWorks" LobSystem
// does not already exist.
LobSystem adventureWorksLobSystem = EmployeeModel.OwnedReferencedLobSystems.Create("AdventureWorks", true, SystemType.Database);
// Make a new AdventureWorks LobSystemInstance.
LobSystemInstance adventureWorksLobSystemInstance = adventureWorksLobSystem.LobSystemInstances.Create("AdventureWorks", true);
// Set the connection properties.
adventureWorksLobSystemInstance.Properties.Add(
"ShowInSearchUI", "");
adventureWorksLobSystemInstance.Properties.Add(
"DatabaseAccessProvider", "SqlServer");
adventureWorksLobSystemInstance.Properties.Add(
"RdbConnection Data Source", "SP2016");
adventureWorksLobSystemInstance.Properties.Add(
"RdbConnection Initial Catalog", "AdventureWorks2016");
adventureWorksLobSystemInstance.Properties.Add(
"AuthenticationMode", "RdbCredentials");
adventureWorksLobSystemInstance.Properties.Add(
"SsoProviderImplementation", "Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c");
adventureWorksLobSystemInstance.Properties.Add(
"SsoApplicationId", "Adventure");
adventureWorksLobSystemInstance.Properties.Add(
"RdbConnection Pooling", "true");
// Create a new Employee Entity.
Entity EmployeeEntity = Entity.Create(
"Employee",
"AdventureWorks",
true,
new Version("1.0.0.4"),
10000,
CacheUsage.Default,
adventureWorksLobSystem,
EmployeeModel,
catalog);
// Set the identifier to the EmployeeID column.
EmployeeEntity.Identifiers.Create(
"BusinessEntityID", true, "System.Int32");
// Create the Finder Method,
// i.e. the method to return all rows.
CreateReadListMethod(catalog, EmployeeEntity);
// Create the Specific Finder Method,
// i.e. the method to return one row.
CreateReadItemMethod(catalog, EmployeeEntity);
// Validate the Employee Entity.
ActivationError[] activationErrors =
EmployeeEntity.Validate();
// Check if the validation failed.
if (activationErrors.Count() == 0)
{
// The validation was successful so publish the Employee Entity.
EmployeeEntity.Activate();
}
}
private static void CreateReadItemMethod(
AdministrationMetadataCatalog catalog, Entity contactEntity)
{
// Create the specific finder method
Method getContactMethod = contactEntity.Methods.Create(
"GetEmployee", true, false, "vEmployee");
// Specify the query
getContactMethod.Properties.Add(
"RdbCommandText",
"SELECT [BusinessEntityID] , [Title] , [FirstName] , [LastName] " +
"FROM [HumanResources].[vEmployee] " +
"WHERE [BusinessEntityID] = @BusinessEntityID");
// Set the command type
getContactMethod.Properties.Add("RdbCommandType", "Text");
getContactMethod.Properties.Add(
"Schema", "HumanResources");
getContactMethod.Properties.Add(
"BackEndObjectType", "SqlServerView");
getContactMethod.Properties.Add(
"BackEndObject", "vEmployee");
// Create the ContactID input parameter
Parameter BusinessEntityIDParameter =
getContactMethod.Parameters.Create(
"@BusinessEntityID", true, DirectionType.In);
// Create the TypeDescriptor for the ContactID parameter
BusinessEntityIDParameter.CreateRootTypeDescriptor(
"BusinessEntityID",
true,
"System.Int32",
"BusinessEntityID",
new IdentifierReference(
"BusinessEntityID",
new EntityReference("AdventureWorks", "Employee", catalog),
catalog),
null,
TypeDescriptorFlags.None,
null,
catalog);
// Create the Contact return parameter
Parameter contactParameter =
getContactMethod.Parameters.Create(
"Employee", true, DirectionType.Return);
// Create the TypeDescriptors for the Contact return parameter.
TypeDescriptor returnRootCollectionTypeDescriptor =
contactParameter.CreateRootTypeDescriptor(
"Employees",
true,
"System.Data.IDataReader, System.Data, Version=4.0.0.0," +
" Culture=neutral, PublicKeyToken=b77a5c561934e089",
"Employees",
null,
null,
TypeDescriptorFlags.IsCollection,
null,
catalog);
TypeDescriptor returnRootElementTypeDescriptor =
returnRootCollectionTypeDescriptor.ChildTypeDescriptors.Create(
"Employee",
true,
"System.Data.IDataRecord, System.Data, Version=4.0.0.0," +
" Culture=neutral, PublicKeyToken=b77a5c561934e089",
"Employee",
null,
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"BusinessEntityID",
true,
"System.Int32",
"BusinessEntityID",
new IdentifierReference("BusinessEntityID",
new EntityReference("AdventureWorks", "Employee", catalog),
catalog),
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"LastName",
true,
"System.String",
"LastName",
null,
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"FirstName",
true,
"System.String",
"FirstName",
null,
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"Title",
true,
"System.String",
"Title",
null,
null,
TypeDescriptorFlags.None,
null);
// Create the specific finder method instance
getContactMethod.MethodInstances.Create(
"GetEmployee",
true,
returnRootElementTypeDescriptor,
MethodInstanceType.SpecificFinder,
true);
}
private static void CreateReadListMethod(
AdministrationMetadataCatalog catalog, Entity contactEntity)
{
// Create the Finder method
Method getContactsMethod = contactEntity.Methods.Create(
"GetEmployees", true, false, "vEmployee");
// Specify the query
getContactsMethod.Properties.Add(
"RdbCommandText",
"SELECT TOP(@MaxRowsReturned) [BusinessEntityID] , [Title] , [FirstName] , [LastName] " +
" FROM [HumanResources].[vEmployee]");
// Set the command type
getContactsMethod.Properties.Add("RdbCommandType", "Text");
// Set the additional property values so that this
// External Content Type can be displayed
// in SharePoint Designer.
getContactsMethod.Properties.Add(
"Schema", "HumanResources");
getContactsMethod.Properties.Add(
"BackEndObjectType", "SqlServerView");
getContactsMethod.Properties.Add(
"BackEndObject", "vEmployee");
// Create a Filter so that we can limit the number
// of rows returned;
// otherwise we may exceed the list query size threshold.
FilterDescriptor limitRowsReturnedFilter =
getContactsMethod.FilterDescriptors.Create(
"RowsReturnedLimit", true, FilterType.Limit, null);
limitRowsReturnedFilter.Properties.Add(
"IsDefault", true);
// Create the RowsToRetrieve input parameter.
Parameter maxRowsReturnedParameter =
getContactsMethod.Parameters.Create(
"@MaxRowsReturned", true, DirectionType.In);
// Create the TypeDescriptor for the MaxRowsReturned parameter.
// using the Filter we have created.
TypeDescriptor maxRowsReturnedTypeDescriptor =
maxRowsReturnedParameter.CreateRootTypeDescriptor(
"MaxRowsReturned",
true,
"System.Int64",
"MaxRowsReturned",
null,
limitRowsReturnedFilter,
TypeDescriptorFlags.None,
null,
catalog);
// Create the Contacts return parameter.
Parameter contactsParameter =
getContactsMethod.Parameters.Create(
"GetEmployees", true, DirectionType.Return);
// Create the TypeDescriptors for the Contacts return parameter.
TypeDescriptor returnRootCollectionTypeDescriptor =
contactsParameter.CreateRootTypeDescriptor(
"Employees",
true,
"System.Data.IDataReader, System.Data, Version=4.0.0.0," +
" Culture=neutral, PublicKeyToken=b77a5c561934e089",
"Employees",
null,
null,
TypeDescriptorFlags.IsCollection,
null,
catalog);
TypeDescriptor returnRootElementTypeDescriptor =
returnRootCollectionTypeDescriptor.ChildTypeDescriptors.Create(
"Employee",
true,
"System.Data.IDataRecord, System.Data, Version=4.0.0.0," +
" Culture=neutral, PublicKeyToken=b77a5c561934e089",
"Employee",
null,
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"BusinessEntityID",
true,
"System.Int32",
"BusinessEntityID",
new IdentifierReference("BusinessEntityID",
new EntityReference("AdventureWorks", "Employee", catalog),
catalog),
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"LastName",
true,
"System.String",
"LastName",
null,
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"FirstName",
true,
"System.String",
"FirstName",
null,
null,
TypeDescriptorFlags.None,
null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
"Title",
true,
"System.String",
"Title",
null,
null,
TypeDescriptorFlags.None,
null);
// Create the finder method instance
MethodInstance readListMethodInstance =
getContactsMethod.MethodInstances.Create(
"GetEmployees",
true,
returnRootCollectionTypeDescriptor,
MethodInstanceType.Finder,
true);
readListMethodInstance.Properties.Add("RootFinder", "");
// Set the default value for the number of rows
// to be returned filter.
// NOTE: The method instance needs to be created first
// before we can set the default value.
maxRowsReturnedTypeDescriptor.SetDefaultValue(
readListMethodInstance.Id, Int64.Parse("10000000"));
}
/// <summary>
/// Returns the AdministrationMetadataCatalog from the Url of the Site or WebApplication.
/// </summary>
/// <param name="siteUrlProperty">SiteUrl property used to get the SPSite.</param>
/// <returns>AdministrationMetadataCatalog object.</returns>
private void CreateAdministrationMetadataCatalog(SPFeatureProperty siteUrlProperty)
{
SPServiceContext context = null;
SPSite site = null;
try
{
site = GetSite(siteUrlProperty);
context = SPServiceContext.GetContext(site);
}
finally
{
if (site != null)
{
site.Dispose();
}
}
var bdcService = parentFarm.Services.GetValue<BdcService>();
if (bdcService == null)
{
throw new InvalidOperationException("Unable to contact BdcService.");
}
amc = bdcService.GetAdministrationMetadataCatalog(context);
if (amc == null)
{
throw new InvalidOperationException("Unable to create AdministrationMetadataCatalog.");
}
}
/// <summary>
/// Creates the finder Method, specify the query it will use, and define the output parameters associated with it.
/// The finder Method returns all of the rows of data from the data source which its query defines.
/// </summary>
/// <param name="name"></param>
/// <param name="table"></param>
/// <param name="lobSystemName"></param>
/// <param name="referenceList"></param>
/// <param name="catalog"></param>
/// <param name="entity"></param>
private static void CreateReadListMethod(string name, string table, string lobSystemName, List<ExternalColumnReference> referenceList, AdministrationMetadataCatalog catalog, Entity entity)
{
string listMethodName = String.Format("Get{0}List", name);
string listMethodEntity = name + "List";
string itemMethodEntity = name;
var identifierField = referenceList.Where(x => x.IsKey).ToList().First();
if (identifierField == null)
throw new NullReferenceException("Could not get identifier field.");
// Create the Finder method
Method getListMethod = entity.Methods.Create(listMethodName, true, false, table); // itemMethodEntity
// Specify the query
// "SELECT [CustomerId] , [FirstName] , [LastName] , [Phone] , [EmailAddress] , [CompanyName] FROM [Customers].[SalesLT].[Customer]"
string queryAllItemsString = "SELECT TOP(@RowLimit) ";
foreach(ExternalColumnReference reference in referenceList)
{
queryAllItemsString += "[" + reference.SourceName + "], ";
}
queryAllItemsString = queryAllItemsString.Substring(0, queryAllItemsString.Length - 2);
queryAllItemsString += " FROM [" + table + "]";
var whereClause = " WHERE";
foreach (ExternalColumnReference reference in referenceList)
{
if (reference.IsSearchField)
{
if (reference.Type == "System.String")
{
whereClause += String.Format(" ((@{1} IS NULL) OR ((@{1} IS NULL AND [{0}] IS NULL) OR [{0}] LIKE @{1})) AND", reference.SourceName, reference.DestinationName);
}
else
{
whereClause += String.Format(" ((@{1} = N'0') OR ((@{1} IS NULL AND [{0}] IS NULL) OR [{0}] = @{1})) AND", reference.SourceName, reference.DestinationName);
}
}
}
if (whereClause.Length == 7)
whereClause = "";
else
{
whereClause = whereClause.Substring(0, whereClause.Length - 4);
}
queryAllItemsString += whereClause;
// Set method properties
getListMethod.Properties.Add("RdbCommandText", queryAllItemsString);
getListMethod.Properties.Add("RdbCommandType", "Text");
getListMethod.Properties.Add("BackEndObjectType", "SqlServerTable");
getListMethod.Properties.Add("BackEndObject", table);
getListMethod.Properties.Add("Schema", "dbo");
// Create the Entity return parameter
Parameter modelParameter = getListMethod.Parameters.Create(name, true, DirectionType.Return);
// Create the TypeDescriptors for the Entity return parameter
TypeDescriptor returnRootCollectionTypeDescriptor =
modelParameter.CreateRootTypeDescriptor(
listMethodEntity,
true,
"System.Data.IDataReader, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
listMethodEntity,
null,
null, // filter descriptor
TypeDescriptorFlags.IsCollection,
null,
catalog);
TypeDescriptor returnRootElementTypeDescriptor =
returnRootCollectionTypeDescriptor.ChildTypeDescriptors.Create(
itemMethodEntity,
true,
"System.Data.IDataRecord, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
itemMethodEntity,
null,
null,
TypeDescriptorFlags.None,
null);
// Create a Filter so that we can limit the number
// of rows returned;
// otherwise we may exceed the list query size threshold.
FilterDescriptor limitRowsReturnedFilter =
getListMethod.FilterDescriptors.Create(
"RowLimitFilter", true, FilterType.Limit, identifierField.DestinationName);
limitRowsReturnedFilter.Properties.Add("IsDefault", false);
limitRowsReturnedFilter.Properties.Add("UsedForDisambiguation", false);
// Create the RowsToRetrieve input parameter.
Parameter identifierParameter =
getListMethod.Parameters.Create(
"@RowLimit", true, DirectionType.In);
// Create the TypeDescriptor for the MaxRowsReturned parameter.
// using the Filter we have created.
TypeDescriptor maxRowsReturnedTypeDescriptor =
identifierParameter.CreateRootTypeDescriptor(
"RowLimit",
true,
"System.Int64",
identifierField.SourceName,
null,
limitRowsReturnedFilter,
TypeDescriptorFlags.None,
null,
catalog);
var stringTypeDescriptorList = new List<TypeDescriptor>();
var counter = 0;
foreach (ExternalColumnReference reference in referenceList)
{
IdentifierReference identityReference = null;
if(reference.IsKey)
identityReference = new IdentifierReference(reference.DestinationName, new EntityReference("EFEXCON.ExternalLookup", itemMethodEntity, catalog), catalog);
if (reference.IsSearchField)
{
FilterType filterType = reference.Type == "System.String" ? FilterType.Wildcard : FilterType.Comparison;
FilterDescriptor filter = getListMethod.FilterDescriptors.Create(
reference.DestinationName + "Filter", true, filterType, reference.DestinationName);
filter.Properties.Add("CaseSensitive", false);
filter.Properties.Add("IsDefault", false);
filter.Properties.Add("UsedForDisambiguation", false);
filter.Properties.Add("UseValueAsDontCare", true);
filter.Properties.Add("DontCareValue", "");
// Create the filter input parameter.
Parameter filterParameter = getListMethod.Parameters.Create(
"@" + reference.DestinationName, true, DirectionType.In);
// Create the TypeDescriptor for the filter parameter.
TypeDescriptor filterParamTypeDescriptor =
filterParameter.CreateRootTypeDescriptor(
reference.DestinationName,
true,
reference.Type,
reference.SourceName,
null,
filter,
TypeDescriptorFlags.None,
null,
catalog);
if (reference.Type == "System.String")
stringTypeDescriptorList.Add(filterParamTypeDescriptor);
if (counter > 0)
filterParamTypeDescriptor.Properties.Add("LogicalOperatorWithPrevious", "And");
counter++;
}
var childTypeDescriptor = returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
reference.DestinationName,
true,
reference.Type,
reference.SourceName,
identityReference,
null,
TypeDescriptorFlags.None,
null
);
childTypeDescriptor.Properties.Add("ShowInPicker", true);
}
// Create the finder method instance
MethodInstance readListMethodInstance =
getListMethod.MethodInstances.Create(
listMethodName,
true,
returnRootCollectionTypeDescriptor,
MethodInstanceType.Finder,
true);
readListMethodInstance.Properties.Add("RootFinder", "");
// Set the default value for the number of rows
// to be returned filter.
// NOTE: The method instance needs to be created first
// before we can set the default value.
maxRowsReturnedTypeDescriptor.SetDefaultValue(
readListMethodInstance.Id, Int64.Parse("30"));
foreach(var typeDescriptor in stringTypeDescriptorList)
{
typeDescriptor.SetDefaultValue(
readListMethodInstance.Id, "");
}
}
/// <summary>
/// Create the specific finder Method, specify the query it will use, and define the input and output parameters associated with it.
/// The specific finder Method returns exactly one row of data from the data source, given an identifier.
/// </summary>
/// <param name="name"></param>
/// <param name="table"></param>
/// <param name="lobSystemName"></param>
/// <param name="catalog"></param>
/// <param name="entity"></param>
private static void CreateReadItemMethod(string name, string table, string lobSystemName, List<ExternalColumnReference> referenceList, AdministrationMetadataCatalog catalog, Entity entity)
{
uint language = SPContext.Current.Web != null ? SPContext.Current.Web.Language : 1033;
string itemMethodName = "Get" + name;
string listMethodEntity = name + "List";
string itemMethodEntity = name;
ExternalColumnReference keyColumn = null;
Method getItemMethod = entity.Methods.Create(itemMethodName, true, false, table);
// Specify the query
// "SELECT [CustomerId] , [FirstName] , [LastName] , [Phone] , [EmailAddress] , [CompanyName] FROM [Customers].[SalesLT].[Customer] WHERE [CustomerId] = @CustomerId"
string querySingleItemString = "SELECT ";
string whereClause = "";
foreach (ExternalColumnReference reference in referenceList)
{
querySingleItemString += "[" + reference.SourceName + "], ";
if(reference.IsKey)
{
keyColumn = reference;
whereClause = "[" + reference.SourceName + "] = @" + reference.DestinationName;
}
}
querySingleItemString = querySingleItemString.Substring(0, querySingleItemString.Length - 2);
querySingleItemString += " FROM [" + table + "] WHERE " + whereClause;
// Set the method properties
getItemMethod.Properties.Add("RdbCommandText", querySingleItemString);
getItemMethod.Properties.Add("RdbCommandType", "Text");
getItemMethod.Properties.Add("BackEndObjectType", "SqlServerTable");
getItemMethod.Properties.Add("BackEndObject", table);
getItemMethod.Properties.Add("Schema", "dbo");
// Create the EntityID input parameter
if (keyColumn == null)
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Creator_KeyColumn", "Resources", language);
throw new NullReferenceException(message);
}
string idParameter = "@" + keyColumn.DestinationName;
Parameter entityIdParameter = getItemMethod.Parameters.Create(idParameter, true, DirectionType.In);
// Create the TypeDescriptor for the EntityID parameter
entityIdParameter.CreateRootTypeDescriptor(
keyColumn.DestinationName,
true,
keyColumn.Type,
keyColumn.SourceName,
new IdentifierReference(keyColumn.DestinationName, new EntityReference("EFEXCON.ExternalLookup", itemMethodEntity, catalog), catalog),
null,
TypeDescriptorFlags.None,
null,
catalog);
// Create the Entity return parameter
Parameter modelParameter = getItemMethod.Parameters.Create(itemMethodEntity, true, DirectionType.Return);
// Create the TypeDescriptors for the Entity return parameter
TypeDescriptor returnRootCollectionTypeDescriptor =
modelParameter.CreateRootTypeDescriptor(
listMethodEntity,
true,
"System.Data.IDataReader, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
listMethodEntity,
null,
null,
TypeDescriptorFlags.IsCollection,
null,
catalog);
TypeDescriptor returnRootElementTypeDescriptor =
returnRootCollectionTypeDescriptor.ChildTypeDescriptors.Create(
itemMethodEntity,
true,
"System.Data.IDataRecord, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
itemMethodEntity,
null,
null,
TypeDescriptorFlags.None,
null);
foreach (ExternalColumnReference reference in referenceList)
{
IdentifierReference identityReference = null;
if (reference.IsKey)
{
identityReference = new IdentifierReference(reference.DestinationName, new EntityReference("EFEXCON.ExternalLookup", itemMethodEntity, catalog), catalog);
}
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create(
reference.DestinationName,
true,
reference.Type,
reference.SourceName,
identityReference,
null,
TypeDescriptorFlags.None,
null
);
}
// Create the specific finder method instance
getItemMethod.MethodInstances.Create(itemMethodName, true, returnRootElementTypeDescriptor, MethodInstanceType.SpecificFinder, true);
}