Refresh SQL Server View

Tested on:
SQL Server 2016 SP2
SQL Server Management Studio 14.0

To refresh SQL Server view, run following script:

EXEC sp_refreshview N'dbo.vwClass'

Example of issue with SQL Server view.

I have following table.
2019-11-12 13_58_55-Window

And a view called vwClass.

SELECT *
FROM dbo.Class

For the reasons unknown to me, when I right click on the view and choose ‘Select Top 1000 Rows’, the query does not return entire column of Class table. Instead, it return following:
2019-11-12 14_00_45-Window

After I run refresh view command above, the view return correct result.
Other possible solution maybe to avoid using SELECT * in a SQL Server view.

Setup SQL Server Connection Resiliency with Entity Framework

Tested on:
Microsoft SQL Server 13.00.5292
Entity Framework 6.2.0

When an application execute too many operations against SQL Server, a transient fault could occur.
Transient fault in SQL Server could occurred in self-hosted or cloud SQL Server. It could caused by many reasons and extremely (if not impossible) to debug.
You might encountered error messages like:

“Database * on server * is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of *.”

Other similar messages also point to transient fault errors.

Microsoft suggested solution to transient fault is to retry the executed operation, often dubbed as database connection resiliency. To set this up in Entity Framework, follow this step:

  1. Set EF DbConfiguration using code-based configuration, see this post.

  2. In the constructor, add following code

    SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy(10, TimeSpan.FromSeconds(50)));
    

  3. The first parameter of SqlAzureExecutionStrategy is the maximum number of retry and second parameter is the delay in milliseconds between retries.

Once set, EF will retry the faulted operation automatically. Consider performance when deciding how many tries and delay time to configure. The longer the try or delay time will increase operation time.

References:
Microsoft
Sql Server Backup Academy

Programmatically Configure EF DbConfiguration

Tested on:
Entity Framework 6.2.0

There are few ways to set EF configuration, this describes using code-based DbConfiguration.

  1. Create a class inherit from DbConfiguration.
    using System.Data.Entity;
    
    namespace InfinityDataModel
    {
        public class InfinityConfiguration : DbConfiguration
        {
            public InfinityConfiguration()
            {
                // Sample configuration
                SetDefaultConnectionFactory(new LocalDbConnectionFactory("InfinityDb"));
            }
        }
    }
    

  2. The class must be in same assembly as your the Entity Framework Data Model.

  3. In the constructor, set the configuration you wanted. For all possible configurations, see here.
  4. There is no need to do anything else. EF will load your configuration class when initialized.

References:
Microsoft

Parse JSON in SQL Server

I have following rows in database.

2019-07-17 17_33_37-SQLQuery45.sql - (local)_.Infinity (ENERGY_sutet (53))_ - Microsoft SQL Server M

Here is the full json:

{
    "FirstName": "Paul",
    "LastName": "Goodman",
    "Occupations": [
        {
            "Title": "Senior Manager",
            "Company": "Purple Ocean",
            "YearOfService": 9
        },
        {
            "Title": "Manager",
            "Company": "Blue Ocean",
            "YearOfService": 4
        }
    ]
}
{
    "FirstName": "Tom",
    "LastName": "Badman",
    "Occupations": [
        {
            "Title": "Supervisor",
            "Company": "Red Sky",
            "YearOfService": 1
        },
        {
            "Title": "Janitor",
            "Company": "Yellow Sky",
            "YearOfService": 7
        }
    ]
}

Here is how I can use JSON_VALUE to parse `Data` column.

2019-07-17 17_34_04-SQLQuery45.sql - (local)_.Infinity (ENERGY_sutet (53))_ - Microsoft SQL Server M

I can also use JSON_VALUE in WHERE clause.

2019-07-17 17_34_38-SQLQuery45.sql - (local)_.Infinity (ENERGY_sutet (53))_ - Microsoft SQL Server M

Ionic App – Xcode Build Error “library not found for -lPods”

Environment:
Xcode 10.2.1 (10E1001)
Ionic CLI 5.2.1
Cordova CLI 8.1.2
cordova-res 0.5.1
native-run 0.2.7

