Generate C# Model from existing Microsoft SQL Server Database

Summary

This 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

00:00

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

05:01

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

10:05

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

Keywords

💡Visual Studio 2019

Visual Studio 2019 is an integrated development environment (IDE) from Microsoft. It is essential for creating and managing software projects, particularly for generating code-first models from an existing SQL database in a C# project. In the video, Visual Studio 2019 is used to demonstrate the setup and management of the project.

💡SQL Server

SQL Server is a relational database management system developed by Microsoft. It is used for storing and retrieving data as requested by other software applications. In the video, SQL Server is the database system from which the code-first models are generated.

💡.NET Framework

.NET Framework is a software framework developed by Microsoft that provides a controlled environment for developing and running applications. It supports the creation of applications using C#. In the video, the presenter opts to create the console application using the .NET Framework instead of .NET Core.

💡Entity Framework

Entity Framework (EF) is an open-source object-relational mapping (ORM) framework for ADO.NET. It enables developers to work with a database using .NET objects. The video demonstrates how to generate code-first models using Entity Framework from an existing database.

💡Code-First Model

Code-First Model is an approach in Entity Framework where the database schema is generated from the code. The video focuses on generating code-first models from an existing SQL database, showing how to set up and configure the database context and entities in a C# project.

💡ADO.NET Entity Data Model

ADO.NET Entity Data Model is a part of the Entity Framework, which allows developers to create a conceptual model of their data. In the video, the presenter adds an ADO.NET Entity Data Model to the project to facilitate the generation of code-first models.

💡Server Explorer

Server Explorer is a tool in Visual Studio that provides access to server resources, including databases. In the video, it is used to add a connection to the SQL Server database and manage database objects like tables.

💡Database Connection

A database connection is a connection to a database through which SQL commands and queries can be executed. The video shows how to set up a connection to the SQL Server database using Visual Studio's Server Explorer.

💡Console Application

A console application is a program that runs in a command-line interface or terminal. In the video, a console application project is created in Visual Studio to demonstrate how to generate and use code-first models from a SQL database.

💡Data Annotations

Data Annotations are attributes that can be applied to classes and properties to enforce validation rules and define database schema details. In the video, the presenter creates entity classes with properties that correspond to the database table columns, implicitly using 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