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

 

Code


      var varTFS = dbDE.usp_TraceFlagSourceFetch(iID);

            //Iterate list
            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

 



Dashboard
Home
Comments I’ve Made
Site Stats
My Blogs
Blogs I Follow
Akismet Stats
Omnisearch

Store
Store
Premium Themes
My Domains
Manage Purchases
Billing History

Posts
All Posts
Add New
Categories
Tags
Copy a Post

Media
Library
Add New

Links
All Links
Add New
Link Categories

Pages
All Pages
Add New
Copy a Page

Comments

Feedback
Polls
Ratings
Feedback

Appearance
Themes
Customize
Widgets
Menus
Header
Background
AMP
Mobile

Plugins

Users
All Users
Invite New
My Profile
Personal Settings

Tools
Available Tools
Import
Export
Delete Site

Settings
General
Writing
Reading
Discussion
Media
Sharing
Polls
Ratings
AdControl
Email Post Changes
OpenID
Webhooks
Collapse menu
My Site
+ ADD NEW WORDPRESS
Learning in the Open
learningintheopen.org
Stats Stats
Plan
Premium
Publish
Blog Posts
Add
Pages
Add
Media
Add
Personalize
Themes
Customize
Configure
Sharing
People
Add
Plugins
Domains
Add
Settings
WP Admin
Reader
Streams
Followed Sites
Manage
Discover
Search
Recommendations
My Likes



Daniel Adeniji
@danieladeniji
Sign Out
Profile
My Profile
Account Settings
Manage Purchases
Security
Notifications
Special
Get Apps
Next Steps
Help
Write
Log Out
Help Screen Options
Edit Post Add New
The backup of this post in your browser is different from the version below.	 Restore the backup
This will replace the current editor content with the last backup version. You can use undo and redo in the editor to get the old content back or to return to the restored version.
Dismiss this notice.
There's an easier way to create on WordPress.com. Switch to the improved editor.
Post updated. View post
Dismiss this notice.
Enter title here
Entity Framework - SQL Server/ Stored Procedure
Permalink: https://learningintheopen.org/2017/05/26/entity-framework…stored-procedure/ ‎Edit Get Shortlink
 Add Media Add PollAdd Poll  Add Contact Form  Add LocationVisualText
bilinkb-quotedelinsimgulollicodemoreclose tagsproofread

<h1>Background</h1>

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.

&nbsp;

<h1>Database</h1>


<h2>SQL Server</h2>


<h3>Diagram</h3>

<a href="https://learningintheopen.org/2017/05/26/entity-framework-sql-server-stored-procedure/diagram_traceflag/#main" rel="attachment wp-att-53111"><img class="alignnone wp-image-53111" src="https://danieladeniji.files.wordpress.com/2017/05/diagram_traceflag.png" alt="" width="1207" height="524" /></a>

<h3>Stored Procedure</h3>


<h4>Stored Procedure - dbo.usp_TraceFlagSourceFetch</h4>



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

 

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(?)

 

 

Entity Framework – Application – Version # – Console & ASP.Net Applications

Background

Let us go a tiny bit further into our Entity Framework application.

But, before we go too far let us review the Version # of the Entity Framework library that is being used.

 

Diagnostic

File System

Let us review the File System, specifically the folder where the application is installed.

Entity Framework Libraries

Entity Framework libraries are bundled in dlls.

There is the baseline EntityFramework.dll and each targeted database platform will have its own dll.

Screen Shots

Console App
Image

EntityFramework.dll

EntityFramework.SqlServer.dll

 

Tabulate
Type File Product Version
 Core
 EntityFramework.dll  6.1.3-40302
 SQL Server
 EntityFramework.SqlServer.dll  6.1.3-40302

 

 

Web App
Image

 

 

Running Application

Resource Monitor

Start the Application and start the OS built-in Resource Monitor.

Image

Console App

Tabulate

Associated Modules
Type Module Name Version# Full Path
 Associated Modules
 EntityFramework.ni.dll 6.1.40302.0 C:\Windows\Assembly\NativeImages_v4.0.30319_32\EntityFramework\…\EntityFramework.ni.dll

 

 

 

Web Site App
w3wp.exe

 

Tabulate

Associated Handles
Type File Product Version
 Core
EntityFramework.DLL  4.0.30319
 SQL Server
EntityFramework.SqlServer.Dll  4.0.30319.33440

 

 

Associated Modules
Type File Product Version
 Core
System.Data.Entity.ni.dll  4.6.1087.0
System.Data.Linq.ni.ll  4.6.108.07
 SQL Server
 System.Data.OracleClient.dll  4.0.30319.33440
 System.Data.OracleClient.ni.dll  4.0.30319.33440

 

 

Summary

Both the libraries\dll files bundled with the Application and the modules loaded into memory reveal that the EntityFramework Library is in-use by the Application.

For our console application, the specific version of EntityFramework Library is Version 6.1.40302.0

And, for the Web App, the version is 4.6.1087.0

BTW, the CLR Version is 4.0.30319 32 bit.

Entity Framework – Using Visual Studio – Basic Application / Day 1

Background

In an earlier post, we spoke about how to generate an Entity Framework model through the command line.

 

Turnabout

And, our hope was to use a simple text editor along with command line tools such as msbuild and layer upon layer continue to build out our little application.

But, so little is publicly documented and shared about that approach and we quickly found in terms of straightforwardness, the plumbing available in Visual Studio will likely be best.

 

Visual Studio

Let go with Visual Studio.

 

