Stored Procedures from ASP.NET and VB.NET

Example - I (A Stored Procedure Returns Single Value)

In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly

'Set up Connection object and Connection String for a SQL Client
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;"
SQLCon.Open()

SQLCmd.CommandText = "SelectRecords" ' Stored Procedure to Call
SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
SQLCmd.Connection = SQLCon 'Active Connection

The procedure can be called by adding Parameters in at least two different methods, the simplest way to add parameters and respective values is using

SQLCmd.Parameters.AddWithValue("S_Mobile", "Test")
SQLCmd.Parameters.AddWithValue("S_Mesg", "Test")
SQLCmd.Parameters.AddWithValue("LastMsgID", "")

In this above method, you doesn’t necessarily know the actually data type that you had in your procedure and all parameters are validated according to the type declared in your procedure but only thing is all the validations will occur in SQL and not in your client code.

We still need to declare the last parameter as Output and we need to do that explicitly as the default type is Input. So here we are going to declare the last parameter as Output by

SQLCmd.Parameters("LastMsgID").Direction = ParameterDirection.Outputfs

If you want to declare parameters properly then you need to use the below method to add all the parameters with its data type, direction. Also if you are using stored procedures to update all the rows in a dataset then you need to declare parameters in the below fashion and give SouceColumn value as the Column name in the DataTable.

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("S_Mobile", SqlDbType.VarChar, 10, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "91000000000"))

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("S_Mesg", SqlDbType.VarChar, 160, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Calling Stored Procedures from VB.NET"))

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("LastMsgID", SqlDbType.BigInt, 5, ParameterDirection.Output, False, 5, 0, "", DataRowVersion.Current, 0))

' The Above Procedure has two input parameters and one output parameter you can notice the same in the “Parameter Direction”
SQLCmd.ExecuteNonQuery() 'We are executing the procedure here by calling Execute Non Query.

MsgBox(SQLCmd.Parameters("LastMsgID").Value) 'You can have the returned value from the stored procedure from this statement. Its all similar to ASP / VB as the only difference is the program structure.

Example - II (Stored Procedure to get Table Result Set)

In order to get the result sets from the stored procedure, the best way is to use a DataReader to get the results. In this example we are getting the results from the Stored Procedure and filling the same in a DataTable.

Here we need to additionally declare a SQLDataReader and DataTable

Dim SQLDBDataReader As SqlClient.SqlDataReader
Dim SQLDataTable As New DataTable

SQLCmd.CommandText = "GetAuthors"
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter("AuthorName", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Y%")) SQLDBDataReader = SQLCmd.ExecuteReader() SQLDataTable.Columns.Add("AuthorName", GetType(Int32), "") SQLDataTable.Columns.Add("AuthorLocation", GetType(String), "")

Dim FieldValues(1) As Object 'A Temporary Variable to retrieve all columns in a row and fill them in Object array

While (SQLDBDataReader.Read)
SQLDBDataReader.GetValues(FieldValues)
      SQLDataTable.Rows.Add(FieldValues)

End While

Example - III (Calling Simple Stored Procedure to get XML Result Set)

In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly.

CREATE PROCEDURE GetRecordsXML (@AuthorName varchar(100))
AS

Select Author_ID, Author_Name, Author_Location Where Author_Name LIKE  @AuthorName from Authors FOR XML AUTO

RETURN

When you use the above procedure you can get XML Results with TableName as Element and Fields as Attributes

Dim SQLXMLReader As Xml.XmlReader

SQLCmd.CommandText = "GetAuthorsXML"
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter("AuthorName", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Y%"))
SQLDBDataReader = SQLCmd.ExecuteReader()

SQLXMLReader = SQLCmd.ExecuteXmlReader()
While (SQLXMLReader.Read)
    MsgBox(SQLXMLReader.ReadOuterXml)
End While

You can further process this XML or write XSL to display results in a formatted manner. But in order to get formatted XML Results, we need to use EXPLICIT case which we can see in our next article on SQL Queries & XML.