none
Handle data from DB directly into classes RRS feed

  • Question

  • Currently I'm very beginner in c# and I don't understand how can I get a data from DB (let's say using data table) and transform it into a object class. 

    For example: 

    public class Car    
    {
      public string Name { get; set; }
      public string Brand { get; set; }
    }
    
    public class DataProccess
    {
      var _dataTable = new DataTable(psfTable);
      var _dataAdapter = new DataAdapter("Select * FROM cars",_conn);
    
      _dataAdapter.Fill(_dataTable);
    }

    How can I map the datatable to the class without mapping each one of the fields? What is the right way to do it? 

    Friday, January 24, 2020 3:27 PM

Answers

  • Sorry. please add as extension method to your project.

    public static T ToObject<T>(this DataRow row) where T : class, new()
            {
                T obj = new T();
    
                foreach (var prop in obj.GetType().GetProperties())
                {
                    try
                    {
                        if(prop.PropertyType.IsGenericType && prop.PropertyType.Name.Contains("Nullable"))
                        {
                            if (!string.IsNullOrEmpty(row[prop.Name].ToString())) 
                                prop.SetValue(obj, Convert.ChangeType(row[prop.Name], 
                                Nullable.GetUnderlyingType(prop.PropertyType), null));
                            //else do nothing
                        }
                        else
                            prop.SetValue(obj, Convert.ChangeType(row[prop.Name], prop.PropertyType),null);
                    }
                    catch
                    {
                        continue;
                    }
                }
                return obj;
            }

    • Marked as answer by FcabralJ Tuesday, January 28, 2020 6:40 AM
    Saturday, January 25, 2020 7:54 PM
  • I totally agree on using EF as it provides us a lot of things out of the box, but if it's not compatible with Sybase I can't use it. 

    What is strange for me is that SqlAnywhere says it provides ADONET for sybase. https://docs.telerik.com/data-access/deprecated/developers-guide/database-specifics/sql-anywhere/database-specifics-sqlanywhere-voerview

    Here is the deal, each database company e.g. Microsoft for SQL-Server provides a provider for EF and also Oracle provides a provider.

    Why is SyBase not supported? Well because Sybase (and I read this recently) has no interest in providing an Entity Framework provider so when we say EF does not support SyBase this is false, instead Sybase does not support working with EF or EF Core.

    Now there is a third party product which supports SyBase called DataDirect. The cost for a single developer/work station is relatively cheap

    So if you want to use EF then you will need something like the product above or forget SyBase for EF and use another database or go with ODBC as a data provider which is not a native data provider so you get what you get.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by FcabralJ Tuesday, January 28, 2020 6:39 AM
    Saturday, January 25, 2020 9:44 PM
    Moderator

All replies

  • Moving the data from a DB into classes is done by what is known as an "Object-Relational Mapper" (ORM). Normally, you would not write your own; instead, you use one of the many that already exist. Microsoft provides one named "Entity Framework", but there are many others.

    If you decide to do it yourself, then yes, you have to map each of the fields. You can resort to using Reflection so that you don't have to write the code for each field, but ultimately the code will result in mapping each one of them. This is what it would look like if you map the fields manually, more or less:

    public class Car    
    {
      public string Name { get; set; }
      public string Brand { get; set; }
    }
    
    public class DataProccess
    {
      public IEnumerable<Car> GetCars()
      {
        var list = new List<Car>();
        using (SqlConnection cn = new SqlConnection(conn))
        {
           SqlCommand cmd = new SqlCommand("Select Name, Brand FROM cars",_conn);
           cn.Open();
           using (SqlDataReader rdr = cmd.ExecuteReader())
           {
             while (rdr.Read())
             {
               list.Add(new Car {
                 Name = rdr.GetString(0),
                 Brand = rdr.GetString(1)
               });
             }
          }
        }
        return list;
      }
    }



    Friday, January 24, 2020 3:36 PM
    Moderator
  • You might consider looking at either Entity Framework classic (version 6) or Entity Framework Core. Both using code first. The following addin to Visual Studio will create just about all that is needed to get started.

    The following are classes that represent two tables in a database.

    Code to read customers table where in simple terms NorthWindAzureContext connects to the database while context.Customers access all customers.

    using (var context = new NorthWindAzureContext())
    {
        var customers = context.Customers.ToList();
    }

    Place a WHERE condition on the query

    var customers = context.Customers.Where(customer => customer.Country == "Germany").ToList();

    Determine if there are changes later in the app.

    private NorthWindAzureContext _azureContext = new NorthWindAzureContext();
    private void button1_Click(object sender, EventArgs e)
    {
        var customers = _azureContext.Customers.Where(customer => customer.Country == "Germany").ToList();
    }
    
    private void button2_Click(object sender, EventArgs e)
    {
        if (_azureContext.ChangeTracker.HasChanges())
        {
            // prompt user to save
        }
    }

    If not interested in Entity Framework you can read a data into a DataTable with code such as this.

    public List<Customers> DataTableToList(DataTable dt)
    {
        IEnumerable<Customers> emailData = dt.AsEnumerable().Select((dataRow) => new Customers()
        {
            CustomerIdentifier = dataRow.Field<int>("CustomerIdentifier"), CompanyName = dataRow.Field<string>("CompanyName")
        }).ToList();
    
        return emailData.ToList();
    
    }

    IMPORTANT

    Entity Framework knows how to deal with changes while the last code example does not detect changes.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 24, 2020 6:38 PM
    Moderator
  • Thank you for both answers.

    I think I have another problem as I downloaded the SQL Anywhere developer edition from Sybase, but after installing it I can't connect to right entity data model as just SQL server appears as an option. 

    Saturday, January 25, 2020 8:22 AM
  • Here is an official list of data providers for Entity Framework Core which does not include Sybase and after a quick web search found that the parent company SAP has no plans to provide a provider for EF Core. To connect to Sybase is going to be an effort as there is no supported data provider at all, to work with Sybase you will need to use ODBC data provider which is a provider that is not native to any one database unlike SQL-Server, MS-Access and Oracle who all have native providers for Entity Framework Core then with Entity Framework there is no MS-Access so that means using OleDb and Oracle provider is rather weak.

    You can never go wrong with using SQL-Server Express edition along with SSMS (SQL-Server Management Studio) which covers all bases needed to work with databases. Both SQLEXPRESS and SSMS are free.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, January 25, 2020 10:24 AM
    Moderator
  •         public List<T> DataTableToList<T>(DataTable table) where T : class, new()
            {
                try
                {
                    List<T> list = new List<T>();
    
                    foreach (var row in table.AsEnumerable())
                    {
                        var obj = row.ToObject<T>();
    
                        list.Add(obj);
                    }
    
                    return list;
                }
                catch
                {
                    return null;
                }
            }

    And use

    var cars=DataTableToList<Car>(_dataTable);



    • Edited by HasanJaf Saturday, January 25, 2020 10:37 AM text to code
    Saturday, January 25, 2020 10:37 AM
  •         public List<T> DataTableToList<T>(DataTable table) where T : class, new()
            {
                try
                {
                    List<T> list = new List<T>();
    
                    foreach (var row in table.AsEnumerable())
                    {
                        var obj = row.ToObject<T>();
    
                        list.Add(obj);
                    }
    
                    return list;
                }
                catch
                {
                    return null;
                }
            }

    And use

    var cars=DataTableToList<Car>(_dataTable);



    Note with this code you there is nothing at all for communicating back to a database which is what Entity Framework or Entity Framework Core provides natively.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, January 25, 2020 11:54 AM
    Moderator
  • I totally agree on using EF as it provides us a lot of things out of the box, but if it's not compatible with Sybase I can't use it. 

    What is strange for me is that SqlAnywhere says it provides ADONET for sybase. https://docs.telerik.com/data-access/deprecated/developers-guide/database-specifics/sql-anywhere/database-specifics-sqlanywhere-voerview

    Saturday, January 25, 2020 5:57 PM
  • Alberto, thank you for your answer.

    I'm using SybaseIQ and I couldn't figure out how can I use the EF with it. Do you know how could I do that? 

    Saturday, January 25, 2020 6:36 PM
  • @HasanJaf,

    When I do that i get the following error:

    'DataRow' does not contain a definition for 'ToObject' and no accessible extension method 'ToObject' accepting a first argument of type 'DataRow' could be found.

    public static List<T> DataTableToList<T>(DataTable dt) where T : class, new()
            {
                List<T> lstData = new List<T>();
    
                var convertedList = (from rows in dt.AsEnumerable()
                                     select rows);
    
                foreach (var row in dt.AsEnumerable())
                {
                    lstData.Add(row.ToObject<T>());
                }
    
                return lstData;
            }


    Saturday, January 25, 2020 6:46 PM
  • Sorry. please add as extension method to your project.

    public static T ToObject<T>(this DataRow row) where T : class, new()
            {
                T obj = new T();
    
                foreach (var prop in obj.GetType().GetProperties())
                {
                    try
                    {
                        if(prop.PropertyType.IsGenericType && prop.PropertyType.Name.Contains("Nullable"))
                        {
                            if (!string.IsNullOrEmpty(row[prop.Name].ToString())) 
                                prop.SetValue(obj, Convert.ChangeType(row[prop.Name], 
                                Nullable.GetUnderlyingType(prop.PropertyType), null));
                            //else do nothing
                        }
                        else
                            prop.SetValue(obj, Convert.ChangeType(row[prop.Name], prop.PropertyType),null);
                    }
                    catch
                    {
                        continue;
                    }
                }
                return obj;
            }

    • Marked as answer by FcabralJ Tuesday, January 28, 2020 6:40 AM
    Saturday, January 25, 2020 7:54 PM
  • I'm using SybaseIQ and I couldn't figure out how can I use the EF with it.

    No, EF only supports a limited number of databases, and Sybase is not among them.

    You may have better luck using a different ORM. For instance, I believe that NHibernate supports Sybase, although I have never tried it myself.

    Saturday, January 25, 2020 9:06 PM
    Moderator
  • I totally agree on using EF as it provides us a lot of things out of the box, but if it's not compatible with Sybase I can't use it. 

    What is strange for me is that SqlAnywhere says it provides ADONET for sybase. https://docs.telerik.com/data-access/deprecated/developers-guide/database-specifics/sql-anywhere/database-specifics-sqlanywhere-voerview

    Here is the deal, each database company e.g. Microsoft for SQL-Server provides a provider for EF and also Oracle provides a provider.

    Why is SyBase not supported? Well because Sybase (and I read this recently) has no interest in providing an Entity Framework provider so when we say EF does not support SyBase this is false, instead Sybase does not support working with EF or EF Core.

    Now there is a third party product which supports SyBase called DataDirect. The cost for a single developer/work station is relatively cheap

    So if you want to use EF then you will need something like the product above or forget SyBase for EF and use another database or go with ODBC as a data provider which is not a native data provider so you get what you get.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by FcabralJ Tuesday, January 28, 2020 6:39 AM
    Saturday, January 25, 2020 9:44 PM
    Moderator
  • I would avoid writing all the boilerplate ADO.NET Code and use Dapper. Dapper is a micro ORM and has no DB specific implementation details. It works across all .NET ADO providers including MySQL. It will handle mapping the results into a strongly typed class.

    example

    public class Car    
    {
      public string Name { get; set; }
      public string Brand { get; set; }
    }
    string sql = "SELECT * FROM cars";
    
    using (var connection = new SqlConnection("YOUR CONNECTION"))
    {            
        var cars = connection.Query<Car>(sql).ToList();
    
        // more code
    }
    


    william xifaras

    Sunday, January 26, 2020 2:34 AM