C# / Sqlite simple way to store query result as variable

Question

I am semi-new to C# but in particular using Sqlite within C#, currently I have a SQlite DB setup fine in terms of it connects with the application well I am running windows form application and I have bound a table within the database to a datagrid view.

This is all fine I have a function setup to run queries where i pass the SQL statement as a string to the function and it runs it as a query.

I was wandering how I do I get a result back from the query I know obviosuly it will be somthing like

private string QueryResult(string query){
    connect
    run query
    read query 
    return result
}

All th examples I have seen use Sqlreader but I can't seem to get it work, I am really used to using PHP with SQL and that seems so much simpler than using it in C# can someone explain or point out somewhere I might be able to find a tutuorial or function that you can run any query in by passing it as a string and getting the result returned pretty simply? The results I need wont be arrays or huge things I am only looking to return 1 word strings or numbers at a time so I don't need anything complicated.

Please help me out I spent about 4 hours reading about this stuff last night and didn't seem to get anywhere.

Answer 1

Try this, maybe it will help you:

public string QueryResult(string query)
{
    string result = "";
    SQLiteConnection sqlite = new SQLiteConnection("Data Source=/path/to/file.db");
    try
    {
        sqlite.Open();  //Initiate connection to the db
        SQLiteCommand cmd = sqlite.CreateCommand();
        cmd.CommandText = query;  //set the passed query
        result = cmd.ExecuteScalar().ToString();
    }
    finally
    {
        sqlite.Close();
    }
    return result;
}

Answer 2

Heres a method that I have Used....

First off, build a class to represent a Table in your DataBase :-

public class Contact
    {
        public int ContactID { get; set; }
        public string Surname { get; set; }
        public string Forename { get; set; }
        public string MobileNumber { get; set; }
        public string EmailAddress { get; set; }
        public string Information { get; set; }   
    }

Then I load this Data into an IEnumerable List :-

public List<Contact> GetContacts()
        {
            DataTable dt = new DataTable();

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Contacts]", Connection);
            Adapter.SelectCommand = cmd;

            Connection.Open();
            Adapter.SelectCommand.ExecuteNonQuery();
            Adapter.Fill(dt);
            Connection.Close();

            var Contacts = (from row in dt.AsEnumerable()

                            select new Contact
                            {
                                ContactID = row.Field<int>("ContactID"),
                                Surname = row.Field<string>("Surname"),
                                Forename = row.Field<string>("Forename"),
                                MobileNumber = row.Field<string>("MobileNumber"),
                                EmailAddress = row.Field<string>("EmailAddress"),
                                Information = row.Field<string>("Information")

                            }).ToList();



            return Contacts;
        }

In My application I create an Instance of this Object :-

   public List<Contact> contactData;
   contactData = dc.GetContacts();

I now have the power to manipulate the data using LINQ :-

var Query = ConactData.Where(item=> item.ContactID == 10)
            .Select(item=> item.Surname).toString(); 

You can use LINQ to query your Data and store it as Lists, Strings etc etc.

Hope This Helps.