Introduction

 Top Next

Welcome to the world of SQL - Structured Query Language.

Welcome also to the world of ADO in .NET. With ADO.NET we can open up the whole horizon

of accessing SQL data from a back-end data server.

Personally, I migrated from the world of DBFs a few years back, and have loved every minute of working

with SQL script, and the Microsoft SQL Server.

I recommend moving to SQL - all those guys who were in Visual Objects with DBFs, and even those later in

Vulcan.NET but still using DBFs.

I have to admit it, there are a few things to get right about SQL, and a few new things to get your head around,

but it is certainly worth it - AND - it does fell a lot more modern that good old simple DBF technology.

However, this eVolume of eNotes is NOT about Transact SQL (T-SQL or TSQL) or about designing and creating

databases, and tables with relations, it is about sending out query scripts from our Vulcan.NET code.

As well as sending scripts in query statements to the server, we need to write Vulcan.NET code to accept returned

data from the server - either as data sets or as a single data item.

Of course some query statements do not generate returned data, and these are shown in the following chapters as

well. As also are those statements which work with, or query the database as an object in its own right, rather than

our data within its tables.

As well as statements (sometimes called scripts) which generate data, we will also see how to create and maintain

Functions and Procedures in the database itself - all by using scripts sent via ADO methods.

You need to be warned however, once you get into SQL you will like it, and like it a lot. Once bitten you will never

return to DBFs and the world of yesterday.

No Excuse :

When I started into the world of SQL some three and a bit years ago, I had a chance to 'bottle out' if things got too bad

for me. It could use the excuse that it was all too difficult to find out about - it was in many ways elusive. Guys would

talk about SQL but few if any could (and would) show you how to get up and running, and even then the next steps

were also very vague.

You have no such excuse !!

Now that all the 'ClickStart' eNotes are available, as well as a couple of example VS solutions, you should have nothing

hidden from you. There should be no stumbling blocks, other than the learning process itself.

Even the free downloads from MS are much easier to do and follow - and I have published a 'blow by blow' install of

MS SQL Server 2008 R2 in my T-SQL 'ClickStart' eNotes.

If I get time I will try and copy the contents of the relevant section to one within these notes - just so you have even less

reason to give up without too much of a fight ;-0))

To get us in the right mood here is some words of wisdom from a web link :-

[ http://www.interview-guide.com/microsoft/what-are-the-advantages-and-drawbacks-of-using-ado-net/ ]

ADO.NET is rich with plenty of features that are bound to impress even the most sceptical of programmers.

If this weren’t the case, Microsoft wouldn’t even be able to get anyone to use the Beta.

What we’ve done here is come up with a short list of some of the more outstanding benefits to using the ADO.NET

architecture and the System.Data namespace.

* Performance – there is no doubt that ADO.NET is extremely fast.

The actual figures vary depending on who performed the test and which benchmark was being used, but ADO.NET

performs much, much faster at the same tasks than its predecessor, ADO.

Some of the reasons why ADO.NET is faster than ADO are discussed in the ADO versus ADO.NET section later in this chapter.

* Optimized SQL Provider – in addition to performing well under general circumstances, ADO.NET includes a SQL Server

Data Provider that is highly optimized for interaction with SQL Server.

It uses SQL Server’s own TDS (Tabular Data Stream) format for exchanging information.

Without question, your SQL Server 7 and above data access operations will run blazingly fast utilizing this optimized Data Provider.

* Disconnected Operation Model – the core ADO.NET class, the DataSet, operates in an entirely disconnected fashion.

This may be new to some programmers, but it is a remarkably efficient and scalable architecture. Because the disconnected

model allows for the DataSet class to be unaware of the origin of its data, an unlimited number of supported data sources can

be plugged into code without any hassle in the future.

* Rich Object Model – the entire ADO.NET architecture is built on a hierarchy of class inheritance and interface implementation.

Once you start looking for things you need within this namespace, you’ll find that the logical inheritance of features and base class

support makes the entire system extremely easy to use, and very customizable to suit your own needs.

It is just another example of how everything in the .NET framework is pushing toward a trend of strong application design and

strong OOP implementations.

HI,

In my point of view, In a connected scenario every changes that you make in your data would then be reflected in your database, it means that every update and every deletions or analysis that you are making, you are contacting your server. This may cause heavy traffics in your network.

In a disconnected environment, you will have the chance to verify your data and change it before sending your updates in the database. In this case, other users would not be then affected by anything you are doing to your data. Coz your data would be stored locally (memory). and also, with a disconnected architecture, you can analyze and manage your data after fetching your records from the server and not bother the server until your are sending your updates to it. Thus lessening the network traffic.

cheers,

Paul June A. Domag

There are multiple factors that can affect the decision between connected vs. disconnected in addition to network traffic and server resources that Paul pointed out. Different applications can require different choices for optimal performance. For example:

Standard forms-based applications that display information, allow a human operator to make changes, then send those changes back to the server are often a prime choice for the Disconnected model.

On the other hand, the disconnected model is built on top of the connected model, so if you know everything that needs to happen over a particular connection and can send it in one execution, the connected model will generally be faster. Sending a series of already-approved transactions to the server could be an example here.

Your transaction requirements can also affect the choice -- if obtaining the data from the server and subsequent update must be done in the same transaction, your app must remain connected to the server (although this can be done with the disconnected model, using System.Transactions for instance, some of the network and server resource benefits are lost, so may affect your decision).

In the end, it comes down to an engineering decision for each application, which requires learning and understanding the tools and their characteristics. I will point out that for a lot of the traditional data access areas, the disconnected model works very well and scales better than a purely connected model.

Connection pooling in .NET is quite effective
and is the preferred method in web applications
versus keeping connections open through the
page lifecycle and prevent other threads
from grabbing that connection out of the pool.

--
Robbe Morris - 2004-2006 Microsoft MVP C#

Updating Using the Disconnected Data Model

Updating the database using the disconnected data model can be thought of as the reverse of the process for querying the database using the disconnected model. Instead of performing the SQL statement and then creating the DataSet, the DataSet is examined to create the SQL statements, which are then executed. The IDataAdapter.Update() method, which accepts a DataSet as a parameter, performs the updating of the database. During the execution of the Update() method, the DataAdapter performs the following actions:

1. Opens a connection to the database.
2. Examines each row in the DataSet, row-by-row.
3. If the row has been modified since the previous interaction with the database, an INSERT, UPDATE, or DELETE SQL statement is executed accordingly. The state of the row (unmodified, inserted, modified or deleted) is maintained as a property in the DataRow object, which is the building block of a DataTable, in turn the building block of a DataSet.
4. Closes the connection.

The actual INSERT, UPDATE, and DELETE SQL commands performed are maintained as properties of the DataAdapter object. You must explicitly set the SQL commands for the INSERT, UPDATE, and DELETE operations before calling the Update() method. Otherwise, an exception will be thrown and the database will not be modified if the appropriate command does not exist. For example, if a row has been deleted from a DataSet, and the DeleteCommand property has not been set, an exception will be thrown when calling the Update() method and the deleted row will remain in the database. Each of the data providers included in the .NET framework include a CommandBuilder object which will create the necessary SQL statements for you, provided that the contents of the DataTable objects in the DataSet each represent a single table in the database (i.e. the DataTable is not the result of a join operation). However, it is not a requirement for a data provider to have a CommandBuilder object, but because of its usefulness and the fact that all data providers included in the .NET Framework have CommandBuilder classes, most third-party data providers will also have a similar utility. Listing 1.9 uses the SQL Server data provider and the classes we just discussed to change the information in the database by altering a DataSet.

PHH - 7th. January 2011.


Getting things straight !

 Top Previous Next

Let us get one thing straight, right from the very beginning, these notes are about ADO.NET and not ADO.

ADO was an earlier product or technology - here is something useful from a web link :-

* Learning Curve – despite the misleading name, ADO.NET is not simply a new version of ADO,

nor should it even be considered a direct successor.

ADO.NET should be thought of more as the data access class library for use with the .NET framework.

The difficulty in learning to use ADO.NET to its fullest is that a lot of it does seem familiar. It is this that

causes some common pitfalls.

Programmers need to learn that even though some syntax may appear the same, there is actually a considerable

amount of difference in the internal workings of many classes.

For example (this will be discussed in far more detail later), an ADO.NET DataSet is nothing at all like a

disconnected ADO RecordSet.

However, if like me you are new to all this, then we can forget ADO - except to know of its existence - and

get on with the task of getting to grips with ADO.NET.

'ClickStartADO' means learning ADO.NET - a longer and more suitable title would just have been 'clunky' !

PHH - 8th. January 2011.


Where are we headed ?

In many ways this is quite simple. Any skills you have developed using Management Studio (MS) to

query a database, will be transferable to the world of dotNet.

When developing my SQL applications (almost from day one), I was looking for a way to allow me to

send query strings - or scripts - from my own Vulcan code as easily as I could from MS.

With the more recent addition of the STATIC class called 'SQL_my_funcs' to handle the first part of the

data access layer - that just above the SQL Server itself - I have come as close as I think I can get to

the level of simplification I was after.

In these eNotes you will see and learn how to write Vulcan code which will easily - and flexibly - allow you to

query the back-end data source with no more difficulty than when using Management Studio.

Basically, we will explore some of the corners of the ADO.NET namespace in the following chapters of these

eNotes. But there is much more in the namespace than we will have time, or the inclination, to explore.

One class of the ADO.NET namespace that we will look at in detail is the DataTable class, as this supports

much of what we need to do in SQL tasks as well as GUI display and interaction.

PHH - 7th. January 2011.


SQL on the Internet

You may well be aware that SQL has been available in one shape of form (flavour?) for many years now.

The older Unix based ISP offerings for web sites were based on PHP and 'MySQL'. I used this myself in

my first proper attempt at creating a web site, and it worked well, and I liked the support web app for managing

the SQL database.

More recently ISps have been offering a Windows based provision, my second of which is used to provide the

'WinClickStartVulcan2.net' site and facilities.

This is truly Windows based, and as well as running all the expected software applications runs Microsoft SQL

Server 2008 R2 - this is the most recent public release to date. What will happen when "Denali" is released to

the public (SQL Server 2012) I don't know.

My personal goal for 2012 is to write some web services in VN2 which access the web SQL server available to

me on my new site. I think I can have 5 separate DBs each with a limit of 200 Mbytes. Quite sufficient to get

my teeth into ;-0))

One aspect of SQL on the web is changing to using the 'Disconnected Model' for data access.

I know from personal experience that such ideas can bring out strange reactions from friends and colleagues. We

will look in more detail about this 'new' model in the following subsection. If you do not already know of them, be

prepared to have some new ideas to think upon, and get head head around, before you start coding for it !

Another way of looking at this :

There is another way of looking at web based SQL, and that is to realise that the server has already been installed.

Yep! that is right, you do not need to do anything about installation - it has been done thoroughly and correctly by

'them that know how', all we need do is use it.

PHH - 8th. January 2011.


the 'Disconnected Model'

PHH - 8th. January 2011.


Suitable learning material

The Future and 'ClickStart' :

It is hoped that after Germany - VODC Stuttgart 2011 - I will make some big changes to the newest site 'WinClickStartVulcan2.net'.

This is a Windows based site with MS SQL Server 2008 R2 available for use by my web apps.

At the moment it only has the later eVolumes of the eNotes, but I intend to place all notes and VS solutions on this site.

'ClickStartTSQL' has some useful information for you as well :-

==========================================================================================================================================================================================

Very recent publication - NEW !?

It is very useful to have quality reference books and this is one of those.

However, having searched through a lot of existing modern books I suggest that you come to terms with the

idea that you will find it very difficult, or almost impossible, to get reading material which gives an overall viewpoint.

Because it was difficult to source material to compare and contrast a lot of the WPF and XAML technology, I started

to write my own notes. So for specific details the reference books are often brilliant, but for overall stuff, as well as

Vulcan coding syntax, my ClickStart eNotes will come in very useful to you.

I did use, and still do at times, the 'orange Bible' below :-

This new book is very nicely printed, glossy and in full colour so it looks like the VS10 screen output.

This is a must to supporting anyone's drive to learn WPF and XAML.

Thoroughly recommended.

Author is Adam Nathan, published June 2010.

ISBN-13 [ 978-0-672-33119-0 ]

Alwyn Jennings-Bramly : the Book !

This is a 'MUST' for anyone coding Vulcan.NET and I refer to it on a regular basis, even after all

these years - yes, Alwyn started the work years ago now.

Also there is about three times the amount of material in the book than you get in regular paper copies from

the likes of Amazon.

I can't recommend it highly enough - you have to have a copy !!

Purchase a copy over the internet from :-

My copy looks like this when the PDF file is opened - each copy is customised - see my detail below :-

I have an icon on my desktop along with icons for my regular reference CHM material.

Checkout my 'fence' for reference material :-

Sams Teach Yourself WPF - in 24 hours :

Mark Long - Training DVDs :

PHH - 1st. November 2011


Accessing 'ClickStart' eVolumes

I have several web sites and addresses you may try for locating useful eNote information.

The following are listed in order or most recent first :-

www.WinClickStartVulcan2.net

www.ClickStartVulcan2.net

www.ClickStartVulcan.net

However there is another general site which is hoped to be a WordPress site and which needs much developing

and this is found at the following URL :-

www.ClickStartLearning.net

eVolumes to consult :

'ClickStartTSQL' T-SQL - learn some more in-depth SQL scripting and enhance your Queries !

ClickStartSQL' Starting SQL Server use with ADO.Net :

'ClickStartXAML ' Starting XAML scripting for easier WPF input

'ClickStartWPF 'Starting WPF application development, painlessly ! :

PHH - 8th. January 2012.


the 'Small Trader' application

In the image above you see the opening screen of the 'Small Trader' application.

The project to create a real and working application, for use in a shop or small company, was born in about January 2010.

The initial app was targeted at version 3.5 of the .NET framework, but in very recent times (July 2011) this has been altered

to be version 4.0 - some comments on changing framework mid-way through a project are to be found in Section 'D' of these eNotes.

Aims, Objectives, and Purposes :

In creating the project it was hoped that I would discover how to use data binding in WPF forms, and learn both the dotNet code

required as well as finding out how to use XAML script, and learning how to set the necessary binding there, within the script.

Because some screens were designed and created well before others (over 15 months apart), we have both ways existing in

the app, and as such prove useful to new guys coming to learn about WPF and data binding.

Another purpose for writing this Small Trader app was so that I could explore the ways in which the ADO.NET code could be

improved from my first attempts (in the publicised 'BEST company' app) - particularly in respect to simplifying specific methods for

inserting and updating table records.

Although the ADO.NET approach to backend data source access is very easy, and just works, it also can be very long winded,

or verbose, if an attempt is not made to try and simplify the process the developer needs to apply when writing new methods for

handling business and custom objects.

More to see and read :

To see more about the original Small Trader app look to Appendix D of these eNotes.

However, it is hoped to have a completely new version of the same application which is fully based on the newest technologies

of SQL and layering with STATIC class use, as well as the latest WPF and data binding, using suitable collections as well as

Dependency Objects for 'Customer', 'Invoice', 'JobSheet' and more.

I even hope to have a version of Small Trader which uses DLLs for the Static class of 'Layer 6' at least.

PHH - 9th. January 2011.


Chapter 0 - the Namespace

At first, coming to ADO.NET may seem a little confusing, as probably the first thing many of us might try

is to find the 'Namespace' for it.

Having failed in my search of the SDK documentation I use VERY regularly, I turned to good old Google.

Below is what I got, and I assume you will get something similar :-

======================================================================================================================================

So I tried the link shown in the very last entry in the image above and got the following :-

==========================================================================================================================================================

So having satisfied myself that I was not missing out on something I ought to know, I am using the 'System.Data' namespace

from now on in - see below for a start :-

*** REMEMBER *** It is not ADO you are going to explore using, but ADO.NET, and they are quite different apparently.

[ .. ADO is dead, long live ADO.NET !! .. ]

Why this Section ? :

I included this subsection mainly to clear up any confusion about the required namespace for 'standard' ADO.net, but

I realise that if we are to use the 'DbFactory' approach then we are going to use another namespace entirely, as you

already have seen.

But for our information, and our education, here is what the top of the SDK documentation looks like for 'System.Data' :-

==========================================================================================================================================

Well you guys, reading the above image information, I hear a lot of what seems to be quite familiar stuff, no surprises, BUT,

I did not see 'DataReader' mentioned even once.

It would seem that although this is still supported, for the reason of moving towards web based processing (and the future?)

the disconnected model is being focused upon, and that means 'DataSets' returned and the connection dropped. Not getting

a DataReader which requires an open connection while application processing handles the data pulled down from the backend

data source via the reader.

I think and hope I am interpreting things correctly.

Anyway, you now know where to look for information, but remember to checkout the 'DbFactory' stuff too, as we will be using

this throughout the Small Trader application.

See you in the next subsection ...............

PHH - September 5th. 2011.

PHH - 7th. January 2011.


Chapter 1 - What is ADO.NET

A slice of History :

Before we get to discussing today, and more importantly, the future, let us look at ADO from a historical

point of view.

ADO stands for "ActiveX Data Objects" and is :-

A programming interface from Microsoft that is designed as "the" Microsoft standard for data access.

First used with Internet Information Server (IIS), ADO is a set of COM objects that provides an interface to OLE DB.

The three primary objects are Connection, Command and Recordset.

The Connection object establishes a connection with a particular database management system (DBMS), or other data source.

It can also send a query to the database.

The Command object is an alternate way of sending a query to the database, and the Recordset object contains the resulting

query answer, which is a group of records. [ See OLE DB, RDO, DAO and ODBC. ]

ADO.NET :

ADO.NET is the .NET version of ADO, which is substantially different from ADO.

It supports XML documents and relies on .NET Data Providers as an interface layer between the application and the databases.

============================================================================================

It is the ADO.net version which we will use extensively in our coding throughout these eNotes and the whole Small Trader application.

With this background information in mind, let us see what the modern SDK documentation has to say on the subject :-

=====================================================================================================================================

Okay then, 'ADO' is dead as far as we are concerned, and "ADO.net" is the thing to use !!

Also be aware that we will be using only one type of data source, that from (or via) an instance of the MS SQL server. I currently us

version 2008 R2 (the Developer edition). Soon we will see from MS the 2012 offering from them, currently called 'Denali' or 'the Great One'.

Below is some extra information on a useful and related namespace :-

=====================================================================================================================================

We will use 'heavily' some of these classes shown above - or their DbFactory equivalent.

SQL - Problem / Issue !? :

Like everything else in life and software development, you just think you are 'getting to grips' with something when

up comes another level of complexity :((

Because SQL 'stuff' is not totally standard, across various manufacturers brand or 'flavour' of SQL server, we can't

use the 'Sqlxxxxx' classes above in our code if we wish it to be usable with other manufacturers SQL servers without

large scale changes in code within the app files.

Overcoming the 'Flavour' of Server Problem :

There is a way around the problem / issue of hard coding a particular manufacturers code syntax (and related class types)

into our solutions, and that is to use effectively a 'super' class of provider classes.

First of all have a look at the following image from the 'SQL Connection Details' screen of the Small Trader app :-

===============================================================================

As you can see in the above image we seem to have a choice of provider 'flavour' of server.

The one shown as selected - SqlClient - is the one for Microsoft Server and works for me with both

2008 and 2005 versions of the SQL Server.

You will recognise that this name corresponds to the one shown in the namespace images at the top

of this subsection, confirming the Microsoft 'link'.

The 'super' class we need to use to allow ourselves the freedom to write code which can later be switched

from one of these 'Providers' to another, is called the 'DbProviderFactory' class.

As it comes 'out of the box' in standard dotNet on my machine, the options are exactly as shown above

in the image.

Before we move on into the next subsection to see how to use the 'DbProviderFactory' class we can first

take a look at the following SDK documentation - study carefully :-

=======================================================================================================================================

As you may agree, useful information indeed, and stuff we ought to take note of in our solution design and

code creation.

Even though I have never used anything BUT MS SQL Server, and probably never will either, I have coded my application

solutions using 'DbProviderFactory' after a good professional 'tip' or nudge, from colleague Colin Newell - thanks Colin ;-)

Now on to the next subsection where we will get to grips with using the 'super' class "DbProviderFactory".

See you there ..............

PHH - September 3rd. 2011

PHH - 7th. January 2011.


Ten Points about SQL and ADO.NET

Ten points about using MS SQL Server with Vulcan 2 :

- use ADO.NET and you will get success in your dotNet application coding.

- ADO.NET is a mature technology and works very well indeed.

- prepare and test any SQL query statements in Management Studio.

- 'Copy and Paste' tested queries into you VS dotNet editor - this approach generates success.

- use my "STATIC" class to code ADO.NET 'stuff' - it will be much easier [ .. this is called 'SQL_my_funcs' .. ]

- to create and update your own 'STATIC' class for each application, copy and adapt the one I supply with the 'Small Trader' applet.

[ .. this is called 'SQL_SmallTrader' .. ]

- base each 'group' of static methods around a custom 'Business Object' (BO) class.

- place 'All' of the code to deal with backend data access in your own 'STATIC' class - 'SQL_your_application_name'.

- whenever possible use the disconnected model, and returned DataSets in place of DataReaders, and then close the connection.

- in preparation for the 'Entity Framework' (EF4) approach, map your custom BO's to SQL tables at design time.

[ .. it seems a big difference to DBFs at first, but you will grow to LOVE it - I did, and have never looked back .. ]

PHH - 7th. January 2012.


General Details on ADO.NET

====================================================================================================================================

History - View SDK contents with caution !? :

===================================================================================================================================

This is of NO Help at all in the Modern World of IT :

===================================================================================================================================

PHH - 7th. January 2011.


Parts of ADO.NET useful with SQL

Enter topic text here.


Parts of ADO.NET to be left alone

========================================================================================================================================

=========================================================================================================================================

=====================================================================================================================================

=========================================================================================================================================

BLOBs - wait for "Denali" :

========================================================================================================================================

=========================================================================================================================================

PHH - 9th. January 2012.


Connection screen - interesting details

The OLD and then the NEW ! :

Well then guys, this next part is going to be fun I feel.

First we are going to see the old style VO type coding I used some three years back when I first got a 'Connection' screen

to work for me, with WinForms and in Chris's VIDE.

Then we will attempt to modernize it by using collections and WPF data binding, set as much as we can from

the XAML script, and not done in dotNet code.

Since the older approach is working in Small Trader at the moment, it is a matter of style, and keeping up our

skills so that we can use the latest techniques and even fashions.

Let us start by having a VERY careful look at the older code, notice the For/Next loops :-

================================================================================================================================

As well as checking out the above code, we need to also check that the XAML script has no binging.

Here is what is in existence at the moment :-

===========================================================================================================

Yes, just what I thought, no markup extension script which specifies any Binding, at all.

Since line 332 actually gets a DataTable, we ought to be able to use this to do some sort of binding with

our drop list, or even a short list box control.

Oh! in case you are wondering, the 'FillDLTimeSecurity' method look as follows :-

========================================================

As you can see in the above code, nothing too clever, and you should be able to appreciate what is being done.

Lets move on, and find out how to improve things .

Making a Few Exploratory Experiments :

The way to tackle this is to make a new WPF form and work from a blank 'canvas' as the 'man-in-the-street' would

say. That way we are keeping things as simple as possible.

========================================================

============================================================================================================================================================

======================================================================================

PHH - 7th. January 2011.


Chapter 2 - the DataTable class

Okay you guys, we need to make some clear statements regarding DataTables (DTs), and the world in which

they originated.

The point of making some comparisons of the IT world of yesteryear, and where it is headed today, and

what is planned for tomorrow, is simply so we can (as developers) best decide on the technologies we need to

choose to learn for our future use.

It would appear to me after quite some time and effort with dotNet 2.0 and then dotNet 4.0, that DataTables,

although very useful and easy to work with at times, do belong in the past - yes, they are old hat !

DTs belong to the earlier world of SQL Server, where tables, columns, rows, and relationships were the

order of the day - the thing to do. And the DT allowed the same technology to be carried out in memory etc..

Now that Microsoft have eventually go their heads into the fact that we developers like business, and custom

objects, objects of all kinds and like to do all things 'objectively', they have turned the thing around.

With EF (Entity Framework 4.0) and the soon to be released 'Denali', or the SQL Server 2012 version, we

are seeing an objective approach by them - to all parts of the solutions we can design. LINQ technology also

fits into this approach, so that SQL script errors do not pass compilation stage - previously a bane for SQL use.

WPF and XAML, with dotNet 4.0 and the 'Data Binding' potentials of the new GUI, supported by the DP

(Dependency Property) system, mean that we can have our business objects directly used with our GUI

designs and creations,

I have had a small exploratory session with EF4 and it does promise to put the data source end of our coding

to rights - in that we can deal far more (entirely?) with custom or business objects. However, I have not yet

tried their 'Code First' approach which is designed to take existing dotNet coded BOs and 'go backwards' in

our old way of thinking - meaning make the database tables mapped from an existing code class of object.

This emerging approach to developing solutions for our customers keeps us more at arms length from the SQL

database and the server, and allows us to think and deal in objects throughout our whole solution.

Some of you may be delighted to hear this, particularly if you are new to all things SQL ;-) You may think that

most of it will just 'go away'.

However, the for new guys, the downside of this seems to be that we all still need to have the skills and knowledge

to be able to script (using T-SQL) query statements, and think in 'set based' algebra as opposed to processing

rows by the old cyclic row pointer approach. This in itself is still quite a challenge.

To get the most from SQL, and some would say even just to 'use' SQL, we all need to work hard at developing the

required abilities. I have been at it some years now and feel I am at about 'reasonable survivor' level. But I have

made some eNotes for you (available in ClickStart) so that you waste as little time as possible while learning.

SDK - documentation :

Let us pause a while to see a few useful things which the SDK has to teach us. Note DTs are very much part of the

ADO.NET approach of dotNet 2.0.

Also notice that a DT is part of a larger structure - known as a 'DataSet' (DS). If a DS is a group of DTs which may be

related (via primary and foreign keys) then a DS is effectively a database (DB) as we know it in SQL. Okay, the real SQL

DB has other objects attached, like functions and procedures, but essentially they are similar.

Checkout the following details :-

========================================================================================================================================

Okay then, read and re-read the above till it makes some sort of sense, then consider the summary below.

Note that a 'schema' is essentially a the table structure, as we think of things in SQL, and for the SQL database,

Summary :

DataTables will still be around for quite a while - we need to know about them.

Although they belong to the old world of WinForms they can be used with WPF, but will not tap into all its potential.

DataTables belong to an IT world which has gone.

WPF and 'data binding' in the GUI is best suited to business objects in collections.

BOs to be bound, are best designed to be Dependency Property (DP) objects and have suitably defined DP properties.

For new work forget DTs and use BOs in collections instead - then you can get the most our of dotNet 4.0, EF, and 'Denali'.

It is easier as a developer / coder to totally embrace the new world of dotNet 4.0 than try to mix older ideas, approaches,

classes, and existing VO type code, with the new WPF world. I read lots of News Group (NG) posts, were I see the

problems others seem to make for themselves by gradually coming to new things from the VO type world. This is

simply the hard way to do things.

DataTables are still fun, and at least we can wrap modern 'Views' around them. So if you are to use them use dotNet 4.0

View classes immediately, and at all times. This way you can at least still use skills and knowledge which applies to

the modern world of dotNet coding.

The Fun ! :

Okay then guys, having told you the truth, let us move on to enjoying the fun of working with the older DTs. I do have a

soft spot for them, and they did get me really into dotNet 2.0 and beginning SQL, so I ought to be thankful ;-0))

See you in the next subsection ..................

PHH - 7th. January 2011.


Creating a Structure

Making a Start :

Now we get down to business, and start by defining the columns of our new table.

Let us start by seeing the documentation for this process :-

============================================================================================================================

And before we study in detail what this all means, and see the code to do it, let us cast

our eyes onto an image of success - see below :-

==================================================================

As you can see in the above image we have a grid showing six columns.

Notice the column headers, or captions - these will be discussed a little later.

Okay then, how do we code things to achieve this ?

Making Columns :

We first need to see and study the top of the relevant PRG file in our little test project.

================================================

The highlighted file is the one we will study in the image below :-

================================================================

In the above image lines 17 and 18 show the extra namespaces required over and above the standard 'stub' ones

provided by the VN2 integration within VS2010.

Notice that in the code we have declared two table parameters, oDT and oDT2.

So having instantiated an 'empty' table and entitled it 'VODC11_DT' we then proceed on line 36 to pass the

table into a suitable method for structure creation.

Now we need to study the following image of the creation of the first column :-

=============================================================================

The first of our columns is a bit special, or different to the others. This is simply due to our design, and

no other reason. We are choosing to give it a few special property settings shown in line 94,95 and 96.

The column will be a unique valued column which is both non-editable and also auto-incrementing, once it has

been seeded with a starting value when the first row is created.

Notice that line 99 shows we have many other column definitions, but these are are similar so have been collapsed

for display purposes.

Let us now look at a couple of these when we expand the section - see below :-

=============================================================================

In the above image we see typical columns of the non-primary key type. Standard data columns.

Note they are capable of being edited, as well as NOT being unique. Take particular attention of the values of

the captions in lines 105 and 116, we will later compare these values to those we see in our grids on the GUI.

As you can see these are typical 'string' type data fields, nothing special at all.

Other Data Type Fields :

Our trial or demo DT has been chosen by me to have both an integer field as well as a 'double' data type field, as

well as a possibly more potentially 'difficult' or 'problematic' DateTime field. I say this simply because DateTime

values and objects seem to cause us developers a lot more hassle than simple numeric types.

Study the differences below :-

==================================================================================

As you can see above, not a lot really, just that we need line 146 to declare the 'DataType' property as one

of type 'DateTime'.

The name and caption are of course quite arbitrary since they do nor affect the behaviour of the column at all.

How does this look ? :

So then, having defined our six fields, or columns, and made up a column collection, what does it look like

before we add any row data ?

Simply check out the image below to answer that one :-

===================================================================

As you can see, we get 'not a lot' !

So it would appear that we need to add some row data to start making things happen.

In the following image we have made and added the first row, one which is a little different to the others.

Checkout the details below :-

==================================================================

==================================================================================================================

As the above image suggests, we need to study the Person class which we are starting to use for creating

our rows - but still in the old fashioned way - simply to pass values.

Here we have the Person class details shown below.

The Person Class :

Okay then, lets look at the class we have used with our DataTable example app so far. I say so far simply

because we may well improve and develop this same class towards a full DP class which can give us fuller WPF

functionality with other similar applications.

Check out the simplest of Vulcan.Net classes below :-

==========================================================

Above we have a simple class, but one with five different data types.

Each class field has related properties, all of which can be both read from and written to.

PHH - 7th. January 2011.


Making and Adding Rows

First in this subsection - let us study the 'CreateFirstRow' method - see below :-

===================================================================================

Remember the above first row is a little special, in that we need to provide a value for the 'id' property

since it is the 'seed' for the rest to follow.

If we do not provide a value then we get the default of zero - see below :-

=====================================================================================================

Likewise a higher values does as expected - see below :-

====================================================================

I have remembered to put the code back to seed at one - ' 1 ' - before I forget ;-)

Subsequent Rows :

This part is quite easy, it is simply built upon code and ideas used to create the first row.

However, because all subsequent rows are not special like the first one, we can write a method to help out.

Here is the method along with the data input 'CreateRows' method :-

=============================================================================================================================

[ .. don't worry if the ages of the people and their dates of birth do not quite agree - I just guessed at them !! .. ]

And the result of all this effort is seem in the following image :-

================================================================

Notice in the image above that the column headers are not apparently the same as the DT captions we defined.

It seems that we need to look more closely at how we have displayed our table and its data.

PHH - 7th. January 2011.


Displaying in a GUI Grid

Displaying the Table :

Let us start at the GUI end of things. Remember we are all about WPF these days - no more WinForms for me, that

was well over two years ago.

Let us see part of the Designer surface in the WPF/XAML editor 'shell' :-

====================================================================================================================

Shown above we have a DataGrid control from the Toolbox, and it has it 'ItemsSource' property set to be bound at runtime.

[ Remember :-- a DataGrid control, like a ListBox and ComboBox, is an 'Items' control. That is they all have a collection ]

[ ======== of items - as opposed to a single 'thing' for a 'content' control. ]

By declaring a binding which is unspecified (see line 9) we need to provide the object in our dotNet runtime code.

======================================================================

Above we see the code line numbered 41 which both sets the object link to the 'ItemsSource' property, as well as a couple of

other things we can view below :-

=======================================================================================

In the above image, line 218 associates the data table object with the data source for the DataGrid element.

If we did use the auto generate feature through checking line 219 then we would need to make our own columns

as we did for the table.

Since our captions of 'My Surname' and 'My Firstname' are not showing it would appear as though the DT column

names are used in place of those which are captions.

I am quite happy with this, and all we have done so far - now to look at the Person class - see the following

relevant information.

PHH - 7th. January 2011.


Filtering and Sorting

By the time we get to filtering and sorting the DataTable (DT) directly, we are starting to have fun ;-)

It has been a bit difficult finding out the full details for this as many text books and also web blogs on the

matter, simply do NOT go far enough, and/or include all common data types.

To get us into this section lets start by studying what the SDK documentation has to say on the matter.

We will start by looking at the 'SELECT' method and its four possible overviews - see below :-

=========================================================================================================================

As this suggests, if we supply certain parameter inputs we can determine the array of 'DataRow' objects which is returned.

If we neglect to pass in any parameter details we get this - study the following :-

=====================================================================================================

Since the most complex overload, that requiring three input parameters, is the most difficult to get to work correctly

for us, I have chosen to focus on this one in our demo / test application. If we get this to function in a suitable manner

then we can handle all other eventualities.

Before we get into coding and scripting, let us see what the SDK has to say about Sorting and Filtering generally.

Checkout the following information :-

=========================================================================================================================================

The above image has information which relates to one of the overloads (fourth one) which will 'SELECT' a set of row items from

the original DT row set, and return it as an array.

The 'select' method of the DT class can take up to three parameters, and this overload is that which I will use throughout our demo

application as we can learn more from its use.

Lets checkout some details for this overload - see below :-

=======================================================================================================================

As you can see in the above image we need to supply two string values and a third which is an enumeration.

The enumeration is of the 'DataViewRowState' which was shown in the images earlier in this sunsection.

Before we study just what needs to be done in code and XAML script to get everything to work, let

us first look at what our test applet looks like as we have it working at the moment.

Study in particular the three drop lists (combo box controls) to the right of the WPF form :-

Firstly, in the above image, we see the set of possible enumerations, these are not offered to us as string values.

However, for the filter conditions we have a bunch of string values in our drop list - see below :-

==========================================================

Similarly, we have another bunch of string values in our 'Sort' drop list which represent sorting conditions.

Checkout the image shown below :-

=================================================

And now finally we can see in the blue striped grid at the bottom of the WPF form the returned set of

data rows - which have been placed into a second 'DataGrid' control.

In the following image see that the results on view match the input strings for Filter and Sort :-

The label which acts as a heading to the lower grid, and the results on display, should be changed to fit the current

state of our application - the above was for an earlier phase of the development.

See below for the more appropriate scripting :-

================================================================================================================

Note the XML way to include a special character - embed it within th text string. Others will be seen for the sort and

filter conditions. Note the '&' is used like an 'escape' it has nothing to do here with the fact that we need an '&' to shown

on the screen. That information is within the 'amp' which is sort hand for the full correct name of 'ampersand'.

Filling the Drop Lists - 'ComboBoxes' :

Okay then you guys, let us see how we can fill the easier Sort and Filter drop list controls.

All we need to do is include a list of string values and test them to see that they both work, and also do not

cause any compilation or runtime errors.

Let us look at how easy it is to put this into the XAML section of our WPF form - see below :-

================================================================================================================

As you can see in the above image, these script lines create the label and the drop list for the sort condition.

And now we need to see the filter conditions, as they appear in the XAML script :-

=======================================================================================================================

There are a couple of things to notice in the above image, one is the use of the '&' to allow the alternative form of

the ' < ' symbol (said less than) as otherwise this would be taken as part of the XAML script syntax.

Check out lines 41,42,45,46 and 47 in the image above.

Now then, although we seemed to have used ' > ' (said greater than) to good effect in our script previously, we can

also use an 'escaped' type format, or XAML syntax.

See below for example :-

========================================================================

Applying the Selected Condition Text :

In order to help us clearly understand both the script and the code, I have changed the default names of the drop list

controls to suit the situation - see below for an example :-

===============================================================================================================

Notice in the above image that the 'Name' property is now "cbxFilter" - instead of the default 'comboBox1'..

This name change is also reflected by the changes I had to make in the dotNet code - see below :-

============================================================================================================

And also the 'DoChangeFilter' method as well - checkout the following :-

========================================================================================================

I will now make similar changes to script and code for the other two drop list - those for Sort and 'RowState'.

Let us now see the 'ApplyFnS' method to see how we are using the string input parameters. (Be aware that

here we are using the two parameter overload, and that will very soon change.)

=================================================================================

Line 206 in the image above shows the two parameter overload in use. Two string inputs, one for

the filter and the other for the sort.

In extensive tests I have carried out during development of this DataTable applet I have found this code above to work well,

and give results as would be expected from the data shown above.

Filling the Drop List for the Enumeration :

Okay then guys, are you ready for a more complex piece of script to allow us to fill the combo box control with enumerations

for the 'DataViewRowState' (DVRS) enumeration ?

However, although the procedure is more complex, it does fall in line with how to fill any drop list control with any system

enumerations, so a small amount of new learning goes a long way ;-)

Let us first remind ourselves of what we have in the DVRS set of options - see below :-

=======================================================================================================================

*** PLEASE *** Do not be tempted to provide string values as we did for Sort and Filter !!

The beauty of WPF and the GUI controls is that 'Items' controls accept any type of object quite nicely. So let us use

the fact and fill the 'ComboBox' control accordingly.

To get us going in the right direction study the following image - checkout the drop list values :-

==============================================================================================================

As you can see in the above image we have an exact match between the drop list and the members of the

enumeration itself.

We are going to see how we can bind the collection of row states to the GUI control.

Here is the way to setup the binding on the 'ComboBox' - see below :-

==================================================================================================================

