Visual Studio 2010 Source Control using Windows + Git + Git Extensions + DropBox

The following lays out a way to use Git + Git Extensions + Drop Box with an existing Visual Studio 2010 project. This will allow you to collaborate across multiple PC’s on the same project.

There may be quite a few steps, but it’s all quite simple stuff…

1. Install Git
2. Install Git Extensions
3. Install Drop Box
4. On the machine with the working Solution / project;
1. Browse to your DropBox folder using Windows Explorer
2. Create a directory to hold your Cloud Based Repo’s, for example /repos
3. Create a directory in your new repos directory to hold your Remote Cloud Based Project Repo, e.g. /repos/myproject/
4. Open a Git Gui
5. Click “Create a new repository”, and point it to the directory housing the local copy of your Solution / Project.
6. Click “Ok”, and let Git Gui create a repository within your Solution location
7. Create a .gitignore file in your favourite editor, and add the following sub directories (don’t forget that the directories are case sensitive, and will require a trailing forward slash)
1. The “/bin” directory for all projects
2. The “/obj” directory for all projects
3. Any other directories which you don’t require in your repository
8. Open your new repository in Git Gui
9. It should show all files ready to be staged and committed in the top left window.
10. Make sure this list contains all the files you’re expecting
11. Select all files and select “Stage to Commit” from the “Commit” menu
12. Add a commit comment such as “Origin”
13. Press Commit
14. Choose “Add” from the “Remote” menu
15. Choose a name for your remote repository. e.g. DropBox
16. In windows explorer copy the path to the remote repository contained in your DropBox folder at Step 2 above
17. In the “Further Action” section, choose “Initialize Remote Repository and Push”. This should then setup your Cloud Based Repository
    
5. On the Users’ machine who wishes to collaborate;
1. Create a directory to house the Solution / Project
2. Open Git Gui
3. Again create a repository at the location in Step 1
4. Again, add a Remote Repository in exactly the same way as in steps 13, 14, 15 and 16 above (NOT step 17!).
5. However, in the “Further Action” section, choose “Fetch Immediatly”
6. For some reason this doesn’t seem to actually go ahead and fetch all the project files, so flip back to windows explorer
7. Browse to the directory of your Solution / Project as created in Step 1
8. Right Click on the Directory and go to to Git Extensions –> Pull
9. In the “Pull From” section, and the “Remote” Drop Down, select the name of your Remote Cloud Based Repo. e.g. DropBox
10. In the “Branch” section, and the “Remote Branch”, select “master”
11. In the “Merge Options” section, make sure “Merge remote branch to current branch” is selected and that the “Auto stash” checkbox is clear
12. Hit “Pull”
13. You should now be able to see all your project files filled into your working directory

You can now;

• Make changes to the project,
• Stage and commit the changes
• Push the Changes to the Remote Cloud Based Repository
• Pull those changes on other machines
• Track changes between machines
• And much more

Free FAT Calculator Online

In a couple of previous Blog’s I’ve described the FAT16 and FAT32 File Systems, and how to calculate the various elements.

I’ve created a small Windows application to help you with finding the various elements.

Feel free to download it here…

http://www.pjgcreations.co.uk/FAT_Calculator_v1.zip

It will calculate all the elements I’ve discussed in my previous blogs, as well as, when you hover your cursor over the result boxes, show you the formulas in use.

It also allows entry in either Decimal or Hex, as well as calculating both FAT16 and FAT32 addresses.

The download is simply a zipped executable. You will require the latest Dot Net Framework 4 in order to run the program.

As a note, all Hex values should be entered as 0x followed by the Hex value. Also, you are unable to mix Hex and Decimal values, please enter either one or the other.

I hope it proves useful, and keep your eyes open for an online version of the same program coming soon!

Moving from FAT16 (SD) to FAT32 (SDHC) – Part 2 – Addressing Differences

In a previous blog I discussed moving from a FAT16 based SD card to a FAT32 based SDHC card.

In that blog I went through the differences in Initialisation procedures between the different SD Specification.

In this blog I’ll detail the differences in Addressing schemes between the devices and the two File Systems.

FAT32 Addressing Changes

