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