In the above image it is line 70 which does the binding - here we are binding to a resource (static) which we must setup

in a 'Window' or 'Grid' Resources section - well, that makes the most sense anyway.

Let us use the widest scope for this WPF form, by using 'Window Resources'.

This is what we need to script in the XAML section of the application :-

=================================================================================================================================

In the image displayed above, we firstly need to examine lines 6 and 7.

These follow the guidelines written in 'Section C' earlier in these eNotes.

We are pointing to the required namespaces, and making an association between them and our local 'prefixes'

called 'sys' and 'data' - take care to use the correct case, and to follow this through in all your script lines.

In order to make line 11 work we need the 'sys' prefix setup in line 6.

We can see what is going on if we look at the intellisense from the Vulcan.net editor - see below :-

=============================================================================================================

===================================================================

This looks like a static method called 'System.Enum.GetValues' which takes an existing system enumeration type

and returns an array. yes, an array not a collections - smells of old fashioned ?

And in order to make the correct list of values we need line 7 - where the prefix will help us point at the namespace

which holds our required enumeration. Remember that the DVRS is in the System.Data namespace.

Looking at line 13 above, this is what it is doing, providing the type for the static method to use to make the array.

So the result is a System.Array of DVRS enumerations.

DON'T ... DON'T ......

Just do NOT try the following - this gives string values within 'ComboBoxItem' objects :-

===========================================================================================================================================

Now lets move on to see how the interaction works.

Selecting from the DVRS drop list :

There is a simple start to the combo box selection process - see below :-

==========================================================================================================

So what does the method on line 86 do for us - the 'DoChangeRowState' method.

Study the following code carefully :-

========================================================================================

Yes, we need two string type inputs and an enumeration.

The filter and sort strings are class variables and will have been set previously, either interactively or in the Initialize() method.

In order to get the interactively selected enumeration we simply have to make the correct cast - see line 91.

Remember, in the drop list we are bound to a strongly typed array of DVRS items and not a collection of ComboBoxItems, so

the cast is quite different to that of Sort and Filter.

Almost finally, we need to use the 'SELECT' method of the DT members, to apply the three parameters - see below :-

==============================================================================================================

And the FINALLY, we can see how to display the results array in a second DT - the blue one at the bottom of the form.

Here is the result - before we look at the code :-

In the above image we see a second data table, and one with the same structure as the first one.

It may have escaped your notice in earlier images to see a couple of lines which copied the structure by 'Cloning'

the first DT just after it had been created.

Lets look at this now - see below :-

================================================

Although the first table has rows as well as a structure, it is only the structure itself which gets copied in

the 'cloning' process.

So now it will make sense if we study the 'MakeResultsTable' method shown below :-

=====================================================================

Above we see code to remove any existing rows from the results data table, and the we construct a set

of new rows from the array returned from the SELECT method.

And then lastly of all we display the result table as shown in the following image :-

=====================================================================================

The only difference being that we use a View instead of binding the DT itself - even though this will produce a default

view.

I like to make a view of my own, so I can then apply sort, grouping and filtering on the view. This is a better WPF approach

it seems to me than directly binding data source objects.

In this final image notice the XAML script does not bind to anything, this means we can code the property

called 'ItemsSource' - checkout below :-

=========================================================================================================================

I leave it to you to rename the element more suitably - like 'dgResults'.

Time to move on, see you in the next subsection ................................................

PHH - 7th. January 2011.


On-Screen Interaction - Add, Edit and Sort

PHH - 7th. January 2012.


Wrapping a View around

"One view or two madam ? "

What on earth do I mean by the above heading ?

Well, quite simply we can use the standard (old fashioned or early dotNet) way or indeed we

can use a more up to date dotNet 4.0 approach.

One way allows us certain functionality, like being able to enumerate the collection or data source, and

the modern way gives far greater functionality and flexibility to the coder / developer.

DateView or ICollectionView ? :

PHH - 7th. January 2012.


Relations, a Table too far !?

=========================================================================================================================================

PHH - 7th. January 2012.


CSV file into DataTable

[ CSV - 'comma separated value' ]

As I assume you well know, we all get files in CSV format from external sources, often sent

to us by email attachment etc..

It is very useful to be able to easily convert this data into an in-memory DataTable object.

The image shown below is from a prototype application, and the data in the DataGrid started out

as a CSV file. Later in this section we will see how to code things to make this happen.

Study the image carefully :-

The format of the CSV file is available for study in the next image, shown below :-

[ .. Notepad is very good for displaying text files so that no 'extra' characters are added by the displaying application ..]

================================================================================================================================================================================

The same file is also readable within Excel, as the following image shows :-

=======================================================================================================================================

And now we need to have a first look at the code which can read in the data from the CSV file.

The following code requires a 'well formed' CSV file - we can discuss what is meant by 'well formed' later on.

For now let us just say we have to have no extra lines of text, and that field names in the query statement have

to have exact equivalents in the header line of text.

========================================================================================================================================

==========================================================

PHH - 9th. January 2012.


DataTable to CSV file

Okay then you guys, let us start with a DataTable object that has been made by our application code.

Shown below is just such a table, and it is displayed within a 'DataGrid' - check it out :-

Once we have run the new STATIC method which is in Layer 6, the 'SQL_my_func' layer, we get the following

result when we double click the new file to open Excel.

========================================================================================================================================

The Vulcan code to do this is shown below.

Note that we can provide different characters (strings), or groups of characters as separators and delimiters.

It all depends on what your target application will accept - that determines the format of the CSV fields.

=====================================================================================================================================

Note that in the above code no attempt has been made to identify, and remove or change, strings within

the field data which are the same as the delimiter strings. This may be required by some situations and so

you may need to add your own modifications to this code shown above.

Summary :

It is often a very useful facility to have available - that of making an Excel readable file from some returned

data in a data set from the SQL Server.

This sort of facility is often available in Reporting Services, part of the overall MS SQL Server download file.

PHH - 10th. January 2012.


DataTable to XML file - and back

Enter topic text here.


Chapter 3 - full SQL access with ADO.NET

PHH - 7th. January 2012.


Using the 'DbProviderFactory' class

As I have previously said, a few years back I took advice from a professional colleague who suggested that

in my coding of apps using an SQL server as a backend data source, I use the 'super' class named

"DbProviderFactory" instead of hard coding my solution to work only with the Microsoft 'flavour' of SQL server.

It does NOT give much of an overhead penalty at all, and at least makes my application much more flexible

as to how it could be used by others.

I see this issue as one which needs to be addressed early in one's experience of SQL coding, and from then on

it will be taken care of.

I seems stupid to me not to use DbProviderFactory!

Here is what the SDK has to say :-

===============================================================================================================

This in fact is a simple class as classes in dotNet go, see below the members :-

===============================================================================================================================

And then seeing what the SDK has to say about the Constructor we see the following :-

==========================================================================================================

How do we make our DbProviderFactory instance for SmallTrader ? What does the code syntax look like ?

Well, see this short code snippet from the working code :-

==============================================================================================================

Obviously we need to know what the 'oConnDetails:cProvider' is and where it comes from.

We cab see that we have the code using a string property of our own class called 'ConnectionDetails', this class

is shown in detail below :-

===========================================================

The above class has been designed by me to work with the 'Connection' screen, so properties match up with

what is shown on the interactive entry form for connection information.

I have to be honest and say the basic design was done years ago (three?), and for a WinForms version, when dotNet

version 2.0 was all the rage ;-)

So there are a few old fashioned parts to the remaining 'underlying' code. But it still does work OK !!

Maybe later I can move it on towards a collection based approach, with binding to the form drop list.

For the moment see the following detail of the content to the 'Config' file where our app stores its SQL connection

and logon information :- [ the config file is named 'ConnectionDeatils.xml' ]

===================================================================================================

In the above image checkout the highlighted 'cProvider' tag line - note that it is "SqlClient".

All this extra information simply means we are effectively code a line such as :-

 oDFactory := DbProviderFactories.GetFactory("System.Data.SqlClient")

Where oDFactory is of type 'DbProviderFactory'.

Now what do we do ? :

Well, having got our instantiation sorted out, we can use our instance of the 'DbProviderFactory' to

supply us with the necessary 'bits' without need to use the 'SqlClient' namespace directly.

By this I mean we use the method 'CreateConnection' exposed by the 'super' class, and not the 'SqlClient'

class.

See below how our application code looks for creating a connection :-

==============================================================================

As you can see in the above code, we need to see what is declared at the head of our class, as

class variables. There must be others as well as the 'connection' type variable show above.

Study this carefully :-

=========================================================================

Since the ADO.net 'stuff' for connecting to the data source, and sending queries, is NOT an application

based class, we will use the same STATIC class for these ADO.net members for each separate app we

deign and create.

Notice in the above code we have suitable types for 'provider' and 'connection'.

Making a Connection String :

Now that we have 'oDConn', and instance of the DbConnection class, we can use this as follows to set a

connection string - see below :-

==========================================================

However, we first need to use the stored information in our deserialized 'config' file to make a suitable string.

Check this out below :-

==========================================================================================================

Okay, I hear you say that there are connection string builder classes etc. etc..

But hey! this is simple enough, and it works well too - and has done for three years, so why change things ?

Moving Forward :

For those of you who wish it, and are interested, here is what the SDK has to say about the String Builder(s) :-

=======================================================================================================================================

=======================================================================

And for those who like to read, study and learn, here is a bit more information :-

================================================================================================================================

I suggest that if you wish to do more than what I have included in the Small Trader app, then you do your own research and testing.

Basically, for now, I am happy enough with how I do things for connection operations.

Now lets move on and try and make a connection.

Opening up a Connection :

Once we have set up our DbConnection object correctly, we simply need to make one call from our code.

See below to find out how to attempt to open a connection :-

=====================================================================================

In the above image line 406 is the only one required to make the connection we require.

Below is the SDK documentation :-

===================================================================================================================================================

In the images from the SDK we see we can close the connection with a simple one line of code as well.

Here is the Small Trader 'Force Disconnect' method :-

=======================================================================================================

Line of code number 419 is that which is required.

Testing our Connection WPF Form :

Let us now move on to try and test the lines of code we have been studying.

I will first of all launch the Small Trader application and then navigate to the Connection screen.

Below I am testing the green button to try and make a connection with the string details existing in the

relevant object properties :-

And now what if we view the string which has been used - see below :-

=============================================================================================

It is interesting to look at the methods which respond to the button clicks of the respective push button

controls - see below for full details :-

====================================================================================================================

Yes, it is obvious from lines 212 and 222 / 224 that we are simply calling on members from the STATIC class

making up layer 6, the 'SQL_my_funcs' class.

*** Tip *** when we refer to access of a STATIC field we must use a colon ( : ) before the name of the field itself.

Yes, the rest of the namespace / class test takes a dot ( . ) or point, but not for the 'access' symbol. For Vulcan

that must be a colon.

Let us now move on to the next subsection where we can see a few other interesting things about the Vulcan code

behind the Connection screen.

See you there ....................

PHH - 7th. January 2012.


ADO.NET - Various types of Query

PHH - 7th. January 2012.


Returned data - how to handle it

Okay then guys, now we know that ADO.net code can be used to ask different sorts of query, then we

can move on to see how we may deal with the returned data - different types thereof - in our calling

Business Logic (BL) code.

General Comments :

It is in 'Layer 6' that we see the coded use of ADO.net members to do our tasks for accessing the SQL database

via the server. So this is where DbFactory 'stuff' is seen.

However, when it comes to using the returned data from our queries, it is the 'Layer 5' members which we will see

the code to deal with the data send back by the server. Now and then we may see some code in the Business

Logic (BL) layer too, but mostly the layer below the BL is where we will see it.

Scalar Values :

We have just seen an example of this in the last subsection when getting an error code returned from

the server.

See below for a cast to integer :-

=============================================================================================

[ .. above is an example from 'Layer 6' itself, but this is unusual, 'Layer 5' and the BL layer will be more common .. ]

So in line 665 above, the returned object is cast to an integer for the rest of the BL to process.

Obviously, it is relatively easy to cast to any data type we know the returned at to be, DateTime, Boolean,

or whatever.

Below is another example, but from 'Layer 5' this time, the STATIC class for Small Trader :-

===========================================================================================

As you can see in the above code, this query statement returns a customer reference number, so is cast to the

required integer type.

'DataSet' Records :

In the following image we see an example a method in 'Layer 5', where one or possibly more records are returned

for a customer reference number 'search'.

In doing my testing I have had occasion to get more than one record, simply because of previous errors etc..

Normally, in a correctly setup database the various constraints and the likes, on the DB itself would not allow the

Primary Key to be duplicated.

Checkout the following details :-

==============================================================================================

As you can see in line 228, we get a data set returned from our 'Layer 6' static method. This means that

when we get to executing code on line 229 the connection is closed and all data is in the object 'oDS'.

This is a contrast to what happens below when the

'DataReader' and Subsequent Use :

In the 'Layer 5' part of the Small Trader application, we see an old method of mine (not sure I would do things like

this nowadays!) which makes an 'ArrayList' object from the subsequent processing of a data reader object returned

by the server.

As you can see the 'DataReader' is used and processed after the line 169 is finished, or executed. If we close the

connection before this processing we get an error!

The open connection allows the data to be pulled down as required in the 'Do / While' loop and the processing code

may be written as seen below - or in a different way seen further below.

Checkout the details following, particularly the use of the oDr (DataReader) object :-

=============================================================================================================

We can see in the above code that the data reader supplies a record at a time, and that record may have its fields

accessed by a fixed numerical form of supplying the 'ordinal' value.

Before we do more on the processing and access side of the data fields, let us view the last couple of lines of the

same method as that shown above :-

======================================================

Notice that after the data reader is closed (line 196) we can the - on line 203 - close the connection.

Alternative Access to 'DataReader' Fields :

In some ways the fixed value 'ordinal' way od accessing the data fields is a bit old fashioned.

Luckily for us there is another, and more flexible, way of doing the same thing.

See in the following image how we can access the fields using a specified string value as opposed

to a numerical one :-

===================================================================================================

Doing things as shown in the image above, means that we do not need know the position of the field in the record,

and can even use a coding line order which more suits us and our processing.

However, we still need to know what type of data we wish to retrieve.

Please notice that we can even match the syntax of the code lines, it will not matter to the resulting casts and

property assignments.

Here is a useful image - see below :-

======================================================================================================================================================

The 'GetProviderSpecificFieldType' looks interesting, as do the other couple of similarly named methods.

Well, I have had a bit of a play around with these and got nowhere, so will leave it up to you if you so wish.

Basically I was hoping to be able to get a value without having to say what it was be selecting 'GetString' and the likes.

However, maybe you are starting to see why I like the DataSet approach ;-))

See you in the next section ..............

PHH - 7th. January 2012.


ADO.NET - getting things better

When I sketched out the contents of this section, I intended to write about how the ADO.net code

required to do the database access was a bit longwinded, and how it may discourage a new user

coming from DBF related technologies.

However, since making my chapter and folder 'sketch' I have developed and tested the new STATIC

class called "SQL_my_funcs".

By providing myself (and you too) with this 'Layer 6' fixed class, which can be ported to every SQL

application that I write, and used in exactly the same way each time, I have in effect removed the

need for this subsection of the eNotes.

ADO.net is an impressive technology, it has just worked for me, right from the very start. I did not

have the benefit of using SQL or ADO (of old) prior to me coming to Vulcan.net, so I had to deal with

everything as 'completely new' ideas and ways of doing things.

The only thing that 'put me off' a little was the amount of code needed to leverage the power of ADO.net,

or so I thought. Now I have rearranged both my ideas and my code, I can use SQL from by VN2 code

as easily as using Management Studio (MS) to query the server and attached database.

This is just what I required, so now my Business Logic (BL) is not cluttered up with ADO.net code.

AND, since dotNet use has gotten me to think in terms of objects, Business Objects (BO) and even

with WPF forms objects at a GUI level, it is nice to be able to carry the idea of objects right down to

the database (DB) access itself.

Getting Things Even "More" Better :

If you excuse me the awful English ;-) the fact that EF (Entity Framework 4.0) is around the corner for us

VN2 guys we are in for an even simpler and better time ahead.

One thing that will not change when we are able to migrate to EF4 is the fact that we will still think in

'objective terms' - our BO as DB objects - as we have done with this demo and sample application of

the 'Small Trader'.

Our ideas of 'layers' is just as valid, and should be even simpler to put into practice - so we are not

wasting our time doing all this VODC 'stuff'.

So having said all that we can move on to the next section.

See you there .................

PHH - January 7th. 2012.


ADO.NET - improving record manipulation

Okay then, some of you may disagree with what I am about to say, BUT, that is your choice !

When it comes to handling returned data, whichever way we do it for a bunch of records, I prefer to

go straight into my application BOs, and make collections of these when it is a suitable thing to do.

If I design my BOs to be Dependency Objects and create collections which are particularly friendly to

WPF and its GUI controls, then life will be as easy in dotNet 4.0 as I can make it - as well as getting

all the extra benefits into the bargain ;-)

So this section could be called "Moving away from Record Manipulation."

Obviously we need to do some manipulation of records in DataTables and DataReaders, BUT, if we

go straight to BOs this will be kept to a minimum.

At times a DataTable object can be used directly, after wrapping it in a view, or similar. But generally,

it will be worth while writing the code to make the BO, and just go for it.

Certainly, when we come to EF4 we will be dealing exclusively in objects, so lets get mentally ready for it

before it hits us as a necessity.

Once again, the fact that we now have a 'Layer 6' STATIC class, along with our application based STATIC

class also, means we can keep this subsection very short indeed.

All that we need to say about processing records from returned data - sets and readers - can be dealt

with in the next subsection where we focus on making instances of BOs out or table and record based

data returned from the server.

And for any existing BOs in RAM we can use Layers 5 and 6 to send new and changed data directly to the

server, and hence database.

Let us now move on to see how we may choose to create our BOs from our returned data sets.

See you in the next subsection .................

PHH - January 7th. 2012.


ADO.NET - record to business object

Okay then you guys, this is an IMPORTANT section of these eNotes !

As most of our SQL applications will have more than one BO and as such we will need more than

one static method (in our Layer 5) to convert a record returned by the server, into an instance of the BO

in focus.

Let us take the Small Trader example, and also the 'Customer' class as a useful guide to what is required.

Using 'DataSets' as the returned source :

The STATIC member 'SendDataSetQueryScript' returns to us a DataSet object before it closes the connection.

Look closely at the following image details of how to access the field data values :-

===================================================================================================

The above code can be used to illustrate the techniques required, even though it appears to be an early bit

of my code in this particular application.

Basically, we are asking for records to be returned (one or more than one) for any Customer with a specified

reference number. Yes, I know that in an ideal world this should be always a single record - BUT !

So this less than 'perfect' method will return the first one (record) of any set returned for the specified Customer.

Since it is the process of coding that matters, we will not worry about other issues of rights and wrongs ;-0))

What is going on above :

Basically, we are expecting only one table to be returned in the data set, and this table ought to be a single row.

This will always be the case if we have setup our database and tables correctly - we will have only one customer.

So taking the first row from the first table in the set, is always going to give results if the Customer is found.

So yes! It would be a good idea to have another STATIC member which checks to see if a Customer is in the DB

in the first place, and then only if found will this member (method) above be called from the static class. I will work

on that extra checking method. [ .. see Addendum below .. ]

For now, notice that in the above code we extract a single data row as an object, and then for every property in the

newly instantiated BO, associate a data value with the appropriate object property.

Don't let the oDR variable name confuse you, it is a DataRow object and not a DataReader one. This means we

have to process the items a little differently.

There may be other ways to do this, but the 'convert' static system class seems to work for my data, just fine.

Alternative way - from 'DataReader' object :

WE have an alternative query method in our ADO.net STATIC class. It is called 'SendReaderQueryScript' and it

is in use below in the displayed image :-

=================================================================================================================

You may think you have seen much of this previously, and indeed you would be correct, but now I have replaced

all the fixed value ordinals with a call to a method of the DataReader class.

Personally, I feel this change makes the whole code section more readable, and also easier to maintain and revisit

after a time away - AND - far less likely for mistakes to be made by the programmer / coder.

Note the last line, this is the correct and best way to close a DataReader object, nothing else is required. The SDK

documentation says so, and Don Caton said to old VO guys - not to use ' := NULL_OBJECT '.

Summary :

So there we have it, two different ways of making an instance of a BO from data returned from the server.

Let us move on to see what we need do to get ADO.net to work for us in the other direction. How we save and update

BOs in memory to the SQL database and specific 'object' tables.

See you in the next section .............

PHH - September 4th. 2011

Addendum :

In our discussion above we said we needed a static method to check on the existence of a Customer in

the database - are they there, or are they not ?

Well here is a short method which does as we require - it did not take very long now that I can do the

construction of the query string and then just call on the relevant static member from 'Layer 6'.

Checkout the following few images :-

=========================================================================================================

Yes, this is correct, Customer 103 is already present.

I know from using Management Studio that Julie (reference 103) is in the table more than once - see below :-

===========================================================================================================================

However, the running customer reference number has not gotten any where near the thousand mark - see below :-

========================================================================================================

Now that we see that it appears to work, here is the coding of the new static method :-

=====================================================================================

This now allows us to rewrite the 'GetCustomerObjectFromRef' method as follows :-

[ .. I realise that some may prefer to trap the condition before entering this method - its up to you ! .. ]

=======================================================================================================

In fact, looking at this new method, we may decide that we also need a static method to tell us how many times

a customer is present in the table - or one to supply any records for Customers who have 'Multiple Entries'.

I will leave these for another day - BUT - wish you to see how easy it is to add new 'Layer 5' methods which

allow us to write cleaner and clearer Business Logic (BL) code.

PHH - January 7th. 2012.


ADO.NET - business object to record

So far, in our process of using ADO.net, we have basically just asked reasonably straightforward

queries and gotten back a bunch of data from the server. The real work was processing this returned data.

Now with creating new records at the server (INSERT), and updating any that already exist (UPDATE), we

need to do all the code work before we send our query statement.

In fact there is a reasonable amount of coding required - luckily a goodly chunk of it can be placed in the

ADO.net 'Layer 6' so that for each application the 'Layer 5' members are reduced in size from what they

used to be before I made the STATIC classes.

For this section we start at 'Layer 4', the BO layer, and move downwards towards the server and database.

Once again, let us take our Customer so that we can illustrate what is going on.

If we imagine the scenario that we have a Customer business object in memory, all set up with data values

associated with each property, and we need to save this to the SQL database.

Well, the 'Customer' class is very straightforward and so we do not need to look at it directly, it is enough to

see it in use in the following code.

First lets look at the 'FillTypedList' method which has been written by me to go with the Customer class.

Check this out carefully :-

=============================================================================================================

The above image shows one method of a pair which is required for each BO in our application.

Notice how each of the Customer properties is used to supply a value to instantiate a 'SQLfieldSpec' object

to specify and hold the necessary details for the field / column in question. There is a one-to-one correspondence

between the columns in the SQL table and the fields spec objects added to the strongly typed list.

Now then, what is done with this list once it is instantiated and filled ?

Making a Business Object :

Okay then guys, let us take the strongly typed list and do something useful with it - see below :-

====================================================================================================================

If we look at the above code we see that line 153 takes the strongly typed list and supplies it as one of three parameters

to the STATIC member in the next layer down, 'Layer 6', where the 'MakeBusinessObjectDetail' method resides.

The other tow parameters are very simple, one the name of the data table in the SQL database, the other is the type

of operation - INSERT or UPDATE, and this is done via an Enumeration :-

========================================

Well then, what does the STATIC member 'MakeBusinessObjectDetail' (MBOD) do for us ?

It simply takes the information of the field data and its values and turn this into a format suitable to construct a

SQL query statement, so we can then send it to the server.

In the following image we see how this is achieved for us - remember this is all written and tested, and will not be

normally even seen by us - thank goodness.

Check this out :-

=================================================================================================================

The above code is basically in two sections, one for INSERT and another for UPDATE.

The static members 'CreateFieldSymbolPairs' and 'CreateFieldsSymbols' are used depending on the mode of

'CRU' required. [ ** remember 'CRUD' stands for create, update and delete - here we ignore 'D' ... ** ]

There is a different syntax required for CR and U - hence the two different static members - 'FieldsSymbols' and

'FieldSymbolPairs'.

Okay then, we make a suitable text string to represent the long and complex query 'script', but before we can send

this script or query statement, we need to add each parameter to the 'command' object we have been building up.

In the STATIC class for 'Layer 6' I have included a few small supporting methods to assist in this task.

So if we study the static member called 'MakeBusinessObjectChange' we will see just what is required for these

parameters. Remember that the statement associated with the 'Command' does not have the actual values within

it, just the '@' type symbols.

Below we are getting towards the end of the longwinded sequence, keep patient! :-

=====================================================================================

Okay then, in the above image we see that the 'Command' object has now got a suitable query statement property,

as well as a connection, but we are passing the 'Command' object to a new static method called 'AllParamsSetAdd'

so that the parameters may be added.

Checkout below the details in this static method :-

================================================================================================================

[ *** note *** the '@@' is required simply because the word 'Symbol' is a reserved word, and this is VN syntax .. ]

In the above code, all we are doing is moving through our strongly typed list of field spec objects and using their

properties to add a suitably structure parameter to the 'Command' object.

The final static method required is only short, and called 'SetAddParam', here it is, shown below :-

==================================================================================================================================

Just to remind you, if we were doing things the old way in ADO.NET we would have to code a parameter group of lines,

like those above, for each of the fields in the table for our Business Object :((

As you can see the code makes a parameter, and adds it to the collection, from the 'input' method parameters.

Summary :

It may seem to have been a long winded way of achieving what seemed a straightforward enough task - sending the properties

of an in-memory BO to an SQL table, but at least most of this we do once in our code, and after thorough testing leave it to

be used, and only have to write the 'Layer 5' related stuff - that for the specific objects used with the business logic.

Having done this both ways, I know I am much happier with using the STATIC method approach.

I think a nice spin-off is that the ease of use encourages me to write probably more application related static methods than I

would have otherwise done - yes, this means better coding practice I suppose.

Time to move on, see you in the next subsection ....................

PHH - 7th. January 2011.


The Future - Entity Framework

PHH - 7th. January 2011.


Objects all the way !

PHH - 7th. January 2011.


Code First - another approach

PHH - 7th. January 2011.


Chapter 4 - Data Access Layers

PHH - 7th. January 2012.


a STATIC class approach ADO.NET

PHH - 7th. January 2012.


Layer 6 - DateTime facilities

==========================================================================================================

==================================================================================

===========================================================================================================

====================================================================================================

PHH - 8th. January 2012.


Chapter 5 - 'Management Studio'

Seeing your data :

If you are going to do anything at all with MS SQL Server then you MUST install and start to use the

latest Management Studio (MS) software from Microsoft.

"Management Studio is your friend !"

I often refer to the MS 'shell' as it effectively sit around your attached databases.

It is VERY effective at communicating with the MS SQL Server, and I have logged up many hours use

with it over the last three years or more.

Using MS you can learn much indeed about SQL and the 'flavour' of script that is used to construct the query

statements. T-SQL (said Tee Sequel, and sometimes Transact Sequel) is like a programming language of old.

It would be described as a interpretive language as opposed to a compiled one, I would guess.

So the T-SQL script is parsed and run at the time it is required.

Getting the Latest Version :

Whatever you do, try to get the very latest version of MS, it tends to come with the download of the SQL server

itself, although in the past I think I have managed to find a separate download.

Microsoft seem to change the downloads available for SQL Server and its support software, on quite a regular

basis, so writing eNotes is quite tricky :((

The GOOD thing about the latest version of MS is that it is always back compatible, so I can connect to both

2005 and 2008 server instances with my current version, and deal with databases of the 2005 and 2008 variety

from the same Object Explorer (OE) pane.

*** WARNING *** MS is not forwards compatible however, and this can catch you out under certain circumstances.

In the following image we see one instance of Server '10' ( we call it 2008) and one instance of

Server '9' (we call it 2005) open at the same time in the OE pane.

Check this out :-

==================================================================================

I am not sure whether the screen information in MS means that a connection is 'open' all the time

we see the OE pane as above. My guess would be that a possible connection is tried and confirmed

by the OE pane content, and that when we subsequently send query statements - with Execute - there

is then a connection opened and closed again afterwards.

It certainly feels to me as if the MS shell uses a disconnected model for its operation.

Once the MS 'shell' is open we make further connections like so - see the image below :-

=====================================

And when the top option of Database Engine is selected we get the very familiar dialog screen - that which

is always shown when we launch the software initially.

Here is what I get on my development PC :-

======================================================================================================

Notice that there is a further option at the bottom of the drop list, to search for more instances of MS SQL server.

This can be helpful at times, particularly when a network is involved, wireless networks may come and go

as the 'shell' is in use.

One thing I do also notice on this old machine of mine, and that is I seem to have server instances which

are no longer functional - in 'street' parlance I would say it has some old 'crap' in it, and I don't easily seem

to know how to get rid of it. So take care with any installations you do - although you can reinstall it is not

always easy to clean up the drop option list.

Disconnecting - How and When :

At any time you wish (almost) we can choose to disconnect from a server present in the OE pane.

To disconnect just right click over the server item line in the OE pane to bring up the context menu

as seen below :-

===================================================================================

Clicking any 'Disconnect' option is quick and the connection in focus is quickly removed from the OE pane.

Difference Between Connection and Attachment :

If a connection is a way of pointing the MS 'shell' at the instance of MS SQL Server either version 2005 or

2008 (and soon to be 2012), then what is the 'Attachment' ?

Well, once we have made a connection to the instance of a Server, we see the available databases listed

in the 'DataBases' folder in the OE pane - see below :-

====================================================

There are not that many attached to this instance, so we had better look at a 2005 instance of mine to get

a fuller and more illustrative list - check this out below :-

=====================================================

I regularly work with both these instances, and with the databases attached to them.

The new work I do with my Vulcan and dotNet coding, is with server 2008, but I do have a lot of work

with a Cardiff client when I have to use 2005 'flavours' of everything, because that is what they use

on-site with their LAN.

Attaching Databases :

You need to think about this for a while, and make sure you get your head around it - just so it makes

the rest of these eNotes make sense, particularly when we come to VN coding.

When we choose to 'attach' a database in the MS 'shell' we seem to be doing no more than constructing a

suitable connection string which the shell tries out, and if OK it stores it, leaving an item in the OE pane

under the 'databases' folder.

It could be said we are registering a database with the 'shell' itself - making a link. Attaching is NOT a

thing we have to worry about when we write our dotNet code.

So how do we Attach and Detach ?

Right click over the 'Databases' folder name as seen below :-

==========================================================

After selection you get a couple of dialog screens, one after clicking the 'Add' button seen in the image below :-

====================================================================================================================================================

Selecting a DB from those on offer should bring up something like show below :-

=========================================================================================================================================

Then click the OK button and cross your fingers - well German friends can hold their thumbs - I do both ;-0))

This sort of stuff with SQL can be a bit of an 'art form' ! - as opposed to a science.

If all goes well you may see something like this in the OE pane :-

=====================================================

Yes, I know, the shell software seems to give the new database item a rather unwieldy name !

To put this to rights, try clicking for the context menu while pointing at the requi8red item, then select

to do a 'Rename' - having just done this I get the following :-

======================================================

Depending on the 'depth' of the folder where the .MDF is stored, the name can either be large or even HUGE !

I remember getting an ear bashing by GS (Global Santa) for this when I was just starting out with SQL some three

years back - he did not seem to appreciate that it was done by MS itself. However, he was the only NG guy giving

me regular encouragement, apart from some gems fro Mike 'P' too I seem to recall.

Many guys seem to run a mile from the server download and installation itself.

Probably because few do it for themselves. Often at customers sites they already have a full and working installation.

This will be different for small companies like 'PC Planet', where for the Small Trader application I had to do the SQL

Server install, and get all bits talking together etc. etc..

Summary so far :

It would appear that making Server instance Connections, and Attaching databases, is really just registering connection

string data with the MS 'shell' itself.

Obviously there is no way we can have connections open to multiple servers with multiple DBs, all at the same time.

So in my mind I think of it as the disconnected model, where connections strings are registered wit the MS 'shell'.

This model has served me well enough for quite some time.

Detaching :

This is easy, and only really needs to be done to keep clear, or clean, the working space in the OE pane - no other

logical or technical reasons.

So to deregister the database part of the connection string we do as seen below :-

===============================================================================================

You do not need to cross your fingers an/or hold your thumbs for this operation ;-0))

So when is a connection made ? :

This is easy to answer, but takes more time and effort to demonstrate, as well as illustrate.

Any and every time we choose to send a query statement to the Server.

Here is a VERY common example of what I do - see the following image :-

===========================================================

And now shown below is the results displayed in the 'results' pane - plus other bits :-

==========================================================================================================================================================================

Selecting to look at all the fields from the first 1000 rows of a table is VERY often the place you start when

trying to make up a new query statement. I must do this many times a day.

I suggest the 'shell' opens a connection when an 'action' is triggered, and then when all the data is returned, the

connection is closed by the 'shell' once again.

'Actions' - what are these ? :

One group of actions are those on offer on the context menu - there are a few more beside the Top 1000 rows,

and Edit the first 200. But we will see the scripting options later.

For now just realise that a menu option can cause an 'action', or query to be sent to the Server, or we can instigate

such an action ourselves.

Lets say we wish to start a new query, one of our own making. Checkout below :-

===============================

The result of this selection to to bring up a new tab as shown below - completely empty of content :-

==============================================================

Now we have a nice clean surface on which to type our own query scripts, or statements.

Simply we have effectively an editor in this tab. Once we enter a suitable query script we can 'Execute' the query.

To do a useful test I will slightly adapt the script we had for the Top 1000 rows - see below :-

========================================================================================================

Notice the red 'pling' and the 'Execute' icon/tag at the top of image.

Once we have some text in the tab clicking this cause the text to be sent to the server, whether it is a correct

statement or not. If not we get a error returned.

I suppose you had better see an error or two, just so you get familiar with what will happen quite frequently ;-))

===============================================================

Well, with my little test the 'shell' is smart and already seems to warn us that we have an error.

See above and below to find out what is returned as a message when we send incorrect scripts.

========================================================

Obviously forcing an incorrect table name, as well as an incorrect field name does not fool the MS 'shell'.

PHH - 7th. January 2012.


Chapter 6 - STATIC classes

I used to think that STATIC methods and properties were something complex and confusing. As a consequence

I used those in the system when I had to, and left things at that for quite a while.

However, after using a few common and standard examples of both of these in my general coding, over time,

I started to change my mind. I could see how easy they were to use, and how useful some of them were to me.

Very recently I decided it was time for me to explore the depths of the STATIC class in general, and now

I consider the whole STATIC 'thing' a great aid to the developer's toolkit, or armoury. We can quite often use

a STATIC approach to improve or code, and general solution style.

In this section on STATIC 'everything', I will demonstrate what is going on when we use the individual parts of

a static class.

First of all however, let us look at some System static methods and properties we may well have met by now.

A Good Start :

Basically, it is a good idea to think of STATIC methods as Functions. The functions I used frequently in VO

can be made into STATIC methods. These can be used directly in our coding.

In fact some STATIC properties may also be thought of as functions in the old world, but also we could think

of them as GLOBAL variables. It all depends where you declare your class containing the STATIC methods.

When we use the System static methods and properties, these are Global, it is our own that we need to

carefully scope, depending on where we wish them to be used.

Of course when we say that system static classes are global - that is provided that we code the namespace

correctly at the head of the appropriate PRG files, and that the system assembly is included in the References

section of the Solution Explorer pane.

A Big Difference :

Quite simply, another major difference when using Static methods is that we do not need to instantiate.

Well, actually it goes further than that - we must not instantiate, as this will cause an error at compile time.

