Basic help with sqllite in C# needed - returning strings or ints from a query

Question

I'm using Microsoft Visual C# 2008 Express Edition with SqlLite. I'm successfully able to open my database and with C# code, add entries to my tables.

When it comes to retriving data, I'm having some issues and have been searching and searching the internet for basic tutorial information on how to do these basic things...

Here's my code... (after I've opened up a connection to the database which is called 'conn' here):

SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select myField1,myField2 from myTable where myField3 = '" + tempstring + "';";
cmd.CommandType = CommandType.Text;
SQLiteDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
  string tmp = reader.GetString(0);
  System.Console.WriteLine(" my output = " + tmp);
}

When I execute this... I get no errors and because I get no output on that last line, it looks like the while loop is not executing at all.

I'm a beginner to this stuff... what am I missing and is there a good web resource where I can learn these basic things? I'm pretty comfortable in SQL on it's own... just not integrated in C# like this...

Thanks!

-Adeena

Answer 1

First, remove the hurtful trailing semicolon from the line while (reader.Read());...!

Answer 2

This looks correct to me. Does the property reader.HasRows return true for your query?

A couple of side issues to be aware of are:

1. Be sure to dispose of your SQL resources by wrapping your objects in using { } blocks.
2. Consider using parameterized queries instead of injecting the query parameter directly in the SELECT statement.

Answer 3

Good one, Alex.

In addition to that and since you are beginning with sqlite (you may want to delete second L from the tag), remember that sqlite does not really guaranty data type safety on the database level.

Answer 4

Answering your question on how to write parameterized queries:

cmd.CommandText = "select myField1,myField2 from myTable where myField3 = @tempString;";
SQLiteParameter param = new SQLiteParameter("@tempString");
cmd.Parameters.Add(param);

// you can modify that value without touching the sql statement (which means you could cache the above command)
param.Value = tempstring;
SQLiteDataReader reader = cmd.ExecuteReader();
[...]

Parameters in SQLite can have several forms which you can find here.

See here for more info on parameterized queries.

Answer 5

Not to divert you from your Sqlite question, but if you are having comfort issues with Sqlite queries embedded in C#, you could try NHibernate coupled with Fluent NHibernate. These technologies provide an excellent data access mechanism into databases, including Sqlite.

NHibernate requests into Sqlite are very fast, and you won't have to worry about some of the Sqlite idiosyncrasies. If you build out your data-access layer properly with NHibernate, you should be able to up-scale to a more robust database very quickly.