Tornado

Tornado works with and extends the power of DatSet in areas like grid, StordedProcedures. When calling an SP, you can specify the Type of command as -

SP (Def) - The name of an SP. This is the primary choice for the SP calls in Tornado.
Text - An SQL query string.
Table - Use the table directly from a datasource.

There are several way to handle the storedprocedures -

Stand alone SP with no inputs using dbStoredProc. Return a single table.
SP with full In/Out parameters using dbStoredProcCmdPrarams.

Note -

Q311540 - "COMPUTE BY Statements Not Supported" Exception When You Use COMPUTE BY Clause with SQL Server". This means you cannot use SqlClient provider for SPs with the COMPUTE BY clause.
That classic ASP-db Stand Alone with on-the-fly parameters option is not available any more due to the restrictions in ASP.NET.

Example - Simple SQL - Single Result Set

<script language='vb' runat='server'>
Sub Page_Load(Source as Object,   E as EventArgs)
  Dim SP As New Tornado.z
    With SP
      .dbQP = "Unit=1|S=7|Pagesize=-1|NavigationItem=None|Th=ti=Simple SP|Pv=OleDB|Dt=SQL"
      .dbDSN = "fujitsu;pubs;sa;sa"
      .dbStoredProc = "spname=reptq1"  '... Add dbResultSet = n to get the 'n' result set
      .ASPdbSP()  '...use .ASPdbSP("getall=true") to get all result sets
    End With
End Sub
</script>

This example execute the 'reqtq1' SP returning a result set as a table. Execute this and observer the return data. The property which is not set here is the dbResultSet and defaults to 1. Now we repeat the example and add a line to it -> .dbResultSet = 2. Add that anywhere between the With...End With block. Execute the example again and you'll see a different result. Now, inspect the reptq1source and execute that in the SQL analyzer of the Enterprise Manager reviews (7) result sets. You can either loop thru that and get each result sets or you can use .ASPdbSP(getall=true) in the .ASPdbSP method. Now, you get all (7) result sets. Repeat this with example reptq2. Example reptq3 is different as input parameters are expected. We need to input to the SP - 'lolimit', 'hilinit' and 'type'. The following code will do that and you'll see all (6) result sets.

<script language='vb' runat='server'>
Sub Page_Load(Source as Object,   E as EventArgs)
  Dim SP As New Tornado.z
    With SP
      .dbQP = "Unit=1|S=7|Pagesize=-1|NavigationItem=None|" & _
          "Th=ti=Advanced SP with Params and ResultSets|Pv=SQLclient| dt=SQL"
      .dbDSN = "fujitsu;pubs;sa;sa"
      .dbStoredProcCmdParams = "spname=reptq3|NoRs=f; Name=@lolimit| Type=integer| Value=5," & _  
          "Name=@hilimit|Type=integer|Value=20,   Name=@type| Value=business"
      .ASPdbSP("getall=true")
    End With
  End Sub
</script>

Note: Specify @type as business instead of 'business'.

For SP that needs input data, we use the .dbStoredProcedureCmdParams property to specify the input variables. The setup is like -

Name of storedprocedure is reptq3
There are return records, therefore NoRs is not true
First input parm is @lolimit, integer and value=5
Second input parm is @hilimit, integer and value=20
Third input param is @type, defaults to string type and value = business
Want to display all result sets.

Imagine how man hours of programming you can save with this less than 10 lines .aspx program.

Now, we'll see how to get Output Variables from the SP.

Simple SQL - DS mapping

Use VB.Net - Drop a Datagrid to the Designer with DataGrid1 and place the following code in a WebForm.

<script language='vb' runat='server'>
 Private Sub Page_Load(ByVal sender As System.Object,   ByVal e As System.EventArgs) Handles MyBase.Load
  Dim DS As New Tornado.z
    With DS
      .dbQP = "U=99|S=2|Ps=-1|D=Nwind"
      .dbTextHolder = "Title=ASPdb DataGrid demo"
      .dbSQL = "Select ProductID,   ProductName,   UnitPrice From products"
      DataGrid1.DataSource = .ASPdbDS()
      DataGrid1.DataBind()
    End With
End Sub
</script>

Simple StoredProcedure - DS mapping

Setup your SQL data source using the simple dbDSN or in dbQP as 'D=server;database;uid;pwd'. We'll use the standard SP example reptq2 which has 17 result sets.

 Private Sub Page_Load(ByVal sender As System.Object,   ByVal e As System.EventArgs) Handles MyBase.Load
  Dim dts As New Tornado.z
    With dts
      .dbQP = "U=99|S=2|Ps=-1|D=fujitsu;pubs;sa;sa|dt=SQL|pv=SQLCLient"
      .dbTextHolder = "Title=ASPdb DataGrid SP demo|Sub=Sub Title"
      .dbStoredProc = "spname= reptq2"
      Dim DS As New DataSet
      DS = .ASPdbSP2DS()
      '...find out how many tables are there
      Dim tbs As Integer = DS.Tables.Count
      '...process tables 0 to tbs-1 your way,   here just show table(1)
      DataGrid1.DataSource = DS.Tables(1)
      DataGrid1.DataBind()
    End With
End Sub

Advanced MS-SQL SP

Let's put the following SP in the pubs database. It allows use to input the WHERE criteria in a select statement. Default value is 'Type LIKE %cook'. Execute this SP in the SQL server Query Analyser by typing -> execute spTest [type LIKE 'bus%'] and also execute spTest 'price > 20'

