CODEWITHSUNDEEP
c#sqlite
adeena
adeena

Reputation: 4167

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

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...

Upvotes: 0

Views: 584

Answers (5)

Jeff Fritz
Jeff Fritz

Reputation: 9861

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.

Upvotes: 0

VVS
VVS

Reputation: 19604

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.

Upvotes: 1

van
van

Reputation: 76992

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.

Upvotes: 0

Kevin Pullin
Kevin Pullin

Reputation: 13327

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.

Upvotes: 2

Alex Martelli
Alex Martelli

Reputation: 881735

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

Upvotes: 5

Related Questions