ASP.NET

holdorf wrote:

My boss is upset and is giving me only a few days to fix this.

holdorf wrote:

Remember I have only one function to fix and handle this problem.

holdorf wrote:

Can someone please give me a small piece of code of how this should be implemented?

public class MyParam
    {
        public string name { get; set; }
        public string value { 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 string Query
        {
            get
            {
                return _query;
            }

            set { _query = value;  }
        }
    }

The calling code:

<pre>
        public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http://www.codeproject.com/Members/account">@account</a>");

            MyParam myParam = new MyParam();

            myParam.name = "@account";
            myParam.value = account.ToString();

            Instance.parameterList.Add(myParam);

            return Convert.ToInt32(ExecuteScaler(Instance, 1));
        } 
<pre>

The implementing code:

<pre>
                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    OpenConnection();
                }

                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;

                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }

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

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();

                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);

                        _iserror = true;
                    }

<pre>

        protected Object ExecuteScaler(QueryContainer Instance, int i)
        {

            object returnValue = null;


            if (!_iserror)
            {
                if (_trace)
                {
                    DoTrace("TAMIS.Data.Loader.ExecuteScalar", Instance.Query);
                }

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

                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;

                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }

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

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();

                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);

                        _iserror = true;
                    }
<pre>

public int GetAccountSortByAccountCode(int account)
{

    int pValue = 0;

    string connStr = ConfigurationManager.ConnectionString("DefaultConnection").ConnectionString;
    SqlConnection conn = new SqlConnection(connStr);

    string query =
    "SELECT "  +
    " ac_sort_order "  +
    " FROM lkup_account_codes " +
    " WHERE ac_code = @account";

    SqlCommand  cmd = new SqlCommand(query, conn);

    SqlParameter param_account = new SqlParameter("@account", SqlDbType.Int);
    param_account .Value = account;
    cmd.Parameters.Add(param_account);

    try
    {
        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            pValue = reader.GetInt32(0);
        }

        reader.Close();
    }
    catch (Exception ex)
    {

    }
    finally
    {
        conn.Close();
    }

    return pValue;
}

Because you really wanted a code review:

public int GetAccountSortByAccountCode(int account)
{
    string connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    // You should be able to make every SQL query a "const";
    // otherwise, you've probably missed a SQL injection vulnerability:
    const string query = "SELECT TOP 1 ac_sort_order FROM lkup_account_codes WHERE ac_code = @account";

    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand(query, conn))
    {
        cmd.Parameters.AddWithValue("@account", account);

        conn.Open();
        object commandResult = command.ExecuteScalar();

        return (commandResult == null || Convert.IsDBNull(commandResult))
            ? 0
            : Convert.ToInt32(commandResult);
    }
}

Public Shared Function check_4_Duplicate_AccountName( _
        ByVal p_Test_AccountName As String) As Boolean

        Dim pCount As Integer = 0
        Dim dV As Boolean = True

        Dim connStr As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim query As String = _
        " SELECT " & _
        " COUNT(AccountName) " & _
        " FROM CUSTOMER_ACCOUNTS " & _
        " WHERE AccountName = @Test_AccountName"

        Dim conn As New SqlConnection(connStr)
        Dim cmd As New SqlCommand(query, conn)

        Dim param_Test_AccountName As SqlParameter
        param_Test_AccountName = New SqlParameter("@Test_AccountName", SqlDbType.VarChar, 80)
        param_Test_AccountName.Value = p_Test_AccountName
        cmd.Parameters.Add(param_Test_AccountName)

        Try
            conn.Open()
            Dim myReader As SqlDataReader = cmd.ExecuteReader()
            While myReader.Read()
                If Not myReader.IsDBNull(0) Then
                    pCount = myReader.GetInt32(0)
                End If
            End While

            myReader.Close()
            myReader = Nothing

            If (pCount = 0) Then
                dV = False
            End If

        Catch ex As Exception

        Finally

            conn.Close()
            conn = Nothing
            cmd = Nothing

        End Try

        Return dV

    End Function

Public Shared Function check_4_Duplicate_AccountName_II( _
    ByVal p_Test_AccountName As String) As Boolean

    Dim pCount As Integer = 0
    Dim dV As Boolean = True

    Dim connStr As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
    Const query As String = _
    " SELECT " & _
    " COUNT(AccountName) " & _
    " FROM CUSTOMER_ACCOUNTS " & _
    " WHERE AccountName = @Test_AccountName"

    Using conn As New SqlConnection(connStr)
        Using cmd As New SqlCommand(query, conn)

            Dim param_Test_AccountName As SqlParameter
            param_Test_AccountName = New SqlParameter("@Test_AccountName", SqlDbType.VarChar, 80)
            param_Test_AccountName.Value = p_Test_AccountName
            cmd.Parameters.Add(param_Test_AccountName)

            Try
                conn.Open()
                Dim cmdResult As Object = cmd.ExecuteScalar()
                dV = If(cmdResult Is Nothing Or Convert.IsDBNull(cmdResult), False, True)

            Catch ex As Exception

            Finally

                conn.Close()

            End Try

        End Using
    End Using

    Return dV

End Function

namespace TAMIS.Business.Common
{
    public class MyParam
    {
        public SqlParameter SqlParam { get; set; }

        public string Name { get; set; }

        public object Value { get; set; }

        public int Type { 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(int type)
        {
            switch (type)
            {
                case 1:
                    return  (SqlDbType) Enum.Parse( typeof(SqlDbType) , "int" , true );
                case 2:
                    return (SqlDbType)Enum.Parse(typeof(SqlDbType), "NVarChar", true);
            }

            return SqlDbType.VarChar;
                
        }

        public string Query
        {
            get
            {
                return _query;
            }

            set { _query = value;  }
        }
    }

public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http://www.codeproject.com/Members/account">@account</a>");

            MyParam myParam = new MyParam();

            myParam.SqlParam = new SqlParameter("@account", Instance.AddParameterType(1));

            myParam.SqlParam.Value = account;
            
            Instance.parameterList.Add(myParam);
            
            return Convert.ToInt32(ExecuteScaler(Instance, 1));

protected Object ExecuteScaler(QueryContainer Instance, int i)
        {

            object returnValue = null;


            if (!_iserror)
            {
                if (_trace)
                {
                   // DoTrace("TAMIS.Data.Loader.ExecuteScalar", Instance.Query);
                }

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

                DbCommand command = _provider.CreateCommand();

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

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

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

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();

                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);

                        _iserror = true;
                    }
                    finally
                    {

                        if ((!KeepAlive && _connection.State == ConnectionState.Open) || _iserror == true)
                        {
                            CloseConnection();
                        }

                    }
                }
            }
            else
            {
                returnValue = -1;
            }

            return returnValue;
        }

<pre>