Back to list
Views:   0
Replies:  0
Archived
,
Reply 1
Generics approach will work.  I have implemented my own custom DAL 5 years back and it is still going smooth (no exaggeration here).

The approach is similar to what is in EF , but with a twist.  Just to outline how it works (there are no class clutters)..

Assume there is a Customer table in the DB.  The entity is modelled as below

[Table ("ebCustomerMaster")]
public class Customer
{
    [Column()]
     public Guid Id {
             get { return id; }
             set { id = value; }
     }

    [Column()]
     public string Name{
             get { return name; }
             set { name = value; }
     }
}

I am just displaying a subset of properties.  Note this class is auto generated by a custom utility.

Now we have a SmartData<T> class which is responsible for generating the SQL based on xml file (as this was our requirement).

A sample insert function in the SmartData is displayed below.


    public class SmartData<T>
    {

        public int Insert(T obj, string spName)
        {
            DataRow queryRow = dsSQL.Tables["Query"].Select("Id = '" + spName + "'")[0];

            string commandText = queryRow["CommandText"].ToString();
            string commandType = queryRow["CommandType"].ToString();

            Request request = new Request();
            request.CommandText = commandText;

            request.CommandType = (CommandType)Enum.Parse(typeof(CommandType), commandType);
            ProcessParameter(obj, request);

            return dbEngine.Insert(request);
        }
       
         // MORE functions goes here....
        
}

NOTE : Here there is no scope for SQL injection attach as the actual sql is not built here....

Then there's a DB engine which is responsible for talking with the underlying dtabase.

The DBEngine's Insert function is shown below...

        public int Insert(Request request)
        {
            using (DbCommand command = factory.CreateCommand())
            {
                int ret = 0;
                //if (null == connection || connection.State != ConnectionState.Open)
                OpenConnection();

                command.Connection = connection;
                if (request.Parameters.Count > 0)
                {
                    foreach (Parameter para in request.Parameters)
                    {
                        DbParameter p1;
                        switch (dataProvider)
                        {
                            case "System.Data.OleDb":
                                p1 = new System.Data.OleDb.OleDbParameter(para.ParameterName, para.Value);
                                p1.DbType = GetDbTypeFromString(para.DataType);
                                command.Parameters.Add(p1);
                                break;
                            case "System.Data.SqlClient":
                                p1 = new System.Data.SqlClient.SqlParameter(para.ParameterName, para.Value);
                                p1.DbType = GetDbTypeFromString(para.DataType);
                                command.Parameters.Add(p1);
                                break;
                            default:
                                command.Parameters.Add(para);
                                break;
                        }
                    }
                }

                command.CommandText = request.CommandText;
                command.CommandType = request.CommandType;
                command.CommandTimeout = 0;

                ret = command.ExecuteNonQuery();
           
                return ret;
            }

The actual code may differ than what is shown here....

The good thing is there are no sub classes or any other helpers apart from the usual Service layer....
The DBEngine abstracts away the providers.  The query details in the XML gives us the flexibility to switch to stored procedures
or plain SQL queries at will.

Switching providers is as easy as creating a new xml file and updating only specifics which changes (in most cases
sql server, mysql, and oracle, most of the things are same)......


NOTE and suggestion:  I had taken this approach when EF was not available (or was in early stages) and data access  block
was also in  a very early stage.

I will recommend you also have a look at the MS Enterprise Library Data access blocks as well.  But don't be afraid to roll out your own if need arises.

This custom solution has provided me with a lot of flexibility in  a real enterprise application handling about 400 tables of data entry and about 1000 reports.... So, you can imagine the magnitude of usage of this library.. The only thing is it is not opensource, but there are various similar implementations available, and if you need to roll out, you can quickly do so... It's not that complex.

Hope this helps !




Rajesh Pillai, Mar 25, 2011
Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas