Creating list of Objects from Database

Question

I have 'Stolen' some code from another answer by Dummy01 in following question: How can I populate a list with values from a SQL Server database?

The problem is that I want to get a list of Objects instead of strings

How would I have to modify this code to do so? Help much appreciated.

The problem occurs in this part of the code where I don't know what type to specify.

  if (!reader.IsDBNull(0))
                        tagsList.Add(reader.GetString(0));

Code:

private void LoadList()
{
    List<string> tagsList = new List<string>();

    using (IDbConnection connection = new SqlConnection(Properties.Settings.Default.DBConnectionString))
    {
        connection.Open();    

        using (IDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT TAGCOLUMN FROM TAGSTABLE";

            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                        tagsList.Add(reader.GetString(0));
                }

                reader.Close();
            }
        }

        connection.Close();
    }
}

A string in .NET is an object. Actually all types in .NET derived form System.Object. I can't get what excalty you want. Do you mean a custom object, a class that you have declared? What? thanks — Christos
– Christos, Commented May 22, 2014 at 12:41
Objects of which type would you like to get in the collection? — st4hoo
– st4hoo, Commented May 22, 2014 at 12:43

Mike Perrenoud · Accepted Answer · 2014-05-22 12:45:20Z

4

So if you want to get a list of objects I'd submit to you that you want to use Dapper. You can get Dapper in your project via NuGet. Once you've done that you need to build a class that mimics your SELECT statement. For the sake of argument let's say your SELECT statement is:

SELECT * FROM TagsTable

and let's say TagsTable looks like this:

public class TagsTable
{
    public int Id { get; set; }
    public string Tag { get; set; }
}

(remember this is just to show you how it works)

Now your code looks like this:

private void LoadList()
{
    List<TagsTable> tagsList;

    using (IDbConnection connection =
        new SqlConnection(Properties.Settings.Default.DBConnectionString))
    {
        tagsList = connection.Query<TagsTable>(sqlStatement);
    }
}

Here the Query method is an extension method that hangs off of the IDbConnection; that's how Dapper works. Not only is the code concise, and the mapping done for you, but Dapper is fast.

answered May 22, 2014 at 12:45

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Baech Over a year ago

I'm sorry, I need some more explanation, if you would. I Can't really seem to grasp the concept. I need a list of <Movie>, but what to write in this part? I'm sorry for my inability to understand simple instructions :) tagsList = connection.Query<TagsTable>(sqlStatement); Don't understand what to switch out with own satements.

Mike Perrenoud Over a year ago

@Matt, so here TagsTable becomes Movie and the value in sqlStatement becomes a query on the Movie table. Google Dapper dot net for more instructions on the API because they can produce parameterized SQL statements with a very simple API as well (e.g. passing in a WHERE clause with filters that are parameterized).

Corwin Schmitz · Accepted Answer · 2014-05-22 12:41:30Z

0

have you tried changing in the decalration <string> to <object>, becaus it works fine by me

answered May 22, 2014 at 12:41

Corwin Schmitz

709 bronze badges

Comments

st4hoo · Accepted Answer · 2014-05-22 12:41:37Z

0

Just construct instance of the type that you want to instantiate and set it's properties according data returned by reader .

answered May 22, 2014 at 12:41

st4hoo

2,21218 silver badges25 bronze badges

Collectives™ on Stack Overflow

Creating list of Objects from Database

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related