CODEWITHSUNDEEP
c#sqlitetextblobdapper
NotNameless
NotNameless

Reputation: 61

c# sqlite - Retrieve BLOB text from database

I have a sqlite database with a single table (currently) and 4 columns:

Name //TEXT
Quote //TEXT
ImageURL //TEXT, an online image
Entry //Text BLOB, I want to parse and store this as a List<Entry>

I've made some methods to create a text file, parse and store data and delete the file. The data is stored in this format:

$"{Index} {Time} {Date} {URL}" // "1 00:00.0 9/13/1985 www.stackoverflow.com"

...so that way, it's easier to split this string and store it in my container class, Entry. The size of these files averages 250 bytes so database memory will most likely never be an issue.

Here are the classes that wire to the column data in my table:

public abstract class BaseClass
{
public abstract string Name {get; set;}
public abstract string ImageURL {get; set;}
public List<Entry> Records {get; private set;} //I already know .Add() is exposed, this problem has been fixed however.
...
}

public class DerivedClass : BaseClass
{
public override string Name {get; set;}
public override string ImageURL {get; set;}
public string Quote {get; set;}
...
}

The solutions I've seen in the past couple days involve different languages and/or BLOB types and none of them use Dapper, Sqlite and C# together.

What I have so far returns a List<DerivedClass> containing a single element that wires to the properties in my derived class I've shown above. Everything but the BLOB, that is.

public static ClassType GetSingleRow(string primaryKey) where ClassType : BaseClass
        { 
            using (IDbConnection connection = new SQLiteConnection(LoadConnectionString()))
            {                
                string sql = $"SELECT * FROM {typeof(ClassType).Name} WHERE Name = \"{primaryKey}\""; //probably vulnerable to sql injection but I'll worry about it later
                var output = connection.Query<ClassType>(sql, new DynamicParameters());
                //how would get a BLOB from this ienumearable?
                return output.ToList()[0]
            }
        }

To reiterate, How would I be able to retrieve BLOB text from my sqlite database with Dapper? Would I need to retrieve it using a byte array? If so, how would I do that?

Upvotes: 0

Views: 1018

Answers (1)

NotNameless
NotNameless

Reputation: 61

Okay so right before I was going to post this question, I thought, "Why don't I just make another property called "Entry" so Dapper can do the rest of the work for me. I made it a string initially but then I changed it to byte[] after getting the InvalidCastException

public class DerivedClass : BaseClass
{
public override string Name {get; set;}
public override string ImageURL {get; set;}
public string Quote {get; set;}
public byte[] Entry {get; set;}
...
}

then in Main(), I can just do

Console.WriteLine(System.Text.Encoding.Default.GetString(DerivedClassObject.Entry));

With my methods to parse the text file, I can easily store this in my List<Entry> class.

Upvotes: 0

Related Questions