Monday, September 26, 2016

Azure Web App: Publishing Web Site/Service Forgot Deployment Password (getting Publish credentials through the Azure Portal)

In the previous positing, Azure Web App: Publishing Web Site/Service Forgot Deploy Password (introducing Cloud Explorer), it was shown how to find the publishing password for an Azure App Service using Cloud Explorer (an add-in for Visual Studio 2015). The impetus for this was to fix the web site/service deployment issue caused when Visual Studio's publishing wizard forgets the deployment password. Clearly a Visual Studio add-in, Cloud Explorer, is not the only way to determine the password used when publishing the Azure App Service. The same information can be retrieved using the Azure Portal, https://portal.azure.com/ which is precisely what this blog entry demonstrates.

An example of the screen shown when the Publishing wizard prompts for a password is as follows:


To find the publishing credentials in the Azure Portal navigate to the App Service for the which the credentials are to be retrieved. The initial blade displayed for an App Service is as follows:



Notice in the upper right corner the ...More menu which is shown below:


Clicking on this menu displays the Get publish profile menu item:



When the Get publish profile menu is clicked on the publishing profile is downloaded by default to a user's Downloads folder. The profile file takes the form of:
    <App Service Name>.PublishSettings

The profile file can be opened with a text editor such as Notepad++ which is shown below:


Within the profile the password can be found by searching for the letters PWD which are contained in the userPWD attribute which is shown below:


The userPWD attribute specifies the password used when publishing a web site/service using Visual Studio.


Sunday, September 25, 2016

C#/Entity Framework 6: Azure SQL/SQL Server, a standard approach to Connection Strings

In this post a strategy will be presented for handling the connection strings associated with Entity Framework models. Additional methods will be developed for the entities class associated with this model. These methods in conjunction with a more consistent approach to managing connection strings will provide an approach that can be applied to on-premises SQL Server instances and Azure SQL databases.

Connection Strings and Project Settings

In a previous post "C#/Entity Framework 6: Project and Connection String Management" an example was shown where a connection string was copied from the App.config of a class library containing an Entity Framework model to the App.config file of an executable invoking the classes associated with the Entity Framework model. An example of the connection string created by Entity Framework within an App.config is as follows and off course the same would be applicable if the connection string was contained in a web.config:


The App.config file above belongs to a console application, MainProgram, that will be used to demonstrated connection string management.

Connection strings are a category of project application settings that can be managed from Visual Studio via a project's properties. To access a project properties right click on the project within Solution Explorer and select Properties:


The Settings properties for the MainProgram console application are as follows:


Remember the App.config file for the MainProgram console application contains a connection string but this connection string does not show up in the settings tab for the project. The connection string in the console application's App.config is as follows (note the name is SchoolEntities):

<add name="SchoolEntities" connectionString="metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost\sql2016;initial catalog=School;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

In order for the SchoolEntities connection to be visible in Project | Settings it would need to be named in the following form:
    <application namespace>.Properties.Settings.SchoolEntities

To add a connection to the project's settings (and hence its App.config or web.Config) click in the link:


Clicking on the previous link enables settings to be added to the project. When the Settings tab is displayed, clicking on the Type drop down lists the viable settings types that can be created. To create a connection string select settings type (Connection string) as is shown below:





A name can be entered for the connection string which is shows below to be SchoolEntities:


Within the Value text box of the SchoolEntities setting is a button labeled as .... Click on the button displays the Connection Properties dialog:


From the previous dialog a connection can be configure and tested via the Test Connection Button. Once the connection is configured, clicking on OK will save the connection settings as a connection string within the Value property of the settings entry:


The connection string is saved as follows in the App.config where the newly created connection string is named MainApplication.Properties.Settings.SchoolEntities:


The newly created connection string takes the following form in App.config:
    <add 
      name="MainApplication.Properties.Settings.SchoolEntities"
      connectionString="Data Source=localhost\SQL2016;Initial Catalog=School;Integrated Security=True"
      providerName="System.Data.SqlClient" />

The text of the newly created connection string is follows:

Data Source=localhost\sql2016;Initial Catalog=School;Integrated Security=True

The connection string above is an ADO.NET style connection string

Converting an ADO.NET Connection String to an Entity Framework Format Connection String

The text of the connection string created by Entity Framework also contained in the App.config is as follows:

metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost\sql2016;initial catalog=School;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;

The the text demarcated in bold face above shows that within the Entity Framework connection string is the actual text of an ADO.NET connection string. It does not take Steven Hawking or Mr. Spock to deduce that String.Format could easily be used to convert an ADO.NET connection string into a connection string suitably for consumption by an Entity Framework model.