[ *** Note *** so the consequence is we can't use SELF within the code syntax for STATIC stuff. ]

Study the following image :-

====================================================================================

And just to confirm the point, here is another good example using the System.Math class.

Study the image below :-

============================================================================

Returning to the File class.

We will see in the next subsection many details about the File class in the 'System.IO' namespace.

For now it is enough to realise that it is a static class and so can't be used in instantiation. So to use

the static method 'File.Exists()' we must just use it 'as is' - no instantiation, allowed or required.

Similarly, when we have a class which may be used with instantiation, but within it there exists a method

which is static, we can't instantiate either, see below for an example :-

==================================================================================

We can't use the 'Now()' method with an instance of the structure 'DateTime'. The above image

shows what happens.

We can improve this as shown below :-

=============================================================================================================

As you can see from the output messages, the above syntax is acceptable to the compiler.

Both the instantiated oDT1 variable, and the oDT2 assigned variable obey the syntax rules.

Notice that when we use code for static classes, methods and properties we use a point ' . ' and not a colon

as in the rest of dotNet Vulcan syntax. Take care with this until you become familiar. It does get quite easy

after a short while.

My Tip - Points or Colons :

Let us now discuss another 'thorny' issue which hits any guy coming to dotNet and Vulcan - not just those

moving from Visual Objects.

When do we use a colon between our Namespace/Class/Method, and when do we use a point ?

My own rule of thumb is that if I am using something already existing in a namespace / assembly and I am

NOT instantiating, then I use a point - just as I would in C# for example.

However, if I am making a instance in my VN2 code, and then using this object, the colon is the thing to use.

Following this rule of thumb of mine, if I use either a STATIC class from the system or one of my own making,

then it is a point which must be used - below are two examples of this :-

=======================================================================================

The class 'SQL_my_funcs' is one of my own making - we will see a lot of this later in this chapter.

In the code above I am using both a static method and a static property - lines 143 and 150.

In both cases I use the 'point' even though these are my 'stuff'. The colon is NOT used because I

have not instantiated. I like to think that once instantiation has taken place then the 'thing' belongs

to Vulcan.net and as such needs it colon syntax.

The above explanation may not be technically based, but it does serve me well while coding in VN2.

Let us move on to the next section and we can deal with some of the above issues in more detail as

things unfurl, and as we start to understand more.

In the next subsection we will look at some system STATICs in action. In the subsection after that we

will start to make our own.

See you in the next section .......

PHH - 7th. January 2011.


STATIC methods, properties and fields

In this subsection we will start to look at STATIC classes, methods, and properties which are available

to us in the system namespaces.

Let us start with a simple, but commonly used method, that called 'File.Exists'. This is a method

called 'Exists()' in the class called 'File' which is in the 'System.IO' namespace.

Study below the relevant line (411) and the syntax used :-

=========================================================================================

Notice that we use the 'point' and not the colon, and that no instantiation takes place. The method is just

used directly.

If we look at the next image we see that the intellisense provides quite a lot of information for us :-

================================================================================================================

The context message says that the static method will return a Boolean, or logic.

Notice also that we can see a lot of other useful stuff for file management, like Copy and Delete.

Let us stand back a few paces, and see what the SDK docs have to say about the Namespace and the Class itself :-

[ note the File entry is the last one, right at the bottom ... ]

==============================================================================================================================================

Having clicked on the File hot link we get more detailed information on the methods - and other things too :-

=========================================================================================================================================

*** NOTE *** The red 'S' symbol / icon indicates that the method is a STATIC one.

This is commonly used across all the SDK docs.

Here are the rest of these :-

========================================================================================================================================

*** Note *** All the methods in the File class are STATIC methods. There is nothing else to this class.

[ Just 37 static methods to aid our development and coding - no properties, no constructor, or anything else. ]

Moving on to another commonly used coding area, that of DateTime.

'DateTime' is a structure, but it has a Constructor. Since we as coders can use the Class itself it is not a STATIC class.

However, do realise that static classes can have Constructors which are used internally by the static class itself.

Although the 'DateTime' structure can be instantiated, it does have some methods and properties which are STATIC.

Below is shown one of 11 overloads of the Constructor - we are instantiating with Year, Month and Day :-

=====================================================================================================

Here is a VERY commonly used STATIC property :-

=========================================================================================================================

*** Note *** I think this is an error on part of Microsoft, It is not a Class but a Structure !?

So the above is what we do if we want a 'DateTime' (DT) of this instance in time, both the date and the time parts

of the DT.

In the image shown below there is also shown a 'Today' static property which returns zeros for hour, minute, second etc.

after the date values of Year, Month, Day :-

==============================================================================================================================================================

You may have noticed that there are two STATIC fields which return maximum and minimum values.

I think the difference in Fields and Properties is that the value of fields is fixed and does not change during

the lifetime of the app, but Now and Today are variable and do change.

If this rule of thumb of mine is true then we would expect the values of Pi and 'e' to be fields also.

Have a look at what we see in the Math class to confirm our ideas :-

============================================================================================================================

Pi and 'e' are fixed values, constants of mathematics.

Back to the 'DateTime' structure :

Notice in the image shown below that we have some STATIC methods too for the 'DateTime' structure :-

========================================================================================================================================

If we look at the above image, we can see that there are a couple of methods which may well be useful to most guys, that of 'Compare',

and that of 'IsLeapYear', as well as 'Parse' too.

As well as STATIC methods and properties, it is possible to have static Fields and Operators as shown below :-

[ Remember that this DT structure is not a STATIC one, and can be instantiated. ]

=======================================================================================================================================

We have already made some basic observations about Fields, so let us now concentrate on the Operators.

Quite simply, the eight defined operators above mean that we can use symbols like +, -, >, <, >=, <=, <>, and = as

long at the types on either side of the operators are correct.

So for example, for + and - we must involve a DateTime and a 'TimeSpan', and the result has to be another DateTime.

So if we look at the SDK sample C# code we see :-

======================================================================================================

And in Vulcan syntax we have the following :-

============================================================================================================

WARNING - Take Care !!! :

On line 168, if we neglect to add the 'as TimeSpan' then the Vulcan compiler seems to make it a USUAL.

This does not fulfill the Operator requirements and so we see the following at runtime, as the syntax is NOT syntactically

incorrect :-

============================================================================================================================================================

In my tests I also noticed that the Try/Catch did not trap this, presumably because it is happening outside of the dotNet

'sphere' of the underlying system.

Message to me is clear, keep away from USUALs and strongly type everything.

Further Care :

When we use the operators + and - we normally expect the order of the values to NOT matter. However this

is not true for how they are defined for 'DateTime'.

Checkout the following image, of what happens when we reverse our above order :-

==============================================================================================

It would appear that for the operators to apply they need to follow a DT value, for which they are defined.

The other values involved depend on the type of operator.

The other seven operators are pretty straightforward, as long as you get the correct data types in your statements,

and also in the correct logical order.

Summary :

As developer / coders, we use STATIC methods and properties as we would functions in other languages, and as

most of us did in VO.

We can use static methods, properties and fields from both System defined classes and structures, as well as those

of our own making.

A non-static class can have static methods and properties, and may be instantiated too.

Static classes and structures may have Constructors - which are used internally.

Where Now ? :

It would seem that we are in a position to try and make our own first examples of STATIC 'stuff', and to get a

feel for what can be done to make our coding life more easy, and possibly better structured too.

Eventually, we will see how to make class libraries of our STATIC classes, so that they may be used in many

of our different solutions - 'off the peg' so to speak.

See you in the next subsection where we get our hands dirty .................

PHH - 21st. August 2011.


Defining our own STATIC classes

In this subsection we will create our own STATIC class, Methods, Properties, and a few other bits

as well.

We will get a good 'feel' for what is going on.

Possible Problem ;-))

Just a slight warning ! Like all things we try to demonstrate in a simple fashion, the quality and relevance of

the example is often 'suspect' and a bit meaningless.

Our first demo example here is probably such a case. Unfortunately, if we make it more relevant it becomes too

complex to early.

So if we wish to have a clear sample to study, we need to be aware that it is a bit 'Micky Mouse' as they say ;-0))

Where to Start ? :

Instead of introducing to a standard class, parts which are STATIC, and building up to a complete STATIC class,

I think it makes more sense to start with a full STATIC class, and work the other way.

To my mind we see quicker and easier what causes what, when we do a full class first.

Let us call our class 'LogDetails'.

In order to do our design, creation and testing we need a simple solution and project - as seen below :-

==============================================================================================================================================================================

To our newly made WPF app I have added a new folder and beneath that a file (PRG) to take the code for

our new STATIC class.

Study the following image :-

==============================================================================================================================

Well, obviously we need to make a few changes to this 'stub' code from the VS integration.

So after some thought and typing I have made a start to our little example, and here is how it runs - see below :-

=======================================================================================================

In the above image take special note of line 30, as this is the instruction which accesses the newly made class.

I will now show you the code of what we have, and which produced the above result.

Study the following code very thoroughly, as a few new things will be found in the syntax :-

=========================================================================

The above class was placed in the "VODCeNotesDemos" namespace.

Before we move on, let us explore the possibilities of removing the class name from before the method name

of any static method used throughout the other static methods and properties, and the constructor itself.

Take a look at the following image :-

=================================================================================================================

Although we have to use the class name in code outside the STATIC class, if we have the class defined as STATIC we

can drop the need to do so in code within the class methods and properties.

This topic will have to be revisited by us when we come to having static methods in an 'open' or non-static class. One

that may be instantiated. For now lets move on to examine the role of the Constructor.

What Part does the Constructor Play ? :

Let us look again at the calling code in the Initialize method of our new WPF form applet.

Study this image below :-

=================================================================

As we said previously, line 30 is the only place from which the static class is referred to.

If we have not done any instantiation, then how does the Constructor of the STATIC class get involved ?

It would appear that the answer is quite simple - for a STATIC class the first time any element of it is accessed by

code, the constructor is called, and any values setup within it are therefore 'set' for future use.

Yes, that means all static fields keep their properties 'as is' for the lifetime of the application, or until they are changed

by code accessing related properties and/or methods.

*** Note *** I think it 'taken as read' that the constructor has only one overload, and that takes no parameters !!

Thus it may be called by the system whenever it needs to do so.

Persistence of Values within the Class :

Let us explore this persistence by doing another simple test - see details in the image below :-

==============================================================================================

And now exploring using the VAT increasing method from outside of the STATIC class itself we see that the

value of the 'nVAT' field must have persisted from one use of the class to another. See the following image :-

=================================================================================================

So at this point I hear a number of you shouting - "Global Variables" - and this is true, we do have a tidy

way to afford ourselves the possible luxury of variables which are available across the application, or at

least across a number of WPF form classes.

We can see a good example of this later in the subsection on 'SQL_my_funcs' where I wish to have methods

and properties (and fields) available for ADO.NET use with my calls to the SQL database. Using a static

approach to this makes my life as a developer a LOT easier ;-) And I like that.

Existing Issues :

At the moment I have an issue which may be put to rights when I consult colleagues. At the moment even Alwyn's

book does not give me the answers :((

I see in the DateTime structure that there are two fields which are both read-only, and I think there are a couple more

in the Math class.

I do not know how to make my Fields publicly available (outside of the class) and yet not allow them to be changed

from outside.

I have tried the modifier term 'PROTECTED' and this is not what we require - see below :-

===========================================================================================

And the code used for this :-

========================================================================

"Watch this space !"

I will now chew Alwyn's ear ;-0))

Answers and Reasons :

It was there in 'the Book' after all, and Alwyn kindly pointed me to it.

In the following image we have public access to a fixed value field, and one that can't be overwritten

from outside of the class by any other - user or developer.

Study what we get at runtime :-

=========================================================================================

In the image above the code is using a field called 'RootTwo'.

Let us now view the code to see how I set up the class and the value for the 'Read Only' field :-

=========================================================================

Okay then, two steps forward and one step back ;-)

Firstly, we seem to have a fixed value field (here defined as CONST) which returns the correct value from outside of

the class itself.

If we do a test to see if we can change this value we get something like the following :-

=====================================================================================

This above output would suggest that we have done the right thing, and that we have a Read Only field.

However, the backwards step is that we have to have a value which exists at compile time. See the image

shown below for an example of the problem / issue.

=================================================================================

And at compile time the STATIC dotNet class cant' be used to provide the fixed value.

Unless we can find some way around this then we will be limited in at least the flexibility of such a way of providing

a Read Only fields - STATIC or otherwise.

Once again I will take advice. I don't think "the Book" can help ou this time as I have read all the related pages.

Keep watching this space.

STATIC Locals :

This actually may have more relevance to the next subsection, where we study a class which is not static but

which has some static methods and/or properties.

Make sure you visit the next section to see how field data may be persisted from one instance of a non-static class to

another, since we can already do this through STATIC fields in our STATIC classes, the relevance seems to be more

with non-static classes, and non-static methods.

A more Complex Example of a STATIC class :

Before we leave this section let me say that soon in this section we will see a full and 'proper' example of a STATIC class.

The class will be completely our own making, and will do a really useful job. It will allow us to use ADO.NET to access

our data in SQL tables, in an easy to code manner. We can then make sure this class is available for use anywhere in

our application.

It took me some years to realise that a Static Class was the way to go, but now I am there, it does feel right ;-0))

Probably time to move on to the next subsection, so see you there .........

PHH - 21st. August 2011.


Classes with mixed static/non-static members

Remember from discussions in earlier subsections, there are a few System classes which are of

a 'mixed' type. By a 'mixed' type I mean that the class itself is non-static, and that it has a few static

methods as well as non-static ones, and the same probably goes for the mix of properties, and possibly

fields too.

It is really the methods and properties which we will usually refer to when we talk of 'mixed' type.

Once again the example we will create and test is not a very clever, or useful one, but we can keep it

simple and short in lines of code, so that we can see clearly what is going on.

Before we look at the class and all of the supporting code, let us look at the result at runtime :-

=================================================================================================

So we see above we can use the un-instantiated form of the syntax, and all works OK.

After seeing the code, we can try to instantiate as well.

First study the code as see below :-

================================================================================

OK then guys, time for the big test - instantiation - see below for the image of the results :-

=============================================================================================================================

Well, the intellisense looks promising, so what do we get when we complete the code and run the test app ?

Study the full details carefully in the image below :-

=============================================================================================

Yes, we seem to be able to use both modes - instantiation and direct STATIC use.

You may notice when studying the class code VERY carefully, that I have tried to mix all the various ways of

defining methods and properties, as well as fields.

It is impossible to get a meaningful example, so you will just have to live with the best 'silly' idea I could come up with ;-)

You will see some use of the 'self' qualifier in the code, as well as the static syntax of 'Class.Member' too.

I have to say that the mixed form class is by far the most difficult to follow and even create. We are mixing two separate

approaches and although they work when coded correctly, they can be hard to understand for new guys coming to

STATIC 'stuff' for the first time.

STATIC locals :

Reading AJB's book I see that there is yet another type of variable which persists for the lifetime of the

application, and this is a local qualified / modified with a 'STATIC'.

The following tests will show that if within any method we use such a 'static local' the value of this will remain for the lifetime

of the app itself. This is even for non-static classes with non-static methods.

In the sample of code shown in the image below, we instantiate three MixedDetails objects, one after another. However,

the value of the count seems to be remembered from previous instantiations as the value keeps on increasing at each

and every new instantiation.

Study the code and other details very carefully :-

=======================================================================================================

[ The above image was generated with a bit of screen capture 'overlay' but is quite genuine otherwise! ]

Now then, what does the code look like which causes this behaviour ?

Notice in the following few lines of code, that if no 'record' of a previous value was stored and remembered, then

each time we instantiate a new object of the class, we ought to get a ' 1 ' as the output :-

=====================================================================================================

Access to Members (Properties, Methods, etc..) :

As I have stated previously, the mixing of STATIC and NON-STATIC features in one class may be very useful,

BUT, it is more difficult to understand initially, certainly for new guys.

Let us get straight one aspect first, and that is how to code our solutions so that we use both types from one of

these 'mixed' classes - either system or our own.

Any static member is accessed without instantiation and by using the "Namespace.Class.Member" notation as

shown below :-

=========================================================================================================================================

In the lines numbered 58, 61, 64, 68/69 we are using the syntax of Class.Member and are dropping the namespace

as we are within that namespace, so it is superfluous.

The above example shows all three types of access to static members, Method, Property and Field.

However, we do have a slight complication which is shown in lines 68 & 69. Here we have two acceptable ways of

finding the character value of the data item - new guys may finf that on line 69 hardest to get to grips with.

I like the braces around the field data item, as I know this is an object and also one made in our Vulcan code - therefore

it may be used by us and the colon used for any further stuff we require.

Note that trying to use the 'point' notation for the 'ToString()' does not work, as shown below :-

====================================================================================================

I suggest you don't worry about this, but make a few small samples and test them for yourself. It will

all start to get a bit easier then, and you will soon get the hang of it - this is what happened to me !

Non-Static Access :

This is simpler, as it is really just the same as all over classes which can be instantiated.

Any confusion may arise when we mix the 'modes' of use - however, don't let the fact that instantiation has occurred

affect your judgement. Even if you have an instantiated object, to get to the STATIC members of that class you

keep to the 'Namespace.Class.Member' notation as seen in the working example in the image below :-

========================================================================

In the above code we have an instantiated object 'oMD', of class 'MixedDetails', but this does not change the way

we have to code line 42. This would work even if no 'oMD' existed at all.

A system example of this type of mixed mode class/structure is 'DateTime'. We should be familiar by now with

the very useful static property of 'DateTime.Now', and yet we instantiate other DateTime objects quite happily elsewhere

in our own code.

Just remember that we are effectively dealing with Functions (of old) and as such a function related to the class in focus

is simply placed there for our convenience. Obviously a 'function' to give us the date of 'Today' needs to be with other

'DateTime' stuff.

On the other hand, the System.Math class is completely just a bunch of single and separate 'functions', and nothing else.

Actually, after a short while, all of this 'stuff' on STATIC 'things' becomes quite straightforward and also VERY useful to us

developers and coder guys.

*** Note *** I still need to find out if we can make a STATIC filed which is read only - like 'PI' in Math, and 'MinValue' in DateTime.

>>> correction to above, see the relevant part of the previous subsection - now filled out with an example <<<

PHH - 21st. August 2011.


Globally available data objects

my personal comments - lazy way to handle objects and scoping

when don't know how to pass one form to a child - whole object etc.

old programming style - declare at start all variables to be used - or not - as the case maybe.

however, we do have need for some widely available data - stuff to do with user, company and logons

as well as maybe printing, and certainly data source access.

Global use of anything needs to be well thought out and carefully used - simple rule ? Keep to a minimum

Often in the past programmers grab for a global when they were in trouble, and to make a quick fix to scoping problems.

This is NOT the way to use STATIC elements / members.

selecting a printer ?

PHH - 7th. January 2011.


Example 1, the 'SQL_my_funcs' class

Background to making the Class :

What I am about to say is all to do with my personal experience of coming to SQL for the first time, and at that

same moment starting to use ADO.NET in my Vulcan dotNet code.

I had not used any other way to access data in an SQL database (DB). So my experience of querying the DB was

solely based on my experiences with the Management Studio 'shell'.

The early advice I was given by colleagues was to conform to the 'disconnected model' or whatever the phrase is,

meaning that when existing data was required, or storage of new data needed, a connection to the server be made, and when

the transaction was complete the connection be removed, broken, or we say we disconnect from the server.

So I started to write methods in my business solutions which looked like the following.

Study the code carefully, and try and find the 'parts' to the code 'chunk' :-

============================================================================================================

Yes, the code in the above method is a bit longwinded, even verbose, but it does do the job quite nicely.

Basically we have part one which tries to connect to the server with the 'set' connection string, and also create

a command object.

The second 'part' or section sets the query script, and the third sends this to the SQL Server with the method of

the command class entitled 'ExecuteReader'.

Obviously the return dataset is associated with the waiting DataReader object.

The collapsed section simply takes the first row in the returned set and builds a Customer object from it. I will show

this section expanded in the image below :-

====================================================================================

Finally, after the above code, we then close the connection to the SQL server.

This may be a bit of an eyeful to take in if you are not used to SQL stuff. And I agree with the sentiment

whole heartedly.

This is one of the reasons I was motivated to try and come up with a better solution, and one which was not complicated,

or difficult to use in my new apps.

Let us now look at possibly better way to code a method in which I am trying to query the server and get an returned

data set.

Study the following code thoroughly :-

====================================================================================

This time, as illustrated in the above image, we form a query and then give it to a STATIC method to be sent to the

server. From this method is returned a DataSet, either filled with data or 'empty'.

So the process of forming a query and then processing the results from that, are covered in just a few lines, and the

number of the lines will depend on the complexity of the query.

This seems to be an improvement to me, and so we need to study the STATIC method which was used to do the work.

In the 'chunk' of code below we see that the opening of a connection (and its close) are handled along with the attempted

querying of the database.

I tend to think of this stuff as the 'messy' but necessary side of using SQL. Study the code used below :-

===================================================================================================================

By removing all the 'dirty' and necessary bits from the more business logic type of code, it is easier to see what it is

the we the developer is trying to do.

Hopefully, you will see a lot more of this sort of improvement in the later part of this subsection.

Where are we headed ? :

Well, ideally, I would like to make a ClassLibrary assembly ( a DLL to you and me).

This library I would like to be able to add to my Reference section in the SE (solution explorer).

Then with the suitable '#USING' at the head of my PRG's I will be able to use my STATIC methods, Properties, and

any Fields too.

With a fixed Class in my library, I can then get very familiar with using the members in the same way each time I code a

new solution.

Thinking Further ahead, I would like to make a separate STATIC class which is specific to the business objects I use with

the SQL tables - so for example 'Customer' will have a method or two to access a row, or fuller dataset, as well as writing

a new row, and updating a targeted row.

The plan would be to have a short set of members (methods and properties) for each table to business object relationship

or association.

So we may expect to see something like the following :-

==========================================================================================================================

Unfortunately, at the moment I have this mixed in with the 'SQL_my_funcs' class, so I need to change that ASAP.

More on the new Class later. Since it is related to a whole business solution, the Class ought to be named after the app

itself. So the above methods ought to appear in a Class called 'SmallTrader', since this is what has the Invoice, Customer,

Configuration, and Service Job objects. These are custom made business objects.

At the moment the 'Job' pair (or small set) of methods has not been made. You will see more of all this later, for now, lets

move on to our next sub-topic.

Size of the Class :

At the moment, the 'SQL_my_funcs' class has 22 methods, 7 properties, and 16 fields. However since this is very much

a 'work in progress' class, I expect these numbers to increase.

The size of the class, although not huge by any stretch of the imagination, does make it difficult to present it in these eNotes

and for readers to easily focus on what is being discussed. This is just due to the complexity of dotNet and we will have to both

live with it as well as do our best ;-)

Before we move on, let us see a collapsed section of the class code - that from middle to the end. check this out :-

==============================================================================================================================

Let us now move on to see more on what the class is and how to use it.

About the Class and its Use :

Lets do a quick summary of where we have got to - we need a STATIC class which can be used from anywhere in our

applications, and which is easy to use, as well as being VERY like using SQL scripting with management Studio.

The ADO.NET stuff will be hidden from view after being thoroughly tested.

The resulting class members will then allow us to right the business logic methods in a more succinct and easy to

read manner.

The consequence of this set of requirements is that we must ensure we cover all the possible ADO.NET 'stuff'.

We must not create a class that just solves those issues and problems for the 'Small Trader' app.

Okay then, let us first tackle the issue of a 'config' file on disk, which when read will provide us with the fixed

details for the MS SQL server and the connection, as well as the name of the database too.

My usual style for this situation is to have a 'config' file residing in the same directory (or folder) that the app's

EXE file is in.

Below is a real example of this :-

==============================================================================================

And we can open the file in Notepad to see the contents - the data which makes connection possible.

Look out for the necessary details - see below :-

In the image shown above the XML tags help to indicate just what is what.

So the connection string itself is :-

Data Source=BEST-V64QUAD\SQL_DEV_08_R2;AttachDbFilename=C:\Users\BEST\SmallTrader.mdf;;Integrated Security=True;Connect Timeout=0;

The server is :-

BEST-V64QUAD\SQL_DEV_08_R2

and the database is named :-

C:\Users\BEST\SmallTrader.mdf

We now need to see how the relevant method in our STATIC class reads this file and sets the class static fields and properties.

Here is the code for reading the config file - study carefully :-

===========================================================================================================================

What is happening in the above code ?

It is quite simply really, even if you are unfamiliar with serializing objects to disk / file, you can at least appreciate what

is being done.

The folder (or directory) is found which holds the application file, and since I always place the config file there, we try to

see if it is available.

All being well (usual behaviour) the file is found and the contents read in by a 'reader' then deserialized and cast into a

'ConnectionDetails' object. This is an object of a class I made way back to go along with an interactive 'Connection' screen

which I first made with WinForms.

To get your head into this, lets look together at the coding of the class - study carefully the following image :-

============================================================

For those that are not yet aware, serializing an existing object in memory, to a file on disk, is very easy and takes few lines

of code. Paul Piko first pushed me in this direction years back - thanks again Paul, yet another good tip from down under.

Since I like working with objects, all the time in my modern way of coding (I was not always a convert to "objects everywhere"),

doing things this way has a lot to offer. Not least being able to bind the object to a WPF 'connection' form if I so choose.

When designing and making a class which is serializable we need to make sure that it as a Constructor which takes no

parameters - and 'empty' constructor in simple mans terms.

I will put more on Serializable Classes in the relevant section of these eNotes. See "Section H", Where you also see the

In order to help new guys to Serialization, and using 'config' files on disk, it seems sensible to show the basis of a new STATIC

method I will develop and test more thoroughly, but as it is it serves well to demonstrate the process involved.

See below the image of the code to serialize a 'ConnectioDetails' object to disk file :-

==================================================================================

Now that we have a deeper interest in static classes, we may like to notice that in the above code we have

used two 'system' static methods - these are in line 442 and 445.

Also notice that my old code, which is being re-used from the "BEST company" app, has some lines commented

(457 & 458) because we have been advised to let the garbage collector for the CLR have its head, and NOT to mess

with things for tidying up memory. So lines like this need to be a 'thing of the past'.

Setting up Our STATIC Class :

In passing, you may notice that the value of the application directory could be made a class variable, and its value

determined once, in the Constructor so that the first time any member of the class is accessed it will be set for

future use.

Remember that my new class 'SQL_my_funcs' is still in the making, and I am improving it as I write these notes, so

why don't we take this opportunity to improve things ? Let us change the Constructor to be as shown below :-

========================================================================================

The above code changes mean that as soon as one member of the STATIC class is accessed, the Constructor will

be run that time only (as it usually does) and then the fields with directories and file names will be populated, ready

for use by the other members.

Let us now see how the two methods have been cleaned up and tidied by these changes - see images below :-

=========================================================================================

In the newly cleaned code above you may also notice that I have changed the old code from VIDE days (years ago)

so that the upper case in command words is now all lower case. Quite often I leave stuff just as it is so I know

which lines of code are old and been reused, and which are new - a history trail if you like ;-0))

And now the Object to Disk Method :-

=================================================================================

I think you will agree that it is cleaner and more tidy !

The only other thing to notice and discuss is the 'MyMessage' STATIC member. This is shown

below and allows the messages shown in this fashion to be toggled on or off. This applies to the whole lot

in the application done like this. It is VERY helpful when developing and testing - here it is :-

================================================================================

Taking this Class Further :

Now that we have seen the basic idea of our real and larger STATIC class, I think we need to split the rest of

the eNotes and explanation into subsections, so things are easier to manage for both reader and writer ;-)

I will try and use section heading which reflect the function of the STATIC members.

See you in the first of these subsections, I will call it "Connecting" ...........

PHH - 22nd. August 2011.


Full working code

//----- SQL_my_funcs.prg -----

//----- created August 2nd. 2011 for VODC in November -----

//----- for "Small Trader" app. - by PHH -----

#using System

#USING System.IO

#using System.Data

#using System.Xml.Serialization

#using System.Data.Common

#using System.Collections.Generic

#using System.Collections.ObjectModel

#using System.Collections

#using System.Windows

//------------------------------------

BEGIN NAMESPACE SmallTrader

STATIC CLASS SQL_my_funcs

#region {.. class variables ..}

static public lConnectionTestedOK as Boolean

static private cFileXML as string

static private cAppDir as string

static private cWork as string

#endregion

#region {.. other variables of the class ..}

//----------------------------------------------

static public oConnDetails AS ConnectionDetails

static private oDFactory AS DbProviderFactory

static private oDConn AS DbConnection

static private cStrConn as string

//----------------------------------------------

#endregion

#region {.. more class variables ..}

static private cSource as string

static private cAttach as string

static private cSecurity as string

static private cTime as string

static private cInst as string

static private cDBfilePath as string

static private lConnected as Boolean

static private lShowMessages as Boolean

#endregion

#region {.. and more ..}

//----------------------------------------------

static private nErrorCode as int

static private cErrorMessage as string

static private cErrorLanguage as string

#endregion

STATIC CONSTRUCTOR()

nErrorCode := 0

cErrorMessage := "No Error"

cErrorLanguage := "1033"

//-----------------------

lConnected := false

lShowMessages := false

//lShowMessages := true

//-------------------------------------------------------

cAppDir := System.IO.Directory.GetCurrentDirectory() +"\"

cWork := System.IO.Directory.GetCurrentDirectory()

cFileXML := cAppDir +"ConnectionDetails.xml"

SQL_my_funcs.ReadSerialXmlFile()

RETURN

//========================= [ Backup Database ] ===========================

static method DoBasicBackup() as Boolean

local lResult := true as Boolean

local oF as DirectoryInfo

oF := Directory.CreateDirectory("C:\BackupSQL")

local cName2Use as string

cName2Use := oF:FullName+"\StaticBasic.bak"

MessageBox.Show(cName2Use,"Will be Used :")

local cScript as string

cScript := "BACKUP DATABASE [" + SQL_my_funcs.DBfilePath + "] "

cScript += "TO DISK = '"+cName2Use+"' "

cScript += "WITH FORMAT, "

cScript += "NAME = 'Full Backup of SmallTrader' "

MessageBox.Show(cScript, "Back-Up :")

try

SQL_my_funcs.KeepOpenSendNonQueryScript(cScript)

MessageBox.Show("Query has been sent .... ","After BU :")

catch oEx as SystemException

MessageBox.Show(oEx:Message, "ERROR :")

end try

local nError as int

nError := SQL_my_funcs.GetErrorCodeFromServer()

if nError = 0

MessageBox.Show("No apparent error","Error ?")

else

MessageBox.Show("Error number :- "+nError:ToString(),"Error ?")

endif

//MessageBox.Show("Just before force disconnect ....","TEST :")

SQL_my_funcs.DoForceDisconnect()

RETURN lResult

//========================= [ End Backup] ===========================

//========================= [ Error Object ] ==============================

static method SaveErrorToSQLtable(oE as MyError) as void

SQL_my_funcs.ChangeErrorRecord(oE, RowChangeModeSQL.Insert)

return

static method Error_FillTypedList(oE as MyError) as List<SQLfieldSpec>

local aSFS as List<SQLfieldSpec>

aSFS := List<SQLfieldSpec>{}

aSFS:Add(SQLfieldSpec{"[UnRef]", "@ur", DbType.Int32, oE:UnRef})

aSFS:Add(SQLfieldSpec{"[Occurred]", "@occ", DbType.DateTime, oE:Occurred})

aSFS:Add(SQLfieldSpec{"[Method_Name]", "@mn", DbType.String, oE:MethodName})

aSFS:Add(SQLfieldSpec{"[Object_Class]", "@obj", DbType.String, oE:ObjectClass})

aSFS:Add(SQLfieldSpec{"[Namespace]", "@nsp", DbType.String, oE:Namespace})

aSFS:Add(SQLfieldSpec{"[Operation]", "@op", DbType.String, oE:Operation})

aSFS:Add(SQLfieldSpec{"[Main_Message]", "@mm", DbType.String, oE:MainMessage})

aSFS:Add(SQLfieldSpec{"[Inner_Message]", "@im", DbType.String, oE:InnerMessage})

aSFS:Add(SQLfieldSpec{"[SQL_Error_Text]", "@set", DbType.String, oE:SQLerrorText})

aSFS:Add(SQLfieldSpec{"[SQL_Error_Code]", "@sec", DbType.Int32, oE:SQLerrorCode})

return aSFS

static method ChangeErrorRecord(oE as MyError, eMode as RowChangeModeSQL) as Boolean

local lResult := false as Boolean

if eMode = RowChangeModeSQL.Delete

MessageBox.Show("Wrong mode requested >> DELETE !?","Change MODE :")

return lResult

endif

local cTable := "[MyErrors]" as string //----- change for other Tables -----

//==========================================================================

local aSFS as List<SQLfieldSpec>

aSFS := SQL_my_funcs.Error_FillTypedList(oE)

//--------------------------------------------

//MessageBox.Show("Just about to try the STATIC func!","Testing :")

lResult := SQL_my_funcs.MakeBusinessObjectDetail(aSFS, cTable, eMode)

//MessageBox.Show("Just after MBO - try the STATIC func!"+lResult:ToString(),"Testing :")

return lResult

//========================= [ End Error Object ] ===========================================

//========================= [ Message for Exceptions ] =============================

static method MyInnerMessage(oEX as Exception) as string

local cText := "" as String

if oEX:InnerException <> NULL

cText := oEX:InnerException:Message+CRLF

cText += "Exception Type : "+oEX:InnerException:GetType():Fullname+CRLF

cText += "Source : "+oEX:InnerException:Source+CRLF

cText += oEX:InnerException:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oEX:InnerException:TargetSite:ToString()+CRLF

endif

//MessageBox.Show(cText)

return cText

static method MyMainMessage(oEX as Exception) as string

local cText as String

cText := oEX:Message+CRLF

cText += "Exception Type : "+oEX:GetType():Fullname+CRLF

cText += "Source : "+oEX:Source+CRLF

cText += oEX:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oEX:TargetSite:ToString()+CRLF

//MessageBox.Show(cText)

return cText

//------------------- And Another Overload --------------------

static method MyInnerMessage(oDBX as DbException) as string

local cText := "" as String

if oDBX:InnerException <> NULL

cText := oDBX:InnerException:Message+CRLF

cText += "Exception Type : "+oDBX:InnerException:GetType():Fullname+CRLF

cText += "Source : "+oDBX:InnerException:Source+CRLF

cText += oDBX:InnerException:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oDBX:InnerException:TargetSite:ToString()+CRLF

endif

//MessageBox.Show(cText)

return cText

static method MyMainMessage(oDBX as DbException) as string

local cText as String

cText := oDBX:Message+CRLF

cText += "Exception Type : "+oDBX:GetType():Fullname+CRLF

cText += "Source : "+oDBX:Source+CRLF

cText += oDBX:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oDBX:TargetSite:ToString()+CRLF

//MessageBox.Show(cText)

return cText

//--------------------------------------------------------------

//========================= [ End Exception Messages ] =============================

static method MakeBusinessObjectDetail(aSFS as List<SQLfieldSpec>, cTable as string, eMode as RowChangeModeSQL) as Boolean

local lResult := false as Boolean

local cText, cHeading as string

if eMode = RowChangeModeSQL.Update

local cFieldSymbolPairs := "" as string

SQL_my_funcs.CreateFieldSymbolPairs(cFieldSymbolPairs,aSFS)

cText := "UPDATE "+cTable+" SET "+cFieldSymbolPairs+" WHERE [UnRef] = @ur ;"

cHeading := "Updating :"

endif

if eMode = RowChangeModeSQL.Insert

local cFields := "" as string

local cSymbols := "" as string

SQL_my_funcs.CreateFieldsSymbols(cFields,cSymbols,aSFS)

cText := "INSERT INTO "+cTable+" ("+cFields+") VALUES ("+cSymbols+");"

cHeading := "Inserting :"

endif

//MessageBox.Show(cText,"Script :")

lResult := SQL_my_funcs.MakeBusinessObjectChange(aSFS, cText, cHeading)

return lResult

static method MakeBusinessObjectChange(aSFS as List<SQLfieldSpec>, cText as string, cHeading as string) as Boolean

local lResult := false as Boolean

//====================================================

SQL_my_funcs.DoSetConnectionString()

IF !SQL_my_funcs.DoTryToConnect()

RETURN lResult

ENDIF

//==========================

LOCAL oCommand AS DbCommand

oCommand := SQL_my_funcs.DFactory:CreateCommand()

oCommand:CommandText := cText

oCommand:CommandType := CommandType.Text

oCommand:Connection := SQL_my_funcs.DConn

//----------------------------------------------------

SQL_my_funcs.AllParamsSetAdd(oCommand, aSFS)

TRY //----- execute the insert statement -----

oCommand:ExecuteNonQuery()

lResult := true

CATCH DbEx AS DbException

MessageBox.Show(DbEx:Message, cHeading)

END TRY

SQL_my_funcs.DoForceDisconnect()

return lResult

static method CreateFieldSymbolPairs(cFieldSymbolPairs ref string, aSFS as List<SQLfieldSpec>) as void

local oLTE as IEnumerator

oLTE := aSFS:GetEnumerator()

local oSFS as SQLfieldSpec

local i := 0 as int

do while oLTE:MoveNext()

i +=1

if i > 1

cFieldSymbolPairs += ","

endif

oSFS := (SQLfieldSpec)oLTE:Current

cFieldSymbolPairs += oSFS:FieldName+"="+oSFS:@@Symbol

enddo

return

static method CreateFieldsSymbols(cFields ref string, cSymbols ref string, aSFS as List<SQLfieldSpec>) as void

local i := 0 as int

local oLTE as IEnumerator

oLTE := aSFS:GetEnumerator()

local oSFS as SQLfieldSpec

do while oLTE:MoveNext()

i +=1

if i > 1

cFields += ","

cSymbols += ","

endif

oSFS := (SQLfieldSpec)oLTE:Current

cFields += oSFS:FieldName

cSymbols += oSFS:@@Symbol

enddo

return

static method SetAddParam(oCommand AS DbCommand , oDbT as DbType, cSym as string, oV as object) as void

LOCAL oParam AS DbParameter

oParam := SQL_my_funcs.DFactory:CreateParameter()

oParam:ParameterName := cSym

oParam:DbType := oDbT

oParam:Value := oV

oCommand:Parameters:Add(oParam)

return

static method AllParamsSetAdd(oCommand AS DbCommand, aSFS as List<SQLfieldSpec>) as void

local oSFS := SQLfieldSpec{} as SQLfieldSpec

local oLTE := aSFS:GetEnumerator() as IEnumerator

oLTE:Reset()

do while oLTE:MoveNext()

oSFS := (SQLfieldSpec)oLTE:Current

SQL_my_funcs.SetAddParam(oCommand, oSFS:DbT, oSFS:@@Symbol, oSFS:Val)

enddo

return

static method ReadSerialXmlFile() as logic

local serial as XmlSerializer

local oSR as StreamReader

local cText, cHead as string

local lResult as Boolean

lResult := false

lConnectionTestedOK := false

if File.Exists(cFileXML)

cText := "Existing file will be read!"

cHead := "XML file :"

//----- use my STATIC toggled message system -----

MyMessages(cText,cHead)

//----- read object from XML file -----

serial := XmlSerializer{typeof(ConnectionDetails)}

oSR := StreamReader{cFileXML}

oConnDetails := (ConnectionDetails)serial:Deserialize(oSR)

oSR:Close()

//-------------------------------------

lResult := true

lConnectionTestedOK := true

//----- use my STATIC toggled message system -----

MyMessages(oConnDetails:ToString(),cHead)

else

cHead := "XML file :"

cText := "File can't be found !?"+CRLF

cText += "Go to Connection and create a string."+CRLF

MessageBox.Show(cText,cHead)

endif

return lResult

static method SerializationToFile() as void

local cText, cHead as string

if oConnDetails = NULL_object

cText := "NULL object ! - ConnectionDetails ..."

cHead := "My Error :"

//----- use my STATIC toggled message system -----

MyMessages(cText,cHead)

return

endif

local serial as XmlSerializer

local oSW as StreamWriter

if File.Exists(cFileXML)

cText := "File exists already!"

cHead := "XML file :"

MessageBox.Show(cText,cHead)

endif

//----- save object to XML file -----

serial := XmlSerializer{typeof(ConnectionDetails)}

oSW := StreamWriter{cFileXML}

serial:Serialize(oSW, oConnDetails)

oSW:Close()

return

static method SerializeOthersToDisk() as void

/*local cFile as string

cFile := "C:\Temp\customers.xml"

local oSW as StreamWriter

oSW := StreamWriter{cFile}

//----- Save object to XML file -----

local serial as XmlSerializer

local aTypes as Type[]

aTypes := Type[]{1}

aTypes[1] := typeof(Customer)

serial := XmlSerializer{typeof(ArrayList),aTypes}

serial:Serialize(oSW,self:alCustomers)

oSW:Close()*/

