CODEWITHSUNDEEP
c#databasesqlite
Jan
Jan

Reputation: 11

c# sqlite query

C# SQLite Query 

String sql = @"SELECT user FROM '" + channel + "' ORDER BY currency DESC LIMIT 10";

Upvotes: 1

Views: 363

Answers (2)

Ramgy Borja
Ramgy Borja

Reputation: 2458

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);
    }

Upvotes: 0

Steve
Steve

Reputation: 216351

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.

Upvotes: 1

Related Questions