Project Introduction

 Top Next

IMG-20120418-00147 - Copy_edited-1

PHH - 1st. July 2012.


The 'Site' of the Project

 Top Previous Next

PHH - 1st. July 2012.


Geographical Location

PHH - 1st. July 2012.


Project Software Application

PHH - 1st. July 2012.


'Mapping' Site with Model

PHH - 1st. July 2012.


Learning material - 'ClickStart'

Try :- 'www.WinClickStartVulcan2.net'

http://www.winclickstartvulcan2.net/ClickStartExec2.html?introduction.htm

http://www.winclickstartvulcan2.net/WebNotes/ClickStartWPF/ClickStartWPF.html

PHH - 17th. June 2012.


About Help

This help file was written using 'Help and Manual', a mature and well known application for writing help notes.

The URL you need to use is :- http://www.helpandmanual.com/products_hm_overview.html

The following image is to be seen when you browse to the above link - see below :-

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

PHH - 15th. June 2012.


About the Software Application

PHH - 1st. July 2012.


Microsoft .NET 4.0 Framework

PHH - 1st. July 2012.


WPF technology for GUI

The old style GUI technology of Microsoft Windows applications was called 'WinForms'.

This technology was Microsoft's main GUI (Graphical User Interface) technology for may years, but they

replaced it with WPF quite some years back now.

WPF stands for 'Windows Presentation Foundation' and was code named 'Avalon' for some time I believe.

Currently Microsoft have three GUI offerings, for different platforms, and these are Silverlight, METRO, and

WPF itself.

METRO is the new style Windows 8 tablet styled app technology, and Silverlight is still very much alive and

kicking, and is really WPF across the web.

It may surprise you to know (but not if you understand how Microsoft (MS) do things) that all of these GUI technologies

are based on XAML (said zammel) which is an XML script which Microsoft have created to described the Form

itself. Yes, we still have a 'Form' in WPF.

So quite recently, when MS released the RC (Release Candidate) of Win8 and Visual Studio 11, I could easily start

to write METRO apps, as the XAML was just as in WPF - which I have been using for about four years now. I gave

up 'WinForms' at least three years back.

So to briefly summarise, WPF is for desktop apps, METRO for Surface tablet and other such devices, and Silverlight

is for web apps. And all three are based on XAML for the GUI description.

WPF and XAML :

The WPF 'screen' is described in XAML script, and XAML script only. There is no 'binary' layer as there was in WinForms.

This means that when we use the Visual Studio editor the graphical part is drawn or 'made' by the VS 'shell' from the

script in the text editor section. If we make changes to the XAML script, changes are immediately seen in the graphical

representation.

Let us look at part of the VS WPF designer for the 'Logged Data' screen - see below :-

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

In the above image there are 21 lines of XAML script showing - this is not all of the script required to draw / create the form itself.

Look at the next illustration where we see the last few lines - making a total of sixty lines.

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

In the image shown above we actually see some 'nested' structure within the form itself.

One thing about WPF is that the form itself is a genuine object, displaying the full set of object behaviour and structure we may

well expect of an object.

On the older WinForms we used object style code to describe an otherwise 'flat' form / window structure.

In WPF we see full nesting capabilities, with 'things within things' as we often say - the 'things' are objects. So our Status Bar

is made up from nested GUI objects like StatusBarItem and TextBlock, but this could be many more, and varied item objects as

well.

So to summarise, WinForms were limited, and out of date because they were not true objects. WPF is the way forward.

WPF technology also has a very well designed and fully operational 'Data Binding' system, which encourages developers to

keep their application solutions correctly 'layered'. Not mixing GUI script with data related code, the interface is clean and

based on the 'data binding' facilities within WPF itself.

This Ground Probe app is fully using WPF for GUI, and as much data binding as and where possible. Classes of object like

'Sensor' are Dependency Objects as then they will behave correctly when used with data binding.

Time to move on .....................

PHH - 26th. June 2012.


a class of Dependency Object

A 'normal' style of user defined class may well start as shown below - check this out :-

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

Above the image shows that properties are defined with the 'Auto' command causing the getter/setter to be set

in the standard way - this is a convenient shorthand for the following type of code style :-

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

Okay, there would have to be a Field called 'nMax', but you get the shorthand 'drift' of mine ;-0)

One line in place of 8 is always a good trade-off in anyone's books!

Dependency, and all that jazz ! :

However, when we move into 'Data Binding' and requiring a class of object which is a Dependency Object, we

need to adjust our code to be as shown below :-

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

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

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

PHH - 26th. June 2012.


Forms and Dialog Screens

PHH - 1st. July 2012.


SQL Server 'backend' Databse Engine

PHH - 1st. July 2012.


Moving the App to other PCs

PHH - 1st. July 2012.


STATIC classes for SQL access

PHH - 1st. July 2012.


Sensors

PHH - 1st. July 2012.


Sensor Groupings

PHH - 1st. July 2012.


On-site Logged Data

PHH - 1st. July 2012.


Data Logger

PHH - 1st. July 2012.


Multiplexor

PHH - 1st. July 2012.


Data File Format

Although discussion of data file format is not an exciting or sexy topic or activity, it is rather crucial to success in this project.

Without understanding fully the nature of the logged data, and how it may be subsequently read into SQL Tables, it will mean we

get far less than 100% success with our requirements from our Ground Probe application.

In order to get to where we wish to be with our SQL table data, we first have to start at the data files produced by the logger, and the

logging system.

In this subsection we will just look at the format of the files as produced by the logging system, and touch on how this will affect

the process of how we process them later.

Firstly then, let us see a group of such data files (here called '.DAT' files), as seen in the W.E. window.

Study the relevant details in the following illustration :-

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

It would appear that by the time I get these something (or someone) has named the files with a sub-name built of the date.

As long as these master (original) data files are uniquely named it hardly matters what their name 'structure' is - however, it seems very sensible

indeed to pick a consistent way of naming and one which reflects the period of the collected data.

The date of the download seems eminently sensible too ;-0)

If we open up the simple text file (for that is what the logged file is) we see something very close to the following :-

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

And since the data rows (sometimes called records) are VERY long we had better show an image of the right hand end of the rows.

Checkout the R.H.S. of the data file - see below :-

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

It has to be realised that any software app other than 'Notepad' will tend to 'distort' the data, by trying to interpret it, and maybe adding to or removing

from the original text, things which the apps thinks are required.

First of all let us use Excel to try and see the data as it is - no messing !

Checkout the following :-

In the above image we in fact see that the double quotes around the 'Timestamp' field (see start of line 5) have been removed as part of the

interpretation.

However, let us see what we get if we use Excel to open a file in a way we specify.

Follow the images in the next few screen shots as we do just that - first we use menu selection in Excel to do File, Open :-

We may need to set the file types drop list to 'All Files' before continuing.

After selecting a file (of type .dat) we get the following dialog screen :-

Make sure it has delimited checked.

In the next dialog select Tab, Comma, and Space etc. - see below :-