CREATE PROCEDURE spTest @strWhere varchar(250)="type like '%cook'"
AS declare 
@tmpSqlMain varchar(255),  
@tmpWhere varchar(180)
select @tmpSqlMain='select pub_id,   type,   title_id,   price from titles'
-- get Where clause
select @tmpWhere= @strWhere
-- check if anything in input criteria and add it to the SQL statement if any
if @tmpWhere <>'' 
begin 
select @tmpSqlMain=@tmpSqlMain + ' Where ' + @tmpWhere
end 
-- Execute SQL statement
execute (@tmpSqlMain)

Now query the advanced SP using both conditions.

<script language='vb' runat='server'>
  Sub Page_Load(Source as Object,   E as EventArgs)
  Dim SP As New Tornado.z
  With SP
    .dbQP = "Unit=1|S=7|Ps=-1|Ni=None|Th=ti=Advanced SP| pv=SQLClient| dt=SQL"
    .dbDSN = "fujitsu;pubs;sa;sa"
    .dbStoredProcCmdParams = "sp=sptest; Na=@strWhere| Ty=string| Val=Type LIKE 'bus%'| Size=16"
'... try also .dbStoredProcCmdParams = "sp=sptest; Na=@strWhere|Ty=string|Val=Price > 20"
    .ASPdbSP()
  End With
</script>

Note: You should specify the Size for the Input value ->  Type LIKE 'bus%' . If you do not specify,   
           system will take the length of the input string. In most cases,   it'll do the job.

SP with Input and Output values

Now, let's cover the case where there are returning values from thw SP. We'll use the following SP from the pubs DB again that returns @ytd_sales. Just cut and paste the code into the storedprocedures section of 'pubs'.

CREATE PROCEDURE get_sales_for_title
@title varchar(80),   -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS
-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title like '%' + @title + '%'
RETURN
GO

We'll define the In and Out parameters

<script language='vb' runat='server'>
Sub Page_Load(Source as Object,   E as EventArgs)
Dim GD As New Tornado.GetData
Dim SP As New Tornado.z
  With SP
    .dbQP = "Unit=3| Ni=None| DSN=fujitsu;pubs;sa;sa| pv=SQLClient"
    .dbTextHolder = "Ti=SP with return value"
    .dbStoredProcCmdParams = "SPname=get_sales_for_title | SPtype=SP | NoRS=true; 
            name=@title | type=VarChar | dir=input | size=80 | value=Valley,   
            name=@ytd_sales | type=integer | dir=output"
    .ASPdbSP()
  End With
  Response.Write("<center><b>Return value @ytd_sales=" & GD.Get_SPOutput("3","@ytd_sales") & "</b></center>")
</script>

The above code is very simple and do not need much explanation. An exercise in options like - we specify the Type as VarChar instead of string. You can set this according to the exact DBType member name if necessary in advanced applications.

DBType Member Name

SqlClient DataType	BigInt, Binary, Bit, Char, DateTime (Date), Decimal, Float, Image, Int (Integer), Money, NChar, NText, NVarChar, Real, SmallDateTime, SmallInt, SmallMoney, Text, Timestamp, TinyInt, UniqueIdentifier, VarBinary, VarChar (String), Variant
OleDb DataType	BigInt, Binary, Bit, Char, DateTime (Date), Decimal, Float, Image, Int (Integer), Money, NChar, NText, NVarChar, Real, SmallDateTime, SmallInt, SmallMoney, Text, Timestamp, TinyInt, UniqueIdentifier, VarBinary, VarChar (String), Variant
Odbc DataType	BigInt, Binary, Bit, Char, Date (Date), DateTime, Decimal, Double, Image, Int (Integer), NChar, NText, Numeric, NVarChar, Real, SmallDateTime, SmallInt, Text, Time, Timestamp, TinyInt, UniqueIdentifier, VarBinary, VarChar (String)
OracleClient DataType	BFile, Blob, Byte, Char, Clob, Cursor, DateTime (Date), Double, Float, Int16, Int32, IntervalDayToSecond, IntervalYearToMonth, LongRaw, LongVarChar, NChar, NClob, Number (Integer), NVarChar, Raw, RowId, SByte, Timestamp, TimestampLocal, TimestampWithTZ, UInt16, UInt32, VarChar (String)
MySql DataType	Blob, Byte, Date (Date), Datetime, Decimal, Double, Enum, Float, Geometry, Int16, Int24, Int32 (Integer), Int64, LongBlob, MediumBlob, Newdate, Set, String (String), Time, Timestamp, TinyBlob, VarChar, Year, VarChar

MS-Access queries as SP

We can also use .dbStoredProc to access Access's query with or w/o input parameters. See the following example -

<script language='vb' runat='server'>
  Sub Page_Load(Source as Object,   E as EventArgs)
  Dim SP As New Tornado.z
    With SP
      .dbQP = "Unit=1|S=7|Ps=5|Th=ti=Access Query as SP"
      .dbDSN = "nwind"
      .dbStoredProc = "spname=[Quarterly Orders by Product]| Type=Table"
      .ASPdbSP()
    End With
  End Sub
</script>

MS-Access queries as SP with input Parameters

We can also use .dbStoredProcCmdParams to access Access's query with input parameters. See the following example -

<script language='vb' runat='server'>
  Sub Page_Load(Source as Object,   E as EventArgs)
  Dim SP As New Tornado.z
    With SP
      .dbQP = "Unit=1|S=7|Pagesize=-1|NavigationItem=None|Th=ti=Access Query as SP with Params"
      .dbDSN = "nwind"
      .dbStoredProcCmdParams = "sp=Employee Sales by Country| Ty=table; 
               Na=Beginning Date| Ty=date| Val=1/1/94,   Na=Ending Date| Ty=date| Val=1/1/95"
      .ASPdbSP()
    End With
End Sub
</script>

Note that abbreviated keywords are used in this example. No need to use [...] for spname with embedded spaces as. system with add that for you. [..]] are not needed for parameter names.

Special Topic - StoredProcedures