So I want to get a list of all the table names from the database through a controller as an ASP.net API project. I tried to do it through raw sql query without entity and it looked something like this.
public async Task<ActionResult<IList>> GetAllTableNames()
{
using (var context = new DbContext())
{
List<string> results = context.Database.SqlQuery<string>("SELECT name FROM sys.tables").ToListAsync();
}
}
But when I try to use the SqlQuery method I get the error " 'DatabaseFacade' does not contain a definition for 'SqlQuery' and no accessible extension method 'SqlQuery' ". Anybody that has any idea how to solve this?
CodePudding user response:
First create an Helper method in your controller as shown below
using System.Data.SqlClient;
public async IAsyncEnumerable<string> GetTableNamesAsync()
{
var connection = new SqlConnection(_dbContext.Database.GetConnectionString());
var command = new SqlCommand("SELECT name FROM sys.tables",connection);
await connection.OpenAsync();
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
yield return reader.GetString(0);
}
}
Then call in your action Like this
public async Task<IActionResult> Index()
{
var list=new List<string>();
await foreach (var item in GetTableNamesAsync())
{
list.Add(item);
}
return Ok(list);
}