如何使用asp.net c#根据用户输入查询oracle数据库

当我尝试根据用户输入查询 Oracle 数据库并填写数据集以以 xml 格式显示时，我遇到了问题。 我在这里写了我的代码：-

using System.Data.OracleClient;

[WebMethod]
public string CallCardDetails(string CallCardNo)
{
     //initialize
     DataSet ds = new DataSet();

     //connect
     using (OracleConnection conn = new OracleConnection("User Id=oraDB;Password=ora;Data Source=CCT"))
     {
            <<(1st method)>>
            string query = "SELECT idcard from CallCardTable where idcard= :pCallCardNo";                

            <<(2nd method)>>
            string query = "SELECT idcard from CallCardTable where idcard=@CallCardNo";

            OracleCommand cmd = new OracleCommand(query, conn);

            cmd.CommandType = CommandType.Text;
            conn.Open();

            <<(1st method parameter -)>>
            OracleParameter pCallCardNo = new OracleParameter();
            pCallCardNo.Value = CallCardNo;
            cmd.Parameters.Add(pCallCardNo);

            <<(2nd method parameter - )>>
            cmd.Parameters.Add("@CallCardNo", OracleType.VarChar).Value = CallCardNo;

            OracleDataAdapter dA = new OracleDataAdapter(query, conn);
            dA.Fill(ds);

            cmd.Dispose();
            cmd.Parameters.Clear();
            dA.Dispose();
            conn.Close();
        }
        return ds.GetXml();

 }


<<1st exception>>
System.Data.OracleClient.OracleException: ORA-01008: not all variables bound

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

<<2nd exception>>
System.Data.OracleClient.OracleException: ORA-00936: missing expression

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

> System.Data.OracleClient.OracleException: ORA-01036: 非法变量名/编号

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

这应该有效：

    public string CallCardDetails(string CallCardNo)
    {
        //initialize
        using (DataSet ds = new DataSet())
        {
            //connect
            using (OracleConnection conn = new OracleConnection("User Id=oraDB;Password=ora;Data Source=CCT"))
            {
                // Oracle uses : for parameters, not @
                string query = "SELECT idcard from CallCardTable where idcard= :pCallCardNo";

                // Let the using block dispose of your OracleCommand
                using (OracleCommand cmd = new OracleCommand(query, conn))
                {
                    // Note: be careful with AddWithValue: if there's a mismatch between the .NET datatype of
                    // CallCardNo and the idcard column you could have an issue.  Cast the value you provide
                    // here to whatever is closest to your database type (String for VARCHAR2, DateTime for DATE, Decimal for NUMBER, etc.)
                    cmd.Parameters.AddWithValue(":pCallCardNo", CallCardNo);
                    conn.Open();

                    // Again, wrap disposables in a using or use try/catch/finally (using will dispose of things in case of exceptions too)
                    using (OracleDataAdapter dA = new OracleDataAdapter(cmd))
                    {
                        dA.Fill(ds);

                        return ds.GetXml();
                    }
                }
            }
        }
    }

编辑：在数据集周围添加使用块。