Solved

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

Posted on 2009-04-07
7
1,074 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 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