Solved

Serializing object into SQL database with c# csharp .net and sql server 2005

Posted on 2009-04-07
7
1,071 Views
Last Modified: 2013-12-17
I have been able to serialize and de-serialize, using a binary formatter, an object which contains other objects successfully to file.

I want to organize all of these file in a database instead of a folder. To do this, I would like to be able to serialize directly into a database in the most efficient way possible.

I think the way I should do this is to serialize the object into a VARBINARY field (SQL Server 2005 Express).

Not sure how to code this. Below I've included some code that I currently use. Thanks for your help!
// Serialize:
BinaryFormatter bf = new BinaryFormatter();
output = File.Create(selectedFileAndPath);
bf.Serialize(output, completeTestDefinition); // Could be time consuming
 
// De-serialize:
BinaryFormatter bf = new BinaryFormatter();
input = File.OpenRead(selectedFileAndPath);
CompleteTestDefinition completeTestDefinition = (CompleteTestDefinition)bf.Deserialize(input);

Open in new window

0
Comment
Question by:yaronusa
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:oobayly
ID: 24091546
Well, you could serialise each object to a memorystream, and save the buffer to the table.
However, it'd be far more prefereable to create a db structure that duplicates your object. LINQ is probably the way to go, but to be honest I've never used it (no time like the present though)
      string connectionString = "";
      SqlCommand comm = new SqlCommand("INSERT INTO tblSerialized(fldData) VALUES(@data)",
        connectionString);
      comm.Parameters.Add("data", System.Data.SqlDbType.Binary);
 
      /* Open the command's connection & prepare the statement
       * if there are lots of items to write
       */
      comm.Connection.Open();
      comm.Prepare();
 
      List<object> objectsToSerialise = new List<object>();
      BinaryFormatter formatter = new BinaryFormatter();
      foreach (object obj in objectsToSerialise) {
        // Serialize to a memorystream
        MemoryStream ms = new MemoryStream();
        formatter.Serialize(ms, obj);
 
        // Set the binary data and execute the command
        comm.Parameters["data"].Value = ms.ToArray();
        comm.ExecuteNonQuery();
      }

Open in new window

0
 
LVL 15

Expert Comment

by:oobayly
ID: 24091580
Sorry, didn't initialise the command properly
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24091597
Sorry, didn't initialise the command properly
string connectionString = "";
      SqlCommand comm = new SqlCommand("INSERT INTO tblSerialized(fldData) VALUES(@data)",
        new SqlConnection(connectionString));
      comm.Parameters.Add("data", System.Data.SqlDbType.Binary);
 
      /* Open the command's connection & prepare the statement
       * if there are lots of items to write
       */
      comm.Connection.Open();
      comm.Prepare();
 
      List<object> objectsToSerialise = new List<object>();
      BinaryFormatter formatter = new BinaryFormatter();
      foreach (object obj in objectsToSerialise) {
        // Serialize to a memorystream
        MemoryStream ms = new MemoryStream();
        formatter.Serialize(ms, obj);
 
        // Set the binary data and execute the command
        comm.Parameters["data"].Value = ms.ToArray();
        comm.ExecuteNonQuery();
      }

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:yaronusa
ID: 24092319
OK, it seems to work as far as serializing, but I won't know until I de-serialize :)

what is the opposite of: sqlCmd.Parameters["@data"].Value = ms.ToArray();

In other words, once I have the binary data in the database, and I pull it out, how do I put it back in the object?

can you show me a couple of lines of code regarding that?

I really appreciate you helping me...
0
 
LVL 15

Accepted Solution

by:
oobayly earned 500 total points
ID: 24092379
This should do the trick, it select all the items from the database, and deserializes each one into the generic list.
      List<object> objectsToBeDeserialized = new List<object>();
      BinaryFormatter formatter = new BinaryFormatter();
      SqlCommand comm = new SqlCommand("SELECT fldData FROM tblSerialized", new SqlConnection(connectionString));
      comm.Connection.Open();
      using (SqlDataReader reader = comm.ExecuteReader()) {
        while (reader.Read()) {
          // Deserialize from a memorystream created from the binary data
          objectsToBeDeserialized.Add(formatter.Deserialize(
            new MemoryStream((byte[])reader["fldData"])));
        }
      }
      comm.Connection.Dispose();
      comm.Dispose();

Open in new window

0
 

Author Comment

by:yaronusa
ID: 24098281
At first I couldn't get it to work because of some database issues on my end... but finally, it did work... I am amazed.

And I know that for you it probably it isn't that amazing, but on my end it is, because I wanted to make this work very badly.

Best of all, I learned a lot from your code examples.

Thank you!
0
 

Author Closing Comment

by:yaronusa
ID: 31568088
At first I couldn't get it to work because of some database issues on my end... but finally, it did work... I am amazed.

And I know that for you it probably it isn't that amazing, but on my end it is, because I wanted to make this work very badly.

Best of all, I learned a lot from your code examples.

Thank you!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now