Launch Visual Studio

Version – Microsoft Visual Studio Community 2015

New Project

Menu

We create a new project by clicking on the menu item ( File New/ Project ).

Template

GUI

Explanation
  1. Template
    • Visual C#\Windows\Console Application
  2. Name
    • Name :- lab001VS
  3. Location
    • C:\Personal\Blog\Microsoft\.net\entityFramework

Add

Add \ New Item..

Access the Solution Explorer, choose the Project, right click your selection and choose the Add Option

 

Add “ADO.Net Entity Data Model”

From the list of items, choose “ADO.Net Entity Data Model“.

 

Entity Data Model Wizard

The Entity Data Model Wizard appears….

Entity Data Model Wizard – Choose Model Contents

We are taking Database First approach, that is, we have a database defined, and will generate the entity code from the DB.

Please choose “EF Designer from database”

Entity Data Model Wizard – Choose Your Data Connection – Original

Here is the original state of the “Choose Your Data Connection” screen.

Please choose the “New Connection…” button.

 

Entity Data Model Wizard – Connection Properties

In the Connection Properties screen:

  1. Data Source :- Microsoft SQL Server (SqlClient)
  2. Server name :- .\v2014
  3. Logon to the Server :- SQL Server Authentication
  4. user name :- edmgen
  5. Password :- < enter password>
  6. Tick the “Save my password” checkbox
  7. Connect to a database :- Chose DBDiag
  8. Click the “Test Connection” button to validate the connection

 

Entity Data Model Wizard – Choose Your Database Objects And Settings

 

Connection Properties – Advanced Properties

Connection Properties – Advanced Properties – Before

 

Connection Properties – Test connection succeeded

 

Entity Data Model Wizard – Choose your Data Connection

Return to the “Choose your Data Connection” window.

 

Choices
  1. Connection info :-
    • [Server\instance].[database].[schema]
  2. Yes, include the sensitive data in the connection string
  3. Save connection settings in App.Config as …
    • DBDiagEntities

 

Entity Data Model Wizard – Choose your Version

  1. Which version of Entity Framework do you want to use?
    • Entity Framework 6.x
    • Entity Framework 5.0

 

Entity Data Model Wizard – Choose your Database Objects and Settings

Explanation
  1. Our SQL Account only has to the traceFlag table

 

 

 

Connection Properties – Advanced Properties

Connection Properties – Advanced Properties – Before

Connection Properties – Test connection succeeded

 

Entity Data Model Wizard – Choose your Version

  1. Which version of Entity Framework do you want to use?
    • Entity Framework 6.x
    • Entity Framework 5.0

 

 

Explanation

As this is a new test Application and we do not have a need to preserve existing code, we will go with the latest EF version, and that is Version 6.0.

 

Security Warning

There are a couple of security warnings that pop up and we click the OK to move on.

 

 

EDMX

The wizard does a bit of behind the scene work and publishes the model and the .Net class files

 

 

Source Code

With all that plumbing work out of the way, let us write a couple of elementary code that will invoke the method availed by EF entities.

 

Outline

  1. Display a simple UI that allows the user to List, Add, Edit, and Delete records
  2. List
    • List Trace Flags
  3. Add
    • Add a Trace Flag
  4. Edit
    • Edit a Trace Flag
  5. Remove
    • Remove a Trace Flag

 

Code

Browse


  public void browse()
        {

            //get all entities from entity traceflags
            //result should be captured in a variant variable tfQuery
            //ordering will likely be based on the DB table's primary key ( PK )
            var tfQuery = from tf in dbDE.traceFlags
                          select tf
                           ;

            //copy data into list object
            listTF = tfQuery.ToList();

            //Iterate list
            //Using foreach expose each list element in varTF
            foreach (var varTF in listTF)
            {

                //cast Var object to traceflag object
                objTF = (traceFlag)varTF;
                
                // prepare UI output
                strLog
                    = String.Format
                        (
                              FORMAT_BROWSE_DETAIL
                            , objTF.id
                            , objTF.description
                        );

                Console.WriteLine(strLog);

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

            } //foreach

        } //browse

Add


 public void add()
        {
         
            Console.WriteLine("Get Trace Flag ID");
            strID = Console.ReadLine();

            if (strID == "")
            {
                return;
            }

            Console.WriteLine("Get Trace Flag Description");
            strDescription = Console.ReadLine();

            objTF = new traceFlag();

            objTF.id = Int32.Parse(strID);
            objTF.description = strDescription;
        
            dbDE.traceFlags.Add(objTF);

            dbDE.SaveChanges();

            objTF = null;


        } //add

Edit



  public void edit()
        {

            
            Console.WriteLine("Get Trace Flag ID");
            strID = Console.ReadLine();

            if (strID == "")
            {
                return;
            }

            iID = Int32.Parse(strID);

            var tfQuery = from tf in dbDE.traceFlags
                          where tf.id == iID
                          select tf
                ;

            objTF = new traceFlag();

            try
            {
                objTF = tfQuery.Single();
            }
            /*
                When ID Filtering is attempted, and matching record not found
                an exception, System.InvalidOperationException, is raised,
                catch and gracefully handle that exception
            */ 
            catch (System.InvalidOperationException exIOE)
            {
                strLog = String.Format
                    (
                        FORMAT_OPERATION_DELETE_FAILED
                        , "edit"
                        , strID
                        , exIOE.HResult
                        , exIOE.Message
                    );

                Console.WriteLine(strLog);

                //deallocated created object
                objTF = null;

                return;

            }


            //Get Trace Flag Description
            Console.WriteLine("Get Trace Flag Description");
            strDescription = Console.ReadLine();

            //If Description is empty, then exit
            if (strDescription == String.Empty)
            {

                //deallocated created object
                objTF = null;

                return;

            }

            //set Trace Flag Description
            objTF.description = strDescription;

            // save changes to db
            dbDE.SaveChanges();

            //deallocated created object
            objTF = null;



        } //edit


