CODEWITHSUNDEEP
c#sql-server
Huck
Huck

Reputation: 977

How to retrieve error when launching sqlcmd from C#?

I need to run a stored procedure from a C# application.

I use the following code to do so:

Process sqlcmdCall = new Process();
sqlcmdCall.StartInfo.FileName = "sqlcmd.exe";
sqlcmdCall.StartInfo.Arguments = "-S localhost\\SQLEXPRESS -d some_db -Q \":EXIT(sp_test)\""
sqlcmdCall.Start();
sqlcmdCall.WaitForExit();

From the sqlcmdCall object after the call completes, I currently get an ExitCode of -100 for success and of 1 for failure (i.e. missing parameter, stored proc does not exist, etc...).

How can I customize these return codes?

H.

Upvotes: 2

Views: 3323

Answers (3)

Matt
Matt

Reputation:

If you are trying to call a stored procedure from c# you would want to use ADO.Net instead of the calling sqlcmd via the command line. Look at SqlConnection and SqlCommand in the System.Data.SqlClient namespace.

Once you are calling the stored procedure via SqlCommand you will be able to catch an exception raised by the stored procedure as well we reading the return value of the procedure if you need to.

Upvotes: 1

Mitchel Sellers
Mitchel Sellers

Reputation: 63126

Even with windows authentication you can still use SqlCommand and SqlConnection to execute, and you don't have to re-invent the wheel for exception handling.

A simple connection configuration and a single SqlCommand can execute it without issue.

Upvotes: 0

Ron Savage
Ron Savage

Reputation: 11079

I have a small VB.Net app that executes system commands like that. To capture error or success conditions I define regular expressions to match the error text output from the command and I capture the output like this:

        myprocess.Start()
        procReader = myprocess.StandardOutput()

        While (Not procReader.EndOfStream)
           procLine = procReader.ReadLine()

           If (MatchesRegEx(errRegEx, procLine)) Then
              writeDebug("Error reg ex: [" + errorRegEx + "] has matched: [" + procLine + "] setting hasError to true.")

              Me.hasError = True
           End If

           writeLog(procLine)
        End While

        procReader.Close()

        myprocess.WaitForExit(CInt(waitTime))

That way I can capture specific errors and also log all the output from the command in case I run across an unexpected error.

Upvotes: 2

Related Questions