|
Customizing the Data Access Layer
Updated June 5, 2006
Iron Speed Designer V4.0 and later
If you are not yet familiar with the Data Access Layer class hierarchy, the following will help you get started.
The BaseClasses.IrelationalDataAdapter interface defines methods for inserting, updating, deleting, and retrieving records from a database. If you create an application that uses stored procedures in Microsoft SQL Server, the implementation of the IrelationalDataAdapter interface that your application uses is found in the BaseClasses.Data.SqlProvider.StoredProceduresSQLServerAdapter class, located in:
...\<Designer>\BaseClasses\Data\SqlProvider\ StoredProceduresSQLServerAdapter.vb
Basic Data Access Layer Customization Examples
Overriding the GetRecordValues Function
Overriding the InsertRecord Function
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”.
|
Column Name |
Data Type |
Length |
|
ShipperID (PK) Identity |
int |
4 |
|
CompanyName |
nvarchar |
40 |
|
Phone |
nvarchar |
24 |
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
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”.
|
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