Entity Framework - the easy way to make and access SQL data ....

Phil Hepburn · Post by **Phil Hepburn** » Fri May 12, 2017 3:25 pm

Hi Michael and others,

I have started this thread to continue the basic thread about Virtual Machines, and running VS 2017, Management Studio, and X# with SQL Server support.

I am still using the above software platform on my office machine and now have the basics of a working demo sample based on Entity Framework technology (version 6) from Microsoft.

All I have done is write some .NET code in X# syntax, and the EF support has done the rest - made an empty database when I first run the app.

Check the images to see the PRGs for the entity classes as well as the Tables created. Remember with complex entities there is no longer a simple one to one mapping of class to Table. But then we don't have to worry ourselves about that.

Soon I hope to have a basic running app which you can run to create a database, and also seed some initial data for a couple of House Holds. Remember 'House Hold' are based on Property (Real Estate), Persons, and an Address object. A person can have a collection of vehicles.

Check these out for a start :-

: HouseHold_db_created_02.jpg (64.99 KiB) Viewed 641 times

: HouseHold_db_created_01.jpg (86.33 KiB) Viewed 641 times

It was quite easy to code this, you just need a simple to follow working app. I used the one I did on 'Stock' for the German conference in Cologne.

Hope this interests a few.

Oh! and did you see Johan's post - we should be able to do the same with a Npgsql database as it has a suitable LINQ driver available for it.

Cheers,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Sun May 14, 2017 10:59 am

Good 'Sunday' morning guys ;-0)

Well, we have done it - from some basic .NET classes in our X# syntax, we have gotten our simple demo app using Entity Framework 6 to save the data to SQL tables, without our code having to do much at all - simple, even 'easy'.

If I create a 'HouseHold' object and manually (hard code) the property values, then the following code in line 64 causes all the value data to be placed into the correct tables, columns etc. - yes, five different tables. Lets check the 'save' code first :-

: SaveChanges_01jpg.jpg (78.67 KiB) Viewed 641 times

We need to know that the 'HouseHolds' property in our Database Context class is as follows :-

: SaveChanges_02jpg.jpg (62.23 KiB) Viewed 641 times

And now the image of lots of simple details - which is what data is really all about :-

: SaveChanges_03.jpg (191.92 KiB) Viewed 641 times

I have hidden the second Person details as they are so similar to the first person ones.

Remember that a 'House Hold' in English (in the UK) is a property (Home) as well as the people (Persons) who live there. Each person also has a possible list of vehicles they own and keep at the property address.

You may need to think about this, and what a step has been made from using ADO.NET back in the old days

Finally, we see a very easy way of displaying some of the list data - it is being bound to a 'DataGrid' control in WPF :-

: SaveChanges_04.jpg (43.06 KiB) Viewed 641 times

I will now go and tidy up the app, add some more data as extra HouseHolds, and then post it for you to try out - adapt and play with etc..

Best regards,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Sun May 14, 2017 11:10 am

Oh! - you probably need proof from the Management Studio IDE so here goes :-

: MS_proof_01.jpg (37.32 KiB) Viewed 641 times

After examining the HouseHolds table we need to check out the others too, here is Persons :-

: MS_proof_02.jpg (44.11 KiB) Viewed 641 times

And now the simpler Addresses table structure :-

: MS_proof_03.jpg (44.52 KiB) Viewed 641 times

And now a simplified 'Property' data table, 'Property' being a house, flat, bungalow or other ... real estate :- [I intend to upgrade the property set of this class to make it more interesting.]

: MS_proof_04.jpg (35.36 KiB) Viewed 641 times

And finally the vehicles data -maybe we should record also the energy source - like diesel, petrol, electricity, hybrid and manual etc.

: SaveChanges_05.jpg (39.61 KiB) Viewed 641 times

All of the above was done after a short line of .NET code was executed. It does feel good to be free from ADO.NET - well free as meaning we do not need to code in ADO.NET classes and the likes.

Hope this interests a few ......

Cheers,
Phil.

Phil Hepburn · Post by **Phil Hepburn** » Mon May 15, 2017 4:05 pm

Okay then guys,

Here is a link for you to access my OneDrive folder with a working EF6 solution.

