Lessons Learned 20150503: Requirements Requirements Requirements!

Unless you want to drag out your product development, always start out with requirement development and invest plenty of time in it.

Let requirements drive your design.

True, before requirement development phase, there is also the project planning phase if you follow a strict waterfall product development cycle.

As I’ve learned from past experience, the waterfall life cycle is as follows:

Project Planning >> Requirements >> Architectural/System Design >> Detailed Design >> Coding >> Verification/Validation Testing >> Maintenance

Designing and coding is sexy. No doubt. But I can’t tell you how much time I’ve personally wasted jumping into designing and coding a piece of software, only to come back to asking myself just what it is this software is supposed to do, who it is for, what the front-end UI needs to be, does it need user management, does it need these settings, etc.

How to set up SQL Server for remote connection

In a Winforms application, I set up a connectionstring in my application config file (in an installed application, it would be located under C:\Program Files (x86), called [Your Application].exe.config, or in development, under your development path\bin\Release or \Debug) that looks like this:

<connectionStrings>
<add name=”App.My.MySettings.ConnectionString” connectionString=”Data Source=.\sqlexpress;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password” providerName=”System.Data.SqlClient”/>
</connectionStrings>

This is fine and dandy as long as both the application and the database server are on the same PC.

There will be times where my application would need to be installed on more than just the “host” PC. In other words, I want to be able to install my application on other PCs and still be able to talk to the PC where my database is installed.

No problem, all you have to do is change the application configuration file’s connection string to something like this:

connectionString=”Data Source=hostPCname\sqlexpress;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password

Not so quick! You’ll most likely come across this exception when you try to run your application on the remote PC:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol)

Below I’ve summarized the steps to take to enable remote connection to the database PC.

On the database host PC, open SQL Server Configuration Manager application. Go to Start -> All Programs -> Microsoft SQ Server XXXX -> Configuration Tools -> SQL Server Configuration Manager

  1.  Configure and enable TCP/IP protocol.
    microsoft-sql-server-management-protocols
  2. Right click on TCP/IP protocol and go to Properties.SQL-TCP-IP-ConfigurationUnder IP Addresses tab, scroll to the bottom in the IPAll section, set TCP Port to a port number you would like. The default SQL Server port is 1433.Apply changes, or click OK and you will be prompted to restart the SQL service.
  3. On the left panel, click SQL Server Services.microsoft-sql-server-managementIf SQL Server (Instance Name) or SQL Server Browser services aren’t already Running, right click on it and choose Start. If they are already running, click Restart.SQL-Server-Start-ServiceIf Start is grayed out, then click on Properties and go to Service tab, change Start Mode to Automatic or Manual and you will be able to start the service.
    SQL-service-start-mode
  4. Open up port in Windows Firewall.Click on Start -> Control Panel -> Windows Firewall and click on Advanced Settings
    windows-firewall-advanced-settingsClick on Inbound Rules on the left panel and click on New Rule… on the right
    Windows-Firewall-with-Advanced-SecurityFollow the New Inbound Rule Wizard to set up a firewall port exception
    Windows-Firewall-New-Inbound-Rule-Wizard-PortPut in the port you specified when you configure your TCP/IP protocolWindows-Firewall-New-Inbound-Rule-Wizard-TCP-PortWindows-Firewall-New-Inbound-Rule-Wizard-Allow-ConnectionFor security, only open this port for private network
    Windows-Firewall-New-Inbound-Rule-Profile Give this rule any name you want
    Windows-Firewall-New-Inbound-Rule-Wizard-Name
  5. IPv6 vs. IPv4

    If all else have been configured and checked out, I learned you may still have connection problems if the client PC is using IPv6. Disable IPv6 in your network configuration and give it a spin
    Disable IPv6

Lastly, you can test your connection (recommend SQL Server Management Studio) to the remote database with this server name:

REMOTEPC,port number\SQLEXPRESS

In your application, something like this:

connectionString=”Data Source=REMOTEPC,port number\SQLEXPRESS;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password

That’s it!

Reference:
http://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx#Testing_TCP_IP_Connectivity

DataRowView RowState Unchanged {2} After Changing Item Value

Came across a very perplexing .NET behavior as I was working with a databound ListBoxControl.

I have multiselect enabled (SelectionMode = Multiextended), and the task was to loop through all list items, insert a new item in database after the last selected item in listbox, and write the new order index back to the database as we loop.

I am using the tableadapter.Update() method and testing return value of 1 for successful update. For some reason it updates fine up until selected item in the listboxcontrol (talk about weird!), and upon further digging I found the RowState isn’t changed after setting the new order index value to my data field:

vb.net rowstate unchanged

Further research suggests I’m not the only one who came upon this weird behavior. See:

http://social.msdn.microsoft.com/Forums/windows/en-US/58bb75e2-4dd5-40cb-81d9-01ad04a4b8e8/datarowstate-not-updating-when-value-being-updated-via-binded-textbox?forum=winformsdatacontrols

The solution is to call EndEdit() method on the DataRowView to force RowState to Modified so the subsequent TableAdapter.Update() call will actually perform an update to the database:

vb.net rowstate modified after calling endedit

Hex ASCII String to Byte Array

Here is a function that takes in a string of Hex and converts to byte array.

For example:

[code]Dim bytes = HexStringToBytes(“ABCD”)[/code]

bytes will now contain 2 elements, first with value of 0xAB (or 171 decimal) and second with value of 0xCD (or 205 decimal):

Here is the function:

[code] ‘convert hex string to byte array (taking 2 chars at a time)
Public Function HexStringToBytes(ByVal hex As String) As Byte()
Dim NumChars As Integer = hex.Length
Dim bytes((NumChars / 2) – 1) As Byte
Dim index As Integer
For index = 0 To NumChars – 1 Step 2
bytes(Math.Floor(index / 2)) = Convert.ToByte(hex.Substring(index, 2), 16)
Next
Return bytes
End Function[/code]

Validation:
It’s probably a good idea that you implement your own validation that the input needs to be multiples of 2, otherwise an exception will occur.

VB.NET OpenFileDialog RestoreDirectory for each dialog

Here is a code snippet (VB.NET 2005) that demonstrates how you can implement a separate RestoreDirectory for each of the OpenFileDialog’s you have.

Basically what this does is it allows you to have multiple “Browse” dialogs that each remember their own last directory used.

I have added comments to Button1_Click code only as Button2_Click code is pretty much the same:
[code]Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
‘declare a new instance of OpenFileDialog
Dim diag As New OpenFileDialog

‘set initial directory to be shown to button’s tag value.
‘this is initially blank
diag.InitialDirectory = Button1.Tag

‘sets button’s tag to directory of the file selected
If diag.ShowDialog() = Windows.Forms.DialogResult.OK Then
Button1.Tag = System.IO.Path.GetDirectoryName(diag.FileName)
End If

‘output last directory shown on a label
Label1.Text = Button1.Tag
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim diag As New OpenFileDialog
diag.InitialDirectory = Button2.Tag
If diag.ShowDialog() = Windows.Forms.DialogResult.OK Then
Button2.Tag = System.IO.Path.GetDirectoryName(diag.FileName)
End If
Label2.Text = Button2.Tag
End Sub
End Class[/code]
Now, assuming that you will be putting a button to show the openfilediaglog, and assuming you will have a button (or some kind of control) for each of the openfiledialog, we can utilize the button’s or control’s .Tag property to store the directory last shown.

You may download the demo project to see this in action:
OpenFileDialog InitialDirectory Demo