Due to how the addressing scheme in FAT16 is organised, a top limit exists at 2GB. In order to allow the FAT File System to expand in capacity, various changes have been made to the File System, including expanding and altering registers entries.

The Boot Sector Entry:

The Boot Sector Entry or BSE, holds critical information regarding the partition’s layout. The following table shows the layout of the FAT16 BSE;

Offset	Item	Length	Notes
00h	Jump Code	3 Bytes	Contains executable Machine code to allow the Host System to jump past the BSE
03h	OEM ID	8 Bytes	The system name which formatted the device
0Bh	Bytes Per Sector	2 Bytes	Normally 0x2000 (512 )
0Dh	Sectors Per Cluster	1 Byte
0Eh	No of Reserved Sectors	2 Bytes
10h	No of FATs	1 Byte	The Number of FAT Entries
11h	Max Root Directory Entries	2 Bytes	For SD Cards this is usually 0x2000 (512)
13h	Total Sectors in Partition	2 Bytes	Only set if the device is < 32Mb. Will likely be unset 0x0000
15h	Media Type	1 Byte	0xF8 = Hard Drive
16h	Sectors Per FAT	2 Bytes	Used for reaching the Root Directory
18h	Sectors Per Track	2 Bytes
1Ah	No of Heads	2 Bytes
1Ch	No of Hidden Sectors	4 Bytes	Number of hidden sectors between the start of the disk and the BSE
20h	No of Sectors in a Partition	4 Bytes	Total number of Sectors in the Partition
24h	Logical Drive No	1 Byte	0x80 for Hard Drives
25h	Head No	1 Byte	Usually 0x00
26h	Extended Boot Signature	1 Byte	Always 0x29 denoting that Serial No, Label and Type fields are valid
27h	Serial No	4 Bytes	Serial No of the Device
2Bh	Partition Name	11 Bytes
36h	FAT Type	8 Bytes
3Eh	Executable Boot Code	448 Bytes	Allows the host to find the first file which boots the system
0x01FE	Executable Signature	2 Bytes	End of BSE

The BSE has been modified and expanded to allow the File System to expand past the 2GB limit. The modified BSE for FAT32 is shown below. The value positions which have changed or been added are shown in red;

Offset	Item	Length	Notes
00h	Jump Code	3 Bytes	Contains executable Machine code to allow the Host System to jump past the BSE
03h	OEM ID	8 Bytes	The system name which formatted the device
0Bh	Bytes Per Sector	2 Bytes	Normally 0x2000 (512 )
0Dh	Sectors Per Cluster	1 Byte
0Eh	No of Reserved Sectors	2 Bytes
10h	No of FATs	1 Byte	The Number of FAT Entries
11h	Max Root Directory Entries	2 Bytes	Always 0 for FAT32
13h	Total Sectors in Partition	2 Bytes	Only set if the device is < 32Mb. Will likely be unset 0x0000
15h	Media Type	1 Byte	0xF8 = Hard Drive
16h	Sectors Per FAT	2 Bytes	Always 0 for FAT32
18h	Sectors Per Track	2 Bytes
1Ah	No of Heads	2 Bytes
1Ch	No of Hidden Sectors	4 Bytes	Number of hidden sectors between the start of the disk and the BSE
20h	No of Sectors in a Partition	4 Bytes	Total number of Sectors in the Partition
24h	Sectors Per FAT	4 Bytes	Defines the total number of Sectors contained in each FAT on the SD Card. e.g. 0x911D0000 = 7569 Sectors per FAT
28h	Flags	2 Bytes	Defines flags for Active FAT Copy and Mirroring
2Ah	Version	1 Byte	Version of FAT Drive
2Ch	Cluster of Root Dir	4 Bytes	Defines the Start Cluster of the Root Directory on the SD Card. e.g.0x02000000 = 2
30h	FS Info Sector	2 Bytes	Defines the first Sector of the File System Info.
32h	Backup BSE Sector	2 Bytes	Defines the First Sector of the Backup BSE
34h	Reserved	12 Bytes	Reserved Bytes
40h	Drive Number	1 Byte	Logical Drive Number of Partition
41h	Reserved	1 Byte	Reserved Byte
42h	Boot Signature	8 Bytes	Defines the Boot Signature of the File system in use on the SD card. If this value is not 0x29, then the total number of hidden sectors is halved, and all values in the Boot Sector Entry after 0x001E should be ignored. e.g. 0x29 = Boot Signature = 41d
43h	Volume Serial No	4 Bytes	Defines the Serial number of the File system on the SD Card. e.g.0x3CF21620 = Serial Number = 242223241
47h	Volume Label	11 Bytes	Defines the Volume Label of the File system. e.g. "NO NAME    "
52h	File System Type	8 Bytes	Defines the type of File System on the SD card. e.g. "FAT32   "