Remove



        public void remove()
        {

            Console.WriteLine("Get Trace Flag ID");
            strID = Console.ReadLine();

            if (strID == "")
            {
                return;
            }

            iID = Int32.Parse(strID);

            var tfQuery = from tf in dbDE.traceFlags
                          where tf.id == iID
                          select tf
                ;


            objTF = new traceFlag();

            try
            {
                objTF = tfQuery.Single();
            }
            /*
                When ID Filtering is attempted, and matching record not found
                an exception, System.InvalidOperationException, is raised,
                catch and gracefully handle that exception
            */
            catch (System.InvalidOperationException exIOE)
            {
                strLog = String.Format
                    (
                        FORMAT_OPERATION_DELETE_FAILED
                        , "deletion"
                        , strID
                        , exIOE.HResult
                        , exIOE.Message
                    );

                Console.WriteLine(strLog);

                //deallocated created object
                objTF = null;

                return;

            }

            //attach created object to trace flags entity
            dbDE.traceFlags.Attach(objTF);

            //mark the object for deletion
            dbDE.traceFlags.Remove(objTF);

            // save changes to db
            dbDE.SaveChanges();

            //deallocated created object
            objTF = null;

        } //remove



 

Entity Framework – Generate Model & Mapping Files Using edmgen

Background

As always we will take to the command line and start building out a simple Entity Framework application.

 

What is entity framework?

Wikipedia
Link

The Entity Framework is a set of technologies in ADO.NET that support the development of data-oriented software applications. Architects and developers of data-oriented applications have typically struggled with the need to achieve two very different objectives. They must model the entities, relationships, and logic of the business problems they are solving, and they must also work with the data engines used to store and retrieve the data. The data may span multiple storage systems, each with its own protocols; even applications that work with a single storage system must balance the requirements of the storage system against the requirements of writing efficient and maintainable application code.

The Entity Framework enables developers to work with data in the form of domain-specific objects and properties, such as customers and customer addresses, without having to concern themselves with the underlying database tables and columns where this data is stored. With the Entity Framework, developers can work at a higher level of abstraction when they deal with data, and can create and maintain data-oriented applications with less code than in traditional applications. Because the Entity Framework is a component of the .NET Framework, Entity Framework applications can run on any computer on which the .NET Framework (starting with version 3.5 SP1) is installed.

 

Create Model

Let us create the model. Again, the model is an abstract model of the entities and the relationships between the entities ( Foreign Keys ).

 

Visual Studio

Of course, we can do so via Visual Studio.

But, that is too easy and we might miss some of the little things.

 

Edmgen

What is edmgen?

Edmgen are actually two words intermingled, edm and gen.

edm stands for Entity Data Model and gen is generation.

It is a command line tool.

 

Where is edmgen located?

Depending on the version of .Net you are running, you will need to target a different folder.

It is easy enough to open up a command shell and find edmgen.exe

 

Code

where /R c:\windows\microsoft.net edmgen.exe

 

Output

 

Issue Sample edmgen

Code


set "_dirBin=C:\Windows\Microsoft.NET\Framework64\v4.0.30319\"
set "_appName=edmgen.exe"
set _appNameFull=%_dirBin%\%_appName%

set "_appMode=FullGeneration"

set "_language=csharp"
set "_project=dbdiag"

rem set "_csIS=Data Source=.\v2014;Database=DBDiag;Application Name=diagConsole;  Integrated Security=SSPI;"
set "_csSN=Data Source=.\v2014;Database=DBDiag;Application Name=diagConsole; uid=edmgen; pwd=62aprAcEcRaq;"
set "_cs=%_csSN%"

%_appNameFull% /mode:%_appMode% /connectionstring:"%_cs%" /language:%_language% /project:%_project%

Output

 

Explanation

  1. Loading database information
    • App connects to database
    • And, determines the list of database objects available to the DB account in use
  2. Generates
    • Generates ssdl
    • Generates msl
    • Generates csdl

 

Summary

This is obviously only the first step.

We have generated the model files.

In follow-up posts, we will review the generated files, discuss how to use and integrate into our main application, update the generated files to include new DB Objects or inherit DB changes, and target different Db environment, i.e. QA and Prod.

 

Sql Server Management Studio ( SSMS ) – Query Results – Max Column Length

Background

Ran a query that gives me the most expensive queries.

As I try to capture the SQL Column I am finding out that the results is chopped off.

 

TroubleShooting

SQL Server Management Studio

Query Results

Access the Options Menu and transversed to Query Results \ SQL Server \ Results to Grid

resultstogrid

And, determine the max Non XML data is 65535 Characters.

 

What is Column Length?

Changed the query to emit the column length…

statementlength

Explanation

  1. Our most expensive query is 147,020 characters
  2. Some other queries are 16,736 and another is 11,133

 

StackOverflow

Googled for a fix and as always found one via Stackoverflow.

Link

Copy Current Cell

stackoverflow_question-11897950

Visualize

stackoverflow_question-11897950_visualize

 

SSMSBoost

