C# Insert and Retrieve a model object as BLOB to/fro database

Question

Everywhere I ask this question about C#, I'm only getting answers for images and files. I want to store an object as BLOB, not an image, into the database from one asp.net application, and retrieve it from another application.

Let's say I have a model Person:

public class Person
{
    public int userID { set; get; }
    public string userName { get; set; }
    public string lastName { get; set; }
    public string firstName { get; set; }
    public string email { get; set; }

    public List<int> someAttr { get; set; }
    public List<int> otherAttr { get; set; }

    public List<SomeModel> modelAttr { get; set; }
    public List<AnotherModel> modelAttr2 { get; set; }
}

This model not only has regular datatype values, but some List(arrays) as well as other specific model type data (SomeModel and AnotherModel). For this reason I need to store this model's object into the database and retrieve it from another application, as the session variables are lost while navigation to and fro between different asp.net applications.

Now my object would be:

Person p1 = *Retrieve data from database, and store it to p1*

I'm trying to store p1 in the database, with all its values intact so that when I retrieve it from my second asp.net mvc application, I can use it like p1.userName, p1.email, and then the lists within a loop like:

for(int i=0; i<p1.someAttr.Count(); i++)
{
     *use i.someAttr[i] in some way*
}

I have found various resources to do this, but with image files. They don't match with my scenario so I'm posting this question.

http://www.c-sharpcorner.com/uploadfile/Ashush/working-with-binary-large-objects-blobs/ http://www.aspsnippets.com/Articles/Read-and-Write-BLOB-Data-to-SQL-Server-database-using-C-and-VBNet.aspx

Thank you.

You can serialize the Person object to JSON using Newtonsoft.Json library. Then store it in a table with UserID, PersonObjectJson (varbinary(max)). It's not pretty but it will work for what you're looking for. — Jon, Jul 14 '16 at 17:49

score 0 · Accepted Answer · answered Jul 14 '16 at 17:58

You can achieve this by using this PersonRepository class, which will store your objects in a SQL database using a serialized version of them in JSON.

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace StackOverflow
{
    #region Models
    public class Person
    {
        public int userID { set; get; }
        public string userName { get; set; }
        public string lastName { get; set; }
        public string firstName { get; set; }
        public string email { get; set; }
        public List<int> someAttr { get; set; }
        public List<int> otherAttr { get; set; }

        public List<SomeModel> modelAttr { get; set; }
        public List<AnotherModel> modelAttr2 { get; set; }
    }
    public class SomeModel
    {
        public int SomeProperty { get; set; }
    }
    public class AnotherModel
    {
        public string AnotherProperty { get; set; }
    }
    #endregion

    public class PersonRepository
    {
        // Before you can use this repository you need to setup a Person table to store your objects
        // CREATE TABLE Person (UserID int primary key, PersonObject text)

        private string _dbConnectionString;
        public PersonRepository(string dbConnectionString)
        {
            this._dbConnectionString = dbConnectionString;
        }

        public void WriteToDatabase(Person p)
        {
            using (var conn = new SqlConnection(_dbConnectionString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    // Serialize the person object to JSON to store in the database
                    var personJson = JsonConvert.SerializeObject(p);

                    command.CommandText = "INSERT INTO Person (UserID, PersonObject) values (@UserId, @PersonObject)";
                    command.Parameters.Add(new SqlParameter("@UserID", p.userID));
                    command.Parameters.Add(new SqlParameter("@PersonObject", personJson));

                    // Execute the SQL command to insert the record
                    command.ExecuteNonQuery();
                }
            }
        }

        public Person ReadFromDatabase(int userId)
        {
            using (var conn = new SqlConnection(_dbConnectionString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "SELECT PersonObject from Person where UserID = @UserID";
                    command.Parameters.AddWithValue("@UserID", userId);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            // Read out the JSON respresentation of the Person object
                            var personJson = reader.GetString(0);

                            // Deserialize it back into a Person object. Note you will have to deal with versioning issues.
                            return JsonConvert.DeserializeObject<Person>(personJson);
                        }
                        else
                            throw new ApplicationException($"No person found with user ID {userId}");
                    }
                }
            }
        }
    }
}

You're using Entity Framework and your object is disposed. If you're using Entity Framework, make sure you're not loading the Person from a using(var dbContext = new DbContext()) block, as that will dispose the ObjectContext. In my example, it works fine because the Person object is not an Entity Framework proxy object. — Jon, Jul 14 '16 at 18:31
Refer to this, you can use .Include() and use virtual properties to lazy load the properties of your Person object. http://stackoverflow.com/questions/18398356/how-to-solve-the-error-the-objectcontext-instance-has-been-disposed-and-can-no-l — Jon, Jul 14 '16 at 18:31
Nevermind I was actually using a code from another source by mistake, I'm getting this error now: In the `WriteToDatabase(person p)` function, in the line `var personJson = JsonConvert.SerializeObject(p);`, I'm getting the following error: `SerializeObject is a method but is used like a type` — Bivo Kasaju, Jul 14 '16 at 18:32
You're doing something wrong. My code works. Here is a simple test: var p = new Person(); var json = JsonConvert.SerializeObject(p); Console.WriteLine(json); — Jon, Jul 14 '16 at 18:34
Idk why I was writing `var json = new JsonConvert.SerializeObject(p)` :p — Bivo Kasaju, Jul 14 '16 at 18:35

score 0 · Answer 2 · answered Jul 14 '16 at 18:35

For short answer, you can save them as Json or Xml format, even can be serialized in a binary Json/Xml but handling CRUD operation will not be simple.

but

BLOB is a binary data types stored in database like image/audio , and currently , they are saved in MS SQL Server as a file stream.

Data like List someAttr, List modelAttr are considered as Collections and can be accessed via their Foreign Keys (and can't be considered as a Blob) In Entity Framework you can retrieve them using "Include" ,example:

var modelAttr = MyEntitiy.Person.Include(r=>r.modelAttr)

In OData you get by "Expand" keyword So, saving collections in a separate Entity can be handled easily in CRUD operation with many of ORM Frameworks, OData and Rest Services.

C# Insert and Retrieve a model object as BLOB to/fro database

2 Answers2