As can be seen above, there have been quite a few changes to the BSE structure to cope with the conversion from FAT16 to FAT32.

Calculating the Root Directory Address (FAT16):

The formula use to calculate the Root Directory Address for FAT16 is;

RDE Address = FAT1 Start Address +
              ((No of FATs^{(From BSE)} *
              Sectors Per FAT^{(From BSE)}) *
              Bytes Per Sector^{(From BSE)})

Calculating the Root Directory location (FAT32):

From the FAT32 BSE table above, we can see we have a new field at offset 2Ch; “Cluster of Root Directory”.

This field allows us to Jump from the first Data Cluster of File System directly to the Root Directory. We do of course need to calculate the first Data Cluster Address. The formula for this is;

First Data Cluster =
         (Partition 1 Start Sector^{(From MBR)} +
         Reserved Sectors^{(From BSE)} +
         (No of FATs^{(From BSE)} *
            Sectors Per FAT^{(From BSE)})) *
         Bytes Per Sector^{(From BSE)}

Once we have the address of the first Data Cluster, we are then able to calculate the address of the Root Directory. This is accomplished simply by applying the formula used for locating a File or Folder;

File / Folder Address =
         Cluster 1 Start Address +
         (((File/Folder Start Cluster–2) *
         Secs/Cluster^{(From BSE)}) * 512)

Thus we apply the formula above feeding in the Root Directory Start Cluster, to obtain the Root Directory Address.

SD Card Addressing Change

As SD cards have increased in size, it has become necessary for devices to use a different addressing scheme. FAT16 devices address on a Byte by Byte level. However, as devices have grown, this has become an unmanageable number.

This change of course affects the use of CMD17 – Block Read. Where the argument supplied is a 4 byte address to read data from. As mentioned above, for normal SD Cards this address is a byte unit address. However, the larger devices now use a Sector by Sector addressing scheme. Of course, this requires that the Host system take this into account when addressing the card, such that it must record which type of card is connected and make a decision of which addressing scheme to use based upon this. The card type is detected using the ACMD41 command sequence detailed in my previous blog.

The simplest way to handle this difference in code, is to simply check the card size before passing the address to CMD17. If the Host is connected to an SDHC device, then simply divide the Byte Address by 512 to reach the Sector Address.

Alternatively, rather than multiplying all Sector Address locations by the Bytes/Sector value, the Host may simply check the Card Type, if a Standard SD card is connected, then simply multiply the Sector Address by the Bytes/Sector value to reach the Byte Address.

This decision will no doubt depend upon whether you are writing code from scratch or upgrading already existing code.

Moving from FAT16 (SD) to FAT32 (SDHC) – Part 1 – Initialisation

Technology moves fast, only 18 months ago, the 1GB SD card was very easy and affordable to purchase. Today, they’re like hen’s teeth, where the 2GB SD card has taken over this position in the market, of course a doubling of which “Mr Moore” would be happy with.

Thus, It’s only natural to project that, in 12 – 18 months time, the 2GB SD card will be difficult to source, leaving the consumer only able to purchase the 4Gb SD Card and above with any ease.

This is all good for the consumer of course, as they are getting more bang for their buck. But, for the designer it raises it’s own set of unique technical hurdles to cross.

As technology moves on, as do the specifications which devices operate under. Moving from a 1GB legacy SD card to either a more modern 1GB Card (If you can source one!) or a larger device, requires that firmware support a different SD Card Specification be written.