And this will bring up a final dialog shown below, leave it as General :-

Then after selecting 'Finish' we see resulting display - just as it is in the following illustration :-

At least this format of a display has the relevant data in the right columns, and lined up by column too - which we certainly did not

have in the Notepad display ;-0)

A quick check of the data type using 'Format Cells' option after a right click to bring up the context menu, we see the following

dialog screen which lets us see that the 'Timestamp' is taken as a 'Custom' type with format of 'dd/mm/yyyy hh:mm'.

Check this out :-

And once again, because the rows are VERY long we need to checkout the R.H.S. of the Excel sheet.

Study the following illustration :-

Note I have split the screen so that we can see both the top and the bottom few rows.

This is sufficient for understanding what our data text files look like before we start to read them in to our SQL tables, and

also before we do any processing.

Time to move on ....................

PHH - 17th. June 2012


Logged Files to SQL Data

Preparation for Action :

Before we can do anything meaningful with the logged data from the 'field collection' process at the site, we need to transfer it from

the original data files and store it in a professional database system - one like Microsoft SQL Server.

As well as transporting the data from simply structured files into relational database 'tables' we need to think ahead to what sorts

of questions we wish to ask of our data.

We say we send 'queries' to the SQL Server engine, and these queries can be more simply structured if we store our data in Tables

which are more suitably set-up for the queries we are likely to 'ask'.

When we send a query 'script' to the database engine we can think of ourselves as 'asking a question'.

If we take the trouble to transform the format of our original data (as it comes from the data logger), and place this in suitably structured

Tables, then our query script reduce in their complexity.

However, the first task we face is actually getting the original data into SQL Table format, without any data loss, or indeed data corruption.

Once the data is in a SQL Table, we can then run .NET code and transform it into much more suitably structured tables, on which we can

then base most, or all, of our subsequent querying.

Getting the Original Data into a Table :

The first task of importing the logged data is to read it into our computer system in a form as close to the original as possible.

To facilitate this we use an approach which creates a 'Read-In' table for the logged data file, and this 'Read-In' table will be over-written

each time we process another and different logged data file.

We have a table in the SQL database called 'DatDumpIn' which serves this purpose. So in many ways you can think of this table as

a 'temporary' one - although technically 'temporary' tables in SQL Server 'speak' are different.

So to put it correctly, this table is a permanent table which we use in a temporary fashion. So its data gets lost when we start to process

a different data file.

Study the following image to this 'DataDumpIn' table with some of its contents :-

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

The above image may indicate the fact that we have LONG rows which mirror the structure of the data file shown in an earlier subsection.

Working with long rows is awkward, and viewing them within management software is hard work, and I feel can lead to mistakes and

errors.

Trying to write help notes is also very awkward as you can easily imagine.

For this reason, in the above image I have shown the first handful of data columns, up to 'Temp(5)' and in the following image I have used the

scroll bar to show the last ten fields (or columns) - the last one being 'Pulse6'.

Checkout the following details :- [ .. the scroll bar is not visible, it is below the image 'cut-off' .. ]

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

If you examine closely the following image we can see that the query script shows at the end of the select statement, that

we have those ten field names which we have just mentioned - checkout below :-

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

Before we move on, let us take a chance to see the Object Explorer (OB) pane in MS.

In the following image we see the column folder (node) expanded, and once again we get a huge long list

of field headers, or column names.

We can see the first few of these in the left hand image below, and the last few in the right hand image :-

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

Okay then guys, now that I have made a good case for processing our original data records (rows) and splitting them into more

manageable (shorter) rows in other tables, I am also going to make a case for also keeping the original LONG rows for future use

A Good Practice for Data Management :

Okay then, my point is that in order to query and process data after it has been logged and converted to a quality database

environment, it is best to be stored in meaningful 'chunks', as small as possible.

However, since we are having our original data read-in to effectively a temporary table, it is also an excellent practice to make a

full and correct copy of it to an 'archive' type of table.

By this I mean that the table into which we copy the original logged data will be as close to the structure of the logged data file as

possible, without any duplicate rows. if then for any reason in the future we need to refer to the original data, we can use the 'Master'

file without needing to read in all those '.DAT' files, one at a time.

View the illustration below which shows the 'MasterDumpSets' table :-

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

The only difference in the 'Master' table as compared to the logged .DAT file, is that I have added a few calculated fields

on the front of each record. These just facilitate general processing at input time, so that other created tables have the right

values for ID columns, as well as extra Date and Time related fields.

Here is an illustration showing these added fields :-

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

In the above image the first three columns have been added, and the original logged data fields start at the blue highlighted

'TimeStampIn' column.

Now then, as all good researches know only too well, nothing is ever as easy as it should be, or even as it first looks!

So now our unforeseen problem / issue. The logged data sets as sent by the logging equipment include duplicates.

Look at the next section to see what is meant by duplicates.

'Overlapping' Data Rows :

It would appear from examining the data files so far downloaded from the data logger, that some system of cyclic over-writing

is taking place.

By this I mean that it looks as though any data logged (every 15 minutes) goes into either 'empty' space in the logging device,

or over-writes the oldest data stored in the memory of the logger.

Whatever, is taking place, one thing we can see, from the actual data itself, is that We get records in one file which also exist in

another. So basically any method we design for processing the master data records read in, MUST take into account the possibility

of duplicate rows, and make sure that this does not happen.

Yes, we will get the duplicates in the temporary table called 'DateDumpIn', but then when this is processed no duplicates must

be created in any of the other more permanent tables, like Reading, BatchHeader, and even the 'MasterDumpSets' itself.

So 'MasterDumpSets' stores only one copy of any record, so the 'Timestamp' is unique.

Study the following image in which we see two such similar rows of data - checkout the 'TIMESTAMP' field :-

It is enough for the present to introduce this 'Duplicates' topic, we will deal with it in more detail in a subsequent

subsection, and see how our dotNet code handles the situation, using unique values and Primary Keys.

Time to move on ...................

PHH - 17th. June 2012.


dotNet code and ADO.NET tools

Since out applications these days are generally written using the .NET framework, we need to know

how to move our querying of the SQL Server, and our Ground Probe database, from the Management Studio environment

to the C# or Vulcan .NET environment.

Please note that Vulcan.Net is a fully fledge .NET language and is very similar in nature to C#. In fact the 'Reflector 7'

software application (well known to .NET developers) can translate automatically from one to the other.

So C# versus Vulcan is no issue at all. It is the dotNet framework, and ADO.NET itself that we need to address and understand.

Below is a method which is written so that the tested query script from MS can be sent from the dotNet code environment.

One thing to notice is that the 'GO' command seen so often in the MS shell editor, actually 'belongs' to MS and is not part

of the T-SQL scripting 'language'. "Transact SQL", or T-SQL for short, is a whole language type 'script'. There are good reasons

to call T-SQL a script rather than a language, but that argument may be a little esoteric for the context of these HELP notes ;-0)

So T-SQl does not have a 'GO' command, and this must be excluded from any check-out script copied from the MS editor.