return

static method MyMessages(cB as string, cH as string) as void

if ShowMessages

MessageBox.Show(cB,cH)

endif

return

//======================== [ basic ADO.net & settings ] ==============================================

static METHOD DoSetConnectionString() AS VOID

IF String.IsNullOrEmpty(oConnDetails:cProvider)

MessageBox.Show("Provider string NOT available !","WARNING")

RETURN

ENDIF

//====================================================================

IF oConnDetails:cUserName:IndexOf("<") > -1 .OR. oConnDetails:cUserName:IndexOf(">") > -1

oConnDetails:cUserName := ""

ENDIF

IF oConnDetails:cPassWord:IndexOf("<") > -1 .OR. oConnDetails:cPassWord:IndexOf(">") > -1

oConnDetails:cPassWord := ""

ENDIF

//----- get the factory for the provider we require -----

oDFactory := DbProviderFactories.GetFactory("System.Data."+oConnDetails:cProvider)

//----- create a connection STRING ------

oDConn := oDFactory:CreateConnection()

//====================================================

cStrConn := ""

cSource := "Data Source="+oConnDetails:cServerName+";"

cAttach := "AttachDbFilename="+oConnDetails:cFilePath+";;"

//--------------------------------------------------------------

cDBfilePath := oConnDetails:cFilePath //----- for backup ! -----

//--------------------------------------------------------------

cSecurity := "Integrated Security="+oConnDetails:cSecurity+";"

cTime := "Connect Timeout="+oConnDetails:nTimeOut:ToString()+";"

//----- Willie said this would leave it always open if ZERO is supplied! -----

cInst := ""

cStrConn := cSource + cAttach + cSecurity + cTime +cInst

oDConn:ConnectionString := cStrConn

oConnDetails:cConnectionString := cStrConn

MyMessages(cStrConn,"My Connecting String :")

RETURN

static METHOD DoTryToConnect() AS LOGIC

LOCAL lResult AS Boolean

lResult := FALSE

lConnected := false

local cText := "" as string

TRY

oDConn:Open()

cText := "Vulcan.NET : Small Trader : << Connected! >>"

MyMessages(cText,"Try to Connect :")

lResult := TRUE

lConnected := true

CATCH DbEx AS DbException

MessageBox.Show(DbEx:Message)

END TRY

RETURN lResult

static METHOD DoForceDisconnect() AS VOID

LOCAL cText AS STRING

IF ! oDConn = NULL_OBJECT

oDConn:Close()

cText := oDConn:ConnectionString+" - State = "+ oDConn:State:ToString()

MyMessages(cText,"Disconnecting? :")

lConnected := false

else

cText := "Vulcan.NET : Small Trader : << Disconnected! >>"

MyMessages(cText,"Disconnecting? :")

ENDIF

RETURN

//========================= [ query ADO.net members ] ================================================

static method KeepOpenSendScalarQueryScript(cScript as string) as Object

local oResult as object

oResult := object{}

try

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch oEx as SystemException

MessageBox.Show(oEx:message,"Start of Scalar Query !")

end try

//----- getting a scalar quanity returned -----

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

oResult := oDComm:ExecuteScalar()

//----- need to cast in calling code section! -----

CATCH DbEx AS DbException

//SaveAndDisplayErrorDetails(DbEx,"Keep, Returned Data Object :")

MessageBox.Show(DbEx:Message,"Keep, Returned Data Object :")

END TRY

return oResult

static method KeepOpenSendNonQueryScript(cScript as string) as Boolean

local lResult as Boolean

lResult := false

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch DbEx AS DbException

MessageBox.Show(DbEx:message,"Start of Non Query !")

end try

//----- getting no data returned -----

oDComm:CommandType := CommandType.Text

TRY

LOCAL cStatement AS STRING

//----- use a terminator ; as recommended by Paul Nielsen -----

cStatement := cScript

oDComm:CommandText := cStatement

oDComm:CommandType := CommandType.Text

//----- execute the supplied statement -----

oDComm:ExecuteNonQuery()

lResult := true

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Keep-Non Query :")

/* local cText := "" as string

cText += "Error Code :- "+DbEx:ErrorCode:ToString()+CRLF

if !DbEx:InnerException = NULL_object

cText += "Inner Exception :- "+DbEx:InnerException:ToString()+CRLF

endif

cText += "Source :- "+DbEx:Source:ToString()+CRLF

cText += "Message :- "+DbEx:Message+CRLF

//cText += "Stack Trace :- "+DbEx:StackTrace+CRLF

MessageBox.Show(cText,"DbEx Details :")*/

//MessageBox.Show(DbEx:Message,"Non Query :")

END TRY

return lResult

//------------------------------------------------------------------------------------------------

static method SendNonQueryScript(cScript as string) as Boolean

local nError := -999 as int

cErrorMessage := ".. reset for use .."

//------------------------------------

local lResult as Boolean

lResult := false

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch DbEx AS DbException

MessageBox.Show(DbEx:message,"Start of Non Query !")

end try

//----- getting no data returned -----

//oDComm:CommandType := CommandType.Text

TRY

LOCAL cStatement := cScript AS STRING

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cStatement

oDComm:CommandType := CommandType.Text

//----- execute the supplied statement -----

oDComm:ExecuteNonQuery()

nError := 0

lResult := true

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Non Query :")

/* nError := GetErrorCodeFromServer()

nErrorCode := nError

//FindErrorMessage()

cErrorMessage := DbEx:Message

MessageBox.Show(nError:ToString(),"Code ?! :")

MessageBox.Show(DbEx:Message,"Non Query :") */

END TRY

SQL_my_funcs.DoForceDisconnect()

return lResult

static method SendDataSetQueryScript(cScript as string, cTableTitle as string) as DataSet

nErrorCode:= -999

cErrorMessage := ".. reset for use .."

//------------------------------------

local oDS_Result as DataSet

oDS_Result := DataSet{}

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

//----------------------------------

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

CATCH DbEx AS DbException

MessageBox.Show(DbEx:message,"Start of DataSet Query !")

end try

//----- getting a data set returned! -----

oDComm:CommandType := CommandType.Text

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

LOCAL oDA AS DbDataAdapter

oDA := oDFactory:CreateDataAdapter()

oDA:SelectCommand := oDComm

oDS_Result := DataSet{}

//----- execute the select statement -----

oDA:Fill(oDS_Result, cTableTitle)

nErrorCode := 0

cErrorMessage := "No error thrown"

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Error DataSet :")

END TRY

SQL_my_funcs.DoForceDisconnect()

return oDS_Result

static method SaveAndDisplayErrorDetails(DbEx AS DbException, cHead as string) as void

cErrorMessage := DbEx:Message //----- CARE with order, must be before next line -----

nErrorCode := GetErrorCodeFromServer()

if !lShowMessages

local cText := "" as string

cText += ErrorMessage+CRLF

cText += ErrorCode:ToString()+CRLF

MessageBox.Show(cText, cHead)

endif

#region {.. test code ..}

//---------------------------------------

//----- make oE from properties and fields -----

//as MyError, eMode as RowChangeModeSQL

/*local eMode := RowChangeModeSQL.Insert as RowChangeModeSQL

local oE := MyError{} as MyError*/

//ChangeErrorRecord(oE, eMode)

#endregion

return

static method SendReaderQueryScript(cScript as string) as DbDataReader

local nError := -999 as int

cErrorMessage := ".. reset for use .."

//------------------------------------

//----- using the DataReader approach as an alternative -----

//----- to the 'DataTable' one I like particularly -----

local oDr as DbDataReader

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

//----------------------------------

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

CATCH DbEx AS DbException

MessageBox.Show(DbEx:message,"Error DataReader :")

return NULL_OBJECT

end try

//----- getting a data set returned -----

oDComm:CommandType := CommandType.Text

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

oDr := oDComm:ExecuteReader()

//----- execute the select statement -----

nError := 0

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Error Datareader :")

/*MessageBox.Show(DbEx:Message,"Error DataReader :")

nError := GetErrorCodeFromServer()

MessageBox.Show(nError:ToString(),"Code ?! :")

nErrorCode := nError*/

//FindErrorMessage()

END TRY

//----- do NOT close the connection - needed Open by DataReader ! -----

return oDr

static method SendScalarQueryScript(cScript as string) as Object

local nError := -999 as int

cErrorMessage := ".. reset for use .."

//------------------------------------

local oResult as object

oResult := object{}

try //----- to Open a connection & make a command object -----

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch oEx as SystemException

MessageBox.Show(oEx:message,"Start of Scalar Query !")

end try

//----- now try getting a scalar quanity returned -----

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

oResult := oDComm:ExecuteScalar()

//----- need to cast in calling code section! -----

nError := 0

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Returned Data Object :")

END TRY

SQL_my_funcs.DoForceDisconnect()

return oResult

//------------------------------------------------------------------------

static method GetErrorCodeFromServer() as int

local nResult := -999 as int

local cScript as string

cScript := "SELECT @@Error;"

nResult := (int)SQL_my_funcs.KeepOpenSendScalarQueryScript(cScript)

if lShowMessages

if nResult = 0

MessageBox.Show("No apparent error ! ","Error Code :")

else

MessageBox.Show("Returned code is :- "+nResult:ToString(),"Error Code :")

endif

endif

return nResult

//================================================================================================

/*static method FindErrorMessage() as void

local cScript as string

cScript := "SELECT text FROM [master].[sys].[messages] where [message_id] = '"

cScript += ErrorCode:ToString() + "' and [language_id] = '"+cErrorLanguage+"' "

cErrorMessage := (string)SQL_my_funcs.KeepOpenSendScalarQueryScript(cScript)

return*/

//----------------------------------

static access Source() as string

return cSource

//----------------------------------

static access Attach() as string

return cAttach

//----------------------------------

static access StrConn() as string

return cStrConn

//----------------------------------

static access DBfilePath() as string

return cDBfilePath

//----------------------------------

static access DFactory() AS DbProviderFactory

return oDFactory

//----------------------------------

static access DConn AS DbConnection

return oDConn

//----------------------------------

static access ShowMessages() as Boolean

return lShowMessages

static assign ShowMessages(lM as Boolean) as void

lShowMessages := lM

return

//----------------------------------

static access ErrorCode() as int

return nErrorCode

//----------------------------------

static access ErrorMessage() as string

return cErrorMessage

//----------------------------------

END CLASS

END NAMESPACE // SmallTrader

======================================================================================

PHH - 17th. October 2011.


Connecting

This is probably the most important part of any SQL related work.

If you can't connect to your data server, and the correct database too, then the rest of the 'stuff' is no use at all.

Obviously, if we connect, we also need to disconnect satisfactorily as well.

The Model :

I think I mentioned someplace earlier in these eNotes that the 'model' I have been using to date, in all my SQL solutions,

is the one where a connection is made with the server ONLY when there is a requirement. By this I mean the application

requires some existing data, or indeed it may wish to change existing data, or add new data.

Once the 'transaction' is completed the connection to the server is closed.

*** TIP *** It is always a good idea, at application launch time, to do a test connection, just to see that things are as

they should be ;0)) So I usually try to connect, show a message, and then force a close - i.e. drop the connection, or

disconnect.

Problem with the Disconnected Approach :

There is one possible 'problem' or issue we may face when applying the 'disconnected model', and that is when it comes to

errors taking place within the server itself.

If for any reason the server throws and error, the error number is available to us (from the server) as long as we send the

correct query immediately after getting an error, and ALSO while the current connection is still intact.

We will deal in greater detail with error codes from the server in the relevant subsection, two or three from here. For now we

will continue assuming that between connecting and disconnecting, we send one query to the server, and get either no reply or

a data object which is a dataset or a single value.

Details of the String Required for Connection :

Before we can open a connection to the server, we need to put together a 'string' value which contains all the necessary

details about which server and which database file.

Usually, this information is know only to the PC user, administrator, and developer. It could be hard coded into the application

code but this is not recommended and is a little naive in my opinion.

Since moving an application to a different PC platform will undoubtedly mean that the connection string details will change, it is

more usual to have a 'config' type file of some description or another, and this is likely to exist outside of the application itself.

In the example shown below we have already read the XML configuration file mentioned, and shown, earlier in the previous subsection.

If you recall the static method 'ReadSerialXmlFile()' is used to populate the class object named 'oConnDetails'. At the point when

we call the 'DoSetConnectionString()' method all the connection details are help in the properties of the 'ConnectionDetails' object.

In the following image, study what is being done, and how the details are accessed from the relevant object :-

=======================================================================================================================

In the above code, please notice that we are just simply getting string data together in the correct order and making it

available for subsequent static method to try to use it for achieving a connection to the server.

Let us now look at the static method to try and gain a connection.

Study the details in the following image, and see how we check to see if we already have an open connection before

we attempt to make a new one :-

===============================================================================================================

Notice that the above code does work, and the message box was generated live - from this very code. The static method

is not very long or complex. And the one to close the connection is even simpler.

In the next image we see the code in the method to force a disconnection - or drop the connection.

Unfortunately, in both of these short methods I have not as yet replaced the three lines to produce the MessageBox

with the one line to use the 'MyMessages' member. I will do this now in my source code so as not to forget ;-)

Study the code below :-

==========================================================================================================

Once again you are seeing live output from the very code we are viewing.

Note that the name of property of the connection object, which is relevant to these operations, is entitled 'State'.

Okay then guys, before we close this subsection, and move on to the next one, we need to see the original form

written to work interactively with the ConnectioDetails class of object - note this was a WinForms creation and done

with VIDE.

Viewing the above image, it should be obvious which controls serialize to file and which deserialize from file. There is

another which just reads the XML file as text for display in the scroll box.

It is not necessary in all apps to have a way to change and test all the connection details - BUT - it is a great help

when setting up apps initially. Although most users should never go near the 'connection' part of SQL the facility

this form / screen offers is excellent for administrators and developers.

I would recommend having such a form - these days a WPF one - and password the access to this form to keep

away "those that know not what they do".

*** TIP *** The easiest way to find the server name is to use Management Studio (MS), open this as shown in the following

image, and all the possible server instances are shown in a drop list. You can also search for more as well.

=================================================================================

I usually like to open MS and open the tables list in the Object Explorer pane - see below for what I did after

first selecting the server from the above drop list :-

=================================================================================

*** TIP *** For reasons I do not understand, I have found in the past that sometimes to get my Vulcan.NET and ADO.NET to

work for the first time (connect etc.) I have to detach the database in focus, close the MS shell, and try again. Don't worry,

it is perfectly possible to have the database attached in MS and also have your app running. I work like this all the time, it is

sometimes the first time that causes a problem.

If you first fail, or have problems, just keep on trying - that is the way to success with SQL 'stuff'.

And finally, before moving on, lets see the WPF version of the Connection form - the one which will be bound to our object.

Don't be fooled by the improved colours ;-0) it does the same basic job of interactively setting the connection details.

As yet this form does not have data binding applied in the usual WPF dotNet 4.0 manner. This copy shown above was

created about 20 months ago and was one of my first few attempts at using WPF for real apps.

I will attempt to improve things by doing them the 'right' way, although this particular screen has not that much to benefit

from data binding - not like many of the other screens in the Small Trader application.

Time to move on, see you in the next section ..........

PHH - 23rd. August 2011.


Returning data

Okay you guys, are you up for this subsection on getting back data from our SQL database ?

In many ways it is quite straightforward, some would even say easy.

One Thing or Many :

When we send a script to the SQL server with the intension of getting back data, we will have in mind

whether we expect to get a single piece of data, or a bunch of stuff.

An example of a 'scalar' quantity (single thing) is for example a date - say the date of the last order of a

specified customer. We can obviously have single or 'scalar' value returns for any data type which is

supported by the SQL table field types. So we can expect Boolean, Strings, Integer and Double, as well

as a few possibly less well used types as well.

If we expect as a result, of our query statement being sent to the server, that we will get a row or a number

of rows from our data table(s), then we are talking about having a DataReader or a DataSet returned.

What form of data we expect returned determines which STATIC method we use to send our query statement.

Let us start with a scalar example.

Getting One (Scalar) :

In the image shown below we are trying to find the unique reference number of the next customer. Checkout

the full details carefully :-

===============================================================================================

*** Note *** I realise that we can have the settings on the Customer table such that the action of supplying a new

customer automatically increments the primary key etc. - but we are using this application as a learning tool and

so we will do some simple things for ourselves for the moment.

Looking at the above code we see that it is quite short, as well as easy to read.

basically, we are forming a string which is the query and then in one line sending this to the server, getting a result,

which we then cast into the type of value we expect. What is returned from the static method is just an object.

So if that was quite easy, then what does the static method itself look like ? Study the following image :-

=============================================================================================

The above static method may be used to get and return any scalar value, of any type. It is very flexible in its use.

Now lets move on to getting a bunch of data.

Getting Many ('DataReader' and 'DataSet') :

Okay then, lets start with using a 'DataReader' to access the data returned by a query statement.

The first thing we need to realise about a data reader is that we need to keep the connection to the server open until we

have done all our reading of the data items. This is not the case with a DataSet, this seems to be returned 'intact', or as a whole.

So we need to remember to close the connection from our calling code - method.

Let us look at the Small Trader application and see the WPF form which finds and displays the customer objects.

In this form I have made the source for the customer drop list an 'ArrayList' - it works OK, but is not the way I would do it today,

more than 20 months down the line.

Study the image following :-

================================================================================

As you can see, the customers are certainly fetched back from the SQL table okay.

So now to see what the form method looks like which achieves this result - see below :-

=========================================================================================================================

In the code in the above image line 736 is the one accessing the STATIC member which uses the statement to return

the DataReader object. remember the data is still at the server until reading is completed, and then, and only then, can

we close the connection.

On line 771 we force a disconnect, as the 'DataReader' is no longer required.

So now that we see how to call our static member, what does the code look like which gets us the reader ?

Study carefully the image shown below :-

====================================================================================================

As you can see from the above code, it is not that different to the Scalar version, and we will find that it is

also not that different to the 'DataSet' version to come.

The one important thing which is different is line 753, where we MUST leave the connection open.

'DataSet' - my Preferred Approach :

[ in order to get a DataSet we use the DataAdaptoer in ADO.NET ]

Let us now look at the third of the alternatives for getting data returned from the SQL server after sending a

query statement, or script.

This time we will do things in such a way that the whole bunch of data will come back to our app as a 'DataSet'.

Once the data is returned we will have our static method close the connection.

Let us start out with a problem to solve - from our 'Small Trader' application. Let us ask for a whole bunch

of job sheets. Study the image shown below :-

=============================================================================================================================

As you can see in the above code, the task of getting our whole pile of data is quite easy - simple almost.

Okay, we also need to look at our method for creating the query string, which we will do a little later on, but first

we need to do an experiment to prove that the second parameter provided on line 169 is actually the name of the first

table and not that of the DataSet itself - study the following image :-

=============================================================================================================================

The point at which our Small Trader application applies this code, is when in the following WPF form, and when

the DataGrid needs filling.

At the moment the 'Refresh' button makes sure that the grid is up to date, and is there simply because I can't

get the appropriate event to fire / work on the 'DatePicker' control. I will work on this, it is something non-standard.

Note that it is the controls which complicate the logic of creating the statement string, or script.

We need to take into account which set of records we require, those with invoices or those without.

Study the following code carefully, particularly lines 149 and 158, note the Left Outer Join makes sure we

get all of the Job Sheets, even those with no match in the invoices :-

===========================================================================================================

So the above code simply makes a long statement string for either of the selected modes (or groups) and also applies

the restrictions from the start and end date controls too.

Okay then guys, it is time to look into the final part of all this, the STATIC method which actually communicates with

the server.

Look closely at the detail of the following image :-

====================================================================================================================

Yes, it does look familiar, as it is not that different from the others we have seen and studied.

The difference here is that we close the connection before returning the 'DataSet' object to the calling method.

Please also be aware that there are other overloads for the Fill method, and so other and slightly different ways of

using Fill to solve our problems.

For the straightforward SQL coding I have done so far, and for standard types of problem I have tried to solve, these

techniques shown above have been perfectly adequate, so I stick with them for now.

Okay then, time to move on and see how we edit data and store new stuff we collect at our WPF form inputs.

See you in the next subsection ......

PHH - 23rd. August 2011.


Making and Changing Table Rows

This subsection is about storing new table row data and also changing existing rows as well.

For those of you who are familiar with SQL scripting, the command words related to these operations

are INSERT and UPDATE.

If we look at the five existing members of the STATIC class we are building - related to sending statements to the server - we

see that we have two which deal with data 'changes'. Note I have yet to deal with DELETE as my usual style of database use

does not involve me deleting records, I usually mark then as inactive so as not to lose the information.

These operations are generally know by the acronym 'CRUD' - for Create, Update and Delete. (These are general terms.)

For much of what I do below in this section I wish to use 'CRU' as I will be just referring to INSERT and UPDATE commands

in the T-SQL script language.

Have a look at the following image to see what we currently have in our STATIC class :-

===============================================================================================================================

In the above code lines please notice the ones marked 'NonQuery', these are on lines 615 and 651.

The 'Non-Query' refers to the fact that the SQL statement sent to the server is not querying for data to match an enquiry,

but more of a way of sending a command statement to the server, and one which does not expect any return at all.

We will more on this non-query aspect a little later on.

Going Further :

I am afraid to have to tell you that these (CRU) are the operations which are generally VERY verbose or longwinded. if

you as a developer / coder don't have a good and well thought out system it is VERY easy to make errors, and ones which are hard

to find later.

It is for these operations that we need most to construct a good 'SQL_my_Funcs' class.

What is the Problem :

Well, funnily enough, there is not a problem as such. If the table structure is small, meaning it has only a few fields, then there

is not too much of a problem of coding our Vulcan in a similar way to what we may write in the Management Studio editor.

Let us look at the following image, one which shows two simple tables and their structure :-

============================================================

In the above image the tables named 'Goods' and 'Services' are shown fully.

It would be quite easy to do 'CRU' on these without any special classes or members.

In fact looking through my code created early in this project I find the following, where the task has been split into

two parts, even though the tables for Goods and Services are simple - study the following carefully :-

===================================================================================================================

By studying the above code you will see that the task seems to be in two parts, the script or statement itself, and

the list of parameters which must be provided - these are 'tag and value' pairs.

Now then, the above code is complex enough for only four (yes 4) fields, but imagine what it gets like when there are many fields.

And I have to admit that most SQL tables can have VERY many fields.

This 'Small Trader' app is just a small demo one really, even though it does a useful task for my friends at the computer repair

store in Newport. And yet if you look at the image shown below you can see just how complex the Invoice table has become :-

===============================================

Yes, plenty of fields, 41 in total I make it, and I am not going to count those in other tables ;-0)

Don't think of tables with many fields as being a result of poor design, it is just quite necessary sometimes (or even quite often)

to have a whole bunch of fields.

So now you see the problem, one of longwinded code on a limited size screen, and parameter definition a long way from the script

line for checking purposes. All a bit of a pain I think.

And so I came up with my alternative approach

PHH - 23rd. August 2011.


How to do this in MS

In this short section we will see how to manually create an INSERT statement which when sent to the SQL server

will add a new row to the table in focus.

Obviously we must observe the mandatory fields, and provide values for these, otherwise we would get an error from

the server. However, all other fields can be supplied or not. This is NOT the way I approach things with my business

object approach, and I suspect that s true for the Entity Framework approach where MS too deals with business objects.

It makes much more sense to update a row, and create a new one, where all the fields of the row are provided, and these

match on a one-to-one basis with the business object properties. In this case the Customer table has exact matching

fields with the Customer class properties.

Let us see how to make a new row in MS - study the image detail carefully :-

===============================================================================================================================================================================================================================

Notice that I have missed out 3 or 4 of the non-mandatory fields - like Add5, Mobile, and Email.

If we write another query we can see the results of our first statement to do the INSERT.

Checkout the following image :-

=======================================================================================================================

As you can see we have at least a couple of NULL values, simply because we did not specify a value (or the field) in our

previous INSERT statement.

In the simple way I now do my 'Business Object' (BO) approach this would not be the case, I would set default values to all my

properties of a new BO before using it in the business logic. Changes and edit would simply change the values.

So for the moment in my new way of doing INSERT and UPDATE I deal with all fields and no NULL properties.

Changing rather than Creating :

While we are at it, and I have the software tools in front of me, we ought to try at least one direct query in the MS shell to do

an UPDATE to an existing table row.

Let us try and change the very row we have just made, and maybe provide some values for those fields previously let without

any specified value.

Check this out carefully :-

===================================================================================================================================================

And now we can run the same query as before to see if the row (or record) of customer 131313 has changed - see below :-

=======================================================================================================================================================================

In the above image you can see that I have collapsed the row width of unchanged fields / columns, so the results are easier to see.

Notice too that with an UPDATE the mandatory fields do NOT play a part - unless for some strange reason you were trying to make

them NULLs.

If the row has been made previously, all mandatory 'rules' or conditions must have been met, so that the server did not throw an

exception. So we simply update, or change, those fields we wish.

Lets move on to the next subsection ..........

PHH - 24th. August 2011.


My Old Way !

I think it is worth showing an example of how I started out saving a business object of my own making

into an SQL table related to this object. Below we will see how I treated an object of the 'Customer' class

in my first real SQL applet called 'BESTcompany'.

Because I was copying the way this was done in the Management Studio 'shell' I have ended up with everything

in one method - not nice ! Below we will see an 'UPDATE' version of my old style coding.

So let us see if I can make this readable in the images shown below :-

=====================================================================================================================

====================================================================================================================================================================================================

As you can see in the images above we have quite a bunch of code lines, and this is just for EIGHT (yes 8) fields !

To my mind the biggest problem is have parameter code with important parameter names, separated by many lines

from the text string which is to be the statement, or query. The chosen parameter name has to appear in both parts,

be exactly correct, and not be a duplicate. This is therefore difficult to achieve with the code arranged as it is above.

Make sure you see the previous subsection which shows the origin of this 'Old' approach, making a direct query from

the Management Studio (MS) shell.

The extra problem to the MS shell is that we have to 'extract' from, or associate with, the properties of our business object,

in this case a 'Customer' object. In MS we supply a fixed value at query time.

See you in the next subsection ...................

PHH - 24th. August 2011.


Possible Problems - New Way

Possible Issues or Problems :

errors in the text entries for the SQL statement - see below

========================================================================================================================

======================================================================================================================================================================================

This is one side of the coin for using new tools for SQL like the EF (Entity Framework) and others like LINQ, checks are all done at compile time.

It could be possible for a compiled and tested app having such an error like this somewhere, waiting to blow things

out of the water, and everyone thinks things are OK :((

and here is another example when I have made a first attempt at saving Goods (parts) in this new way :-

===============================================================================================

PHH - 23rd. August 2011.


Errors at the Server

Having errors take place within the SQL server after we have sent a query statement from our ADO.NET based

methods in our Vulcan code, is an issue we MUST address, and one which can be handled quite nicely - if we

go about it in the right way.

If we use dotNet code 'as is', and not LINQ and Entity Framework technologies, then the strings we send to the

server for it to process, can have errors within them, which only show up when the server tries to parse and then

execute the statement.

The Vulcan compiler can't possibly detect any error in a string value making up the statement script. This means

we can have code which compiles and runs, which may at some later stage throw an error because of a hitherto

untried string which has been constructed at runtime by the dotNet method code.

For this simple reason we MUST use a system which handles errors in the server, and respond to them in an

appropriate manor.

How to Detect Errors :

Well, this little subsection is just Good News !

If we send a short query statement to the server immediately after any previous statement, we can detect if the

previous statement failed, and if it did what the reason was.

The script requesting this MUST be the one immediately after the statement in focus, AND, has to be for the same

connection. So in layman's terms we must keep the connection open, and make sure that no other query statement

is sent after the one in question, and before the script to enquire of the error situation.

First of all let us see what can be got back from the server after an error - below is what we get in the next subsection

if we neglect to include a database name in the script to request a database backup :-

As you can see it is not as yet VERY informative, except that we know we definitely had an error, as zero ( ' 0 ' ) is

what comes back to us when no error at all has taken place previously.

So from this simple output we know we had an error, and that its code is 1038.

Interpreting the Error Code :

Well, this bit is also easy, as Microsoft have provided a 'View' in the master database for us to use.

Study the following image of the Object Explorer in Management Studio :-

================================================================================

In the above image see that the 'System Views' comes within the 'master' under 'System Databases'.

If we look at the following screen shot of what I found on Googling the topic, it suggests that there is a table

which belongs to the system - this maybe true BUT it is the 'view' which is accessible to us, and which we can

use to find the explanation, or error message.

=========================================================================================================================================================================

So from the above image we know we are looking for 'sysmessages', but instead of a table to use, we find

a 'view' - but with a name of 'sys.messages' instead.

In effect the 'view' is a Read Only form of the table!

The image shown below clearly illustrates that we can't find a table which is suitable :-

=============================================================

However, using the correct 'view' - called 'sys.messages' - we can access the messages associated with

the error codes which are returned to us from the server.

Study the following image carefully :-

=============================================================================================================================================================================================

From the above information we can now form a more specific query statement and send it to the server.

Study the short script below :-

======================================================================================================================================

As you can see in the above image, the filter condition in our script means that we now only get eleven rows (11) returned

to us - AND - that ten of these are not in my local language.

So if we wish to have all the field data for the error code 1038, and just in English, we can do the following :-

=======================================================================================================

Equally, Dieter and our friends in Germany code use '1031' as the filter and get the message in German.

Very nice to have it this simple, and I think using 1049 would do Chris OK, it looks like Greek to me ;-0)

Getting the Error Code :

This in itself is easy enough, study the following code :-

==============================================================================================================

But do remember that this MUST be done immediately after the query your are concerned about.

For an example to help you understand, if you sent this script immediately after the Backup error we saw above, we

would get returned to us '1038', and if we then ran the 'GetErrorCodeFromServer()' again we would get a different

result - you guessed it, ZERO, since there was no error when we sent the first statement of "SELECT @@Error;".

After all, this is a query statement like any other, so if it ran without error then we get zero back when we do it

a second time.

Problem - Issue ? :

Okay then, "what is the catch ?" I hear you say.

Up until now we have designed and used our static methods to query the server in a fully 'disconnected' fashion. So

before and after each query - within the static method - we Open and Close a connection.

The consequence of this is that we can't run the get error method after using any query methods, we do not have the

same connection. If we did we would simply get zero each time around.

Top Down Design of what is Required :

Before we get into all the necessary details, let us look at how we may wish to use the facility to automatically pick up

any error code (and message) which may happen during the course of a standard query.

Study the following code carefully :-

============================================================================================

What we have done above is make an incorrect statement string (on line 214) and then send it to the newly changed

static method (entitled 'SendScalarQueryScript') - then print out some stored details.

If we then run the above handful of code lines - from the end of the 'Initialize()' method of the main form - we see the results

of our efforts displayed in the message boxes. We can see some of the changed code in the background, but we will

study the full details soon.

Check this out :-

===========================================================================================================================

And now for the next message box, that which shows us the error code returned from the server.

Note the error code is stored in a static field called 'nError' within the SQL_my_funcs static class.

======================================================================================

And finally, the last of the three message boxes, that which displays the stored error message itself.

=============================================================================================

Okay then guys, we can see the idea - after any query, the error code and message are always

available if required by the developer / coder.

What Needs to be Done in the Static Query Methods ? :

So what do we have to do to change our existing static query methods to bring this about ?

We will use the 'SendScalarQueryScript' method to explore the possibilities, as well as doing

some testing too.

The New Fields :

Before we can do any processing we need a few more data fields - see below :-

=======================================================================

This simple method of storing an error code and related message, is not perfect, but will certainly do us for now.

Let us go on to study in detail how the 'SendScalarQueryScript' method has been changed to make all this work

for us :-

==============================================================================================

In the image above the first thing to notice is that lines 635 and 660 Open and Close a connection to the server.

This means that on other static member call must involve any other connection changing - making and breaking!

Quite simply, this means the 'GetErrorCodeFromServer' static member, as well as the 'FindErrorMessage' static

member must written so as to use an open connection and then leave it open when done - see below :-

==============================================================================================================

And the new error message method :-

==============================================================================================================

From the two above images of the code being successfully used we see that one special (and new) static method

is required - this is entitled 'KeepOpenSendScalarQueryScript' and it does not Open or Close a connection.

Checkout the code below :-

==============================================================================================

Yes, this is a simple or 'primitive' form of the other similar query of the name 'SendScalarQueryScript', and obviously it

must not have any error checking or anything fancy - other than the query itself, and passing back the result.

So Where Now ? :

Well, it would seem that now we have a working 'Auto Error Catcher' form of the Scalar query method, we now need to

make similar changes to the methods for 'Reader', 'DataSet' and 'Non Query'.

This I have attempted, and after making a slight improvement in the code to that I first tried in the 'Scalar' version, I have

also tested out each with an incorrect script to see if things generally worked OK.

See below for my first attempts at testing :-

=============================================================================================

Having had some success with all the first test I changed the script and the type of error I 'forced' into it.

See below for more testing - AND - the results by 'magic' screen capture too :-

===================================================================================================================================================

[ *** note *** Once again the testing was done just before the end of the Initialize() method in the main Window1 form.]

From the above runtime output it would seem that the SQL server uses the same table of error messages when

it provides us with an exception message in the form shown below :-

===========================================================================

In that case we may be better to say the exception message itself, rather than do a lookup of the message table.

Anyway, enough for the moment, now let us see the improved code I used in the new query methods - below is

the one for DataSets - check it out thoroughly :-

==================================================================================================================

Looking at the above image I realise that the green L.H.S. markings more or less indicate where the changes

have been done.

Summary :

To summarise, we can now automatically check for errors after each type of statement query to the server, and the results

of the error check are stored in fields in the static class.

Since we are not making a special effort to check for an error, we need to use the field values from other code when we feel

we may have a problem.

In fact, now that we auto capture any errors, we could decide to save them in a table too. We could do this on an

automatic basis as well.

I will have a think about this and add more here if anything comes of the idea.

Now it is time to move on to the next section ...............

PHH - 26th. August 2011.


Alternative Error Handling

So far in the handling of errors taking place within the SQL server, we have tried to take control of

things ourselves by sending the SELECT statement with '@@ERROR' included.

Well, that works okay, and can be done from Management Studio and other places too. After all

it is just standard T-SQL stuff and regular server behaviour.

HOWEVER, we are using dotNet code, and have more sophisticated tools at our disposal. Previously,

you may have seen me use code similar to that shown below :-

=============================================================================================

In the above code you will see that in the TRY / CATCH we use the exception class 'DbException'.

Usually I use 'SystemException' for my TRY / CATCH structures, but there are certain benefits to us

using this other class - which is specially designed for SQL server use.

If we take a look at the SDK documentation for the class - shown below - you see we ought to have

all the details of the server error already at our finger tips, we don't need enquire of the server ourselves.

Study these details carefully :-

===============================================================================================================================

In the image shown above it is the handful of properties which prove to be the most benefit to us trying to

acquire and store the details of any error occurring.

Let us now do a test to see if we can use some of these properties, ones like ErrorCode, InnerException, and Source -

as well as the Message we already use.

====================================================================================================

=========================================================================================================================================================

=====================================================================================================

====================================================================================================================================================

=======================================================================================================================================

Problems - numeric code returned from this method should be 102, 208, and 207 - but is always -2146232060

==================================================================================================================

PHH - 27th. August 2011.


Backing up of the Database

To do basic backups of the full database being used in an application is actually quite easy.

In fact sometimes more than one database may be being used, and in that case 'they' all can be

backed up in a similar fashion - all quite straightforward.

First Attempt - Basic backup :

To our existing STATIC class 'SQL_my_funcs' let us add a further static method, and call it 'DoBasicBackup'.

I have coded this new method and run a test too - the image below shows a lot, study it carefully :-

=========================================================================================================================

Although the backing up members of the STATIC class are very much a 'work in progress' project area, the above

code does work nicely and can form a good basis to build upon.

Following is a few small images to show the calling code and the results in a 'WE' pane output :-

====================================================================================

And just to prove that if we get something wrong, and still try to send an incomplete statement to the server, we

have the following images to study - check these out :-

*** notice **** the sting for the database is obviously 'empty' !

The following is what we get back from the server after using the STATIC method called 'GetErrorCodeFromServer()'.

We will need a way to lookup the code (here 1038) to see what the message is.

"Watch this space" ;-)

And finally a couple of error boxes to show that my Try/Catches are working at various levels :-

*** Note *** 'GAC' is not the usual interpretation, it is my acronym and stands for 'Get All Customers'.

So I have an issue somewhere in the following code :-

=============================================================================================================

And yes, it is because I am still converting parts of the original Small Trader app to the new way of using

the 'SQL_my_funcs' static class, in place of the older 'built-in' SQL methods.

And finally the highest of my level catches :-

PHH - 25th. August 2011.


Example 2, the 'SQL_SmallTrader' class

Background to making the Class :

Earlier I explained that it was a good idea to keep any methods related to SQL tables, and hence business

objects, from the 'SQL_my_funcs' static class. We ideally need to keep this class a general one, and one which

can support any application needing SQL database access.

To be in-line with this 'aim' or design idea, I have created a new STATIC class called 'SQL_SmallTrader' which

now has pairs of members to deal specifically with tables and objects which are in the Small Trader solution.

Let us have a look at what we now have - study the following image carefully :-

=============================================================================================================================

Since previously mentioning this 'pairs' of methods (members) structure, I have added three further pairs for the Service Job sheet,

the Goods (or parts) required for a repair, and the Services carried out during a repair. These can be seen at the end of the above

image of my coding.

If we now look at my cleaned up MS image of the database tables - in the object explorer - we see the details shown

in the image below :-

========================================================

In the above image we can see that we have a table called 'MyErrors' and this does match have a matching pair of

members in the new static class. Why is this?

Well, it is quite simple, the table called 'MyErrors' will be a general table, and one present in all my solutions to SQL

application problems. The idea is that it will hold errors generated in the server itself, as opposed to application

dotNet exceptions.

The consequence of this is that the similar pair of members required for INSERT and UPDATE need to be in the

generally available 'SQL_my_funcs' class - see below :-

