Entity Framework – SQL Server/ Stored Procedure

Background

Let us look further into Entity Frameworks.

As Entity Framework supports the full repertoire of programmable objects in SQL Server, we can use Stored Procedures and Functions, in addition to Tables and Views.

 

Database

SQL Server

Diagram

Stored Procedure

Stored Procedure – dbo.usp_TraceFlagSourceFetch

if object_id('[dbo].[usp_TraceFlagSourceFetch]') is null
begin
	exec('create procedure [dbo].[usp_TraceFlagSourceFetch] as ');
end
go

alter procedure [dbo].[usp_TraceFlagSourceFetch] 
(
	@id int
)
as
begin

	select 
			  tblTFS.id
			, tblTFS.[sequenceNbr]
			, tblTFS.[status]
			, tblTFS.
			, tblTFS.[title]
			, tblTFS.[url]
			, tblTFS.[addedBy]
			, tblTFS.[dateAdded]
	from   [dbo].[traceFlagSource] tblTFS

	where tblTFS.id = @id

end
go

grant execute on [dbo].[usp_TraceFlagSourceFetch]   to [edmgen]
go


 

Visual Studio

Launch Visual Studio and open the Project.

Solution Explorer

Access the Solution Explorer and double click on the edmx file.

EDMX

Here is what our EDMX Diagram looks like.

 

Model Browser

Let us review the Model Browser.

Right click in an empty spot on the diagram panel and from the drop-down menu choose, Model Browser.

Diagram Panel – Menu

Model Browser Panel – What Is?

Access the “Model Browser“.

An edmx contains the following section, Diagrams, Conceptual model, and Data Store.

We have already covered the Diagram.

The Conceptual Model reflects the Object Components ( Entity Types, Complex Types, Enum Types, Association, and Function Imports ).

And, the last section is the Data Store.

 

Model Browser Panel – Original

 

Update Model

Let us update the model so that we can have access to the recently created or exposed Stored Procedure.

Update Model – Choose Your Database Objects and Settings

Before Creating \ Exposing

Here is what the “Update Wizard” looks like before exposing the Stored Procedure.

 

After Creating \ Exposing

Upon creating the SP, usp_TraceFlagSourceFetch, it shows up in the Update Wizard’s Add Tab.

 

Add the Database Stored Procedure

 

Model Browser Panel – Post Adding Database Object

Once we update our Database Model and choose to Add the Stored Procedure, we will revisit the Model Browser.

The pertinent areas are :

  1. Model
    • Function Imports
      • usp_TraceFlagSourceFetch
  2. Store
    • Stored procedures / Functions
      • usp_TraceFlagSourceFetch

 

Function Import – default

During the process of updating the model with the Stored Procedure, the Wizard also creates a default Function Import.

Model Browser – Model – Function Imports – usp_TraceFlagSourceFetch

Properties

Let us quickly review the properties of our auto-created Function Import

Menu

To access the property right click on the Function and select Properties from the dropdown menu.

Properties

Explanation
  1. Stored Procedure Name
    • We see the name of the  underlying Stored Procedure, usp.TraceFlagSourceFetch,
  2. Return Type
    • And, also notice that the system has auto created a Return Type
      • The Return Type is usp_TraceFlagSourceFetch_Result1

 

Properties – Return Type

We are able to access the “Return Type” and review the structure of hat is actually Returned

Edit Function Import

By default the Return is an auto-created type.

It’s naming syntax is <SP>_Result#.

 

Edit Function Import .. #2

We should click on the “Get Column Information” button to get the actual columns and their metadata.

 

Mapping Details

If we access the Model Browser and review the Mapping Details for our selected SP, here is what we see:

 

 

Add New – Function Import

When defining our Stored Procedure we made sure to include the same columns as the table that we are exposing.

And, in so doing, we can explicitly map the Return Type to our table.

Add New – Function Import – Menu

Right click in an empty area on the diagram model and choose the “Add New” \ “Function Import” menu entry.

 

Add New – Function Import – TraceFlagSourceFetch

Explanation

  1. Function Import Name
    • We offered a name and that name is TraceFlagSourceFetch
  2. Return a collection of
    • Entities
      • traceFlagSource

Add New – Function Import – TraceFlagSourceFetch – Get Column Information

Upon clicking on the “Get Column Information” button, we are able to review the list of columns.

 

Model Browser Panel – Post Adding a “Function Import”

Explanation

Quick review, we have two Function Imports:

  1. TraceFlagSourceFetch
    • The one we defined ourselves
  2. usp_TraceFlagSourceFetch
    • The one auto-created for us

 

 

Visual Studio

Launch Visual Studio and open the Project.

Solution Explorer

Access the Solution Explorer and double click on the edmx file.

EDMX

Here is what our EDMX Diagram looks like.

 

Model Browser

Let us review the Model Browser.

Right click in an empty spot on the diagram panel and from the drop-down menu choose, Model Browser.

Diagram Panel – Menu

Model Browser Panel – What Is?

Access the “Model Browser“.

An edmx contains the following section, Diagrams, Conceptual model, and Data Store.

We have already covered the Diagram.

