Querying MySQL with C#-CodePudding

I am new to C# and trying to figure out how to query my DB using C# while also being able to save results from the query to a list. I thought I had gotten things semi-sorted but I get the error that I cannot convert from string to my class?

I have not been able to find something that was emulating what I am attempting to do here (with the class variables) otherwise I think some of the other examples that use just what seems like C#'s standard generics for lists would probably work. I am much more accustomed to using Python and so am not finding the level of documentation I am used to finding on a particular subject. Any help is appreciated. Thank you!

using System.Collections.Generic;
namespace FilmData
{
    public class Director
    {
        public Director()
        {
            Films = new List<Film>();
        }
        
        public int DirectorID { get; set; }
        public string DirectorName { get; set;}

        public List<Film> Films { get; set; }
    }

    public class Film
    {   
        public int FilmID { get; set; }
        public string FilmName { get; set;}
        public int FilmReleaseYear { get; set; }
        public float FilmAvgRating { get; set;}
    }

    public static void Main ()
    {
        try
        {
            using (MySqlConnection conn = new MySqlConnection("server=localhost;user=user123;database=myFilms;port=port;password=somePW"))
            Console.WriteLine("Connecting to MySQL...");
                    conn.Open();

                    List<Director> director = new List<Director>();   // <-- trying to create list item

                    string sql = "SELECT * FROM Director";
                    using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                    {
                        MySqlDataReader rdr = cmd.ExecuteReader();
                        while (rdr.Read())
                        {
                            var AddToList = rdr[0]   ",   "   rdr[1];
                            director.Add(AddToList);  // <-- error here
                            
                            /* want to be able to write DB values to list
                            / utilizing the structure of DB class, but
                            / do not know the correct formatting to use
                            / List<Director> DirectorList = new Director();
                            / does not allow for me to use .Add() with
                            / string
                           */
                        }
                        
                    }
                    conn.Close();
                    Console.WriteLine("Done.");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }
}

CodePudding user response：

You will have to map from datareader to your .net object.

while (rdr.Read())
 {
   Director director = new Director();
   director.DirectorID = (int)reader["DirectorId"]; <-- check here what is exact column name 
   director.DirectorName = reader["DirectorName"].ToString(),

   // fill other poperties ..
 }

Or you can be more elegant and use reflection to do this conversion. Provided comment against each line to show what each line is doing.

public List<T> PopulateList(SqlDataReader reader)
{
        var results = new List<T>(); // create generic list T will be incoming type
        var properties = typeof(T).GetProperties(); // get all poperties defined in your object
    
        while (reader.Read())
        {
            var item = Activator.CreateInstance<T>(); // create instance of object
            foreach (var property in typeof(T).GetProperties()) // loop through each properties in your object
            {
                if (!reader.IsDBNull(reader.GetOrdinal(property.Name)))
                {  // if that exists in the datareader
                    Type convertTo = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                    property.SetValue(item, Convert.ChangeType(reader[property.Name], convertTo), null); // set the property from reader
                }
            }
            results.Add(item); // add to result list
        }
        return results;
}

then call it

YourClass<Director> obj = new YourClass<Director>();
var directorList = obj.PopulateList(reader);

CodePudding user response：

You asked in another comment if your requirement is unusual /if there are other ways

Your requirement is very common; "select rows from a db table and turn them into c# objects" is about as common a requirement to work with DBs as you can get. In the old days we did it how you have done: write a query, add parameters, execute it, pull data out of the reader, out the object in a list, repeat til the rows are processed, return the list. It's incredibly tedious, boring, mindless coding that makes any creative developer wish they were doing something actually interesting (which is why it trnds to get lumped on the junior devs, poor suckers)

As happens when developers are faced with a repetitive, boring task they find a way to hand it off to the computer. Navoneel's done that here; instead of copying property by property, column by column they've developed a technique to look at the property name, the column name, assign them together so it can be done in a loop - it's a neat trick.

Other devs have developed other similar ideas over the time, into well specified and fully fledged libraries that do all this and more, and they've then made them available for free. Here's a quick look at a couple:

Dapper

The technology that runs stackoverflow. It's a set of extensions on a database connection that will run a query you give and turn it into an object, list of objects, set of related objects etc

It looks like this for you:

//we make a connection
using var c = new MySqlConnection("...conn str");

//then call the Query method that installing Dapper added onto the connection
List<Director> dirs = c.Query<Director>("SELECT * FROM director");

This gets all directors and, provided the name of the column matches the c# property, works out simply

If column names don't match props, rename the columns:

//c# props are called FirstName, LastName, db are Name, Surname
var dirs = c.Query<Director>("SELECT Name as FirstName, Surname as LastName FROM director");

You can use parameters in your query:

var dirs = c.Query<Director>("SELECT * FROM director WHERE surname like @ln", new { on = "Smith%");

You can get just one:

var dir = c.QueryFirst<Director>("SELECT * FROM director WHERE surname like @ln LIMIT 1", new { on = "Smith%");

It supports first, single, async, executing updates, automatic expansion of arrays to IN lists... so much stuff it needs entire websites such as www.dapper-tutorial.com to go through. Take a look...

Entity Framework

Perhaps the next step up from Dapper; a device you just point at your DB and say "reverse engineer this" and it scans the db and turns every table and relationship into a set of c# objects with columns going to properties, and the objects all link to each other. You then query the db just by treating it as if it were a big graph of these related objects - you don't write sql yourself.

Examples:

//all directors
context.Directors.ToList();

//directors names starting smith
context.Directors.Where(d => d.LastName.StartsWith("Smith"));

//directors directing more than 3 companies
context.Directors.Where(d => d.Companies.Count() > 3);

//Microsoft, including all director details 
context.Comapnies.Include(c => c.Directors).First(c => c.Name == "Microsoft");