This article meant for aspirants need a start towards Entity Framework. Also included some value added things like EntitySQL. As this one meant for starters, I created evry thing from scratch including the DB tables. So let's go with.
Before everything create a new SQLServer database named "INVENTORY". Create 2 tables inside it as below. You need to keep the relation mentioned. Now add 4 products under valid categories. You also need to keep at least 2 categories in category table.
Now you need to create a new web application. Right click on project and select add->New Item. From there you need to select "ADO.NET Entity Data Model". From here you need to configure the Entity Model like below screen shots.
Click the Finish button will load your 1st Entity Model. You need to make below changes by loading INVENTORYMODEL.edmx like below.
From property window of Product and Category entities make sure the below values are matching.
Once this finished, build the application. Now we have an entity model for 2 DB objects [Tables]. But you should able to communicate to real DB through your entity model. For that we will use a new query language named, "Entity SQL". More over you need to connect to DB using a special class named "Entity Connection". For all these you need to add below name spaces.
using System.Configuration;
using System.Data;
using System.Data.EntityClient;
My first priority is to create a connection by code
string connectionString = ConfigurationManager.ConnectionStrings["INVENTORYENTITIES"].ConnectionString;
using (EntityConnection
conn = new EntityConnection(connectionString))
{
conn.Open();
I am using an EntityReader for storing retrieved data. More over you can see EntityCommand too. This will work as SQLCommand object, for our Entity Model. The bolded part represents EntitySQL. Query taking all rows from our PRODUCTS Entiy Model as a single set i.e equivalent to "*" in SQL. But for EntitySQL you need to use a key word "VALUE" alonng with any single set value representation. The VALUE keyword that we're using here is instructing the runtime not to wrap the returned data in a row.
EntityCommand cmd = conn.CreateCommand();
string commandText = "SELECT VALUE PROD FROM INVENTORYENTITIES.PRODUCTS AS PROD";
cmd.CommandText = commandText;
EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
GridView1.DataSource = reader;
GridView1.DataBind();
The retrieved data is showing in a GridViewControl like any other DataSource. You can have parametric queries too like below.
commandText = "SELECT
PROD.PRODUCTID, PROD.PRODUCTNAME FROM INVENTORYENTITIES.PRODUCTS AS PROD WHERE
PROD.PRODUCTID=@ID";
cmd.Parameters.AddWithValue("ID",
4);
EntityClient is the fastest option available when interacting with an EDM, so it becomes the solution when additional speed is neccesary. Below are the 3 possible draw backs.
- It's read-only
- You can only retrieve back string-based data via the EntityDataReader.
- Your queries must be written using Entity SQL. No pure SQL possible.
NB: You should not run the attached code directly, as it may not work in your machine based on your configuration. The code attached is for reference purpose. You want to start everything from scratch and if anything not working compare with attached code. That's the correct way of learning.