sqlasp.netentity-frameworkasp.net-web-apirawsql

How to get a list of all Table names through asp.net API controller


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?


Solution

  • First create an Helper method in your controller as shown below

    using System.Data.SqlClient;

      public async IAsyncEnumerable<string> GetTableNamesAsync()
        {
            using 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);
        }