Click here to monitor SSC
  • Av rating:
  • Total votes: 63
  • Total comments: 7
William Brewer

Calling Stored Procedures from .NET Applications

11 September 2009

Everybody knows how to call stored procedures from a .NET application. Right? But then, how often do you see stored procedures used to their full advantage in database applications? William Brewer goes through some of the basics, but uses PowerShell, IronPython and VB.NET for the example .NET application just to freshen the subject up a bit, and avoids all mention of Northwind or AdventureWorks!

In this article, I’m going to illustrate how a stored procedure can be called from a .NET process, including all the ways in which data can be passed to the database. I’ll use Powershell in the example code just because so many examples exist already in the other .NET languages, but I'll add a version of the finished routine in IronPython and VB.NET.

When you  access a database application from a client, the chances are that you will need to call stored procedures  in the database layer. You’ll probably use the SQLClient  if you are using .NET, or SQL Native Client in unmanaged code. From Linux, you’d use JDBC.  In any event, all the hard work is done for you.

in .NET, there are three very similar ways of accessing SQL Server. If you want to access other databases such as MySQL, Access or SQLite, then you'll use the net library System.Data.ODBC but we are going to use System.Data.SQLClient, as it supports all the current SQL Server datatypes.The third main approach is System.Data.OLEDB but but this is minority-interest.

Stored Procedures provide more alternatives in the way they can pass data back to the application. You can pass data and information

  • As one or more results
  • As a series of messages (e.g. DBCC)
  • As an integer return code
  • In output or input/output parameters.
  • In an error

Because of this, the interface can be slightly more complicated than just using Ad-Hoc SQL or views, and getting back single results. The payback is that Stored Procedures tend to work faster, are much more secure, are more economical with server memory, and can contain a lot more logic. Additionally, it makes teamwork easier: As long as the name of the stored procedure, what it does, and the parameters remain the same, it also allows someone else in the team to work on the database code without you having to change your client software.

Most Stored procedures are very simple, and are easy to access.  Stored procedures can, if so wished, return absolutely nothing, and such things are pretty simple to implement, but are not particularly common in actual use. Even if you are making a call to a database to record an event or update  a record, you are likely to want to know if it was successful or not, so already, you are likely to be tackling at least one transfer medium in the list I’ve given.

Let’s start off with a very simple application log consisting of a table and a stored procedure. We’ll refine it later. To try this out, just create a trial database in SSMS and use this code (I’m using SQL Server 2005) by pasting it into a query window and executing it…

--delete the procedure if it exists

IF EXISTS ( SELECT  *

            FROM    information_schema.Tables

            WHERE   TABLE_Name LIKE 'ActivityLog' )

    DROP TABLE [dbo].[ActivityLog]

go

 

CREATE TABLE [dbo].[ActivityLog]

    (

     [ActivityLog_id] [int] IDENTITY(1, 1)

                            NOT NULL,

     [Type] [int] NOT NULL,

     [creator] [varchar](80) NOT NULL,

     [insertiondate] [datetime] NOT NULL,

     [LogString] [varchar](2000) NOT NULL,

     [terminationdate] [datetime] NULL,

     CONSTRAINT [PK_dbo_ActivityLog] PRIMARY KEY CLUSTERED ([ActivityLog_id] ASC)

        WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF,

              ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]

    )

ON  [PRIMARY]

 

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'primary key for the table', @level0type=N'SCHEMA',

    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',

    @level2type=N'COLUMN', @level2name=N'ActivityLog_id'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'Date the record was created (automatic)', @level0type=N'SCHEMA',

    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',

    @level2type=N'COLUMN', @level2name=N'insertiondate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'Date for the termination of the record', @level0type=N'SCHEMA',

    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',

    @level2type=N'COLUMN', @level2name=N'terminationdate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'This table is the audit log of all activity in the application',

    @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',

    @level1name=N'ActivityLog'

GO

 

ALTER TABLE [dbo].[ActivityLog]

       ADD CONSTRAINT [DF_ActivityLog_Type] DEFAULT ((1)) FOR [Type]

ALTER TABLE [dbo].[ActivityLog]

       ADD CONSTRAINT [DF_ActivityLog_creator] DEFAULT (user_name()) FOR [creator]

