| Recently on our eggheadcafe.com
forums somebody posted a question about how to save a large
float array into SQL Server. I answered briefly there, but after
thinking about it for a while, I realized that not only would it
be a good subject for a more comprehensive article, but also that
it was code that I'd likely need to use myself later on. So I went
ahead and built a little test Winforms app that creates an instance
of a sample class, populates the class with two public members,
a string value and a random array of floats, and then uses the BinaryFormatter
to serialize the class into a MemoryStream and save it to the "Photo"
column in the Employees table of the trusty old Northwind SQL Server
database. We then have a method that retrieves the Photo (type image)
column, deserializes it (again using the BinaryFormatter class)
and rehydrates it into a new instance of our sample class and displays
the name along with some of the float array as a sort of "proof
of concept".
I have two basic methods , "SerializeAndSave", and "RetrieveAndDeserialize",
that do the actual serialization , and both call helper methods
"SavetoDB" and RetrieveFromDB" that do the actual
saving or retrieving to / from the database.
If you have ever decompiled the System.Runtime.Serialization.Formatters.Binary
namespace (it's in MsCorLib.dll) then you know it is huge and complex.
Actually, there are over 66 separate classes, interfaces and enumeration
in it. The reason it is so complex is because it is used in a lot
of the .NET platform "under the hood" and it needs to
be able to accurately serialize virtually any .NET type into a compact
object graph in the form of a byte array - exactly what we need
to store our live instance of any class including all its methods,
properties, members ( yes, even an array of floats) into a database.
Now you might be asking, "why would I want to store a live instance
of a class into a database?". There are a number of reasons why
this might be useful.
First, you may have found that storing state data in your business
logic layer is both more convenient and faster if you can store the
instance of the class itself, rather than coming up with some method
to get the data out and then save it into the database.
Second, if you are running some sort of distributed application
you may wish for storage of class instances and their data so that
another instance of the application on another machine on your network
can retrieve the class instance from the database and continue processing
on it. Or, it could be used to resurrect the business layer "state"
on a different machine in a failover type of situation. It would
also be possible to design a sort of "Database Message Queuing"
infrastructure where for example, a Windows Service periodically
polls a database table for a specific status message that was created
by the saving of a class instance into the RDBMS and uses this as
it's "queue" message to retrieve a specified class instance
and perform some work on it. An interesting alternative to MSMQ,
possibly without some of the overhead and problems (at least that
I've seen) in using Message Queueing.
Finally, and certainly not least important, it could prove to be
a very elegant way to archive or persist business logic state data
(which, by the way, can also be encrypted prior to storage in the
database) and therefore be able to resurrect it for reporting or auditing
purposes at any time.
Without further ado, let's jump into the sample code:
private void button1_Click(object sender, System.EventArgs
e)
{
SerializeAndSave();
}
private
void button2_Click(object sender, System.EventArgs
e)
{
RetrieveAndDeserialize();
}
public void SerializeAndSave()
{
try {
// instantiate a MemoryStream and a new instance
of our class
MemoryStream ms
= new MemoryStream();
ClassToSerialize
c=new ClassToSerialize(txtName.Text);
// create a new BinaryFormatter instance
BinaryFormatter b=new BinaryFormatter();
// serialize the class into the MemoryStream
b.Serialize(ms,c);
ms.Seek(0,0);
// Show the information
textBox1.Text="Ms Length: " + ms.Length.ToString();
int res=SaveToDB(txtName.Text,ms.ToArray());
textBox1.Text+="\nDB RetVal: "+res.ToString()
+ "\n";
//Clean up
ms.Close();
}
catch(Exception
ex)
{
textBox1.Text=ex.Message;
}
}
public void RetrieveAndDeserialize()
{
MemoryStream ms2 = new MemoryStream();
byte[] buf = RetrieveFromDB(txtName.Text);
ms2.Write ( buf,0,buf.Length
);
ms2.Seek(0,0);
BinaryFormatter b=new BinaryFormatter();
ClassToSerialize c=(ClassToSerialize)b.Deserialize(ms2);
textBox1.Text+="Deserialized Name:
" +c.name + "\n";
textBox1.Text+="Portion
of Deserialized float array: \n";
for(int j =0;j<100;j++)
{
textBox1.Text+=c.fltArray[j].ToString()
+"\n";
}
ms2.Close();
}
private int SaveToDB(string imgName,
byte[] imgbin
{
SqlConnection connection
= new SqlConnection("Server=(local);DataBase=Northwind;User
Id=sa;Password=;");
SqlCommand command = new
SqlCommand( "INSERT INTO Employees (firstname,lastname,photo)
VALUES (@img_name,
@img_name, @img_data )", connection );
// (need to write something
to first and lastname columns
//
because of constraints)
SqlParameter param0 = new SqlParameter(
"@img_name", SqlDbType.VarChar,50 );
param0.Value = imgName;
command.Parameters.Add( param0
);
SqlParameter param1 = new SqlParameter(
"@img_data", SqlDbType.Image );
param1.Value = imgbin;
command.Parameters.Add( param1
);
connection.Open();
int numRowsAffected = command.ExecuteNonQuery();
connection.Close();
return numRowsAffected;
}
private byte[] RetrieveFromDB(string
lastname)
{
SqlConnection connection = new SqlConnection("Server=(local);DataBase=Northwind;
User Id=sa;Password=;"); SqlCommand
command = new SqlCommand("select top 1 Photo
from Employees
where lastname ='"+lastname
+"'", connection );
connection.Open();
SqlDataReader dr = command.ExecuteReader();
dr.Read();
byte[] imgData = (byte[])dr["Photo"];
connection.Close();
return imgData;
}
}// end class
[Serializable]
public class ClassToSerialize
{
public string name;
public float[] fltArray;
// constructor initializes name
and creates the sample array of floats
public ClassToSerialize(string
theName) {
this.name=theName;
float[] theArray= new float[1000];
Random rnd = new System.Random();
for(int i =0;i<1000;i++)
theArray[i]=(float)rnd.NextDouble()
*1000;
fltArray=theArray;
}
}
}
|
|