Working with Stored Procedures

In most cases developers will not need to use stored procedures to access or modify data. However, there are some cases where this may be desirable or necessary. For cases where it makes more sense to use SQL Stored procedures ORM.NET makes calling and retrieving these results very easy.

To accomplish this ORM.NET creates a wrapped method for each stored procedure defined for a database. The stored procedure methods are generated in the StoredProcedures.cs class file in the do_not_edit directory of the generated data layer.

The examples shown below describe how to work with different types of the wrapped stored procedure methods.

Example 1:  Return SQL OUTPUT parameters from a wrapped stored procedure.

Stored Procedure definition for spGetContactDetails

ALTER PROC spGetContactDetails

@LastName varchar(50),

@City varchar(50) OUTPUT,

@State varchar(50) OUTPUT

AS

SELECT @City=City, @State=state

FROM Teacher t, Contact c

WHERE  t.FKContactId=c.Id

AND   LastName=@LastName

C#

DataManager dm = new DataManager(Config.Dsn);

string CityName = "";              // define and initialize SQL Output params

string StateName = "";

// Pass Input and Output parameters to the Stored procedure

DataSet ds = StoredProcedures.spGetContactDetails("Goldberg",ref CityName, ref StateName);  

Console.WriteLine("Display results: " + CityName + " " + StateName);

Visual Basic.NET

Dim dm As New DataManager(Config.Dsn)

Dim CityName As String = ""           ' define and initialize SQL Output params

Dim StateName As String = ""

' Pass Input and Output parameters to the Stored procedure

Dim ds As DataSet = StoredProcedures.spGetContactDetails("Goldberg",ref CityName, ref StateName)  

Console.WriteLine("Display results: " + CityName + " " + StateName)

The generated wrapper method that ORM.NET generates for  spGetContactDetails (in C#) is:

public static DataSet spGetContactDetails(  System.String LastName, ref System.String City,                                                  

                                            ref System.String State )

{

     ArrayList arrayParams = new ArrayList();

     SqlParameter paramLastName = new SqlParameter( "@LastName", LastName);

     paramLastName.SqlDbType = (SqlDbType) Enum.Parse( typeof(SqlDbType), "varchar", true);

     paramLastName.Direction = ParameterDirection.Input;

     arrayParams.Add( paramLastName );

     SqlParameter paramCity = new SqlParameter( "@City", City);

     paramCity.SqlDbType = (SqlDbType) Enum.Parse( typeof(SqlDbType), "varchar", true);

     paramCity.Direction = ParameterDirection.Output;

     arrayParams.Add( paramCity );

     SqlParameter paramState = new SqlParameter( "@State", State);

     paramState.SqlDbType = (SqlDbType) Enum.Parse( typeof(SqlDbType), "varchar", true);

     paramState.Direction = ParameterDirection.Output;

     arrayParams.Add( paramState );

     DataSet ds = dm.ExecuteProcedure("spGetContactDetails", (SqlParameter[])              

     arrayParams.ToArray(typeof(SqlParameter)));

     City = (System.String) paramCity.Value;

     State = (System.String) paramState.Value;

     return ds;

}

Example 2:  spStudentsByCity Returns the number or students for each City and displays the results.

The Syntax for spStudentsByCity is:

CREATE PROC spStudentsByCity

AS

SELECT City, Count(c.Id) as 'Total'

FROM Student s, Contact c

WHERE s.FKContactId=c.ID

GROUP BY City

C#

DataSet ds1 = StoredProcedures.spStudentsByCity();

foreach (DataRow dr in ds1.Tables[0].Rows)

       Console.WriteLine ("City: " + dr["City"] + " Total: " + dr["Total"]);

Visual Basic.NET

Dim ds1 As DataSet = StoredProcedures.spStudentsByCity()

Dim dr As DataRow

For Each dr in ds1.Tables[0].Rows

       Console.WriteLine ("City: " + dr["City"] + " Total: " + dr["Total"])

Next

Example 3:  The following stored procedure will return a more ADO.NET friendly DataSet

ALTER Proc spStudentContact

@LastName varchar(50)

AS

DECLARE @ContactId int

DECLARE @FKContactId int

SELECT * FROM Student WHERE LastName=@LastName

SELECT @FKContactId=FKContactID FROM Student WHERE LastName=@LastName

SELECT * FROM Contact WHERE ID=@FKContactId

If you structure your stored procedures like the one above you can benefit from using the DataManager object's PopulateObjectsFromDataSet and the Get[Object]CollectionFromDataSet methods shown below.

C#

DataSet ds = StoredProcedures.spStudentContact("Johnson");  // call the stored procedure

String[] arrTables = {"Student","Contact"};   // create the array with the tables included in the

                                                 DataSet

dm.PopulateObjectsFromDataSet(ds,arrTables);

StudentCollection students = dm.GetStudentCollectionFromDataSet();

ContactCollection contacts = dm.GetContactCollectionFromDataSet();

// Work with the objects

foreach(Contact c1 in contacts)

Console.WriteLine("Address: " + c1.Address1);

Visual Basic.NET

Dim ds As DataSet = StoredProcedures.spStudentContact("Johnson")  ' call the stored procedure

' create the array with the tables included in the DataSet

Dim arrTables() As String = {"Student","Contact"}   

dm.PopulateObjectsFromDataSet(ds,arrTables)

Dim students As StudentCollection  = dm.GetStudentCollectionFromDataSet()

Dim contacts As ContactCollection = dm.GetContactCollectionFromDataSet()

' Work with the objects

Dim c1 As Contact

For Each c1 in contacts

Console.WriteLine("Address: " + c1.Address1)

Next