/// <summary>
/// Create a new LobSystemInstance for the given LobSystem object.
/// </summary>
/// <param name="lobSystem"></param>
/// <param name="server"></param>
/// <param name="database"></param>
/// <param name="sssId"></param>
/// <param name="providerImplementation"></param>
/// <returns></returns>
public static LobSystemInstance CreateLobSystemInstance(LobSystem lobSystem, string server, string database, string sssId, string providerImplementation)
{
LobSystemInstance lobSystemInstance = null;
foreach (var instance in lobSystem.LobSystemInstances)
{
if (instance.Name == lobSystem.Name && instance.LobSystem == lobSystem)
{
lobSystemInstance = instance;
}
}
if(lobSystemInstance == null)
{
lobSystemInstance = lobSystem.LobSystemInstances.Create(lobSystem.Name, true, lobSystem);
}
// Set the connection properties
// The following url helps to understand the different authentication modes and the relation to the ones
// given in SharePoint Designer: https://technet.microsoft.com/en-us/library/ee661743.aspx#Section3
lobSystemInstance.Properties.Add("AuthenticationMode", "WindowsCredentials");
lobSystemInstance.Properties.Add("DatabaseAccessProvider", "SqlServer");
lobSystemInstance.Properties.Add("RdbConnection Data Source", server);
lobSystemInstance.Properties.Add("RdbConnection Initial Catalog", database);
lobSystemInstance.Properties.Add("RdbConnection Integrated Security", "SSPI");
lobSystemInstance.Properties.Add("RdbConnection Pooling", "false");
lobSystemInstance.Properties.Add("SsoApplicationId", sssId);
lobSystemInstance.Properties.Add("SsoProviderImplementation", providerImplementation);
return lobSystemInstance;
}
public ArtDevBCS LobSystemInit(string name)
{
this.lobSystemName = name;
// Make a new Employee LobSystem
// NOTE: Assume that the "LobSystemName" LobSystem
// does not already exist.
this.lobSystem = this.Model.OwnedReferencedLobSystems.Create(name, true, SystemType.Database);
// Make a new AdventureWorks LobSystemInstance.
this.lobSystemInstance = this.lobSystem.LobSystemInstances.Create(name, true);
return(this);
}
/// <summary>
///
/// </summary>
/// <param name="lobSystem"></param>
/// <returns></returns>
public static Microsoft.SharePoint.BusinessData.Administration.PropertyCollection GetLobSystemInstanceProperties(LobSystem lobSystem)
{
uint language = SPContext.Current.Web != null ? SPContext.Current.Web.Language : 1033;
List<LobSystemInstance> list = lobSystem.LobSystemInstances.ToList();
if (!list.Any())
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_LobSystem", "Resources", language);
throw new Exception(message);
}
LobSystemInstance instance = list[0];
return instance.Properties;
}
/// <summary>
///
/// </summary>
/// <param name="lobSystem"></param>
/// <returns></returns>
public static List<String> GetTablesForLobSystem(LobSystem lobSystem, Credentials credentials)
{
uint language = SPContext.Current.Web != null ? SPContext.Current.Web.Language : 1033;
var connectionString = GetDatabaseConnectionString(lobSystem);
string database =
connectionString.Split(';').Where(x => x.StartsWith("Database")).Select(x => x.Split('=')[1]).ToArray()[
0];
if (String.IsNullOrEmpty(database))
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_Database", "Resources", language);
throw new NoNullAllowedException(message);
}
try
{
using (new Impersonator(credentials.User, credentials.Domain, credentials.Password))
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
var commandString = String.Format("SELECT TABLE_NAME FROM {0}.INFORMATION_SCHEMA.Tables",
database);
SqlCommand cmd = new SqlCommand(commandString, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
var result = new List<String>();
while (reader.Read())
{
result.Add(reader.GetString(0));
}
return result;
}
}
}
catch (Exception e)
{
return null;
}
}
public static bool HasOnlyInactiveEntities(this LobSystem lobSystem)
{
if (lobSystem == null)
{
throw new ArgumentNullException("lobSystem");
}
if (lobSystem.GetEntityCount() == 0)
{
return(true);
}
foreach (Entity entity in lobSystem.Entities)
{
if (entity.Active)
{
return(false);
}
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="lobSystem"></param>
/// <returns></returns>
public static Credentials GetCredentialsFromLobSystem(LobSystem lobSystem)
{
uint language = SPContext.Current.Web != null ? SPContext.Current.Web.Language : 1033;
var sssId = "";
var providerimplementation = "";
foreach (Property prop in SqlHelper.GetLobSystemInstanceProperties(lobSystem))
{
if (prop.Name == "SsoApplicationId")
sssId = prop.Value.ToString();
if (prop.Name == "SsoProviderImplementation")
providerimplementation = prop.Value.ToString();
}
if (String.IsNullOrEmpty(sssId))
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_SecureStore", "Resources", language);
throw new Exception(message);
}
if (String.IsNullOrEmpty(providerimplementation))
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_Provider", "Resources", language);
throw new Exception(message);
}
var credentials = new SecureStoreHelper(sssId, providerimplementation).GetCredentials();
if (credentials == null)
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_Credentials", "Resources", language);
throw new NoNullAllowedException(message);
}
return credentials;
}
/// <summary>
///
/// </summary>
/// <param name="lobSystem"></param>
/// <param name="credentials"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static List<TableColumn> GetTableStructure(LobSystem lobSystem, Credentials credentials, string tableName)
{
var connectionString = GetDatabaseConnectionString(lobSystem);
try
{
using (new Impersonator(credentials.User, credentials.Domain, credentials.Password))
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
var commandString =
String.Format(
"SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{0}'",
tableName);
SqlCommand cmd = new SqlCommand(commandString, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
var result = new List<TableColumn>();
while (reader.Read())
{
result.Add(new TableColumn()
{
Catalog = reader.GetString(0),
Schema = reader.GetString(1),
Name = reader.GetString(2),
Type = reader.GetString(3),
Nullable = reader.GetString(4)
});
}
return result;
}
}
}
catch (Exception e)
{
return null;
}
}
/// <summary>
///
/// </summary>
/// <param name="lobSystem"></param>
/// <returns></returns>
private static string GetDatabaseConnectionString(LobSystem lobSystem)
{
uint language = SPContext.Current.Web != null ? SPContext.Current.Web.Language : 1033;
string server = "";
string database = "";
foreach (Property prop in SqlHelper.GetLobSystemInstanceProperties(lobSystem))
{
if (prop.Name == "RdbConnection Data Source")
server = prop.Value.ToString();
if (prop.Name == "RdbConnection Initial Catalog")
database = prop.Value.ToString();
}
if (String.IsNullOrEmpty(server))
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_Server", "Resources", language);
throw new NoNullAllowedException(message);
}
if (String.IsNullOrEmpty(database))
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_Database", "Resources", language);
throw new NoNullAllowedException(message);
}
// Good read on connection strings and integrated security:
// http://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi
return String.Format("Server={0};Database={1};Integrated Security=SSPI;",
server, database);
}
// 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();
}
}
/// <summary>
/// Create a new external content type in the Business Data Connectivity Service.
/// </summary>
/// <param name="name"></param>
/// <param name="table"></param>
/// <param name="referenceList"></param>
/// <param name="lobSystem"></param>
public static void CreateNewContentType(string name, string table, List<ExternalColumnReference> referenceList, LobSystem lobSystem)
{
uint language = SPContext.Current.Web != null ? SPContext.Current.Web.Language : 1033;
SPWeb web = SPContext.Current.Web;
BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);
SPServiceContext context = SPServiceContext.GetContext(web.Site);
AdministrationMetadataCatalog catalog =
service.GetAdministrationMetadataCatalog(context);
// Create a new customer model
Model model = Model.Create(name + "Model", true, catalog);
string lobSystemInstanceName;
IEnumerator<LobSystemInstance> enumerator = lobSystem.LobSystemInstances.GetEnumerator();
enumerator.MoveNext();
if(enumerator.Current != null)
{
lobSystemInstanceName = enumerator.Current.Name;
}
else
{
var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_LobSystem", "Resources", language);
throw new LobGenericException(message);
}
// Create a new Entity
Entity entity = Entity.Create(name, "EFEXCON.ExternalLookup", true, new Version("1.0.0.0"), 10000, CacheUsage.Default, lobSystem, model, catalog);
// Set the identifier
ExternalColumnReference keyReference = referenceList.First(x => x.IsKey == true);
entity.Identifiers.Create(keyReference.DestinationName, true, keyReference.Type);
// Create the specific finder method to return one specific element
Creator.CreateReadItemMethod(name, table, lobSystem.Name, referenceList, catalog, entity);
// Create the finder method to return all rows
Creator.CreateReadListMethod(name, table, lobSystem.Name, referenceList, catalog, entity);
// Validate the entity before activating it
entity.Validate();
// Publish the newly created Entity to the BCS Metadata Store.
entity.Activate();
}