CAREFULLY study the following image to see the now coded version of the script which when sent to the SQL Server engine

will cause a selected data file on disc to be read in to a newly created table, and then a copy of all the new rows in this table

to be found and displayed in a data grid control on the WPF main form - see below :-

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

Please note that in the above image lines 396 - 507 are hidden as they are so similar to others included in the image, that

they would not add anything to our understanding of what is going on, and would just 'get in the way'!

Probably lines 524 to 50 are the most crucial to aid our understanding.

The making of the new table stops at line 521, and the reading in of the file called 'DumpSetX.GP2' takes place.

Below is shown the code which uses the selected file name and copies this chosen file to another of the 'standard' (or fixed)

name of "DumpSetX.GP2".

Check this out below :-

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

We can also see the newly made file if we use W.E. to search and find - study the following image :-

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

Once the duplicate logged file is in place, with the 'standard' or fixed name, it may be opened and used.

In the image below line 283 is where the method is called to do this, and is effectively attached to the push button

on the main form :-

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

And the push button is seen below, along with the XAML specification of the click event :-

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

So the clicking of the "Select Log File" button causes the dialog screen to be shown, and then after selection the file is

copied and the standard name is used.

The consequence of all this is seen in the data grid control of the main form (see below) where the DataTable is shown

after being retrieved from the SQL Server.

The file selected is that called 'LP_110512.dat' in the GroundProde2 folder.

In a later version of the app we will be able to see this name (or any other used) in a status bar.

The following image shows the Open File dialog at run time :-

As we will be come accustomed to hearing, the script to create and populate the new table is send to the SQL

Server via the 'SendDataSetQueryScript' STATIC method within the STATIC class called 'SQL_my_funcs' ,

which is in 'Layer6'.

Take a look at this STATIC method once more and see that effectively the script text input to it goes straight to line

609 and then 616 where it is used by the SQL Server. Check out the details below :-

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

We will discuss many more details of the STATIC class called 'SQL_my_funcs' in the following subsection.

Time to move on ...............

PHH - 17th. June 2012.


STATIC class - 'SQL_my_funcs'

What is a STATIC class ? :

The answer to this question is quite simple in many ways.

A STATIC class is one which is made from STATIC methods and properties as well as an optional STATIC constructor.

Any property or method which is 'STATIC' may be used directly in code as a function 'of old'. We do not need to instantiate

a member of the class first, before calling the method / property.

Many things in .NET are STATIC, many in the System itself.

What makes a STATIC class different to a Class with STATIC methods is that the first time any member of the class is used,

the STATIC constructor is called, and values so generated are held for the lifetime of the app, or till the properties are

changed by code.

So for example the following image shows that the connection string is found the first time the Class is used.

This means the first time any STATIC member (method or property) is called the serial 'config' file will be read and

the Connection string read in.

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

So to summarise, STATIC members are like functions in other languages, supplying a value or a process, or both.

And a STATIC constructor (if it exists) will be called and run the first time any member is called - and properties

persisting for the lifetime of the app.

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

One VERY important point to understand is that we need the help and support of a STATIC class in

order to send our SQL query scripts to the Server, and also to get the returned results.

So we need some support material, as well as a pre-written STATIC class to support our dotNet coding.

The following web link will help anyone wishing to learn more about T-SQL, as all queries are written in

the Transact SQL script found at this URL :-

http://www.winclickstartvulcan2.net/ClickStartExec2.html?introduction.htm

If you use the link you will see the following in your Browser :-

And in fact you can also us the next web link to find out details of ADO.NET itself :-

http://www.winclickstartvulcan2.net/WebNotes/ClickStartADO/ClickStartADO.html

These notes have much more detail on the STATIC class(es) than I will place within these HELP notes.

Below is the front screen for the ADN.NET eNotes and examples are in Vulcan.NET, which is very similar to C#.

The specific web link to take you straight to the T-SQL section is that shown below :-

http://www.winclickstartvulcan2.net/WebNotes/ClickStartTSQL/ClickStartTSQL.html

You will see the following opening screen :-

Do be aware that T-SQL and how it is used to query SQL databases is a HUGE topic in its own right.

However, it can be used as reference material and the Search facility is VERY useful for this purpose ;-0)

The STATIC class we shall use a small part of in our 'Ground Probe 2' application is shown below in a collapsed view :-

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

As you can see in the above image we see only the Regions and Method names when collapsed.

If I now expand a most relevant section we then see a bunch of STATIC methods to send queries to the SQL Server, and some of these

also return data in various formats - datasets, scalar values, or even a data reader object which keeps the connection to the Server open.

All the rest close the connection as soon as the data is retrieved.

Note in particular the method on line 589 as this will be used most often in our application - it gives us back a table of data

held within a data set object.

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

The above STATIC methods actually use within themselves, the following methods which are related to setting

a connection string and then opening and closing it when required.

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

Since for our application purposes we do not need to know the details of these, let us just use them to get results!

However, there is one other IMPORTANT STATIC method which we will use regularly, and that is the one to do a

quick and easy backup of the whole database.

Check this out for ease of use :-

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

Well, in fact since I initially wrote the STATIC class 'SQL_my_funcs' I have added to and improved the backup

facilities.

The method which is associated with the button click event (of the backup button) is shown below :-

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

So in fact we need to see the 'Do_GP2_CyclicBackup' STATIC method.

If we study the next illustration we see that the method is actually in a different class - see below :-

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

This is simply because the 'Layer6' class is general to all ADO.NET work, but the Layer5 class is directly related

to the Ground Probe application.

Let us now open this method to see what it does - checkout below :-

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

As you can see above it is one of those methods which needs to do a lot of 'huff and puff' and not a lot of 'crunchy' or

interesting things.

Basically it is the same as the 'BASIC' backup except it does a lot to find the next cyclic file-name, and select the

correct folder etc. etc..

And here is the code to select the next file name - check out the following image :-

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

Okay then, enough about STATIC things, lets move on ...................

PHH - 17th. June 2012.


working Query script

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

USE [C:\USERS\GROUNDPROBE08.MDF]

GO

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[DataDumpIn]') AND type in (N'U'))

BEGIN

DROP TABLE [dbo].[DataDumpIn]

select 'Our DUMP Table - [DataDumpIn] - was found and DROPPED !!'

END;

go

--==========================================================================

USE [C:\USERS\GROUNDPROBE08.MDF]

GO

