Virtual Machines - X# and related VS & SQL stuff ...

Phil Hepburn · Post by **Phil Hepburn** » Mon May 08, 2017 2:19 pm

Hi again folks,

The next step in me helping new guys to get into SQL with X# will take me a little bit longer than usual. (A day or so.) This is because I will use the notes in the Appendix 'X' section of my "ClickStartLINQ" eNotes to make a small test app which will access the MDF we used previously with Management Studio. So I will be coding from scratch - just to show how it is done, and because my old C# and Vulcan demo apps are in a bit of confusion after 8 years of changes - so a clean start sounds a good idea.

Here is what I will be using to guide me :-

: L2S_firstGoForum_01.jpg (121.2 KiB) Viewed 572 times

And here is where my copy of the MDF exists / resides :-

: L2S_firstGoForum_02.jpg (55.71 KiB) Viewed 572 times

Wish me luck !

You can be trying out new queries for yourselves in Management Studio, or VS 2017.

Regards,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Tue May 09, 2017 9:22 am

Good morning Pearls People (of the Forum variety),

I have made a good start to doing some X# code to allow us to access an existing SQL database.

The link below is to my OneDrive folder so that you can share and download the small app.

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

For those guys who are unfamiliar with SQL use and operation from coded .NET apps, getting a working connection string which finds both an Instance of a SQL server and the data file, is a VERY good first step to making a working app.

If you have followed my posts so far (like Michael) then this small app should work for you.

Good Luck!

Here are a couple of images to show you what you should get :-

: L2S_firstGoForum_13.jpg (158.93 KiB) Viewed 572 times

And now the WPF form but without the data in the 'DataGrid' control - grey and at the bottom of form.

: L2S_firstGoForum_14.jpg (136.1 KiB) Viewed 572 times

I will now add the extra (but still small) amounts of code to allow us to return and show the Customer table data.

Cheers,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Tue May 09, 2017 11:08 am

Okay guys, we've done it !

We now have our foot firmly in the door (we are on our way, big time).

The attached image shows that we can now retrieve the data set for all customers in the Customer table - using only our own .NET code - of the X# syntax variety.

The WPF form has a DataGrid control which is bound to the returned query data set.

There is hardly any code and script to do this as you will see in my next post when I have eaten some food !

Here are the ten customers :-

: L2S_firstGoForum_25.jpg (80.24 KiB) Viewed 572 times

Hope this interests a few guys - I will post a working solution later this afternoon.

And before that I will show and explain the code/script behind this simple one Table sample using 'Linq to SQL' technology.

Regards,
Phil.
Wales, UK.

NickFriend · Post by **NickFriend** » Tue May 09, 2017 11:41 am

Hi Phil,

SqlConnectionStringBuilder has properties for all the different connection string parameters,
so rather than doing oSCSB["Data Source"] := "xxxxx" which is of course susceptible to typos, you should be able to do oSCSB:DataSource := "xxxxx" which is a bit more reliable and should give you IntelliSense help.

HTH

Nick

Phil Hepburn · Post by **Phil Hepburn** » Tue May 09, 2017 12:52 pm

Hi Nick,

Good point ! This is a small problem coming from me using old, but working, code from way back and then the good old copy and paste ;-0)

I was focusing on getting guys into using real live SQL data ASAP, so that is my excuse.

Also for your knowledgeable ears, I do intend to get from L2S (Linq to SQL) to L2E (Linq to Entity) as soon as possible. It was just that L2S makes a nice simple in between stage for guys new to all this stuff.

Thanks for the input.
Cheers,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Tue May 09, 2017 1:27 pm

Nick,

I followed up your tip and got all but one property to translate.

I have attached an image of the new working static method - I can't find any alternative for 'Trusted_Connection' and I had to change the name for the 'ConnectTimeout' from "connection".

My example will run okay without a value being specified for 'Trusted_Connection'.

Any ideas or comments on this ? I have to admit it is not my strong point - most of what I have ever used for connecting was given to me by Willie Moore years ago. And it just worked.

: L2S_firstGoForum_29.jpg (105.51 KiB) Viewed 572 times

Speak soon,
Phil.
Wales, UK.

Phil Hepburn · Post by **Phil Hepburn** » Tue May 09, 2017 1:40 pm