ALTER TABLE [dbo].[ActivityLog]

       ADD CONSTRAINT [DF_ActivityLog_insertiondate] DEFAULT (getdate()) FOR [insertiondate]

ALTER TABLE [dbo].[ActivityLog]

       ADD CONSTRAINT [DF_ActivityLog_LogString] DEFAULT ('') FOR [LogString]

GO

 

--delete the procedure if it exists

IF EXISTS ( SELECT  *

            FROM    information_schema.routines

            WHERE   Routine_Name LIKE 'InsertLogString'

                    AND Routine_Type LIKE 'Procedure' )

    DROP PROCEDURE InsertLogString

go

   

CREATE PROCEDURE InsertLogString

/*

Logs an event in a activity log table and adds the

date, user and so on

 

 usage:

InsertLogString 4, 'Just written a stored procedure'

*/  (

     @Type INT,--the type of entry

     @LogString VARCHAR(2000)--the actual text

    )

AS

    INSERT  INTO ActivityLog (Type, LogString)

            SELECT  @Type,        --the type of entry

                    @LogString    --the actual text

 go

 

Now we can easily call this logging stored procedure  from our client application just by executing the stored procedure. For some reason, the hardest part always seems to be the configuration string. It is worth getting this right before you start, maybe using an office application to test it. There are even websites that exist just to give sample Configuration strings, but they are mostly for ODBC. SQLClient is pretty easy.

 Once you have got the configuration string right, it is downhill all the way. Here is a simple Powershell example. (Remember  to alter the connection string in the code to suit your server and database, of course.)

$ErrorActionPreference = "Stop"

#none of our errors are recoverable

$conn = new-Object System.Data.SqlClient.SqlConnection("Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID")

$conn.Open() | out-null

$cmd = new-Object System.Data.SqlClient.SqlCommand("InsertLogString", $conn)

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure

#

$cmd.Parameters.Add("@Type",[system.data.SqlDbType]::Int) | out-Null

$cmd.Parameters['@type'].Direction = [system.data.ParameterDirection]::Input

$cmd.Parameters['@type'].value=2

$cmd.Parameters.Add("@LogString",[system.data.SqlDbType]::VarChar) | out-Null

$cmd.Parameters['@LogString'].Direction = [system.data.ParameterDirection]::Input

$cmd.Parameters['@LogString'].value = 'This has happened quite suddenly'

$cmd.ExecuteNonQuery() #because we are not getting a result back

$conn.Close()

So we have called a stored procedure with a couple of parameters. Hmm. We’re not quite finished.  That connection string shouldn't really be in code, as you'd have to change it if your credentials change. You can store connection strings in XML configuration files and read them in with the ConfigurationManager class.  More importantly, we need to be able to see any errors that happen. There are a number of ways of doing this. In Powershell, though, we’re rather restricted. We’ll take an easy option and adapt the code slightly where the $cmd.Executenonquery() is called

$ErrorActionPreference = "SilentlyContinue"

$rdr = $cmd.Executenonquery()  #because we are not getting a result back

 if(-not $?) {$error[0]|format-list -force}

$conn.Close()

This provides us with more information than we really need, but it keeps the code simple. Next, we worry about validating the input. There is no point in firing an error on invalid input as the application will want to know what type of  problem there was, and react accordingly. A lot of stored procedures send back a return code. We can always read that.

We’ll first make a slight change to the stored procedure so it returns 0 if it all went well, 1 if the log type was out of range, 2 if it failed to write to the log and 3 if something else happened….

--delete the procedure if it exists

IF EXISTS ( SELECT  *

            FROM    information_schema.routines

            WHERE   Routine_Name LIKE 'InsertLogString'

                    AND Routine_Type LIKE 'Procedure' )

    DROP PROCEDURE InsertLogString

go

   

CREATE PROCEDURE InsertLogString

/*

Logs an event in a activity log table and adds the

date, user and so on

 

 usage:

InsertLogString 4, 'Just written a stored procedure'

 

Declare @ret int

Execute @ret=InsertLogString 4, 'Just added a return code'

Select @Ret

*/  (

     @Type INT,--the type of entry

     @LogString VARCHAR(2000)--the actual text

    )

AS

