RSS

Tag Archives: .net

Entity Framework, .Net and SQL Server Table Valued Parameter

This is step by step setup of using SQL Server TVP (Table Valued Parameter) in .Net application with EF (Entity Framework). In this example, I use SQL Server 2016 (SP2-CU3), .Net 4.5.1 and EF 6.20.

1. Create a table to store data.

CREATE TABLE [dbo].[Something] (
    [Id]            INT	IDENTITY(1,1)   NOT NULL,
    [Name]          VARCHAR(150)        NOT NULL,
    [Status]        CHAR(1)             NOT NULL,
    CONSTRAINT [PK_Something_Id] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

2. Create `User Defined Table Type` in SQL Server. For simplicity, in this example the type’s columns are same as table I created on step 1. In real-world, the type’s columns could be significantly different than table where we store the data, it might even used for join with other tables.

CREATE TYPE [dbo].[udt_Something] AS TABLE (
	[Name]		VARCHAR(150)	NOT NULL,
	[Status]	CHAR(1)		NOT NULL,
);
GO

3. Create stored procedure to take parameter (of a `User Defined Table` type we created earlier) and perform necessary task to persist our data.

CREATE PROCEDURE [dbo].[sp_SaveSomething]
	@udt_Something [dbo].[udt_Something] READONLY
AS
BEGIN
    INSERT INTO [dbo].[Something]
        SELECT *
        FROM @udt_Something
END
GO

4. Create extension method to convert `IEnumerable<T>` object to a `DataTable` object. In order to use SQL TVP, we have to pass our parameter as a `DataTable`. This method will help convert our data to `DataTable` type.
Helper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace QC
{
    public static class Helper
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> enumerable, IEnumerable<string> orderedColumnNames)
        {
            var dataTable = new DataTable();

            // Get all properties of the object
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();

            // Get column
            var columnNames = (orderedColumnNames ?? readableProperties.Select(s => s.Name)).ToArray();

            // Add columns to data table
            foreach (string name in columnNames)
            {
                dataTable.Columns.Add(name, readableProperties.Single(s => s.Name.Equals(name)).PropertyType);
            }

            // Add rows to data table from object
            foreach (T obj in enumerable)
            {
                dataTable.Rows.Add(columnNames.Select(s => readableProperties.Single(s2 => s2.Name.Equals(s)).GetValue(obj)).ToArray());
            }

            return dataTable;
        }
    }
}

5. For the purpose of this example, let’s say we want to save a collection of objects. This is our object definition.
Something.cs

namespace QC
{
    public class Something
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Status { get; set; }
    }
}

6. Using EF, called stored procedure we created and pass in `SqlParameter`, which is a collection of objects that we converted to `DataTable`. Don’t forget to specify parameter type as `User Defined Table Type`.
DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class DataAccess
    {
        public void Save(IEnumerable<Something> data)
        {
            // Columns for ordering, the order of the columns must be the same as user defined table type
            var orderedCols = new[] { "Name", "Status" };

            // SQL parameter to pass to stored procedure
            var param = new SqlParameter("@udt_Something", SqlDbType.Structured);
            param.Value = data.ToDataTable(orderedCols);
            param.TypeName = "dbo.udt_Something";

            try
            {
                // QCDB is our EF entities
                using (var db = new QCDB())
                {
                    // Call stored procedure and pass in table valued parameter
                    db.Database.ExecuteSqlCommand("EXEC dbo.sp_SaveSomething @udt_Something", param);
                }
            }
            catch
            {
                throw;
            }
        }
    }
}

7. Example of usage.
OpsController.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class OpsController : ApiController
    {
        public void SaveSomething()
        {
            var data = new List<Something>();
            data.Add(new Something { Id = 1, Name = "Chap", Status = "A" });
            data.Add(new Something { Id = 2, Name = "Stick", Status = "D" });

            var dataAccess = new DataAccess();
            dataAccess.Save(data);
        }
    }
}
Advertisements
 
Leave a comment

Posted by on January 18, 2019 in General

 

Tags: , , , , , , , , ,

Unit Test Internal Members

Applies to .Net application in Visual Studio 2010 and up.

Say I have C# project (call it QC project). In the project, I have class that has internal member(s) like this:

namespace QC
{
    public class PersonService
    {
        public bool PromotePerson(string userName)
        {
        }

        internal bool FindPerson(string userName)
        {
        }
    }
}

For unit test, I’d created separate project (call it QCTest project). Because internal members can only be accessed in same assembly, I won’t be able to invoke `QC.PersonService.FindPerson(string userName)` method from QCTest project.

To overcome this issue, in `AssemblyInfo.cs` of project being tested (in my case, QC project), simply add

[assembly: InternalsVisibleTo("QCTest")]

After rebuilding my solution, I am able to access `QC.PersonService.FindPerson(string userName)` in my QCTest project.

As an added bonus, if you use mock library like Moq or Rhino and try to mock internal members, you most likely need to add:

