.net.devcity.weekly ---
If you are unable to see the message, visit http://www.devcity.net/newsletter/archive/devcity/devcity20060220.htm

Advertisement

AdvertisementAdvertisement

The newsletter is compiled by DevCity.NET NewsMasters Ged Mead and Mike McIntyre

Advertisement

Table Of Content:

Advertisement
Diary of a .NET Newbie: Yippee! I've Got a BLOB

by Ged Mead

Introduction

There was a time when I was in the military, long before computers became commonplace, when discovering that you had to deal with a BLOB wouldn't have brought you a lot of happy anticipation. It's maybe still not a lot of fun now, but happily in these days of computer-speak, BLOB has a much more benign meaning.

BLOB - or Binary Large Object - is a way of storing data in a database in binary format. Although it is generally thought of - and most popularly used for - storing images, the format isn't actually limited to this.

In a recent VBCity Topic someone had got hold of a code snippet that they wanted to tweak for their own purposes. The requirement was to be able to store images in an Access database and also to store a text string to identify the image . I think the original code had come from MSDN, but if I've inadvertently hijacked anyone's personal code in this article, my apologies; feel free to let me know and I'll be happy to acknowledge.

Saving to the Database

Here's the code tweak that fixed the original requirement (saves an image and its text description to an Access database):

Imports System.IO
Imports System.Data.OleDb

'  Various variables for accessing the database
Dim strDBName As String = "\StaffList.mdb"
    Dim strConn As String = "Provider=microsoft.jet.oledb.4.0; " _
      + " data source=" & Application.StartupPath & strDBName
    Dim Conn As New OleDbConnection(strConn)
    Dim cmd As OleDbCommand

Assuming that there is an image in the PictureBox named picImage, a button click event fires off the database saving code:

Private Sub Button1_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles Button1.Click
    '  Create a MemoryStream
    Dim MemStream As New MemoryStream
    '  Save the image to the Stream in jpg format 
    '  (other alternatives available)
    picImage.Image.Save(MemStream, Imaging.ImageFormat.Jpeg)
    '  Create Binary buffer to receive image
    Dim buffer(CInt(MemStream.Length - 1)) As Byte
    '  Reset buffer's position back to 0
    MemStream.Position = 0
    '  Read MemStream contents into buffer
    MemStream.Read(buffer, 0, CInt(MemStream.Length))

    '  The Insert Command to save to Database
    '  Parameters used for maximum versatility
    cmd = New OleDbCommand("INSERT INTO StaffData(StaffPhoto, " _
      + " StaffName) VALUES (@ThePicture, @UserName)", Conn)
    cmd.CommandType = CommandType.Text

    ' Create the parameters and assign values
    '  1.  The image
    Dim prmpic As New OleDbParameter
    With prmpic
        .ParameterName = "@ThePicture"
        .OleDbType = OleDbType.Binary  ' Required for BLOB
        .Value = buffer
    End With
    cmd.Parameters.Add(prmpic)
    '  2.  The Text
    Dim prmName As New OleDbParameter("@UserName", txtName.Text)
    cmd.Parameters.Add(prmName)

    Try
      ' We're good to go, so let's save the data and tell user
        Conn.Open()
        cmd.ExecuteNonQuery()
        MessageBox.Show("Image and Text Saved!")

    Catch ex As Exception
        MessageBox.Show("There was a problem saving to the DataBase" & _
          ControlChars.CrLf & ex.Message)
    Finally
      '  Housekeeping - dispose of objects
        If Conn.State = ConnectionState.Open Then Conn.Close()
        Conn.Dispose()
        cmd.Dispose()
        Conn = Nothing
        cmd = Nothing
    End Try
End Sub

Reading Them Back Again

Although not part of the original question, I then got quite interested in how to do the reverse - populate a PictureBox with the required image, the selection being based on the text description.

