-1

if I want to select a record from table Person then I would write query like this.

using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
{  
    connection.Query<Person>("SELECT * FROM Person Where Id=?",Id);  
    return true;  
}

But what if I want to use the same four lines for multiple or dynamic tables? I want to pass dynamic table name instead of person. Like this.

void SelectDataForId(string tablename, int id)
{
    using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
    {              
        connection.Query<tablename>("SELECT * FROM "+tablename"+ Where Id=" + id);  
        return true;  
    }
}

I tried using Generic type of T as well but I am doing it wrong I guess.

void SelectDataForId<T>(T obj, string tablename, int id)
{
    using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db")))  
    {            
        var type = GetValue<T>(tablename);  
        connection.Query<type>("SELECT * FROM "+tablename"+ Where Id=" + id);  
        return true;  
    }
}  
public static T GetValue<T>(string value)
{
    return (T)Convert.ChangeType(value, typeof(T));
}

Please correct me where I am wrong.

Thanks

Ruhika
  • 67
  • 7
  • please use ***parameterised queries*** - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for ***[SQL Injection attacks](https://bobby-tables.com/)***. your original query is already doing it the right way, so why downgrade? also: if you have problems in your code, it's best to _describe them_ when asking a question? "i am doing it wrong i guess" is _not_ an error description. – Franz Gleichmann Feb 23 '21 at 05:55
  • When you use a generic type or generic method (like `List` or `.Query`), you must use the compile time type name (like `List`). You can't use a variable of type `System.Type`. You can get around this by using Reflection's `MakeGenericType` (and the method equivalent). It's probably not worth the effort here – Flydog57 Feb 23 '21 at 05:58
  • @Flydog57, So I have to check for each tablename comparing with the actual Table and then call this function ? like this ? `if(tablename == "Person")using (var connection = new SQLiteConnection(System.IO.Path.Combine(folder, "Persons.db"))) { connection.Query("SELECT * FROM Person Where Id=?",Id); return true; }` – Ruhika Feb 23 '21 at 06:00
  • I'd have one using statement, one connection and a switch statement. Or, you can look how to do it using reflection. I've set up things plike this using reflection before (for example, to populate tables on the fly from JSON files) – Flydog57 Feb 23 '21 at 06:09
  • @Flydog57 could you please give me sample of using reflection? I had searched about it but didn't get it how to implement in my project. – Ruhika Feb 23 '21 at 06:22

1 Answers1

1

As per this SO post, you cannot use parameters for the table name. What we do is to pass in the class representing a record of the desired entity as generic type:

// IRecord is the base class for all our DB entity classes
List<T> SelectDataForId<T>(int id) where T : IRecord, new()
{
    string tableName = typeof(T).Name;
    List<T> list = _connection.Query<T>("SELECT * FROM " + tableName, id);
    return list;
}
Philipp
  • 61
  • 6