Putting a VB.NET class in to a MySQL BLOB

[Jared Mark]

Greetings all... brand new here. Been using MySQL with my PHP projects, and I love it... both of the web servers I have my content on use Apache and MySQL.

Sooo, I decided that MySQL would be a good way to store data for a program (a game) I'm writing. It's a persistant world game, and the purpose of the database is for crash recovery only... I won't be pulling records except during server startup, and the writing of data can be queued if necessary, as all of the actual mucking about with the LIVE data will be done in memory.

That said...

In my game, I have a "Character" class. This class has the following members:

ID as Long
Name as String
Gender as Byte
Race as Byte
CData as Class "CData"

My database table has corresponding fields:

ID (autonumbered unique indexed)
Name as TinyText
Gender as VarChar
Race as VarChar
CData as Blob

All of the dynamic data about the character is stored in that CData class... stats like strength, intelligence... you get the picture.

In VB.NET I have serialized the data... it's all gone in in to a MemoryStream type variable:

Dim ms as new MemoryStream

This is where I get stuck...

Hell, here's my code:

Imports System.Runtime.Serialization
Imports System.Runtime.Serialization.Formatters.Binary

Function SaveData(ByVal C as Character) as boolean
Dim Cmd As New Odbc.OdbcCommand
Dim TempStr As String
Dim formatter As IFormatter = New BinaryFormatter
Dim ms As MemoryStream
formatter.Serialize(ms, C.CData)
ms.Seek(0, SeekOrigin.Begin)
If OpenDB() = True Then
Try
Cmd.Connection = DBCon
TempStr = "INSERT INTO `Character` (`Name`,`Gender`,`Race`) values ('" & C.Name & "','" & C.Gender & "','" & C.Race & "')"
Cmd.CommandText = TempStr
Cmd.ExecuteNonQuery()
Catch ex As Exception
dForm.OutputConsole.Items.Add(ex.Message)
End Try
End If
End Function

As you can see I'm not really doing anything with the memorystream right now. I'm quite able to save the other information, as you can see...

The SQL ends up being:

INSERT INTO `Character` (`Name`,`Gender`,`Race`) values ('ExampleName','1','1')

What I WANT to do is put in that BLOB field... but I can't find a way to put the memorystream in to the query!

I can't convert the memorystream type in to a string type.

Do I need to move away from using SQL statements, and use a DataSet?

I realize this is more of a VB question, and not really a MySQL question, but I'm hoping to find some help here anyway... :)

Any help is appreciated... thanks.