3 replies to this topic

[terric]

Hi everyone, I'm new to the forum.

I've started to learn LINQ today and I'm trying to create a bunch of reusable objects that I can apply to multiple systems. For example: the system will have people and users. Therefore I have two classes:

[Person]
[User]

These classes will have interfaces that allow contracts such as create(), update() and delete(). Now what I want to do is implement the LINQ syntax in each of these methods that will perform the respective function. However, I obviously need to define the schema for the tables these objects will represent. My question basically is how can I extract the mapping of these objects to their respective tables? The only area of change I want between multiple systems using this class library is this mapping of object attributes to database table schema.

Is this possible? I'm getting sick to death of writing stored procedures for each system so would like to encapsulate this stuff into the objects themselves.

Thanks for any advice!

T

[sam_coder]

Firstly, welcome to the forum!

I think you need to be a little more verbose, and maybe give a specific example...

are you saying you want to be able to create a generalized class, that will know how to insert, update, delete, or select from whatever table?

That's easy to do.. but, at some point you have to take the generalization out, or else working with your new class is going to be a pain in the ass. (can i say that here?) :cool:

There are a lot of advantages to stored procedures that a lot of folk don't think about.
it can make your code work on different DB vendors (MySQL, SQL Server, Oracle, etc)
it can make your code a lot easier to maintain, its definitely cleaner looking

Anyways, I'm stepping out of the approximate area of the question you asked.. can you give me a specific example?

[terric]

Hi, thanks for the reply!

I'll try to describe the issue a little better. Basically I want to completely separate the data layer from the business logic layer. So let's take the Person class for example (with it's data layer interface that all classes that read / write to the database will implement):

class IRecord {
void Create() { }
void Update() { }
void Delete() { }
}

class Person : IRecord {
string _forename;
string _surname;

void Create() {
// LINQ statement here that will basically write to any database
}

void Update() {
// LINQ statement here that will basically write to any database
}

void Delete() {
// LINQ statement here that will basically write to any database
}
}

The clear area of change here will be the mappings between the object Person and the database table schema. I may want to use this set of objects on a MySQL database with 2 columns (mysql_first_name, mysql_last_name) and an MSSQL database with the same 2 columns (mssql_first_name, mssql_last_name). With LINQ I assume the syntax of the query can be the same across all database technologies, it is the column mappings that will be different. I could use indexes in this example yes, but that kind of defeats the point of my question.

Could I, for each implementation, assign the column mappings on the instantiation of the object Person thus making that the only change I need to do across implementations or pass a class that contains the column mappings into the object? Or is there a better, more LINQ way of doing this?

I really hope that helps describe my problem a little better!!

T

[sam_coder]

I think so, but you know, when you use Linq to SQL, you would typically have the tooling generate your mappings for you.
a great intro: Using LINQ to SQL (Part 1) - ScottGu's Blog

this is one of the problems I have with linq, its fantastic right up until a certain point. I mean you can always wrap your linq with whatever you want, as you've shown, but in doing so, by separating your class, you'll find yourself creating and committing contexts way to often, unless you pass them in with the calls to Create, Update, Delete etc.
One of the big advantages to linq, is limiting the amount of times you actually hit the database. This type of optimization almost makes it undesirable for the enterprise, but highly desirable for a lot of other things.

I may be over-generalizing when I say that, I very much get the want to break this down into layers of like-logic.

Have you considered "Vanilla" ADO.NET?

I would typically start by creating a relevant namespace, and a 'base' data class like this:

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;


namespace whatever.data {


    /// <summary>

    /// Base/Abstract Data Class

    /// </summary>

    internal abstract class Data<T> : object {


        /// <summary>

        /// Used to generate connection objects

        /// </summary>

        protected ConnectionFactory m_connection_factory;


        /// <summary>

        /// Initialize Data Class

        /// </summary>

        /// <param name="connection_name">Name of the connection</param>

        protected Data(string connection_name) {

            m_connection_factory = ConnectionFactory.new_instance(connection_name);

            return;

        }


        /// <summary>

        /// Generates a single result

        /// </summary>

        /// <param name="extract_func">function used to extract values from database reader</param>

        /// <param name="reader">reader used to pull result from the datasource</param>

        /// <returns>object representing row in datasource</returns>

        protected T generate_result(Func<IDataReader, T> extract_func, IDataReader reader) {

            using (reader) {

                if (!reader.Read())

                    return default(T);

                return extract_func(reader);

            }

        }


        /// <summary>

        /// Generates an array of results from the database

        /// </summary>

        /// <param name="extract_func">function used to extract values from database reader</param>

        /// <param name="reader">reader used to pull results form the datasource</param>

        /// <returns>object representing row in the datasource</returns>

        protected T[] generate_results(Func<IDataReader, T> extract_func, IDataReader reader) {

            using (reader) {

                List<T> results = new List<T>();

                while (reader.Read())

                    results.Add(extract_func(reader));

                return results.ToArray();

            }

        }


        /// <summary>

        /// Extracts a scalar value from a data reader

        /// </summary>

        /// <typeparam name="NT">expected type to be returned</typeparam>

        /// <param name="reader">reader used to pull scalar value from the data source</param>

        /// <returns>scalar value</returns>

        protected NT extract_scalar<NT>(IDataReader reader) {

            using (reader) {

                if (reader.Read())

                    return (NT)reader.GetValue(0);

                else

                    return default(NT);

            }

        }


    }

}

see, this way, I can create my complex times, like...

class Person {

   int ID { get; set; }

   string name { get; set; }

   ...

}

And then I would create a class for each domain within my application. A domain for this might be.. People, or Employees, or something like that. This class would be what I use to interact with the table, and would directly translate to the object time shown.

internal class People : Data<Person> {


        #region [Construction]


        /// <summary>

        /// Initialize Labels

        /// </summary>

        internal News()

            : base(ConfigurationManager.AppSettings["default_db"]) {

            return;

        }


        #endregion


        #region [Extraction Methods]


        private NewsStory extract_news_story(IDataReader reader) {

            Person person = new Person();

            person.id = reader.GetInt32(0);

            person.name = reader.GetString(1);

            return person;

        }


        #endregion


        #region [Get Methods]


        internal Person[] get_all() {

            string sql_query = "SELECT P.id, P.name FROM people P ORDER BY P.name ASC;";

            using (MySqlConnection connection = m_connection_factory.create_connection()) {

                connection.Open();

                using (MySqlCommand command = new MySqlCommand(sql_query, connection)) {

                    using (IDataReader reader = command.ExecuteReader()) {

                        return base.generate_results(extract_news_story, reader);

                    }

                }

            }

        }


        #endregion


        #region [Update Methods]


//update methods here, in a similar fashion


        #endregion


    }

Anyways, you get the idea. I know it's not what you were asking, specifically about Linq, but perhaps you'll find some worth in it...