/// <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(); }