Difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

• ExecuteScalar is going to be the type of query which will be returning a single value. An example might be SELECT @@IDENTITY AS 'Identity'. 

• ExecuteReader gives you a data reader back which will allow you to read all of the columns of the results a row at a time. A(e.g., SELECT col1, col2 from sometable). 

• ExecuteNonQuery is any SQL which isn't returning values really, but is actually performing some form of work like inserting deleting or modifying something. (e.g., UPDATE, INSERT, etc.).

DataAccessLayer
public class MsSqlQueryParameters
    {
        private SqlParameter SQLParam;
        private ArrayList ParamArray;

        public MsSqlQueryParameters()
        {
            ParamArray = new ArrayList();
        }

        public void Add(string ParamName, SqlDbType ParamType, object ParamValue)
        {

            SQLParam = new System.Data.SqlClient.SqlParameter();
            SQLParam.SqlDbType = ParamType;
            SQLParam.ParameterName = ParamName;
            SQLParam.Value = ParamValue;
            ParamArray.Add(SQLParam);
        }

        public void Add(string ParamName, SqlDbType ParamType, object ParamValue, int ParamLength)
        {

            SQLParam = new System.Data.SqlClient.SqlParameter();
            SQLParam.SqlDbType = ParamType;
            SQLParam.ParameterName = ParamName;
            SQLParam.Value = ParamValue;
            SQLParam.Size = ParamLength;
            ParamArray.Add(SQLParam);
        }

        public void ClearAll()
        {
            try
            {
                ParamArray.Clear();
            }
            catch
            {

            }
        }

        public ArrayList QueryParam
        {
            get
            {
                return ParamArray;
            }
        }
    }

public DataSet GetDataSet(string SQLQuery, CommandType Type, MsSqlQueryParameters Parameters)
        {
            if (dbCon.State == ConnectionState.Closed)
                dbCon.Open();
            command = new SqlCommand(SQLQuery, dbCon);
            command.CommandType = Type;
            if (Parameters != null)
            {
                foreach (SqlParameter ParameterItem in Parameters.QueryParam)
                {
                    command.Parameters.Add(ParameterItem);
                }
            }
            dSet = new DataSet();
            dataAdapter = new SqlDataAdapter(command);
            if (maxrecords > 0)
                dataAdapter.Fill(dSet);
            else
                dataAdapter.Fill(dSet, tblName);

            //object AffCnt = null;
            //command.CommandTimeout = 120;
            //AffCnt = command.ExecuteScalar();
            ////AffCnt = command.ExecuteNonQuery();

            dataAdapter.Dispose();
            command.Dispose();
            dbCon.Close();
            return dSet;
        }

BusinessAccessLayer

private MsSqlDataProxy dataLayer = null;

private MsSqlQueryParameters parameters = null;

public DataSet GetAllAdmission()
        {
            parameters.ClearAll();
            parameters.Add("@method", SqlDbType.NVarChar, BLConstants.Query_GetALL, 25);
            parameters.Add("@admissionId", SqlDbType.BigInt, 0);
            dataSet = dataLayer.GetDataSet("SPGetAdmission", "TblAdmission", CommandType.StoredProcedure, parameters);
            return dataSet;

        }

private Admission fillAdmission(DataRow dr)
        {
            Admission admissionFile = new Admission();
            if (dr != null)
            {
                if (dr["admissionId"] != DBNull.Value)
                    admissionFile.AdmissionId = (long)dr["admissionId"];
                if (dr["admissionType"] != DBNull.Value)
                    admissionFile.AdmissionType = (int)dr["admissionType"];
                if (dr["title"] != DBNull.Value)
                    admissionFile.Title = (string)dr["title"];
                if (dr["shortDescription"] != DBNull.Value)
                    admissionFile.ShortDescription = (string)dr["shortDescription"];
                if (dr["filename"] != DBNull.Value)
                    admissionFile.Filename = (string)dr["filename"];
                if (dr["createdDate"] != DBNull.Value)
                    admissionFile.CreatedDate = Convert.ToDateTime(dr["createdDate"]);
                if (dr["modifiedDate"] != DBNull.Value)
                    admissionFile.ModifiedDate = Convert.ToDateTime(dr["modifiedDate"]);
            }
            return admissionFile;

        }