Declare @Rowcount int

    IF @Type NOT BETWEEN 1 AND 12

        RETURN 1

     

 

    INSERT  INTO ActivityLog (Type, LogString)

            SELECT  @Type,          --the type of entry

                    @LogString      --the actual text

set @rowcount=@@Rowcount                   

    IF @rowcount=0

        RETURN 2

    IF @rowcount=1

        RETURN 0

 

    RETURN 3

 go

 

Now, at the end of the client routine, we have access to this return code. In this example, we’ll just print it out but you’d want to react in the procedure according to the value of the return code. Apologies for not doing a complete worked example but we want to keep things simple just so we can illustrate the basics..

$cmd.Parameters.Add("@rtn",[system.data.SqlDbType]::Int) | out-Null

$cmd.Parameters['@rtn'].Direction = [system.data.ParameterDirection]::ReturnValue

$ErrorActionPreference = "SilentlyContinue"

$rdr = $cmd.Executenonquery()  #because we are not getting a result back

 if(-not $?) {$error[0]|format-list -force}

$conn.Close()

write-output $cmd.Parameters['@rtn'].value

 

Okay, but what about getting something back from this routine? Let’s pretend that it would be useful to return the date on the database  at which the log entry was made.

Well, we have two choices. We can make the @LogString into an InputOutput variable or we can create an output variable just to hold the Date and time. Let’s do the latter, since using a variable for two purposes is a potential snare..

--delete the procedure if it exists

IF EXISTS ( SELECT  *

            FROM    information_schema.routines

            WHERE   Routine_Name LIKE 'InsertLogString'

                    AND Routine_Type LIKE 'Procedure' )

    DROP PROCEDURE InsertLogString

go

   

CREATE PROCEDURE InsertLogString

/*

Logs an event in a activity log table and adds the

date, user and so on

 

 usage:

InsertLogString 4, 'Just written a stored procedure'

 

Declare @ret int

Declare @DateOfLog datetime

Execute @ret=InsertLogString 4, 'Just added a return code',@DateOfLog output

Select @Ret,@DateOfLog

*/  (

     @Type INT,--the type of entry

     @LogString VARCHAR(2000),--the actual text

     @DateOfEntry DATETIME OUTPUT--the date of the insertion

    )

AS

    DECLARE @Rowcount INT

    IF @Type NOT BETWEEN 1 AND 12

        RETURN 1

     

 

    INSERT  INTO ActivityLog (Type, LogString)

            SELECT  @Type,          --the type of entry

                    @LogString      --the actual text

    SET @rowcount=@@Rowcount  

    SET @DateOfEntry=GETDATE()                

    IF @rowcount=0

        RETURN 2

    IF @rowcount=1

        RETURN 0

 

    RETURN 3

 go

All we then had to do in order to get the value back was to add the following parameter

$cmd.Parameters.Add("@DateOfEntry",[system.data.SqlDbType]::DateTime) | out-Null

$cmd.Parameters["@DateOfEntry"].Direction = [system.data.ParameterDirection]::Output

So, we’ve coped with input parameters, output parameters and return codes. Next we have to deal with a stored procedure that has one or more results.

Just to make life a bit more difficult for ourselves, we’ll pass back two results. We’ll return the values in the inserted row, and we’ll return the time that elapsed between log entries for the last ten log entries (we have the bones of a performance logging system here!).

This time, the stored procedure is beginning to bristle with outputs. It also is getting a lot nearer the sort of stored procedures I use. A stored procedure that returns a customer object that may have a number of contact numbers, addresses, notes, and purchases, and all this information is easier to transfer as a series of results rather than a Godzilla view, and the basic customer details such as the surrogate ID can easily be passed in an output variable.

--delete the table if it exists

IF EXISTS ( SELECT  *

            FROM    information_schema.Tables

            WHERE   TABLE_Name LIKE 'ActivityLog' )

    DROP TABLE [dbo].[ActivityLog]

go

 

CREATE TABLE [dbo].[ActivityLog]

    (

     [ActivityLog_id] [int] IDENTITY(1, 1)

                            NOT NULL,

     [Type] [int] NOT NULL,

     [creator] [varchar](80) NOT NULL,

     [insertiondate] [datetime] NOT NULL,

     [LogString] [varchar](2000) NOT NULL,

     [terminationdate] [datetime] NULL,

     CONSTRAINT [PK_dbo_ActivityLog] PRIMARY KEY CLUSTERED ([ActivityLog_id] ASC)

        WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF,

              ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]

    )

