MVC4列出数据库中的可用表 [英] MVC4 Listing available tables in database

问题描述

我需要使用ASP.NET MVC4 Framework来构建一个数据管理工具，该工具将对用户提供的数据执行查询。由于数据将由用户提供，因此表的模式每次都会不同，因此在对数据库执行查询之前，表的模型将是未知的，数据库将显示数据库中的表列表和表已被选中。



为了让事情顺利进行，我想简单地开始使用连接字符串连接到数据库并执行列出数据库中所有可用表的查询并将该列表传递给视图。



到目前为止，根据我所做的研究，我提出了以下代码：

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:

/*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.
}

当我构建并运行它时代码，我没有得到任何错误，但是，当我访问视图页面时，我得到一个空白页面，我相信是因为没有发生在我传递给SqlQuery（）的列表中。我对.NET，MVC和实体框架相当新，因此我不确定为什么会出现这种情况。



非常感谢任何帮助或指导。如果我让任何人感到困惑，我也会事先道歉。

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.

推荐答案

这里是代码只更改或添加粗体部分

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.
}

更新：



你只需要更改TableStructure calss以便与您的查询结果匹配。

查看查询：

从sys.tables选择名称作为TableName

，因为你看到我的查询中的结果是'TableName'列所以我不得不创建一个具有'TableName'属性的类。所以你不需要提到列名，只需要将结果与你的类匹配

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

这篇关于MVC4列出数据库中的可用表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！