It is not finished, very much a "work in progress" project, but it does create and populate a SQL database using Entities, as well as retrieving the 'HouseHold' entity collection and displaying that in a DataGrid control. Check this out to see if you get it to work :-

https://1drv.ms/f/s!AiCBl-gBWjY9g_pnfKyLPi_8Lo9Ycw

For guys like Michael, running VS 2017 in a Virtual Machine and with SQL Express etc., the solution should work and make a SQL database - it does in my VM, which is exactly as it was when I documented earlier posts etc..

Here is what you get displayed if it all works :-

: ReadBackOK_04.jpg (57.87 KiB) Viewed 641 times

Okay, I agree, it does not look much as yet, as the data values are still encapsulated in objects and lists etc. But the detail is there to be discovered in my later posts.

Here is the code which does the 'action' :-

: ReadBackOK_05.jpg (102.77 KiB) Viewed 641 times

Todays new bits are shown in the following image - line 31 accesses the database using LINQ technology - first the image to show this in X# syntax :-

: ReadBackOK_01.jpg (84.32 KiB) Viewed 641 times

The next couple of images may be helpful to you to see what else is needed to make this all work :-

: ReadBackOK_02.jpg (53.17 KiB) Viewed 641 times

And finally, the boring but important part of data entry - even though it is hard coded :-

: ReadBackOK_03.jpg (154.25 KiB) Viewed 641 times

Please let me know if the link to my OneDrive is okay and the solution downloads and runs in VS.

Good Luck,
Phil.
Wales, UK.

FFF · Post by **FFF** » Mon May 15, 2017 6:08 pm

Phil,
link works, sln does not

It's not clear, what from the OD content has to be fetched, and where to be put - got me the sln and the complete demo-folder as zip, put both into one place, double clicked the sln file, "project file could not be loaded, part of path to xs.proj couldn't be read.
There IS a proj file next to the sln file, so i seem to have misdone some folder struc?

Phil Hepburn · Post by **Phil Hepburn** » Tue May 16, 2017 10:06 am

Good morning Karl, Michael and others,

Firstly the issue of reference assemblies, and their related 'using' statements at the head of a code file, listing required namespaces.

I will include what is in my working solution to guide you. It looks a bit messy in that we don't as yet in X# have the useful feature (as in C# integration) where the Visual Studio system tells us which we have defined but not used. This feature is a useful TIDYING tool.

