C# static Data Access Layer

Hola,

This about C# Data Access Layer. For example, cosider the following 2 code snippets one using static class DataAccessLayer and the other non-static class DataAccessLayer.

What are the pros and cons of a static DAL if the GUI is an ASP.NET or MVC page in a web application. And if you think this question is out of scope of this forum, then if the GUI is a Windows Form in a dirstributed application?

Please help with expert inputs.

Code Snippet 1:

//DAL

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

static class DataAccessLayer
{
    private static string ConnectionString
    {
        get
        {
            ConnectionStringSettingsCollection connectionStringSettings = ConfigurationManager.ConnectionStrings;
            return connectionStringSettings["ConnectionString"].ConnectionString;
        }
    }

    public static DataTable Select(SqlCommand commandSql)
    {
        DataTable datTable = null;
        try
        {
            using (SqlConnection connectionSql = new SqlConnection(ConnectionString))
            {
                commandSql.Connection = connectionSql;
                commandSql.Connection.Open();
                datTable = new DataTable();
                datTable.Load(commandSql.ExecuteReader());
                return datTable;
            }               
        }
        finally
        {
            datTable = null;
        }
    }

    public static int Execute(SqlCommand commandSql)
    {
        int rowsAffected = 0;
        using (SqlConnection connectionSql = new SqlConnection(ConnectionString))
        {
            commandSql.Connection = connectionSql;
            commandSql.Connection.Open();
            rowsAffected = commandSql.ExecuteNonQuery();           
        }
        return rowsAffected;
    }
}

//GUI using static class DataAccessLayer:

private DataTable SelectRecord()
{
    DataTable datTable = null;
    try
    {
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("Select * FROM Customer;");
        using (SqlCommand commandSql = new SqlCommand(selectQuery.ToString()))
        {
            datTable = DataAccessLayer.Select(commandSql);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());                
    }
    return datTable;
}

