Web Development

        public bool isTamAsp(int aspKey, int fy, string accountCode)
        {
            MyParam myParam;

            string sqlQuery = "select isTamMacom = count(macom_key) FROM hier_fy " +
                "WHERE hier_key = @aspKey AND fy = <a href="http://www.codeproject.com/Members/fy">@fy</a>  AND @accountCode NOT IN (3,4,7,8) AND macom_key IN (select hier_key from lkup_e581_MacomThatRequireTAM) AND is_visible = 1 AND is_active = 1";

            QueryContainer Instance = new QueryContainer(sqlQuery);

            myParam = new MyParam();

            myParam.SqlParam = new SqlParameter("@aspKey", Instance.AddParameterType(_DbTypes.Int));

            myParam.SqlParam.Value = aspKey;

            Instance.parameterList.Add(myParam);

            myParam = new MyParam();

            myParam.SqlParam = new SqlParameter("@fy", Instance.AddParameterType(_DbTypes.Int));

            myParam.SqlParam.Value = fy;

            Instance.parameterList.Add(myParam);

            myParam = new MyParam();

            myParam.SqlParam = new SqlParameter("@accountCode", Instance.AddParameterType(_DbTypes._string));

            myParam.SqlParam.Value = accountCode;

            Instance.parameterList.Add(myParam);

            if (Convert.ToInt32(ExecuteScaler(Instance)) < 1)
                return false;

            return true;
        }
<pre>

Calling code with no parameters:


<pre>

Calling code with no parameters:

<pre>

        public long GetMarinesUploadNextUploadKey()
        {
            string query = "SELECT MAX(upload_key) FROM temp_auth_usmc_upload";

            QueryContainer Instance = new QueryContainer(query);

            string result = Convert.ToString(ExecuteScaler(Instance));
            if (string.IsNullOrEmpty(result))
                return 1;
            else
                return Convert.ToInt64(result) + 1;
        } 

<pre>

Code calling my previous code with three parameters:

<pre>

        public bool isTamAsp(int aspKey, int fy, string accountCode)
        {
            return e581provider.isTamAsp(aspKey, fy, accountCode);
        }
<pre>

Method calling the SQL executing my code:


<pre>

                DbCommand command = _provider.CreateCommand();

                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;

                    if (Instance.parameterList.Count > 0)
                    {
                        foreach (var p in Instance.parameterList)
                        {
                            command.Parameters.Add(p.SqlParam);
                        }
                    }

                    if (_useTransaction) { command.Transaction = _transaction; }

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }

<pre>

My Class containing the SQL string and the cmd parameter List

<pre>

    public enum _DbTypes
    {
        Int = 1, _string = 2, _long = 3, _bool = 4, _DateTime = 5,
        _decimal = 6, _float = 7, _short = 8, _bite = 9
    } 

    public class MyParam
    {
        public SqlParameter SqlParam { get; set; }
    }
    /// <summary>
    /// Summary description for QueryContainer SGH
    /// </summary>
    public class QueryContainer
    {

        string _query;

        public List<myparam> parameterList = new List<myparam>();

        public QueryContainer(string query) { _query = query; }

        public SqlDbType AddParameterType(_DbTypes id)
        {
            switch (id)
            {
                case _DbTypes.Int:
                    return (SqlDbType)Enum.Parse(typeof(SqlDbType), "int", true);
                case _DbTypes._string:
                    return (SqlDbType)Enum.Parse(typeof(SqlDbType), "NVarChar", true);
                case _DbTypes._long:
                    return (SqlDbType)Enum.Parse(typeof(SqlDbType), "SqlDbType.BigInt", true);
                case _DbTypes._bool:
                    return (SqlDbType)Enum.Parse(typeof(SqlDbType), "SqlDbType.Bit", true);
            }

            return SqlDbType.VarChar;

        }

        public string Query
        {
            get
            {
                return _query;
            }

            set { _query = value; }
        }
    }
<pre>

protected DataTable ExecuteDataTable(DbCommand command, ParamData[] pDataArr)
{
    DataTable returnValue = null;
    try
    {

        if (_connection == null)
            OpenConnection();
        else
        {
            if (_connection.State == ConnectionState.Closed)
                OpenConnection();
        }

        command.Connection = _connection;
        command.CommandType = CommandType.Text;
        command.CommandTimeout = 12000;

        //add Parameter
        for (int i = 0; i < pDataArr.Length; i++)
        {
            DbParameter parameter = command.CreateParameter();
            parameter.ParameterName = pDataArr[i].pName;
            parameter.DbType = pDataArr[i].pDataType;
            parameter.Value = pDataArr[i].pValue;
            command.Parameters.Add(parameter);
        }

        // Create a DataTable
        returnValue = new DataTable();

        DbDataReader reader;
        reader = command.ExecuteReader();

        using (reader)
        {
            // Fill DataTable
            returnValue.Load(reader, LoadOption.OverwriteChanges);
        }

        reader.Close();

        if (!KeepAlive && _connection.State == ConnectionState.Open)
        {
            CloseConnection();
        }
    }
    catch (Exception e)
    {
        if (e is EntryPointNotFoundException)
            throw e;
        _iserror = true;
        LogBLL bll = new LogBLL();
        bll.WriteErrorLog(e);
    }

    pDataArr = null;

    return returnValue;
}