Overriding the InsertRecord Function

You can call your stored procedures by overriding various functions in the Data Access Layer.  This example overrides the InsertRecord function and calls a custom stored procedure (one not created by Iron Speed Designer) using ADO.NET to insert a new record.

The table used in this example is “MyShippers”.

The name of the stored procedure is “spAddShippers”.

CREATE PROCEDURE spAddShippers

     @CompanyName nvarchar(40),

     @Phone nvarchar(24),

     @ShipperID int output

AS

     INSERT

     INTO MyShippers    VALUES  ( @CompanyName,  @Phone)

     set @ShipperID = SCOPE_IDENTITY()

GO

This stored procedure takes two input parameters, CompanyName and Phone, and one output parameter, ShipperID.

Step 1:  Create an application using the MyShippers table.

Step 2:  Locate the MyShippersSqlTable.cs file located in:

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

Step 3:  Override the InsertRecord function in the MyShippersSqlTable class.

C#:

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

using System;

using System.Collections;

using System.Data;

using BaseClasses.Data;

using BaseClasses.Data.SqlProvider;

using BaseClasses.Utils;

Add the code below in the MyShippersSqlTable class.

public override BaseClasses.Data.KeyValue InsertRecord(BaseClasses.Data.TableDefinition table,

          BaseClasses.Data.RecordValue recVal,

          bool[] columnsChanged)

{

     KeyValue retKeyVal = new KeyValue();

     //Step1:Create a connection string

     string connectionString = "server=(local);uid=sa;pwd=; database=Northwind";

     //Step2:Create a connection object, initialize it with the //connection string.

     System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);

     //Step3:create a command object

     System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

     //Step4:create a transaction object

     System.Data.SqlClient.SqlTransaction transaction;

     //Open the connection.

     connection.Open();

     transaction = connection.BeginTransaction();

     command.Transaction = transaction;

     command.Connection = connection;

     try

     {

          command.CommandText = "spAddShippers";

          command.CommandType = CommandType.StoredProcedure;

          System.Data.SqlClient.SqlParameter param;

          //Set input param CompanyName

          param = command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40);

          param.Direction = ParameterDirection.Input;

          param.Value = recVal.ColumnValues[1].Value;

          //Set input param Phone

          param = command.Parameters.Add("@Phone", SqlDbType.NVarChar, 24);

          param.Direction = ParameterDirection.Input;

          param.Value = recVal.ColumnValues[2].Value;

          //Set output param ShipperID

          param = command.Parameters.Add("@ShipperID", SqlDbType.Int);

          param.Direction = ParameterDirection.Output;

          //Step5:Execute the stored Procedure

          command.ExecuteNonQuery();

          transaction.Commit();

          //Step6:Retrieve the ShipperID

          int myShipperID = (int)(command.Parameters["@ShipperID"].Value);

          if (table.IsHasPrimaryKey)

          {

              retKeyVal.AddElement("ShipperID", myShipperID.ToString());

          }

     }

     catch (System.Exception e)

     {

          transaction.Rollback();

     }

     //close the connection

     connection.Close();

     return retKeyVal;

}

Visual Basic .NET:

Public Overrides Function InsertRecord( _

     ByVal table As BaseClasses.Data.TableDefinition, _

     ByVal recVal As BaseClasses.Data.RecordValue, _

     ByVal columnsChanged() As Boolean) _

     As BaseClasses.Data.KeyValue

     Dim retKeyVal As KeyValue = New KeyValue

     'Step1:Create a connection string

     Dim connectionString As String = "server=(local);uid=sa;pwd=; database=Northwind"

     'Step2:Create a connection object, initialize it with the //connection string.

     Dim connection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

     'Step3:create a command object

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

     'Step4:create a transaction object

     Dim transaction As System.Data.SqlClient.SqlTransaction

     'Open the connection.

     connection.Open()

     transaction = connection.BeginTransaction()

     command.Transaction = transaction

     command.Connection = connection

     Try

          command.CommandText = "spAddShippers"

          command.CommandType = System.Data.CommandType.StoredProcedure

          Dim param As System.Data.SqlClient.SqlParameter

          'Set input param CompanyName

          param = command.Parameters.Add("@CompanyName", System.Data.SqlDbType.NVarChar, 40)

          param.Direction = System.Data.ParameterDirection.Input

          param.Value = recVal.ColumnValues(1).Value

          'Set input param Phone

          param = command.Parameters.Add("@Phone", System.Data.SqlDbType.NVarChar, 24)

          param.Direction = System.Data.ParameterDirection.Input

          param.Value = recVal.ColumnValues(2).Value

          'Set output param ShipperID

          param = command.Parameters.Add("@ShipperID", System.Data.SqlDbType.Int)

          param.Direction = System.Data.ParameterDirection.Output

          'Step5:Execute the stored Procedure

          command.ExecuteNonQuery()

          transaction.Commit()

          'Step6:Retrieve the ShipperID

          Dim myShipperID As Integer = CType(command.Parameters("@ShipperID").Value, Integer)

          If table.IsHasPrimaryKey Then

              retKeyVal.AddElement("ShipperID", myShipperID.ToString)

          End If

     Catch ex As Exception

          Dim myexp As String = ex.GetBaseException.ToString

          transaction.Rollback()

     End Try

     'close the connection

     connection.Close()

     Return retKeyVal

End Function

Step 4:  Build and run the application.

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

• InsertRecord

• UpdateRecord

• DeleteRecords

See Also

Part V: Customizing Application Code