In this first blog, I will deal with the differences in Initialisation of SD cards under the different Specification revisions.

The v1.01 SD Card specification was written in 2001, the v1.1 spec was introduced in 2006, with the v2 spec was introduced later that year. Following the introduction of the SDHC and SDXC formats in 2010 came v3 of the specification.

With these changes, come semantic differences in the operation of the devices.

SD Spec V1 Initialisation:

Below is the initialisation flowchart for SD Cards conforming to V1 of the SD Card Specification; (Click to enlarge)

It can be seen that, from an Idle State the Host sends the CMD0 Command – “Go Idle” while holding the Chip Select pin Low. This command resets all connected SD cards to their Idle state, effectively it is a Reset command. Holding the CS pin Low forces the card into SPI mode, which is how this design is operated.

As a point of note, almost all commands sent to the SD card will be responded to with the R1 response. This response contains a set of bytes which indicate the devices status; Busy, Idle, Error, Card Locked, CRC error and a host of other status Bits.

Following CMD0, the SD card will be in it’s Idle state, from here the host may read the Operating conditions Register, or OCR, using CMD58. The SD card responds to this command with the contents of the OCR, allowing the host to ascertain such parameters as the cards operating voltage range etc. This allows the host to reject incompatible cards from the outset.

The next command can be either CMD1 or ACMD41, in my original design, for simplicity’s sake, CMD1 was chosen here.

CMD1 – “Send Op Cond”, puts the SD card into it’s Initialisation State. The SD card will respond with the standard R1 response, which contains the devices' status. The host must wait for the device to signal that it is Idle, that is the R1 response is 0x00.

At this point the device is ready to use normally.

SD Spec V2 Initialisation:

We now move onto v2 of the SD Specification, and the initialisation has changed to the following; (Click to enlarge)

We can now see that the Initialisation sequence has become somewhat more involved.

With the introduction of the V2 SD Card Specification, the Host now needs to determine which specification the connected device conforms to. This is accomplished with the addition of the CMD8 Command – “Send Interface Condition”. The host must include this along with this command a parameter which defines the voltage range it allows.

Upon reception of this command, the device will send the R7 response. This response contains the devices accepted operating voltage range.

The host at this point waits for the the R7 response, meaning the device is both V2 Spec compliant and also can operate at the given voltage range. If either fail, it can then peruse alternative options for communicating with the device, or rejecting the device completely.

If the R7 response is correctly received, then the Host must now determine whether the device is a standard format SD card or the newer and larger SDHC format.

As the devices have grown, in order to maintain backward compatibility, it has become necessary for the host system to determine which “size” of device is connected.

This process is accomplished by the host, via the use of the “Send Op Cond” (ACMD41) command. By supplying the High Capacity Support (HCS) bit, the Host can inform the connected SD card as to whether the host itself can support the High Capacity Card format.

As a note, the ACMD41 command is actually a combination of two separate commands, the “Application Command” (CMD55), and the “Send Op Cond Command” (ACMD41). CMD55 instructs the SD card that the next command is an Application Specific command, thus ACMD41 must be proceeded with CMD55.

In order initialise v2 onwards compliant devices, the above process is carried out. To simplify matters, the process can be used for v2 compliant, standard SD cards as well as SDHC, where the CCS bit in the response to ACMD41 is used to determine which type of card is connected.

Further, the specification requires that the sequence CMD55 + ACMD41  must be repeated for at least 1 second at power up in order to initialise the SD card correctly.

I must note however, that I have found by trial and error that, in order to properly initialise the SD Card and determine it’s Card Capacity, it is necessary to add a CMD55 – “Read OCR” command to the ACMD41 sequence. I’m not sure at this time why this is, but once I’ll know I shall update this blog to reflect my new knowledge!

Once this process is complete the SD Card is then ready for use.

SD Spec V3 Initialisation:

The v3 SD Specification shows the following for the Initialisation Sequence; (Click to enlarge)

I have included the above sequence for completeness however, it is almost the same as the V2 Initialisation sequence. It is however clear to see the progression of complexity, along with the increase in technical specs of the devices has had an impact upon how these devices are initialised.

---