Journeyed to SSMS Boost web site here

Available Binaries

Downloaded version Specific

download

Download & Install

Downloaded and Installed SSMS Boost

 

License

Retrieve User/machine Has Token

Retrieved User/Machine Token through running SSMS and accessing menu items SSMSBoost / About License.

ssmsboost-aboutlicense-croppedup

 

Request Community License

Requested Community License by going here.

requestingfreelicense-cropped-up

Here is the Free License Request Form that comes up.

requestlicense-cropped

 

Received License

Received license over email

 

Applied License

 

Usage

Ran the query again.

Grid

copycurrentcell1to1-cropped-up

 

Select Grid Cell

Selected the Grid Cell and right clicked on our selection.

Chose “Copy current cell 1:1” from the dropdown menu.

We were able to capture our cell’s content.

 

Editor

NotePad++

Was able to paste into Notepad++.

Contents

notepadplusplus-summary

Summary

In Notepad++, accessed the menu item View \ Summary.

Here is what our summary window looks like:

notepadplusplus-summary-20170216-0604pm

Explanation:

We have about over 43 thousand characters.

 

Dedicated

Dedicated to SSMSBoost’s directory Andrei Rantsevich.

 

Summary

From the Query’s contents:

(@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 bit,@p__linq__3 uniqueidentifier,@p__linq__6 bit,@p__linq__4 uniqueidentifier,@p__linq__5 bit)SELECT
[Project40].[Id] AS [Id],
[Project40].[C1] AS [C1],
[Project40].[Id2] AS [Id1],
[Project40].[Id5] AS [Id2],
[Project40].[Id1] AS [Id3]

we can see that the query is not handwritten, but generated via Microsoft .Net Linq.

Microsoft – WCF/OData – Deploying Services to IIS

Introduction

There are a couple of paths we can take towards deploying our web services.

Prerequisite – Msdeploy

Introduction

I really enjoyed realizing the benefits of Microsoft MSDeploy.  It allows us to deploy to the local server and to remote hosts, as well.

Here is a list of arguments that are supported:

 Argument  Meaning
/T Simulates deployment
/Y Actual deployment
/M Destination Name of remote computer
/U Destination user name
/P Destination password
/A Authentication – NTLM/Basic

Visual Studio Publishing

We can publish from Visual Studio and here are the steps to take:

  • Launch Visual Studio
  • Load the Project
  • Access the “Solution Project”
  • Select the project, make sure to select the Project and not the Solution
  • Right click on your selection
  • And, from the drop-down menu, select “Publish…”

Publish Web Application – Profile

The first screen upon launching “Publish Web Application” is the screen pasted below:

publishWebApplication

As we do not have existing profiles, we will access the drop-down menu and select the “New” option.

Publish Web Application – Profile

In the screen below, we have chosen the name “SoftApps”; for our Profile.

publishWebApplication - NewProfile

Publish Web Application – Connections

The connection method is the most important screen.  It in we will choose the publish method, the service URL, and the Site/Application.

To just show how important it is, I burnt up a couple of hours, upon entering an incorrect “Site/application” in the screen below.

publishWebApplication - Connection - WebDeploy

The right choice for “Site/application” is:

publishWebApplication - Connection - WebDeploy (good choice)

The important distinction been that the former choice has SoftAppsWCF and the corrected choice has “Default Web Site/SoftAppsWcfService“.

IIS has a default web site known as “Default Web Site”.  And, you likely want to use that one.  On the other hand, If you are an expert IIS Administrator, you very well might have created a new web site that you want to indicate here.

Publish Web Application – Settings

The next important screen is the “Settings” screen.  In it, we choose between “debug” and “release”.

During your first iterations, I will suggest that you choose the Debug choice.

The next important decision is confirming your Database Connection Strings.  Please keep in mind that if you have the “Use this connection string at runtime (update destination web.config)” checked, you may end-up over-writing previously carefully tailored DB connection profiles on targeted hosts.

publishWebApplication - Settings

Publish Web Application – Preview

The preview screen is next.

publishWebApplication - Preview

Msdeploy

Background

Pasted below is the output from *.deploy.cmd:   

We unsuccessfully targeted a remote host.



wcfServiceSoftApps.deploy.cmd /T /M:WebServerRemote

SetParameters from: "WcfServiceSoftApps.SetParameters.xml"
You can change IIS Application Name, Physical path, connectionString
or other deploy parameters in the above file.
-------------------------------------------------------
 Start executing msdeploy.exe
-------------------------------------------------------
 "C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='WcfServiceSoftApps.zip' -dest:auto,computerName="WebServerRemote",includeAcls="Fal
se" -verb:sync -disableLink:AppPoolExtension -disableLink:ContentExtension -disa
bleLink:CertificateExtension -setParamFile:"WcfServiceSoftApps.SetPar
ameters.xml" -whatif
Info: Using ID '2158a280-82ae-462d-9117-683b7c2aaf35' for connections to the rem
ote server.
Error Code: ERROR_DESTINATION_NOT_REACHABLE
More Information: Could not connect to the remote computer ("WebServerRemote"). On 
the remote computer, make sure that Web Deploy is installed and that the required 
process ("Web Deployment Agent Service") is started.  Learn more at: 
http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_DESTINATION_NOT_REACHABLE.
Error: The remote server returned an error: (404) Not Found.
Error count: 1.

… due to the error code (ERROR_DESTINATION_NOT_REACHABLE), I realized I needed to do a couple of things:

  • Install & configure WebDeploy
  • Configure hosts firewall rules to allow network port connections

