public List <string> GetTablesFromDatabase(TableConfigurationDatabaseFilterServiceModel dataToFilter)
{
try
{
if (dataToFilter.ConnectionId == default(int))
{
return(null);
}
//get Database Name from database connection
DatabaseConnection dataBaseDetails;
List <string> tableNames = new List <string>();
using (var dataBaseConnectionRepo = new RepositoryPattern <DatabaseConnection>())
{
dataBaseDetails = dataBaseConnectionRepo.SelectByID(dataToFilter.ConnectionId);
}
if (dataBaseDetails == null)
{
return(null);
}
var connectionString = "server= " + dataBaseDetails.ServerInstanceName + ";Initial Catalog=" + dataBaseDetails.DatabaseName + " ;uid=" + dataBaseDetails.UserName + ";pwd=" + dataBaseDetails.Password + ";";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string query = string.Empty;
if (dataToFilter.IsTable)
{
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';";
}
else
{
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS";
}
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
tableNames.Add(dr[0].ToString());
}
}
}
}
return(tableNames);
}
catch (Exception ex)
{
throw;
}
}
public HttpResponseMessage GetTablesFromDatabase(TableConfigurationDatabaseFilterServiceModel dataToFilter)
{
return(Request.CreateResponse(HttpStatusCode.OK, _tableConfigurationService.GetTablesFromDatabase(dataToFilter)));
}