=================================================================================================================

Although I have decided to place the error members within the class 'SQL_my_funcs' the handling of the server errors

is still a 'work in progress' task, so thing will change and improve before I document anything in these eNotes.

In the code above you may also notice a few general members related to displaying screen messages from exception

objects - I have included an overload to provide the inner message, an idea first put to me in a news group posting

from Johan in SA. (Thanks.)

Going Further :

Now that we have firmly in our mind that a complex set of members is required to carry out any INSERT and UPDATE

procedure from our business objects in RAM, we can look at what is different about the DELETE side of the general

phrase 'CRUD' - create, update and delete.

Foolow the ideas in the next subsection to see how DELETE differs, and is much easier to deal with if we choose to

use in in our projects and solutions.

Putting the 'D' into 'CRU' :

With the acronym 'CRU' we are covering commands in the T-SQL scripting language of 'INSERT' and 'UPDATE' and both

of these feature and involve field data - that is data values for each column in the table.

However, when we come to the DELETE command in T-SQL we are simply identifying the rows or records which need to

be removed. So the statement we have to construct is very similar to a query statement, or script.

Since we are running a selection query in principle, we can use the existing static method(s) to achieve our goals.

To make this more clear let us see this in action in the MS shell.

Study carefully the following sequence of scripting actions :-

===============================================================================================

By using the above query script we see that for some reason we have two entries (rows) for Lee Purkiss.

So we would like to remove one of these rows with 'UnRef' of value 113. Let us try to select the one without

'himself' as the 'KnowBy' value.

Let us start by trying to select both of the rows - see below :-

===========================================================================================

Okay, this is fine, but now we need to identify the empty field under 'KnownBy' - see below :-

===========================================================================================

Having got the SELECT query to find the very row we are after, we can now simply change the content

of the query pane in MS to carry out the removal. Note that I have followed what Paul Nielsen recommends

and test the script with SELECT first, then, and ONLY then, change it to 'DELETE' and re-run it.

Check out the following :-

=================================================================================

Well, the messages tab content would suggest that all went well and that now we should have only one

Lee Purkiss in our table.

Let us try re-running the earlier general query to see what we have - check this out :-

==============================================================================

Yes, checking the above image of the results after removal / deleting, we see the result we were after.

So Where does this Leave Us ? :

If you remember, we started this DELETE section with the purpose of finding out how we could use the existing

member(s) of our STATIC class to achieve the results we are after.

Since we ended up using a relatively short and simple statement, and that we required no data returned from the

server itself, we could in fact do what we did in the MS shell with a :-

SendNonQueryScript(cScript)        //----- where 'cScript' is the tested query from above

so we could code something like :-

cScript := "DELETE FROM [C:\USERS\BEST\SMALLTRADER.MDF].[dbo].[Customer] where UnRef = 113 and [KnownBy] = ''"

and then calling the method as seen above.

If we once again look directly at our data we see we have another duplicate on which we could try out our Vulcan code.

See below for the double entry of '101' :-

===========================================================================

This time we can use the 'Salutation' to distinguish one row from another.

Here is my test in the MS shell :-

===================================================================================================================================================

And when I transfer this to the Initialize() method of my Small Trader application we get this :-

=======================================================================================================================================================

Well, the above image seems to suggest to me that we have had success with our simple test.

Let us now return to the MS shell and re-run the general query as previously :-

====================================================================================

Yep! things look good, and we now have only one Stephen Smith, the 'Mr' row has been removed by the server.

Summary :

Although DELETE puts the 'D' into 'CRUD' we do not need any special member in our STATIC class to deal with the

removal of unwanted rows.

If we construct a query statement and then use the member called 'SendNonQueryScript' we get the job done.

Oh! one word of caution, if you have referential integrity set on the database tables then you may NOT be able to do

some deletions, as their are rows in other tables which depend on some of those you are trying to remove.

At the moment with this development / test app I have left it off and even Primary key values may be duplicated. We

can see how to change things to switch on referential checking etc. later in the eNotes.

Finally - The 'ServiceJob' Members :

Since I first started taliking about the 'pairs' of members related to each table and business object, I have gone and

created those for ServiceJob and the related Goods and Services.

This refreshed in my mind how easy / difficult the task was, to first create and then test a new pair of methods.

Let me not run away from reporting back to you that the task is a little tedious, it has to be said, BUT, it is a one-off

task for each major table - and business object. So well worth doing carefully, and testing thoroughly too.

It may be best to see the results of my labour before I discuss any more details of my efforts and testing.

Study carefully the new code for the 'ServiceJob' pair of members shown below :-

========================================================================================================================

Since there are few changes to be made to the second of these methods - I took and existing one and copies, pasted, and

adapted it - then it is how I went about creating the first method which is of particular interest.

My approach was to have the table open in the Object Explorer (OE) in MS, showing the column / field specifications - see below

for an illustration of this :-

====================================================

To make and code each line of my Vulcan static method, I first clicked on the relevant

column in the OE, simply to help keep track of where I was up to, and so as not to miss out any

columns from the set required.

Then each dotNet coded line was created with the appropriate column name, parameter 'symbol' or tag,

the data type, and then the property of the business object which is to provide the data value at runtime.

In the above image I am pointing at the first column, that named 'UnRef' and so the dotNet code line being

worked on is the following one :-

============================================================================================

Although I have stated what the four parameters represent in the above code line, we need to look in more detail

what actually the 'SQLfieldSpec' class is and does for us. So we understand what happens when we instantiate

with these parameters.

Before we move on to see the 'SQLfieldSpec' class we need to say that the overall process, or procedure, of adding

the code lines for each column, is a matter of taking care, and checking spelling, as well as making sure that the data

type is correct - AND - that each chosen 'tag' for the parameter is unique.

** Reminder ** The approach I am taking with the 'SQL_myFuncs_ and 'SQL_SmallTrader' static classes is that each

business object relates one-to-one with an SQL table. Also that each field is given a value by the appropriate property

of the business object having an acceptable value. The constructor in the business object class makes sure that we

have no NULLs.

Not because I have a 'thing' about NULLs, but rather that for the tasks I do with the static class, it suits me best to have

chosen default values, and not NULLs. Personally, I think that every guy should get into using NULLs correctly, and do

some thorough homework on the topic. If you run away from NULL use it will come back to bite you sometime ;-0)

>> I suggest you look at Section E in these notes, at the relevant Tips & Tricks on NULL. <<

Below is the way I initialize the values of the fields in my 'ServiceJob' class at instantiation, study carefully :-

=================================================================================

There are 27 fields in the MS OE pane for the table, and also 27 class fields up to line 110 where we have

other field data related to the Goods and Services tables.

Lets move on to the 'SQLfieldSpec' class, and its use to us in the application.

The 'SQLfieldSpec' Class :

To support the STATIC class of 'SQL_my_funcs' we need to have a class to hold the four parameter values we

saw earlier in this subsection - while making the 'FillTypeList' for the ServiceJob table and business object.

Below is the collapsed outline of the new class :-

=============================================================================================================

Basically, the point of this class is simply to hold in one place all of the necessary four parameter values

which we will need to create the SQL statement for INSERT and UPDATE.

Having such a class makes it easier for the developer to code correctly and as fast as possible without using

EF (Entity Framework).

Let us expand the parts we have used so far in the images above, firstly the Constructor and Initialize method :-

========================================================================================================

We have used the overload of the constructor which takes all of the four parameters, and so we also need to

look into the relevant overload of the Initialize() method.

As I said earlier, the class is really a 'container' for the four different parameters.

Here are the properties of the class :-

=========================================================

Think about it now, I may have been better advised to have called the 'Symbol' property 'Tag'. But the

purpose would not have changed in doing this, just the semantics and the syntax a little ;-0))

What is done with the set of 'SQLfieldSpec' objects ? :

For us to see and understand the use of this class we need to start the code trail within the 'ServiceJob_FillTypedList()'

member on the STATIC class 'SQL_SmallTrader'.

Let us view the code below :-

========================================================================================================================

Looking at the above image we can clearly see that a strongly typed list is being made.

The 'List' is of 'SQLfieldSpec' objects, and in our current example we should get a list of length 27, one for

each field of the SQL table in focus.

So one 'Job' object (class 'ServiceJob') is made into a single typed list in line 211, with 27 'SQLfieldSpec' items.

All that is left to do is done in line 213, where an SQL statement is both constructed and also sent to the server.

Once again, hindsight is a wonderful thing, and maybe another, and better, name ought to have been chosen. I think

what I was thinking was that a "business object" was being processed to use its detail to make a statement, which could

then be sent to the server.

You need to realise that this method handles any type of business object structured in the fashion described above, and so

we could not use a specific name - just plain old 'Business Object'.

The subsequent method called 'MakeBusinessObjectChange' was actually going to carry out the querying of the server, and

hence bring about 'change'.

Let us first study the static method which creates the SQL string to use in the query. See below :-

========================================================================================================================================================

When we examine the above code we see that in lines 146 and 153 there are a couple of string variables which are being

used to construct the statement - these are generated in the static members called 'CreateFieldSymbolPairs' and

'CreateFieldsSymbols'.

We will look at this aspect later, but for now we need to see that the 'eMode' parameter switches between INSERT and UPDATE

as the style of SQL statement being constructed. We will look at the related enumeration a little later on.

Also in the above code, before a Boolean result is returned, the code of line 157 sends the string statement to the server

for execution within the server itself.

By way of a good test, if I uncomment the line at 156 and run the application we will see just what statements are being

made for us by the method.

In the sample illustrations below I am saving a 'ServiceJob' in the usual way for the Small Trader app.

Study these images carefully, they tell us a lot :-

========================================================================================================================================================

The above statement is for the Service Job business object itself, and as usual shows something a bit of a nightmare

to read, check, code, or script manually. Works nicely in a code method however ;-) I know what I will be using

from now on.

Now, because I chose a Service Job with some parts and service too, we get as many Goods and Services statement

message boxes as there are items in the Goods and Services arrays - see one illustration form each set :-

First for a single 'Goods' object :-

====================================================================================================

And now for a single 'Services' object :-

========================================================================================================

Okay then, we can see that the static method creating the statement seems to be working fine, but how

does the method which sends the SQL query statement look ?

And don't forget, as well as a script statement like those in the message boxes above, we have to create ad provide

a whole bunch of parameter objects for the Command object, so we will need 27 of these added to the array or

collection needed by the Command object.

Firstly, let's check this out below :-

===============================================================================================================================================

Above we see the Vulcan code associates the 'cText' input parameter with the SQL query statement - line 170.

And the static member used in line 175 turns our set of 27 'SQLfieldSpec' object items into a collection of parameter objects

for the Command object.

====================================================================================================================

Parameter Objects - Making and Adding to Collection :

Let us concentrate now on how a single parameter object is created, and then how these are arranged into a collection

for use with the Command object of the ADO.NET querying process.

Let us start with the static method of the 'SQL_my_funcs' class which makes the collection- see below :-

=================================================================================================================

This static method needs to use the static member (method) 'SetAddParam()' for each of the 27 strongly typed list

items for our example using the 'ServiceJob' business object.

Look carefully at the following code :-

===================================================================================================================================

So in the above image line 225 is the point at which each parameter item is added to the growing collection.

Field Symbols and Field Symbol Pairs :

The differing styles of the INSERT and UPDATE statements mean we need some string 'snippets' which are either

field and symbol 'pairs' like those below :-

==================================================================

or a whole set of fields followed by a whole and matching set of symbols, as seen below :-

===========================================================================

First let us see the static method which makes the UPDATE style of fields/symbols :-

==================================================================================================================================

And then the member which does the whole set grouping of fields and symbols for the INSERT style of statement.

Study the following carefully :-

==========================================================================================================================================

This is not complicated, or rocket science - BUT - it is complex, and because it has many parts and can be

just a touch longwinded, it can be hard to know what you are doing - on a bad day !!

However, lets put the finishing touches to a difficult and long - but VERY necessary - section.

The 'Row Change' Enumeration :

You may have noticed in the code we have seen previously, that instead of me using fixed string values as "INSERT" and "UPDATE",

I have used enumerations in their place.

Enumerations offer a lot of benefits including making code better to read, easier to maintain, and checkable by the compiler.

Also of course we have intellisense in the editor as well.

The possible variations are illustrated in the image shown below :-

==========================================

At the moment with VN2 version 207 we have to place the 'Enum' definition outside of any class definition, in the same way at the

moment that we can't have nested classes defined.

This now makes a lot of sense of code like the following (which you saw earlier) :-

========================================================================================================================================================

Well, that is all for now I think - it has been a long journey but I can tell you the rewards are GREAT.

I wish I had had this solution to using SQL in such a business object way some years back, still, better late

than never they say ;-)

See you in the next section ................

PHH - 25th. August 2011.


Full working code

//----- SQL_my_funcs.prg -----

//----- created August 2nd. 2011 for VODC in November -----

//----- for "Small Trader" app. - by PHH -----

#using System

#USING System.IO

#using System.Data

#using System.Xml.Serialization

#using System.Data.Common

#using System.Collections.Generic

#using System.Collections.ObjectModel

#using System.Collections

#using System.Windows

//------------------------------------

BEGIN NAMESPACE SmallTrader

STATIC CLASS SQL_my_funcs

#region {.. class variables ..}

static public lConnectionTestedOK as Boolean

static private cFileXML as string

static private cAppDir as string

static private cWork as string

#endregion

#region {.. other variables of the class ..}

//----------------------------------------------

static public oConnDetails AS ConnectionDetails

static private oDFactory AS DbProviderFactory

static private oDConn AS DbConnection

static private cStrConn as string

//----------------------------------------------

#endregion

#region {.. more class variables ..}

static private cSource as string

static private cAttach as string

static private cSecurity as string

static private cTime as string

static private cInst as string

static private cDBfilePath as string

static private lConnected as Boolean

static private lShowMessages as Boolean

#endregion

#region {.. and more ..}

//----------------------------------------------

static private nErrorCode as int

static private cErrorMessage as string

static private cErrorLanguage as string

#endregion

STATIC CONSTRUCTOR()

nErrorCode := 0

cErrorMessage := "No Error"

cErrorLanguage := "1033"

//-----------------------

lConnected := false

lShowMessages := false

//lShowMessages := true

//-------------------------------------------------------

cAppDir := System.IO.Directory.GetCurrentDirectory() +"\"

cWork := System.IO.Directory.GetCurrentDirectory()

cFileXML := cAppDir +"ConnectionDetails.xml"

SQL_my_funcs.ReadSerialXmlFile()

RETURN

//========================= [ Backup Database ] ===========================

static method DoBasicBackup() as Boolean

local lResult := true as Boolean

local oF as DirectoryInfo

oF := Directory.CreateDirectory("C:\BackupSQL")

local cName2Use as string

cName2Use := oF:FullName+"\StaticBasic.bak"

MessageBox.Show(cName2Use,"Will be Used :")

local cScript as string

cScript := "BACKUP DATABASE [" + SQL_my_funcs.DBfilePath + "] "

cScript += "TO DISK = '"+cName2Use+"' "

cScript += "WITH FORMAT, "

cScript += "NAME = 'Full Backup of SmallTrader' "

MessageBox.Show(cScript, "Back-Up :")

try

SQL_my_funcs.KeepOpenSendNonQueryScript(cScript)

MessageBox.Show("Query has been sent .... ","After BU :")

catch oEx as SystemException

MessageBox.Show(oEx:Message, "ERROR :")

end try

local nError as int

nError := SQL_my_funcs.GetErrorCodeFromServer()

if nError = 0

MessageBox.Show("No apparent error","Error ?")

else

MessageBox.Show("Error number :- "+nError:ToString(),"Error ?")

endif

//MessageBox.Show("Just before force disconnect ....","TEST :")

SQL_my_funcs.DoForceDisconnect()

RETURN lResult

//========================= [ End Backup] ===========================

//========================= [ Error Object ] ==============================

static method SaveErrorToSQLtable(oE as MyError) as void

SQL_my_funcs.ChangeErrorRecord(oE, RowChangeModeSQL.Insert)

return

static method Error_FillTypedList(oE as MyError) as List<SQLfieldSpec>

local aSFS as List<SQLfieldSpec>

aSFS := List<SQLfieldSpec>{}

aSFS:Add(SQLfieldSpec{"[UnRef]", "@ur", DbType.Int32, oE:UnRef})

aSFS:Add(SQLfieldSpec{"[Occurred]", "@occ", DbType.DateTime, oE:Occurred})

aSFS:Add(SQLfieldSpec{"[Method_Name]", "@mn", DbType.String, oE:MethodName})

aSFS:Add(SQLfieldSpec{"[Object_Class]", "@obj", DbType.String, oE:ObjectClass})

aSFS:Add(SQLfieldSpec{"[Namespace]", "@nsp", DbType.String, oE:Namespace})

aSFS:Add(SQLfieldSpec{"[Operation]", "@op", DbType.String, oE:Operation})

aSFS:Add(SQLfieldSpec{"[Main_Message]", "@mm", DbType.String, oE:MainMessage})

aSFS:Add(SQLfieldSpec{"[Inner_Message]", "@im", DbType.String, oE:InnerMessage})

aSFS:Add(SQLfieldSpec{"[SQL_Error_Text]", "@set", DbType.String, oE:SQLerrorText})

aSFS:Add(SQLfieldSpec{"[SQL_Error_Code]", "@sec", DbType.Int32, oE:SQLerrorCode})

return aSFS

static method ChangeErrorRecord(oE as MyError, eMode as RowChangeModeSQL) as Boolean

local lResult := false as Boolean

if eMode = RowChangeModeSQL.Delete

MessageBox.Show("Wrong mode requested >> DELETE !?","Change MODE :")

return lResult

endif

local cTable := "[MyErrors]" as string //----- change for other Tables -----

//==========================================================================

local aSFS as List<SQLfieldSpec>

aSFS := SQL_my_funcs.Error_FillTypedList(oE)

//--------------------------------------------

//MessageBox.Show("Just about to try the STATIC func!","Testing :")

lResult := SQL_my_funcs.MakeBusinessObjectDetail(aSFS, cTable, eMode)

//MessageBox.Show("Just after MBO - try the STATIC func!"+lResult:ToString(),"Testing :")

return lResult

//========================= [ End Error Object ] ===========================================

//========================= [ Message for Exceptions ] =============================

static method MyInnerMessage(oEX as Exception) as string

local cText := "" as String

if oEX:InnerException <> NULL

cText := oEX:InnerException:Message+CRLF

cText += "Exception Type : "+oEX:InnerException:GetType():Fullname+CRLF

cText += "Source : "+oEX:InnerException:Source+CRLF

cText += oEX:InnerException:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oEX:InnerException:TargetSite:ToString()+CRLF

endif

//MessageBox.Show(cText)

return cText

static method MyMainMessage(oEX as Exception) as string

local cText as String

cText := oEX:Message+CRLF

cText += "Exception Type : "+oEX:GetType():Fullname+CRLF

cText += "Source : "+oEX:Source+CRLF

cText += oEX:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oEX:TargetSite:ToString()+CRLF

//MessageBox.Show(cText)

return cText

//------------------- And Another Overload --------------------

static method MyInnerMessage(oDBX as DbException) as string

local cText := "" as String

if oDBX:InnerException <> NULL

cText := oDBX:InnerException:Message+CRLF

cText += "Exception Type : "+oDBX:InnerException:GetType():Fullname+CRLF

cText += "Source : "+oDBX:InnerException:Source+CRLF

cText += oDBX:InnerException:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oDBX:InnerException:TargetSite:ToString()+CRLF

endif

//MessageBox.Show(cText)

return cText

static method MyMainMessage(oDBX as DbException) as string

local cText as String

cText := oDBX:Message+CRLF

cText += "Exception Type : "+oDBX:GetType():Fullname+CRLF

cText += "Source : "+oDBX:Source+CRLF

cText += oDBX:StackTrace:ToString()+CRLF

cText += "Targert Site : "+oDBX:TargetSite:ToString()+CRLF

//MessageBox.Show(cText)

return cText

//--------------------------------------------------------------

//========================= [ End Exception Messages ] =============================

static method MakeBusinessObjectDetail(aSFS as List<SQLfieldSpec>, cTable as string, eMode as RowChangeModeSQL) as Boolean

local lResult := false as Boolean

local cText, cHeading as string

if eMode = RowChangeModeSQL.Update

local cFieldSymbolPairs := "" as string

SQL_my_funcs.CreateFieldSymbolPairs(cFieldSymbolPairs,aSFS)

cText := "UPDATE "+cTable+" SET "+cFieldSymbolPairs+" WHERE [UnRef] = @ur ;"

cHeading := "Updating :"

endif

if eMode = RowChangeModeSQL.Insert

local cFields := "" as string

local cSymbols := "" as string

SQL_my_funcs.CreateFieldsSymbols(cFields,cSymbols,aSFS)

cText := "INSERT INTO "+cTable+" ("+cFields+") VALUES ("+cSymbols+");"

cHeading := "Inserting :"

endif

//MessageBox.Show(cText,"Script :")

lResult := SQL_my_funcs.MakeBusinessObjectChange(aSFS, cText, cHeading)

return lResult

static method MakeBusinessObjectChange(aSFS as List<SQLfieldSpec>, cText as string, cHeading as string) as Boolean

local lResult := false as Boolean

//====================================================

SQL_my_funcs.DoSetConnectionString()

IF !SQL_my_funcs.DoTryToConnect()

RETURN lResult

ENDIF

//==========================

LOCAL oCommand AS DbCommand

oCommand := SQL_my_funcs.DFactory:CreateCommand()

oCommand:CommandText := cText

oCommand:CommandType := CommandType.Text

oCommand:Connection := SQL_my_funcs.DConn

//----------------------------------------------------

SQL_my_funcs.AllParamsSetAdd(oCommand, aSFS)

TRY //----- execute the insert statement -----

oCommand:ExecuteNonQuery()

lResult := true

CATCH DbEx AS DbException

MessageBox.Show(DbEx:Message, cHeading)

END TRY

SQL_my_funcs.DoForceDisconnect()

return lResult

static method CreateFieldSymbolPairs(cFieldSymbolPairs ref string, aSFS as List<SQLfieldSpec>) as void

local oLTE as IEnumerator

oLTE := aSFS:GetEnumerator()

local oSFS as SQLfieldSpec

local i := 0 as int

do while oLTE:MoveNext()

i +=1

if i > 1

cFieldSymbolPairs += ","

endif

oSFS := (SQLfieldSpec)oLTE:Current

cFieldSymbolPairs += oSFS:FieldName+"="+oSFS:@@Symbol

enddo

return

static method CreateFieldsSymbols(cFields ref string, cSymbols ref string, aSFS as List<SQLfieldSpec>) as void

local i := 0 as int

local oLTE as IEnumerator

oLTE := aSFS:GetEnumerator()

local oSFS as SQLfieldSpec

do while oLTE:MoveNext()

i +=1

if i > 1

cFields += ","

cSymbols += ","

endif

oSFS := (SQLfieldSpec)oLTE:Current

cFields += oSFS:FieldName

cSymbols += oSFS:@@Symbol

enddo

return

static method SetAddParam(oCommand AS DbCommand , oDbT as DbType, cSym as string, oV as object) as void

LOCAL oParam AS DbParameter

oParam := SQL_my_funcs.DFactory:CreateParameter()

oParam:ParameterName := cSym

oParam:DbType := oDbT

oParam:Value := oV

oCommand:Parameters:Add(oParam)

return

static method AllParamsSetAdd(oCommand AS DbCommand, aSFS as List<SQLfieldSpec>) as void

local oSFS := SQLfieldSpec{} as SQLfieldSpec

local oLTE := aSFS:GetEnumerator() as IEnumerator

oLTE:Reset()

do while oLTE:MoveNext()

oSFS := (SQLfieldSpec)oLTE:Current

SQL_my_funcs.SetAddParam(oCommand, oSFS:DbT, oSFS:@@Symbol, oSFS:Val)

enddo

return

static method ReadSerialXmlFile() as logic

local serial as XmlSerializer

local oSR as StreamReader

local cText, cHead as string

local lResult as Boolean

lResult := false

lConnectionTestedOK := false

if File.Exists(cFileXML)

cText := "Existing file will be read!"

cHead := "XML file :"

//----- use my STATIC toggled message system -----

MyMessages(cText,cHead)

//----- read object from XML file -----

serial := XmlSerializer{typeof(ConnectionDetails)}

oSR := StreamReader{cFileXML}

oConnDetails := (ConnectionDetails)serial:Deserialize(oSR)

oSR:Close()

//-------------------------------------

lResult := true

lConnectionTestedOK := true

//----- use my STATIC toggled message system -----

MyMessages(oConnDetails:ToString(),cHead)

else

cHead := "XML file :"

cText := "File can't be found !?"+CRLF

cText += "Go to Connection and create a string."+CRLF

MessageBox.Show(cText,cHead)

endif

return lResult

static method SerializationToFile() as void

local cText, cHead as string

if oConnDetails = NULL_object

cText := "NULL object ! - ConnectionDetails ..."

cHead := "My Error :"

//----- use my STATIC toggled message system -----

MyMessages(cText,cHead)

return

endif

local serial as XmlSerializer

local oSW as StreamWriter

if File.Exists(cFileXML)

cText := "File exists already!"

cHead := "XML file :"

MessageBox.Show(cText,cHead)

endif

//----- save object to XML file -----

serial := XmlSerializer{typeof(ConnectionDetails)}

oSW := StreamWriter{cFileXML}

serial:Serialize(oSW, oConnDetails)

oSW:Close()

return

static method SerializeOthersToDisk() as void

/*local cFile as string

cFile := "C:\Temp\customers.xml"

local oSW as StreamWriter

oSW := StreamWriter{cFile}

//----- Save object to XML file -----

local serial as XmlSerializer

local aTypes as Type[]

aTypes := Type[]{1}

aTypes[1] := typeof(Customer)

serial := XmlSerializer{typeof(ArrayList),aTypes}

serial:Serialize(oSW,self:alCustomers)

oSW:Close()*/

return

static method MyMessages(cB as string, cH as string) as void

if ShowMessages

MessageBox.Show(cB,cH)

endif

return

//======================== [ basic ADO.net & settings ] ==============================================

static METHOD DoSetConnectionString() AS VOID

IF String.IsNullOrEmpty(oConnDetails:cProvider)

MessageBox.Show("Provider string NOT available !","WARNING")

RETURN

ENDIF

//====================================================================

IF oConnDetails:cUserName:IndexOf("<") > -1 .OR. oConnDetails:cUserName:IndexOf(">") > -1

oConnDetails:cUserName := ""

ENDIF

IF oConnDetails:cPassWord:IndexOf("<") > -1 .OR. oConnDetails:cPassWord:IndexOf(">") > -1

oConnDetails:cPassWord := ""

ENDIF

//----- get the factory for the provider we require -----

oDFactory := DbProviderFactories.GetFactory("System.Data."+oConnDetails:cProvider)

//----- create a connection STRING ------

oDConn := oDFactory:CreateConnection()

//====================================================

cStrConn := ""

cSource := "Data Source="+oConnDetails:cServerName+";"

cAttach := "AttachDbFilename="+oConnDetails:cFilePath+";;"

//--------------------------------------------------------------

cDBfilePath := oConnDetails:cFilePath //----- for backup ! -----

//--------------------------------------------------------------

cSecurity := "Integrated Security="+oConnDetails:cSecurity+";"

cTime := "Connect Timeout="+oConnDetails:nTimeOut:ToString()+";"

//----- Willie said this would leave it always open if ZERO is supplied! -----

cInst := ""

cStrConn := cSource + cAttach + cSecurity + cTime +cInst

oDConn:ConnectionString := cStrConn

oConnDetails:cConnectionString := cStrConn

MyMessages(cStrConn,"My Connecting String :")

RETURN

static METHOD DoTryToConnect() AS LOGIC

LOCAL lResult AS Boolean

lResult := FALSE

lConnected := false

local cText := "" as string

TRY

oDConn:Open()

cText := "Vulcan.NET : Small Trader : << Connected! >>"

MyMessages(cText,"Try to Connect :")

lResult := TRUE

lConnected := true

CATCH DbEx AS DbException

MessageBox.Show(DbEx:Message)

END TRY

RETURN lResult

static METHOD DoForceDisconnect() AS VOID

LOCAL cText AS STRING

IF ! oDConn = NULL_OBJECT

oDConn:Close()

cText := oDConn:ConnectionString+" - State = "+ oDConn:State:ToString()

MyMessages(cText,"Disconnecting? :")

lConnected := false

else

cText := "Vulcan.NET : Small Trader : << Disconnected! >>"

MyMessages(cText,"Disconnecting? :")

ENDIF

RETURN

//========================= [ query ADO.net members ] ================================================

static method KeepOpenSendScalarQueryScript(cScript as string) as Object

local oResult as object

oResult := object{}

try

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch oEx as SystemException

MessageBox.Show(oEx:message,"Start of Scalar Query !")

end try

//----- getting a scalar quanity returned -----

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

oResult := oDComm:ExecuteScalar()

//----- need to cast in calling code section! -----

CATCH DbEx AS DbException

//SaveAndDisplayErrorDetails(DbEx,"Keep, Returned Data Object :")

MessageBox.Show(DbEx:Message,"Keep, Returned Data Object :")

END TRY

return oResult

static method KeepOpenSendNonQueryScript(cScript as string) as Boolean

local lResult as Boolean

lResult := false

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch DbEx AS DbException

MessageBox.Show(DbEx:message,"Start of Non Query !")

end try

//----- getting no data returned -----

oDComm:CommandType := CommandType.Text

TRY

LOCAL cStatement AS STRING

//----- use a terminator ; as recommended by Paul Nielsen -----

cStatement := cScript

oDComm:CommandText := cStatement

oDComm:CommandType := CommandType.Text

//----- execute the supplied statement -----

oDComm:ExecuteNonQuery()

lResult := true

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Keep-Non Query :")

/* local cText := "" as string

cText += "Error Code :- "+DbEx:ErrorCode:ToString()+CRLF

if !DbEx:InnerException = NULL_object

cText += "Inner Exception :- "+DbEx:InnerException:ToString()+CRLF

endif

cText += "Source :- "+DbEx:Source:ToString()+CRLF

cText += "Message :- "+DbEx:Message+CRLF

//cText += "Stack Trace :- "+DbEx:StackTrace+CRLF

MessageBox.Show(cText,"DbEx Details :")*/

//MessageBox.Show(DbEx:Message,"Non Query :")

END TRY

return lResult

//------------------------------------------------------------------------------------------------

static method SendNonQueryScript(cScript as string) as Boolean

local nError := -999 as int

cErrorMessage := ".. reset for use .."

//------------------------------------

local lResult as Boolean

lResult := false

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch DbEx AS DbException

MessageBox.Show(DbEx:message,"Start of Non Query !")

end try

//----- getting no data returned -----

//oDComm:CommandType := CommandType.Text

TRY

LOCAL cStatement := cScript AS STRING

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cStatement

oDComm:CommandType := CommandType.Text

//----- execute the supplied statement -----

oDComm:ExecuteNonQuery()

nError := 0

lResult := true

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Non Query :")

/* nError := GetErrorCodeFromServer()

nErrorCode := nError

//FindErrorMessage()

cErrorMessage := DbEx:Message

MessageBox.Show(nError:ToString(),"Code ?! :")

MessageBox.Show(DbEx:Message,"Non Query :") */

END TRY

SQL_my_funcs.DoForceDisconnect()

return lResult

static method SendDataSetQueryScript(cScript as string, cTableTitle as string) as DataSet

nErrorCode:= -999

cErrorMessage := ".. reset for use .."

//------------------------------------

local oDS_Result as DataSet

oDS_Result := DataSet{}

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

//----------------------------------

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

CATCH DbEx AS DbException

MessageBox.Show(DbEx:message,"Start of DataSet Query !")

end try

//----- getting a data set returned! -----

oDComm:CommandType := CommandType.Text

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

LOCAL oDA AS DbDataAdapter

oDA := oDFactory:CreateDataAdapter()

oDA:SelectCommand := oDComm

oDS_Result := DataSet{}

//----- execute the select statement -----

oDA:Fill(oDS_Result, cTableTitle)

nErrorCode := 0

cErrorMessage := "No error thrown"

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Error DataSet :")

END TRY

SQL_my_funcs.DoForceDisconnect()

return oDS_Result

static method SaveAndDisplayErrorDetails(DbEx AS DbException, cHead as string) as void

cErrorMessage := DbEx:Message //----- CARE with order, must be before next line -----

nErrorCode := GetErrorCodeFromServer()

if !lShowMessages

local cText := "" as string

cText += ErrorMessage+CRLF

cText += ErrorCode:ToString()+CRLF

MessageBox.Show(cText, cHead)

endif

#region {.. test code ..}

//---------------------------------------

//----- make oE from properties and fields -----

//as MyError, eMode as RowChangeModeSQL

/*local eMode := RowChangeModeSQL.Insert as RowChangeModeSQL

local oE := MyError{} as MyError*/

//ChangeErrorRecord(oE, eMode)

#endregion

return

static method SendReaderQueryScript(cScript as string) as DbDataReader

local nError := -999 as int

cErrorMessage := ".. reset for use .."

//------------------------------------

//----- using the DataReader approach as an alternative -----

//----- to the 'DataTable' one I like particularly -----

local oDr as DbDataReader

try

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

//----------------------------------

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

CATCH DbEx AS DbException

MessageBox.Show(DbEx:message,"Error DataReader :")

return NULL_OBJECT

end try

//----- getting a data set returned -----

oDComm:CommandType := CommandType.Text

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

oDr := oDComm:ExecuteReader()

//----- execute the select statement -----

nError := 0

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Error Datareader :")

/*MessageBox.Show(DbEx:Message,"Error DataReader :")

nError := GetErrorCodeFromServer()

MessageBox.Show(nError:ToString(),"Code ?! :")

nErrorCode := nError*/

//FindErrorMessage()

END TRY

//----- do NOT close the connection - needed Open by DataReader ! -----

return oDr

static method SendScalarQueryScript(cScript as string) as Object

local nError := -999 as int

cErrorMessage := ".. reset for use .."

//------------------------------------

local oResult as object

oResult := object{}

try //----- to Open a connection & make a command object -----

SQL_my_funcs.DoSetConnectionString()

SQL_my_funcs.DoTryToConnect()

LOCAL oDComm AS DbCommand

oDComm := oDFactory:CreateCommand()

oDComm:Connection := oDConn

catch oEx as SystemException

MessageBox.Show(oEx:message,"Start of Scalar Query !")

end try

//----- now try getting a scalar quanity returned -----

TRY

//----- use a terminator ; as recommended by Paul Nielsen -----

oDComm:CommandText := cScript

oDComm:CommandType := CommandType.Text

oResult := oDComm:ExecuteScalar()

//----- need to cast in calling code section! -----

nError := 0

CATCH DbEx AS DbException

SaveAndDisplayErrorDetails(DbEx,"Returned Data Object :")

END TRY

SQL_my_funcs.DoForceDisconnect()

return oResult

//------------------------------------------------------------------------

static method GetErrorCodeFromServer() as int

local nResult := -999 as int

local cScript as string

cScript := "SELECT @@Error;"

nResult := (int)SQL_my_funcs.KeepOpenSendScalarQueryScript(cScript)

if lShowMessages

if nResult = 0

MessageBox.Show("No apparent error ! ","Error Code :")

else

MessageBox.Show("Returned code is :- "+nResult:ToString(),"Error Code :")

endif

endif

return nResult

//================================================================================================

/*static method FindErrorMessage() as void

local cScript as string

cScript := "SELECT text FROM [master].[sys].[messages] where [message_id] = '"

cScript += ErrorCode:ToString() + "' and [language_id] = '"+cErrorLanguage+"' "

cErrorMessage := (string)SQL_my_funcs.KeepOpenSendScalarQueryScript(cScript)

return*/

//----------------------------------

static access Source() as string

return cSource

//----------------------------------

static access Attach() as string

return cAttach

//----------------------------------

static access StrConn() as string

return cStrConn

//----------------------------------

static access DBfilePath() as string

return cDBfilePath

//----------------------------------

static access DFactory() AS DbProviderFactory

return oDFactory

//----------------------------------

static access DConn AS DbConnection

return oDConn

//----------------------------------

static access ShowMessages() as Boolean

return lShowMessages

static assign ShowMessages(lM as Boolean) as void

lShowMessages := lM

return

//----------------------------------

static access ErrorCode() as int

return nErrorCode

//----------------------------------

static access ErrorMessage() as string

return cErrorMessage

//----------------------------------

END CLASS

END NAMESPACE // SmallTrader

============================================================================================

PHH - 17th. October 2011.


Layer 5 - creating within a new app

It has already become apparent that it is 'Layer 5' of our overall scheme of things which will need

most of the work and effort.

Every time we start a new application we will need to construct a fresh 'Layer 5'.

The 'STATIC class' which makes up this layer reflects the business objects in use. Remember, the BOs

also have a close relationship to the data tables in the SQL database.

Structure with the 'STATIC class' :

The whole of Layer5 for an app with contain a group of methods which are specific to each and every Business

Object (BO) used in the application.

So for our Small Trader app we have a class called 'SQL_SmallTrader'. This class will be appropriately named in

any new application.

Within the static class we will see a set of groups of methods - I think it is best to group them such that all the

methods for say 'Customer' are together - see below for an example of the collapsed groupings :-

=================================================================================

If I now expand the 'Customer' group so we see the next 'level' it looks as follows :-

=========================================================================================================================

Within the 'Customer' group of methods I have placed the two important ones to add data to the SQL 'Customer' table,

together at the top, and then bunched up the others in another group.

All of these methods have SQL query statements constructed within them and relating to the 'Customer' table. Also

each of these methods will call on a suitable method from Layer 6 - a general ADO.net related method.

For example, let us see the detail within the 'FindNextCustomerUnRef' method - check this out :-

============================================================================================

Line of code 163 constructs the statement string (in this case it is quite short and simple) and line

numbered 168 calls a method from the next layer, 'Layer 6'.

We will see this relationship between layers 5 and 6 in each of the methods in our BO grouping, except

one - that is the 'FillTypedList' for the BO, as this is actually used within the 'Change_XXXXX_Record' method.

It may look like quite a bit of work, and indeed that will most likely be the case, BUT, it does do the majority