Msdeploy Download Matrix

I think you can go with the latest version supported by your OS.

And, for most people starting from Windows XP to Windows 2008 that will be MSDeploy version 3.

Version URL OS Version
Version 1.1 http://www.microsoft.com/en-us/download/details.aspx?id=24709 (x86)http://www.microsoft.com/en-us/download/details.aspx?id=4148 (x64) Windows 7, Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP
Version 2 http://www.microsoft.com/en-us/download/details.aspx?id=25230 Windows 7, Windows Server 2003 Service Pack 2, Windows Server 2008, Windows Server 2008 R2 SP1, Windows Vista Service Pack 2, Windows XP Service Pack 3
Version 3 http://www.microsoft.com/en-us/download/details.aspx?id=30436 Windows 7 Professional, Windows Server 2003 Service Pack 2, Windows Server 2008, Windows Server 2008 R2 SP1, Windows Server 2012 Release Candidate, Windows Vista, Windows XP
Version 3.5  http://www.iis.net/downloads/microsoft/web-deploy

Download Msdeploy

Download the latest available MSDeploy for your deployment host.  As I often say, in terms of “Enterprise” Development and Deployment roadmap, Microsoft’s train is really a high speed train.  Yesterday it was called MSDeploy, Today is called WebPI, and next week it will yet be called something else.

So as I am on yesterday’s timeline, I will stay with MSdeploy and download it from http://www.microsoft.com/en-us/download/details.aspx?id=30436.

The version is v3.

As an aside and as a satire, I am trying to play music on this MS Windows 2012 box, and I do not even know how to get to MS Media Player.  So what to do, but yet again Goggle for help!

Salvation came from David Trounce:

Add Feature … User Interface and Infrastructure … Desktop Experience. This is similar to Server 2008 R2.

http://social.technet.microsoft.com/Forums/windowsserver/en-US/a816bbdc-5a9c-4cf9-aa8c-090a61da3e3a/need-to-install-windows-media-player-on-windows-server-2012-release-candidate-many-apps-requires?forum=winserver8gen

… Must not be my time, as still no WMPlayer.  Will just go back to Apple’e iTune, as that is installed and working.

About an hourly later, as I closed some of the multiple screens that I had left opened, discovered that I needed to restart.

This was indicated as I closed “Sever Manager”.  And, noticed the information detailed below:

ServerManager-IIS-RestartPending

That might be why no WindowsMediaPlayer.

Install Msdeploy

On the targeted host, here is a sample DOS Batch script that does the following:

  • It installs WebDeploy_2_10_x85_en-US.msi
  • The destination port is 8080 and the targeted Virtual Directory is /MSDeployAgentService2
  • And, it restarts the “Web Deployment Agent Service” (MsDepSvc)


rem msiexec /I <msi_filename> /passive ADDLOCAL=ALL LISTENURL=http://+:8080/MsDeployAgentService2/
msiexec /I WebDeploy_2_10_x86_en-US.msi /passive ADDLOCAL=ALL LISTENURL=http://+:8080/MsDeployAgentService2/

net stop msdepsvc /y
net start msdepsvc

Validate Msdeploy

Once the deployment package is prepared, test it out by issuing the *.deploy.cmd and passing in the /T command argument.

The *.deploy.cmd does a few important things:

  • It reads the *.SetParameters.xml file and one is able to change the Web Application Name, the Physical Path, and DB ConnectionString

When I issued “WcfServiceSoftApps.deploy.cmd /T” to test out my install, I get the sampled output shown below:


SetParameters from: "WcfServiceSoftApps.SetParameters.xml"
You can change IIS Application Name, Physical path, connectionString
or other deploy parameters in the above file.
-------------------------------------------------------
 Start executing msdeploy.exe
-------------------------------------------------------
 "C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='WcfServiceSoftApps.zip' -dest:auto,includeAcls="False" -verb:sync -disa
bleLink:AppPoolExtension -disableLink:ContentExtension -disableLink:CertificateE
xtension -setParamFile:"WcfServiceSoftApps.SetParameters.xml" -whatif

Info: Adding directory (Default Web Site/SoftAppsWcfService\bin).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\EntityFramework.dll).

Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.Edm.dl
l).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.OData.
dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.Servic
es.Client.dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.Servic
es.dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\System.Spatial.dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\WcfServiceSoftApps.dl
l).
Info: Adding file (Default Web Site/SoftAppsWcfService\ModelSoftApps.Context.tt)
.
Info: Adding file (Default Web Site/SoftAppsWcfService\ModelSoftApps.edmx.diagra
m).
Info: Adding file (Default Web Site/SoftAppsWcfService\ModelSoftApps.tt).
Info: Adding file (Default Web Site/SoftAppsWcfService\packages.config).
Info: Adding file (Default Web Site/SoftAppsWcfService\Service1.svc).
Info: Adding file (Default Web Site/SoftAppsWcfService\WcfDataServiceSoftApps.sv
c).
Info: Adding file (Default Web Site/SoftAppsWcfService\Web.config).
Info: Adding ACL's for path (Default Web Site/SoftAppsWcfService)
Info: Adding ACL's for path (Default Web Site/SoftAppsWcfService)
Total changes: 17 (15 added, 0 deleted, 2 updated, 0 parameters changed, 4302476
 bytes copied)

Actual Deploy

Actual deploy by passing in the /Y argument.  To truly deploy against a host named Sunlight we will issue:

