What is a good way to model my SQL Table Class?

Question

I am trying to retrieve a table with several columns and I have created a class that will be able to represent each row as an object with properties.

E.G.

class TableA {
   int prop1;
   int prop2;
   ....
}

I am using SqlDataReader to read the value for each row and then assigning it to the object that I have created

TableA tab = new TableA()
tab.prop1 = sqlreader.GetValue(prop1_ordinal).toString();

At the moment I need to explicitly state:

tab.prop1 = etc2.. 
tab.prop2 = etc2...

This can be quite troublesome when I have quite a few properties (20+ or so).

What other alternatives should I be using?

I am thinking of using a Dictionary or something of the sort but am not sure how to start. That way, I can just use a foreach loop to go through a list of all the properties and set the values.

Essentially, I don't want to put in too much redundant code just to set values.

After all the data has been put into the object, I will essentially write it to a CSV file after the values have been manipulated and changed.

Any thoughts will be appreciated?

Try something with a code generator. Writing explicit assignments can become cumbersome and error prone. A code generator would take care of that for you and be correct. — Andrew, Mar 18 '15 at 02:10
Possible duplicate [convert-rows-from-a-data-reader-into-typed-results][1] [1]: http://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results — nobody, Mar 18 '15 at 02:22

score 2 · Answer 1 · answered Mar 18 '15 at 02:22

Have you tried using linq to generate your table & schema:

[Table(Name = "Test")]
public class TableA
{
    [Column(IsPrimaryKey = true)]
    public int ID { get; set; }

    [Column]
    public int prop1 { get; set; }

    [Column]
    public int prop2 { get; set; }
}

static int main()
{
    var constr  =   @" Data Source=NOTEBOOK\SQLEXPRESS;Initial Catalog=DemoDataContext;Integrated Security=True " ;
    var context  =   new  DataContext(constr) { Log  =  Console.Out };
    var metaTable  =  context.Mapping.GetTable( typeof (TableA));

    var typeName  =   " System.Data.Linq.SqlClient.SqlBuilder " ;
    var type  =   typeof (DataContext).Assembly.GetType(typeName);
    var bf  =  BindingFlags.Static  |  BindingFlags.NonPublic  |  BindingFlags.InvokeMethod;
    var sql  =  type.InvokeMember( " GetCreateTableCommand " , bf,  null ,  null ,  new [] { metaTable });
        Console.WriteLine(sql);
         // Excute SQL Command 
}

Make sure to include System.Data.Linq and:

using System.Data.Linq.Mapping;
using System.Data.Linq;
using System.Reflection;

You can find more information at: https://msdn.microsoft.com/en-us/library/bb384396.aspx

Once you have everything mapped you can import the data into an object by using linq funcitonality to fill in objects for you !

And an example: https://social.msdn.microsoft.com/Forums/en-US/2bdfdde6-596e-4880-a3b3-3cb3ec365245/could-i-use-linq-to-sql-create-table-in-my-database?forum=linqtosql

CindyH · Answer 2 · 2015-03-18T03:03:58.017

EntityFramework is an ORM (object relational mapper) designed for such a thing. It's perfect for an enterprise-level product, but it's pretty big and bulky if you have a smaller project. Dapper is a super-light-weight ORM that will essentially do the tab.prop1 = sqlreader.GetValue(prop1_ordinal).toString(); part for you if you name the properties the same as the column names. You pull Dapper in with NuGet. The following code will give you an IEnumerable of your object (TModel).

        IEnumerable<TModel> result;
        using (MySqlConnection conn = new MySqlConnection(_mysqlConnString))
        {
            // "Query" is a Dapper extension method that stuffs the datareader into objects based on the column names
            result = conn.Query<TModel>("Select * from YourTable");
        }
        // do stuff with result

This links to a full example, instead of just the piece I pulled out of my current project. http://www.tritac.com/bp-24-dapper-net-by-example

score 0 · Answer 3 · answered Mar 18 '15 at 04:03

The problem you are describing is one of the main reasons we have Object Relational Mappers (ORM's). The easiest to use for Microsoft SQL Server is probably Linq2Sql, but Entity Framework allows you to use other database engines and will allow you to define more complicated relationships. Another solid ORM is NHibernate.

score 0 · Answer 4 · answered Mar 18 '15 at 22:01

If you find EntityFramework to be big and bulky you could try Trinity Framework it's a small database first ORM framework with T4 templates and smart mapping to you database.

nugget: PM> Install-Package TrinityFramework

What is a good way to model my SQL Table Class?

4 Answers4