Не добавляются значения к параметра в запросе SQL

191

10 мая 2019, 01:40

Не понимаю в чём выражается некорректность кода. Не добавляются значения к параметрам в запросе SQL к БД Oracle

public static string Ora(string surname, string name, string secname, string birthday)
        {
            using (OracleConnection connection = new OracleConnection(/**/)) 
            {
                using (OracleCommand command = new OracleCommand("select distinct nvl2(ID,ID,'Нет в БД') from KSL" +
                    "where surname = UPPER(trim(:surname_p))  " +
                    "and name = UPPER(trim(:name_p)) " +
                    "and secname = UPPER(trim(:secname_p)) " +
                    "and birthday = to_char(:dr_p,'DD.MM.YYYY')", connection))
                {
                    connection.Open();

                    OracleParameter OraSurname = new OracleParameter(":surname_p", OracleDbType.Varchar2);
                    OracleParameter OraName = new OracleParameter(":name_p", OracleDbType.Varchar2);
                    OracleParameter OraSecname = new OracleParameter(":secname_p", OracleDbType.Varchar2);
                    OracleParameter OraDR = new OracleParameter(":dr_p", OracleDbType.Char);
                    OraSurname.Value = Sl(surname.ToString());
                    OraName.Value = Sl(name.ToString());
                    OraSecname.Value = Sl(secname.ToString());
                    OraDR.Value = Sl(birthday.ToString());
                    command.Parameters.Add(OraSurname);
                    command.Parameters.Add(OraName);
                    command.Parameters.Add(OraSecname);
                    command.Parameters.Add(OraDR);
                    Debug.WriteLine(OraSurname + " " + OraName);
                    OracleDataReader reader;
                    reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            var result = reader.ToString();
                            reader.Close();
                            connection.Close();
                            Debug.WriteLine(reader.ToString());
                            return (result);
                        }
                        return("");
                    }
                    else
                    {
                        reader.Close();
                        connection.Close();
                        return ("Нет в БД");
                    }
                }
            }

Answer 1

попробуйте так:

    public static string Ora(string surname, string name, string secname, string birthday)
    {
        using (var connection = new OracleConnection(/**/))
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "select distinct nvl2(ID,ID,'Нет в БД') from KSL " +
                        "where surname = UPPER(trim(:surname_p))  " +
                        "and name = UPPER(trim(:name_p)) " +
                        "and secname = UPPER(trim(:secname_p)) " +
                        "and birthday = to_char(:dr_p,'DD.MM.YYYY')";
                connection.Open();
                command.Parameters.Add(":surname_p", OracleDbType.Varchar2, surname.ToUpper(), ParameterDirection.Input);
                command.Parameters.Add(":name_p", OracleDbType.Varchar2, name.ToUpper(), ParameterDirection.Input);
                command.Parameters.Add(":secname_p", OracleDbType.Varchar2, secname.ToUpper(), ParameterDirection.Input);
                command.Parameters.Add(":dr_p", OracleDbType.Char, birthday.ToUpper(), ParameterDirection.Input);
                Debug.WriteLine($"{surname} {name}");
                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            var result = reader.ToString();
                            Debug.WriteLine(reader.ToString());
                            return result;
                        }
                        return "";
                    }
                    else return "Нет в БД";
                }
            }  
        }
    }