Generate C# Model from existing Microsoft SQL Server Database
Summary
TLDRThis tutorial video guides viewers on generating code-first models from an existing MSSQL database within a C# project using Visual Studio 2019. It begins by setting up the necessary tools, including the latest Visual Studio and SQL Server, and installing the .NET workload. The presenter demonstrates how to connect to a database, create a new one if needed, and add a sample 'products' table with attributes like product ID, name, price, and description. After updating the database, the video proceeds to show how to create a new console application, add an ADO.NET Entity Data Model, and generate code-first models from the database. Finally, it illustrates how to access and print data from the database using Entity Framework, providing a practical example of integrating a database with a C# application.
Takeaways
- 😀 The video provides a tutorial on generating code-first models from an existing MSSQL database in a C# project using Visual Studio 2019.
- 🛠️ Prerequisites include having the latest version of Microsoft Visual Studio, SQL Server, and the .NET workload installed via the Visual Studio installer.
- 🖥️ The tutorial starts by opening Visual Studio 2019 and using the Server Explorer to connect to a SQL database.
- 🔗 It demonstrates how to add a new data connection using the SQL client with Windows authentication and how to create a new database if one does not exist.
- 📝 The video shows step-by-step instructions to create a new table named 'products' with columns for Product ID, Name, Price, and Description.
- 🔄 After creating the table, the tutorial explains how to update the database schema and verify the changes by refreshing the table view.
- 📈 The presenter adds sample data to the 'products' table and shows how to view the data using the Server Explorer.
- 💡 The next part of the tutorial involves creating a new Console Application project in .NET Framework, not .NET Core, to differentiate the two.
- 🔧 The process of adding an ADO.NET Entity Data Model to the project is detailed, with the selection of the database and the tables to be included in the model.
- 📑 The video shows the automatic generation of the Entity Framework DbContext and entity classes like 'Product.cs' that correspond to the database table structure.
- 🔍 Finally, the tutorial concludes with a simple example of how to retrieve and print data from the 'products' table using a for-each loop in the Console Application.
Q & A
What is the main topic of the video?
-The video is about generating code-first models from an existing MSSQL database within a C# project using Visual Studio 2019.
What are the prerequisites before starting the process shown in the video?
-The prerequisites include having the latest version of Microsoft Visual Studio, SQL Server, and the .NET workload installed using the Visual Studio installer.
How does one open the Server Explorer in Visual Studio 2019?
-To open the Server Explorer, you can go to the 'View' menu and select 'Server Explorer' from there.
What is the first step in creating a connection to a database in the video?
-The first step is to right-click on 'Data Connections' and then select 'Add Connection'.
Which SQL client is used in the video?
-The SQL client used in the video is SQL Server Express.
What type of authentication is used to connect to the database in the video?
-Windows authentication is used to connect to the database in the video.
How is a new database created in the video?
-A new database is created by entering a new database name (e.g., 'ytt111') and clicking 'OK', which prompts to create the database if it does not exist.
What is the name of the table created in the video?
-The table created in the video is named 'products'.
What are the attributes added to the 'products' table in the video?
-The attributes added to the 'products' table are 'Product ID', 'Name', 'Price', and 'Description'.
How is the database schema updated after creating the table?
-The database schema is updated by clicking on 'Update Database' in the Server Explorer.
What is the purpose of creating a new console app project in the video?
-The purpose of creating a new console app project is to demonstrate how to generate code-first models from the database using Entity Framework.
What framework is used for the console app project in the video?
-The .NET Framework is used for the console app project in the video, not .NET Core.
How is the Entity Data Model added to the project?
-The Entity Data Model is added by right-clicking on the project, selecting 'Add New Item', and choosing 'ADO.NET Entity Data Model'.
What is the method used to generate the code-first model from the database?
-The code-first model is generated by selecting 'Generate Code First from database' in the Entity Data Model Wizard.
What is the name of the entity class created for the 'products' table?
-The name of the entity class created for the 'products' table is 'Product.cs'.
How are the data from the 'products' table retrieved and displayed in the console app?
-The data is retrieved using a foreach loop that iterates over the 'products' entity set and prints out the product name and price.
Outlines
😀 Setting Up the Environment for Code Generation
This paragraph introduces the video's purpose, which is to demonstrate how to generate code-first models from an existing MSSQL database within a C# project using Visual Studio 2019. The presenter advises viewers to subscribe to the channel and outlines the prerequisites: having the latest version of Microsoft Visual Studio, SQL Server, and the .NET workload installed. The presenter then shows how to open Visual Studio, access the Server Explorer, and add a new data connection using SQL Server. They proceed to create a new database named 'ytt' and add a 'products' table with columns for product ID, name, price, and description. The table is updated, and the presenter shows how to view and add data to the 'products' table.
😀 Creating a New Project and Entity Data Model
The presenter starts by creating a new console application project in Visual Studio using the .NET Framework. They then guide the viewer on how to add a new item to the project, specifically an ADO.NET Entity Data Model named 'entity'. The goal is to generate a code-first model from the database. The presenter selects the database 'yt111' created earlier and tests the connection. They proceed to select the database tables to import and finalize the model generation. The result is an Entity Framework DbContext and an automatically generated 'product.cs' file that corresponds to the 'products' table in the database, with properties matching the table's columns.
😀 Implementing Data Access and Displaying Results
In the final paragraph, the presenter focuses on implementing data access within the console application. They show how to use a foreach loop to iterate over the 'products' entity set retrieved from the database context. The presenter writes code to print the product name and price to the console, converting the price to a string for display. The video concludes with a demonstration of running the application, which successfully prints the product data from the database. The presenter encourages viewers to explore and build upon the provided example and reminds them to subscribe to the channel for more upcoming content.
Mindmap
Keywords
💡Visual Studio 2019
💡SQL Server
💡.NET Framework
💡Entity Framework
💡Code-First Model
💡ADO.NET Entity Data Model
💡Server Explorer
💡Database Connection
💡Console Application
💡Data Annotations
Highlights
Introduction to generating code first models from an existing MSSQL database in a C# project using Visual Studio 2019.
Requirement of having the latest version of Microsoft Visual Studio and SQL Server installed.
Installation of the .NET workload through the Visual Studio installer.
Opening Visual Studio 2019 and Server Explorer to connect to the database.
Adding a new data connection and selecting the SQL client.
Entering server details and using Windows authentication for database connection.
Creating a new database if it doesn't exist within the SQL Server.
Adding a new table 'products' with attributes like product ID, name, price, and description.
Updating the database schema with the new table and attributes.
Viewing and adding sample data to the 'products' table.
Creating a new Console Application project in .NET Framework.
Adding a new ADO.NET Entity Data Model to the project.
Generating the code first model from the database using Entity Framework.
Selecting the database and tables to import into the Entity Data Model.
Automatic generation of the Entity Framework DbContext and entity classes.
Creation of a 'Product.cs' entity class that corresponds to the 'products' table.
Using a for-each loop to retrieve and print product data from the database.
Demonstration of running the application to display data from the database.
Encouragement to subscribe to the channel for more upcoming videos.
Transcripts
hello everyone
welcome back once again
in this video we'll look at how we can
generate code first models from an
existing msql database inside the
c-sharp project using visual studio 2019
but before we get started please make
sure
you subscribe to the channel if you
haven't
so let's get to it then
so to continue these are the things that
are required obviously you should have
the latest version of Microsoft
Visual Studio
and then SQL Server
you have to also make sure that you
install the.net workload using the
visual studio installer so once you've
got all these sets then
we open the visual studio right so as
you can see I've I've actually got a
visual studio 2019 opened and then I've
got the start Windows I'm just going to
close it yeah so if you look at it here
I've got the server Explorer open as
well so if you don't have it open you
can click on The View and if you find
the server Explorer here right so once
we've actually got that a bit yeah then
we can right click on the data
connections right
select and right click the data
connections and then add a connection so
here
obviously you can select your data
source okay in my case I'm selecting the
SQL client right and then you should you
have to enter your
server name in my case it's a DOT
backslash SQL Express
and then the log details yeah in my case
also is a Windows authentication right
and then here
you can enter the database that you want
to use if you don't have it you can
enter a new one and create it right in
my case I'm just going to create a new
one so I'm just going to add ytt
one one so if we click OK yeah
you see so the database does not exist
so if we click on yes then we should get
a new one created for us so we click on
yes
and then we've got a new database
created yeah so here we've actually got
nothing inside the table so we're just
going to add one table quickly right so
add a new table
so here the table we're just going to
change the table name right
and we're just going to call it products
yeah
so here we're just going to add some few
details here so the idea we're just
gonna make it
product ID
and then
name for product name so the
data type we're just going to make it
test
and then uh
we're just gonna put
price and the data type we're just gonna
make it money
right
so as you can see it's not very
difficult so we're just going to add one
more attribute description
so the description we're gonna make a
test as well
yeah so as you can see here we've got a
very simple stuff here so this is how we
actually update the database right we
click on this update
so now we can click update database here
so as you can see update completed
successfully so if we come here refresh
the table
so as you can see we've got a new table
inside
product with all the attributes yeah
so this is what we we can also do so we
right click on the table product right
and then show table data
right so here as you can see we've got
nothing inside so we can just add
data to the table
so I will just
we're just gonna add some few data so
I'm just going to post it while I do so
yeah so as you can see here I've
actually added a couple of
a couple of days at the table so you can
just refresh it yeah
and everything should be fine yeah so
the next stage
yeah the next stage this is what we're
actually going to do so I'm just going
to close this bit here so as you can see
here I've got an empty solution with
nothing inside so I'm just gonna click
on the the start window
and then creates a new project
so here I'm just going to type in
console app
so the console lab this time here we're
actually going to use the.net framework
not the.net core yeah because the the
net call I'll make a separate video on
that right
so we click on the.net framework and
then next
so here you can configure the project
details so we're not going to look to to
that
so we're just going to wait for the
project to create
so as you can see now we've actually got
a console app created now
so what we do is right click on the
project and then add new item
so here we click on the data and then
ado.net entity data model right
so here we're just going to call this
one entity
call this entity
and then here this is what we're going
to do we're actually going to generate
the code first from database yeah so
here you can also generate it from an
Entity framework designer model
oh from um and empty code first model
right so this is what we're gonna do so
we're going to generate the code first
model from the database right so now we
click on the nest
so from here we can actually select our
database so if we if you may recall
uh the the the connection string and
whatnot yeah so now we can actually
browse the data of the databases and
select the one we actually created right
so yt111 yeah so we can test the
connection
the connection is successful
so you can click on OK
so now as you can see here we've
actually got it selected here so we can
click on the nest
so here we can select whatever
attributes of that database that we want
to import
in this case we're just going to import
a DB or just for the database tables
right
so once you actually select that then we
click on the Finish
so it should take a while then so I'm
just going to pause it
so as you can see here now we've
actually got the Entity framework DB
contest and everything generated for
that for us automatically right
so if we look here as you can see we've
also got product.cs created this is
actually in line with the product table
that we actually created
here so if we look at it
so we've got all the necessary
properties inside the table
so if we look at the type name is test
the money if we look at it when we
actually created the database
the database what all the stuff that we
actually added yeah so
as you can see here now it's not it's
not it's not difficult so we've got the
entity class so we can actually up grab
the data that we actually added inside
the database by doing
um
entity
okay but we're going to use the using
entity
DB contest security
new entity right
so now we could say
DB
dot products
dots
to list
that for each so we can just look
through the products
P for each product
console
the right line
so we could just say
okay so I'm just gonna make it simple
I'm just gonna remove the whole thing
here and actually use the for each Loop
yeah so for each variable
products
in
DB
dot products
then we can write console
the right line
so now we can actually
print out
so the product name this will be
P dot name
and the price will be
something like
product dot price
so we actually convert this to string as
well
so put a small letter to C
foreign
I'm just gonna leave it just like that
that's the price right and then
console
the read line
so right now we're just going to run it
to see what actually comes out of here
so as you can see here now we've
actually got the the data from the
database
table printed out here now so I'm just
going to leave it here for you to
explore it forever so you can just look
at it and just try and build it on
yourself so once again yeah if you
haven't subscribed to the Channel please
make sure you do because I've got lots
of videos coming up and I hope this
actually video helps you
and peace
Ver Más Videos Relacionados
Complete CRUD Operation in Asp.Net C# With SQL Server Step by Step
Dream Report: Acquiring Data from SQL Server
EntityFramework Core Migrations | ASP.NET CORE | CLI Migrations | Package Manager Console Migratio
Part3 : Database Testing | How To Test Schema of Database Table | Test Cases
view data PHP
Asp.net sql server database connection tutorial
5.0 / 5 (0 votes)