WcfServicesSoftApps.deploy.cmd /T /M:Sunlight

IIS – Backup IIS Configuration

Backup IIS Configuration

I later found out that IIS as a capable and easily accessible tool for backing up IIS Configuration.

I will say use it before mucking up IIS too much.

Sample backup invocation

%windir%\system32\inetsrv\appcmd add backup 20131123_b4WcfInstall

List backups

%windir%\system32\inetsrv\appcmd.exe list backup

List backups

I was curious as to where backups are saved and so inquisitively goggled for where.

Thankfully did not over indulge as Mike Volodarsky’s blogged about same – Most Important AppCmd Commands: Backing up and restoring IIS7 configuration –  http://mvolo.com/most-important-appcmd-commands-backing-up-and-restoring-iis7-configuration/.

And, so I now that I need to do backups via appcmd and that they are stored in the C:\windows\system32\inetsrv\backup.

inetsrv-backup-listfolders
And, also importantly, I should occasionally back that folder up to an external storage.

IIS – Application Pool

Introduction

It is possible that this is not actually needed, but I think it is best to create or identify an appropriate Application Pool.  And, then create and seed a Virtual Directory.

Create Application Pool

Before now I had stayed in the shadows of Microsoft .Net v2.  And, so I know I need a new v4.0 Application pool:

appPool

Please keep in mind that there are only two available baseline Frameworks:

  • .Net Framework v4.0.30319
  • .Net Framework v2.0.50727

I know that Windows Communication Framework was introduced in Microsoft .Net 3.5 and so I know needed the v4.0 version.

Even the lure of an accurate blog post is not enough to lure me into digging more into the difference between the Classic and Integrated Managed Pipeline Modes.  I am sure I will sooner than later run into a problem that will force me to learn.

IIS – Virtual Directory

Add a new Virtual Directory

Once you have decided on an Alias and Physical Path, please indicate as much.

But, more importantly click on the “Test Settings…”and validate your choice.

AddVirtualDirectory

Msdeploy – Helpful Hint

Attempted MSDeploy again, by issuing “WcfServiceSoftApps.deploy.cmd /y”..

And, here is the output I received.



U:WcfServiceSoftApps.deploy.cmd /y 
SetParameters from:
"U:\WcfServiceSoftApps.SetParameters.xml"
You can change IIS Application Name, Physical path, connectionString
or other deploy parameters in the above file.
-------------------------------------------------------
 Start executing msdeploy.exe
-------------------------------------------------------
 "C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='U:\WcfServiceSoftApps.zip' -dest:auto,in
udeAcls="False" -verb:sync -disableLink:AppPoolExtension -disableLink:ContentExtension -disableLink:CertificateExtensi
 -setParamFile:"U:\WcfServiceSoftApps.SetParameters.xml"
Error Code: ERROR_APPPOOL_VERSION_MISMATCH
More Information: The application pool that you are trying to use has the 'managedRuntimeVersion' property set to 'v2.

This application requires 'v4.0'.  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_APPPOOL_VERSIOMISMATCH.
Error count: 1.

The important error informant is the line that read:

Error Code: ERROR_APPPOOL_VERSION_MISMATCH
More Information: The application pool that you are trying to use has the 
'managedRuntimeVersion' property set to 'v2.
This application requires 'v4.0'.  
Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_APPPOOL_VERSIO
MISMATCH.

Microsoft .Net Framework / OS Compatibility

I think it is important to note that you probably should think a bit about your development goals and weigh that against current deployment state.  That is, which WinOS version are you currently running and which .Net Frameworks does that OS Support.

For example, if you ‘re running MS Windows 2003, it does not support .Net Framework 4.5.

