For my technology watch, I chose a theme related to the technologies I already work with.
My choice is therefore Entity Framework which is the ORM of the .net framework which is a C# framework.
An ORM (object-relational mapping) is a programming technique that allows you to transform the tables of a database into an object, that is to say that everything related to the database will now be an object, we will see later in detail what this means.
Entity framework is not basic in the project, you have to add it, to do so you just have to right click on the project and click on manage NuGet packages.
NuGet allows you to search to add packages to our project.
Then just search for Entity Framework using the input field in the top left corner.
Once found, click on EntityFramework and install it, as you can see, my EntityFramework is already installed in version 5.0.0.
Once the installation is finished, EntityFramework will be referenced in your project, if it is not, you will have to add it to use it.
Adding Entity Framework as a project reference is not enough to be able to use it, we must now link it to our database.
To right-click on the project and add a new element, then choose ADO.NET Entity Data Model.
After this addition, we have to choose the model, we will take the first choice.
The connection to the database must then be managed.
Click on new connection and a new page will appear.
The type of the data source, which I did not specify, must be of SQL Server type.
Then give the name of the database server, in my case I was local.
To finish it is necessary to select the name of our database, my database will be the one of my tutorial that we will see later.
Make test the connection to make sure it works properly and then Ok, when you return to the page of the choice of the connection do the following.
We now come to the entities that we will include in the model, in my case I only had tables to add, but it is also possible to add views or stored procedures and functions.
Finish and here is your connection between Entity Framework and your database done.
After the connection Entity Framework will generate what is called an .edmx file, it contains all the entities of your database.
In my case it appears that I have only three tables.
But you could very well have 540 tables!
You should notice that your .edmx file has subfiles, we'll go through them one by one.
The .context.tt file, unfortunately I haven't found any documentation about it so I can't talk about it.
The .context.cs file is the class of our database, it contains the name of the connection to the database and the accessors of the table classes.
The name of the connection that we see next to the :base(" name=... ") is the one that allows to make the connection to our database, it is located in our App.config file.
In order to be able to connect to the database, you have to give the same name in the name=... that allows you to connect to the database in the App.config file.
The .Designer.cs and .edmx.diagram files are for the edmx diagram shown above.
The files we are really interested in are the ones that come down from the .tt file
They contain the classes of my tables in the database, so I only have three files, but if there were 540 tables you would have 540 files.
Here is an overview of my product class for example :
In the red box appear the properties generated by Entity Framework, they allow either to obtain or to define a characteristic of the product in this case.
The laCategory property was added by me for a need in a DataGridView.
Now that the general presentation of the files has been done, we will move to a real presentation of the ORM.
As I said before, everything will now be in object, there will be no more SQL queries in your code.
But how do you proceed with these queries? Here is an example of displaying products in a DataGridView.
Here is our product display.
It took only one line to make all these displays, here it is:
We give the DataGridView a list of products.
This product list is retrieved by creating a database object
ClientProductEntities db = new ClientProductEntities(); In my case here is the object
And from this object, position yourself on products, and return this as a list.
Of course, Entity Framework features don't stop there.
dtgEntityProducts.DataSource = db.products.OrderBy(p => p.price).ToList();
This line allows to make an OrderBy on the price of the product, it will give a list of products sorted by price from the lowest to the highest price.
The reverse is possible.
dtgEntityProducts.DataSource = db.products.OrderByDescending(p => p.price).ToList();
This time, just use the word OrderByDescending.
The displayed columns are in fact the properties that are located in the class of the displayed table
For example products had the properties id, libelle, price, id_CategoryProduct and categoryProduct
For the need of this DataGridView, I had to hide the id_CategoryProduct which doesn't interest us, to hide also categoryProduct which displayed an error message in the DTG and to finish to create myself a property in the class products to allow to display only the category's libel, I then renamed the columns headers for a better visual.
With Entity Framework it is also possible to make additions, modifications and deletions in the database.
The screenshot below shows the few lines of code to make an addition.
Just create an object from the table you want to add.
Specify its characteristics as we do with libelle, libelle receives the content of the textbox entered by the user.
Then from the connection object we go to the desired class then .add() with the object to add between the parenthesis.
To finish it is enough to make a .SaveChanges() on the connection object, this method is MANDATORY to make it possible to save the changes in the database.
Here is the example of the modification, we are going to modify a product.
Here is a new method that we haven't seen yet, the Where(), you probably already know it, it allows in our case to retrieve a product whose id is equal to the indicated number.
The Single() method allows you to retrieve only one object unlike the ToList() method which returned a list.
To modify a product (or any other class) it is enough to retrieve the product and modify its characteristics directly on the object as we do with theProduct.libelle, we must do this for each field to be modified then perform the SaveChanges() which will save the modification.
The deletion is the simplest CRUD to perform, here is the :
The client is retrieved using the Where and Single method.
We call the connection object on the client class with the Remove() method, in the Remove() method we set the client or other object to be removed as needed.
And we simply save (MANDATORY) the database.
Having made the tour of the main functions, I finish this technological watch, there is still a multitude of things to learn about Entity Framework I showed only a tiny part of it.
We can therefore conclude that Entity Framework is a very fast way to make the link between a database and a program, it also allows to abandon all ideas of SQL queries and it is a very heavy weight in less if a project has a database with hundreds of tables.
Thanks to all these links and a lot of research on the internet I managed to retrieve enough information to train myself on this ORM.