C# SQLite Query
String sql = @"SELECT user FROM '" + channel + "' ORDER BY currency DESC LIMIT 10";
Add a comment
|
2 Answers
You have a problem with your query. The SELECT extracts only one column from the table represented by the variable channel. So your GetString(1) in the reader loop fails because there is no field at index 1 (arrays start at index zero). You need to change that GetString index.
Then there is a problem in the return value. You say that you want to return a single string but there is no return statement and you don't have any single string to return
You could write
public string top10()
{
List<string> toplist = new List<string>();
String sql = "SELECT user FROM '" + channel + "' ORDER BY currency DESC LIMIT 10";
using (cmd = new SQLiteCommand(sql, myDB))
{
using (SQLiteDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
toplist.Add(r.GetString(0));
}
}
}
return string.Join(",", toplist);
}
or change the return type of the method to
public List<string> top10()
{
.....
return toplist;
}
or to
public string[] top10()
{
.....
return toplist.ToArray();
}
I have changed your internal array to a List<string> because if you have less than 10 records your array will have empty strings instead a list will return just the rows found.
3 Comments
Nitin Aggarwal
Adding with Steve answer please use parameter with dynamic query. please follow this link for more on this. msdn.microsoft.com/en-us/library/ff648339.aspx
Steve
@NitinAggarwal unfortunately you can't use parameters for tablenames.
Jan
Thanks Steve! If you are still here, can you please show me how I can return a user rank? For example, it will order by currency and it will return the row of a certain user (aka rank). Thank you again.
You may also try this answer.
public string getTop10()
{
List<string> Toplist = new List<string>();
string connection = "YourConnectionString";
using (var con = new SQLiteConnection { ConnectionString = connection })
{
using (var command = new SQLiteCommand { Connection = con })
{
con.Open();
command.CommandText = @"SELECT user FROM @channel ORDER BY currency DESC LIMIT 10";
command.Parameters.AddWithValue("@channel", channel);
using (var r = command.ExecuteReader())
{
while(r.Read())
{
Toplist.Add(r.GetString(0));
}
}
}
}
return string.Join(",", Toplist);
}
Comments
