Retreive blob as text SQLite

[DPaul1994]

I have an SQLite database with a column type of BLOB. But, here I inserted a text, because is a very long text and data types TEXT,STRING or VARCHAR does not support such a length. How can I show the text in a richTextBox? The code so far:

using (Conexiune.getConnection())
{
string select = "SELECT * FROM legislatie WHERE capitol = '" + SimulatorManager.LegislatieCapitol + "'";
SQLiteCommand cmd = new SQLiteCommand(select, Conexiune.getConnection());
cmd.CommandType = CommandType.Text;
SQLiteDataReader rdra = cmd.ExecuteReader(CommandBehavior.CloseConnection);
try
{
while (rdra.Read())
{
data = (byte[])(rdra["date"]);
}
}
catch (InvalidOperationException ex)
{
MessageBox.Show(ex.Message);
}
MemoryStream ms = new MemoryStream(data);
richTextBox1.Text = data.ToString(); // wrong, it shows System.IO in richtextbox
}

[Richard Deeming]

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. You're calling Conexiune.getConnection() twice, throwing away the result of the first call, and not disposing of the result of the second call. You should wrap your disposable objects (the connection, command, and datareader) in using blocks. You shouldn't use SELECT * FROM - state the column(s) you want to load explicitly. There's no need to use a datareader to select a single value. ExecuteScalar will return the first column of the first row of the query results. You can also add a LIMIT 1 at the end of the query, since you're only looking at a single record. To convert a byte[] array to a string, you'll probably need to the Encoding class[^]. Which encoding you use will depend on how your data was added to the database.

const string select = "SELECT data FROM legislatie WHERE capitol = @capitol LIMIT 1";

using (SqlLiteConnection connection = Conexiune.getConnection())
using (SqlLiteCommand cmd = new SqlListCommand(select, connection))
{
cmd.Parameters.AddWithValue("@capitol", SimulatorManager.LegislatieCapitol);
cmd.CommandType = CommandType.Text;

var data = cmd.ExecuteScalar() as byte\[\];
if (data == null || data.Length == 0)
{
    richTextBox1.Text = string.Empty;
}
else
{
    // TODO: Choose the correct encoding, based on how your data is stored:
    richTextBox1.Text = System.Text.Encoding.Default.GetString(data);
}

}

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[DPaul1994]

I see, thank you, it worked :)