Hello,
my question is rather connected with some aspects of ADO.NET but I've
been trying to ask on .dotnet.framework.adonet with no result. I hope
someone here can help me.
The problem is here: some stored procedures raise errors and return results
in the same time. One of such sps is sp_droplogin [but it is fairlty easy to
even write such procedure, so the problem is more general].
I wish to intercept the exception and show the conflicted databases
[look at the snippet below, the sp_droplogin returns the recordset of
conflicting databases, you can try it in the Query Analyzer]. This does not
work in my approach: the thrown exception clears any results returned from
the database. I have no idea if this is the fault of ADO.NET or just my
ignorance.
The question is: how this can be achevied [EnterpriseManager does that
for example] in C#+ADO.NET? as far as I can remember this worked in old
vb6+ado because the errors raised from the stored procedures were not
reported as exceptions. I've even found a kb article on that (Q245179) but
still have no idea how to work this out in ado.net.
could anyone share his/her experience with me and englighten me a little
bit?
thanks in advance,
Wiktor Zychla
// code snippet
private void someMethod()
{
SqlConnection c = null;
SqlDataReader r = null;
try
{
c = new SqlConnection( ... );
c.Open();
SqlCommand cmd = new SqlCommand( "sp_droplogin", c );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( "@loginame", ... );
r = cmd.ExecuteReader(); // should return the conflicted databases
}
catch ( SqlException ex )
{
// cannot look at the result set because it has been cleared out!!
while ( r.Read() ) { ... // wrong! but how to read the result? }
MessageBox.Show( ex.Message );
}
finally
{
if ( r != null ) r.Close();
if ( c != null && c.State == ConnectionState.Open ) c.Close();
}
}
