.net核心数据库连接 [英] .net core db connection

问题描述

您好， 
$


我在asp.net core 2.1 API工作，我遵循3层架构。 API - 业务层 - 数据访问层



目前我遇到连接数据库的问题。我不知道如何将连接字符串从API传递给DAL。在示例代码下面，我尝试过但不工作..

Hello, 

I am working in asp.net core 2.1 API and I am following 3-tier architecture. API – Business layer – Data Access Layer

Currently I am facing problem with connecting to database.i am not sure how to pass the connection string from API to DAL. Below the sample code I tried and not working..

public class CustomerDB : DbContext
    {

public CustomerDB (DbContextOptions<CustomerDB> options)
            : base(options)
        {
             
        }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(//ConnectionString);
            }
        }

}

startup.cs

startup.cs

    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }
            services.AddDbContext<CustomerDB>(options => options.UseSqlServer(Configuration.GetConnectionString("ConnectionString")));
}

样本数据库调用方法：

Datatable dtCustomers = new Datatable();
            using (CustomerDB dbConnect = new CustomerDB ())
            {
                dbConnect.Database.OpenConnection();
                DbCommand cmd = dbConnect.Database.GetDbConnection().CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.Getcustomers"; 
                using (var reader = cmd.ExecuteReader())
                {
                    //parse and bind the reader data to datatable
                }
                return dtCustomers 
            }
        }

不确定谁将连接字符串传递给配置方法。我试图创建设置连接字符串这个DAL 级别（硬编码），它工作。如下所示

Not sure who to get the connection string the pass it to onconfiguring method. i tried to create set the connection string this DAL  level (hard coded) and it worked. like below

public class CustomerDB : DbContext
    {

public CustomerDB (DbContextOptions<CustomerDB> options)
            : base(options)
        {
             
        }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Server=178.40.132.28;Initial Catalog=customers;User ID=XXXXXX;Password=XXXX;Trusted_Connection=False");
            }
        }

}

但我不想在这个DAL中使用硬编码。它必须来自API。请帮助我如何实现这一目标。任何示例代码都将受到高度赞赏

but i don't want to hard code in this DAL. it has to come from API. please help me on how to achieve this. any sample code would be highly appreciated

love dotnet

loving dotnet

推荐答案

我们将从数据表开始。虽然您可以使用数据表，但它不适用于使用装箱和拆箱的高性能Web应用程序。

We'll start with the datatable. Although you can use a datatable, it is not optimal usage in high performance Web applications, which uses boxing and unboxing.

https://dzone.com/articles/reasons-move-datatables

https://dzone.com/articles/reasons-move-datatables

https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

您正在使用MS SQL Server Command对象和datareader。所以你应该使用的是DTO模式，并通过图层在集合中发送DTO或DTO，并将它们用于数据库的CRUD操作。

You are using MS SQL Server Command objects and a datareader. So what you should be using is the DTO pattern and sending a DTO or DTO(s) in a collection through the layers and using them for CRUD operations with the database.

https：// en.wikipedia.org/wiki/Data_transfer_object

https://en.wikipedia.org/wiki/Data_transfer_object

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

您应该创建一个classlib项目，调用它来保存DTO的实体，所有项目都引用实体并了解DTO。

You should create a classlib project call it Entities to hold the DTO(s) and all projects have reference to Entities and know about the DTO(s).

你有Dbcontext的错误。

You have a misusage of the Dbcontext.

https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.dbcontext?redirectedfrom=MSDN& view = entity-framework-6.2.0

https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.dbcontext?redirectedfrom=MSDN&view=entity-framework-6.2.0

您似乎拥有通常和理想情况下使用ADO.NET Entity Framework Core的代码。因此，我甚至不知道你是如何在使用数据表时尝试模仿使用的。 

You seem to have code that would normally and ideally be working with the ADO.NET Entity Framework Core. So I don't even know how you even got to the point that you are trying to mimic the usage while using a datatable. 

你应该使用的是DAO模式DAL，将连接字符串转换为DAL到DAO并打开数据库连接，使用带有T-SQL的SQL命令对象内嵌或sproc，使用DTO模式 并关闭连接。

What you should be using is the DAO pattern in the DAL, getting the connection string into DAL to a DAO and open the database connection, use the SQL Command Objects with T-SQL in-line or sproc, using the DTO pattern  and closing the connection.

https://en.wikipedia.org/wiki/Data_access_object

https://en.wikipedia.org/wiki/Data_access_object

https://www.tutorialspoint.com/ design_pattern / data_access_object_pattern.htm

https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

您正在谈论Core 2 API，所以我假设您正在谈论WebAPI，您的目标是如何获得DAL的连接字符串。

You are talking Core 2 API so I am going to assume you are talking about WebAPI, and your goal would be how do you get the connectionstring to the DAL.

所以我要停在这里，因为我在这里错过了一些东西，或者你对于你需要做些什么才能使这项工作感到困惑。你需要解释一下你如何使用Dbcontext和你已经组装的代码。 

So I am going to stop here, becuase either I am missing something here, or you are very confused as to what you need to be doing to make this work. You need to explain as to how you came to be using Dbcontext with the code you have assembled. 

这篇关于.net核心数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！