[assembly: InternalsVisibleTo("DynamicProxyGenAssembly2")]

What is `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.

Reference: https://stackoverflow.com/a/17179923

 
Leave a comment

Posted by on January 18, 2019 in General

 

Tags: , , , , , ,

Web API .Net with Basic Authentication

Github project.

Notes:
1. This work on .Net framework 4.6.1.
2. Authorization part is not covered.

The core authentication code is in `Security/BasicAuthAttribute.cs`. This class inherit from following:

ActionFilterAttribute
So we can use it as attribute to decorate controllers or actions.

IAuthenticationFilter
To invoke WebApi’s authentication pipeline. Some developer like to use `IActionFilter` for authentication, while it may work, it is not a best practice as `IActionFilter` execute later in the WebApi stack.

IAuthenticationFilter implement 2 methods:
1. `AuthenticateAsync`. Run first. This is code to authentication user. Caller pass in credential in request header. First we begin by parsing the header and user name/password credential caller passed in. Then authenticate user, in Github project, I add user to generic principal but in production app, you should validate credential against security provider (ie: ADFS, Auth0), etc.
2. `ChallengeAsync`. Run after `AuthenticateAsync`. This is where authentication failed and we can challenge caller to prove them selves, which is done by passing `Authorization Basic` in response header.

Usage
There are 3 ways to use this attribute in WebApi.
1. Globally. Every actions will require authentication.

WebApiConfig.cs

public static void Register(HttpConfiguration config)
{
    // Add global authentication
    config.Filters.Add(new BasicAuthAttribute());

    // Web API routes
    config.MapHttpAttributeRoutes();

    config.Routes.MapHttpRoute(
        name: "DefaultApi",
        routeTemplate: "api/{controller}/{id}",
        defaults: new { id = RouteParameter.Optional }
    );
}

2. In entire controller. Every actions under that controller will require authentication. Notice the `[BasicAuth]` decoration.

ValuesController.cs

[BasicAuth]
public class ValuesController : ApiController
{
    public IEnumerable Get()
    {
        return new string[] { "value1", "value2" };
    }
            
    public string Get(int id)
    {
        return "value";
    }
}

3. In specific action. Notice the `[BasicAuth]` decoration.

ValuesController.cs

public class ValuesController : ApiController
{
    public IEnumerable Get()
    {
        return new string[] { "value1", "value2" };
    }

    [BasicAuth]
    public string Get(int id)
    {
        return "value";
    }
}
 
Leave a comment

Posted by on January 11, 2019 in General

 

Tags: , , , ,

C# Dynamic-Typed Parameter Trap

A little tricky lesson today in C#.

Passing a dynamic-typed parameter to a method that has defined return type (ie: string, int, etc) will not return the defined type, instead the method will return dynamic type.

class Program
{
	static void Main(string[] args)
	{
		FooBarClass fooBar = new FooBarClass();

		var result1 = fooBar.Foo("Foo");

		dynamic bar = "Bar";
		var result2 = fooBar.Foo(bar);
	}
}

public class FooBarClass
{
	public string Foo(string fooArg)
	{
		return "This is Foo";
	}
}

In the above example, type of result1 is string, but type of result2 is dynamic, even though they both call same method that return string type.
result1
result2

This is because compiler do not evaluate dynamic type until runtime therefore it can’t determine return type at compile time.

This is important to point out because if we apply extension method to result2, it will throw exception at runtime, not compile time.

Example of extension method.

public static class FozBazClass
{
	public static void Foz(this string fozArg)
	{
		fozArg = "Test..." + fozArg;
	}
}
result1.Foz(); // this will execute normally
result2.Foz(); // this will throw exception

result2 exception

Just another reason we should avoid dynamic type.

 
Leave a comment

Posted by on May 29, 2018 in General

 

Tags: , , , ,

String as a Reference Type and Immutable

In .Net, string object is reference type, however when we pass the value like this

void Main()
{
    string s = "Test";

    TestString(s);
    Console.WriteLine("TestString: " + s); // Output is: TestString: Test
}

public static void TestString(string s)
{
    s = "TestString";
}

Why is the output “TestString: Test”, we know for sure string is a reference type and we assign new value to our parameter in TestString() method.

Turn out, beside being a reference type, string is also an immutable, which mean its value can’t be modified or changed. So when we assign new value to our parameter in TestString() method, it actually create new string object. It acts like value type but it isn’t a value type, it’s a reference type with immutable behavior.

 
Leave a comment

Posted by on May 15, 2018 in General

 

Tags: , , , ,

Float, Double, Decimal Types and DivideByZeroException

Most of us know the difference between Float, Double and Decimal types in .Net is the precision.

  • Float 32 bit (7 digits)
  • Double 64 bit (15-16 digits)
  • Decimal 128 bit (28-29 digits)

But most of us scratch our head over this:

var numerator = 10;
var denominator = 0;
var resultNoException1 = (float)numerator / (float)denominator; // Result Infinity, does not throw exception
var resultNoException2 = (double)numerator / (double)denominator; // Result Infinity, does not throw exception
var resultException = (decimal)numerator / (decimal)denominator; // Throw DivideByZeroException

Suffice to say throw/catch block won’t caught any exception on resultNoException1 and resultNoException2.

Why?

Because Float and Double types are binary floating point types (or sometimes being referred to as floating point types) while Decimal is decimal floating point types.

From MSDN:

Dividing a floating-point value by zero doesn’t throw an exception; it results in positive infinity, negative infinity, or not a number (NaN), according to the rules of IEEE 754 arithmetic.

 

 
Leave a comment

Posted by on May 2, 2018 in General

 

Tags: , , , , , , ,

What’s My .NET Framework Version?

Using Registry

Most accurate is through registry. For .NET Framework 1-4:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP

And newer .NET Framework:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full

The DWORD represents different .NET Framework version.

Using Code

Easier way is to use code:

void Main()
{
    GetVersionFromRegistry();
    GetNet45PlusFromRegistry();
}

private static void GetVersionFromRegistry()
{
    // Opens the registry key for the .NET Framework entry.
    using (RegistryKey ndpKey =
        RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, "").
        OpenSubKey(@"SOFTWARE\Microsoft\NET Framework Setup\NDP\"))
    {
        // As an alternative, if you know the computers you will query are running .NET Framework 4.5
        // or later, you can use:
        // using (RegistryKey ndpKey = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine,
        // RegistryView.Registry32).OpenSubKey(@"SOFTWARE\Microsoft\NET Framework Setup\NDP\"))
        foreach (string versionKeyName in ndpKey.GetSubKeyNames())
        {
            if (versionKeyName.StartsWith("v"))
            {
                RegistryKey versionKey = ndpKey.OpenSubKey(versionKeyName);
                string name = (string)versionKey.GetValue("Version", "");
                string sp = versionKey.GetValue("SP", "").ToString();
                string install = versionKey.GetValue("Install", "").ToString();
                //no install info, must be later.
                if (install == "")
                {
                    var version = versionKeyName + "  " + name;
                    Console.WriteLine(version);
                    version.Dump();
                }
                else
                {
                    if (sp != "" && install == "1")
                    {
                        var version = versionKeyName + "  " + name + "  SP" + sp;
                        Console.WriteLine(version);
                        version.Dump();
                    }
                }
                if (name != "")
                {
                    continue;
                }
                foreach (string subKeyName in versionKey.GetSubKeyNames())
                {
                    RegistryKey subKey = versionKey.OpenSubKey(subKeyName);
                    name = (string)subKey.GetValue("Version", "");
                    if (name != "")
                        sp = subKey.GetValue("SP", "").ToString();
                    install = subKey.GetValue("Install", "").ToString();
                    //no install info, must be later.
                    if (install == "")
                    {
                        var version = versionKeyName + "  " + name;
                        Console.WriteLine(version);
                        version.Dump();
                    }
                    else
                    {
                        if (sp != "" && install == "1")
                        {
                            var version = "  " + subKeyName + "  " + name + "  SP" + sp;
                            Console.WriteLine(version);
                            version.Dump();
                        }
                        else if (install == "1")
                        {
                            var version = "  " + subKeyName + "  " + name;
                            Console.WriteLine(version);
                            version.Dump();
                        }
                    }
                }
            }
        }
    }
}

private static void GetNet45PlusFromRegistry()
{
    const string subkey = @"SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full\";
    string version = string.Empty;

    using (RegistryKey ndpKey = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32).OpenSubKey(subkey))
    {
        if (ndpKey != null && ndpKey.GetValue("Release") != null)
        {
            version = ".NET Framework Version: " + CheckFor45PlusVersion((int)ndpKey.GetValue("Release"));
            Console.WriteLine(version);
            version.Dump();
        }
        else
        {
            version = ".NET Framework Version 4.5 or later is not detected.";
            Console.WriteLine(version);
            version.Dump();
        }
    }
}

// Checking the version using >= will enable forward compatibility.
private static string CheckFor45PlusVersion(int releaseKey)
{
    if (releaseKey >= 461308)
        return "4.7.1 or later";
    if (releaseKey >= 460798)
        return "4.7";
    if (releaseKey >= 394802)
        return "4.6.2";
    if (releaseKey >= 394254)
    {
        return "4.6.1";
    }
    if (releaseKey >= 393295)
    {
        return "4.6";
    }
    if ((releaseKey >= 379893))
    {
        return "4.5.2";
    }
    if ((releaseKey >= 378675))
    {
        return "4.5.1";
    }
    if ((releaseKey >= 378389))
    {
        return "4.5";
    }
    // This code should never execute. A non-null release key should mean
    // that 4.5 or later is installed.
    return "No 4.5 or later version detected";
}

Reference
Microsoft Docs

 
Leave a comment

Posted by on January 22, 2018 in General

 

Tags: ,

 
%d bloggers like this: