Generic Data Access Layer in an Multi-Layer Arcitechture

Question

I need to create an generic data access layer to use in my final assignment in Software Engineering, but my data access layer that I have currently created can automatically generate CRUD(Create, Read, Update and Delete) SQL Statement. I still need to define every table in my database and every time I change my database I need to define the changes in my data access layer.

Please look at the sample of my code and tell me how to change my code to improve my access layer:

class sqlConn
{
    //Local
    private String strConn = @"Data Source=.\SQLEXPRESS;" +
        @"AttachDbFilename='D:\JP Stuff\BELGIUM CAMPUS\3de Jaar\SOFTWARE ENGINEERING\ASSIGNMENT\Premier Service Solutions\Premier Service Solutions\DB\db_PSS_1.0.mdf';" +
        @"Integrated Security=True;" +
        @"User Instance=True";
    private SqlConnection conn;

    //Properties
    public SqlConnection Conn
    {
        get { return this.conn = new SqlConnection(this.strConn); }
    }

    //Constructor
    public sqlConn()
    {

    }
}




class sqlFactory : sqlConn
{
    //Constructor
    public sqlFactory()
        : base()
    {

    }

    //Insert Record into database
    public void Create(String[] dbData, List<String> strRow)
    {
        using (SqlConnection sqlCon = this.Conn)
        using (SqlCommand com = new SqlCommand("SELECT * FROM " + dbData[0], sqlCon))
        {
            SqlDataAdapter da = new SqlDataAdapter(com);
            SqlCommandBuilder sqlbuilder = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds, dbData[0]);

            DataRow dr = ds.Tables[dbData[0]].NewRow();

            for (int i = 0; i < dbData.Count() - 2; i++)
            {
                dr[i + 1] = strRow[i];
            }

            ds.Tables[dbData[0]].Rows.Add(dr);
            da.Update(ds, dbData[0]);
        }
    }
}

 class dbDefinitions : sqlFactory
 {
    public static Dictionary<String, String[]> columns;

    static dbDefinitions()
    {
        columns = new Dictionary<String,String[]>();

        //tblCall Definition
        #region call
        String[] data = new String[]
        { 
            "tblCall", "call_ID_PK", "call_emp_ID_FK", 
            "call_Description", "call_Notes", "call_Start_Time", 
            "call_End_Time", "call_Job_FK"
        };
        columns.Add("call", data);
        #endregion
    }
}

Probably because the question is somewhat vague, and it's hard to pick a definitive answer. In other words, every answer here so far is helpful, but which is THE answer. — Craig M, Aug 18 '11 at 19:32

score 1 · Accepted Answer · edited May 23 '17 at 11:55

This may not answer your question fully, but you can improve this code in several ways.

Composition versus Inheritance

First, understand and apply composition over inheritance. Composition is a "has a" relationship, whereas inheritance is a "is a" relationship.

For example, if a Person class has a property of the Phone class type, it's composition.

public class Person 
{
    public Phone Phone {get; set;}
}

If a Person class descends from the Phone class it's inheritance.

public class Person : Phone
{
}

In your code, sqlFactory should contain a sqlConn instead of inheriting from it.

Composition gives more flexibility, especially since C# doesn't allow for multiple inheritance. Read here for more: Prefer composition over inheritance?

SQL Injection

You should never build sql statements using string concatenation like this.

"SELECT * FROM " + dbData[0]

This creates a potential security hole that allows for SQL Injection attacks. You should always use parameterized queries to prevent against this.

Read Tip/Trick: Guard Against SQL Injection Attacks to understand SQL Injection attacks and how to prevent them.

Coding Convention

The nearly universally accepted convention for class naming amongst C# developers is to use PascalCase, where the first letter of every word in your class name is capitalized. Your classes would be SqlFactory, SqlConn, and DbDefinition.

This guide gives fairly commonly used conventions: C# Coding Standards document

score 1 · Answer 2 · answered Aug 18 '11 at 14:28

Your DAO should have backing classes that act as models to the tables. These models should all have a common interface. Your DAO should then have instances of the Model interface with XML configuration pointing to the proper tables for the model. This will prevent you from having to define your tables in your code. Your data access layer is the layer that accesses your data, not the layer that defines your data. Your models should define the data.

score 0 · Answer 3 · answered Aug 18 '11 at 13:41

0

Try defining the database schema information in an xml file and read it to create the CRUD operations.

answered Aug 18 '11 at 13:41

Vijay

513
1
6
16

Generic Data Access Layer in an Multi-Layer Arcitechture

3 Answers3