OracleCommand 执行带参数据的SQL语句

来源：互联网发布：cad 2016 mac 汉化编辑：程序博客网时间：2024/05/21 19:36

刚才执行带参数据的SQL语句时报“ORA-01036: 非法的变量名/编号“错误。

错误代码如下：

    public static void ExecuteParameterSQL(string asName)
    {
        sConnectionString = ConfigurationManager.AppSettings.Get("UserConnection");
        OracleConnection ocConnection = new OracleConnection(sConnectionString);
        ocConnection.Open();

        OracleCommand ocCommand = ocConnection.CreateCommand();
        ocCommand.CommandType = CommandType.Text;
        ocCommand.CommandText = @"select EMPLOYEEID,EMPLOYEENAME from ORGANIZATION_EMPLOYEE where EMPLOYEENAME=@loginName";

        OracleParameter opName = new OracleParameter();
        opName.ParameterName = "loginName";
        opName.OracleType = OracleType.VarChar;
        opName.Value = asName;
        ocCommand.Parameters.Add(opName);

        int i = (int)ocCommand.ExecuteNonQuery();
        ocConnection.Close();
    }

去网上查了一下，原来问题出在SQL语句中参数定义不对。SQL server中用“@”定义参数，而Oracle中用“：”。
将SQL语句修改如下，运行没有问题。注意代码中红色部分。

/// <summary>
    /// 执行参数
    /// </summary>
    /// <param name="asName"></param>
    public static void ExecuteParameterSQL(string asName)
    {
        sConnectionString = ConfigurationManager.AppSettings.Get("UserConnection");
        OracleConnection ocConnection = new OracleConnection(sConnectionString);
        ocConnection.Open();

        OracleCommand ocCommand = ocConnection.CreateCommand();
        ocCommand.CommandType = CommandType.Text;
        ocCommand.CommandText = @"select EMPLOYEEID,EMPLOYEENAME from ORGANIZATION_EMPLOYEE where EMPLOYEENAME=:loginName";

        OracleParameter opName = new OracleParameter();
        opName.ParameterName = "loginName";
        opName.OracleType = OracleType.VarChar;
        opName.Value = asName;
        ocCommand.Parameters.Add(opName);

        int i = (int)ocCommand.ExecuteNonQuery();
        ocConnection.Close();
    }：