private DataTable SelectRecord(string column)
{
    DataTable datTable = null;
    try
    {
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("Select * FROM Customer ");
        if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("CustomerId = @CustomerId;");
        }
        else if (!string.IsNullOrEmpty(textBox2.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("FirstName = @FirstName;");
        }
        else if (!string.IsNullOrEmpty(textBox3.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("LastName = @LastName;");
        }
        else if (!string.IsNullOrEmpty(textBox4.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("Synonym = @Synonym;");
        }
        using (SqlCommand commandSql = new SqlCommand(selectQuery.ToString()))
        {
            if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@CustomerId", Convert.ToInt32(textBox1.Text.Trim()));
            }
            else if (!string.IsNullOrEmpty(textBox2.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@FirstName", textBox2.Text.Trim());
            }
            else if (!string.IsNullOrEmpty(textBox3.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@LastName", textBox3.Text.Trim());
            }
            else if (!string.IsNullOrEmpty(textBox4.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@Synonym", textBox4.Text.Trim());
            }
            datTable = DataAccessLayer.Select(commandSql);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    return datTable;
}

private bool Insert(string firstName, string lastName, string synonym)
{
    bool isInserted = false;
    try
    {
        int rowsAffected = 0;
        StringBuilder insertQuery = new StringBuilder();
        insertQuery.Append("INSERT INTO Customer ");
        insertQuery.Append("(");
        insertQuery.Append("FirstName, ");
        insertQuery.Append("LastName, ");
        insertQuery.Append("Synonym ");
        insertQuery.Append(") ");
        insertQuery.Append("VALUES ");
        insertQuery.Append("(");
        insertQuery.Append("@FirstName, ");
        insertQuery.Append("@LastName, ");
        insertQuery.Append("@Synonym ");
        insertQuery.Append(");");
        using (SqlCommand commandSql = new SqlCommand(insertQuery.ToString()))
        {
            commandSql.Parameters.AddWithValue("@FirstName", firstName);
            commandSql.Parameters.AddWithValue("@LastName", lastName);
            commandSql.Parameters.AddWithValue("@Synonym", synonym);
            rowsAffected = DataAccessLayer.Execute(commandSql);
        }
        if (rowsAffected > 0)
        {
            isInserted = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    return isInserted;
}

private bool Update(int custId, string firstName, string lastName, string synonym)
{
    bool isUpdated = false;
    try
    {
        int rowsAffected = 0;
        StringBuilder updateQuery = new StringBuilder();
        updateQuery.Append("UPDATE Customer ");
        updateQuery.Append("SET ");
        updateQuery.Append("FirstName = @FirstName, ");
        updateQuery.Append("LastName = @LastName, ");
        updateQuery.Append("Synonym = @Synonym ");
        updateQuery.Append("WHERE ");
        updateQuery.Append("CustomerId = @CustomerId;");
        using (SqlCommand commandSql = new SqlCommand(updateQuery.ToString()))
        {
            commandSql.Parameters.AddWithValue("@CustomerId", custId);
            commandSql.Parameters.AddWithValue("@FirstName", firstName);
            commandSql.Parameters.AddWithValue("@LastName", lastName);
            commandSql.Parameters.AddWithValue("@Synonym", synonym);
            rowsAffected = DataAccessLayer.Execute(commandSql);
        }
        if (rowsAffected > 0)
        {
            isUpdated = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    return isUpdated;
}

private bool Delete(int custId)
{
    bool isDeleted = false;
    try
    {
        StringBuilder deleteQuery = new StringBuilder();
        deleteQuery.Append("DELETE FROM Customer ");
        deleteQuery.Append("WHERE ");
        deleteQuery.Append("CustomerId = @CustomerId;");
        SqlCommand commandSql = new SqlCommand(deleteQuery.ToString());
        commandSql.Parameters.AddWithValue("@CustomerId", custId);
        int rowsAffected = DataAccessLayer.Execute(commandSql);

        if (rowsAffected > 0)
        {
            isDeleted = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
        isDeleted = true;
    }
    return isDeleted;
}

Code Snippet 2:

//DAL

class DataAccessLayer
{
    private string ConnectionString
    {
        get
        {
            ConnectionStringSettingsCollection connectionStringSettings = ConfigurationManager.ConnectionStrings;
            return connectionStringSettings["ConnectionString"].ConnectionString;
        }
    }

    public DataTable Select(SqlCommand commandSql)
    {
        DataTable datTable = null;
        try
        {
            using (SqlConnection connectionSql = new SqlConnection(ConnectionString))
            {
                commandSql.Connection = connectionSql;
                commandSql.Connection.Open();
                datTable = new DataTable();
                datTable.Load(commandSql.ExecuteReader());
                return datTable;
            }               
        }
        finally
        {
            datTable = null;
        }
    }

    public int Execute(SqlCommand commandSql)
    {
        int rowsAffected = 0;
        using (SqlConnection connectionSql = new SqlConnection(ConnectionString))
        {
            commandSql.Connection = connectionSql;
            commandSql.Connection.Open();
            rowsAffected = commandSql.ExecuteNonQuery();           
        }
        return rowsAffected;
    }
}

//GUI using class DataAccessLayer:

private DataTable SelectRecord()
{
    DataTable datTable = null;
    try
    {
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("Select * FROM Customer;");
        using (SqlCommand commandSql = new SqlCommand(selectQuery.ToString()))
        {
            DataAccessLayer dal = new DataAccessLayer();
            datTable = dal.Select(commandSql);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());                
    }
    return datTable;
}

private DataTable SelectRecord(string column)
{
    DataTable datTable = null;
    try
    {
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("Select * FROM Customer ");
        if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("CustomerId = @CustomerId;");
        }
        else if (!string.IsNullOrEmpty(textBox2.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("FirstName = @FirstName;");
        }
        else if (!string.IsNullOrEmpty(textBox3.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("LastName = @LastName;");
        }
        else if (!string.IsNullOrEmpty(textBox4.Text.Trim()))
        {
            selectQuery.Append("WHERE ");
            selectQuery.Append("Synonym = @Synonym;");
        }
        using (SqlCommand commandSql = new SqlCommand(selectQuery.ToString()))
        {
            if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@CustomerId", Convert.ToInt32(textBox1.Text.Trim()));
            }
            else if (!string.IsNullOrEmpty(textBox2.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@FirstName", textBox2.Text.Trim());
            }
            else if (!string.IsNullOrEmpty(textBox3.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@LastName", textBox3.Text.Trim());
            }
            else if (!string.IsNullOrEmpty(textBox4.Text.Trim()))
            {
                commandSql.Parameters.AddWithValue("@Synonym", textBox4.Text.Trim());
            }
            DataAccessLayer dal = new DataAccessLayer();
            datTable = dal.Select(commandSql);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    return datTable;
}

private bool Insert(string firstName, string lastName, string synonym)
{
    bool isInserted = false;
    try
    {
        int rowsAffected = 0;
        StringBuilder insertQuery = new StringBuilder();
        insertQuery.Append("INSERT INTO Customer ");
        insertQuery.Append("(");
        insertQuery.Append("FirstName, ");
        insertQuery.Append("LastName, ");
        insertQuery.Append("Synonym ");
        insertQuery.Append(") ");
        insertQuery.Append("VALUES ");
        insertQuery.Append("(");
        insertQuery.Append("@FirstName, ");
        insertQuery.Append("@LastName, ");
        insertQuery.Append("@Synonym ");
        insertQuery.Append(");");
        using (SqlCommand commandSql = new SqlCommand(insertQuery.ToString()))
        {
            commandSql.Parameters.AddWithValue("@FirstName", firstName);
            commandSql.Parameters.AddWithValue("@LastName", lastName);
            commandSql.Parameters.AddWithValue("@Synonym", synonym);
            DataAccessLayer dal = new DataAccessLayer();
            rowsAffected = dal.Execute(commandSql);
        }
        if (rowsAffected > 0)
        {
            isInserted = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    return isInserted;
}

private bool Update(int custId, string firstName, string lastName, string synonym)
{
    bool isUpdated = false;
    try
    {
        int rowsAffected = 0;
        StringBuilder updateQuery = new StringBuilder();
        updateQuery.Append("UPDATE Customer ");
        updateQuery.Append("SET ");
        updateQuery.Append("FirstName = @FirstName, ");
        updateQuery.Append("LastName = @LastName, ");
        updateQuery.Append("Synonym = @Synonym ");
        updateQuery.Append("WHERE ");
        updateQuery.Append("CustomerId = @CustomerId;");
        using (SqlCommand commandSql = new SqlCommand(updateQuery.ToString()))
        {
            commandSql.Parameters.AddWithValue("@CustomerId", custId);
            commandSql.Parameters.AddWithValue("@FirstName", firstName);
            commandSql.Parameters.AddWithValue("@LastName", lastName);
            commandSql.Parameters.AddWithValue("@Synonym", synonym);
            DataAccessLayer dal = new DataAccessLayer();
            rowsAffected = dal.Execute(commandSql);
        }
        if (rowsAffected > 0)
        {
            isUpdated = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    return isUpdated;
}

private bool Delete(int custId)
{
    bool isDeleted = false;
    try
    {
        StringBuilder deleteQuery = new StringBuilder();
        deleteQuery.Append("DELETE FROM Customer ");
        deleteQuery.Append("WHERE ");
        deleteQuery.Append("CustomerId = @CustomerId;");
        SqlCommand commandSql = new SqlCommand(deleteQuery.ToString());
        commandSql.Parameters.AddWithValue("@CustomerId", custId);
        DataAccessLayer dal = new DataAccessLayer();
        int rowsAffected = dal.Execute(commandSql);

        if (rowsAffected > 0)
        {
            isDeleted = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
        isDeleted = true;
    }
    return isDeleted;
}

Thanks