Yes, it is better to have one or two too many, than one short. The first means your app compiles and the second means it does not ;-0((

Lets first start with the references section and the listed assemblies, check this out :-

: AssembliesEtc_01.jpg (91.5 KiB) Viewed 641 times

And now the head of a code file - check this out :-

: AssembliesEtc_02.jpg (185.28 KiB) Viewed 641 times

For your interest I got around to commenting the end of some USING statements so that I knew what I was adding them for.

In my eNotes (ClickStart) and the Appendix 'X' in my recent notes for X#, I make it very clear at each folder, sub-folder stage, as to which assemblies are requited and which Namespaces are used in the code heading section. Keep a look out for this stuff - its not sexy but it is necessary ;-0)

Another issue which usually crops up in larger projects/solutions is that of defining our own Namespaces, and then 'using' them.

Here is what I could find in my previous apps, but it is in the C# part of my Vulcan solution - there are three small images, one where we define our own Namespace, one where we USE it, and one where we need to include the assembly in the references - check these :-

: own_NS_01.jpg (27.79 KiB) Viewed 641 times

: own_NS_02.jpg (20.21 KiB) Viewed 641 times

: own_NS_03.jpg (20.2 KiB) Viewed 641 times

Actually, once you get into the 'swing' of these things they become quite straightforward (almost easy), just give it a go.

Also, when we are not creating LARGE apps the performance 'hit' of too many assemblies and/or too many Namespaces to search, is not really an issue. I leave that to the 'tidying' process which I may do manually at the end of application development, in the optimising phase.

While we are doing stuff to create and learn EF6 apps, we just need to get stuff to work.

Now then KARL :- if you tried to run my last link to my OneDrive (solution) then you do realise it needs an installed SQL server - Express or more. My EF6 samples are all about me using MSSS (Microsoft's SQL Server - either Express or Full). The .NET code statements/comments made by me regarding no need for T-SQL script etc., does NOT mean we do not need an install SQL Server instance. Just that what we write is all in .NET code and the comiler and runtime does the rest for us.

So is you current issue that you downloaded and installed incorrectly, or that you forgot that you needed a SQL Server ?

I did try to make it clear to Michael and all, that my posted code and images were building on what I had developed previously in the Virtual machine platform I developed and posted about.

Good Luck to all, and keep me informed please - problems or no !!!
Cheers,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Tue May 16, 2017 10:33 am

Hi Karl,

I have just tested out the link I posted yesterday in my OneDrive folder to share with the Forum.

I downloaded a zip file of all the items available for download (in my posted solution) and then copied this into my Virtual machine platform where I unzipped it and ran it in VS 2017 - the app then compiled and ran - so making a new SQL database and displayed the rather boring three row data grid.

I guess you are missing the SQL Server instance.

Out of interest, why do you not wish to have a SQL Server instance on your system, or hard drive ? I have had a few servers all at once on all my PC hardware platforms for years, and not seen an issues or problems I would wish to avoid. Especially since we can now use Hyper-V virtual machines, which if not connected will not run any of the resources consuming apps within them.

I did a test yesterday where I ran on the main machine Malwarebytes scan as well as Windows Defender quick scan, at the same time as a VM running VS 2017 and my app running EF6 using SQL Server.

The vast majority of the performance / resources were consumed by the file checker apps.

Speak soon,
Regards,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Tue May 16, 2017 12:56 pm

Hi People of the Pearls,

I have just done a little testing and research back here in Newport, Wales.

The feature in the C# integration which shows us unused 'USING' statements can be seen in the next couple of images, check them out :-

: own_NS_12.jpg (95.88 KiB) Viewed 641 times

In our C# projects any unused USING statement is shown in light grey. and also a quick 'feature option' will also remove these after showing the red screen display.

: own_NS_11.jpg (130.17 KiB) Viewed 641 times

So currently while using the X# VS integration I try to do something similar, but in a slower manual way. If we comment out one or two lines of USING statements at a time and then try to compile, we soon get something which looks like the following :-

: own_NS_14.jpg (104.16 KiB) Viewed 641 times

The above is for the Main window of my test WPF app. The fact that the message box is shown means that no USINGs are missing. So in the end for this code file we actually need only three lines - BUT - this is a static situation, and we only need to add another line of code to change the requirements for other USING 'namespaces'. So I suggest leaving them well alone till the tidy up at the end of the app development.

Here is the more extreme case for the 'HouseHold' business class PRG file I created a couple of days ago :-

: own_NS_13.jpg (201.21 KiB) Viewed 641 times

Out of all of those lines previously included, we actually only need four ;-0)

But as I say, until we have the nice feature of the C# sample, more lines of Namespaces are better than less. Less means no compilation, too many mean loss of performance but at a level we can hardly detect. The loss of performance comes for there being many more items of namespace to check through, many unnecessary - but then we have powerful machines

Anyone got any ideas on this aspect of X# coding ?

Speak soon,
Regards,
Phil.
Wales, UK.

FFF · Post by **FFF** » Tue May 16, 2017 2:47 pm

Phil,

: Phil_OD.PNG (49.84 KiB) Viewed 641 times

that is, what i see, when i follow your link.
So, what is it i have to download, please?

Phil Hepburn · Post by **Phil Hepburn** » Tue May 16, 2017 3:03 pm

Hi Karl,

That looks OK - it is the whole of the project folder in my VS2017 'Projects' main folder.

So you can make a folder in your Projects called 'HouseHolder_EF6_XS_demo' and the expand the zip file into that new folder.

Launching the solution and VS 2017 is done by double clicking the '.sln' file. Or launch and empty VS instance and navigating to the solution file under the 'Open' menu option.

This is exactly what I did for my test done earlier today.

These images may help you see what I have got here etc. :-

: Karl_01.jpg (115.99 KiB) Viewed 641 times

: Karl_02.jpg (43.33 KiB) Viewed 641 times

: Karl_03.jpg (34.48 KiB) Viewed 641 times

Good Luck,
Phil.
Wales, UK.

Main menu