''' 

''' Calls custom stored procedure.
''' 
Private Function CallCustomStoredProcedureForGridView() As System.Data.DataSet

	'' Step 1: Configure parameters for your stored procedure if the stored procedure accepts parameters.
	
	'Dim firstParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
	
	'' For SQL Server: use parameter type System.Data.SqlDbType
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, System.Data.SqlDbType.Int, System.Data.ParameterDirection.Input)
	
	'' For Oracel: use parameter type System.Data.OracleClient.OracleType
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, System.Data.OracleClient.OracleType.Int, System.Data.ParameterDirection.Input)
	
	'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, MySql.Data.MySqlClient.MySqlDbType.Int, System.Data.ParameterDirection.Input, True)


	'Dim secondParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
	
	'' For SQL Server: use parameter type System.Data.SqlDbType	
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)    
	
	'' For Oracel: use parameter type System.Data.OracleClient.OracleType
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input)    
	
	'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input, True)    



	'' Step 2: Add the configured parameters to an array list.
	'Dim parameterList(2) As BaseClasses.Data.StoredProcedureParameter
	'parameterList(0) = firstParameter
	'parameterList(1) = secondParameter
  '' Note, in case you need to return output value from the Stored procedure you might use the following code:
  '' Dim thirdParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
  '' Dim myvar As String = ""
  '' thirdParameter = New BaseClasses.Data.StoredProcedureParameter("@StatusDate", myvar, System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Output)
  '' NOTE: if this parameter has a string type such as varchar, nvarchar, string, etc. you have to set its size:
  '' thirdParameter.Size = 10
  '' parameterList(2) = thirdParameter
   
	'' Skip steps 1 and 2 if stored procedure does not accept parameters.

	Dim myStoredProcedure As BaseClasses.Data.StoredProcedure = Nothing

	'' "DatabaseNorthwind1" is a connection string obtained from Web.config 
	'' located in application's root directory.

	'' Step 3: Connect to the stored procedure using following line if 
	'' the stored procedure does not accept any parameters
	myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", nothing)

	'' If the stored procedure accepts parameters, use the following
	'myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", parameterList)    
     

	'' Step 4: Run the stored procedure.    
	'' RunQuery() will return true if stored procedure successfully executed. Otherwise, it will return false.
	'' Use RunQuery or RunNonQuery.  RunQuery is used when a set of records is being returned.  RunNonQuery is used when one or more values are returned through output parameters.
	If (myStoredProcedure.RunQuery()) Then

    '' If you have output parameter, use code like:
    '' Dim outputParameter As System.Data.IDataParameter
    '' outputParameter = DirectCast(myStoredProcedure.OutputParameters(0), System.Data.IDataParameter)
    '' Dim myText As String = outputParameter.Value.ToString()

		'' Result from stored procedure is available 
		'' as a DataSet or as an array of RecordValue objects.
		return (myStoredProcedure.DataSet)

	Else
		' You can raise an exception in the custom stored procedure and catch the exception and reporting it to the user.
		' To raise the exception:
	
		' SET NOCOUNT ON;
	    	' RAISERROR (N'My custom error message goes here', 11, 1)
	
		' IMPORTANT: If you raise an error that has a severity level of 10 or less, it is considered  
		' a warning, and no exception is raised. The severity of the error must be between 11 and 20
		' for an exception to be thrown.
	
		' Once the exception is raised, you can look at:
		' myStoredProcedure.ErrorMessage to get the text of the error message and use RegisterJScriptAlert to report this to the user.
		return nothing
	End If   
End Function
 

 
	Public Sub LoadData()
		' Load the GridView control when page loads.
		Me.LoadGridControl()
	End Sub

     

 
	''' 

	''' Binds the GridView control with data from stored procedure.
	''' 
	Private Sub LoadGridControl()
		' Enable pagination for the GridView control.
		Me.MyGrid.AllowPaging = True

		' Get the data for GridView control from stored procedure.	
		Me.MyGrid.DataSource = Me.CallCustomStoredProcedureForGridView()

		' Save the data obtained from stored procedure in
		' session to be used re-bind the GridView control
		' after postback occurs since GridView control loses 
		' data after postback occurs.
		Me.Page.Session("DataSet") = Me.MyGrid.DataSource

		' Let the GridView control handle rendering of columns
		' and their names.
		Me.MyGrid.AutoGenerateColumns = True

		' Bind the GridView control with the data from stored procedure.		
		Me.MyGrid.DataBind()
		Me.MyGrid.Visible = True
	End Sub

     

 
	''' 

	''' Handles pagination for the GridView control.
	''' 
	Protected Sub gridView_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
		' Set the GridView control to display
		' new page
		Me.MyGrid.PageIndex = e.NewPageIndex
		
		' Obtain the stored procedure data
		' that was saved in session.
		Dim ds As Object = Me.Page.Session("DataSet")
		If (Not IsNothing(ds)) Then
			' Re-bind the GridView control with the data
			' from session.
			Me.MyGrid.DataSource = CType(ds, DataSet)
			Me.MyGrid.DataBind()
			Me.MyGrid.Visible = True
		Else
			Me.MyGrid.Visible = False
		End If
	End Sub