of the work for our application, and hence makes our Business Logic (BL) layer much simpler. All we need do

in the BL layer is call on the 'Layer 5' static members.

Let us move on to see this in action - within our Small Trader application.

Mini Project / Task - Finding Duplicates :

In the topic section we will add some extra functionality to the main menu by creating options to find and display

any duplicate entries in the Customer, ServiceJob, and Invoice SQL tables.

Let us see a good start - the menu and XAML script for it - as yet we have no functionality behind the menu items.

This is what it looks like now :-

====================================================

And the XAML script to do this was quite easy to insert into the existing stuff - see below :-

========================================================================================================

And then after interactively adding the events handler 'stuff' in the XAML designer surface we see this below :-

=========================================================================================================

And the dotNet 'stub' code too has been added by the Designer - see below for this :-

================================================================================================================================================

So before we 'hook' anything up, we now need to go and make some dotNet methods to carry out the necessary

tasks querying the server.

Exploring the Query Script :

Let us now turn to our trusty tool, the Management Studio 'shell'.

Look at a couple of query statements I have developed and got to work for us :-

===============================================================================

As you can see in the above image, we have one query, the second one, which seems to do just as

we require. It finds only the duplicate Customer entries, and returns the reference number of any who

have more that one entry, along with the number of occurrences within the table.

*** Note *** the result of the probable required script is right at the bottom of the output pane.

And correct to what we have previously seen, Customer 103 is duplicated, we have two of Julie.

Now that we have something suitable, and which does the 'set algebra' just as we require, we can try

to massage the script to be more suitable to how we need to construct it when we write our dotNet code

and static method.

Checkout the new script syntax, seen below :-

=======================================================

The SELECT statement is now in a form we can use with our ADO.net static members in 'Layer 6'.

So let us look at an image of success before we examine the working dotNet code in detail :-

====================================================================================================

As you can see, a simple test from within the Initialize() method of the main window gives us a data table

with only one row.

And taking our test and print-out a little further we see the following at runtime :-

================================================================================================================================

This looks very promising, we seem to be finding just what we need.

Let us now see the static method we have used to create our output :-

==============================================================================================================

I simply copied and pasted the script from MS and then tidied the code by adding double quotes etc..

Don't forget, Management Studio is your friend when dealing in all matters SQL server ;-0))

Before we move on to use the same 'set based' algebra to find other duplicates, in the ServiceJob and Invoice table,

we first need to link-up the menu option click to at least a simple MessageBox output display.

Well below is shown a very basic way, but one which needs improving as far as display of information is concerned.

Check this out :-

===============================================================================================

I will leave it to you to add the loop code to cycle through all records, when there are more than just one !

Finally we see the event handler being called - see below :-

=============================================================================================

Lets move on to the duplicate ServiceJobs.

ServiceJobs :

If we once again start in the MS 'shell' and view the table data, we see we have in my test data one duplicate.

ServiceJob '102' has a double entry. Don't get confused with the 'CustomerRef' - that is just unfortunate coincidence ;-0))

Find the duplicate in the image following :-

================================================================

So all we have to do it take the script for Customer, and adjust it ever so slightly to make it work for 'ServiceJob'.

Checkout the details below :-

========================================================

And because we have done all the basic work previously, it has been VERY easy for me to copy, paste, and adapt

the dotNet code and script to do as we need for the ServiceJob duplicates.

I have even added the loop structure to handle displays of more than one dunplicate found.

Checkout the following images, with all their detail :-

=================================================================================

Here is the new static method - only a few small changes from the Customer 'flavour' :-

=======================================================================================================

And finally the method to include in the Windows1 class, note the extra loop structure :-

============================================================================

>>Whoops, forgot to edit line 239 - should read 'ServiceJobs' ! <<

And for the sake of being complete, lets move on to the duplicate invoices.

Invoices :

If we are going to do this - since it is VERY similar to the other two - we need to do something extra.

What if I make the effort to add a few extra duplicates, just so we can test the code more thoroughly ?

Okay then, just spent a few minutes in the MS shell, and the "Edit Top 200 Rows" context menu option.

I think we have some useful test data - see below :-

======================================================

These MS results would suggest that we have three duplicate invoice references, and with 2, 3 and 4 occurrences

of each it would seem.

These results seem to be totally in-line with the recollections I have of actually doing the editing in MS.

Now for some positive results :-

===============================================================================

Yep! that seems to square up with my thoughts on what should be found, and displayed.

Now here is the new static method :-

==============================================================================================================

And also the event handler method for the Window1 class :-

======================================================================

This time I think I have managed to edit all the places 'Invoices' should appear ;-0))

Now let us move on to the next subsection ................

PHH - 5th. September 2011.


Layer 6 - a couple of exceptions!

In layer 6 the ADO.net layer, we said we would not generally see query statements being constructed in

the static methods. That is generally true but there are a couple of exceptions.

In layer 5 we see MANY static methods with query statements relating to the BO class, and the BO related

tables in the database - these belong in layer five as they are BO related tasks.

However, there are a couple of statements we can construct which do NOT relate to the BOs at all.

As way of an example lets see the 'DoBasicBackup' static method - check this out :-

================================================================================================

Like a lot of things in SQL Server, we send a query string to get something to happen.

In this example above the task is NOT related to the BOs at all, but the database itself, we are requesting a back-Up.

Another obvious one we have used in these eNotes is the 'GetErrorCodeFromServer' static method.

If we do more and more database related task, the more likely we are to need to add methods to our layer 6 which

have query statements.

However, it is still true that all BO related statements belong in layer 5.

Let us move on, see you in the next subsection ............

PHH - 31st. August 2011.


Creating DLLs - STATIC classes

PHH - 8th. January 2012.


Layer 6 - SQL / ADO

PHH - 8th. January 2012.


Layer 5 - ADO / BOs

PHH - 8th. January 2012.


<*========================*>

' The BIG divide !! '


Appendix A - Installing SQL Server

Installing MS SQL Server can be a bit tricky, I have to say !

Over the last three years plus I have done it many times and on desktops as well as laptops.

It would seem to me that the best advice is try to download and install the latest version from Microsoft

which is on general release to the public. Do not try an early release version if you are new to SQL - it may

prove difficult to remove later, and any final public version generally will not install on your PC until you have

completely removed the pre-release version.

State of your PC / laptop :

Another thing which is likely to cause you grief when you try to install SQL Server is the state of your software

on your PC.

I found that a newish and pretty 'clean' laptop / PC would allow the download and install of the latest version of

SQL Server without too much hassle, or issues being thrown up.

The hardest case I had was with a development desktop system where I had accumulated older versions of Visual

Studio and other stuff - over years - I could not remove this because it was still needed to serve clients needs.

So if you have to do an install on an older and possibly 'messy' system - try out a download and install first on

an easier and cleaner PC.

The whole process is long and can takes days and days for you to get something suitable which works for you.

Also the 'state' of your operating system may also cause a problem or two to you while attempting an install.

Make sure you have a recent version of Windows and that it is right up to date with service patches and regular

updates. It is best to make sure this is done first, before you try an install of SQL Server.

Financial Cost, and the Express version :

These days the problem of a single developer getting hold of a genuine and fully functional version of MS

SQL Server is not a problem at all.

Microsoft provide very good downloads of the Express version of 2008 R2, with promises for 'Denali' to come.

The Express version now has quite a large database limit - far too large to worry any Vulcan guys wishing to

download and get started immediately.

Where to get your download :

I am afraid that this seems to vary with time, MS sites seem to come and go, and so you will need to be flexible

in your approach.

http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

===================================================================================================================================================================

What else do we need ? :

Well, what I say now may be a little out of date - but you do need to be aware of it !

In previous downloads of MS SQL Server we the user had to select to include the 'Advanced Tools' or was the

phrase 'Advanced Services'.

Whatever it was it meant that we the user also got management Studio (a 'must have') and also Business Intelligence

Studio (BIDS), which allows Reporting Services to be used with your chosen version of SQL Server.

I have a feeling that these days it is all included in the one download - but try and check this as you certainly need

Management Studio (MS).

Oh! - I seem to have answered my own questions - see below :-

========================================================================================================

However, I have just had a bit of a play around with this and like all this stuff on SQL it seems to be not quite

as you may expect it to be. So from now on you are on your own - download the versions you think you need,

and hope you get ;-) and 'Save' them to your disk rather than 'Run' them. This way you can compare size of file etc.

before you commit yourself.

Pressing the Panic Button ! :

If you start an install then do not worry too much, or even 'panic' !

There is usually ways of leaving the install process once it is in progress, in fact the older install software actually

took you through screens and screens to gather data before it actually started to do anything 'serious'.

Using ClickStart for guidance :

I went through the painful process of writing detailed notes some time ago - but these are probably still very helpful

today. Most of my recent installs have seemed similar in nature to what I have previously written here :-

http://www.clickstartvulcan.net/WebNotes/SQL/ClickStartSQL.html

Try www.ClickStartVulcan2.net and find your way from there to "ClickStartSQL".

=====================================================================================================================================================================

What to look out for in the Installation :

One thing for which you need to look out is the place in the install dialog screens where extra users and usernames

may be specified.

I say this because as most of us all know by now the modern Windows O/S is tied down by security, and security issues.

I have found that by including extra entries for users - and certainly add 'Administrator' as well as the logged in user - then

there are less problems with security.

I have to admit I also set Management Studio (MS) to open and run in Administrator mode. This too helps to stop the sorts

of issues which frustrated me in early days with working with SQL Server.

Okay then you guys, having warned about how difficult and tricky this can be, we will attempt to go through the full process,

from start to finish, in the following subsection.

Hold on to your hats and we will have one hell of a ride ;-0))

See you in the next subsection ...........................

PHH - 1st. November 2011.


Twelve points about install .........

- locate and download a suitable version of MS SQL Server - get the latest public release if possible

[ .. do not use CTP version as it will cause problems later .. ]

- make sure your PC has it Windows O/S, and stuff like Visual Studio, right up to date with latest patches and SPs

- run the EXE file and follow the dialog screens - use the guides provided here to help you respond correctly

- use Management Studio (MS) to browse for all instances of MS SQL Server, and find the one you just added

- in MS open the instance of the new SQL server

- in WE locate a suitable database (in a folder which you have R/W access) and attach it to the server instance in MS

- point to a suitable SQL table in the DB and use the context menu to list the TOP 1000 rows.

- ** just as a precaution, detach the DB and close MS. OPen MS again and re-attach the same DB

- when coding ADO.NET stuff from Vulcan the first time access after attachment can seem to be an issue

[ .. by trial and error I found the above may solve any problems ..]

- in VN2 code use ADO.NET to make a connection and try to open and close as a test - use Message Boxes to show results

- once a connection can be established, write a very simple method or two to query the server and get a returned data set.

- now write a simple method to allow you to write data into the DB.

[ .. once you can do all above you are ready for some serious code writing and project design .. ]

I can assure you that once you get through all these and you are successful, you will feel like a million dollars ;-0))

PHH - 1st. November 2011.


a Full Live Install ....

Okay then, having said how this can be somewhat of a problem - installing a new instance of SQL Server onto

a desktop or laptop, we will now attempt to go through the complete process on my other PC behind me.

I choose this PC because it is more modern and the O/S is cleaner I feel. If we can't do it there then we have

little chance on this old development machine of mine.

Although one or two of my captured images show CTP in the detail at places, in fact I eventually did a full install

of a Public release of Server 2008 R2, one which needed a serial number ( I am a member of MSDN).

I suggest you keep away from CTP (Community Technology preview) versions as this can cause problems when

the public release is made. Generally the two versions can't coexist on the same PC platform.

How to use these install images :

I suggest you have a look through the full set of images shown below, before you try your own install.

Depending on the type and version of your download file, you may (will?) get some dialog screens in a slightly

different order, so you need to be flexible on top of being well prepared.

So having clicked my install or setup .EXE file we see the following :-

=========================================================================================================================================

Choose the 'New Installation' - you should then see the following :-

Notice that we get a list of all the other instances of SQL Server on the system. I show you how to do the same thing in

Vulcan code in Section I of these notes.

Depending on your version of install you may see this next screen at this stage of the installation :-

If you get issues other than the Firewall you may have to go fix then depending on what is suggested in the status 'blurb'.

The clean result above is one of the main reasons I used the PC that I did to try out the new install.

Now for Product Key you may have a free Express version - those with MSDN like me will have a key - mine is hidden beneath the

drop list shown below :-

==============================================================================================================================================

The following dialog screen is simple, but necessary to get correct - see below :-

===========================================================================================================================================

At a subsequent screen you may well have to select the features you require. For safety (meaning we want things to work for

this first install of yours) we can select 'All' - or everything.

After you select and click 'Next' you get an information screen - after a minute or two (few seconds?) :-

============================================================================================================================================

Then after this feed-back we are expected to do some real work to help our installation along.

Study the following image in great detail :-

I have manually typed the 'Named Instance' to be "SQL2008_VODCtest".

We need to remember this when we get a lot further through the installation, as well we be looking for it in list of

existing instances.

When we click the next button we see the following :-

===========================================================================================================================================

And then once again we go to a screen which needs far more input by us.

Checkout the drop list input boxes in the following image :-

============================================================================================================================================================

For this first trial install of your own, leave the 'network' option alone and just select 'system' from each of the drop lists.

And that includes the 'Agent one - don't do what I did - shown below :-

==================================================================================================================

Note that the error message does not get removed until after you click 'Next' having selected a suitable input for 'Agent.

Once again we get a screen needing careful attention.

In fact the following screen is rather 'crucial' to the success of the install - check this out :-

I have to say that the input dialog screen are sadly lacking in terms of them communicating exactly what it is that

they require :(

What we are about here, is making an entry (or a few) into the white empty input text box.

The first thing to do is click the 'Current User' button - this is what I got - see below :-

=======================================================================================================================================

Now we need a add a bunch more, and so we need to use the 'Add' button - see below :-

I have to admit then next part of this is NOT at all intuitive - BUT - what do you expect from these young guys at Microsoft ;-0))

For you PC or laptop just click 'Advanced' to bring up another dialog, just as strange, or 'odd' - see below :-

We are now needing to see the list of possibilities on the system, so click 'Find Now'.

You should see something like the image shown below :-

After selecting 'Administrator' we get the result shown beneath :-

And clicking 'OK' does this for us :-

So all we need do now is repeat the whole process to add many more items to the list as I have done.

See below to find out what I have added :-

===============================================================================================

Before we move off the "Database Engine Configuration" screen let us see what the tab called "Data Directories" has on show for us :-

================================================================================================================================================================

The next screen for the "Analysis Services Configuration" is similar in nature to what we have just completed,

so we need say little about it except make sure you add a couple of meaningful users - see below for what I did :-

====================================================================================================================================================================

And now we get the choice screen for Reporting Services :-

You need to keep going as we are almost there - YES - it is a bit tedious I have to admit ;-0))

But if you want to "SQL it" and "ADO.NET from your code" then you have to suffer just a little bit.

You need to be pressing the appropriate buttons at each stage of this install, depending on what is

on offer at the bottom of the screens.

============================================================================================================================================

And, after all this we see a dialog screen which basically says we have not done anything yet - the install system has just

gathered input information from us.

So before we click to start the install itself we see this - see below :-

After clicking 'Install' we have a period when things whir away and hard disk click and clatter etc..

Then we see the following if we are lucky :-

It is now time for us to move on to the next section where we make our first connection and then see data and the likes ;-0)

However, before we do just let me say that it took me almost three weeks to get to this stage when I did it first, some three

years plus ago now. Needles to say I did NOT have some helpful notes to help me along.

See you in the next subsection .........................

PHH - 1st. November 2011.


Making our first connection

For this next subsection we are going to use management Studio - the software tool which comes as part of the

Advanced Services download.

Below is my desktop and how MS looks as an icon :-

When we launch MS by a double click on the icon on the desktop we see the following at startup :-

===================================================================================================

You will notice that I am actually using the version of MS which comes with "Denali" - or SQL Server 2012.

You may not be aware however that we have set the Privilege Level as shown below :-

Now for the BIG shock ! :

After struggling through an installation (or many more as I did in months gone by) I was expecting to see our new

instance in the drop list of the opening dialog screen!

But no this is what we get today - see below :-

It seems silly now, in the cold light of day, but in my haste and desperation to find my "SQL2008_VODCtest"

in the list on offer, I forgot that the last entry of '<Browse for more...> ' is in fact what we need to select.

When we do this it can take a while for the system to search through what is available.

Also we need to uncheck the collapse icon so we actually see the list on offer in the "Browse for Servers" dialog.

Hers is genuinely what I got this morning when at first I did not see it - check this out :-

===================================================================================

Obviously we can now select what we want, now that we can see it - so choose as necessary :-

================================================================================================

Click the 'Connect' button and this is what happens - hey presto !

======================================================================================

The new MS software (which apparently was written in WPF) now shows we have an open server in the Object Explorer (OE)

pane at the left of the screen.

So all we have to do now is attach a database.

For this part of the exercise we will move on to the next subsection.

See you there for more success ...................

PHH - 1st. November 2011.


Attaching a Database

*** notice *** when using Management Studio, you only see databases in the Object Explorer that you have previously

chosen and attached yourself. The MS software shell does not go looking for your SQL databases !!!

The process of making a database name appear in the Object Explorer (OE) under the 'Databases' section

is called 'Attachment' or attaching.

We save we attach a new database to the server instance in focus.

Study the following image :-

==========================================================================

Usually when we do this the default dialog file selection screen looks as follows :-

However, very often we are moving database files from another folder, or usually PC / laptop.

*** WARNING *** before you can copy an existing database (DB) from one place to another, the DB in question

must NOT be attached within a MS shell - even if the shell is closed. So you have to detach first - more on that later.

Here in the following image is a couple of files which make up a database :-

=======================================================================================================================================================

These files were copied from my other PC - the development one - and have come from a folder where me

as a user can have access to reading and writing.

It is important that you have DB files in folders which are NOT restricted for access. This can cause a LOT of grief to you

and make you think that there are all sorts of other problems - when in fact you do not have access.

I have placed my files under the username folder that I am logged on as - i.e.Philp (yes, I know, I left out an 'i' when I was

installing Windows - have just lived with it ever since I realised as by then it was written in other code and script etc..

Let us see what we have then - see below :-

So if we click on 'SmallTrader.mdf' we get the following :-

And simply by clicking the 'OK' button we get some positive results - see below :-

===========================================================================

In the above image I have expanded some regions by clicking with the mouse.

I will now show some data from the Customer table. I right click in the highlighted region to get

the context menu as shown below :-

===========================================================================

Now when we select the option to display 1000 rows we see the following appear :-

==================================================================================================================================================================

Note that a Query tab is automatically opened for us, and the query script and returned data set is displayed

in quite a nice and clear manner.

I will try and show you more about using the MS shell at Stuttgart.

For now we need to see that we can have a second instance of a SQL server open in the OE pane, and

in this case we have a 'Denali' version underneath the Server 2008 instance.

We can also have instances of versions for 2005 Servers too.

What we can't do is have these in an older version of MS software, or attach a 2005 DB to say a 2008 Server, or similar

mismatches.

MS is backwards compatible, but Databases need to be converted. It is not our worry at the moment, we need to focus

on ADO.NET and Data Binding.

Well, "that is that" ... we have come a long way today, BUT, I wish you to realise that everything I have shown you was

done live by me as I captured the screen images and carried out a 'live install'.

The DB files were genuinely moved and attached, and the connection made and ata found - all live stuff.

So if I can do this the so can you ;-0)) Good Luck !

Time to move on, see you in the next section ................

PHH - 1st. November 2011.


Appendix B - SQL Server in the future

Local and Distant :

Soon there is a new version to be released - a full and quite 'new' version of the MS SQL Server, the first for four

years. It is on 'Preview' release at the moment under the code name of "Denali", which is an Alaskan word for "the Great One".

Basically SQL Server 2012 will address quite a few problems apparently. certainly the write-up sounds good.

Just one word of warning - do not install this early version on your PC / laptop unless you really need to.

Quite often it is difficult to remove pre-release software cleanly, and one thing that public releases OFTEN demand is that all previous

pre-release versions are removed from your system - BEFORE - a new and clean install can be attempted.

So that is the local way forward. What of the 'distant' ?

Well, it is now quite easy to acquire a Windows based web provision. By this I mean that Internet Service Providers (ISP) are offering

a package to users based on Windows server 2008 and not Unix.

The Windows based package allows Silverlight to be run from the site, but also has MS SQL Server 2008 R2 available for backend

database provision, in place of the commonly used MySQL on Unix based sites.

The benefit of the MS SQL Server provision is that the installation and setup has already been done for us ;-0))

In addition to a new Windows based site being ready to use (as far as SQL is concerned) it also will have quality tools to aid us

manage our databases and tables. Usually these software shells are very easy to use, and quite powerful as well.

I have yet to explore the possibilities of this new way of doing Internet stuff, but will write some ClickStart eNotes when I do.

A new ClickStart eVolume may have to be entitled 'ClickStartWEB' as it will need to cover quite a few aspects of this new

'flavour' of web site.

ASP.NET and Umbraco are also possible from the Windows based sites. Below is an image of the the ISP site I used recently :-

Version types :

If at all possible use the new "Denali" version of MS SQL Server.

If you are new to SQL then install the CTP3 version (Community Technology Preview) and get into using that.

When the full version is release uninstall and completely remove the CTP3 version and download a fresh and full public

release 'Express' version of Server 2012.

*** Remember *** It is only the server software you are removing, any databases you have made using "Denali" will still

work OK with the full public release. Al you will need to do to see them is use the new version of Management Studio (MS) and

attach the DBs as per usual for MS.

To be perfectly honest I wish I could start completely afresh with Server 2012. Unfortunately I have clients 'stuck' on server 2005

and this unlikely to change I feel. So I need to have MS SQL Server 2005 and all its support software apps as well, on my PC

development platform.

However, since all my own stuff is based on Server 2008 R2, I will make an effort to move myself over to "Denali" completely. As

soon as the public release is made. I will look into the possible ways of converting DB from 2008 to 2012.

Below is the current web link to the site shown in the following image. I have downloaded and installed it onto a separate platform

here in my office - the Management Studio looks like a re-write on WPF technologies - it looks nice.

http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

===================================================================================================================================================================

Find yourself a laptop or PC on which you can try out the CTP3 version shown above.

But do NOT put it on a PC platform where it will be difficult to remove at a later date.

Time to move on, see you in the next section ......................

PHH - 3rd. November 2011.


Appendix C - Serializable Classes

Start with a Simple Truth :

After you have coded for a short while in dotNet and Vulcan you will fully appreciate the benefits of being

easily able to take an in-memory custom object and 'dump' it to a disk file.

It goes without saying that you will be even happier when you can also easily 'point' at the disk file and re-make

a custom object in memory.

This process is called "Serialization and Deserialization" (SnD).

So far in these eNotes we have seen the use of 'SnD' to keep configuration details for SQL server connections.

However, we can design many classes of object to be serializable. We just need to know how to go about it.

How to Specify Our Own Classes :

If our classes are comprised of simple, or basic, dotNet data types then life is easy indeed. It is just when we

start to get fields and properties which are complex data types, that we need to take some care - and do some

initial testing too.

Let us view the serializable class I made for my invoices in the Small Trader app. - study the detail carefully :-

============================================================

Sorry it's so longwinded, but then that's the reality of coding !

Notice the data types are ... String, Double, Boolean, DateTime and Int. Nothing fancy here

at all, in the invoice class for serializing.

Let us now look at how I have used this in the application to dump and invoice object from Small Trader, so it may be

picked up and used from another application called 'BESTinvoicePrinting'.

In the image following, we see both of the two EXE files, one which serializes the invoice object, the other which then

deserializes it for use in the printing process :-

===============================================================================================

You may have realised that the XML file called "Inv2Prnt" is the serialized file, and it's name stands for

'Invoice to print'.

Now let us move on to see how the Small Trader creates the XML file.

How to Use a Serializable Class :

If we study what has been done to print an invoice in the Small Trader application we will start to get a feel for

what needs to be done.

Let us first look at the short method which controls the dumping of the invoice data to file.

Checkout the following image :-

======================================================

The above image shows that we first create an object of a serializable 'type'.

We then populate the fields of this object from suitable and available data objects.

Then, when the serializable object is complete, we process it in a serializable way.

In this example, the last thing we do is to start an external process which is to launch the second application

to carry out the printing.

The purpose of me doing things this way was quite simple, I already had the code module to print an invoice, it

was part of another application I use regularly. So lazy or time saving, whichever you care to label me ;-0))

=================================================================================

===================================================================================

=====================================================================================

How to Deserializise from a File :

Obviously we need to have the serializable Class available in our second application code, it is

needed for the deserialization process.

=========================================================================================

===================================================================

PHH - 24th. August 2011.


Possible 'Gotcha' !

Although Serialization works for me in Vulcan.net, and I have been getting good result for a number of years,

since Paul Piko introduced me to it, I have just found a situation in which i got 'strange' and unacceptable results.

Below is an image of a Serializable class where the properties have been commented out. Study it carefully :-

================================================================================================

I found that with the properties defined (Access and Assign uncommented) I got a double entry in the XML file

of the data I expected.

Although I thought Serialization only 'dumped' the data in the fields, I found that I had an XML file section for both

Seats and aSeats. This was also true for Parties and aParties.

I had first thought me problems were to do with me having the data in ArrayList as alSeats and alParties, before

I changed this. But my problem persisted.

There are a few differences in having to deal with ArrayList, and I will show you later how to write the code to Serialize

these. I got my inspiration from Alwyn's PDF book on Vulcan - very useful it is too.

Now that I have removed the cause for my duplicated data I see the XML file to look as follows :-

=====================================================================================================

Above is the start of the file, and an early entry in the aSeats 'typed' array.

Below is the end of this file and we see an entry at the end of the aParties 'typed' array. See below :-

======================================================================================================

If you look at the image below you will see that the XML file is of size 132 KBytes and not the double of this

that I was initially getting - see below :-

=================================================================================================================

This behaviour strongly suggests to me that it is better to design a class specifically for the job of serializing. By this I mean

that I would create a class solely for the purpose of serializing, and that it would have Export (or Public) fields, and Constructors

only - no methods and properties. Remember it would need to have an 'empty' constructor whatever else I provide - this is

one of the requirements for serializing.

Take the Customer class as an example. I would design and create a Customer class for general use, but then another one,

possibly called 'S_Customer' which would only have the fields for the data.

What I would then do is write some code that would turn the general class of Customer object into an instantiated object

of the S_Customer class. Then I would serialize using this 'S_' class object. Then hopefully any duplication would not occur.

PHH - 24th. October 2011.


Example 1 - Hall Venue Seat Booking

Arrays and ArrayLists :

If we are going to do this then we may as well also make any ArrayList fields into Arrays, as this is easier and more likely to

succeed - as far as us getting the code syntax correct.

Before we move on to using ArrayLists study the code shown below which is used to serialize the class without ArrayLists.

Study the following carefully :-

=============================================================================================

Notice in particular lines 410 through 413.

Getting the Syntax Correct for ArrayLists :

First of all let us look at some success, where we have an XML file made from ArrayList data.

The file is overall in size about the same as previously, but there are some extra characters because of the small changes made.

Study the following image carefully, where we have made a new file called 'HallVenueAL.xml' :-

================================================================================================================

The calling code was used when the main window was closed - check the following :-

===============================================================================================================================================

So it would seem we now need to examine closely the method called 'SerializeHallVenueAL', which

is shown below in detail :-

===============================================================================================

In the above code image we can see that at lines 410 through 413 we have had to add some extra information.

The new class of 'HallVenueAL' does not now have any reference to what type of class of object is being held in

the array lists, and so we need to supply this in an alternative overload to the 'XmlSerializer' class.

It would also appear as though the following code also produces the same XML file content - checkout the following :-

======================================================================================

But if we leave out one of the classes within the ArrayLists we get the resulting error shown below :-

=========================================================================================

Maybe I got carried away by using Alwyn's example too literally - here is what I was following :-

==============================================================================

So if we make sure we supply the extra class types of Seat and Party, all is well for us.

Now we had better take a look at how the new adapted class called 'HallvenueAL' looks - see below :-

=====================================================================================

Although we seem to understand a lot more about serialization, and we have some more complex successes, be

warned that when we get ArrayLists within objects in an ArrayList we have a more difficult and complex situation to

deal with. It may just work fine by adding more class types to the 'aTypes' input parameter - BUT - my feeling

is that making specific simplified classes just for the purpose of serialization is more likely NOT to fail, or let us

down when we need to succeed.

So good luck with your serialization, and remember to Deserialize with the same class as you used for Serialization.

Time to move on, see you in the next subsection ..............

PHH - 24th. October 2011.


Appendix D - Small Trader

The Small Trader application was party designed and made with VODC 2011 in mind.

The nice thing about this application is that the 'Business Logic' layer does not really amount to very

much at all, some would say it was even missing altogether.

The application to accept and track jobs relating to PC and laptop repairs is very much a data input

and storage 'system', with occasional need to display data on screen, and even less often print out a

sheet for the customer and the accounting physical records - we all need to keep the accountants happy !

Because of this limited existence of the Business Logic layer, I can present the application is two separate

presentation sessions - one to focus on the 'Top Half' or "Input & Display" section, the other which focuses

on the 'Bottom Half' or the "Data Storage & Access" section.

Let us study below the schematic of how I see the Small Trader application :-

[ B.O. & BO, is 'Business Object', ST is Small Trader, MS is Management Studio ]

{ to be seen as 'layers' }

1. GUI - Form and its controls

2. GUI/B.O. - Binding BO to the controls

[ .. binding done mainly in XAML for ease of scripting ... point at data source in code .. ]

3. Business Logic - all the business 'stuff' !

4. B.O. - the object class definitions [ .. like 'Customer' and 'Invoice' .. ]

5. ADO.net / B.O. - STATIC class for all interfaces with business logic.

[ .. this layer has the Business SQL statements in it - no others .. ]

6. SQL / ADO.net - STATIC class to interface with SQL server

[ ... this layer has very limited SQL statements, only for 'Error Retrieval' and 'Backing Up' .. ]

7. Server (SQL) - 'out of the box'

8. Database (SQL) - tables designed for ST app and made in MS

*** Note *** layers 1,2 & 4 will be covered in one presentation, and layers 5,6 & 8 the other.

[ There is nothing much we need to say about layers 3 and 7. ]

Structure of Any Application :

It may interest you to know that I now see the above 8 layered structure as the overall for any application I

now design and create using WPF and Vulcan. This structure has a LOT going for it and makes life a

lot easier for the coder / developer, once they have the ideas firmly in their head.

For Your Information :

It is not till very recently that I finalised this eight layered structure, by writing (creating) those numbered 5 and 6.

The STATIC class numbered six (6) is the general class I can now use in any of my future apps, it does all the

standard 'stuff' with the SQL server. No SELECT, INSERT or UPDATE statements relating to the B.O.s are in

this layer.

I now realise too, that I will need to write the STATIC class for layer five (5) for each app that I design. This has

all members relating to the specific BO which are also designed specifically for the application in focus.

If we look at an image of the Solution Explorer for SM we see the following :-

====================================

The two PRG files are in the order 6 then 5 - and the STATIC class for the application specific members will always

tend to be named with that application related name - here 'SmallTrader.prg'.

Looking at this from another Angle :

If you wish, we could look at this from the other direction, so to speak. We could say that layers 8,5 & 4 need

to be considered as a whole - all at once!

While we are solving our development needs without new tools like E.F. (Entity Framework from MS), and are

coding everything ourselves in mainly Vulcan, we need to consider the structure of the data tables along with

our design of the business objects we require, and also the members we create in the fifth layer.

Remember the fifth layer is where we write specific query statements which relate to our table structures. So for

example, if our app does not involve invoicing, then we will NOT have an 'Invoice' table, nor an Invoice BO and also

therefore no STATIC members in layer 5 relating to storing and retrieving invoice data.

Benefits to the Developer :

So what do we gain by doing things in layers like shown above ?

Well, a few things, and one is speed of development, once we have a tried and tested layer 6.

Quite soon we will not get errors arising because of layer 6, it will be tried and tested - quite thoroughly.

Also, it will mean we get accustomed to writing our fifth layers (application specific) since we are then

more familiar with what layer 6 requires us to do in layer 5.

Already I am feeling a speed and ease factor because of the above.

Although the ADO.net code is not rocket science, or complicated, it can be a bit tricky. So the idea of removing

as much of its as possible to a 'one-off' layer is a GREAT idea - wish I had thought of it earlier.

So finally, the benefit is that for the majority of the time we as developers are concentrating on the business aspects

of the solution, and not the 'nuts and bolts' of making it work.

One final benefit, which I quite like, is that all the code is generated by myself, no third party stuff for me to misunderstand.

Also, as things stand for the Small Trader application, all but one class is written in Vulcan - say 98.5% of the code, or more.

I find writing code in VN2 is better and much faster for me than using C#, but I will when I need to, and feel that all of us

needs to be able to read C# so that we can adapt bits for our own use in VN2.

Summary :

So the outcome of all this 'layered' talk is that one presentation at VODC will look at business objects as bound to the GUI

controls, and how to make life easier by using data binding, and the other will look at the data access side of things. The

data access is done using the two STATIC class layers which interface with the same business objects as we have bound

to the GUI.

Now time to move on - see you in the next subsection ..........

Addendum :

It needs to be realised that I started to code the Small Trader app around Christmas time 2009 and so although the

design was always WPF and data binding, not everything has been coded in the style I would use today.

This in itself can be helpful to see how things may be done in Vulcan code, as well as in the more recent XAML script.

I will attempt to point out when there are better, or more recent ways to do things.

In particular, there is one aspect I have kept in the application in order to show possibilities, and that is the printing of

invoices. The way invoice printing is done at the moment is by effectively passing an object from one EXE to another.

This shows how to use older and existing code (the printing EXE is based on WinForms) with a newer program module.

This use of an existing printing module also demonstrates nicely the use of 'Serialization' of an in-memory object.

Finally, on this topic of old and new styles, or coding approaches to a working solution, there are a on-show in the app

a few ways of coding a business object for use with data binding. Data binding in WPF uses Dependency Properties (DP)

and the more fully compliant your BO with DP technology the better.

Sometimes binding works OK, for what the app requires, with less of a 'full' DP approach, and sometimes we even require

a class written in C#. Luckily for us, the need to dip into C# to work around the current slight VN deficiency using events

will soon be a thing of the past. And even by the time we attend VODC in November 2011.

========================================================================================

More Details of Some Layers :

It may be helpful to you if I expand some on what was said previously about the 'layers' that we see

in the make-up of the Small Trader application.

Layer 1. - The GUI :

How much you do for this layer depends on your experience.

It is possible to use the Visual Studio 2010 designer in a simple fashion, by just pacing Toolbox objects on the surface

as you did in WinForms. The XAML script is made automatically for you.

If you have more skills then use placement elements to help you, like grids with rows and columns, as well as others

designed to improve layout and arrangement.

There are elements and techniques available in WPF which are far superior to those we had in WinForms - it may

take a while to discover them, it did for me, and I am still learning.

One personal tip, I suggest you edit the XAML script which is made automatically for you, simply to provide names for

controls which will be more meaningful to you and your dotNet code than the default values.

For example, I would change 'textBox4' into 'tbxAdd3', and 'listBox2' into 'lbxCustomers'. These are then available in

the Vulcan PRG files as 'self:tbxAdd3' and also 'self:lbxCustomers'.

Layer 2. - GUI and B.O.

I strongly suggest binding to GUI controls as often as possible - always even !

Another thing I prefer at the moment is not specifying in the XAML script the source of the binding, I do that in the PRG

file using a 'DataContext' property assignment on the control(s) needing to be bound. This makes it easier at the XAML

end of things, even though it is possible to script it.

Layer 3. - Business Logic

Nothing much needs to be said about this layer - except don't do anything here which should be elsewhere according

to the overall plan of things.

Don't be tempted to 'do a quick fix' and place a method in here which should be with those of layer 5 or 6.

Most often you will use in your 'Business Logic' (BL) the members you create in layer 5.

Depending on your application type your BL could be small or HUGE !! It all depends on your problem.

Layer 4. - Business Objects (BO)

If you can, place your business, or custom objects, in a Class Library. You may make the library after first testing them

in the main body of your application.

Now that I have the benefit of hindsight, I am sure I will always make my main BOs dependency class objects. By that

I mean that I would code them in Vulcan such that they would successfully take part in 'TwoWay' binding if required.

Certainly this would be my approach for BOs like 'Customer', 'JobSheet', and 'Invoice'

Now that I have working examples of DP classes in dotNet and Vulcan, I simply need to carefully copy and adapt.

Making sure that I had the correct data type selected and a suitable default value. See the section on Data Binding

to understand what I mean by this.

Layer 5, - ADO.net / BO [ STATIC class ]

This will take a bit of hard work for each application you develop.

You will need to create methods for each of your BOs - for database / table INSERT and UPDATE operations, as well as quite

a few specific or specialised aspects - like finding the oldest order, or customers not ordering, unpaid invoices, and a host more

stuff like that. Since each of these can be scripted into an SQL query, then a static member can be written.

You will find yourself working in Management Studio a lot while developing and coding layer 5.

These specific members will be used in your Layer 3 - the business logic.

Layer 6. - SQL / ADO.net [ STATIC class ]

Once the standard members of our general STATIC class have been originally created, and then thoroughly tested, there

will be little need to change any code - just use the members as you require them from layer 5.

Layer 7. - SQL Server

Nothing needs to be said about the server, it just works, and works well !!

Choose the most recent and up-to-date if you can, and Microsoft's flavour if at all possible. This will make life easier for

yourself while developing your solution.

Layer 8. - Database, mainly SQL Tables

Before we get the use of any further technologies from Microsoft (like EF - Entity Framework) we will need to make our

own data tables.

Our data table structures will VERY often mirror the BOs (Business Objects) we need to use in our application.

And often the BO will be mirrored in the GUI form itself. Sometimes a GUI may have groups of controls bound to more

than one type of business object. For instance it is possible to have the top left of a Job screen bound to some of the

properties of a related 'Customer' object.

Where will all the work take place ? :

Simple to answer. Layer 7, 4, 1 (with a bit of 2), and 6 - and probably in that order too.

I suppose there is some work in layer 3 to make it all hang together, but the detail in layer 6 will be crucial to the