The first step in this conversion is to remove the &quote; contained in the string. These are used in App.config because it is an XML and &quote; indicates a double quote within a string. To tweak the string so it is C# ready (String.Format ready) replace &quotl with \".

metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"data source=localhost\sql2016;initial catalog=School;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework\"

To complete making the Entity Framework connection string usable so String.Format can inject an ADO.NET connection string inside the Entity Framework, replace the existing ADO.NET connection string with a {0}:

metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"{0};App=EntityFramework\"

Extending the Entity Framework Entities Class

Next we need to determine the appropriate location for the code that converts ADO.NET connection strings into Entity Framework connection strings. Recall that the code used to originally access the Entity Framework model was follows:


To see of Entity Framework implemented the SchoolEntities class, right click on the class name in the source code and select Go to Definition from the context menu:


The code generated by Entity Framework to implement SchoolEntities is as follows as found in the file, DataAccess\School.Context.cs:


The negative of the previous implementation is that there is a lone constructor and requires that connection string exists in the App.config/Web.Cong and this connection string must be named SchoolEntities.

The pro of the previous implementation is that it is a partial class so it is possible to create a second class containing additional constructors, properties and methods associated with the SchoolEntities partial class. The SchoolEntitiesExtra.cs file was added to the DataAccess class library and is implemented as follows in order to provided additional implementation to the SchoolEntities partial class:

using System;
using System.Data.Entity;
using System.Configuration;

namespace DataAccess
{
    public partial class SchoolEntities : DbContext
    {
        private const string _entityFrameworWrapper =            "metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"{0};App=EntityFramework\"";

        private SchoolEntities(
             string efConnectionNameorEFConnectionString) : 
                      base(efConnectionNameorEFConnectionString)
        {           
        }

        public static SchoolEntities 
                          CreateWithADOConnectionString(
                              string adoConnectionString)
        {
            string entityFrameworkConnectionString = String.Format(
                       _entityFrameworWrapper, adoConnectionString);

            return 
              new SchoolEntities(entityFrameworkConnectionString);
        }

        public static SchoolEntities 
                        CreateWithEntityFrameworkConnectionString(
                            string efConnectionString)
        {
            return new SchoolEntities(efConnectionString);
        }

        public static SchoolEntities 
                        CreateWithADOConfigurationConnectionName(
                          string adoConnectionName)
        {
            string adoConnectionString = 
                     ConfigurationManager.ConnectionStrings[
                       adoConnectionName].ConnectionString;

            return 
              CreateWithADOConnectionString(adoConnectionString);
        }

        public static SchoolEntities 
               CreateWithEntityFrameworkConfgurationConnectionName(
                   string efConnectionName)
        {
            return new SchoolEntities(efConnectionName);
        }
    }
}

The previous code implements a new connection string which is as follows:

private SchoolEntities(
            string efConnectionNameorEFConnectionString) : 
                      base(efConnectionNameorEFConnectionString)
{           
}

The base class of SchoolEnties is DbContext. The DbContext base class exposes multiple constructors defined in MSDN at DbContext Constructor. A constructor of note is defined as:


The idea is that a SchoolEntities instance can be created if a string is passed to the constructor containing an Entity Framework connection string or the connection name of a Entity Framework connection string stored in App.config/Web.Config.

The following methods within SchoolEntitiesExtra.cs invoke this constructor in order to create an instance of SchoolEntities:

public static SchoolEntities 
                CreateWithEntityFrameworkConnectionString(
                    string efConnectionString)
{
    return new SchoolEntities(efConnectionString);
}

public static SchoolEntities 
       CreateWithEntityFrameworkConfgurationConnectionName(
           string efConnectionName)
{
    return new SchoolEntities(efConnectionName);
}

The method below from SchoolEntitiesExtra.cs converts an ADO.NET formatted connection string into an Entity Framework formatted connection using String.Format as discussed above:

private const string _entityFrameworWrapper =            "metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.SqlClient;provider connection string=\"{0};App=EntityFramework\"";

public static SchoolEntities 
                  CreateWithADOConnectionString(
                      string adoConnectionString)
{
    string entityFrameworkConnectionString = String.Format(
               _entityFrameworWrapper, adoConnectionString);
    return 
      new SchoolEntities(entityFrameworkConnectionString);
}

The following method from SchoolEntitiesExtra.cs takes an ADO.NET connection name as a parameter. The ADO.NET connection string associated with the connection name is retrieved from the application's configuration file (App.config/Web.config). The ADO.NET connection string is passed to CreateWithADOConnectionString in order to create the entities instance:

