/// <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; } }
/// <summary> /// /// </summary> /// <param name="credentials"></param> /// <param name="connectionString"></param> /// <returns></returns> protected Boolean ConnectionStringIsValid(Credentials credentials, string connectionString) { try { using (new Impersonator(credentials.User, credentials.Domain, credentials.Password)) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); return connection.State == ConnectionState.Open; } } } catch(Exception ex) { var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Status_DataSource_Connection", "Resources", _language); Status.InnerHtml = HtmlHelper.CreateErrorString(message , ex); return false; } }
/// <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> /// <returns></returns> public Credentials GetCredentials() { uint language = SPContext.Current.Web != null ? SPContext.Current.Web.Language : 1033; Credentials userCredentials = new Credentials(); ISecureStoreProvider provider = GetSecureStoreProvider(); // Get the credentials for the user on whose behalf the code // is executing. using (SecureStoreCredentialCollection credentials = provider.GetRestrictedCredentials(_sssId)) { SecureString secureUsername = null; SecureString securePassword = null; // Look for username and password in credentials. foreach (ISecureStoreCredential credential in credentials) { switch (credential.CredentialType) { case SecureStoreCredentialType.UserName: case SecureStoreCredentialType.WindowsUserName: secureUsername = credential.Credential; break; case SecureStoreCredentialType.Password: case SecureStoreCredentialType.WindowsPassword: securePassword = credential.Credential; break; } } // Username and password have been read. if (secureUsername != null && securePassword != null) { var loginName = SecureStringToString(secureUsername); if (!loginName.Contains("\\")) { var message = SPUtility.GetLocalizedString("$Resources:ExternalLookup_Helper_DomainMissing", "Resources", language); throw new FormatException(message); } var userArray = loginName.Split('\\'); var domain = userArray[0]; var username = userArray[1]; var password = SecureStringToString(securePassword); userCredentials.Domain = domain; userCredentials.User = username; userCredentials.Password = password; } } return userCredentials; }