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
    }
}

Answer 1

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