In my next blog  I will be dealing with the differences in Data Access mode commands, and it’s effects upon the host firmware.

The FAT16 File System (with SD cards)

I have been working on a  project where the client requires their device to have it’s firmware updated from a Secure Digital (SD) Card.

The standard way to store and access data on SD cards is to use the MS FAT File System.

There are several libraries available dotted around the Internet which allow developers to access SD using FAT. However, if you are on a tight program space budget, such as in the Boot-Loader of an embedded Microcontroller, then you may need to program the FAT File System access manually.

This process is reasonably difficult to accomplish in raw code as there are quite a few steps to perform before you gain access to the actual file you are after.

I’ll outline here the basic features of FAT16, which for the purposes of this example, will be the simplest to begin with.

---

The FAT16 Layout

The layout of the MS FAT16 File System can be seen below;

The Master Boot Record (MBR):

Offset	0x0000
Size	512 Bytes

 

 

The Master Boot Record is the first location that the Host File System parses in order to navigate the File Structure of the SD Card. It is always located at Sector zero and not only sets out the addressing parameters of the File System but also contains operating code run by the host at startup.

Address (Offset)	Item	Length	Notes
0x0000	Executable Code	446 Bytes	Executed by the Host system at startup
0x01BE (00h)	Partition 1: State (Partition 1 Entry Begin)	1 Byte	Beginning of the Partition 1 Entry. 16 Bytes Total. The State determines if the Partition is Active (80h) or Inactive (00h)
0x01BF (01h)	Partition 1: Start Head	1 Byte
0x01C0 (02h)	Partition 1: Start Sector / Cylinder	2 Bytes	Defines the Start Sector and Cylinder of Partition 1
0x01C2 (04h)	Partition 1: Partition Type	1 Byte	Defines the Type of Partition; 01h = 12bit FAT 04h = 16bit FAT (<32Mb) 05h = Ex MSDOS 06h = 16bit FAT (>32Mb) 0Bh = 32bit FAT (<2Gb)
0x01C3 (05h)	Partition 1: End Head	1 Byte
0x01C4 (06h)	Partition 1: End Sector / Cylinder	2 Bytes	Defines the End Sector and Cylinder of Partition 1
0x01C6 (08h)	Partition 1: Start Sector	4 Bytes	Contains the Start Sector Address of Partition 1. Defined as the number of Sectors between the MBR and the first Sector of the Partition.
0x01CA (0Ch)	Partition 1: Partition Length (Partition 1 Entry End)	4 Bytes	End of the Partition 1 Entry. 16 Bytes Total. Defines the number of Sectors in the Partition
0x01CE	Partition 2 Entry	16 Bytes	Defined as above
0x01DE	Partition 3 Entry	16 Bytes	Defined as above
0x01EE	Partition 4 Entry	16 Bytes	Defined as above
0x01FE	Executable Marker	2 Bytes	Set as 0x55AA

 

The Boot Sector Entry (BSE):

Offset	1st sector of Partition
Size	512 Bytes
Address Location Calculation	Partition Start Sector (From MBR) * Bytes / Sector

The Boot Sector Entry contains all the pertinent information regarding the Partitions’ structure. The basic layout is shown below;

Offset	Item	Length	Notes
00h	Jump Code	3 Bytes	Contains executable Machine code to allow the Host System to jump past the BSE
03h	OEM ID	8 Bytes	The system name which formatted the device
0Bh	Bytes Per Sector	2 Bytes	Normally 0x2000 (512 )
0Dh	Sectors Per Cluster	1 Byte
0Eh	No of Reserved Sectors	2 Bytes
10h	No of FATs	1 Byte	The Number of FAT Entries
11h	Max Root Directory Entries	2 Bytes	For SD Cards this is usually 0x2000 (512)
13h	Total Sectors in Partition	2 Bytes	Only set if the device is < 32Mb. Will likely be unset 0x0000
15h	Media Type	1 Byte	0xF8 = Hard Drive
16h	Sectors Per FAT	2 Bytes	Used for reaching the Root Directory
18h	Sectors Per Track	2 Bytes
1Ah	No of Heads	2 Bytes
1Ch	No of Hidden Sectors	4 Bytes	Number of hidden sectors between the start of the disk and the BSE
20h	No of Sectors in a Partition	4 Bytes	Total number of Sectors in the Partition
24h	Logical Drive No	1 Byte	0x80 for Hard Drives
25h	Head No	1 Byte	Usually 0x00
26h	Extended Boot Signature	1 Byte	Always 0x29 denoting that Serial No, Label and Type fields are valid
27h	Serial No	4 Bytes	Serial No of the Device
2Bh	Partition Name	11 Bytes
36h	FAT Type	8 Bytes
3Eh	Executable Boot Code	448 Bytes	Allows the host to find the first file which boots the system
0x01FE	Executable Signature	2 Bytes	End of BSE

