Start New Discussion within our Software Development Community

If you need to execute SQL Statements\Stored Procedures\UDFs which return\don't return results, Here I've developed this class to be used in Data Access Layer.

//This code developed by Ramy Mahrous 
//ramyamahrous@hotmail.com
//Its contents is provided "as is", without warranty.


/// <summary>
/// Acts as Data Access Layer for SQL Server
/// </summary>
public class DataAccessLayer
{
private static string SqlConnectionString = "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DummyDatabase;Data Source=.;Connect Timeout=200;";
//your connection string I place mine for illustration.. DON'T HARDLY WRITE IT, pass it as argument or add it in application configuration 
        /// <summary>
        /// Replaces every parameter with its value from 2D array
        /// </summary>
        /// <param name="query">Query</param>
        /// <param name="parameters">Query parameters</param>
        /// <returns>Query with parameters value to be executed against SQL Server Database</returns>
        private static string SetParametersValue(string query, string[,] parameters)
        {
            for (int i = 0; i < parameters.Length / 2; i++)
            {
                if (!string.IsNullOrEmpty(parameters[i, 0]))
                    query = query.Replace(parameters[i, 0], "'" + parameters[i, 1] + "'");
            }
            return query;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sp, CommandType commandType)
        {
            SqlConnection con = new SqlConnection(SqlConnectionString);
            SqlCommand com = new SqlCommand(sp, con);
            object result = null;

            com.CommandType = commandType;

            try
            {
                con.Open();
                result = com.ExecuteScalar();
                con.Close();
            }
            catch (System.Exception ex)
            {
                //log the exception
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sp"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sp, string[,] parameters, CommandType commandType)
        {
            SqlConnection con = new SqlConnection(SqlConnectionString);
            SqlCommand com = new SqlCommand(SetParametersValue(sp, parameters), con);
            object result = null;

            
            com.CommandType = commandType;

            for (int i = 0; i < parameters.Length / 2; i++)
            {
                com.Parameters.AddWithValue(parameters[i, 0], parameters[i, 1]);
            }

            try
            {
                con.Open();
                result = com.ExecuteScalar();
                con.Close();
            }
            catch (System.Exception ex)
            {
                //log the exception
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteQuery(string sp, CommandType commandType)
        {
            SqlConnection con = new SqlConnection(SqlConnectionString);
            SqlCommand com = new SqlCommand(sp, con);
            SqlDataReader reader = null;
            
            com.CommandType = commandType;

            try
            {
                con.Open();
                reader = com.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Exception ex)
            {
                //log the exception
            }
            return reader;
        }

        /// <summary>
        /// FOR SQL
        /// </summary>
        /// <param name="sp"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteQuery(string sp, string[,] parameters, CommandType commandType)
        {
            SqlConnection con = new SqlConnection(SqlConnectionString);
            SqlCommand com = new SqlCommand(SetParametersValue(sp, parameters), con);
            SqlDataReader reader = null;

            
            com.CommandType = commandType;

            for (int i = 0; i < parameters.Length / 2; i++)
            {
                com.Parameters.AddWithValue(parameters[i, 0], parameters[i, 1]);
            }

            try
            {
                con.Open();
                reader = com.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Exception ex)
            {
                //log the exception
            }
            return reader;
        }

        /// <summary>
        /// FOR SQL
        /// </summary>
        /// <param name="sp"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static void ExecuteNonQuery(string sp, CommandType commandType)
        {
            SqlConnection con = new SqlConnection(SqlConnectionString);
            SqlCommand com = new SqlCommand(sp, con);

            
            com.CommandType = CommandType.StoredProcedure;

            try
            {
                con.Open();
                com.ExecuteNonQuery();
                con.Close();
            }
            catch (System.Exception ex)
            {
                //log the exception
            }
        }

        /// <summary>
        /// FOR SQL
        /// </summary>
        /// <param name="sp"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static void ExecuteNonQuery(string sp, string[,] parameters, CommandType commandType)
        {
            SqlConnection con = new SqlConnection(SqlConnectionString);
            SqlCommand com = new SqlCommand(SetParametersValue(sp, parameters), con);

            
            com.CommandType = commandType;

            for (int i = 0; i < parameters.Length / 2; i++)
            {
                com.Parameters.AddWithValue(parameters[i, 0], parameters[i, 1]);
            }

            try
            {
                con.Open();
                com.ExecuteNonQuery();
                con.Close();
            }
            catch (System.Exception ex)
            {
                //log the exception
            }
        }
    }
The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.