CODEWITHSUNDEEP
c#entity-framework
eusataf
eusataf

Reputation: 897

How to make the method fill the list with values from different tables and fields in one list?

I want to make a method that will populate the lst_List list with rows from various tables and fields.
Preliminary version of the code: 

cntDB = new ContextDB();
 List<List<string>> lst_List = new List<List<string>>();
 public void Method_2(string nameTable, string nameField)
        {
            var phones = cntDB.Database.ExecuteSqlCommand("SELECT " + nameField + " FROM " + nameTable); // +-
            lst_List.AddRange(phones.ToList());
        }

 

How to make the method fill the list with values from different tables and fields in one list?

Upvotes: 0

Views: 251

Answers (3)

strickt01
strickt01

Reputation: 4048

ExecuteSqlCommand returns an int not the result set and is used primarily for non-SELECT commands (e.g. INSERT, UPDATE, DELETE). Use SqlQuery instead:

cntDB = new ContextDB();
 List<string> lst_List = new List<string>();
 public void Method_2(string nameTable, string nameField)
        {
            var lst_List = cntDB.Database.SqlQuery<string>("SELECT " + nameField + " FROM " + nameTable).ToList();
        }

You don't get a List<List<string>> this way but I can't see how you will populate that type from a single flat query as per your SQL statement?

Upvotes: 2

GoldenAge
GoldenAge

Reputation: 3068

I think it would be easier to use LINQ:

var list = new List<List<string>>();
var FieldName1Rows = cntDB.TableName1.Select(x => x.FieldName1.ToString()).ToList();
list.Add(FieldName1Rows);
var FieldName2Rows = cntDB.TableName2.Select(x => x.FieldName2.ToString()).ToList();
list.Add(FieldName2Rows);

Upvotes: -1

michal.materowski
michal.materowski

Reputation: 447

If I understood correctly, You would like to populate list with different entries depending on given name and table.

Given on method with which You're trying to do that, I would make a class:

    private class DbTable
    {
        public string Name { get; set; }
        public List<string> Fields;
    }

Now You could modify Your method to take List of DbTable objects. 

    public void Method_2(List<DbTable> tables)
    {
        foreach (var table in tables)
        {
            foreach (var field in table.Fields)
            {
                var phones = cntDB.Database.ExecuteSqlCommand("SELECT " + field + " FROM " + table.Name); // +-
                lst_List.AddRange(phones.ToList());
            }
        }
    }

You need to instantiate a list that You want to pass to the method

        var tablesList = new List<DbTable>();
        var dbTable = new DbTable();
        dbTable.Name = "MyTableName";
        dbTable.Fields = new List<string>
        {
            "FirstField",
            "SecondField"
        };
        tablesList.Add(dbTable);

Upvotes: 1

Related Questions