public static SchoolEntities 
                CreateWithADOConfigurationConnectionName(
                  string adoConnectionName)
{
    string adoConnectionString = 
        ConfigurationManager.ConnectionStrings[
            adoConnectionName].ConnectionString;

    return CreateWithADOConnectionString(adoConnectionString);
}

The ConfigurationManager is a static class. This class's property, ConnectionStrings, is used to look up the ADO.NET formatted connection string by name. At the top of the SchoolEntitiesExtra.cs the namespaces made accessible via using include System.Configuration which is the namespace containing ConfigurationManager. A reference to System.Configuration.dll was added to the DataAccess, class library project.

Azure SQL

Placing the term "Azure SQL" in the title was bit of teaser. The impetus to developing the four methods was because the connection string to Azure SQL generated as project settings are in the for of ADO.NET connection strings. To demonstrate an Azure SQL connection string will be created as part of project seetings. This is achieved as was shown previously by right clicking on the MainApplication project withint Solution Explorer and selecting the Properties entry from the conect menu. When the project Properties are displayed, select the Settings blade:


An additional connection string can be added to settings, in this case the underlying database will be SQL Azure. An example of this is shown below:


To set up the connection string the ... button is clicked on:



An Azure SQL connection is setup usign the Connection Properties dialog just like an on-premise SQL Server connection. The first setp is to enter the server name which Azure calls "SQL Servers" (a rather common sense name. In a previous entry an overview of setting up SQL Azure and determinging the server name on which an Azure SQL dabase resides was presented SQL Azure: Finding the Server Name for SQL Azure Instance.

The Connection Properties dialog filled out to point to an Azrue SQL instance is as follows:


Clicking on the OK button will create a new connection strign this time ponting to a SQL Azure instance. This connection string will be of the form of an ADO.NET connection string but a strategy for converting such a connection string to its corresponding Entity Framework representation has already been presented.

Source Code

The source code for this example is found in GitHub solution, EntityFramework6.V02.Master, in repository: https://github.com/softwarepronto/Blog.



Wednesday, September 21, 2016

Azure Web App: Publishing Web Site/Service Forgot Deployment Password (introducing Cloud Explorer)

Publishing a web site to Azure (or on-prem IIS for that matter) is extremely convenient as it is integrated within Visual Studio. What make it beautiful on Azure is that a the password used for deployment is created automatically so a developer never needs to remember said password. Golly that sounds swell until during attempt to publish the following dialog is displayed during publishing:



The problem with the previous dialog is that an Azure developer typically has no idea what is the password to enter. This post demonstrates  where to acquire the missing password required in order to deploy a web site and/or web service.

To publish a site/server to Azure or IIS for that matter, select the Build menu's Publish <project name> option which kicks off publish wizard. 



Alternatively by right clicking on the web site/service project within Solution Explorer, the context menu displayed contains the Publish option:



Invoking the Publish wizard (as demonstrated above) display the Publish dialog show below:


When the Publish button on the Publish dialog is clicked, the configuration displayed in the drop down will be used during publishing. For Azure web sites/services no prompt is typically given to enter a password in order to publish as the credentials are saved. Every so often the following dialog appears prompting the user to enter a password they may not even know:


The pass can readily be acquired using Cloud Explorer which is an extension to Visual Studio 2015. The Cloud Explorer extension can be found at Cloud Explorer for Visual Studio 2015. In order to install Cloud Explorer the following prerequisits must be installed:

  • Microsoft Visual Studio 2015
  • Microsoft Azure SDK for .NET (VS 2015) - 2.7 or later


Once installed, Cloud Explorer can be invoked using Visual Studio's View menu as follows:


The Cloud Explorer tab appears as follows when it is accessed for the first time and hence does not remember current Azure login/subscription:



The previous screen is rather sparse because there no currently logged into Azure account. By clicking on Select an account a user can initiate login and after login a user can select the appropriate Azure subscription associated with a login.

Once logged into a specific user name and a specific subscription, Cloud Explorer can be configured to display the contents of a resource group as is demonstrated below:


Once the resource group containing the Web App to publish to and select the Download Publish Profile option at the bottom of the Cloud Explorer. The file download will be found in the Downloads folder and will be named for the web application name with the extension "publishsettings" as is shown below:

The publish settings files can be opened with any text editor such sa Notepad+:

The publish settings appear as follows when displayed in Notepad++:


Notice that the previous screenshot contains the userPWD element. The value stored in the userPWD element is the password that was prompted for by the publishing wizard.

The Cloud Explorer Visual Studio add-in demonstrated itself to be quite useful. Looking at the page used to download Cloud Explorer (Cloud Explorer for Visual Studio 2015), the extensions is given a review rating of 2.1 of 5 stars. This low rating is ironic given how handy Cloud Explorer at retrieving the password used to deploy a web site/service to Azure:



Tuesday, September 20, 2016

C#/Entity Framework 6: Project and Connection String Management

I seem to explain/implement the same fundamentals of working with Entity Framework again-and-again each time I start a new project. The steps discussed apply to both on-premise SQL Server and SQL Azure. This posting will demonstrated how to setup a class library containing a Entity Framework Model generated using a database and how to implement the database's connection string using an application/web configuration file from a a different assembly.

The solution and projects for this post are as follows:


The code to demonstrate the basic setup of an Entity Framework is Visual Studio 2015. The solution and projects for this demonstration can be seen above where:
  • EntityFramework6.Master; the solution containing the projects used to implement the code
  • DataAccess: class library that will ultimately contain the Entity Framework Model generated from a SQL Server on-premise instance.
  • MainApplication: console application which references the DataAccess assembly in order to access the underlying database.
    • The App.Config of the console application will hold the database connection string used by the DataAccess assembly.
An Entity Framework Model is added to the DataAccess, class library, project by right clicking on the project name in solution explorer and selecting Add | New Item from the context menu:



Select New Items displays the Add New Item dialog which is as follows:


Notice in the Add New Item dialog that the category of project selected as Installed | Visual C# Items | Data and that the project type is ADO.NET Entity Data Model. Once this is selected a name can be entered as shown below where the name is School:


Clicking on the Add button on the Add New Item dialog displays the following:


The model selected from the previous screen of the Entity Data Model Wizard is "EF Designer from database" which means the wizard will be pointed at an existing database via a connection and from which the Entity Framework will generate the model. Clicking on Next advances the Entity Data Model Wizard as follows:


A database connection can be selected from the drop down on the previous screen or can be created using the New Connection button. Once a connection is selected, click on Next:



The next screen in the Entity Data Model Wizard allows the version of Entity Framework to be selected. In the previous screen, Entity Framework 6.x is selected following which Next can be clicked on which displays:


The previous screen of the Entity Data Model Wizard allows the data objects from which the model is generated to be selected. In the previous dialog the tables only were selected. Doing this followed by clicking on Finish displays the model generated by Entity Framework:


Once the model is generated the app.config of the class library, DataAccess, will be populated with a database connection string at line 8. The app.config is shown below: 


The console application, MainApplication, already contains a reference to the Data Access, class library project. The rudimentary code to access the generated Entity Framework models is found in the MainApplication application's Program.cs file:


In the previous screenshot, Visual Studio is displaying error messages. This occurs because the NuGet package for Entity Framework 6.x are missing. The required NuGet package was added to the class library, DataAccess, when the Entity Framework Model was added.

To manage the Nuget packages for all projects in the solution, select the following from Visual Studio, Tools | NuGet Package Manager | Manage NuGet Packages for Solution... which is shown below:


The following dialog is displayed when Manage NuGet Packages for Solution... is selected:


From the previous screen click on the EntityFramwork which displays the following:


When EntityFramework is selected, the previous screen shows that the DataAccess project has Entity Framework 6.1.3 installed and that the MainApplication project does not have EntityFramework installed . By clicking on the check box next to the MainApplication it is possible to setup to install Entity Framework for the MainApplication, console application, project: 


When the check box next to MainApplication is checked, the Install button is enabled. Clicking on the Install button installs Entity Framework. A couple of dialog boxes are also displayed related to the package installed and licensing. The user should just navigate through these dialogs. 

The code is still not ready run. Attempting to run the console application, MainApplication, generates an exception as is show below:



The previous exception message is as follows: {"No connection string named 'SchoolEntities' could be found in the application config file."}

Recall the the wizard that setup a connection string within the app.config file for the class library, DataAccess. There is no suitable connection string in the app.config file of the console application. The remedy is simple; copy the <connectionStrings> section from the DataAccess project's app.config and paste it into the the app.config of the MainApplication project.


Lines 7-9 in the MainApplication's app.config were copied from DataAccess's app.config file. By making thi schange, the MainApplication console application will execute successfully. 

The source code for these projects can be found under: the EntityFramework6.Master solution folder within the following github,com repository: https://github.com/softwarepronto/Blog