如何使用控制器表从数据库？

问题描述

在我的许可证控制器I使用了SQL脚本来创建表。有没有什么办法，使该表用我自己的控制器和视图模型？

In my Licenses controller I used SQL scripts to create table. Is there any way to make that table use my own controller and view models?

这是我的许可证控制器的一部分：

This is part of my Licenses controller:

var list = GetAllCompanyNames();
foreach(var lists in list) {
    if (lists.Value == licenses.companyName) {
        string connectionString =
            "Data Source=(LocalDb)\\v11.0;AttachDbFilename=|DataDirectory|\\aspnet-LicenseManager-20141219012740.mdf;Initial Catalog=aspnet-LicenseManager-20141219012740;Integrated Security=True";
        using(SqlConnection con = new SqlConnection(connectionString)) {
            con.Open();
            using(SqlCommand com = new SqlCommand("CREATE TABLE " + "company_" + lists.Key + " (CompanyID INT, companyName TEXT)", con)) {
                com.ExecuteNonQuery();
            }
        }
    }
}

无法在互联网上找到任何解决方案。

Couldn't find any solution on the internet.

推荐答案

您控制器应的不的做数据库访问。这不是控制器的工作。通过把你的控制器对数据的访问，你有$ P $轻易分开pvented他们。更好的办法是把数据库访问在一个单独的图书馆，或者至少是一个单独的类。

Your controller should not be doing database access. That's not the controller's job. By tying your controller to the data access, you have prevented them from easily being separated. A better approach is to put database access in a separate library, or at the very least a separate class.

您应该定义你的模型：

//All the properties of Product should be described here, Name, description, price etc
public class Product
{
    public string Id {get; set;}
    public string Name {get; set;}
    public string Description {get; set;}
    public double Price {get; set;}
}

助手库：

//This will contain handy functions for decreasing repetitive database code
public class MsSqlDatabaseHelpers
{
    public static DataTable GetDataTable(SqlCommand command, string connectionString)
    {
       DataTable dt = new DataTable();
       using (var connection = new SqlConnection(connectionString))
       {
           command.Connection = connection;
           connection.Open();
           dt.Load(command.ExecuteReader());
       }
       return dt;
    }
}

信息库（数据访问层）：

Repository (data access layer):

//This will have methods for getting models from the database and also inserting and updating them (not shown)
public class MsSqlStoreRepository : IStoreRepository
{
    private string ConnectionString { get; set; }

    public MsSqlStoreRepository(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public List<Product> GetAllProducts()
    {
        var command = new SqlCommand("select id, name, description, price from products");
        var dt = MsSqlDatabaseHelpers.GetDataTable(command, ConnectionString);
        return dt.AsEnumerable().Select(r => GenerateProductFromDataRow(r)).ToList();
    }

    //I abstracted this logic out so we could reuse it elsewhere
    private Product GenerateProductFromDataRow(DataRow row)
    {
        return new Product()
        {
            Id = row.Field<int>("id"),
            Name = row.Field<string>("name"),
            Description = row.Field<string>("description"),
            Price = row.Field<double>("price")
        };
    }
}

然后，你的控制器会是这样的：

Then you controller would be like this:

public class ProductsController
{
    private IStoreRepository StoreRepository {get; set;}

    //When the controller is created, we'll initialize our data access layer
    public ProductsController()
    {
        StoreRepository = new MsSqlStoreRepository(Config.ConnectionString); //normally you'd use Dependency Injection instead of constructor
    }

    public ActionResult List()
    {
        var products = StoreRepository.GetAllProducts();
        return View(products);
    }
}

现在你可以必须更轻松更换你的数据访问层一个选择 - 也许你想还提供了一个 OracleStoreRepository 或 XmlStoreRepository 。

Now you can must more easily swap out your data access layer with an alternative - maybe you want to also provide an OracleStoreRepository or XmlStoreRepository.

注意，数据库没有关于该网站或控制器的想法。数据库层，它应该只知道数据库的同样的事情。

Notice that the database has no idea about the site or controllers. Same thing for the database layer, which should only know about the database.

这篇关于如何使用控制器表从数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！