This is stated as much @ .NET Framework System Requirements – .NET Framework 4.5 (http://msdn.microsoft.com/en-us/library/8z6watww(v=vs.110).aspx).

.Net Framework 4.5 can only be installed on MS Windows 2008, 2008/R2, 2012, 2012/R2.

Review & Confirm your Visual Studio Project Targeted Microsoft .Net Framework

While we are it, let us quickly and briefly confirm which .Net Framework we are targeting:

  • Launch Visual Studio
  • In Visual Studio, Access “Solution Explorer”
  • Select the Project – Please be sure to choose the Project and not the Solution
  • Right click on the Project
  • From the drop-down menu, select the “Properties” entry
  • On the left side of the window, move up and select the “Application” tab
  • The “Target Framework” choices are availed, please review and confirm that this is the actual Framework you will like to target

SelectProject

IIS Manager – Review & Change CLR Version

Accessed IIS Manager and fumbled quite a bit with changing the targeted CLR version.

Even though I had accessed the Advanced configuration panel:

AdvancedSettings

There was still no obvious path to changing the CLR Version.

Thought a bit more, and decided to change from a simple Virtual Directory to an Actual Application.

To do so:

  • Select the Virtual Directory
  • Right click on your selection and from the drop-down menu select “Convert to Application”

Select “Convert to Application” from drop-down menu

convertToApplication

Add Application screen

Here is the initial “Add Application” screen:

addApplication-before

Application Pool 2.0 Screen

Here is the default “Application Pool” screen:

It is obviously referencing “.Net Framework version: 2.0”.

addApplication-showing-appoolv2

Application Pool 4.0 Screen

Here is us changing to our preferred custom 4.0 pool.

addApplication-showing-appoolv4

Test Settings

Here is what our screen looks like, once we have the Application Pool that we want.

addApplication-after

Test Settings

Please make sure to Test things out – Just click on “Test Settings…”.

TestConnections

Click Close.

 

And, once you are good “Click OK” and let us see…

Msdeploy – Good!

Attempted MSDeploy again, by issuing “WcfServiceSoftApps.deploy.cmd /y”..

And, here is the output I received.

msdeploy-good

IIS – Virtual Directory Configuration

Introduction

I spent the whole day trying to get this application to work.

For those who feel the same struggle, here are screen shots from a working version.

Connection Strings

ConnectionString

Authentication

Authentication

Directory Browsing

DirectoryBrowsing

Failed Request Tracing Rules

FailedRequestTracingRules

Logging

Logging

The important configuration items to note are:

 Feature Why important?
Connection Strings Lists DB Connection items
Authentication The ones we have enabled are Anonymous / Asp.Net / Basic / Windows
Directory Browsing Enabled – To list individual files – Should be disabled once you have things working well
Failed Request Tracing Files As I said I experienced quite a bit of hardship and so resorted to this to try and see what was wrong
Logging Will talk more about this in a later post

Error

Error: The server was unable to process the request due to an internal error.

You might get the error pasted above, if you do not add a couple of code snippets to your source code.

You need this class decoration statement:

 [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]

And, in your class initializeService method, you need:

 //very important as otherwise you will get the very unhelpful
            //The server encountered an error processing the request. 
            //See server logs for more details.
            //use Verbose Errors
            config.UseVerboseErrors = true;

Our new source code reads:



using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;

namespace WcfServiceSoftApps
{
    //public class WcfDataServiceSoftApps : DataService
    //< /* TODO: put your data source class name here */ >
    [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
    public class WcfDataServiceSoftApps : DataService< SoftAppsEntities>
    {

        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {

            config.DataServiceBehavior.MaxProtocolVersion 
                    = DataServiceProtocolVersion.V3;

            //very important as otherwise you will get the very unhelpful
            //The server encountered an error processing the request. 
            //See server logs for more details.
            //use Verbose Errors
            config.UseVerboseErrors = true;

            config.SetEntitySetAccessRule("*", EntitySetRights.All);

            config.SetServiceOperationAccessRule(
                                        "ApplicationListFetchAll"
                                        , ServiceOperationRights.All
                                    );

            config.SetServiceOperationAccessRule(
                            "ApplicationListFetchFiltered"
                            , ServiceOperationRights.All
                        );

        }

        [WebGet]
        public IQueryable ApplicationListFetchAll()
        {

            return this.CurrentDataSource.usp_GetApplicationList_Filtered
                      (null, null, null, null, null).AsQueryable();

        }

        [WebGet]
        public IQueryable ApplicationListFetchFiltered
            (
              Guid? ApplicationTypeID
            , Guid? VendorID
            , Guid? LicensingID
            , short? PlatformID
            , String SearchTag 
            )
        {

            return this.CurrentDataSource.usp_GetApplicationList_Filtered
                (
                    ApplicationTypeID, VendorID, LicensingID, PlatformID, SearchTag
                ).AsQueryable();

        }

    } // ApplicationListFetchFiltered

}

IIS – Configuration – Error – 0x80070002

Once I added the debug statements, ran into a prescient error.

Web Browser - Error - HTTP Error - Compilation - Target Framework

This error pointed me to the fact that I had stipulated for .Net Framework 4.5

IIS – Configuration – Error – 0x80070002

Thought everything is well.  But, stayed in the wilderness for most of my day.  The exact error message is.

Item  Value
Error HTTP Error – 404.0 – Not Found
Module IIS Web Core
Notification MapRequestHandler
Handler StaticFile
Error Code 0x80070002

HTTP Error 404

Web Browser - Error - HTTP Error 404.0 - Not Found

Solution:

On a MS Windows 2012

Download & Install Microsoft .Net 4.5

As my installed MS Visual Studio 2012 was configured to point at Microsoft .Net 4.5 I needed to upgrade from v4.0 to v4.5.

Install it

Install it

Configure Microsoft WCF  – ServiceModelReg.exe


C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\ServiceModelReg.exe –i

Configure Microsoft WCF Services – HTTP Activation

  • Launch Control Panel \ Programs and Features
  • Access “Windows Features”
  • In the “Select Install Type ” – Role based or Featured-based installation
  • In the “Select destination server” – Select a server from the server pool
  • In the “Feature roles” – Make sure that “.Net Framework 3.5 Features is installed” — “.Net Framework 3.5 Features” and “HTTP Activation” and “Non-HTTP Activation”
  • In the “Feature roles” – Make sure that “.Net Framework 4.5 Features is installed” — “.Net Framework 4.0/4.5 Features” and “WCF Services – HTTP Activation” / “WCF Services – TCP Activation” / “WCF Services – TCP Port Sharing”

Summary:

Aforementioned, I spent a whole day trying to get this to work.  I wish that I can say that using this or that tool gave me specific error messages per MS Windows 2012.

Yes, I know that I was having “Microsoft WCF Service – HTTP Activation” problem.

As I struggled a bit and Googled for help, I found out that I should pay more attention to my targeted Framework.

But, the time wasted was more with properly provisioning WCF for .Net 4.0/4.5.  I assumed that the one for v3.5 will suffice; and had a blind spot to the need for checking the v4.0/v4.5 package(s), as well.

All problems stayed same until I goggled specifically for MS Windows 2012 & WCF.  And, found a fellow WordPresser – Steve – Host WCF in IIS8  – Windows Server 2012 ( http://3water.wordpress.com/2012/09/05/host-wcf-in-iis8-windows-server-2012/ ).

His guidance along proper configuration of Microsoft .Net v4.0 on a MS Windows 2012 finally brought me across the line.

References:

References – IIS

 

References – IIS – Error Logging

References – IIS – WCF – MS Windows 2012

References – IIS – appcmd

References – Debugging WCF Services

Technical: Microsoft – SQL Server – Compare Database Object Schema / Specifically Column Types

Introduction

Another Day, Another broken build courtesy of Daniel.

Don’t understand this ORM Life; everything has to be so nicely synced – The Database Objects, the edmx file, etc.

Broken Object

So what did I break today!  A database view.  What broke – One column allows null, the other does not.

Stored Procedure

Here is a quick stored procedure that allows me to quickly compare the columns of two entities and make sure that they match.


use [master]
go

if OBJECT_ID('dbo.sp_CompareObjectSchema') is null
begin
	exec('create procedure dbo.sp_CompareObjectSchema as select 1/0 as [undefined]')
end
go

alter procedure dbo.sp_CompareObjectSchema
(
	  @objectName1 sysname
	, @objectName2 sysname
	, @listallAtributes bit = 0
)
as

	select 

		 tblColumn1.name as [columnName]

		, case
			when (tblColumn2.name is not null) 
			 then 'Yes'
			else tblColumn2.name + 'No'
			 end as 'Presence'

		,
			case
				when (tblColumn1.column_id = tblColumn2.column_id)
					then 'Yes'					

			        when (
				      (tblColumn1.column_id != tblColumn2.column_id) 				     )	
					then '1 - ' 
					 + cast(tblColumn1.column_id as sysname)
					 + ' and 2 '
					 + cast(tblColumn2.column_id as sysname)								

				when (
				        (tblColumn2.column_id is null) 						     )	
					then '2 - ' 
						+ ' Not Present '

					else 'Test failed'

				end as 'Column ID'

		,
		   case
			when (tblColumn1.max_length = tblColumn2.max_length)
				then 'Yes'					

			when (
				    (tblColumn1.column_id != tblColumn2.column_id) 					
		  	   )	
				then '1 - ' 
			  	  + cast(tblColumn1.max_length as sysname)
				  + ' and 2 '
				  + cast(tblColumn2.max_length as sysname)								

			else 'Test failed'

		   end as 'ColumnMaxLength'

		,
			case
			    when (tblColumn1.user_type_id = tblColumn2.user_type_id)
				then 'Yes'					

			   when (
			         (tblColumn1.user_type_id != tblColumn2.user_type_id) 				
				 )	
			     then '1 - ' 
			       + cast(TYPE_NAME(tblColumn1.user_type_id) as sysname)
			       + ' and 2 '
			       + cast(TYPE_NAME(tblColumn2.user_type_id) as sysname)								

			   else 'Test failed'

			end as 'ColumnType'

		,
			case
			   when (tblColumn1.is_identity = tblColumn2.is_identity)
				then 'Yes'					

			   when (
			           (tblColumn1.is_identity != tblColumn2.is_identity) 				and (tblColumn1.is_identity = 1) 												
			       )	
				 then '1 - Yes, and 2 - No'

			  when (
			           (tblColumn1.is_identity != tblColumn2.is_identity) 				
				and (tblColumn2.is_identity = 0) 												
			      )	
				then '1 - Yes, and 2 - No'

			   else 'Test failed'

			end as 'Identity'

		,
		     case
				when (tblColumn1.is_nullable = tblColumn2.is_nullable) 
					then 'Yes'					

				when (
						(tblColumn1.is_nullable != tblColumn2.is_nullable) 					
					and (tblColumn1.is_nullable = 0) 												
					  )	
					then '1 is Nullable, and 2 is Not Nullable'

				when (
						   (tblColumn1.is_nullable != tblColumn2.is_nullable) 					
					and (tblColumn2.is_nullable = 0) 												
					)	
					   then '1 is Not Nullable, and 2 is Nullable'

				else 'Failed'

		     end as 'Nullability'

	from   sys.columns tblColumn1

			left outer join sys.columns tblColumn2

				on  tblColumn1.name = tblColumn2.name	
				and tblColumn2.object_id = object_id(@objectName2)			

	where  tblColumn1.object_id = object_id(@objectName1)

	and
		(

		    --column is not in object 2
		    (tblColumn2.name is null)

		    --Identity Test
		    or (tblColumn1.is_identity != tblColumn2.is_identity)

		   --UserType Test
		   or (tblColumn1.user_type_id != tblColumn2.user_type_id)

		   --Nullable differences
		   or (tblColumn1.is_nullable != tblColumn2.is_nullable)	

		   or (@listallAtributes = 1)			

		   )		

	order by
		     tblColumn1.column_id

go

EXEC sys.sp_MS_marksystemobject 'dbo.sp_CompareObjectSchema'
go

/*

	declare @objectName1 sysname
	declare @objectName2 sysname

	set @objectName1 = 'dbo.v_sales'

	set @objectName2 = 'dbo.v_Sales_New'

	use [dbName]

	exec dbo.sp_CompareObjectSchema
		  @objectName1 = @objectName1
		, @objectName2 = @objectName2

*/

Listening…

Took my brother’s big car out this weekend and listened in to Sirius. You know I Looked for the Country Music stations and what a joy I lucked into.

Here is King George singing about the things that matters:

Best Day of my LIFE
http://www.youtube.com/watch?v=0-d3enqUwUc