success of your application.

Most of your problem solving can be done in layer 5 with a bit of thought. Just in the same way that creating reports

with Reporting Services (MSSQL server) requires that 90% of the work be put into designing the query to return the

most suitable data set.

Oh! - Should have said !

It may be obvious to some of you - BUT - it needs to be said, only Layers 1 through 6 will appear in the VS solution.

Also I am 'exploring' having folders in the S.E. pane which are part named 'Layer1_' and so on.

So there will be a group of sub-folders in the SE pane which come under 'Layer1_' and are named after the WPF form

classes - like 'Customer', Invoice, 'Job' etc..

The S.E. Pane - a 'layered approach' :

Take a good look at the image shown below, it reflects this new layered structure :-

In the above image you see what I am trying out, or exploring. A new structure for me of an application

in the Solution Explorer (SE) pane.

Notice that layers numbered 7 and 8 are not present, as these are SQL entities on HDD and not therefore

within our VS file and folder structure.

Let us expand a few sections to see how they look with this new structure :-

And then expanding one of the GUI related form folders we see this :-

======================================================

Each of the folders in the 'GuiBinding' layer will have an XMAL designer file.

Now let us look at another folder, that of the Business Logic - check this out :-

======================================================

There will be a folder for each of the WPF form classes (screens) as well as a couple more,

like 'Printing' which do not have a screen for display purposes.

Note that there is a folder for 'Window1' even though the WPF form is at an application root

level in the structure. See the following image and locate the main form at the bottom - it

is still called 'Window1'.

Since I had a bit of a S.E. disaster in the past when I tried to relocate this XAML file in a sub-folder,

I have left it well alone, as it works fine where it is ;-)

Now checkout the Business Classes layer, and see that we do not appear to have a folder for

Invoices - we can see three others below :-

======================================================

This is simple, the 'InvoiceCS' class we use in the Small Trader application is a C# coded class and

is held in the class library referenced in the References folder - see below :-

===================================

Supporting Classes :

As yet I have not completed the tidying up of my code and files, in the near future I will find each Enumeration,

Structure and Class which is used - and is NOT a major BO class - and place them in the same folder, within

a similar namespace to that required by any scoping conditions.

For this application most will probably be placed in the 'SmallTrader' namespace.

Okay then, after some time away tidying the files, looking for extra classes, and moving them around, we see

the following in the supporting classes folder and SmallTrader namespace :-

===========================================================================

The S.E. pane now looks like this :-

===========================================

So the extra classes, enumerations and structures are in a file called 'Windows1_OtherClasses.prg'.

Okay then you guys, enough of tidying and reorganising, time to move on, the structure seems to

be good enough to try for real.

====================================================================================

How you should use it :

Copy and Adapt, Test and Improve :

Once again, a simple answer.

You should use it as I will, as a working model on which to base my future SQL applications in WPF.

Select the code and script parts which suit your needs best, then take a copy of those sections and adapt

them to your needs.

It is very difficult, or almost impossible, to code off the top of our heads as we so often did it the past. We

MUST have examples to refer back to these days, and the skills we require now are those of being able to

select and adapt suitable code, to suit our needs. And make it work of course.

Be aware however that some parts are older than others, and although everything is basically dotNet 4.0

and WPF, there are some techniques which are more 'modern' and those I would prefer to use myself

over others that are in the Small Trader application.

I think I make it clear in my notes, while discussing each section, those techniques I prefer and recommend.

Getting into WPF :

This is easier than you think. If you follow through many of my working Vulcan examples, in both these eNotes and

the ones in the ClickStartXAML and ClickStartWPF, then you will be well on your way to using WPF technologies.

Remember WPF is really just the modern Windows GUI, and as such replaces the WinForm technology which is now

decades old, and shows it.

Once you get into WPF and get some sucess for yourself, then you will NEVER wish to go back to older GUI technologies.

I know, it happened to me, and I see it happening to VOUG members of my local group based in Bristol UK.

Starting SQL database use :

If you have not got a installed 'instance' of MS SQL Server to use, then install your own.

Use the later sections of these notes to find out how to download an Express version of a recent version of SQL Server.

Then also follow the notes in this eVolume to show you how to install your own instance of SQL Server.

Make sure you get as far as making Management Studio work for you.

Try to copy my supplied database (DB) which comes with Small Trader (ST) - it is a "2008 R2" compatible one, so do not

try to use it with SQL Server 2005 or "Denali".

My advice is to find yourself a copy of Express 2008 R2 and install that.

Once you have the ST data showing in MS you are ready to try and get the Small Trader app to run for you, and also to

start writing your own Vulcan ADO.NET code.

Getting into WPF Collections :

The ST app was done by me n order to get me into WPF and some early data binding, so I have not done a lot within it

regarding collections - as such.

You will on the other hand get a lot more from these eNotes themselves, and the little demo app called 'VODCdataBinding'

will also guide you in the right direction.

"Section K" in these notes is where you can learn a 'heap' of useful stuff on collections.

Start off by reading and studying the 'Ten points about Collections'.

Collections are important in WPF and binding, so make the effort right from the start.

PHH - 9th. January 2012.


Appendix E - Finding Intances of SQL server

=====================================================================================================

============================================================================================================

========================================================================================================

============================================================================================

==========================================================================================================================================

PHH - 24th. August 2011.


Appendix F - STATIC class - making a DLL

PHH - 9th. January 2012.


Appendix G - STATIC class - using the DLL

PHH - 9th. January 2012.


Appendix H - Example scalar Function

==================================================================================================================================================================

====================================================================================================

====================================================================================================================

=============================================================================================================================

============================================================================================================================

Using 'Views' :

This approach allows us to make multiple 'virtual' table objects so that we can use then in complex FROM clauses when

we have many more than can be handled with a single CTE and a simple sub-query or two.

We can even script the query statement so that any 'View' made for the purpose is removed at the end of execution - that

is it is dropped from the DB.

================================================================================================================================================

==================================================================================================================================================

PHH - 21st. January 2012.


Appendix I - Cursors

*** See the subsection on 'Printing' and simple output of information - this will be useful for Cursor work. ***

Personal Note of Caution etc. :

Because I have been convinced by Paul Nielsen that a Cursor has a VERY limited place in SQL Sever use by

developers, I will not cover the subject in as much depth as other aspects of T-SQL scripting.

However, we do need to know how to use it, and also where to use it, because there is no 'set based' alternative.

[ .. by the way Paul Nielsen is a Microsoft MVP .. ]

================================================================================

=========================================================================================================================

Paul Nielsen (Microsoft SQL Server 2008 - red Bible) suggests that in 99% of cases there is a better and faster

'set based' solution and that we as students of SQL Sever ought to develop our knowledge and skills so that we

turn to the set based approach - rather than stay within our comfort zones if coming from the world of ISAM and

record and pointer based processing.

In my opinion, after following his books and DVD training material for years, he is correct and totally justified in

what he advises.

More than that though, I have found that if we as users don't know enough of the 'set based' approach to be able

side-step times when we may have reached for Cursors and Temp tables etc., then we simply don't know enough

to be using a modern SQL Server DB engine, and one shared by others.

If we as developers go out of our way to find the correct way to use Objects and OOPs, as well as finding the correct

way to use the most appropriate .NET classes and methods, then why shouldn't we embrace the language of

MSSS itself, and use 'set based' algebra both fully and correctly as well ?

Syntax :

Although MSSS does, or accepts, both ISO - SQL-92 and enhanced T-SQL syntax, I will only focus on the later in

my eNotes - see below :-

================================================================

You will have to consult the SDK documentation if you wish to study and know all about the various parameters

and options.

In the examples shown in the eNotes I will use the commonly used ones - nothing too clever, or fancy.

Locks - be Careful :

You need to be aware that some options set on the 'Cursor' can cause locks to be placed on data rows in the database.

Try to see your options used to the minimum your need, to get your script to work. Do NOT opt for 'maximum' settings as these

can cause problems due to locks being left 'closed'. Here I use the term 'maximum' for most flexible - a bit like 'exclusive' on a DBF.

Check out a few lines of SDK documentation where locks are mentioned - see below :-

=============================================================================================================================

And another, but related, section is here below :-

==================================================================================================================================

?? use of ' WITH (NOLOCK) '

and my personal examples.

PHH - 22nd. January 2012.


Printing values - simple output

In case you have never tried this in Management Studio, here is a way of outputting information :-

===================================================

And going one step further we can use variables as well - see below :-

============================================

And even going that much further we have the following possibility - check this out :-

====================================================================================

Finally, for these simple tests, we can overcome our earlier problem by casting our data types as shown in the following example :-

==========================================================================================================================

Notice in the above script adding and integer to a 'DateTime' type increments the 'day' count accordingly.

So June 1st. must be 131 days on from January 22nd. - well I never ;-0))

By using 'max' for our 'nvarchar' specification we handle any length of whatever the string may be. However if you have a definite

or defined space for printing in the use something like this below :-

==============================================================================================================

Having shown how flexible this is just *beware* as sometimes when limiting the length of output we can generate

errors from the parser compiler.

In the example below all I have done to the script above - which ran successfully - is change '3' into a '2'. Check

out the following image details :-

=============================================================================================================

So now you know - just be careful !

PHH - 22nd. January 2012.


Anatomy of a Cursor

In the 'red Bible' Paul Nielsen has this to say (with my additions) :-

A 'Cursor' is essentially a pointer to a single row of data.

Commonly, a WHILE loop is used to cycle through the data and each row of a data set read into the

'Cursor' - until the end of the data set is reached.

Each time the 'Cursor' is read, and updated with new values, some computation, or processing, is carried

out - this may be INSERTing into another table, or REPLACEing table data with newer stuff.

The Five steps to 'Cursoring' (as he puts it) :

1. Declaring the 'Cursor', this sets both the 'Type' of cursor as well as the SELECT statement from which

the 'Cursor' will pull its data - one record (row) at a time.

2. Opening - this gets the data defined in the SELECT statement and fills the 'Cursor' with the first row.

3. Fetching - this commonly moves the pointer to the next row and retrieves the data, inserting it into the 'Cursor'.

4. Closing - when a cursor is closed the data locks are released but the SELECT is retained. The 'Cursor' may

be opened once again if required.

5. Deallocating - this releases all related memory, and the definition of the 'Cursor' is removed / lost.

We need to see this stuff in action ............

Simple Example of a 'Cursor' in use :

First let us look at some simple data in one of my existing tables - see below :-

==================================================================================

And now we can examine in detail the following simple test for using a cursor - check this out :-

===========================================================================================================

Well, it seems to have printed the first row from our returned set from the SELECT statement - see that we asked

for it to be in descending order - large to small.

However, you will also see that as yet we have no looping included in our script, so we only get one printed answer.

Before we move on please also take note of the fact that we have a declared variable for each of our fields specified in

the SELECT statement, and these are in corresponding order.

Going Further - Looping :

Okay then guys, let us now employ a loop so that we can access each and every row of our chosen data set - that

returned by the specified SELECT statement - see below for details :-

==========================================================================

What if we have an empty returned data set ? That is no rows at all ?

Checkout the following two images for details :-

=================================================================================

And now the 'Cursor example with the same WHERE clause filter condition :-

==========================================================================

As you can see, we don't get an error, but we don't get any printed information either !

So this lopping structure would seem like a good one to stick with for other trials.

At this point it is probably a good idea to see what a 'STATIC' type of cursor is :-

STATIC :

Defines a cursor that makes a temporary copy of the data to be used by the cursor.

All requests to the cursor are answered from this temporary table in tempdb;

therefore, modifications made to base tables are not reflected in the data returned by

fetches made to this cursor, and this cursor does not allow modifications.

PHH - 22nd. January 2012.


Updating Table Fields

Okay then guys, let us try and put some of our simple knowledge about a 'Cursor' to good use.

Let us try to set up a simple Temporary Table and update vales in that from those we 'FETCH' and read

from our specified data set for our 'STATIC' cursor.

Once we get the hang of this we can try to be more adventurous - see below for details :-

===================================================================================================================

*** Note *** if you get to a stage in developing and testing where you get an error message like this appearing :-

=======================================================================================

It is simply because you have not 'DROPPED' the Table from the last test or trial.

The solution is to simply run one line of script, that where the 'DROP TABLE' is listed :-

=====================================================

Then after running this one line, run the whole script once more and if you have not missed out any lines then you

will get success ;-0)

To see how to update the same table as the one upon which the 'Cursor' is based, see the next subsection of these notes.

See you there ..............

PHH - 22nd. January 2012.


Updating the Table of the Cursor

====================================================================================================================

PHH - 22nd. January 2012.


Updating at the Cursor position

=======================================================================================================

=========================================================

PHH - 23rd. January 2012.


When we MUST use a Cursor

PHH - 22nd. January 2012.


Appendix S - the SELECT statement

What is a SELECT ? :

This is a simple answer.

The result of specifying, and using, a SELECT statement is data.

Yep! the data will either be a single value (scalar quantity) or a data set in the form of a table.

So wherever one of these types of data is allowable, then a SELECT statement may be used.

You will see this frequently in more complex statements, and we call them 'sub-queries' and 'correlated sub-queries'. But

once all is said and done they produce (and result in) a value or table.

Obviously, the type of sub-query must match the type of data required by the structure o the statement, and where it is used.

One special case exists and that is when 'IN' is used the SELECT must produce a single columned table - referred to as a 'List'.

This also applies to SOME|ANY and ALL, as they require List as well.

Contribution by Paul Nielsen :

In his DVD training material and both his SQL red Bibles, he says something like this :-

"The key to unlocking the power of SQL Server is a deep understanding of the SELECT statement."

After three and a half years trying to get to grips with SQL Server I see what he means - and guess what, he is right ;-)

We can dissect this statement as much as we care to do so, and try to pinpoint what it is that we are NOT supposed to

do if we run away from gaining a 'Deep' knowledge of the SELECT statement.

One obvious behaviour of new guys is to turn to the use of Temporary Tables (TT) when their skills with SELECT are limited.

So if ever you are thinking about using TTs go first and see how you can do the same thing using SELECT in a more advanced

fashion than you had previously thought of.

Where are we headed ? :

In this appendix we will explore all of the common ways of using SELECT as possible.

The subsections will be more numerous and structured than other chapters and appendices, so that we can more easily find and

refer to what we wish to consult while doing real scripting and dotNet coding with ADO.NET.

However, I think that this appendix should be also read through like a normal chapter, for those wishing to learn generally about

using the SELECT statement.

Good Luck on your journey, and remember, it is a 'DEEP' understanding that we are trying to achieve.

Future Cross-check :

When you have been using SQL Server and T-SQL script for some time, ask yourself these questions.

A. Have I ever used Temporary Tables ?

B. Have I ever used a Cursor ?

If the answer to both is no then you are probably starting to do things correctly, and you are moving towards that 'deep' understanding

that we are all try to attain.

Syntax or Format of a SELECT query :

Use of square brackets - []

Study this query shown below :-

=========================================================================

Notice that I have used square brackets to surround both field names as well as table, schema, and database names.

This is useful when field names have spaces and special characters, but also I feel it helps improve the way a query reads.

To my eyes I seem to find it easier to mentally parse, or understand, when the [ ] (brackets) are used.

What goes Where ?! :

It helps any guy coming new to SQL, and writing SELECT query statements, to realise that there are places throughout the

statement where scalar ( or single ) values are expected, and others where data sets (or tables) are required.

Anything which resolves to a scalar value or quantity may be placed where a scalar value is expected, even another SELECT statement.

This also applies for data sets, or tables (and lists) - once again a SELECT statement which resolves to a data set, may be used where

a table is expected.

There is a variation of the latter statement (above) where we expect a List in the ' IN ' command, and as long as the SELECT statement

resolves into a List (single column table) then it will be acceptable, and hence parsed by the Server.

Good Advice - take it ! :

Whenever you need a data set returned from the Server then experiment fully using Management Studio (MS).

Script the SELECT query statement in MS and check it out thoroughly to see that it provides what you require.

Then when you are happy that the query script does as you wish, copy it into you ADO.NET code which you are developing.

As a rule of thumb, the more complex the SELECT statement the more testing and checking you need to carry out.

But even that rule is not always correct, as you can see with the simple script for 'TOP 100 PERCENT' when rolled up

into a 'view', or 'stored query'.

Be Aware - Ignore the Red Wavy Line ! :

For some reason the Management Studio 'shell' app reports an error / warning by showing a red wavy line as seen below :-

==================================================================================================================================

This seems to happen on my desktop, the way things are set-up - HOWEVER - the script runs fine, without problems.

Here is another example where the tooltip is showing :-

=====================================================================================================================

If I were to guess, I would suggest that it has something to do with the way the MS 'shell' has attached the database - with a

full file path included in the name.

I just wish you to know that I have not messed with anything, and let MS have its head - do as it wishes. The DB name was

put there by it, just as it is. And since everything seems to work OK - apart from the editor 'parsing' warning - then I leave

well alone ;-0)

PHH - 11th. January 2012.


Simple - one table

Okay then guys, let us get started.

First of all we will use one rather simple table for our first tests and trials. At lot can be learned about SELECT by

even using a single table.

One of the simplest 'structures', or formats of the SELECT statement is seen below - study the following image :-

=========================================================

The star or asterisk - ' * ' - following SELECT mean return all the fields possible, and in the order of the table definition.

So if we see the table definition below we see the natural order :-

==================================================

If we now examine the full range of the headings of the returned data set we see this below :-

==============================================================================================================================================================================================================

Notice that I have had to collapse some of the fields somewhat, to allow it all to fit onto the screen / page.

However, if you compare the order you will see that they match up correctly.

More Information :

In case we get fixated about the data rows and column headings etc., we need to be pointed at the very bottom

right of the 'Results Pane' in management Studio (MS).

Study the detail of the following image and see what can be seen at bottom right :-

Yes, we get more information about the number of rows returned, and the time it took the database 'engine' to

find and return the results of our query.

Short a simple queries always appear to take no time at all - ZERO ! However, this will soon change ;-)

At this point it is useful to show that immediately after a query data set has been returned, we can access from the

Server the number of rows by sending a subsequent SELECT statement - study the following image :-

===========================================================

'@@Rowcount' is a system function and is grouped under the node in Object Explorer (OE) as 'Other Functions'. It only return the

correct value is used immediately after the query we are interested in.

Do it a second time and you will get 1 - try it for yourself, and then work it out why !?

Here is the OE display of the Functions :-

=============================================

These are to be found in each DB under 'Programmability', and then 'Functions'.

Expanding the node of the tree we see this in the following image :-

=====================================

So the function returns an integer and takes no parameters.

Moving on :

Moving on we see that in the image below we have two ways of specifying the same simple query in the MS shell.

The first way we provide a full database, schema, and table specification within the FROM clause of the query statement.

In the second way we specify the database (DB) separately with a 'USE' command. The subsequent SELECT statement

therefore only needs schema and table to be specified.

Before we go further checkout the image below :-

==============================================================================================================================================================

However, if you try to shorten this any further you are likely to see the following type of information in your MS shell :-

==============================================

As you can see from the above image, the MS shell does not know which DB to access, or use.

If we first run a query from the four lines shown below, and then try the two line query once more, we

do get a result this time. Checkout the following image detail :-

========================================================

For this reason I always try and supply a full object specification for my Table objects, it always works, no

matter what.

Also, when you get to working with client's real life DBs you find that you may often use two, or even three, different

databases in one query - in other words we get to joining tables in different databases.

Under these circumstances it is necessary to specify a full object 'name'.

Here is an example from my client's DB related script :-

==========================================================================================================================================================

In this genuine 'view' (remember a 'view' is a stored query) from my client's database, we see a FROM clause in which we

are using both 'pelican' and 'SAGE1000' databases, as sources of table data.

Also note in the above image that we are careful to use correct casing - as some client servers are case sensitive, and

so it is best to be 'correct' with our casing all of the time - everything we do.

Having said that the TSQL commands can be either upper or lower case, the SQL server engine does not seem to mind

that commands have mixed case. I suggest you are careful, and keep all data and named fields, and DB objects, 'case correct'.

SELECT from the MS 'shell' :

There is an easy way to make a simple selection from any table, and that is to use the context menu on the table node from the

'TreeView' pane in Management Studio.

In the image shown below I have used the 'Select Top 1000 Rows' option - check this out :-

==========================================================================================================================================================================

You can see from the script which is auto-generated that the format of the query statement uses 'named' fields and

not the asterisk (start), or ' * ' .

I have to confess I usually start by doing this even on a table where I am to develop quite a complex query.

The following shows the basic query for my table entitled 'BatchHeader', and retrieves all of the rows :-

======================================================================================================================

As you can see we only have three - that is nice and easy to work with here in these eNotes ;-0)

Choosing the output 'Fields' :

The first part of most SELECT query statements is to do with choosing which columns (or fields) are to be returned from the

Server, and in which order.

Please note from the very beginning that we can have fields which exist in the data table, as well as adding new ones which do not.

First of all let us phrase our query statement so that we only select three columns, and in a different order to the natural one in

the table - checkout the following image :-

===============================================================================

You can see we have not had returned any data for fields named 'Processed', 'NumDumpSets', or 'NumSensors'.

Extra Fields :

Let us now focus on the topic of fields 'extra' to those columns existing in our data table.

These extra fields are often called 'calculated' fields, even when we can insert constant values, as shown below :-

============================================================================================

In the above image you can see that we have also given the extra field a name , in this case it is 'MyHeader'.

Before we move on, let us make a simple calculated field as a test or experiment. Checkout the following :-

==============================================================================

Later we will see how we can do much more with our 'extra' fields, even using Functions and input parameters

from the row values themselves - this is a very powerful feature.

Finally, we can take this a bit further and use three separate fields in our calculations - see below :-

====================================================================================================

I think you are getting the idea of making new / extra fields ;-0)

For now let us move on.

Filtering - Using the WHERE clause :

For now lets skip over the FROM clause, which is used to point at tables to be used, and focus our attention the

WHERE clause which is often called the filtering clause - this chooses the rows of data to be returned.

Look at the following SELECT query, which tells the Server to ignore all rows with a 'Batch_ID' field value of over

the 123 value - check this out :-

===========================================================================================

And yes, it is gone - we only have two of our three original rows, or data records.

The next example shows how we can combine two filter conditions on the same field to set a range limit. However,

do note that there is an easier way to do ranges using 'BETWEEN' which is covered in the next subsection.

Below study how we have removed the highest and lowest valued rows, on the [Batch-ID] field :-

===========================================================================================

Lets move on to another aspect of simple querying ...............

Unique - Getting only single values in our sets :

It is a very common requirement that we need to have a list, or set of rows, in which a certain field 'value' appears

only once.

Below is a simple example of this situation - check it out :-

==================================================

And of course we can then sort these unique results into some order :-

============================================

That is easy to understand as it is just a single column, but what happens when we have more than one .....

See below for an answer :-

==========================================================

In the example shown above we have specified a pair of fields BUT because our example is not a good one, we

can't really see what is going on :(

If we choose another, and better example, we can see more easily what our returned data set 'means'.

Study carefully the image shown beneath :-

=========================================================================

If we look carefully at the above rows we see that we have a number of pairs where the value of the [Temp] field is -113.90

and one of these has a [Freq] value of 3306.

It would appear as if what we have as DISTINCT is the pair of values. and NOT what you may first think, a value of Freq to

give with distinct values of the Temp column.

Let us do another test to see it there are any other values of 3306 for the [Freq] field - see below :-

==========================================================================

Yep! there are ten in total, and so another nine is the answer to our question.

So basically we have a whole bunch of pairs of values where the value 'pair' is unique.

Let us move on - we need to move to a new subsection, see you there ........................

PHH - 11th. January 2012.

Addendum :

Let us see a simple example of how we can put a sub-query into the fields selection part of the SELECT statement.

Checkout the following image details :-

=============================================================================================

Each time the Server selects a new it runs the sub-query to find out the number of jobs that exist in the 'ServiceJob'

data table for that customer.

You can see what is going on if we isolate the single sub-query for the customer with ID equal to 103.

Checkout the following :-

=================================================================================

The result in the above image fits the output seen in the image one above it - see row 4 which is

for customer 103, and shows 7 jobs in existence.

PHH - 13th. January 2012.


Sizing - returned data set

Returning a Specified Size of data set :

We need to know, even if we don't often use it, that there are a couple ways of limiting the size of the returned data set.

TOP and DISTINCT are the command words to be used, and TOP can be used in a couple ways as well.

Let us start out with the "TOP 'N' " format, or syntax :-

==================================================================================================================================

In the above image we are asking for only seven rows to be returned, and since we have not specified any order of the rows

before the top 7 are selected, they will come back in any order that the Server decides is easiest.

If in fact we wish to have the top few rows of a list of records in a specified order we do something like the following :-

============================================================================================================================

Notice that in the above image we are using the order set by [Sensor_ID] in decreasing value.

Now let us try something a little more interesting ;-0) Let us use [Reading_ID] and chose not a fixed number of rows, but a certain

specified percentage of the possible return set.

Check this out below :-

===========================================================================

It would have been easy to use ' 5 PERCENT ' in our example, but because we have so many possible rows I have done

something more unusual and selected '0.005' % instead.

The second query is to show you the number of rows, which is nearly 84 thousand.

Also notice that this time we have used the ordering field to be [Reading_ID] field.

Lets move on a pace ....

Order of Returned 'TOP' sets :

============================================================================================

PHH - 14th. January 2012.


Between, Like, In, Exists, Any|Some ....

PHH - 11th. January 2012.


Between - clause

===============================================================================================

=============================================

================================================================================

PHH - 11th. January 2012.


Like - clause

==============================================

============================================

===========================================

===============================================

==============================================

PHH - 11th. January 2012.


In - clause

Note that as well as ' IN ' we can have ' NOT IN ' - and using the correct one at times can definitely improve performance, so be aware,

particularly when the search list gets long.

This is what the Help SDK documentation has to say :-

=======================================================

IN (Transact-SQL) :

Determines whether a specified value matches any value in a "sub-query" or a list.

"sub-query" :

Is a sub-query that has a result set of one column. This column must have the same data type as 'test_expression'.

*** Remarks ***

Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632.

To work around this problem, store the items in the IN list in a table.

===============================================================================

Error 8623:

The query processor ran out of internal resources and could not produce a query plan.

This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

===============================================================================

Okay then, doesn't sound like much - BUT - it is extremely useful when designing SELECT query statements.

Let us do some testing and experimenting. Look at our first simple test shown below :-

=================================================================

The List 'content' of the IN clause is a 'List' (single column table) and here we specify two constant values.

Note that we have eight possible different values for salutation in our data - within the table.

In the image above we get all the rows returned where the Salutation is only "Ms or Mrs". However, we

can get the 'other' records by simply adding a ' NOT ' - see below :-

==========================================================

This works fine, but of course we can also use as a 'List' a one column table / data set we make using a sub-query.

Checkout the following, the full contents of the braces ' ( ) ' is the sub-query.

==============================================================================================================================================

This is just what we may have expected, but just to check, let us see what the sub-query produces when we

use it on its own - see below :-

======================================================

That looks good, and was the list that was being check out for each row. I think you will see that if the

most common (or frequent) items appear near the start of the list then the speed of querying will increase - or should

we say the time to query will diminish, or go down.

Going a step further (getting clever?) :

Okay then you guys, we have used a sub-query as our list for IN, but this was a one-off 'static' result.

PHH - 11th. January 2012.


Exists - clause

This is what the Help SDK documentation has to say :-

EXISTS (Transact-SQL) :

Specifies a sub-query to test for the existence of rows.

==============================

sub-query :

Is a restricted SELECT statement.

The COMPUTE clause and the INTO keyword are not allowed.

For more information, see the information about sub-queries in SELECT (Transact-SQL).

PHH - 11th. January 2012.


Some|Any, All - clause

Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.

================================================================================================================

PHH - 13th. (Friday) January 2012.


Simple - using 'Views'

Every database (DB) in SQL Server has a node in the 'TreeView' pane of Object Explorer which is

entirely for 'Views',

'Views' are both system created and also user generated.

A user defined 'View' is really "just a stored query".

A 'View' is often called a 'Virtual Table', simply because it can be used in T-SQL script anywhere a Table may be used.

'Views' are really about security of the data. You can give users rights to views when an underlying Table is NOT within

their access rights.

Many of us will start our SQL careers without the restrictions of access rights being applied - BUT - although this makes

our lives easier in many ways, we must not forget that many design features of SQL are about 'security'.

So, still on the topic of security, a user can have rights to use a specifically named 'View' of a Table, and yet have no rights

to use the Table itself.

The topic of security is to be covered in the T-SQL eVolume of the 'ClickStart' eNotes.

Now, back to our Object Explorer pane, and the 'Views' node :-

Study the image below of the OE 'TreeView' pane for the 'Small Trader' database :-

================================================

** Note ** that in the image above we two user defined 'Views', and later - beneath - we have a third which

has been added.

Let us now try and 'script' out the '_phh_vTest14' view to see what it looks like - see below for details :-

================================================================================================================================

And after the script is displayed (it always comes out messy, with little formatting) and I have tidied it in the

editor just a little !

It then looks like the following :-

============================================================================================================================

In the image above we have a few things of which to take note. The first is that all the columns are 'named' and any ' * ' used

when the 'View' is created is converted when saved by the system to be part of the database.

It is necessary for the final (and stored) version of the 'View' to have named columns as the security system works with named

columns. For example the security system may see that the column named 'EmployeePay' is restricted to administrator use,

so it would make sense for all fields used to be named ones.

Also notice that we have a 'TOP ...... ORDER BY ... ' structure. This is the only time that the ORDER BY clause is allowed, as

part of the TOP N [PERCENTAGE], and not as a clause on its own.

==============================================================================

*** WARNING ***

Almost everywhere (99.99%) we use a 'view' it produces the same data set as when we use the script in the

'view' separately.

The exception is when we have a query structure using TOP, under certain conditions ONLY.

Look at the details of this under the earlier subsection entitled 'Sizing - returned data set'

==============================================================================

Let us look at some examples.

Remember, an 'ORDER BY' clause should be the very last one in the T-SQL script, and only one is allowed in

the query statement. Check out this carefully :-

==========================================================================================================

The image above proves we can't have a single 'ORDER BY' clause in a 'View' - so not all queries can be stored

in the database as a 'View'.

As said previously, this is not the case if the ORDER BY is part of the 'TOP N ....' structure.

=======================================

*** CARE *** Some possible confusion !!!

The use of '100 PERCENT' in the 'TOP' sub-clause.

=======================================

It would appear that the Server detects when '100 PERCENT' is specified and ignores the 'ORDER BY' part of the

script altogether. We will run a couple test to see what is going on.

Below is a script which we will use to make a new 'View' - first lets run it in a query pane within Management Studio.

===========================================================================================

Notice that the 'CustomerRef' column is in order as we may expect - BUT - it may be because the

'ORDER BY' clause is last in the query statement.

Lets do another test, one in which we roll the same script into a 'View'.

And now lets run this as a 'View', after it has been 'Created'. Notice particularly the order of the

'CustomerRef' column - or lack of it !. Check this out :-

=========================================================================

Yes, the order of the rows is different - BUT - to my mind the SQL 'rules' are still being applied.

The way I read things, the SQL Server will only return a dataset in a specified order if an ORDER BY clause

is provided and is the last clause of the query statement.

If you look at the last query - which uses the 'View' as a virtual Table - there is no ORDER BY clause, and

therefore we should expect the Server to give us an unspecified order in the returned dataset.

Changing the 'View' script - slightly :

Now if we make a small but very interesting change to our script within the 'View' we see a different results

dataset returned when the query is run - see below :-

=====================================================================================================

In the image shown above we still get the whole 14 rows of data, but this time it is in order of 'CustomerRef',

low to high.

And in the following version of the script for the same selection of rows we see the following :-

=========================================================================================

Once again the above image shows we have an applied order when the 'View' is run - BUT - remember, I see it as just

'good fortune', since we have no final 'ORDER BY' clause then we can't expect anything other than an unpredictable

order from the query's returned dataset.

Some other Facets (sides) of 'Views' :

Let us get away from orders of rows returned, and look at some other aspects of the 'View'.

One thing I like about making a 'View' is that I think of it like an object in dotNet is some ways.

If you look carefully at the following 'real life' view from my client's DB we can see that 'Active Patients' can

be seen as a one-off virtual Table, when in fact the underlying script is quite complex.

The returned data set to this stored query (remember that is what a view is) is patients who are active as

customers (or consumers), but we need to join three tables in order to find the information.

Also we are joining two tables from one DB (called 'SAGE1000') and one table for the DB called 'pelican'.

On top of that we have a couple of filter conditions as well.

===========================================================================================

However, once we have 'crafted' this view, and tested it thoroughly, we can then just simply use it as

a table of active patients - as we have entitled it - see below for how this makes things MUCH easier :-

=========================================================================================================================================================================================

In the above image we can see that by hiding the complexity we can now focus on the other (and important) aspects

of the query.

So for my point of view as a developer and coder, I find I use 'Views' to make my scripting tidy, easier to read, and also

if designed right easier to maintain too.

An Important Suggestion :

Remember that a 'View' is stored in the database (DB) itself, and like all stored DB objects ought to be general in nature.

Try to design your Views and Functions so that they do a general type of job or task.

Taking my trivial example above - do NOT make a 'View' to return the patients with names starting with 'B'. If you wish to

do something like that then write a Function to accept a letter and return a set of rows where the Surname field starts with

the input parameter.

That way you can use it (the Function) for any letter of the alphabet. In fact if we do this it would be even better if we

designed the Function to accept more than one character in the search string.

Making your DB objects general in nature is considered 'Good Practise', and will help to keep your DB from being cluttered

up with lots of rather 'one-off', or very specific objects.

Joining :

Don't forget that if a 'View' is seen as a virtual table then a 'View' can be used with the JOIN sub-clause, within the FROM

clause.

Time to move on .... see you in the next subsection .................

PHH - 17th. January 2012.


Scalar quanity - single value

One simple way of looking at our query statements is to think of them returning from the Server either a single

value - called a 'scalar', or a data set which is generally a table for many of us.

Let us discuss the details of scalar value and dataset / table, to see just what we can have.

Oh! by the way it is important for us to know about this as the choice of method using ADO depends on us

knowing what will be returned.

Scalar :

When we script a query such that we expect a single data value to be returned, of type INT or DATETME, or

NVARCHAR we have a scalar. This is NOT to be confused with a table with one row and one column.

Let us have a look at some examples :-

===================================================

In the above image we have asked the Server to count the number of rows in the table - it will be the whole table

since we have not applied a filter condition using a WHERE clause.

Probably a better name for the output is shown below :-

==================================================

If we apply a filter condition we get a lot less in our count - well, depending on the condition that is !

See below to see how we have remove 7/8 ths. of the table rows :-

=========================================================

Going Further :

Since a query returning a scalar is actually a 'value' then let us try to use scalar SELECT statements directly as values.

Look at the following :-

===================================

As it is scripted above the query will not work, simply because I have used 'x' to represent a value, one to be

determined by a SELECT statement.

Before we correct this and run it to get a resultant value, note that the answer ought to be three times that of the 'x'.

================================================================================================

Okay then 83,808 * 3 is 251,424 and if you don't believe it try the Server - see below :-

====================================================

Another Data Type :

Let's try a 'DateTime' value as a change from above.

===================================================

=======================================================

Let us prove we have two single value types from our above queries.

We ought to be able to get the difference of two 'DateTime' values and generate a time difference.

Note we have a date range from October 14th. through till November 7th. - and that is 24 days inclusive,

according to my fingers.

Lets give it a whirl !

========================================================================================================================

We had better look at the System Functions in the OE pane in MS - check this out :-

=========================================

Yep! quite nice really - and note the 'diff' from DATEDIFF function is inclusive of the limits !!

Summary so far :

Wherever we need to supply a value type, for a function or other, we can use the whole SELECT statement in braces, as the

actual value.

It can get confusing with all the script text - BUT - start out with a schematic script like that shown below - then add the

copied SELECT statements from the script which has been first tested out for accuracy :-

======================================================================

The 'x' represents the early date, and the 'y' the later date.

Now just copy and paste the tested queries to suit - I like to remove x & y first. I also like to format

the syntax of my query so as my eyes scan it more easily - as in the form shown a couple images above.

If for any chance the SELECT gets far too big for comfort, then make it into a 'view' or an in-line Function first. Then

use it in your main query statement.

Look out for this sort of thing in the View and Function subsection of this appendix.

PHH - 11th. January 2012.


Whole data set - Tables and Lists

By whole 'data sets' I mean returned data from a query which is NOT a scalar quantity - not a single 'value'.

The most commonly used 'data set' is the 'Table'.

Table :

By table we don't just mean a table with rows and columns - BUT the other extremes also.

We may get a table with only one row, or even none at all, and empty table, no rows and no columns.

However, if we have one column and many rows, we call this a list. And lists can be used in query script

in places we can't use tables - so we need to know what we are about.

And believe it or not a table with one row and one column is still a table, and not a scalar. It all depends on

how we ask the question - or script the query.

List :

Although a list is just a table with a single column, there is at least one place in TSQL script where we MUST use a table

with a list structure, and that is in the ' IN ' clause.

Other similar times are when we use the ANY / SOME, and ALL sub-clauses.

If we use a SELECT sub-query the structure of the returned set must be a list, or single column table.

Now look at this :

===============================================================================================================================================

================================================================================================================

======================================================================================================================

Effectively you can have an order in a CTE if you use TOP and set the number to be 'huge'.

Whether you really want to do this, or that it makes sense is not the question here - it is that it works.

Better formatted as :-

=======================================================================================================================

I think a better caption for the above is "Okay, if you want silly, I will give you silly! ".

PHH - 11th. January 2012.


CASE - in Fields specification

Enter topic text here.


Sub-Queries - Select within Select

PHH - 11th. January 2012.


Standard sub-query

==============================================================================================================================================

=====================================================================================================================================================

===========================================

=================================================

PHH - 14th. January 2012.


Correlated subquery

The Correlated sub-query is also known to some as a 'Repeating Sub-query'.

Take a look at what the SDK documentation has to say of this topic :-

================================================================================================================

Okay then, so we have a sub-query in the WHERE clause, but in this example one of the 'inputs' or 'parameters' for

the sub-query is from the 'row in focus' of the outer query.