The Conceptual Model reflects the Object Components ( Entity Types, Complex Types, Enum Types, Association, and Function Imports ).

And, the last section is the Data Store.

 

Model Browser Panel – Original

 

Update Model

Let us update the model so that we can have access to the recently created or exposed Stored Procedure.

Update Model – Choose Your Database Objects and Settings

Before Creating \ Exposing

Here is what the “Update Wizard” looks like before exposing the Stored Procedure.

 

After Creating \ Exposing

Upon creating the SP, usp_TraceFlagSourceFetch, it shows up in the Update Wizard’s Add Tab.

 

Add the Database Stored Procedure

 

Model Browser Panel – Post Adding Database Object

Once we update our Database Model and choose to Add the Stored Procedure, we will revisit the Model Browser.

The pertinent areas are :

  1. Model
    • Function Imports
      • usp_TraceFlagSourceFetch
  2. Store
    • Stored procedures / Functions
      • usp_TraceFlagSourceFetch

 

Function Import – default

During the process of updating the model with the Stored Procedure, the Wizard also creates a default Function Import.

Model Browser – Model – Function Imports – usp_TraceFlagSourceFetch

Properties

Let us quickly review the properties of our auto-created Function Import

Menu

To access the property right click on the Function and select Properties from the dropdown menu.

Properties

Explanation
  1. Stored Procedure Name
    • We see the name of the underlying Stored Procedure, usp.TraceFlagSourceFetch,
  2. Return Type
    • And, also notice that the system has auto created a Return Type
      • The Return Type is usp_TraceFlagSourceFetch_Result1

 

Properties – Return Type

We are able to access the “Return Type” and review the structure of hat is actually Returned

Edit Function Import

By default the Return is an auto-created type.

It’s naming syntax is <SP>_Result#.

 

Edit Function Import .. #2

We should click on the “Get Column Information” button to get the actual columns and their metadata.

 

Mapping Details

If we access the Model Browser and review the Mapping Details for our selected SP, here is what we see:

 

 

Add New – Function Import

When defining our Stored Procedure we made sure to include the same columns as the table that we are exposing.

And, in so doing, we can explicitly map the Return Type to our table.

Add New – Function Import – Menu

Right click in an empty area on the diagram model and choose the “Add New” \ “Function Import” menu entry.

 

Add New – Function Import – TraceFlagSourceFetch

Explanation

  1. Function Import Name
    • We offered a name and that name is TraceFlagSourceFetch
  2. Return a collection of
    • Entities
      • traceFlagSource

Add New – Function Import – TraceFlagSourceFetch – Get Column Information

Upon clicking on the “Get Column Information” button, we are able to review the list of columns.

 

Model Browser Panel – Post Adding a “Function Import”

Explanation

Quick review, we have two Function Imports:

  1. TraceFlagSourceFetch
    • The one we defined ourselves
  2. usp_TraceFlagSourceFetch
    • The one auto-created for us

 

Code

We have to stub codes to share:

The first one calls the Function created by default.

And, the other one calls the function we created and casted to our Table.

Code – Default Function


private void listTraceFlagSourceUseSPImplicit(int iID)
{ 

	//invoke the default Function
	var varTFS = dbDE.usp_TraceFlagSourceFetch(iID);

	//Iterate list
	//Remember that it Returns data as a usp_TraceFlagSourceFetch_Result1
	foreach (usp_TraceFlagSourceFetch_Result1 objTFS in varTFS)
	{

		// prepare UI output
		strLog
			= String.Format
				(
					  FORMAT_TRACESOURCE_LIST
					, objTFS.source
					, objTFS.title
					, objTFS.url
				);

		Console.WriteLine(strLog);

		// Output Line Break
		Console.WriteLine(CHAR_LINEBREAK);

   } //foreach


} //listTraceFlagSourceUseSPImplicit

Code – Explicit Function


public void listTraceFlagSourceUseSPExplicit(int iID)
{

	//invoke the explicit Function
	//Remember that the data return is strongly type
	//and it is a table
	var varTFS = dbDE.TraceFlagSourceFetch(iID);

	//Iterate list
	//Using foreach expose each list element in varTF
	foreach (traceFlagSource objTFS in varTFS)
	{

		// prepare UI output
		strLog
			= String.Format
				(
					  FORMAT_TRACESOURCE_LIST
					, objTFS.source
					, objTFS.title
					, objTFS.url
				);

		Console.WriteLine(strLog);

		// Output Line Break
		Console.WriteLine(CHAR_LINEBREAK);

	} //foreach
	


} //listTraceFlagSourceUseSPExplict


Sql Server Profiler

Let us target SQL Server Profiler and see what our payload looks like:

Explanation:

  1. Event Class
    • RPC:Completed
    • TextData
      • Sample
        • exec [dbo].[usp_TraceFlagSourceFetch] @id=2559
    • CPU
      • 0
    • Reads
      • 2
    • Writes
      • 0

 

Listening

Behind every post there is a story.

But like Rick, this is how I will have to end this Story.

Slick Rick – Bedtime Story
Link

He dropped the gun, so went the glory
And this is the way I have end this story

Just another case ’bout the wrong path,
Straight ‘n narrow or yo’ soul gets cast(?)

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s