Basic SQLite help in C # needed - returning strings or int from query
I am using Microsoft Visual C # 2008 Express Edition with SqlLite. I can successfully open my database and with C # code, add records to my tables.
When it comes to retrieving data, I am having some problems and you have searched and searched the web for basic tutorial information on how to do these basic things ...
Here's my code ... (after I opened a database connection 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 do this ... I don't get any errors and because I have no output on that last line it looks like the while loop is not executing at all.
I'm new to this business ... what am I missing and is there a good web resource where I can learn these basic things? I'm pretty comfortable with SQL on my own ... just not integrated into C # like this ...
a source to share
This looks right to me. Does the property reader.HasRows
return true for your request?
A few side issues to be aware of:
- Be sure to dispose of your SQL resources by wrapping your objects with {} blocks.
- Consider using parameterized queries instead of entering the query parameter directly in the SELECT statement.
a source to share
Answering the 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();
[...]
SQLite parameters can take several forms, which you can find here .
For more information on parameterized queries, see here .
a source to share
Not to distract you from the Sqlite question, but if you are having trouble comfortably with Sqlite queries embedded in C # you can try NHibernate in combination with Fluent NHibernate . These technologies provide an excellent mechanism for accessing data in databases, including Sqlite.
NHibernate queries in Sqlite are very fast and you don't have to worry about some of the Sqlite features. If you shape your data access layer correctly with NHibernate, you can quickly scale it to a more robust database.
a source to share