Here is the simplest of working X# solutions, accessing the SQL database to retrieve Customers.

Note that although the MDF has more than one Table, we currently are just accessing one, that of Customer.

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

I will now make changes to add classes for the other existing Tables in the MDF, and update the look / feel of the main WPF form to suit.

Hopefully by a click of a button we will be able to display different data sets returned from the SQL engine for different queries we make and send.

Attached below is the code for the Customer class :-

: L2S_firstGoForum_22.jpg (145.44 KiB) Viewed 572 times

And now shown below is the calling code which uses pure LINQ code, not T-SQL script :-

: L2S_firstGoForum_21.jpg (135.38 KiB) Viewed 572 times

Under the hood, L2S technology sends a T-SQL query for us - BUT - we don't need to know what is being done for us.

However, any knowledge of SQL and select statements is more than helpful to any modern .NET coder, so don't run away from learning ;-0)

Fingers crossed,
Phil.
Wales, UK.

NickFriend · Post by **NickFriend** » Tue May 09, 2017 2:26 pm

Hi Phil,

I think that IntegratedSecurity is the same as Trusted_Connection ie. having set IntegratedSecurity the Trusted_Connection property is no longer required... but not 100% sure about that.

Nick

beerhand · Post by **beerhand** » Tue May 09, 2017 8:40 pm

Hi Phil,

this was interesting. Finally, I was able to run the app. The concept is more or less clear but a lot of questions are popping up.

1. There is nothing like a "dbServer object" that takes care of all these SQL connection strings?
2. There is no "automated" class generation for all the SQL table fields? Means, hand-written code is the solution. In case of multiple large tables, we need to do this by ourselves?

In other words: a visual "SQL stuff builder", where I select the SQL Server, Table, fields and the "builder" creates the rest does not exist

3. Syntax of X#: there are quite a few unknown commands to me. e.g what does "local implied" mean. Also, it looks like I can define variables somewhere in the code and not necessarily at the beginning. How can I look up the definition?

4. WPF is also absolutely new to me. Based on my research WPF is the way to go. Do I need to learn (besides X#, SQL) also XAML?

As of today, this seems to be an intersting journey. I think as a next step for me I need to digest the code and try to re-build it by myself. I am pretty sure I need to cheat and look at Phils code. Especially the SQL connection stuff looks complicated...

Have a great night.
Michael

Phil Hepburn · Post by **Phil Hepburn** » Tue May 09, 2017 10:41 pm

Hi Michael,

I am really glad you managed to follow along, and I hope a few others may do so as well ;-0)

As for your questions, well many/most of them will just disappear when we take the next step to EF6 (entity framework) where we have similar code BUT deal in 'Entities', which is really .NET complex classes. We give up thinking in tables, and all the relational mapping is easily sorted out for us, once we define our entity classes. So all is done in .NET code, like what we are used to.

The only reason I used 'L2S' was because it proves a useful stepping stone to get to EF6, and it allows us to see how EF simplifies things on the SQL side of things.

I understand you need time to try out, and digest, the stuff we have seen so far. I did the same with Nick Friend's MVVM sample - three weeks I sat there I seem to recall ;-0) But I got there in the end.

As for WPF, we will use it in a 'simple and straight forward' way - still very powerful however.

When we use the EF6 approach we will first define the Entity classes, and then the code will create the database for us - its called 'Code First'. This is the approach I like to use - BUT - as you have seen, with L2S we can easily use existing data tables.

Using EF6 we will also see how to populate our database from entities, which automatically place data in the correct and most suitable Tables.

When we use EF6 we can effectively forget about SQL, all we MUST do is use LINQ to deal will all our collections of object items.

No doubt I will have some other useful points in the morning.

And remember, we have done all this with a "Virtual Machine", and with software which is FREE from Microsoft and also the X# Team - all it cost you was a few Euros, for a Windows upgrade and some time and effort to work with me

Hope you sleep well after the mental efforts and brain activity.

I have another (extended) sample to post, it will help you go further - more simple sample code to copy and adapt etc..

Here is an improved but not finished sample output :-

: L2S_firstGoForum_31.jpg (74.39 KiB) Viewed 572 times

Cheers,
Phil.
Wales, UK.

Main menu