none
Sqlite3 - how to store a class into the db? RRS feed

  • Question

  • i saved a class into a blob column.

    string sql = "CREATE TABLE IF NOT EXISTS highscores (name varchar(20), score int,obj BLOB)";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
    
    
    
     string sqlInsert = "INSERT INTO highscores (name,score,obj) values(@name,@score,@obj)";
                 command = new SQLiteCommand(sqlInsert,m_dbConnection);
    
                //int x = Json.Serialization(testClass);
                command.Parameters.AddWithValue("@name", "bvbb");
                command.Parameters.AddWithValue("@score", 3);
                command.Parameters.AddWithValue("@obj", testClass);
                command.ExecuteNonQuery();

    my class is

     [Serializable]
        class TestClass
        {
            
            private string _string;
            private int _int;
            private long _long;
            private DateTime _datetime;
    
            public TestClass(string @string, int @int, long @long, DateTime datetime)
            {
                String = @string ?? throw new ArgumentNullException(nameof(@string));
                Int = @int;
                Long = @long;
                Datetime = datetime;
            }
    
            public string String { get => _string; set => _string = value; }
            public int Int { get => _int; set => _int = value; }
            public long Long { get => _long; set => _long = value; }
            public DateTime Datetime { get => _datetime; set => _datetime = value; }
        }
    when i try to restore the class, i get a byte array, how can i convert it back to class or there is maybe other solution how to store it?

    Thursday, May 2, 2019 5:43 AM

Answers

  • Hi 

    Thank you for posting here.

    For your question, you want to convert the byte array back to class.

    You could try the following code.

     private void Button1_Click(object sender, EventArgs e)
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
                SQLiteConnection connection = new SQLiteConnection(@"Data Source = MyDatabase.sqlite");
                connection.Open();
                string sql = "CREATE TABLE IF NOT EXISTS highscores (name varchar(20), score int,obj BLOB)";
    
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                command.ExecuteNonQuery();
    
    
                string sqlInsert = "INSERT INTO highscores (name,score,obj) values(@name,@score,@obj)";
                command = new SQLiteCommand(sqlInsert, connection);
                Stream stream = new MemoryStream();
                TestClass testClass = new TestClass("hello",2,3,Convert.ToDateTime("2019-01-01"));
                BinaryFormatter bf = new BinaryFormatter();
                bf.Serialize(stream, testClass);
                byte[] array = null;
                array = new byte[stream.Length];
                stream.Position = 0;
                stream.Read(array, 0, (int)stream.Length);
                stream.Close();
    
                //int x = Json.Serialization(testClass);
                command.Parameters.AddWithValue("@name", "bvbb");
                command.Parameters.AddWithValue("@score", 3);
                command.Parameters.AddWithValue("@obj", array);
                command.ExecuteNonQuery();
    
                SQLiteCommand sqlCom = new SQLiteCommand("Select * From highscores", connection);
    
                SQLiteDataReader sqlDataReader = sqlCom.ExecuteReader();
    
                int i = 1;
                while (sqlDataReader.Read())
                {
                    if (i == 1)
                    {
                        byte[] test = (byte[])sqlDataReader.GetValue(2);
                        MemoryStream memory = new MemoryStream(test);
                        BinaryFormatter formatter = new BinaryFormatter();
                        ArrayList list = new ArrayList();
                        while (memory.Position != memory.Length)
                        {
                            list.Add(formatter.Deserialize(memory));
                        }
                        MessageBox.Show(((TestClass)list[0]).String);
                        MessageBox.Show(((TestClass)list[0]).Int.ToString());
                        MessageBox.Show(((TestClass)list[0]).Long.ToString());
                        MessageBox.Show(((TestClass)list[0]).Datetime.ToString());
                    }
                    listBox1.Items.Add(i);
                    listBox1.Items.Add(sqlDataReader.GetValue(0));
                    listBox1.Items.Add(sqlDataReader.GetValue(1));
                    listBox1.Items.Add(sqlDataReader.GetValue(2));
                    i++;
                    
                }
               
                MessageBox.Show("success");
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by want 2 Learn Saturday, May 4, 2019 7:49 PM
    Thursday, May 2, 2019 8:06 AM
    Moderator