ON  [PRIMARY]

 

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'primary key for the table', @level0type=N'SCHEMA',

    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',

    @level2type=N'COLUMN', @level2name=N'ActivityLog_id'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'Date the record was created (automatic)', @level0type=N'SCHEMA',

    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',

    @level2type=N'COLUMN', @level2name=N'insertiondate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'Date for the termination of the record', @level0type=N'SCHEMA',

    @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'ActivityLog',

    @level2type=N'COLUMN', @level2name=N'terminationdate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

    @value=N'This table is the audit log of all transaction activity in the database',

    @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',

    @level1name=N'ActivityLog'

GO

 

ALTER TABLE [dbo].[ActivityLog]

ADD CONSTRAINT [DF_ActivityLog_Type] DEFAULT ((1)) FOR [Type]

ALTER TABLE [dbo].[ActivityLog]

ADD CONSTRAINT [DF_ActivityLog_creator] DEFAULT (USER_NAME()) FOR [creator]

ALTER TABLE [dbo].[ActivityLog]

ADD CONSTRAINT [DF_ActivityLog_insertiondate] DEFAULT (GETDATE()) FOR [insertiondate]

ALTER TABLE [dbo].[ActivityLog]

ADD CONSTRAINT [DF_ActivityLog_LogString] DEFAULT ('') FOR [LogString]

GO

 

--delete the procedure if it exists

IF EXISTS ( SELECT  *

            FROM    information_schema.routines

            WHERE   Routine_Name LIKE 'InsertLogString'

                    AND Routine_Type LIKE 'Procedure' )

    DROP PROCEDURE InsertLogString

go

   

CREATE PROCEDURE InsertLogString

/*

Logs an event in a activity log table and adds the

date, user and so on

 

 usage:

Declare @DateOfLog datetime

Execute InsertLogString 4, 'Just written a rather good stored procedure',@DateOfLog output

 

Declare @ret int

Declare @DateOfLog datetime

Execute @ret=InsertLogString 4, 'Just added a return code',@DateOfLog output

Select @Ret,@DateOfLog

*/  (

     @Type INT,--the type of entry

     @LogString VARCHAR(2000),--the actual text

     @DateOfEntry DATETIME OUTPUT--the date of the insertion

    )