FAT Entries (FAT):

Offset	FAT1 = 1st sector after Reserved Sectors
Size	Sectors Per FAT (From BSE)
Address Location Calculation	FAT1 = (Partition Start Sector(From MBR) + Reserved Sectors(From BSE)) * Bytes / Sector

The FAT Entries lay out how the Partitions’ identically sized clusters are laid out. There are normally two FAT Entries in order to help prevent FAT corruption problems.

The FAT is located in the first sector after the Reserved Sectors. All sectors between Partition Start Sector and the FAT, which includes the Boot Sector Entry, are contained within the Reserved Sectors Allocation.

So, to find the first FAT we navigate from the beginning of the Device, adding together the Partition Start Sector and the Number of bytes contained in the Reserved Sectors section.

Root Directory Entry (RDE):

Offset	After last FAT
Size	No of Root Entries(From BSE) * 32
Address Location Calculation	RDE = FAT1 Start Address + ((No of FATS(From BSE) * Sectors Per FAT(From BSE)) * Bytes Per Sector)

The Root Directory contains the Main Directory and File Structure for the entire Partition

The Root directory is almost identical to a standard File System Directory with only a few differences, namely; The first byte is the first character of the Partition Name. The Create Date and Time are set to 0x00.

The Root directory appears after the last FAT, and so can be found by adding the Start Address of the first FAT to the number of Bytes occupied by all FAT’s, which is invariably 2 for SD Cards. Thus the formula can be simplifed to;

RDE = FAT1 Start Address +
      ((2 * Sectors Per FAT) * 512)

Each entry in the Root Directory consumes a total of 32 Bytes as shown below;

Offset	Item	Length	Notes
00h	DOS Filename	8 Bytes	For all other Directories the first byte has special values.
08h	DOS File Extension	3 Bytes
0Bh	File Attributes	1 Byte	8 bits to denote various attributes
0Ch	NT Case Info	1 Byte	Used for Windows NT Casing Info
0Dh	Create Time (ms)	1 Byte	10ms Units
0Eh	Create Time (Hrs/Mins/Secs)	2 Bytes
10h	Create Date	2 Bytes
12h	Last Access Date	2 Bytes
14h	File / Folder Start Cluster (High)	2 Bytes	Only used in FAT32 Systems
16h	Last Modified Time	2 Bytes
18h	Last Modified Date	2 Bytes
1Ah	File / Folder Start Cluster (Low)	2 Bytes
1Ch	File Size (Bytes)	4 Bytes	Folders will have a File Size of 0x0000

Sub Directory / Files:

Offset	After Root Directory
Size	N/a
Address Location Calculation	Root Folder Address + (Max Root Directory Entries(From BSE) * 32) + ((( File / Folder Start Cluster(From RDE) – 2) * Sectors Per Cluster(From BSE) ) * Bytes Per Sector(From BSE) ) Normally For SD Cards using FAT16; Max Root Directory Entries = 512 Bytes Per Sector = 512

A Sub Directory takes on almost exactly the same format as the root Directory above.

All data stored after the Root Directory is located in the third Data Cluster, thus in order to find the location of all data we must subtract these two clusters from the Start Cluster given in the File / Folder Start Cluster Field.

The first data is found using the formula above, however for SD Cards using FAT16, the maximum Root Directory Entries and Bytes Per Sector are invariably 512. Thus the formula can be simplified to the following;