I think the best way to get a better feel for this is to make up our own example using the 'Small Trader' database.

Building our own example - Correlated sub-query :

Okay then, I can tell you that this is NOT as difficult, or as complex, as you may think it is. So let's give it a

really 'good go' !

First, let us see and try a simple query which will find the jobs existing in the DB for a specific (single) customer.

Checkout the image below in which we can see all the jobs for customer with unique ID of '103'.

====================================================================

The query in the above image will be the basis of the one we use as the correlated sub-query later on.

When we use this query, in place of supplying a fixed, or constant, customer ID, we will provide the relevant field value from

the outer query, the field value of the row in focus.

Let us now move on to see the full query, complete with sub-query - there are two 'flavours' of this main query, one which

provides a returned data set of customers with jobs, and one which returns all those without jobs.

Customers with Jobs :

Let us start by examining the script for the case of customers who have jobs in the database.

Study this following image carefully :-

========================================================================================================================

In the above image script we can see that the value of ' C.[UnRef] ' actually comes from the row in focus, this is the value which

will probably change every time the Server processor the 'next row'.

The 'Exists' sub-clause simply makes a 'TRUE' condition if anything is found by the sub-query.

So we could read the main (or outer) WHERE clause as ... if the customer in focus, because of the row we are examining, has

any jobs in the data, then include details in the return data set.

We can now move on to see the 'other set' ...............

Customers without any Jobs :

In order to get the other set of customers, those without jobs in the data table, all we need do is use the existing script

and add a ' NOT ' in the appropriate place. Yes, I know we can get the same results data set in other ways too - using different

scripting techniques - BUT - the subsection of the eNotes is about using Correlated sub-queries so study the following image :-

==========================================================================================================================================

As you can see these customers did not appear in the previous set - or in the 'ServiceJob' table as shown below :-

============================================================================

Checkout the Customer numbers for yourself ..... AND ..........

Before you ask ! :

There is not a 104 or 108, and so these reference numbers can't be found anywhere, in either data set !

Adding a second Correlated sub-query :

Let us now take our example a step further, and output in the returned data rows a value for the number of Jobs for

each of the customers pointed to by the row in focus, the row being processed, as it were.

Study the following image to see how we now have a second correlated sub-query, but this time in the fields specifications.

Check this out below :-

===========================================================================================================================================

The 'shape' of our SELECT query statement is now getting interesting ;-0)

We have a correlated sub-query in both the fields specification clause as well as the WHERE filtering clause.

Both of these are evaluated for each row examined by the main query 'execution'. The recent sub-query will return a different

integer value each time, and the earlier sub-query a TRUE or FALSE to determine whether the row is to be included in the returned data set or not.

Summary :

Once we have seen correlated sub-queries in action (like above) we soon get the feeling of how they can be used, and at

which parts of our main SELECT query statement we can include them.

It may be however, that we we come to large numbers of records in the tables being processed, that we hit performance problems,

and so need to see if we can script a solution in a different fashion, possibly with JOINs instead.

The purpose of this subsection of the eNotes was to explore just what correlated sub-queries were, and to get

some success for ourselves by making and using our own.

I think we have succeeded so it is time to move on ..............

PHH - 11th. January 2012.


CTE - Common Table Expression

======================================================================================================

Notice that we have two 'ORDER BY' clauses in the query script.

=======================================================================================================

Now we can have a query statement with three ORDER BY clauses - see below :-

======================================================================================================

PHH - 11th. January 2012.


SELECT - Clauses within, how to Read

Really, this subsection is all about the 'anatomy' of the SELECT statement.

Its structure, how it is made up, or put together.

In the second part we will move on to see how it is best for us to read the SELECT statement, in so far as to

what it does at runtime.

Clauses within :

The word 'clause' is one you will hear used a lot in conjunction with query SELECT statements.

It simply refers to 'sub-parts' of the main query.

Study the following image closely, it represent a common form of the statement :-

I suppose we could term the 'SELECT' in the first line as the main clause, and the other three blue colour commands as

the sub-clauses.

Either way we talk about the 'WHERE' clause, and the 'FROM' clause etc..

So thinking about others we have seen and used, we have a 'GROUP BY' clause.

In real life situations the SELECT statement gets much more complex than this but most of that is a visual complication,

the basic 'stuff' is just the same for many and varied statements.

It is probably best to take the four lines above in turn, and explain the details separately.

The separate sections are to be found in child sections from here.

I suggest you take a look at these after studying the 'How to Read it' section below.

How to Read it :

First of all do not start to read the SELECT statement from the beginning (or top).

Start at the 'FROM' clause - this tells us which Tables or virtual tables are being used for data provision.

The 'FROM' clause often contains 'JOIN's and these specify how the tables are connected together - basically to make

longer rows with more data within them.

The 'WHERE' clause is used to filter out - or remove - certain rows which do not fit the requirement are ignored, or possibly not

even added to the data set to be processed.

It is worth you trying out different orders of a complex filter condition to see how performance is changed by which filter condition

is processed first. I have found for large data sets changing the order of the WHERE sub-clauses can make a noticeable difference

in the time required to return a data set.

I think I found it best to first apply (state) the condition which removed significant numbers from the first stated table. I think it may even be

a strategy to put all conditions first which apply to the first table. Then move on to the second, and so forth.

And, even if the DB engine goes on to do its think, and apply the conditions as it sees fit, it is not a bad thing for us to think in these ways.

It is best to think about what we put in our SELECT statement, rather than treating it all as magic ;-0)

So the FROM and WHERE are used by the DB engine to produce a data set which can then be used row by row to create the

outputted 'returned data set'.

The 'target' data set (collection of rows) resulting from the 'FROM' and 'WHERE' clause can be thought to be processed one row at a time

moving through that set.

The next line (or clause) to be applied (or used) in the process of creating the resultant data set, is the filed specification within the SELECT

main clause itself.

For each of the fields specified in the first line of the statement, data is composed and output in the column / field order specified.

Most often fields are simply taken and used, directly from the fields / columns of the 'target' data set, and very often the header is left to be

the same as from the original data tables.

But do remember that a field output by the first line of SELECT can output a constant, or a value calculated, or made from a function. Also

we need to remember that the 'target' fields can be used more than once, and even combined by our definitions within the field specifications

within line one of SELECT.

Because the DB engine goes through the 'target' rows in turn we are then 'freed up' and able to use any of the filed values of the row in focus, as

inputs to sub-queries - that is other SELECT statements.

The sub-queries may be placed in the first line - where the new fields are being assembled - or within the WHERE clause. It may even be that

when the DB engine sees a sub-query in WHERE (correlated type, using focus row values) that it applies a different algorithm to when it only

finds fixed values. Certain sub-queries in WHERE can produce a one-off value, unlike most correlated sub-queries.

Finally, once the whole 'target' row set has been processed, and the return data set assembled, then any grouping and the ordering takes place.

Commonly we have an ORDER BY clause, and less commonly a GROUP BY clause.

Whatever we have, the ORDER BY clause must be last in the set of SELECT clauses, and only once can it be in the whole statement, unless as

part of the 'TOP X .... ' structure in 'Views' or 'Functions'.

** Note ** Reading the FROM clause as far as JOINing goes is covered specifically in the 'Joining tables' subsection.

DOs and DON'Ts - Cans and Can'ts :

It is sometimes tempting to do some of the following - but don't !

Trying to add a filter condition using a field which only appears in the returned data set - this should NOT work. Only filter on

fields that are within the 'target' data set.

What confuses us is that many returned fields are also 'original 'target' fields and so they appear to be doing what does not work.

Also if we change the field header then it is the version which is in the returned set that must be used for ordering, not the 'target' header.

Don't use a new output field in the WHERE clause, as it does not exist to be applied at that stage of the processing.

I am not sure of this next one BUT will state it - in the FROM clause only use tables which exist from the very beginning. Do NOT try

and use correlated sub-queries. I need to check this.

PHH - 11th. January 2012.


FROM

The FROM clause is possibly THE clause of the whole SELECT statement.

It is within the FROM clause that we choose the table or tables to be used as the 'target' for the data which

is to be selected and used to compile the returned data set.

For 'table' read Table object (part of the DB) or a virtual Table like a 'View' object, but also a returned data set

from another sub-query SELECT statement. In-Line table Functions also apply here, and work within the

FROM clause.

Obviously, a CTE (Common Table Expression) is also a table.

Any of these tables can be JOINed in the usual manner, and aliases given to them as well.

Also the WHERE clause filtering conditions apply to any table, virtual or DB object, in just the same way.

Let us take a look at some examples to demonstrate the above ideas.

Simplest - one Table DB object :

Let us start simple and work our way through to more complex JOINs of tables within the

FROM clause - we will try to show all the various types of table, real objects to 'virtual' tables.

First study the simple one table case below :-

===================================================================================

We are probably all familiar with this case.

Now let us move on to JOINing (called merging as a general name) two Table objects.

One JOIN - two Table DB objects :

We can merge the data in two Tables as shown below - here we are using a commonly used INNER JOIN,

but this approach equally applies to other types of JOIN - check this out :-

=============================================================================================================================================================================================

As long as we have at least one common field on which to join we can do this, however it is not uncommon to see

two or more joining conditions applied.

One JOIN - one Table DB object, and one 'View' :

Now we will see that we can use a 'View' object in just the same way - this is like a virtual table.

Also please note that the 'View' itself may combine data from more than one Table object, or other source.

First of all checkout the 'View' script shown below :-

=====================================================================================================================================================

And how to use this in the FROM clause is simple - just as before. See below for details :-

=====================================================================================================================================================================================

[ Once again please ignore the red wavy lines they are not errors ! ]

Nothing surprising, so lets move on to the next case.

One JOIN - one Table DB object, and one sub-query :

Let us now see how to merge data from a Table object with the table data returned set from a sub-query.

Study the following image of the necessary script carefully :-

==========================================================================================================================================================================================

Personally, I find that many places I look at existing T-SQL script it is for my eyes VERY badly formatted, or laid out.

If the text lay-out is edited and improved, often much 'stuff; is far easier to read and also understand.

Management Studio itself does a poor job at scripting existing DB objects like 'Views' and 'Functions'.

Lets move on ....................

One JOIN - one Table DB object, and one Table Function :

Time now to look at one of my favourites, the In-Line table function.

Study carefully the image below, particularly the line of the 'inner join' :-

========================================================================================================================================================================================

** Note ** the particular order of the brackets and braces in script for the in-line table function.

Note that we have supplied input parameters to our function - which is defined as shown below :-

==================================================================================

So our In-Line table function takes two parameters, both of type DateTime.

It is very nice use such functions in report using Reporting Services I find. I suggest you try giving them a real go!

Can't Do's - Stored Procedure :

I have carefully looked at the pages 171 & 172 of Paul Nielsen's red Bible (2008) and can't find any reference to being

able to use Stored Procedures within the FROM clause.

Okay, enough of all that, time to move on ............

PHH - 19th. January 2012.


WHERE

PHH - 19th. January 2012.


SELECT - fields specification

If we look at many SELECT statements we see the first line looking similar to that shown below :-

===============================

For clarity of viewing and reading I usually format the script so that the column names (field headers) are

not in long untidy lines.

Commas separate the column names and so what do these names actually mean ?

Let us take a closer look at what comes out in the results pane from the above query - some of which has been

missed off in the earlier image above - check this out :-

==================================================================================================================

In fact the column headings originate in the 'BatchHeader' table - do NOT get confused with the fact that this table has

the word 'header' in it, it is not what we are discussing at the moment.

Here is the Table show in the following image :-

===============================================

So what is going on in the main SELECT line of the statement ?

It is quite simple, the data from the existing columns in the data Table are being mapped directly to the output fields, and

because we are not defining (specifying) another name for the header, the original one carries through.

So as it stands we are just requesting that data be used directly from the Table columns.

Let us go a step further .............

Naming the Output Columns - providing Field Headers :

Let us now see a couple of different ways to supply a header value (or name) to each of the output columns.

In the following image we as the use of 'as' and also it being missed out - check this out :-

============================================================================================================

Notice the different header text values at the top of the data area of the output pane.

Also notice that the 'mapped' field names are still the same - as they point to the original columns - or fields.

So how about making a new column from two of the existing fields of the original table data ? We can call this

a new 'calculated' field - see below for details :-

===============================================================================

But equally we can make up new fields (columns) which have fixed data, and are not related to the original table

at all.

In the following image I have used string data, but we could have equally used numeric of datetime data. Check

this out below :-

==============================================================================

Please note that although the MS shell lets us get away with not supplying a column header, there are

times when making Views and Functions and other DB objects, when the parsing system complains and

requires us to supply one.

Personally I feel it is good practise to do so - however, checkout the following just so you know what it

looks like in case you ever get it :-

===============================================================================

Lets move on ..............

Use of Sub-Queries :

Although the following example of using a sub-query in the field specification of a column in the SELECT main clause,

is not a particularly good one, and it is not very meaningful, it does work and the syntax is correct.

Checkout the following details :-

==============================================================================================================================================================================

In the image above the extra calculated field is the sum of a sub-query and a fixed integer value.

It is probably easiest to see the 'select' line as the following - where 'X' is the value of the sub-query :-

==================================================

Let us move on to something which is VERY useful under certain circumstances, and it really is about

improving the quality (and tidiness) of the existing table data.

Massaging existing data - the use of CASE :

PHH - 19th. January 2012.


GROUP BY

*** Note *** There is another section on GROUP BY after this, in the main section of the Appendix.

In that section I will cover just what may be done in real life situations with 'grouping'.

I find that it is a good way to look over your data entries in larger files and databases.

========================================================================

This section will prove to be an exciting one for many of you, I know that it interests guys coming from a

DBF background.

If used correctly the 'GROUP BY' clause can do a lot of useful work for the developer. And its is fun too !!

First of all we need some suitable data - from a single table - with which to do some tests and trials.

In the image below we see we have a bunch of almost 84,000 rows of data - check this out :-

===================================================================================================================================

If we study the above image we see that there are twelve sensors, with IDs going from 1 through 12.

For each sensor there is a couple of 'properties' - if you think like a dotNet coder - and these are 'Freq',

'Temp', and 'Lin'. We can do something useful with these in a short while, but first let us just group the data

set on 'Sensor_ID' and then do a 'count'.

Study the following details :-

========================================================================

As you can see the rows of the returned data set are limited to the twelve we discussed above.

As such this is not really a lot of help, except it does prove that there aren't any spurious values in the whole

bunch of 83,808 records. This can be useful sometimes to find the odd NULL of blank, or other vale.

But getting back to our current example, let us count the number of rows for each 'Sensor_ID' value.

Checkout the following image for details :-

===============================================================================

There are a couple of things to notice in the above image, one being that we can order by a named field specified

in the field specification of the SELECT statement. So 'My Sensor' only is known in the after the returned data set

has been determined.

Remember we could not use the 'My Sensor' field in the WHERE clause as it is not part of the table in the FROM clause.

Secondly, notice that for the output fields specified in the first line, we must use aggregate field values, and can't use any

individual values - remember the 'grouped' line is effectively a 'summary' for the group item, or row.

Adding more Aggregate Columns :

Okay then guys, let us work out a few averages, maximums and minimums for our row 'properties'.

Carefully study the following details, see in the image below :-

================================================================================

Well, this seems to work nicely - BUT - supposing we did not wish to see the grouped result rows

for minimum temperatures of zero.

Filtering our Groups - with 'HAVING' :

First of all, let us see how NOT to do it, and then we will find out why. Study the following image detail :-

===================================================================================================

Although we do not get a parsing error for the above script, when we try to run it we get a runtime error.

This is simply because we are grouping and 'group filtering' on the the original table rows, with the field

names that they posses.

The HAVING clause - similar to the WHERE clause - must use column headers, or field names, which

exist at the time the DB engine does the task - those headers in the returned data set are NOT available

for use by the DB engine at the time of 'group filtering' so we must do the following - check out below :-

===============================================================================

That look better ;-0)

Now let us do something to change the 'Total' values.

Let us filter out of the original table rows some chosen records, or rows - see below :-

=================================================================================

As well as changing the values of the Total column, we can now also sort it small to large.

Groups within Groups - Getting VERY brave ! :

Okay you 'brave' guys - this next bit is not for the feint hearted amonst you ;-0)

If we are getting the hand of this then we cab see in the output above, that we can actually group some

of the output rows by yet another GROUP BY in a subsequent SELECT statement.

Checkout the following image of script and results :-

==========================================================================================

Yep! it actually works. But it may seem a little visually confusing, so we can use 'View' or CTE

(common table expression) to make it look simpler and nicer.

Check this out for size ! :-

================================================================================================

Notice how the header names have been changed in the WITH clause, I could have left them the same if

I hadn't wished to try something different.

Lets move on .............. I will leave you to try a 'View' approach.

Multiple Field Groupings :

So far we have only grouped on one column / field, let us try some different data and then two fields - see below :-

===================================================================

PHH - 19th. January 2012.


ORDER BY

PHH - 19th. January 2012.


Joining tables - de-Normalising, Merging

Joining data from the rows in one table with data in the rows within a subsequent table, is often

called 'de-normalisation'.

So 'joining' is about using multiple tables within a single query, AND, they are in the FROM clause.

Think of the 'joining' process as one which makes data rows longer by adding fields from other tables.

If you are going to have a well designed SQL database, with plenty (sufficient) normalisation of data, then it

is inevitable that you will need to do much 'joining' when you design and create your SELECT query statements

in order to get back suitable data sets.

Remember that 'NORMALISATION' is seen as the process of splitting up your data into sub-modules (usually

tables) such that any discrete piece of data only appears once in the database (DB).

For example the postcode for a customer should probably be in the 'Contact' table and nowhere else at all. the

Contact table would most likely have 'Supplier', 'Shipper', 'Employee' information (records) as well as 'Customer'.

EXTREMELY IMPORTANT - as a skill for use with SQL :

I can't stress enough how crucially important it is to become VERY familiar with using all forms of JOIN.

A database other than a trivial one, needs to have SELECT statements created and used frequently, which calls

on JOINs of different types. It is easy to get these wrong if you are unfamiliar - BUT - the good news is that they

are not rocket science and can be reasonably easily mastered if one puts one mind to the task.

We will mainly cover 'INNER JOIN' and 'LEFT OUTER JOIN', but 'RIGHT OUTER' is not hard to understand either.

These three JOINs are what new to intermediate guys need to learn to use.

I have to confess, at first I was scared of JOINs and now I love them, yes, I actually like the creative part of designing

the query where I decide on which type of join and how to apply them.

Strange old world, isn't it !?

On What do we JOIN ? :

Syntax of the 'FROM' clause with JOIN included :

A Classic Example :

Cross Join :

================================================================================================================

=====================================================================================================================

==================================================================================================

PHH - 11th. January 2012.


Combining data sets

In this subsection we are going to look at 'Set Algebra'.

This is a very convenient way for us to combine the results of two or more query SELECT statements into

a single result set.

In the child sections of this subsection on 'Set Algebra' we will look at each of the four possible 'operations'

that may be done to 'combine' data sets, but first we need to see what the 'basic' rules are which apply to

the 'combining' process.

Rules for Data Sets :

Let us define a standard type of 'operation' :-

'C' is the result of -- 'A' operation 'B'

'A' and 'B' are data sets with the same 'form', they have to have the same 'form'.

By 'form' we mean that the 'signature' of their columns is identical.

And this means that each set has the same number of columns, that each column is of the equivalent data

type as that in the other data set.

By equivalent types we mean that the order of the columns is the same, and that their data type is compatible.

Obviously, the resultant data set 'C' will also have the same 'form' as 'A' and 'B'.

Since the names of the columns are just 'tags' they do not matter, however, these 'tags' on the final resultant

data set are the same as those used for set 'A'.

" .. we need to be dealing 'like with like' .. "

With what are we concerned ? :

Well, having said a lot about columns and data types, when we get down to scripting the operations we are really

concerned with the rows within those data sets on which we operate.

Also, when we say the rows, we do actually mean the whole - or complete - row. That is all fields within a row.

What can be done ? :

We can combine sets so that they effectively add one set to another, all the rows of A and B and this is 'UNION ALL'.

We can alternatively combine A and B so that we get a resultant set of only 'unique' rows, this is 'UNION'. And the

definition of 'unique' is for all fields within the row to be different.

We can get a resultant set which is just rows which are in one set but NOT in the other set, this is done via the

'EXCEPT' operation.

And we can also get a resultant data set which is rows that are common to both to both A and B. Once again I think

we will see that we get no duplication with this operation. Any row will appear only once in the result set 'C'.

Does not seem much !? :

To be honest, at first this does not seem like much at all, no a big deal, BUT, when it comes down to using the set

algebra we find we can do some VERY useful 'stuff', things which are very difficult to do otherwise.

Without knowing about, and using, set based algebra operations your scripting of query statements will be so much

the poorer.

If you like, we are talking about combining SELECT statements, and easy way to get some interesting results.

Time to move on and see some of this stuff in 'operation' - so to speak ;-0))

See you in the next subsection .............................

PHH - 16th. January 2012.


Union / Union All

Union All :

In many way this is the easiest operation to understand, not that the others are difficult however.

The resultant data set 'C' is totally every row in both 'A' and 'B'. So the number of rows is a total of

the number in 'A' plus the number in 'B'.

Let us take a simple example - it may not make a lot of 'physical sense but it helps to demonstrate

the way to use 'UNION ALL'.

Here are two separate SELECT statements, one producing 39 lines and the other 6. Study the details

carefully in the following image :-

================================================================================

Notice in the above image that the columns (fields) are similar, as is their data types.

Now let us try simply placing a 'UNION ALL' extra line between these scripts and then executing

the whole lot of the scripts - see below for what results :-

============================================================================

As expected we get 45 rows, and as well as that we seem to get one result set 'stuck' on the end

of the other - see the following image :-

===================================================================

This is fine, and may be as you would expect, HOWEVER, when we try other operations the resultant

data set may not be so obvious or well arranged.

Let us move on to 'UNION' .........

Union :

Okay you guys, the first thing we need do is get ourself a suitable example for the purpose of demonstrating.

Below I have used the 'ServiceJob' table in the 'Small Trader' database.

Study the code to get jobs made in July, and then those in August. Check this out below :-

==================================================================================================

So what will we get if we use the UNION operator on our two sets above ?

Carefully study the following image - the whole of the script showing was highlighted and executed in MS :-

==================================================================================================

In the above image it would appear that the final returned data set is in order 'little to large'.

Certainly the set is a UNIQUE set with any row being present only once. We will need to see what happens

with the resultant order when we have more complex situations - more columns, and which order is returned

then.

However, we can of course make our own order even with one column - see the following image :-

==================================================================================================

In the above image see how the the 'ORDER BY' clause is at the very end of the query script, almost

isolated it would seem !

Multiple Columns :

Let us try and get some meaningful data from the 'AdventureWorks' database.

We will use the 'PurchaseOrderHeader' table to provide us with lost of rows of dated orders.

See below the three separate queries being run, to give us three separate data sets with respectively 272,

1035 and 2697 rows of data.

==============================================================================================

If we now uncomment the 'UNION' command lines, as well as adding a suitable ORDER BY clause we

see the following returned data set - see below :-

=============================================================================================

As you can see in the image above we only have 958 rows in the final combined data set.

If we change the order of the fields / columns and do not specify an order of our own, we see that

we have a different order in our combined data set - see below, and study carefully :-

=============================================================================================

This time see the order that the Server returns is on the first column, which is now 'EmployeeID'. So we

need to find out what the Server order would be for the combined set when we had the original order of the

specified columns as '[VendorID],[EmployeeID]'

Let us try that now :-

===============================================================================================

And below is another part of the output pane, somewhere in the body of the returned set, not the end as above.

=================================

As you may see from the last two images, the combined set is in order of the first column, here it is 'VendorID'.

I suggest that the way the Server does its work on the operations required is by keeping track of the first field

specified. This is because UNION will result in a 'unique' rows set and so it needs to have an ordered list for its

own internal comparisons.

Anyway, enough of that, lets move on to see EXCEPT as an operation ..........

PHH - 16th. January 2012.


Except

==========================================================================================

PHH - 11th. January 2012.


Intersect

PHH - 11th. January 2012.


Ordering the results set

Simple in theory, but CARE must be taken when putting the ideas into practice. Well, some of the time anyway!

Let us take a simple example, with the 'ORDER BY' clause being the part to set the order of the returned

data set - or we often also say 'sorted by' .....

==================================================

The 'ORDER BY' clause in the above script makes the returned data be sorted in order of highest to lowest, on the

'CustomerRef' field.

One Returned Dataset - One Order of Rows :

When it is Easy :

Generally speaking, if we have a basic SELECT statement with an ORDER BY clause at its end, we will be OK, and

won't need to worry about the results coming back in a different order to that expected.

A

When we MUST take care :

If we have a SELECT query statement within another 'enclosing' DB object, like a 'View' or a 'Function' then we need to

take care.

The 'ORDER BY' clause is part of the 'TOP X' structure and looks as though it ought to work as the standard (ordinary)

type of 'ORDER BY' but in fact it is slightly different. We will try to explore the difference in the next part of these eNotes.

PHH - 11th. January 2012.


Grouping the results set

Although this is not going to be a long subsection it certainly is a VERY important one. And one

which can provide us with MUCH flexibility.

Let us get a feel for 'grouping' by trying a few good basic examples.

Grouping Data in the Returned Set :

To get the idea of 'Grouping' in our SELECT query we had better work through some good examples.

We will go back to using our Readings table we have used frequently in the previous subsections.

In the image shown below we will group our rows by 'Sensor_ID', and the aggregate value we will output in the returned set

will be the maximum of the [Reading_ID] field.

Study the following image closely :-

=========================================================================

And of course in the above case we can then sort the first column as seen in the following image :-

=========================================================================

However, we have possible simple variations on the same 'theme' or idea.

Checkout the next two possible variations :-

=========================================================

And also the following :-

===================================

Comparing with Distinct :

Okay the guys, here is an interesting comparison - two different ways to get a unique list of Salutation 'values'.

Carefully study the following image :-

==============================================

I think you will agree that the above example is 'interesting' - grouping has a LOT to offer.

Aggregation - across each Group :

There are obviously a lot of things we can do with Aggregate functions, and apply these to each group.

Below is an extension of what we have done so far - check this out :-

====================================================================================================================

The above output is with aggregates being specified for each of the groups in turn - not the whole

record set, or full bunch or rows.

In contrast, the following gets the aggregate values for the full record set. You can clearly see a difference from

those above.

====================================================

Of course with grouping we can still filter the records to be included in the returned set which is to be 'grouped'.

Here is and example building on the first 'grouping' above.

====================================================================================================================

The new clause 'having' is applied to the grouped outputs only.

HAVING - a new clause for use with 'GROUP BY' :

Basically this is a filter condition on the rows which exist 'after' the grouping takes place.

If the condition in the 'HAVING' clause is true it will result in loss of rows from the returned data set.

You can write a SELECT statement without a 'HAVING' clause, in the same way we can write one which

does not have a 'WHERE' clause.

PHH - 11th. January 2012.


How to use effectively to explore data

PHH - 20th. January 2012.


Functions

PHH - 15th. January 2012.


those Available

A useful facility within the Object Explorer (OE) pane in Management Studio (MS) is the

presence of the complete list of functions which are system based - meaning they are part of

SQL Server itself.

The Transact SQL (T-SQL) functions are shown in the following image, study them carefully :-

========================================================

If we expand some of the most commonly used by new and intermediate guys we see details like those

shown in the image below :-

===================================================================================================================================

You will see a number of the above functions used throughout this appendix section.

Some of the other groups of System Functions are probably a bit too advanced for us right now, or they are

much less commonly used for solving 'day to day' problems in the normal course of querying databases.

If we now take one particular function from the 'Date and Time' list, and expand it, we see the following :-

====================================

Notice that we can easily see which parameters are required by the function, as well as their data type - and

what is returned by the function as well.

Look out for some of these in the other subsections of this appendix.

PHH - 11th. January 2012.


making and using our own

The whole purpose of this section of the eNotes is to explore the use of the SELECT statement.

So if we actually create a user defined function it is simply so we can use it within SELECT. There

are other notes in the 'ClickStartTSQL' eVolume which go into greater depth on the subject of 'Functions'.

For now let us make a simple one for these purposes.

We will look at the 'Invoice' table of the Small Trader application, and below we see a script which finds the

set of invoices for a specified customer, in this example the customer is unique ID '103'.

Check this out below :-

=================================================================================

What we will do is base a new function on this script, and adapt it such that the function accepts a parameter

which is the customer ID and returns the number of invoices existing for that customer.

We first need to adapt the above script so that the query to the Server returns a scalar value - see below :-

===============================================================================

To start a new Function of our own, we right click the 'Function' node to get the context menu(s) - see below :-

==================================================================================================

It is not a difficult task, but I actually prefer using a copy of one of my existing functions, and the adapt it

to suit my new needs. Below is the result after such an adaptation - check this out below :-

=========================================================================================

** Note ** The prefix of 'ufn' I use to signify 'User Function), and you can adjust this naming approach

to suit your own needs - like having 'usfn' instead, where the extra 's' stands for 'Scalar'.

Let us now try to use our new function within a SELECT query statement, one where we are mainly

querying the Customer table - study the following image carefully :-

==================================================================================================================================

In the above script the use of the 'Function' can be compared to using a Correlated sub-query like those we

studied earlier in this Appendix.

A Better way to Use the Function ! ? :

It is possible to change the syntax of the above query script and have something like the following - check this out :-

======================================================================================================================

The above code for this example shows that we use the 'Function' directly, since it returns a scalar value then we can use it

in any place a scalar value is allowed, or acceptable, within our query statement.

So basically, no need for the extra 'select' parts as in the image two above.

There is a slight problem with this method, although it works OK to just 'quote' the function in an 'in-line' fashion within our

main SELECT statement, we can't test it like this in the Query pane of MS.

Here is what happens if we try to - see below :-

============================================================================================

As you can see, we at least need one SELECT statement as the vehicle for querying and returning a value or a data set.

But as long as we have one SELECT statement then we can use the scalar function anywhere the SELECT statement requires

or receives a scalar value.

Going a Step Further :

Let us now try and use our new function in a different clause of the SELECT statement. Let us try the WHERE clause.

In the following example, the image of the script shows that for every row in focus, as the row is processed by the database engine,

the function finds the number of invoices for the customer (of the row) and so the returned value from the scalar function is used

by the filter condition to decide whether the row is in or out of the returned data set of the main SELECT query statement.

Checkout the following details :-

======================================================================================================================

*** Remember *** We can't use fields that appear only in the field selection of SELECT in other clauses except ORDER BY.

We can only use fields (and calculated values etc.) which come directly from the FROM clause, and the rows being processed.

The selected fields are being placed in the 'OUTPUT' part of the Server engine, and only the ORDER BY clause is applied

after they (all the fields) are ready for output. In other words the sorting is the last process to be carried out by the DB engine

as so new fields can be used as well as existing ones - any fields in fact.

Study this image, and the error message too :-

========================================================================================================================

But of course there is no reason why we miss out the value as a field output, and just use the value for each row,

and the filter condition - see below :-

============================================================================================================

Yep! we get the same results but one less field, the 'Invoices' calculated field is absent.

In-Line Table Function :

Before we leave the topic of 'Functions' within the SELECT statement, we need to see one example of an In-Line Table

function, and how it may be used in the FROM clause - the same way that we use a 'real' Table object, or 'View' object

of the database.

Let us look at the script which was run to make the function just a few minutes ago on my desktop.

Checkout the full details carefully :-

==========================================================================================================================

Okay then, as seen in the image above, we have a function which produces a table. The table data is for customers

with a number of invoices greater than the value input as the single parameter to the function.

The above data was generated for customers re-ordering, that is they have more than one invoice in the DB.

Below is some output - returned data - for inputs of both 2 and 3 - study carefully :-

====================================================================================

As you can see in the above image the size of the dataset diminishes as we increase the input parameter.

So lets start to try and incorporate the 'Function' into a few SELECT query statements. See how the function

appears as a table in the syntax used :-

===========================================================

Because the function slots directly into the SELECT script we say that it is and 'In-Line' table function.

These functions are relatively new to SQL Server and can be used in places where Stored Procedures can't.

Now then, let us try to go further, in the following example we use the function with a table in an 'INNER JOIN'.

Study carefully what we have below :-

[ .. remember to ignore the red wavy lines - there is no error ! .. ]

=======================================================================================================================

Notice that in the above script we have even taken the table output from the function and rolled it into another SELECT query

statement, in which we only output the unique, or distinct, values for the customer reference ID.

I am sorry that the example is a little 'forced' or 'contrived', this is what comes of trying to demonstrate more complex cases,

and situations, with simple database - as well as test and development data rows.

Time to move on, let us go to the next subsection ..... see you there .................

PHH - 15th. January 2012.


Aggregation

PHH - 11th. January 2012.


String comparison

PHH - 11th. January 2012.


Handling NULLs correctly

This to some is a 'thorny' subject.

Why it is a difficult, and unpopular topic I am not really sure.

All I can think of is that people do not take any time to find out about, and then understand NULLs.

I was given good advice right form the start of my SQL experience - thanks Paul !

The advice was .. 'embrace NULL and use it correctly - then you will have few issues' .... now some

3.5 years on and he seems to be totally right, as usual ;-0)

Background :

For any column added to a Table in an SQL database there is an option to allow it to be 'NULL'.

So this means that for every field, whatever its data type, the field (or column) can be NULL.

For 'NULL' we mean that the field is unspecified - not given a value, without value.

If the field has a value then that value must fall within what is allowable for the data type being used.

For example, a column entitled 'InvoiceDate', with a data type of 'DateTime', if set to allow NULLs, can

either have a value or not. If it does have a value then the DateTime(DT) values falls within the defined limits

for a DateTime value.

It is NOT the DT value which is NULL - that does not make any sense at all.

Comparisons :

If a field or column can be unspecified (or set to NULL) then how do we know it is so ? And, how do we compare

this field with another field with a similar data type ?

We need to take care, as although this is not rocket science, it can get confusing if you approach it in the wrong manner.

Let us start to go through it 'step by step' ....

=========================================================

And here is something else the SDK docs have to say :-

=================================================================================================================================

So let us now try and use some of this new knowledge to make up a suitable query.

The following SELECT statement uses a table I know has some NULLs in the 'Temp' column.

The WHERE clause uses the 'IS NULL' syntax as follows :-

============================================================================

In the above image the extra filter conditions are required to restrict the number of rows in the returned data set

to be manageable, and possible to show here in the eNotes.

We can now alter the script to show the other set - that which is all those which are not NULL.

I have had to further restrict the returned set because of quantity, but otherwise it is the similar to before.

Study the following carefully :-

===========================================================================

Well, we may have thought that this would be the case - BUT - what do we think will happen if we use the

common comparison operators of = and <> ? let us do a test to see - checkout below :-

=========================================================================

We get no error message and also no returned data rows :( So what of the other comparison operator ?

Now check this out :-

==========================================================================

Once again, we see no error and also no returned data set.

This is simply because ' [Temp] = NULL ' evaluates to false, as so does ' [Temp] <> NULL ' and therefore

both queries return zero rows in the dataset, as the filter condition is never TRUE.

This is something to be VERY careful of in you scripting and ADO.NET solutions.

Moving On :

if we move on now, and try to identify the existence of NULL, or the situation of a field value not having been set,

we see the interesting script below - study it carefully :-

============================================================================

We need to be careful when setting a 'default' value. Some fields like the 'Temp' column above, can't safely be

given a value of zero ( '0' ) because that is a meaningful one.

It may be better to see that the displayed value is 'blank' or 'n/a' - see below for an example, and

study the details thoroughly :-

=============================================================================

So lets see this with ' n/a ' as the default value :-

===========================================================================

Okay then, enough of this fun and success.

Lets move on to do something different, see you in the next subsection ...............

PHH - 15th. January 2012.


DateTime - and all that stuff

PHH - 11th. January 2012.


Adding columns - calculated fields

Extra Fields / Columns :

Fields Filled by Functions :

Using 'Case' to fill a field on complex conditions :

PHH - 11th. January 2012.


Example - with INSERT

I have just been doing some 'real work' as opposed to writing 'ClickStart', and came across this interesting example

of using the SELECT statement in a few different way within one INSERT module.

The Table entitled [dbo].[_phh_tRecvNHSweeklyData]) is a receiver table which is dropped and re-created,

and then filled from a CSV file.

Once read in the dat is to be transferred and converted , as well as being placed in a long term Table entitled

[pelican].[dbo].[_phh_tNHS_wh_Data] - which will then be used for report generation and a few other things.

Study carefully the script in the following image :-

==========================================================================================================

In the above image you can see that in the last SELECT statement I use a couple of functions, these are entitled :-

[pelican].[dbo].[fs_phh_NHSyearPeriod2PelMonth]

and

[pelican].[dbo].[fs_phh_NHSyearPeriod2PelYear]([Year],[Period])

these are scalar functions returning a single string.

Just before we leave this short example section you may be interested to see the scripts for the initial receiver Table

creation and population, as well as the two small functions.

First of all here is the script for the external CSV file reading - study it carefully.

=======================================================================================================

** Note **

The 'BULK INSERT' command must receive a string constant as the file location and filename, it will not accept a string variable.

[ If we must provide a variable then we need to construct dynamic SQL - according to Paul Nielsen (2008) page 687. ]

And now the scalar functions :-

===============================================================================================================

And finally the function to provide a text month .... yes, I know it is not that clever, but it is robust and works,

and we are not worried about performance at all.

================================================================================================

Time to move on, but at least we have some more working code to copy and adapt when we choose.

See you in the next subsection ..............

PHH - 17th. January 2012.


Appendix T - Temporary Tables, Table Variables

PHH - 21st. January 2012.


Global TemporaryTables

PHH - 21st. January 2012.


Temporary Tables

=========================================================

Need to 'DROP' while a session lasts ! :

==================================================================================

============================================================================

======================================================================================================

=========================================================

PHH - 21st. January 2012.


the Table Variable

====================================================================================================================================

=========================================================================================================

==============================================================

The solution to READ ONLY problems :

===============================================================================================

===========================================================================================================================

PHH - 21st. January 2012.


Cursors with temp Tables

 Top Previous

==========================================================================================================================================

====================================================================================

PHH - 21st. January 2012.