Installed platforms:
ios 5.0.1

After building your Ionic project, you open with Xcode then build and got following error:

library not found for -lPods-[your app name]

The error is caused by missing Pod library, most likely your app is using CocoaPods to manage some of dependency libraries.

To solve this issue, in /platforms/ios/ folder, do not open .xcodeproj file with Xcode, instead, open .xcworkspace file and build. The workspace file will include Pods libraries.

Image 2019-06-29 at 9.52.45 PM

C# Check if PropertyType is Null

You want to check if a property’s PropertyType is nullable. In the example below, only property `IsStudent` is nullable.

User.cs

public class User
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool? IsStudent { get; set; }
}

Checking if PropertyType is nullable is useable when adding column to `DataTable` object. If column to be added is nullable type, we need to explicitly set it.

To check if PropertyType is nullable, we can use `System.Nullable.GetUnderlyingType(Type nullableType)`, see example below and reference here.

public void GetUsers()
{
    System.Reflection.PropertyInfo[] properties = typeof(User).GetProperties();
    System.Reflection.PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();
    foreach (var property in readableProperties)
    {
        var propertyType = property.PropertyType;
        if (System.Nullable.GetUnderlyingType(propertyType) != null)
        {
            // It's a nullable type
        }
        else
        {
            // It's not a nullable type
        }
    }
}

Reference
Stackoverflow

DynamicProxyGenAssembly2

DynamicProxyGenAssembly2 is a temporary assembly built by mocking systems that use CastleProxy like Moq or NSubsitute. It is generated when the mock is needed and disposed of after the tests are finished.

Tested on:
Moq 4.10 https://github.com/moq/moq4
.Net 4.5

Following is AssemblyInfo.cs in QC.Client project.

using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;

[assembly: AssemblyTitle("QC.Client")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("QC.Client")]
[assembly: AssemblyCopyright("Copyright ©  2019")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: ComVisible(false)]

[assembly: Guid("d62d1a60-782a-46d8-b5f5-2f6d12ad1339")]

[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]
[assembly: InternalsVisibleTo("QC.ClientTest")]

Following is class i want to test.
UserManagement.cs (In QC.Client project)

namespace QC.Client
{
    public class UserManagement
    {
        public void UpdateUser(string address)
        {
            var executer = new Executer();
            executer.Run();
        }
    }
}

Executer.cs (In QC.Client project)

namespace QC.Client
{
    public class Executer
    {
        internal virtual bool Run()
        {
            return true;
        }
    }
}

Following is my unit test.
UserManagementTest.cs (In QC.ClientTest project)

using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;
using QC.Client;

namespace QC.ClientTest
{
    [TestClass]
    public class UserManagementTest
    {
        [TestMethod]
        public void UpdateUserShouldWork()
        {
            // Mock
            var mock = new Mock();
            mock.Setup(x => x.Run());

            // Act
            var userManagement = new UserManagement();
            userManagement.UpdateUser("Test Address");

            // Assert
            mock.Verify(x => x.Run(), Times.Once);
        }
    }
}

Problem
MS Test throwing following error when unit testing internal member of a class in different project.
When unit test in above `UserManagementTest.cs` is run, MS Test throw following error:

Message: Test method QC.ClientTest.UserManagementTest.UpdateUserShouldWork threw exception:
System.ArgumentException: Cannot set up Executer.Run because it is not accessible to the proxy generator used by Moq:
Can not create proxy for method Boolean Run() because it or its declaring type is not accessible. Make it public, or internal and mark your assembly with [assembly: InternalsVisibleTo("DynamicProxyGenAssembly2")] attribute, because assembly QC.Client is not strong-named.

Note:
– MS Test only throw exception if the accessor of method being tested is internal. In above example it is `Executer.Run()`.
– Virtual keyword in method being tested is to allow Moq to mock the method.

Solution is to simply add following code in AssemblyInfo.cs

[assembly: InternalsVisibleTo("DynamicProxyGenAssembly2")]

Sources
Stackoverflow