none
Dynamic LINQ expressions RRS feed

  • Question

  • Hi all,

    I'm just getting started with .NET / C#, coming from a PHP background and I'm wondering if someone can basically tell me if what I want to try and do is the correct way to go about it in the .NET world, or if I'm trying to do something that just isn't done...

    Basically want I want to be able to do is write a library which will handle CRUD operations for a database (through EF). So I would define a class that can be initialised with a link to the database, the table to be edited and the columns to be used.

    In PHP I might use something like:

    $src = new DataRetrieval( $db, 'staff' )
       ->addField( 'first_name' )
       ->addField( 'last_name' )
       ->addField( 'position' );

    Then have methods such as `read`, `edit`, `insert` and `remove` on the class (passing in the POSTed data). It would generate the SQL needed based on the fields added.

    Is that the correct approach to take here as well?

    Thanks, and sorry for the "basicness" of the question!

    Allan

    Monday, August 4, 2014 1:59 PM

Answers

  • Yes, but then you shouldn't use EF as you haven't defined any data model. EF is an object relational mapper that basically maps objects (classes) in your application to tables in a database.

    Below is an example of a method that takes a connectionstring, a table name and a variable number of column names and performs a query against the database specified in the connectionstring using the common ADO.NET classes SqlConnection, SqlCommand and SqlDataReader:

    public void ReadFromDatabase(string connectionString, string tableName, params string[] columns)
            {
                string queryString = string.Format("SELECT {0} FROM {1}", string.Join(",", columns), tableName);
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read()) //for each row in the resultset
                    {
                        for (int i = 0; i < columns.Length; ++i) //for each column in the row
                        {
                            string columnValue = reader[i].ToString();
                        }
    
                        //do something...
                    }
                }
    
            }
    

    Sample usage:

    ReadFromDatabase("Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;", "MyTable", "myCol1", "myCol2");

    You should take this approach if you want to be able to query a database based on the dynamic value of some input parameters.

    • Marked as answer by allanjard Friday, August 8, 2014 3:25 PM
    Friday, August 8, 2014 1:13 PM

All replies

  • If you are trying to write a generic data access layer that can be used for retrieving, inserting, editing and deleting data from a database through Entity Framework, my blog post may be a good starting point for you: http://blog.magnusmontin.net/2013/05/30/generic-dal-using-entity-framework/
    Monday, August 4, 2014 2:27 PM
  • Hi Magnus,

    Your blog post is fantastic. I'm still reading an digesting the information in it, but this is exactly the kind of thing I'm looking for I think. Thanks!

    Allan

    Tuesday, August 5, 2014 1:47 PM
  • Hi Magnus,

    I've read through your blog post and implemented the code noted there, but while that all seems to make sense, it doesn't seem to provide the generic access that I'm looking for. There is code in the DAL that specifically refers ti the department and employees for example (the repositories).

    I was more looking for a way to write a library that I can provide as a dll which will provide the business logic you mention in your article for validation and so on - with the client telling me what the structure of the data is.

    Is that is something that is possible using this approach?

    Thanks,

    Allan

    Wednesday, August 6, 2014 3:27 PM
  • "The client telling me what the structure of the data is"? This is the wrong way of doing things, the client application shouldn't really know anything about how the data is stored. This would defeat the whole purpose of using a data access layer (DAL) and an N-tier architecture.

    Also, the entity model consists of a conceptual model, a storage model and a mapping file between these. This means that you need to update the model whenever you want the generic DAL to support another entity anyway. The purpose of using the generic class is to save you the time it takes to write code for doing the CRUD operations for each entity.

    Thursday, August 7, 2014 7:39 AM
  • What I'm trying to do is produce a dll which a client can use to perform the CRUD actions, regardless of what the data source is. The CRUD processing would be completely generic and would be configured to perform the CRUD actions by the client - rather like my example at the top of the thread. There the `DataRetriveal` class is told about three fields and can process them (select, insert, update, validate, whatever).

    Is that just not possible with EF, or is it not the way things are done in the .NET world?

    What I'm really struggling with conceptually is how to provide the CRUD processing in a dll which doesn't have a reference to the database (since I don't know what the database layout will be - the client configures that).

    Allan

    Thursday, August 7, 2014 1:34 PM
  • What I'm trying to do is produce a dll which a client can use to perform the CRUD actions, regardless of what the data source is. The CRUD processing would be completely generic and would be configured to perform the CRUD actions by the client - rather like my example at the top of the thread. There the `DataRetriveal` class is told about three fields and can process them (select, insert, update, validate, whatever).

    Is that just not possible with EF, or is it not the way things are done in the .NET world?

    What I'm really struggling with conceptually is how to provide the CRUD processing in a dll which doesn't have a reference to the database (since I don't know what the database layout will be - the client configures that).

    Allan

    No, if the client configures the database you cannot use EF since its entity model relies on a conceptual model, a storage model and a mapping file between these as I wrote in my previous reply.

    In an enterprise environment, the DAL is the only layer that should know anything about the database. Configuring the database from the client makes no sense at all in a large enterprise system scenario and EF was and is not designed to be used this way. I guess that answers your question.

    Please remember to mark any helpful posts as answer to close the thread.


    Thursday, August 7, 2014 1:45 PM
  • Thanks for the reply - I really appreciate your input.

    So one final question if I may. There is no way to create a class that I can initialise a class with an array of strings, each defining a column in a database, and that class would be able to read from the database and perform whatever other logic is required?

    I'd hoped to use EF to do this rather than needing to write a database abstraction layer that creates the SQL, as I did in PHP.

    Thanks again,

    Allan

    Thursday, August 7, 2014 4:07 PM
  • Yes, but then you shouldn't use EF as you haven't defined any data model. EF is an object relational mapper that basically maps objects (classes) in your application to tables in a database.

    Below is an example of a method that takes a connectionstring, a table name and a variable number of column names and performs a query against the database specified in the connectionstring using the common ADO.NET classes SqlConnection, SqlCommand and SqlDataReader:

    public void ReadFromDatabase(string connectionString, string tableName, params string[] columns)
            {
                string queryString = string.Format("SELECT {0} FROM {1}", string.Join(",", columns), tableName);
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read()) //for each row in the resultset
                    {
                        for (int i = 0; i < columns.Length; ++i) //for each column in the row
                        {
                            string columnValue = reader[i].ToString();
                        }
    
                        //do something...
                    }
                }
    
            }
    

    Sample usage:

    ReadFromDatabase("Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;", "MyTable", "myCol1", "myCol2");

    You should take this approach if you want to be able to query a database based on the dynamic value of some input parameters.

    • Marked as answer by allanjard Friday, August 8, 2014 3:25 PM
    Friday, August 8, 2014 1:13 PM
  • That's great - thanks for this. I very much appreciate the feedback!

    Allan

    Friday, August 8, 2014 3:25 PM