MVC4 Listing available tables in database

Question

4.00/5 (1 vote)

See more:

I am required to use use ASP.NET MVC4 Framework to build a data management tool that will perform queries on user provided data. Since the data will be provided by the user, the schema of the table will be different each time, hence the model for the table will be unknown until a query is performed on the database which will show a list of tables in the database and a table is selected.

To get things going, I want to simply start by connecting to a database using a connection-string and performing a query that lists all of the available tables in the database and pass that list to a View.

So far, based on the research that I've done, I have come up with the following code:

C#

/*Inside the model*/
public class DbTablesContext : DbContext
{
    public DbTablesContext(string connectionstring) : base(connectionstring)
    {
    }
}

/*Inside the controller*/ 
public ActionResult Index()
{
    string constring= ConnectionString();
    List<string> data = new List<string>();

    DbTablesContext db = new DbTablesContext(constring);
    
    db.Database.Connection.Open();
    db.Database.SqlQuery(typeof(List<string>), "select * from sys.tables", data);
    db.Database.Connection.Close();

    ViewData["data"] = data;
    
    
    return View();
}

public string ConnectionString()
{
    SqlConnectionStringBuilder sqlbuild = new SqlConnectionStringBuilder();

    sqlbuild.DataSource = "(LocalDB)\\v11.0";
    sqlbuild.InitialCatalog = "playDB";

    return sqlbuild.ConnectionString;
}

/*Inside the view */  
@{
    ViewBag.Title = "Index";
}

@{

//foreach loop for @ViewData["data"]
//Since the list returns empty, I'm unsure how the for-each loop will work.
}

When I build and run this code, I do not get any errors, however, when I access the view page, I get a blank page which I believe is because nothing is happening to the list I am passing to SqlQuery(). I am fairly new with the .NET,MVC and Entity Frameworks, hence I am not sure why this behavior is occurring.

Any help or guidance with this is highly appreciated. I also apologize in advance if I am confusing anyone.

Posted 23-Jun-14 8:25am

MassMan07

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jafarinejadvazifehkhorani · Answer 1 · 2014-06-23T10:50:00

here is the code only bold parts are changed or added

/*Inside the model*/
public class DbTablesContext : DbContext
{
    public DbTablesContext(string connectionstring) : base(connectionstring)
    {
    }
}

public class TableStructure
  {
      public string TableName { get; set; }
  }
 
/*Inside the controller*/ 
public ActionResult Index()
{
  List<tablestructure> listOfTable = new List<tablestructure>();
using (
                DbTablesContext db = new DbTablesContext(ConnectionString())
                )
            {
                listOfTable = db.Database.SqlQuery<tablestructure>("select name as TableName  from sys.tables").ToList();
            }

            ViewData["data"] = listOfTable;
            return View();
</tablestructure></tablestructure></tablestructure>}
 
public string ConnectionString()
{
    SqlConnectionStringBuilder sqlbuild = new SqlConnectionStringBuilder();
 
    sqlbuild.DataSource = "(LocalDB)\\v11.0";
    sqlbuild.InitialCatalog = "playDB";
 
    return sqlbuild.ConnectionString;
}
 
/*Inside the view */  
@{
    ViewBag.Title = "Index";
}
 
@{
 
//foreach loop for @ViewData["data"]
//Since the list returns empty, I'm unsure how the for-each loop will work.
}

Updated:

you only need to change TableStructure calss in order to be matched with your query result.
look at the query :
select name as TableName from sys.tables
as you see the result in my query is 'TableName' column so i had to create a class with 'TableName' property that was the idea. so You don't need to mention the column names just the result should be matched with your class