Generate C# Model from existing Microsoft SQL Server Database

Summary

In this engaging tutorial, the video creator, "Hacked," demonstrates how to generate C# code-first models from a Microsoft SQL Server database using Visual Studio 2019. The process starts with ensuring that all necessary software, like the latest version of Visual Studio and SQL Server, is installed. The video walks viewers through creating a new SQL database, populating it with tables, and using the .NET Framework to build a console app. Through a step-by-step guide, Hacked illustrates how to generate the code-first model, retrieve data, and run it to verify the results. It's an informative session aimed at helping developers efficiently utilize existing databases to create models in C# projects.

Highlights

• Start by creating a new database and table in SQL Server through Visual Studio. 📅
• Select needed attributes and use Entity Framework to generate models from the database. 🛠️
• Fetch data seamlessly from the database and display it using a simple C# console app. 💻

Key Takeaways

• Ensure Visual Studio 2019 and SQL Server are installed before starting! 🎯
• Learn to create new databases and tables directly from Visual Studio's Server Explorer. 🗄️
• Use .NET Framework to generate code-first models easily. 🚀
• Retrieve and manipulate database data using Entity Framework DB Context. 📊
• Understand the structure of Entity Framework and its automatic model generation. 🤖

Overview

The video kicks off with the necessary preparations, ensuring you have the latest Visual Studio and SQL Server ready and configured with a .NET workload. This setup is essential for proceeding with the project. Following setup, viewers are led through creating a new SQL database and tables within Visual Studio, which serves as the foundation for generating models.

Hacked demonstrates how to generate a code-first model from the existing database using Visual Studio 2019. The walkthrough includes configuring the project settings, utilizing the Entity Framework, and setting attributes to correspond with the database table structure. This section rightly emphasizes the link between database design and C# model creation.

Finally, the video wraps by showing how to fetch data from the newly created models using a console app, leveraging Entity Framework DB Context to pull data efficiently. Hacked simplifies complex topics and encourages viewers to explore further, solidifying their understanding of integrating databases into C# development.

Chapters

• 00:00 - 00:30: Introduction and Requirements The chapter introduces the process of generating code-first models from an existing MS SQL database using Visual Studio 2019 within a C# project. It emphasizes the importance of subscribing to the channel before proceeding. As a preparatory step, it lists the necessary requirements needed to perform the operations discussed.
• 00:30 - 01:30: Setting Up Visual Studio and SQL Server The chapter provides a detailed guide on setting up Microsoft Visual Studio and SQL Server. It emphasizes the importance of installing the .NET workload using the Visual Studio installer. Once the installations are complete, the chapter walks through the process of opening Visual Studio. It mentions Visual Studio 2019 specifically, noting the presence of a start window, which can be closed, and the importance of the Server Explorer, including steps to open it if not already visible.
• 01:30 - 03:30: Adding a Connection and Creating a Database In this chapter, the process of adding a connection and creating a database is described. It begins with navigating to the server Explorer, then right-clicking on data connections to add a new connection. The user needs to select the appropriate data source, such as SQL client, and provide the server name, for example, DOT backslash SQL Express.
• 03:30 - 04:30: Creating and Updating Database Tables The chapter discusses the process of creating and updating database tables, specifically through a Windows authentication system. If a database does not exist, one can be created right from the interface. In the example, a new database named 'ytt one one' is created after realizing the specified one does not exist.
• 04:30 - 05:30: Inserting Data into Tables In this chapter, we discuss the process of inserting data into tables. It is demonstrated how to create a new database and add a new table within it. The chapter begins with confirming the creation of a database by clicking 'yes'. Subsequently, it walks through renaming and preparing the table for data insertion. The primary focus is on setting up the initial components required for database table operations.
• 05:30 - 06:30: Creating a Console App and Adding ADO.NET Entity Data Model In this chapter, a console application is created, and an ADO.NET Entity Data Model is added. Initially, the process involves defining a simple data model for products, including fields for product ID and product name. However, the data type to be used for the product name is referred to as 'test,' suggesting a placeholder or potential misunderstanding. The focus is on setting up the groundwork for a product database within this application.
• 06:30 - 07:30: Generating Code First Model from Database In this chapter, the process of generating a 'Code First' model from an existing database is explained. The speaker illustrates how to add attributes to a model in code, specifically using the 'price' attribute with a money data type and a 'description' attribute with a text data type. The explanation emphasizes the simplicity of adding these attributes to the model during the code-first approach.
• 07:30 - 08:30: Entity Framework DB Context and Class Generation This chapter explains how to update a database using Entity Framework. It begins by demonstrating the process of clicking 'update database' and confirming that the update was successful. A practical example is shown where a table is refreshed, and a new table appears, confirming the update's success.
• 08:30 - 10:30: Using Entity Framework to Access Data This chapter explores how to access data using Entity Framework. The discussion begins with the process of right-clicking on a product table and selecting the 'Show Table Data' option to view its contents. It explains that initially, the table might be empty and guides on how to add data to it.
• 10:30 - 12:00: Conclusion and Call to Subscription In this concluding chapter, the speaker demonstrates the process of adding data to a table, emphasizing the ease of refreshing the table to incorporate changes. The focus is on ensuring the data updates smoothly and underscores a call to action, encouraging subscription for more content.

Generate C# Model from existing Microsoft SQL Server Database Transcription

• 00:00 - 00:30 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
• 00:30 - 01:00 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
• 01:00 - 01:30 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
• 01:30 - 02:00 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
• 02:00 - 02:30 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
• 02:30 - 03:00 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
• 03:00 - 03:30 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
• 03:30 - 04:00 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
• 04:00 - 04:30 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
• 04:30 - 05:00 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
• 05:00 - 05:30 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
• 05:30 - 06:00 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
• 06:00 - 06:30 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
• 06:30 - 07:00 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
• 07:00 - 07:30 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
• 07:30 - 08:00 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
• 08:00 - 08:30 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
• 08:30 - 09:00 the database by doing um entity okay but we're going to use the using entity DB contest security new entity right
• 09:00 - 09:30 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
• 09:30 - 10:00 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
• 10:00 - 10:30 products in DB dot products then we can write console the right line so now we can actually print out
• 10:30 - 11:00 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
• 11:00 - 11:30 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
• 11:30 - 12:00 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