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”.
Column Name |
Data Type |
Length |
ShipperID (PK) Identity |
Int |
4 |
CompanyName |
nvarchar |
40 |
Phone |
nvarchar |
24 |
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
Part V: Customizing Application Code