Overriding the GetRecordValues Function

You can call custom stored procedures by overriding various functions in the Data Access Layer.  This example overrides the GetRecordValues function, calls a custom stored procedure (one not created by Iron Speed Designer) using ADO.NET, and populates a table control.

The table used in this example is “MyShippers”.

The name of the stored procedure is “spGetShippersRecords”.

CREATE PROCEDURE spGetShippersRecords

AS

SELECT * FROM MyShippers

ORDER BY CompanyName

GO

Step 1:  Create an application using the MyShippers table.

Step 2:  Locate the MyShippersSqlTable.cs file.

...\<Application Folder>\App_Code\Data Access Layer\MyShippersSqlTable.cs

Step 3:  Override the GetRecordValues function in the MyShippersSqlTable class in MyShippersSqlTable.cs or .vb.

C#:

Insert the following lines at the top of.MyShippersSqlTable.cs.

using BaseClasses.Data;

using BaseClasses.Data.SqlProvider;

using System.Data;

using System.Data.SqlClient;

Add the code below in the MyShippersSqlTable class.

public override System.Collections.ArrayList GetRecordValues(BaseClasses.Data.TableDefinition table,

          BaseClasses.Data.SqlBuilderColumnSelection requestedSelection ,

          BaseClasses.Data.SqlBuilderColumnSelection workingSelection,

          BaseClasses.Data.SqlBuilderColumnSelection  distinctSelection,

          BaseClasses.Data.BaseFilter filter,

          BaseClasses.Data.OrderBy sortOrder,

          int startIndex,

          int count,

          ref int totalCount)

{

     System.Collections.ArrayList resultList = new System.Collections.ArrayList();

     int i;

     System.Data.SqlClient.SqlDataReader reader;

     //Step1: Create a sql connection object

     System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection();

     //Step2: Set the connection string

     objConn.ConnectionString = "server=(local); database=Northwind; uid=sa;pwd=;";

     //Step3: Open the connection

     objConn.Open();

     //Step4. Create a sql command object

     System.Data.SqlClient.SqlCommand objCmd = new

          System.Data.SqlClient.SqlCommand("spGetShippersRecords",objConn);

     //Step5: Set the CommandType as Stored Procedure

     objCmd.CommandType = System.Data.CommandType.StoredProcedure;

     //Step6: Execute the stored Procedure

     reader = objCmd.ExecuteReader();

     try

     {

          object[] vals;

          vals =(object[])System.Array.CreateInstance(typeof(object), reader.FieldCount);

          while(reader.Read())

          {

              //create a RecordValue obj

              RecordValue recVal = new RecordValue(vals.Length);

              int numVals;

              numVals = reader.GetValues(vals);

              //Get the column values and set the values in the RecordValue object

              for( i = 0 ;i<= (numVals - 1);i++) {

                   recVal.ColumnValues[i] = new ColumnValue(reader.GetValue(i));

                   recVal.ColumnValues[i].Value = reader.GetValue(i);

              }

              //Add the RecordValue object to the ArrayList 

              resultList.Add(recVal);

          }

     }

     catch(System.Exception ex)

     {

          string errStr = ex.GetBaseException().ToString();

     }

     //Close the reader object

     reader.Close();

     //Close the connection object

     objConn.Close();

     totalCount = resultList.count;

     //Return the ArrayList

     return resultList;

}

Visual Basic .NET:

Public Overrides Function GetRecordValues( _

     ByVal table As BaseClasses.Data.TableDefinition, _

     ByVal requestedSelection As BaseClasses.Data.SqlBuilderColumnSelection, _

     ByVal workingSelection As BaseClasses.Data.SqlBuilderColumnSelection, _

     ByVal distinctSelection As BaseClasses.Data.SqlBuilderColumnSelection, _

     ByVal filter As BaseClasses.Data.BaseFilter, _

     ByVal sortOrder As BaseClasses.Data.OrderBy, _

     ByVal startIndex As Integer, ByVal count As Integer, ByRef totalCount As Integer) _

     As System.Collections.ArrayList

     Dim resultList As New ArrayList

     Dim i As Integer

     Dim reader As System.Data.SqlClient.SqlDataReader

     'Step1: Create a sql connection object       

     Dim objConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection

     'Step2: Set the connection string

     objConn.ConnectionString = "server=(local); database=Northwind; uid=sa;pwd=;"

     Try

          'Step3: Open the connection

          objConn.Open()

          'Step4. Create a sql command object

          Dim objCmd As System.Data.SqlClient.SqlCommand = New

              System.Data.SqlClient.SqlCommand("spGetShippersRecords", objConn)

          'Step5: Set the CommandType as Stored Procedure

          objCmd.CommandType = System.Data.CommandType.StoredProcedure

          'Step6: Execute the stored Procedure

          reader = objCmd.ExecuteReader()

          Dim vals() As Object

          vals = DirectCast(System.Array.CreateInstance(GetType(Object), reader.FieldCount), Object())

          While reader.Read()

              'create a RecordValue obj

              Dim recVal As RecordValue = New RecordValue(vals.Length)

              Dim numVals As Integer

              numVals = reader.GetValues(vals)

              'Get the column values and set the values in the RecordValue object

              For i = 0 To (numVals - 1)

                   recVal.ColumnValues(i) = New ColumnValue(reader.GetValue(i))

                   recVal.ColumnValues(i).Value = reader.GetValue(i)

              Next

              'Add the RecordValue object to the ArrayList 

              resultList.Add(recVal)

          End While

     Catch ex As Exception

          Dim myStr As String = ex.GetBaseException.ToString

     End Try

     'Close the reader object

     reader.Close()

     'Close the connection object

     objConn.Close()

     totalCount = resultList.count

     'Return the ArrayList

     Return resultList

End Function

Step 4:  Build and run the application.

This stored procedure does not have input or output parameters.  The result obtained by executing the stored procedure is passed as an ArrayList which is used to populate the table control.

Note that this simple example does not implement paging, sorting or search functionality.  If you want your table control to support these features, you will have to add custom code.

Other functions you can override in the Data Access Layer are:

• InsertRecord

• UpdateRecord

• DeleteRecords