CODEWITHSUNDEEP
c#sqliteblob
user1073472
user1073472

Reputation: 139

Alter a BLOB field in Database

I've got a BLOB column in a sqlite databasefile an I want to alter this field to give it more information.

The BLOB contains this text:

"{\"Number\":\"35\",\"ChannelType\":\"TV\",\"ServiceName\":\"DVBLink\",\"ImageInfos\":[],\"IsInMixedFolder\":false,\"Name\":\"13th Street HD\",\"Id\":\"466ab1fb9be4f77e65ad2b229a15c326\",\"DateCreated\":\"2015-09-14T12:55:51.4081476Z\",\"DateModified\":\"0001-01-01T00:00:00.0000000Z\",\"DateLastSaved\":\"2015-10-17T13:34:37.4214116Z\",\"LockedFields\":[],\"ParentId\":\"00000000000000000000000000000000\",\"Studios\":[],\"Genres\":[],\"ProviderIds\":{\"ProviderExternalId\":\"13250000\"}}"

Here the code to get this information

        private void button1_Click(object sender, EventArgs e)
    {
        try {
            SQLiteConnection con = new SQLiteConnection("Data Source=D:\\2work\\VisualStudio\\Projects\\MapTVIcons\\library.db");

            con.Open();

            SQLiteCommand cmd = new SQLiteCommand();

            cmd.Connection = con;
            cmd.CommandText = "SELECT guid,data FROM TypedBaseItems where type = 'MediaBrowser.Controller.LiveTv.LiveTvChannel'";
            //cmd.CommandText = "SELECT * FROM TypedBaseItems";

            DataSet ds = new DataSet();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            ds.Tables.Add("TypedBaseItems");
            da.Fill(ds);
            //dataGridView1.DataSource = ds.Tables[1];
           foreach (DataRow dr in ds.Tables[1].Rows)
            {


                byte[] array = (byte[])dr["data"];
                var str = System.Text.Encoding.Default.GetString(array);
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }
}

What I want to do is readout the Id value (inside the blob Id\":\"466ab1fb9be4f77e65ad2b229a15c326) and add a path to the "ImageInfos" and put the new BLOB back to the database file.

Upvotes: 0

Views: 221

Answers (1)

Yahya
Yahya

Reputation: 3444

A not-so-short way of doing this would be to extend on what you have already tried:

  1. Read json blob in a C# object (Include Newtonsoft.Json nuget package in your project)

        var json = "JSON STRING";

    var ro = JsonConvert.DeserializeObject<RootObject>(json);

  2. Above will translate into following C# classes 

    public class ProviderIds
{
    public string ProviderExternalId { get; set; }
}


public class RootObject
{
public string Number { get; set; }

public string ChannelType { get; set; }
public string ServiceName { get; set; }
public List<object> ImageInfos { get; set; }
public bool IsInMixedFolder { get; set; }
public string Name { get; set; }
public string Id { get; set; }
public string DateCreated { get; set; }
public string DateModified { get; set; }
public string DateLastSaved { get; set; }
public List<object> LockedFields { get; set; }
public string ParentId { get; set; }
public List<object> Studios { get; set; }
public List<object> Genres { get; set; }
public ProviderIds ProviderIds { get; set; }
}

  3. And then you can simply manipulate the fields you like.

  4. And update it back to the database

Upvotes: 1

Related Questions