/****** Object: Table [dbo].[DataDumpIn] Script Date: 01/06/2012 08:16:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [DataDumpIn](

[TimeStamp] varchar(21) NULL,

[Record] [bigint] NULL,

[batt_volt] [float] NULL,

[Temp(1)] [float] NULL,

[Temp(2)] [float] NULL,

[Temp(3)] [float] NULL,

[Temp(4)] [float] NULL,

[Temp(5)] [float] NULL,

[Temp(6)] [float] NULL,

[Temp(7)] [float] NULL,

[Temp(8)] [float] NULL,

[Temp(9)] [float] NULL,

[Temp(10)] [float] NULL,

[Temp(11)] [float] NULL,

[Temp(12)] [float] NULL,

[Temp(13)] [float] NULL,

[Temp(14)] [float] NULL,

[Temp(15)] [float] NULL,

[Temp(16)] [float] NULL,

[Temp(17)] [float] NULL,

[Temp(18)] [float] NULL,

[Temp(19)] [float] NULL,

[Temp(20)] [float] NULL,

[Temp(21)] [float] NULL,

[Temp(22)] [float] NULL,

[Temp(23)] [float] NULL,

[Temp(24)] [float] NULL,

[Temp(25)] [float] NULL,

[Temp(26)] [float] NULL,

[Temp(27)] [float] NULL,

[Temp(28)] [float] NULL,

[Temp(29)] [float] NULL,

[Temp(30)] [float] NULL,

[Temp(31)] [float] NULL,

[Temp(32)] [float] NULL,

[Temp(33)] [float] NULL,

[Temp(34)] [float] NULL,

[Temp(35)] [float] NULL,

[Temp(36)] [float] NULL,

[Temp(37)] [float] NULL,

[Temp(38)] [float] NULL,

[Temp(39)] [float] NULL,

[Temp(40)] [float] NULL,

[Temp(41)] [float] NULL,

[Temp(42)] [float] NULL,

[Temp(43)] [float] NULL,

[Temp(44)] [float] NULL,

[Temp(45)] [float] NULL,

[Temp(46)] [float] NULL,

[Temp(47)] [float] NULL,

[Temp(48)] [float] NULL,

[Temp(49)] [float] NULL,

[Temp(50)] [float] NULL,

[Temp(51)] [float] NULL,

[Temp(52)] [float] NULL,

[Temp(53)] [float] NULL,

[Temp(54)] [float] NULL,

[Temp(55)] [float] NULL,

[Temp(56)] [float] NULL,

[Temp(57)] [float] NULL,

[Temp(58)] [float] NULL,

[Temp(59)] [float] NULL,

[Temp(60)] [float] NULL,

[Temp(61)] [float] NULL,

[Temp(62)] [float] NULL,

[Temp(63)] [float] NULL,

[Temp(64)] [float] NULL,

[Temp(65)] [float] NULL,

[Temp(66)] [float] NULL,

[Temp(67)] [float] NULL,

[Temp(68)] [float] NULL,

[Temp(69)] [float] NULL,

[Temp(70)] [float] NULL,

[Temp(71)] [float] NULL,

[Temp(72)] [float] NULL,

[Temp(73)] [float] NULL,

[Temp(74)] [float] NULL,

[Temp(75)] [float] NULL,

[Temp(76)] [float] NULL,

[Temp(77)] [float] NULL,

[Temp(78)] [float] NULL,

[Temp(79)] [float] NULL,

[Temp(80)] [float] NULL,

[Temp(81)] [float] NULL,

[Temp(82)] [float] NULL,

[Temp(83)] [float] NULL,

[Temp(84)] [float] NULL,

[Temp(85)] [float] NULL,

[Temp(86)] [float] NULL,

[Temp(87)] [float] NULL,

[Temp(88)] [float] NULL,

[Temp(89)] [float] NULL,

[Temp(90)] [float] NULL,

[Temp(91)] [float] NULL,

[Temp(92)] [float] NULL,

[Temp(93)] [float] NULL,

[Temp(94)] [float] NULL,

[Temp(95)] [float] NULL,

[Temp(96)] [float] NULL,

[Temp(97)] [float] NULL,

[Temp(98)] [float] NULL,

[Temp(99)] [float] NULL,

[Temp(100)] [float] NULL,

[Temp(101)] [float] NULL,

[Temp(102)] [float] NULL,

[Temp(103)] [float] NULL,

[Temp(104)] [float] NULL,

[Temp(105)] [float] NULL,

[Temp(106)] [float] NULL,

[Temp(107)] [float] NULL,

[Temp(108)] [float] NULL,

[Temp(109)] [float] NULL,

[Temp(110)] [float] NULL,

[Temp(111)] [float] NULL,

[Temp(112)] [float] NULL,

[Temp(113)] [float] NULL,

[Temp(114)] [float] NULL,

[Temp(115)] [float] NULL,

[Temp(116)] [float] NULL,

[Rain_mm] [float] NULL,

[SlrW_Avg] [float] NULL,

[SlrMJ_Tot] [float] NULL,

[Pulse1] [float] NULL,

[Pulse2] [float] NULL,

[Pulse3] [float] NULL,

[Pulse4] [float] NULL,

[Pulse5] [float] NULL,

[Pulse6] [float] NULL

) ON [PRIMARY];

go

--===============================================

USE [C:\USERS\GROUNDPROBE08.MDF]

go

bulk

insert DataDumpIn

from 'C:\BenData\GroundProbe2\LP 180512.dat'

with

(

firstrow = 4,

fieldterminator = ',',

rowterminator = '\n'

);

go

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

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

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

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

PHH - 17th. June 2012.


SQL Tables and Database

This subsection is for anyone who is new to SQL, and unfamiliar with how it operates on a PC platform.

SQL Server (MSSS) is Microsoft's flagship database management software / application.

MSSS has a few different versions, and getting these correctly 'matched up' can be crucial to success.

The actual server (referred to as MSSS) now comes in the older version of 2005, as well as 2008 R2, and

even the very latest called version 2012, or 'Denali'.

The SQL Server is an application which needs to be installed on a Windows O/S 'box', or PC.

The databases used with the Server are files which are effectively sealed. These can be attached to the

instance of the Server.

Attachments are just made with a click of the mouse within the Management Studio (MS) environment.

Databases can also be detached in a similar way.

Note that databases made for one Server version don't usually connect and open with any other!

You will get a flavour of what is going on by seeing below how things are done within MS itself.

Firstly, we need to open an instance of a SQL Server - see the menu option selection below :-

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

This selection brings up a dialog screen, which has 'memory' of the last few servers selected (in order),

so check this out below :-

Obviously, I chose version 2008 R2 last time I used the MS 2012 shell software.

Also please note that the Management Studio apps are backwards compatible, so me

using a 2012 version of MS with a Server version of 2008 R2 is not a problem.

However, the reason I am selecting 2008 R2 is because my DB was made for 2008 R2 - and that

is crucial !

In the following image you can see some of the other options - showing versions of MSSS I have used

recently on my development PC.

Note in particular the new 2012 option of the 'LocalDB' server - this is special and will be discussed

later in another section.

Run your eyes over what is available in the list shown below :-

For the purpose of this demonstration we will visually follow up what happens in MS when

we select the 'LocalDB' from the list - see below :-

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

As soon as the Object Explorer (OE) pane is populated we see any databases attached to the Server.

Clicking in the OE pane on the tables 'node' (folder?) we see that the list of current tables is displayed.

Unfortunately, for this demonstration, we do not have many at all, only two in fact.

However, if we select the 2008 R2 Server we see the following :-

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

And opening this out to display DBs and Tables we see many more in the Ground Probe DB.

In fact these are all the tables that exist at the time of me writing this subsection. And of course they

may change slightly as work progresses, but not a lot I should guess.

Study the list below, and notice the latest Table addition, that of 'MasterDumpSets' :-

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

Above we see the blue highlight is over the database name, and it just so happens that the

MS shell has auto-named it using the folder and path and file name. Just don't ask why, I have

been wondering that for some years.

What I do know is that changing it using the 'Rename' context menu option is NOT a great idea,

as there are times when this cause the developer serious grief !!

In the following image the blue highlighting shows the Table into which we first read our logged data.

Check it out :-

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

Now opening the columns node shows us details of fields (or columns).

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

There is a simple way to see all the data in the first one thousand rows - see below :-

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

And now we have selected this option, see the results as shown below :-

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

Choosing to see the details of a table with fewer columns is probably more instructive.

We have chosen the "DumpSetHeader" table - see below :-

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

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

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

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

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

PHH - 17th. June 2012.


Application input Forms

Okay then guys, we know about the SQL Tables which have been designed to hold the logged data,

however we need to see just how to use the application (Ground Probe 2) to get the data from the original

text string data files into our SQL data tables.

At one selection level down from the main screen to our Ground Probe app., we see the following

input screen - first take a look and we can discuss it further :-

In the above image we see a data grid control which simply allows us to see any table of returned data

which results from any processing, usually from a SQL query.

Initially, when the form is first opened, the grid is blank, as shown in the next illustration :-

If however we choose the 'Select Log File' button we get a 'File Open' dialog screen as shown below :-

And after a selection we currently see a Message Box just to aid in application design and testing - check it out :-

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

Now you see where the grid data came from in the first image in this subsection.

The log file has been read into our temporary 'holding' table called 'DataDumpIn'. Next we need to process this

'Dump' table to create more usefully structured SQL tables, and without any duplicated data.

Currently, all we need do to kick this process into action is press the second button down, called

'Dump Table DATA to SQL'.

Improvements :

Looking at things as I write these notes it would seem like a good idea to add another button to allow us to see the

logged data.

Although when we read in any new selection it causes the grid to show what has been read in, we need to be able to

see what is in the temporary table before we take any actions - just see it as it is.

This we can now do with the new 'View' button.

Take a look at the following image, and see the script which is also displayed when we click the new button :-

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

The resulting display is shown, once the data has been returned from the Server - see below :-

Checking After Input :

PHH - 20th. June 2012.


Primary and Foreign Keys

For anyone new to SQL they need to know that Primary Keys (PK) are rather important to a well functioning

database solution.

They also need to know that the use of Foreign Keys with primary Keys is also rather important.

A Primary Key is a unique identifier on the rows of a particular table. Each table can have its own PK and it does

not need to be an integer.

However, if we have a PK which is an integer we can get the database to issue the next / subsequent identity for

us and no duplication will take place.

What is more is that the DB will not re-issue an ID which has previously bee issued and then the record deleted.

Relationships may be set between tables using PKs / FKs.

So far in our simple Ground Probe app we have a few PKs on show. Let us first look at the 'BatchHeader' table.

In the following image note the icon next to the 'Batch_ID' bigint :-

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

If we now open the table designer for the 'BatchHeader' we see the following :-

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

In the above image we see how the identity is checked, and that the seed is et to 100 (means

the first value will be 100) and the increment to 1 - so the values will go up by one each time a

value is automatically given by the DB.

Now then, for the 'DumpSetHeader' table we see that it has its own PK (called 'DumpSet_ID') and

that this table also uses 'Batch_ID' as a foreign key :-

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

If we look at the 'Keys' set on the 'DumpSetHeader' we see the following.

Why is this ? - see below :-

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

There is a PK and not a FK !?

When in fact if we look at the classic Microsoft DB called 'AdventureWorks' we see the following :-

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

Notice that in this case we see both PK and FK icons in the columns, as well as two entries in the

Keys node / folder.

It is not sufficient to use the value of another tables PK as a Foreign Key, you also need to let the DB know

you are doing this. However, often developers leave such relationship 'connections', or 'hook ups' until they

are more certain that their DB structure and supporting code works as exactly as they wish.

Once the DB knows of PK / FK relationships then it will restrict row deletions to save data being lost.

Okay then guys, we have gone far enough, if you now wish to see how to set up a relationship then go

to the child topic of this section.

I hope to see you there ...............

PHH - 17th. June 2012.


Setting up a Relationship

Okay the guys and gals, lets start at the end, a VERY good place to start !

Take a look at the next image to see the PK and FK icons.

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

As you can see in the above image we now have our table FK set to be related with

the PK in the 'BatchHeader' table.

I have to confess the way the dialog screens work for setting these relationships is not that clever.

In fact I would say it is rather misleading - but then we will have to stick with it, as there is no other GUI way

of doing this ;-0)

So my approach is to go to the Table in which the FK resides, then call up the Relationships dialog with a

right click - it odes not seem to matter where you right click as long as it is in the Table editor surface for

the table with the FK in it.

Here is what you will get :-

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

Then choosing the Relationships option we see the following :-

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

So none exist, and we are going to make one.

So obviously click 'Add', and see the following :-

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

This is now where you need all your guile and intelligence ;-0)

Make sure you select on the R.H.S. the PK table where your PK exists, and then on the L.H.S. the FK

table (in this case DumpSetHeader) as well as the FK field.

This is where it helps to have called your columns something sensible and meaningful - like the same same

and an ID in there somewhere ?!

Let us continue with my example, see the next image which is what we get from the above when we

click 'OK' :-

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

Well guys, that is it. When we click close we see the entries in the Object Explorer (OE) of MS and

we may first have to refresh the Tables node with a right click and a context menu selection.

By the time you use the Ground Probe app there ought to be a few more relationships in place.

Testing our Relationships :

Okay then, how brave are you ? Shall we try to delete (remove completely) a row from the 'BatchHeader' table ?

If we have PK values from the BatchHeader table in rows in our DumpSetHeader table (as we do) then we should

not be able to remove the PK row, as this would leave orphans, and break the referential integrity of the relational

database. Remember SQL Server is an RDMS ! I leave you to look up the acronym !

Here goes then, view carefully and thoroughly the following image :-

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

As you can see we are attempting to remove line three,with ID equal to '102'.

Let us see what we get when we select to delete, check this out below :-

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

Oh! it does not as yet object, but then that is probably because we are still in the GUI environment and have

not as yet communicated with the DB engine.

Lets go that step further p see below :-

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

Ah! that's better, it sees that something is up, AND, a lot of the message does revolve around integrity

and the referential constraint etc. etc..

So we have not deleted row three, and so have no orphans to worry about ;-0)

Now time to move on ...............

PHH - 18th. June 2012.


Removing / Ignoring the Duplicates

PHH - 1st. July 2012.


Managing the Database

PHH - 1st. July 2012.


Setting up SQL Server 2012

PHH - 1st. July 2012.


Backing-up the Database

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

PHH - 1st. July 2012.


Restoring the DB from a Back-up 'set'

PHH - 1st. July 2012.


Querying the SQL Database

When SQL Server is used as a back-end data source querying the data takes place on the

Server itself.

This means that we send a 'script' to the database 'engine' and returned is a data set. In some

cases the data set is a single row, single column, data set and is then called a 'scalar' value.

Most often the returned data set is a table of values.

Certain query scripts (like that for Back-up of the database) cause no return data, and just perform

a 'task' at the database (DB) end of things.

Whether an error occurs or not, the error code may always be obtained using a function of the system

which is ' @@ERROR ' and this must always be the very next query script to be sent.

The following image shows what we get after a successful query - see below :-

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

Okay the, enough of background information to querying, let us take a real-life situation from

the Ground Probe project, and see what we get when we ask for an output of all of the fields (columns)

in each of the stored rows within the 'Reading' table.

Checkout the following image, and particularly the bottom right of the image :-

As you can see, there are a lot of rows (or records) within the Reading table, even with data from

just a short time-scale of a few weeks.

There is almost one third of a million data rows from the 'unrestricted' query above.

However, we usually apply restriction to many of our queries, these are often referred to as 'filters'.

Let us now look at such a typical 'filter' - below we restrict the return data set to rows associated

with sensor number 13, and when the logged value is larger than nine.

As you can see in the image shown below, there are now less than two thousand - check it out :-

Let us add some further filter conditions to our query.

The following query uses the Reading table and joins to it the data from the 'Extra' DumpSetHeader

table - this allows us to use Date and Time related fields within our query filter.

Below is the image of the query and some returned data, for such a more complex query script.

The number of rows returned in the dataset is now reduced to 520. The extra filter restrictions are that

the time of day is afternoon, it is in the day (that is before 8.00 pm) and the value is larger than ten.

Checkout the following details :-

Usually, the restriction we will commonly make for querying the Ground Probe data is one based on

date range.

Most commonly you will see a date range applied before any subsequent filter conditions. By default

on many application input screens we will ask for the last 14 days worth of results.

Below we can see a typical data range query, but the subject will be dealt with in more detail in a following

section of these notes - check this out :-

Look at a subsequent subsection where we deal with Dates and Times, and any manner of related query.

For now, remember that the filtering of results is done in the 'WHERE' clause of the query script (sometimes

called a query statement).

The 'FROM' clause tells the database engine which data to retrieve, the 'SELECT' clause defines the fields to

be returned, and the 'WHERE' clause which rows to include in the data returned from the back-end Server.

So far we have been sending our queries from the Management Studio (MS) 'shell'. MS is an application from

Microsoft which is designed for managing the whole of the SQL database.

However, when we come to creating our own applications we will need to send queries in a more direct way.

We will cover how to send SQL queries from .NET code in a subsection shortly to follow.

Let us move on ....................

PHH - 17th June 2012.


Erros in our Query scripts

Although we hope to make few (if any) mistakes in our queries, we do need to know what happens

when we do make an error.

Let us take a previous successful query and introduce an intentional error.

Although the 'clever' MS 'shell' informs us of a possible error before we send it to the DB engine (see

the red wavy line) we will do so anyhow, just to see what we get.

Study the following image where we are using the column name of 'DoNN' in place of 'DoN' :-

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

Now you may well say that the red message displayed is not very helpful - BUT - it does in fact

contain the correct error code.

Before we go any further, let us see what else the MS 'shell' can help us with - see below :-

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

Let us now ask the Server for the error code of the VERY last query we sent to it - check this out :-

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

So if we can send this short query ' select @@ERROR ' from the MS shell we can also send it from

our .NET code when we need to.

For now let us just see what the System functions has to say when we access then directly from the

Object Explorer in MS - see below :-

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

You may be interested to know that all 'in-built' functions, known "System Functions" are available at the

click of a mouse while using Management Studio. This is VERY useful to the developer.

Just remember that the '@@ERROR' function call has to be the very next query in order to get the error

code returned, to get some idea of the fragility of the use of this query, see the following image where we

do a 'double test' :-

I would excuse anyone for thinking that this query would return 207 and 207 !

It is best if you think about this yourself - think about how many queries we are sending to the Server.

Time to move on, and if we make no mistakes then we do not have to worry about errors ;-0)

PHH - 18th June 2012.


Query Scripts, MS to ADO code

Okay then you guys, we have seen a little of how to send query scripts to the SQL Server directly from the

Management Studio (MS) shell. But now we need to see how we do this from our application code.

Quite simply, we need to take our tried and tested script from MS and put it into one STRING variable.

This string can then be sent to the SQL Server using a suitable .NET framework class / method.

However, you need to realise that in order to keep application coding as simple as possible, the ADO.NET method

has been encapsulated into an easy to use STATIC class with STATIC methods.

In the following image, of some Vulcan.NET code (very similar to C#), we see that line 197 is the one which uses

the script to query the Server.

The dataset object oDS is returned in the same statement, and from this dataset we get the first data table, as

there is only one table returned in the 'set'.

Study the following image to see how the dotNet code looks for sending the same script to the SQL Server :-

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

[ ** Note ** a space is inserted at the end of each script line so that constant text does not merge and give an error ! ]

When we run the application we see the detail below in the message box created by the code :-

Note in the above illustration that we seem to have suitable spaces between the command words, variables, and constants.

This is now looking more like the script we created in the MS shell.

Finally, we need to deal with line 200 of our code, and this is where the returned data table is associated with

the data grid control on the WPF form.

Study the following illustration in which we see the returned data displayed in our data grid :-

Although the whole point of having the ADO.NET related methods placed in one STATIC class, is to allow

developers an easy way to use then, and hence access the SQL Server, we do need sometime to have a look

at what is there ;-0)

Below is the method we used on line 197, and it shows the amount of code needed to set up the connection to the

database and the like, and once again it is only one line (numbered 616) which actually does the business !

Study the following image to see what this method looks like :-

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

The notes will cover more about the STATIC classes for ADO.NET use in another section and subsections, it

is sufficient for you to know now that they exist, and that they are easy to use.

*** SUGGESTION ***

I strongly suggest you look at the learning eNotes at :-

http://www.winclickstartvulcan2.net/WebNotes/ClickStartADO/ClickStartADO.html

Where you will find more basic information about ADO.NET, like the following details :-

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

Finally, note that the DataSet and DataTable classes used above are all part of the ADO.NET approach to using

and presenting data.

Time to move on ..................

PHH - 17th June 2012.


Returned 'DataTable' Objects

PHH - 17th June 2012.


'DataTable' Objects Displayed in Grids

PHH - 17th June 2012.


Queries based on Date and Time

In developing the Ground Probe application we will commonly use filters based on Date and Time values.

The common dotNet data type for date and time is the one called 'DateTime'. This is actually a 'structure' and

not a class.

However, if we only have DateTime values then we need to process them each time we need parts of the DT value.

Since we have thousands of such values, we are best to 'break up' the DateTime value of the 'Timestamp' field

into useful 'sub-parts' so that our querying can be made much easier.

Example :

Let us look at an example where we use one of the extra fields of the 'DumpSetHeader' data to look at data

within a single day.

The 'Date' field in the 'X' for 'Extras' table of the DumpSetHeader can be used to compare values for a particular

day, without the need in the query to strip off the time related part of the Timestamp column.

Lines 6 and 7 in the image shown below use the Date field in this simple way, and all values within a single

day are compared in an aggregate fashion.

Study the details shown below :-

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

The above image shows that we seem to have 19 different days worth of data.

'INCLUSIVE' :

Also please realise that the command 'BETWEEN' takes two inputs and the results include the limiting values,

as well as those in the range itself. So for our above example May 13th. and May 31st. will be in the set.

Saving the Script to File :

You need to know that it is easy to save the script displayed in a Tab of the editor / work area of the MS shell.

Below we see the result of a mouse right click on the yellow (active) tab in the work area.

We get a dialog screen which allows us to provide a folder path, as well as a file-name for the script being saved to disc.

Checkout the following details :-

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

So after we do the 'Save' we can then see the file in W.E. and open it in Notepad too - see below :-

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

Do be aware however that the whole of the contents of the Tab pane is saved, not just the section which is a tried and tested

(successful) SQL query !

But this in itself may prove to be useful to many developers.

More Examples :

PHH - 17th June 2012.


Exporting Query Results to Excel

When using many software application today, we see options of some sore or another, which allow the

user to 'export' the data in focus to Microsoft's Excel platform. By this I mean that we have a facility to

move (or copy actually) so data - usually in table format - to a file which can be opened and read by the

Excel spread sheet application.

Once the data can be read into Excel it may then be saved in standard Excel worksheet format, for later

reuse.

Below is shown the menu option to do such an 'export' from Reporting Services which comes with MSSS 2005.

Check this out :-

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

In the above image notice that CSV is listed separately to 'Excel' even though it can be read in by Excel itself.

CSV can be read by a number of different applications, and is VERY common, even though many developers

dislike it because all the data items have been converted to text strings. Developers and coder guys say that

the 'strong typing' has been lost, and many distinctively feel 'uncomfortable' about this.

When we convert any table data to Excel format we make files where the data types have been preserved, either

in full, or at least in the main part. DateTime values are the usual bones of contention - problems ?

Before we move on to look in detail at how some of this may be done, let us see how we can export data from

the Management Studio environment itself - directly from the returned data sets / tables.

Check this out :-

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

So from the image shown above, what exactly are our 'save' options ?

Well as the following illustration shows, only text and CSV it would appear :-

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

So if we save as CSV what do we end up with ?

See below for CSV saving :-

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

The files so saved, eventually called 'test4HELP.csv' is shown in part as opened in Notepad - see below :-

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

You can even recognise some of the data lines, from the original results table, to the Notepad text lines.

Time to move on to see more details of how this is all done and achieved by .NET code .......................

PHH - 17th June 2012.


Different Formats of Export Data

Throughout our 'Ground Probe' application we have our tabular data in a .NET object called

a 'DataTable'. This is not a 'visual' object and needs to be displayed in WPF using a GUI

control named a 'DataGrid'.

Often, throughout the screens of the 'Ground Probe' application you will see 'DataGrid' controls

showing our results - there is always a 'DataTable' object behind this visual representation, this

is usually code as ' oDT ' (without the single quotes).

Shown below is a typical example of this, the data is what has been read in from the raw data

files sent from the data logger - check this out :-

If you need to know more detail regarding the ADO.NET 'DataTable' class I suggest you follow

the link shown below :-

http://www.winclickstartvulcan2.net/WebNotes/ClickStartADO/ClickStartADO.html

Chapter 2 is the one which will help you find out more.

Briefly, the DataTable hold rows of data based on column types. Each column (or field) has a

defined data type, like STRING, INT, Boolean and DateTime - there are many others.

So the data in each column position within the row is said to be 'strongly typed'. Even a STRING

is a strong type, but if a DateTime is represented in string form it is said to have lost its strong

typing.

If the following subsections we will move on, to see how the .NET code allows us to convert any

DataTable into different formats, each of which has its 'moments', or 'time and place', as we would

say in the vernacular ;-0)

On a personal note I feel uncomfortable with CSV - some would say this goes as far as 'dislike' !

For this and many more reasons I have not time and space to expound here, I prefer the more

professional approach of making Excel workbooks and sheets shown in the later subsection.

There are further benefits to the NPOI approach covered in that section, in that it allows us to do

charts directly from code, and also to move to other applications apart from Excel - those in the

Office suite and open office environment.

Time to move on .............................

PHH - 17th June 2012.


To CSV

The well known (and pretty old) CSV - Comma Separated Values - format for data is simple

to understand.

Each line of data within a data file has the same number of fields, and the data items in the fields

are separated by commas. This is all fine and dandy until we get commas within a string field, like

that of an address line - example "33, Front Street".

The simple answer is to virtually do as I have done above and enclose the whole single data field

within double quotes. Oh! then we can worry about what if the string field has a double quote

character (or more) within it. Probably by now you are starting to see why I am at best 'wary' of

the CSV format.

Of course there are tricks to counter some of this, like replacing an double quotes with single ones

before the string encapsulation - BUT ..................

If we see some of the raw data from the logger open in Notepad - see below :-

We clearly see that the logger has put the DateTime data within quotes. So it is a string value, even

though there ought not to be commas within the characters representing that type of data.

Why make a fuss ? :

Some of you may be asking yourself why I am making all the fuss over a few commas, and the odd

double or single quote.

Well, the impact on importing data is quite horrific, as field positions can get very easily swapped over,

( position 6 becoming 7 for example) and the rest of the end of the data line can get forgotten, (or thrown

away) and much more quite 'nasty' stuff too.

The sorts of things that nightmares are made of - well, to a database guy that is ;-0)

And, al lot of this stuff is not always predictable, as it can vary on what application is creating the CVS

information, as well as what is reading it in and trying to make sense of it.

So let us look at some .NET based code to turn a DataTable into a CSV file.

In the following image notice line 151 which sends the DataTable object to a suitably code method - see below :-

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

So if we supply a DataTable object and a filename, then what does the code look like which processes these ?

Checkout the following method - study it carefully :-

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

In the above image we turn every data item within a row into a string, from the strong type value which exists.

However, if the column details relating to the item in the row show that the type is STRING itself, then we need

to try and clean up, or improve, the STRING content so that we can avoid certain characters.

If we choose we could remove commas, or replace them with alternative characters, as well as making sure we

have no double quote characters within our string.

I think that all STRING fields ought to be checked for " existence, and replaced by ' if found.

Study the following dotNet code to see how I have handled the swapping of " to ' within the whole string :-

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

The sorts of results we see from using this code are shown in the following images.

Study the image below to see how the double quotes from the data logger have been replaced by single ones.

I am not suggesting that this is a useful task in the current application, but am just using the raw data as useful

test data to explain the process required.

Checkout the before and after in the following image :-

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

So, seen above, we have the " of the logged raw data being replaced by ' in the CSV text placed into the file on disc.

If we now look at the next image we see the created CSV file (from the above larger method) and how it looks when opened

within Excel itself :-

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

Okay then you guys, all of this character switching and the like may have you confused, well here is

some more to confuse you even further ;-0)

Let us actually see what we have in the CSV file itself, by using Notepad to open it up.

Check this out :-

Let us explain the above in detail, as we seem to now have both single and double quotes present.

The raw data from the logger placed the 'timestamp' field in double quotes - these have been replaced by our

'cleaning' method code shown above.

Then the whole field data was wrapped in a pair of double quotes by our method called 'MyExportCSV'.

If you like, the single quotes belong to the data, and the double ones belong to the encapsulation process, or procedure.

The point of this is for general data fields related to contact addresses the following is 'safe' data :-

" 'Oceanview', 39, St. John's Crescent, Grangetown. "

The whole of t he above string of characters will be seen as one data item.

Any comma or single quote will not mess up our future processing, and 'Oceanview' is quite likely to have started

out in the original data as "Oceanview" for the name of the house.

Okay then, we know and understand a lot more about CSV, both format, data, and files.

Let us now move on to something simpler to achieve - XML file creation.

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

PHH - 17th June 2012.


working code

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

STATIC METHOD MyExportCSV(oDT AS DataTable, cFile AS STRING) AS VOID
LOCAL sRow, sDel, sQuote, sContent AS STRING
LOCAL sb AS StringBuilder
LOCAL sw AS StreamWriter
sb := StringBuilder{}
sContent := STRING.Empty
sRow := "" //----- alternative -----
sDel := ""
FOREACH col AS DataColumn IN oDT:Columns
sRow += sDel + col:ColumnName
sDel := ","
NEXT
sb:AppendLine(sRow)
FOREACH row AS DataRow IN oDT:Rows
sDel := "" //----- just the first time, as no comma required -----
sRow := ""
FOREACH col AS OBJECT IN row:ItemArray
IF col:GetType() = TypeOf(STRING)
sQuote := e"\""
//----- need to check the contents of the following string -----
//----- and adjust if needs be -----
sContent := CleanString(col:ToString())
ELSE
sQuote := STRING.Empty
sContent := col:ToString()
ENDIF
sRow += sDel + sQuote + sContent + sQuote
sDel := ","
NEXT
sb:AppendLine(sRow)
//----- above adds an end of line character to the row -----
NEXT
IF sb:Length > 0
sw := StreamWriter{cFile, FALSE, Encoding.UTF8}
sw:Write(sb:ToString())
sw:Close()
sw:Dispose()
ENDIF
RETURN

STATIC METHOD CleanString(cText AS STRING) AS STRING
LOCAL cResult, cDQ AS STRING
cDQ := e"\"" //----- .NET escaping syntax, effectively -----
//----- means it has the value of a single 'Double Quote' character -----
IF cText:Contains(cDQ)
//----- change to a single quote -----
cText := cText:Replace(cDQ, "'")
ENDIF
cResult := cText
RETURN cResult

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

PHH - 29th. June 2012.


To XML / XSD

Well, well !

Would you believe it, to convert our data from the DataTable object into what many may call a 'more complex'

format, that of XML, is as simple as pie, and only one short .NET code line.

Maybe that tells us something in itself ?!

There is a method provided within the dotNet framework which does this for us, one which is a member of the

DataTable class itself.

Checkout the following image details :-

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

We use the above code in a similar way to that for CSV previously - see below :-

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

And the results can also be seen just as easily in Notepad, with the new file seen in the background, within

the Windows Explorer tool - see below :-

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

What is XML and what is XSD ? :

PHH - 29th June 2012.


To HTML

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

PHH - 29th June 2012.


To Microsoft 'Excel'

Okay, I can hear you all protesting - saying "why didn't we just scrap the other, earlier approaches,

and concentrate on doing it 'properly' ?

Well, the answer is quite straight forward, 'complexity'.

Although we have a way to make proper Excel worksheets and workbooks, as well as graphs too, we

need to employ a method which uses quite extensive and more complex detailed DLLs.

DLLs are dynamic linking libraries - or in layman's terms, they are a set of .NET classes which allows us

as developers to make the more complex Excel files like '.xls'.

So we need to weigh up what we require before we dive in and do it one way or another.

In many ways it is good to keep it simple, but for many other reasons we may wish to do things the

correct way.

Certainly, if we do use the DLL approach we can get as complex and as complicated as we like, when we

require it. Whereas a simple way always has limitations.

Let us study more about the 'NPOI' approach using DLLs.

NPOI 1.2.5 - Open Source Software :

The following web link is currently active :-

http://npoi.codeplex.com/releases/view/49524

We see the following screen when we use it - see below :-

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

I suggest you get the latest version you can, at the moment it seems to be version 1.2.5 it would seem.

Here is more information on NPOI :-

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

And once we download and install etc., etc. we can move/copy a couple of files into our working folder as seen in

the following image :-

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

Before we can use the NPOI classes in our own dotNet code we have to add a couple of references into our solution within

Visual Studio. These are named 'NPOI' and 'Ionic.Zip'.

The following screen capture shows the added references :-

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

And to show you how it looks at the point of adding these references, checkout the next image

which is shown below :-

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

Obviously, I moved the DLLs and any supporting NPOI files into my 'Release' folder (application folder)

before adding the references via the dialog screen.

Level of Complexity :

To gain some idea of the increased level of complexity that this DLL approach lends to the making of Excel files,

take a look at the 'Reflector Object Browser' shown below :-

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

Okay, this is not so frightening - BUT - how about when we expand it ? - see below to be 'frightened' ;-0)

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

Oh! dear, Oh! dear, what a lot more complex than in previous subsections - BUT - we ain't seen

nothing as yet !

Checkout below what we get when we expand the 'UserModel' which is what seems to be used as the main

namespace in the examples provided. Well those few I have so far looked at.

Now checkout the set of classes we get when we look into this namespace - see below :-

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

And then if we look at a main class, like that of the 'Cell' we see even more complexity.

Okay guys, I hear one or two of you saying that CSV doesn't seem so bad ;-0)

Arrrh ! yes, but we can do a lot of nice stuff with all this complexity - honest !

Let's see what the Cell class looks like - a few properties and methods - see below :-

Enough on the 'complex issue' side of the argument.

Let us view some success at making a file in '.xls' format.

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

Yep! all the above colour and formatting were done from our dotNet code, and the different colouration

is due to a conditional logic which was applied to the data values themselves.

Quite neat you might think - try doing that from CSV dumping !!!

PHH - 29th June 2012.


Air Temperature and Rainfall

PHH - 1st. July 2012.


'Holes' in Logged Data

 Top Previous

PHH - 1st. July 2012.