How do I create a Data Access Layer objects from database first approach?

Question

I have built a SQL database with my CustomerDetails, ProductDetails, OrderDetails from scratch and also stored procedures to create new Customers, new Products and Orders.

So now I want to build a data access layer from C#. I don't know how to associate the entity framework data object declaration such as the following code to map my SQL data tables, because using the following code will be a code first approach and entity framework will generate the SQL code for me. I don't want EF generated SQL code because it is so buggy.

public int ProductID { get; set; }
public string ProductName { get; set; }
public string Description { get; set; }
public int? OrderID { get; set; }

I have checked out the following reference which is so daunting for me, because a lot of reference pages are out of date (downloads are no longer available and the User Interface of Visual Studio has been completely changed since), and some are involved in MVC which make things unnecessarily complicated.

https://learn.microsoft.com/en-us/aspnet/web-forms/overview/data-access/introduction/

I have also checked out using Table Adapters. But it doesn't allow me to create data objects. All data objects are automatically generated.

So how do I hand-code explicitly both SQL code and data object code if you know what I mean? My goal is to be able model my data objects according to the SQL table and call stored procedures already created in SQL to perform all kinds of operations. Please let me know where I can take reference.

github.com/sjh37/… <-- This T4 script does everything you need. EF Core also has its own scaffolding, but last time I checked templates weren't customizable and overall it left leaving a lot to be desired. — Dai
– Dai, Commented May 18, 2022 at 22:58
What you probably need is an EDMX (database first approach), with it you can update EF model from database - here's a similar q+a on SO on how to create it: stackoverflow.com/questions/51487232/…. And a step by step description can be found here: yogihosting.com/entity-framework-create-edmx-file. Let me know if it helped. — Matt
– Matt, Commented May 19, 2022 at 6:42
yeah, i realised it:s called database first approach. I am studying this youtube.com/watch?v=ZX7_12fwQLU&t=38s — Hoy Cheung
– Hoy Cheung, Commented May 19, 2022 at 19:27
@Matt EDMX can be considered deprecated. Certainly not something use for new code. — Gert Arnold
– Gert Arnold, Commented May 22, 2022 at 15:35

Matt · Accepted Answer · 2022-05-23 07:06:39Z

1

From what I saw you are trying to do databasefist approach so you need to do the following things:

First you need to open the package manager console. That is from
View -> Other Windows -> Package Manager Console.
There you need to make sure that the default project is the project you want to work with.

Then run this command

Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer 
                   -Connection "Data Source=.\SQLExpress;Initial Catalog=DatabaseName;Integrated Security=True"

Change the "Data Source" to your SQL instance and "Initial Catalog" with your database name

(This will work only if you use SqlServer)

edited May 23, 2022 at 7:06

Matt

27.4k19 gold badges131 silver badges202 bronze badges

answered May 21, 2022 at 17:23

kddinev18

744 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I create a Data Access Layer objects from database first approach?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related