First Data = Root Folder Address + 16384 +
             (((File/Folder St Cluster – 2) *
             Sectors Per Cluster) * 512)

A Worked Example:

Card Type	Sandisk 2Gb SD
MBR Length	512
Partition 1 Start Sector	129
Bytes Per Sector	512
Sectors Per Cluster	64
Reserved Sectors	2
Number of FAT’s	2
Sectors Per FAT	239
Hidden Sectors	129
BSE Length	512
Number of Root Entries	512

The table above gives all the information taken from the MBR and BSE of the Card.

Calculating the BSE Address:

BSE = Partition Start Sector *
         Bytes Per Sector

BSE = 129 * 512

BSE = 66048 = 0x10200

Calculating the FAT1 Address:

FAT1 = (Partition Start Sector +
       Reserved Sectors) * Bytes Per Sector)

FAT1 = (129 + 2) * 512

FAT1 = 131 * 512

FAT1 = 67072 = 0x10600

Calculating the Root Directory Address:

RDE = FAT1 Start Address +
      ((No of FATS * Sectors Per FAT) *
      Bytes Per Sector)

RDE = 67072 +
      ((2 * 239) *
      512)

RDE = 67072 + (478 * 512)

RDE = 67072 + 244736

RDE = 311808 = 0x4C200

Calculating the First File Address:

For a file or folder which has a starting cluster value of 0x04, then the following will locate the start address;

File = Root Folder Address +
      (Max Root Entries * 32) +
      (((File/Folder St Cluster – 2) *
      Sectors Per Cluster) *
      Bytes Per sector)

File = 311808 +
       (512 * 32) +
       (((4 – 2) *
       64) *
       512)

File = 311808 +
       16384 +
       ((2 * 64) * 512)

File = 311808 + 16384 + (128 * 512)

File = 311808 + 16384 + 65536

File = 393728 = 0x60200

SQL Server - Formatting Numbers as Strings with Leading Zero’s

Sometimes it’s necessary to format numbers as Strings with leading zero’s.

One such example is when we sort a list in Excel, Excel will sort the numbers as 0,1,10,2,20,3,30,…..

So, to get round this, we simply append a leading 0 to the number.

This is achieved in T-SQL by using the RIGHT instruction with following syntax.

RIGHT('00'+ CONVERT(VARCHAR,YourNumber),2)

The first Parameter - 00, is the default format for your formatted number, and should contain the number of zero’s corresponding to the total length of your formatted string.

The second parameter -‘YourNumber’, is the name of the field you wish to convert.

The third parameter - 2, is the total length of your formatted field.

Remaining VB.net Projects for WF Screencasts

In January I blogged about the WF screencast series by DevelopMentor instructor, freelance developer and MVP Maurice de Beijer.

All the VB.Net Projects for this series are now available on my website.

http://www.pjgcreations.co.uk/public/WFScreenCasts/

Have fun!

Retrieving data from a Stored Procedure (with parameters) in Microsoft Excel 2007 / 2010

I have a method here which allows you to add Parameters to the Stored Procedure from Excel.

There are quite a few steps, but I’ve broken it down quite alot.

The process is actually really easy once you know how... as with anything.

1. Open Excel
2. Select the Data Tab (If you’re using Excel 2007 onwards!)
3. Select the “From Other Sources” drop down box
4. Select “From SQL Server”
5. It will open up the following box
   
   
   
6. Enter “.\SQLExpress” (That’s Dot Backslash SQLExpress and it’s without the quotes) in the “Server name:” box
7. Depending on your Server Configuration, either leave the Log on credentials as “Use Windows Authentication”, or select the “Use the following User Name and Password” radio button and enter your SQL Server credentials
8. Hit “Next >”
9. You should then see the following screen:
   
   
   
10. In the “Select the database that contains the data you want:” Dropdown, select the database you wish to use.
11. In the list at the bottom, select a table, it doesn’t matter which you choose.
12. Press “Next >”
13. You should then be presented with the following screen:
    
    
    
