handling external exceptions in ADO.NET

[Wiktor Zychla]

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();
}
}

 

[Dmitriy Lapshin [C# / .NET MVP]]

Wiktor,

My colleague has just suggested there is a dedicated SP that just returns
the list of databases used by a login. You can employ this SP first to check
for conflicts and then safely drop the login.

 

[Wiktor Zychla]

My colleague has just suggested there is a dedicated SP that just returns

the list of databases used by a login. You can employ this SP first to check
for conflicts and then safely drop the login.

Thanks Dmitriy. I know that.

For me its more general problem - how to retrieve the results from the
command that raises exception and returns the result.

If you run the sp_droplogin in QueryAnalyzer, you can see the exception
message and the resultset (on the separate grid). I do not think the QA is
smart enough to know that there is another stored procedure to be run before
sp_droplogin.

We were trying to handle this case properly using 3 different development
environments: C#+ado.net, delphi 6+ado and vb6+ado. none of them succeded
(the exception is always thrown) so I really wonder how QA handles this one.

Wiktor

 

[Dmitriy Lapshin [C# / .NET MVP]]

QA probably uses a low-level API which deals with error codes but not
exceptions. I guess not every error assumes that the returned results should
be discarded, but ADO / ADO .NET seem not to distinguish between various
errors and discard the results in case of any error.