Sybase ase ado. net output parameters

I don't know how many people use Sybase for database development. With Sybase ase ado. net, the system is running normally after a lot of resistance is broken through. Further efforts should be made to increase data permissions. We used to judge using the result set directly returned by the function on SQL Server. However, Sybase does not support functions, so we have to use the stored procedure to return a condition string for permission determination for dynamic execution. The problem is that the output parameter always prompts "command has been closed ". It's strange that the connection was opened during execution. Write a test Program :

Aseconnection con = new aseconnection ("Data Source = 'sybase '; Port = 5000; uid = 'sa'; Pwd =''; database = 'data '; connection timeout = '000000 ';");

Asecommand COM = new asecommand ("getdatarightsql", con );

Com. commandtype = system. Data. commandtype. storedprocedure;

Try

{

Aseparameter PRM = new aseparameter ("@ userid", 1 );

// PRM. Direction = system. Data. parameterdirection. input;

Com. Parameters. Add (PRM );

PRM = new aseparameter ("@ category", "Department ");

Com. Parameters. Add (PRM );

Com. Parameters. Add (New aseparameter ("@ fieldname", "dept_id "));

PRM = new aseparameter ("@ returnsql", asedbtype. varchar, 250 );

PRM. Direction = system. Data. parameterdirection. output;

Com. Parameters. Add (PRM );

Con. open ();

Com. executenonquery ();

Console. writeline (COM. Parameters ["@ returnsql"]. value );



}

Finally

{

If (con! = NULL & con. State = system. Data. connectionstate. open)

Con. Close ();

}

Return;

Ft. No problem. The returned results are displayed. The data is correct.

Because we have a data layer dedicated to database operations, we began to suspect that the encapsulation is not good. After checking for n times, I don't know where the error occurred. I thought there was a problem with the output parameter type. I tried several times and changed the length. No.

Later, I noticed that the trace execution was successful, and errors always occurred when taking the parameters. Let's take a look at the error message "command has been closed! The database connection is not required to be open when the parameter value is obtained, right?

Modify Test Code :

Aseconnection con = new aseconnection ("Data Source = 'sybase '; Port = 5000; uid = 'sa'; Pwd =''; database = 'data '; connection timeout = '000000 ';");

Asecommand COM = new asecommand ("getdatarightsql", con );

Com. commandtype = system. Data. commandtype. storedprocedure;

Try

{

Aseparameter PRM = new aseparameter ("@ userid", 1 );

// PRM. Direction = system. Data. parameterdirection. input;

Com. Parameters. Add (PRM );

PRM = new aseparameter ("@ category", "Department ");

Com. Parameters. Add (PRM );

Com. Parameters. Add (New aseparameter ("@ fieldname", "dept_id "));

PRM = new aseparameter ("@ returnsql", asedbtype. varchar, 250 );

PRM. Direction = system. Data. parameterdirection. output;

Com. Parameters. Add (PRM );

Con. open ();

Com. executenonquery ();

Con. Close (); // close the database in advance

Console. writeline (COM. Parameters ["@ returnsql"]. value );



}

Finally

{

If (con! = NULL & con. State = system. Data. connectionstate. open)

Con. Close ();

}

Return;

Haha, an error occurred.

Previously, when operating on SQL Server and Oracle, the connection was directly closed after execution. If you want to handle the returned parameters, there is no error. However, I didn't expect the database to operate on the parameters of the asecommand in Sybase ASE ADO. net.