AS

    DECLARE @InsertionCount INT,

        @InsertedRow INT

    IF @Type NOT BETWEEN 1 AND 12

        RETURN 1

     

    PRINT 'storing '''+@Logstring+''' at '+convert(char(17), GetDate(), 113)

    INSERT  INTO ActivityLog (Type, LogString)

            SELECT  @Type,          --the type of entry

                    @LogString      --the actual text

    SET @InsertionCount=@@RowCount 

    SET @insertedRow=@@identity

    SET @DateOfEntry=GETDATE()                

    SELECT  ActivityLog_id,

            [Type],

            creator,

            insertiondate,

            LogString terminationdate

    FROM    ActivityLog

    WHERE   ActivityLog_ID=@InsertedRow

            --and now the elapsed time between log entiries

    SELECT top 20

            insertionDate,

            [lapsedTime]

             = DATEDIFF(ms, InsertionDate,

                COALESCE((SELECT TOP 1

                                    [next].insertionDate

                          FROM      ActivityLog AS [next]

                          WHERE     [next].ActivityLog_ID

                                                            >[current].ActivityLog_ID

                          ORDER BY  [next].ActivityLog_ID ASC

                         ), GETDATE())),

            LogString

    FROM    activityLog AS [current]

    order by InsertionDate Desc          

    IF @InsertionCount=0

        RETURN 2

    IF @InsertionCount=1

        RETURN 0

    RETURN 3

 go

So all we have to do now is to add the extra logic in the Powershell script to get and display the two results. Here is the script.

$ErrorActionPreference = "Stop"

#none of our errors are recoverable

$conn = new-Object System.Data.SqlClient.SqlConnection("Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID")

$conn.Open() | out-null #open the connection

$cmd = new-Object System.Data.SqlClient.SqlCommand("InsertLogString", $conn)

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure

#now we have created the command and set it to be a stored procedure

#we now add the parameters to the stored procedures

#The @Type parameter

$cmd.Parameters.Add("@Type",[system.data.SqlDbType]::Int) | out-Null

$cmd.Parameters['@type'].Direction = [system.data.ParameterDirection]::Input

$cmd.Parameters['@type'].value=2

#The @LogString parameter

$cmd.Parameters.Add("@LogString",[system.data.SqlDbType]::VarChar) | out-Null

$cmd.Parameters['@LogString'].Direction = [system.data.ParameterDirection]::Input

$cmd.Parameters['@LogString'].value = 'We have just called a stored procedure'

#The @DateOfEntry Output parameter

$cmd.Parameters.Add("@DateOfEntry",[system.data.SqlDbType]::DateTime) | out-Null

$cmd.Parameters["@DateOfEntry"].Direction = [system.data.ParameterDirection]::Output

#and The return code

$cmd.Parameters.Add("@rtn",[system.data.SqlDbType]::Int) | out-Null

$cmd.Parameters['@rtn'].Direction = [system.data.ParameterDirection]::ReturnValue

$ErrorActionPreference = "SilentlyContinue"

$rdr = $cmd.ExecuteReader()  #because we are getting a result back

 if(-not $?) {$error[0]|format-list -force}#if we have an error here report it and quit

 #Remember that you can't read the output variables until you've read all the results!

 #first result

$ErrorActionPreference = "Stop"

$Counter = $rdr.FieldCount

while ($rdr.Read()) {

      for ($i = 0; $i -lt $Counter; $i++) {

            @{ $rdr.GetName($i) = $rdr[$i]; }

      }

}

$rdr.NextResult()#get the next result

$Counter = $rdr.FieldCount

#and display it.

while ($rdr.Read()) {

      for ($i = 0; $i -lt $Counter; $i++) {

            @{ $rdr.GetName($i) = $rdr[$i]; }

      }

}

 

$conn.Close()

#and we just show we've read the output variables!

write-output $cmd.Parameters['@rtn'].value

write-output $cmd.Parameters['@DateOfEntry'].value

The only task I haven’t shown is how to get the messages from the stored procedure. If you do PRINT statements, or you want to see other status messages (Normally you switch off the rowcount message using SET NOCOUNT  ON)  then you have to get hold of these messages. This is done by creating a SqlInfoMessageEventHandler delegate, identifying the method that handles the event,  to listen for the InfoMessage event on the SqlConnection class. Message-context information such as severity and state are passed as arguments to the callback, because from the system perspective, these messages are just like errors. At its simplist, you might do this...

static void ffs(object sender, SqlInfoMessageEventArgs messages)

    {

      Console.WriteLine("msg->" + messages.Message);

    }

But this cannot be done with Powershell v1.

The routine translates easily into IronPython, but error-handling in Python (IronPython) is even more arcane than Powershell, so I've omitted it.

import clr

clr.AddReference('System.Data')

from System.Data import *

 

conn = SqlClient.SqlConnection("Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID")

conn.Open()

cmd = SqlClient.SqlCommand("InsertLogString", conn)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@Type",SqlDbType.Int)

cmd.Parameters['@type'].Direction = ParameterDirection.Input

cmd.Parameters['@type'].Value=2

cmd.Parameters.Add("@LogString",SqlDbType.VarChar)

cmd.Parameters['@LogString'].Direction = ParameterDirection.Input

cmd.Parameters['@LogString'].Value = 'We have just called a stored procedure'

#The @DateOfEntry Output parameter

cmd.Parameters.Add("@DateOfEntry",SqlDbType.DateTime)

cmd.Parameters["@DateOfEntry"].Direction = ParameterDirection.Output

#and The return code

cmd.Parameters.Add("@rtn",SqlDbType.Int)

cmd.Parameters['@rtn'].Direction = ParameterDirection.ReturnValue

rdr = cmd.ExecuteReader()  #because we are getting a result back

Counter = rdr.FieldCount

while rdr.Read():

    for i in range(Counter):

          print rdr.GetName(i), rdr.GetValue(i)

rdr.NextResult()#get the next result

Counter = rdr.FieldCount

#and display it.

while rdr.Read():

    for i in range(Counter):

        print rdr.GetName(i), rdr.GetValue(i)

rdr.Close()

conn.Close()

#and we just show we've read the output variables!

print cmd.Parameters['@rtn'].Value

print cmd.Parameters['@DateOfEntry'].Value

 

 

We have not quite achieved all we want to yet as it would be nice to receive all those PRINT messages from SQL Server. To do this, you would do best to use either VB.NET or C#. The code just requires an event handler. This is explained very well here on MSDN- Working with Connection Events. Here is the complete code in VB

Imports System

Imports system.configuration

Imports System.Data

Imports System.Data.SqlClient

Module storedprocedure

    Dim process As String

    Dim Messages As String

    Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)

        Dim err As System.Data.SqlClient.SqlError

        For Each err In args.Errors

            Messages += err.Message + vbCrLf

        Next

    End Sub

 

    Sub Main()

        Dim i As Integer

        Dim connectionString As String

        Dim counter As Integer

        Messages = ""

        connectionString = ""Server=MyServer;DataBase=MyDatabase;password=MyPassword; uid=MyUserID""

        Try

            Using conn As New SqlConnection(connectionString)

                conn.Open()  'Open the connection

                AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

                Using cmd As New SqlCommand("InsertLogString", conn)

                    With cmd

                        .CommandType = CommandType.StoredProcedure

                        'now we have created the command and set it to be a stored procedure

                        'we now add the parameters to the stored procedures

                        'The @Type parameter

                        .Parameters.Add(dd("@Type", SqlDbType.Int)

                        .Parameters("@type").Direction = ParameterDirection.Input

                        .Parameters("@type").Value = 2

                        'The @LogString parameter

                        .Parameters.Add("@LogString", SqlDbType.VarChar)

                        .Parameters("@LogString").Direction = ParameterDirection.Input

                        .Parameters("@LogString").Value = "We have just called a stored procedure"

                        'The @DateOfEntry Output parameter

                        .Parameters.Add(dd("@DateOfEntry", SqlDbType.DateTime)

                        .Parameters("@DateOfEntry").Direction = ParameterDirection.Output

                        'and The return code

                        .Parameters.Add("@rtn", SqlDbType.Int)

                        .Parameters("@rtn").Direction = ParameterDirection.ReturnValue

                        Dim rdr As SqlDataReader = .ExecuteReader()  'because we are getting a result back

                        'Remember that you can't read the output variables until you've read all the results!

                        'first result

                        counter = rdr.FieldCount

                        While rdr.Read()

                            For i = 0 To counter - 1

                                System.Console.Write(rdr.GetName(i) & " = " & rdr.GetValue(i) & vbCrLf)

                            Next i

                        End While

                        rdr.NextResult() 'get the next result

                        counter = rdr.FieldCount

                        'and display it.

                        While rdr.Read()

                            For i = 0 To counter - 1

                                System.Console.Write(rdr.GetName(i) & " = " & rdr.GetValue(i) & vbCrLf)

                            Next i

                        End While

                        rdr.Close()

                        'and we just show we've read the output variables!

                        System.Console.Write("@rtn=" & .Parameters("@rtn").Value() & vbCrLf)

                        System.Console.Write("@DateOfEntry=" & .Parameters("@DateOfEntry").Value() & vbCrLf)

                    End With

                End Using

                conn.Close()

            End Using 'the connection

        Catch ex As Exception

            System.Console.Write(ex.Message & " whilst " & process & vbCrLf)

        End Try

        System.Console.Write("Messages=" & Messages)

    End Sub

 

End Module

So you will see from this article that there are a number of different types of information we need to be able to get from a stored procedure. Some aspects are easy, and appear in demos all around the internet. Other information, such as messages, are fiendish and very language-dependent. Once you feel confident with output variables, return codes, and multiple results, you'll find uses for them to make interacting with a database a lot easier.

William Brewer

Author profile:

William Brewer is a SQL Server developer who has worked as a Database consultant and Business Analyst for several Financial Services organisations in the City of London. True to his name, he is also an expert on real ale.

Search for other articles by William Brewer

Rate this article:   Avg rating: from a total of 63 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Well OK...
Posted by: Bill Barnacle (view profile)
Posted on: Tuesday, September 22, 2009 at 6:09 AM
Message: Not a bad article on the Powershell syntax, but does it tell anyone but a complete newbie anything new? Surely this is very basic stuff, as far as Stored procs go.

Maybe I'm missing something, but this should be general knowledge for anyone developing against a database: either a database developer or application developer.

I would have been more interested in an article on Powershell and advantages/disadvantages in its use.



Subject: Re: Well OK...
Posted by: Andrew Clarke (view profile)
Posted on: Tuesday, September 22, 2009 at 9:03 AM
Message:

We actually asked William to do us a basic article on calling stored procedures from .NET. We instantly bought his idea of illustrating this with a range of different .NET languages, in order to provide more interest for other readers.
I'm not sure I agree that the proper collection of SQL Server messages (as in the final illustration) is widely known about. I often see errors and misunderstandings on the subject.

If this really is too basic for you, we also have


Subject: Embedded SQL syndrome.....might be a cure !!!
Posted by: H. McCreight (not signed in)
Posted on: Wednesday, September 23, 2009 at 3:11 AM
Message: Yes this is pretty basic stuff. But considering that a lot of code samples eg on CodeProject and the books that people learn from AND even some training courses have got SQL code embedded in the actual code VB.NET code rather than wrapping it up neatly in stored procedures, then this article might just let them see the error of their ways and cure them of embedded SQL syndrome

Subject: Re: Embedded SQL syndrome.....might be a cure !!!
Posted by: Phil Factor (view profile)
Posted on: Wednesday, September 23, 2009 at 4:57 AM
Message: If people followed these simple guidelines then SQL Injection would be extraordinarily difficult, for a start. I like to return the number of rows in the result and the return value is an unconventional but handy way of doing this, though I'd recommend output variables as ideal for this purpose. OK, there is basic stuff here, but I like being reminded of it all.

Subject: Its Nice
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 24, 2009 at 4:07 AM
Message: Its ok but its a problem to understand beginners

Subject: Very Helpful
Posted by: Sean-Phoenix (view profile)
Posted on: Sunday, August 26, 2012 at 9:51 AM
Message: I am a self-proclaimed beginnger and this article was extremely helpful to me.

Subject: Need assistance
Posted by: Sean-Phoenix (view profile)
Posted on: Sunday, August 26, 2012 at 10:35 AM
Message: I created the database objects and PowerShell script as stated and they run fine. I created the VB.NET application but when I run it all I get back is:

"The method or operation is not implemented. whilst Messages="

What is the issue?

Thanks

 

Top Rated

Acceptance Testing with FitNesse: Multiplicities and Comparisons
 FitNesse is one of the most popular tools for unit testing since it is designed with a Wiki-style... Read more...

Acceptance Testing with FitNesse: Symbols, Variables and Code-behind Styles
 Although FitNesse can be used as a generic automated testing tool for both applications and databases,... Read more...

Acceptance Testing with FitNesse: Documentation and Infrastructure
 FitNesse is a popular general-purpose wiki-based framework for writing acceptance tests for software... Read more...

TortoiseSVN and Subversion Cookbook Part 11: Subversion and Oracle
 It is only recently that the tools have existed to make source-control easy for database developers.... Read more...

TortoiseSVN and Subversion Cookbook Part 10: Extending the reach of Subversion
 Subversion provides a good way of source-controlling a database, but many operations are best done from... Read more...

Most Viewed

A Complete URL Rewriting Solution for ASP.NET 2.0
 Ever wondered whether it's possible to create neater URLS, free of bulky Query String parameters?... Read more...

Visual Studio Setup - projects and custom actions
 This article describes the kinds of custom actions that can be used in your Visual Studio setup project. Read more...

.NET Application Architecture: the Data Access Layer
 Find out how to design a robust data access layer for your .NET applications. Read more...

Calling Cross Domain Web Services in AJAX
 The latest craze for mashups involves making cross-domain calls to Web Services from APIs made publicly... Read more...

Web Parts in ASP.NET 2.0
 Most Web Parts implementations allow users to create a single portal page where they can personalize... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.