I was quite keen to do it in a way that didn't involve having all the images from the database loaded into the PC's memory at the same time. That approach seemed to be a waste of resources and poor design. So what I aimed to do was to display a list of the descriptions in a ComboBox and then populate the PictureBox with the relevant image whenever the ComboBox's selection changed.

With a lot of help from MSDN, here's the final result: 

'  Populate the ComboBox at Form Load.
Private Sub frmReadFromDB_Load(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles MyBase.Load
    '  On Form Load, read all the Descriptions into a ComboBox
    Try
        Conn.Open()
        Dim strSQL As String = "SELECT StaffName FROM StaffData"
        cmd = New OleDbCommand(strSQL, Conn)
        '  Execute Reader
        Me.cboNames.Items.Clear()  ' Ensure empty first
        '  Enumerate through all entries and show names in cbo
        Dim rdr As OleDbDataReader = cmd.ExecuteReader
        While rdr.Read
            cboNames.Items.Add(rdr("StaffName".ToString))
        End While
        rdr.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)

    Finally
        Conn.Close()
    End Try
End Sub

And then in the Combo's Selection changed event, go and get the required image from the underlying database:

Private Sub cboNames_SelectedIndexChanged(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles cboNames.SelectedIndexChanged
    Try    ' Get the data
        Dim strSQL As String = "SELECT StaffPhoto FROM StaffData WHERE " & _
          "StaffName = '" & CType(cboNames.SelectedItem, String) & "'"

        cmd = New OleDbCommand(strSQL, Conn)

        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(cmd)
        Dim DS As New DataSet
        da.Fill(DS)

        '  Now populate the PictureBox
        If DS.Tables(0).Rows.Count > 0 Then
            Dim ByteArray() As Byte
            ByteArray = DirectCast((DS.Tables(0).Rows(0)(0)), Byte())
            Dim memstrm As New MemoryStream(ByteArray)
            Me.picImage.Image = Image.FromStream(memstrm)
        End If

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

All Done! (Almost)

Of course, it wasn't long before I wanted to add other features, such as letting the user fill the PictureBox with an image from file, saving an image directly from the hard drive file to the database, displaying thumbnails next to the descriptions, yada, yada. What's that expression? Oh yes, "creeping scope". Probably responsible for more sleep deprivation and stiff necks than any other activity known to man!

But anyway for now it achieves its modest ambitions. Picture and text description can be saved and retrieved to order.

So there it is - images of almost any size stored in a database, thanks to the availability of BLOB. Problem cured - and not a drop of penicillin in sight!

- Junior (Ged Mead aka XTab, xtab@vbcity.com)

by Fadzai Chamba

In a bid to see the end of software that treats users like peripheral devices or sub routines, I will be writing a number of articles on usability. This first one however, is aimed at error handling.

On Error, Goto Hell

The move to .NET saw a lot of developers move from the On Error Goto Label error handling mechanism to the structured Try...Catch mechanism. One of the immediate advantages of this move was that we looked smarter in front of our clients because no longer did we have unexpected errors, we had to deal with exceptions in our programs. I mean, who wants to admit to making an error in an expensive piece of software?

The main disadvantage is that this presented us with a new way of thinking and our imagination limited a lot of us to just giving the user a screen dump of what went wrong. Code such as the following is too common in the .NET world. 

Try
    'The code here can throw an exception that we will catch.
    SomethingHard()
Catch ex As Exception
    'Report the error.
    MessageBox.Show("There has been an error:" & ControlChars.CrLf & _ 
      ex.ToString) 'could also us ex.Message
Finally
    'Clean up the memory we were using...
End Try

While this gives the user an accurate report of what went wrong, the average user doesn't know what to do with this information. "What is an exception?", they may ask; worse when it's an ArgumentNullException. Chances are, they were not arguing with anyone, or they had a valid point. As commendable as it is for you as the developer to alert the user to mishaps, I don't support the idea of bothering him/her with this information. It is better to deal with the problem or to give the user more useful information, in his/her terminology; including telling him/her (I wish "shim" could be an official word) what he/she can do about it.

Try Again

I always lament the lack of a TryAgain keyword in .NET to match the Resume facility from the past. Given an instance where an application fails to find a file and gives the user an option to search for it if it had been moved, classic VB had the following advantage over the current incarnation.

On Error GoTo Handler
    'Try to open the file - it has been moved so we go to Handler
    Exit Sub

Handler:
    Select Case Err.Number
        Case 53 'file not found error number
            'Show dialog to search for new location...
            'Save new location in .ini so as not to bother user again for it
            'runs the same line of code that failed to open the file
            Resume 
        Case Else
            'runs the next executable line after the offending one
            Resume Next 
    End Select
End Sub

To do this using Try...Catch, you would have to employ some pretty ugly hacks or write lots of code to jump back to the desired location. Here is what I have seen all too often:

Try
    'Try to find file
Catch ex As IO.FileNotFoundException
    Dim sb As New System.Text.StringBuilder
    sb.Append("The file {0} has not been found. Specify a new location")
    MessageBox.Show(sb.ToString().Replace("{0}", ex.FileName))
    'After this, the user normally goes through the whole process again
End Try

At present, at least to my knowledge, there is no equivalent feature to Resume, that will literally Try Again. The user will have to specify a new location and start the whole process again. Sometimes I feel like they shouldn't have moved the file in the first place, but given a choice of putting the end user or the programmer to work, I'd gladly send the coder off to work extra for the user to have it easy.

Well, what can we do?

Unfortunately there is no easy answer for this. Neither is there a correct one. You will have to apply what works best with your situation. I will propose a simple method I use when working with databases.

The scenario we have before us is a user attempting to add a new client record to the company contacts database. The first thing we have to do is figure out what problems we could have when adding something to a database. A quick list of the most common things follows:

  • The application will fail to connect to the database
  • The application will find that there will be a duplicate id field if it proceeds
  • Some critical information is missing from the record to be saved
  • The process completed successfully

The first thing I do is create an enumeration; this will have all the possible return values for adding an element.

Namespace Enumerations
    Public Enum AddElementReturnValues
        DataBaseFailure
        DuplicateID
        IncompleteDetails
        Success
    End Enum
End Namespace

Next, my add method is written as a Function with AddElementReturnValues as the return type.

Public Shared Function AddCustomer(ByVal cust As Customer) _
      As AddElementReturnValues

    'Is it OK to add this customer...
    If cust.DateCreated <> Nothing Then _
      Return AddElementReturnValues.DuplicateID

    'Similar test code...
    If cust.City = String.Empty _
      Then Return AddElementReturnValues.IncompleteDetails

    Try
        con.Open()
        If Not CBool(con.State And ConnectionState.Open) Then
            Return AddElementReturnValues.DataBaseFailure
        End If
        'Do the deed, add the info and finish off
        Return AddElementReturnValues.Success
    Catch ex As Exception
        'for now left for you to decide what to do with the exception
        Throw 
    End Try

End Function

The code that calls the add method would test the returned value; if successful a report will do. If a connection couldn't be established, the user is told to contact the system admin for more assistance. In the case that incomplete details were supplied, the application then loops through all input controls set aside for critical information till it finds the one with missing data, sets the focus in there, and tells the user to fill in that information.

Conclusion

While this creates a lot more work for me (about twice as much error handling or prevention code than everything else), my end users don't get to see cryptic error messages, and in turn don't get to call me when I'm watching Liverpool FC beating Chelsea in the European Football Champions' League. When they do call however, there is more useful information than "the program isn't working", they can tell me exactly what's going wrong, and chances are, I can resolve this sooner and get back to my football.

I also feel that we need more than one example so I am currently working on a long series with a real world (very simplified however) application that will introduce one aspect of usability in detail per issue. Before I leave, I have to say that this is just one way, my way, of dealing with problems. I am merely suggesting a better alternative to barking at the user whenever anything goes wrong. I am open to suggestions and would welcome anyone else's method if it offers a realistic and significant advantage to the user. For the time being; let's not treat users as peripheral devices and happy coding.

- Fadz (Please use Subject "DevCity Article" for your email)

Review: SQL Delta

by Chris Manning

Let's talk about simplicity and ease of use at its finest. Let's talk about minimal headaches. Let's talk about SQL Delta.
SQL Delta is a database comparison and synchronization tool for Microsoft SQL Server that easily and accurately allows you to sync-up your database schemas.

Let's face it, one of the most tedious, repetitive, and cumbersome jobs of application development is creating that mirror image of your production data environment to test your software or software patches against so you don't affect your production environment. While there are other real world scenarios that SQL Delta can be used for, this is the real life scenario I'll be talking about in this short review.

SQL Delta provides a feature rich client interface which makes it very easy to compare two data environments visually.

Setup is a snap, utilizing the wizard interface to connect and compare. Alternatively, if you're more "old-school" and want to set everything up manually, you can easily do that as well.

Just to give you an idea of how easy this application is use, to compare the two database schemas, here are the steps involved:

First you launch the application. The first screen that appears is the connection wizard. In the wizard screen you simply supply the server address, username and password, and catalog to compare for both schemas.

Then you press the "Compare Structures" button. Depending on the physical size of the schemas selected, you'll wait anywhere from 5 seconds to a couple of minutes while the comparison is being performed.

Next, you'll be presented with the main application screen. Let me just say that when Microsoft was discussing what "GUI" meant, this is probably what they had in mind. This screen presents all the data in symbol (with associated descriptors) format of the comparison that was performed between the two schemas.

Information can be broken down easily by using the toolbar at the top of the screen. For example, if you just wanted to see the table comparison, you can simply select the toggle button for tables.

If you are just concerned with the permissions or roles, you can select that toggle button only. Or, if you want to see the entire schema comparison, you can select all the toggle buttons. Each and every major facet of the typical database schema is covered as a stand-alone information presentation.

Alternatively, if you want to see the entire schema, well, that's covered too by presenting an entire view of the database objects.

Not only can you compare the objects in the data schema, you can also compare the data contained in the table structure and sync that as well, using the same simple methods previously outlined.

The final step: Syncing it all up. Once you've selected what you want to sync and the type of sync operation, either structure or data, all you have to is press the "Action" button on the toolbar and select the appropriate action. Then press the script button and select then de-select any actions you don't want performed or vice versa.
Then press the "Run" button.
Repeat these steps for the data comparison. Presto-change-o, a completely synced up data structure.

In summary, I would recommend this product to anyone who routinely has to do any type of data synchronization. It's easy to use, easy to read and interpret, and pretty easy on the wallet for the punch it packs. If you compare the time and cost associated to do these common tasks manually or write your own procedures, not to mention the head-ache associated with this chore, SQL Delta's price tag is well worth every penny spent.

Key Features:

  • Easy to use GUI
  • Accurate and informative data presentation
  • Seamless integration and syncing for SQL Server environments.

For information on this product or to order your license please visit: SQL Delta

A 14 day trial download is available for you to test the product.

AdvertisementAdvertisement

We encourage you to pass this issue of
.net.devcity.weekly on to anyone you know with an interest in .NET technology and News You Can Compile

Manage Your Subscription Here.

You are currently subscribed as '*EMAIL*' to .net.devcity.weekly.

Click here to unsubscribe.

Thanks for reading!

Contact:
vbCity.com, LLC
4957 Lakemont Blvd SE C4 #331
Bellevue, WA 98006

DevCity.NET is hosted by FullControl.NET
Copyright vbCity.com, LLC 2003. All Rights Reserved.