fbpx
Welcome, Guest
Username: Password: Remember me
Share your code snippets, screen shots etc. here
  • Page:
  • 1

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

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1856

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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 :-





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.
Attachments:

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1859

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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 :-



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



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



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 :-



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.
Attachments:

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1860

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
Oh! - you probably need proof from the Management Studio IDE so here goes :-



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



And now the simpler Addresses table structure :-



And now a simplified 'Property' data table, 'Property' being a house, flat, bungalow or other ... real estate :-



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



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.

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1865

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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 :-

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 :-



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' :-



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 :-



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



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



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.

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1868

  FFF's Avatar FFF Away Posts: 574
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?

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1872

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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 :-



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



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 :-







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.

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1873

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1880

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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 :-



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.



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 :-



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 :-



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.
Attachments:

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1883

  FFF's Avatar FFF Away Posts: 574
Phil,


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

Please Log in or Create an account to join the conversation.

Last edit: by FFF.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1884

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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. :-







Good Luck,
Phil.
Wales, UK.
Attachments:

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1886

  FFF's Avatar FFF Away Posts: 574
Ok, didn't understand that the whole content of your link-site should be fetched ;)
Got it as zip, unzipped, doubleclicked sln, vs17 starts, and is content...
Compiles, but naturally does not run, as still no Express or similiar here...
Already way to much stuff on this machine.

Please Log in or Create an account to join the conversation.

Entity Framework - the easy way to make and access SQL data .... 2 years 1 month ago #1887

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
Karl,

Invest in a bigger SSD device. I believe you already have one 1TB device, so why not two, or three ?

I have a second 1 TB as drive D: which I save mainly for Virtual Machines and virtual drives etc.

You see it as too much stuff on one machine, I see it as too little disk space on a very nice PC you have ;-)

Anyway, well done on getting that far. SQL Server Express does not take that much room on disk.

Cheers,
Phil.

Please Log in or Create an account to join the conversation.

  • Page:
  • 1