All replies

  • The thing you are looking for is called "Serialization".

    You don't store the class like you are doing, by passing the object as a parameter. Instead you first have to "serialize" your object, which means convert it into either a string or an array of bytes. You then save into the database the string or array of bytes. When you want to recover the object, you read back the string or array, and you "deserialize" it, which produces back the object.

    There are several serializers in the Framework. Given that you already marked your class as [Serializable], this probably means that you intend to use Runtime Serialization. There are two runtime serializers, the BinaryFormatter and the SoapFormatter. If your field in the database is of type binary (given that, as you mentioned, you have opted for saving an array of bytes and not a string), then the one to use is the BinaryFormatter.

    The documentation for the BinaryFormatter contains an example of serialization and deserialization:

    https://docs.microsoft.com/en-us/dotnet/api/system.runtime.serialization.formatters.binary.binaryformatter?redirectedfrom=MSDN&view=netframework-4.8

    The example serializes the data into a FileStream, which saves the object into a file on disk. In your case, you will want to use a MemoryStream instead of the FileStream. This will produce an array of bytes, and you can then save those bytes into your database.

    Thursday, May 2, 2019 6:23 AM
    Moderator
  • Hi 

    Thank you for posting here.

    For your question, you want to convert the byte array back to class.

    You could try the following code.

     private void Button1_Click(object sender, EventArgs e)
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
                SQLiteConnection connection = new SQLiteConnection(@"Data Source = MyDatabase.sqlite");
                connection.Open();
                string sql = "CREATE TABLE IF NOT EXISTS highscores (name varchar(20), score int,obj BLOB)";
    
                SQLiteCommand command = new SQLiteCommand(sql, connection);
                command.ExecuteNonQuery();
    
    
                string sqlInsert = "INSERT INTO highscores (name,score,obj) values(@name,@score,@obj)";
                command = new SQLiteCommand(sqlInsert, connection);
                Stream stream = new MemoryStream();
                TestClass testClass = new TestClass("hello",2,3,Convert.ToDateTime("2019-01-01"));
                BinaryFormatter bf = new BinaryFormatter();
                bf.Serialize(stream, testClass);
                byte[] array = null;
                array = new byte[stream.Length];
                stream.Position = 0;
                stream.Read(array, 0, (int)stream.Length);
                stream.Close();
    
                //int x = Json.Serialization(testClass);
                command.Parameters.AddWithValue("@name", "bvbb");
                command.Parameters.AddWithValue("@score", 3);
                command.Parameters.AddWithValue("@obj", array);
                command.ExecuteNonQuery();
    
                SQLiteCommand sqlCom = new SQLiteCommand("Select * From highscores", connection);
    
                SQLiteDataReader sqlDataReader = sqlCom.ExecuteReader();
    
                int i = 1;
                while (sqlDataReader.Read())
                {
                    if (i == 1)
                    {
                        byte[] test = (byte[])sqlDataReader.GetValue(2);
                        MemoryStream memory = new MemoryStream(test);
                        BinaryFormatter formatter = new BinaryFormatter();
                        ArrayList list = new ArrayList();
                        while (memory.Position != memory.Length)
                        {
                            list.Add(formatter.Deserialize(memory));
                        }
                        MessageBox.Show(((TestClass)list[0]).String);
                        MessageBox.Show(((TestClass)list[0]).Int.ToString());
                        MessageBox.Show(((TestClass)list[0]).Long.ToString());
                        MessageBox.Show(((TestClass)list[0]).Datetime.ToString());
                    }
                    listBox1.Items.Add(i);
                    listBox1.Items.Add(sqlDataReader.GetValue(0));
                    listBox1.Items.Add(sqlDataReader.GetValue(1));
                    listBox1.Items.Add(sqlDataReader.GetValue(2));
                    i++;
                    
                }
               
                MessageBox.Show("success");
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by want 2 Learn Saturday, May 4, 2019 7:49 PM
    Thursday, May 2, 2019 8:06 AM
    Moderator
  • You don't have anything in the class that can't be XML serialized into an XML object which is string that can be XML deserialized  back into a concrete object, which is only saving the data in the class's public properties in a XML format.

    The string XML can be saved to a data table column for primitive type string, like NvarChar(max).

    You should be using public auto properties.

    Here is an XML serialize and deserailze helper class example.

    https://gist.github.com/heiswayi/cb66748bc11efe360ad6c233fa8e603f

    Thursday, May 2, 2019 9:50 AM
  • Hello,

    Although the following does not do a byte array it can. I do one property, you should be able to work from this.

    https://social.msdn.microsoft.com/Forums/en-US/1209c499-bcc9-4117-af36-3d2dcfc4e3e0/how-to-permanently-save-variable-values-after-program-closes?forum=csharpgeneral 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, May 2, 2019 11:01 AM
    Moderator
  • thanks you all!!!

    the binary stream approach helped me

    Thursday, May 2, 2019 11:11 AM
  • Hi

    It seems that your problem has been solved. If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 3, 2019 1:10 AM
    Moderator