14. In the “Filename” Textbox, set the name you would like to save the connection as, remembering to leave the “.odc” on the end.
15. If you want the Password to be stored with this connection, then check the “Save password in file” Checkbox, say “Yes” to the warning box.
16. In the “Description” enter something meaningful for this connection
17. In the “Friendly Name:” Textbox, set the name you would like to appear in the Recent Connections dialog. There’s no need for a “.odc” extension here.
18. Hit “Finish”
19. You should then be shown the following screen:
    
    
    
20. Click “Properties...”
21. The “Connection Properties” window will be shown:
    
    
    
22. Click the “Definition” tab, which will show:
    
    
    
23. If required click the “Save password” Checkbox, and press “Yes” to the warning box
24. Change the “Command Type” Dropdown to be “SQL”
25. Set the “Command text to “exec ” + the name of your Stored Procedure. If you stored procedure requires parameters then simply tack them on the end seperated by commas.
    
    e.g. exec GetUsersByAreaandName Eastern, Smith
    
    If you wish to pass in a Null, then simply type “NULL” as a parameter.
    
    e.g. exec GetUsersByAreaandName Eastern, NULL
    
26. Press “OK”, and Press “Yes” to the warning box
27. You should be shown the Import Data screen again, Press “OK”

If everything went according to plan, you should have all your data on screen!

Just started looking at Windows Workflow Foundation (WF) 4

I’ve just started looking at the Windows Workflow Foundation 4. Now in it’s fourth Generation, the Windows Workflow Foundation, or WF for short, is a completely different way to go about Programming.

WF allows a programmer to create applications using a graphical approach, such as Flowcharts, Sequence Diagrams and State Machines.

Complex programs can be created direct on a xaml designer workspace with very little in the way of lines of code.

I’ve been following a great series of screencasts by DevelopMentor instructor, freelance developer and MVP Maurice de Beijer.

In these screencasts, Maurice takes you through from beginning your first “Hello World” (or “Hello workflow” maybe?), all the way through to creating custom activity and error handling.

The Screencast series can be found here;

http://msdn.microsoft.com/en-us/netframework/dd733248

One thing which is missing from the series are the code samples however, so I’ve uploaded the code for first five Screencasts to my website in case anyone needs them. They’re available here…

http://www.pjgcreations.co.uk/public/WFScreenCasts/

Note however that, as a I am a VB.net programmer, these Code Samples are all in VB.net, however Maurice has chosen to use C#.

This isn’t a major problem as there’s not a massive amount of actual coding involved, and most of the code is very similar anyhow.

But, if you do need the C# version, I highly recommend you use the following free website to convert VB.net code to C#;

http://www.developerfusion.com/tools/convert/csharp-to-vb/

.EndEdit causes row movement of a sorted BindingSource

When applying a sort to a Database BindingSource, executing the BindingSource.EndEdit method causes the currently selected row to change.

This has the effect of reloading any bound controls when tee edited row is saved, which of course can have unwanted effects if you need to operating on data related to the currently selected row after the EndEdit has been performed.

This issue can also be observed to a degree by selecting a row in a bound DataGrid, then choose the sorted column. It can be seen that the physical location of the selected row doesn’t change, however the previously selected row has now moved.

There are a number of ways to deal with the EndEdit issue, one of which  I found here…

http://social.msdn.microsoft.com/Forums/en/winformsdatacontrols/thread/0878567a-ccb1-441b-a51d-3e014372e61b

Where we check the ListChanged event and the e.ListChangeType;

Private Sub bsBindingSource_ListChanged(ByVal sender As Object, ByVal e _

    As System.ComponentModel.ListChangedEventArgs) Handles _

    bsBindingSource.ListChanged

    If e.ListChangedType = _

        System.ComponentModel.ListChangedType.ItemMoved Then

        bsBindingSource.Position = e.NewIndex

    End If

End Sub

Another method is to store the current state of the form, i.e. editing, adding, duplicating, normal etc in an enum. then use the following code;

Private Sub bsBindingSource_ListChanged(ByVal sender As Object, ByVal e _

    As System.ComponentModel.ListChangedEventArgs) Handles _

    bsBindingSource.ListChanged

    If sttCurrentFormState = Normal Then

        bsBindingSource.Position = e.NewIndex

    End If

End Sub