取得OracleCommand SQL (parameters binding)

2014-08-09

397
0
.net

摘要:取得OracleCommand SQL (parameters binding)

用parameters binding來寫SQL可以避免SQL injection的問題，但是在debug上實在太麻煩了

每次都要將所有的變數自己填入SQL中，才能在PL/SQL developer等工具中，檢視SQL的結果

目前只想到寫一個方法來將SQL取出來，下面的方法能應付目前用到的SQL


    public static string GetOracleCommandText(OracleCommand cmd)

    {

        string commandTxt = cmd.CommandText;

        foreach (OracleParameter param in cmd.Parameters)

        {

            string val = String.Empty;

            switch (param.OracleDbType)

            {

                case OracleDbType.Date:

                    val = "TO_DATE('" + Convert.ToDateTime(param.Value).ToString("yyyy/MM/dd HH:mm:ss") + "', 'yyyy/mm/dd hh24:mi:ss')";

                    break;

                case OracleDbType.Decimal:

                case OracleDbType.Double:

                case OracleDbType.Int16:

                case OracleDbType.Int32:

                case OracleDbType.Int64:

                case OracleDbType.Long:

                case OracleDbType.Single:

                    val = param.Value.ToString();

                    break;

                case OracleDbType.Varchar2:

                default:

                    val = "'" + param.Value.ToString() + "'";

                    break;

            }



            string pat = string.Format(@"(:{0})([,|\s]+?)", param.ParameterName);

            Regex regex = new Regex(pat, RegexOptions.IgnoreCase);

            commandTxt = regex.Replace(commandTxt, val + "${2}");

        }

        return commandTxt;

    }

回首頁