Converting List To DataTable

In my previous post SqlBulkCopy I wrote about how to load data from .NET code into SQL Server database using efficient BULK INSERT command. This class needs a DataTable or IDataReader instance as a source. I’ve collected a couple of examples how to convert List to DataTable.

1. Variation of Marc Gravell solution.
Source: https://stackoverflow.com/a/14548027

2. Solution created by Jennifer Hubbard, Bill Wagner, etc.
Source: How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow

SqlBulkCopy

SQL Server have great commands like BULK INSERT or OPENROWSET(BULK…) to insert a huge amount of data into a database. .NET Framework has a class SqlBulkCopy that provides similar capabilities in C#. Full documentation is available on MSDN article SqlBulkCopy Class. But SqlBulkCopy has some restrictions on input data. It can read from:

  • DataRow[] array;
  • DataTable;
  • IDataReader.

A simple example of usage can be as follows:

SQL Server Profiler shows that the correct command was really executed.

In order to use SqlBulkCopy class elegantly, it would better to have a method or extension to convert a generic list to DataTable or IDataReader. But it’s a topic for my next post.

Offline Installer For Visual Studio 2017

Microsoft does not offer an ISO image for Visual Studio 2017, we need to make it ourselves by running web installer with some command-line arguments. Here are the links to official pages:

Create an offline installer for Visual Studio 2017

Visual Studio 2017 workload and component IDs

Example to grab community edition:

Layout parameter is mandatory, it points to a folder where the downloaded files would be saved.
Workload_id_list is a list of component IDs, separated by space. If you skip it, all components will be downloaded.

[UPDATE 2017-06-21]
Workloads contain only the recommended components. As mentioned by geoyar, you need to include optional components manually.

Component ID Description
Visual Studio core editor (included with Visual Studio Community 2017) Microsoft.VisualStudio.Workload.CoreEditor The Visual Studio core shell experience, including syntax-aware code editing, source code control and work item management.
Azure development Microsoft.VisualStudio.Workload.Azure Azure SDK, tools, and projects for developing cloud apps and creating resources.
Data storage and processing Microsoft.VisualStudio.Workload.Data Connect, develop and test data solutions using SQL Server, Azure Data Lake, Hadoop or Azure ML.
.NET desktop development Microsoft.VisualStudio.Workload.ManagedDesktop Build WPF, Windows Forms and console applications using the .NET Framework.
Game development with Unity Microsoft.VisualStudio.Workload.ManagedGame Create 2D and 3D games with Unity, a powerful cross-platform development environment.
Linux development with C++ Microsoft.VisualStudio.Workload.NativeCrossPlat Create and debug applications running in a Linux environment.
Desktop development with C++ Microsoft.VisualStudio.Workload.NativeDesktop Build classic Windows-based applications using the power of the Visual C++ toolset, ATL, and optional features like MFC and C++/CLI.
Game development with C++ Microsoft.VisualStudio.Workload.NativeGame Use the full power of C++ to build professional games powered by DirectX, Unreal, or Cocos2d.
Mobile development with C++ Microsoft.VisualStudio.Workload.NativeMobile Build cross-platform applications for iOS, Android or Windows using C++.
.NET Core cross-platform development Microsoft.VisualStudio.Workload.NetCoreTools Build cross-platform applications using .NET Core, ASP.NET Core, HTML, JavaScript, and CSS
Mobile development with .NET Microsoft.VisualStudio.Workload.NetCrossPlat Build cross-platform applications for iOS, Android or Windows using Xamarin.
ASP.NET and web development Microsoft.VisualStudio.Workload.NetWeb Build web applications using ASP.NET, ASP.NET Core, HTML, JavaScript, and CSS.
Node.js development Microsoft.VisualStudio.Workload.Node Build scalable network applications using Node.js, an asynchronous event-driven JavaScript runtime.
Office/SharePoint development Microsoft.VisualStudio.Workload.Office Create Office and SharePoint add-ins, SharePoint solutions, and VSTO add-ins using C#, VB, and JavaScript.
Universal Windows Platform development Microsoft.VisualStudio.Workload.Universal Create applications for the Universal Windows Platform with C#, VB, JavaScript, or optionally C++.
Visual Studio extension development Microsoft.VisualStudio.Workload.VisualStudioExtension Create add-ons and extensions for Visual Studio, including new commands, code analyzers and tool windows.
Mobile development with JavaScript Microsoft.VisualStudio.Workload.WebCrossPlat Build Android, iOS and UWP apps using Tools for Apache Cordova.
Unaffiliated components Component.GitHub.VisualStudio GitHub extension for Visual Studio
Unaffiliated components Microsoft.Component.Blend.SDK.WPF Blend for Visual Studio SDK for .NET
Unaffiliated components Microsoft.Component.HelpViewer Help Viewer
Unaffiliated components Microsoft.Net.Component.3.5.DeveloperTools .NET Framework 3.5 development tools
Unaffiliated components Microsoft.VisualStudio.Component.DependencyValidation.Community Dependency Validation
Unaffiliated components Microsoft.VisualStudio.Component.LinqToSql LINQ to SQL tools
Unaffiliated components Microsoft.VisualStudio.Component.TestTools.Core Testing tools core features
Unaffiliated components Microsoft.VisualStudio.Component.TypeScript.2.0 TypeScript 2.0 SDK
vs_community__1238641179.1486458197.exe –layout C:\VS2017 –lang en-US –add Microsoft.VisualStudio.Workload.CoreEditor Microsoft.VisualStudio.Workload.Azure Microsoft.VisualStudio.Workload.Data Microsoft.VisualStudio.Workload.ManagedDesktop Microsoft.VisualStudio.Workload.NetCoreTools Microsoft.VisualStudio.Workload.NetWeb Microsoft.VisualStudio.Workload.Node Microsoft.VisualStudio.Workload.Office Microsoft.VisualStudio.Workload.VisualStudioExtension Component.GitHub.VisualStudio Microsoft.Component.Blend.SDK.WPF Microsoft.Component.HelpViewer Microsoft.Net.Component.3.5.DeveloperTools Microsoft.VisualStudio.Component.DependencyValidation.Community Microsoft.VisualStudio.Component.LinqToSql Microsoft.VisualStudio.Component.TestTools.Core Microsoft.VisualStudio.Component.TypeScript.2.0

Useful NLog Targets

NLog has many targets to which it can save log entries. Here are some that I consider quite useful.

File

I’ve added some parameters to archive log on everyday basis.

EventLog

NLog will create custom application log MyAppLog.

Visual Studio Debug window

Also NLog allows to save log in a database table, but it needs more efforts and lines of code to setup it. You can read more in the article Database target in NLog project documentation.

Reloading Configuration In NLog

When you use NLog in your C# project, its configuration is loaded once on the application startup. Now I want to change the configuration (targets, rules, levels) while application is running. It’s easy – just edit NLog.config file.

That’s not enough. Application should notice that changes and reapply them. This can be triggered by re-reading NLog.config again at some point, by pressing the button or calling Web API, i.e. you or your application should initiate reloading.

As for NLog, reloading is implemented by 3 lines of code.

C#. Passing Lambda Expression As Parameter

Using delegates you can pass a lambda expression as a parameter to a function, and then use it in LINQ queries. This can be done with Func<…> delegates.

If you want to pass a lambda expression to be used, for example, in Where clause, you need a Func<T, bool> delegate. Just hover the mouse over Where in your code, and IntelliSense would show you the required syntax.

Here is a simple demo program:

C#. Passing Function As Parameter Using Delegate

I need a generic C# module that will execute different functions for some classes, or execute none. This can be done with delegates.

The basic info is described in MSDN articles:
Using Delegates (C# Programming Guide)
Func<T, TResult> Delegate

Here is my simple console application that would call different functions that are passed as parameters.

Comparison: Entity Framework vs C#. When Equality Operators Are Not Equal

When you compare numbers, you know that 5 equals 5. But C#, Entity Framework, SQL and many programming languages have a special value called “null”. It’s something unknown. So when you compare a definite number, for example 5, with null, the result will be “false”, because 5 and unknown is not the same.
But the situation differs when you compare null with null. Guess will it be true or false? But the right answer is: It depends… And now I’ll tell you why.

T-SQL has a concept that unknown value NULL is not equal to another unknown value NULL. At the same time C# and Entity Framework believe that two nulls are equal. This comes from the reference types where null means that a pointer does not reference to any variable. So when 2 pointers are null, so they are equal, because they don’t reference to any variable.

To better understand the differences between Entity Framework and C# I created a simple test to compare values. Here is my C# project StringEqualityTest that you can download and run in your own pace.

The results are the following:

Left operand Right operand C# Entity Framework Explanation
“A” “A” true true Exact match
“B” “b” (lowercase) false true* EF uses SQL Server comparison rules
“B” “B ” (with trailing space) false true EF uses SQL Server comparison rules
“” “” (empty string) true true Exact match
null null true true EF uses C# rules
* – not always

Let’s look deeper into what’s going on.

When Entity Framework compares two not null strings, it applies rules defined in SQL Server. It seems that EF sends a query and merely receives the result from SQL Server. And all the rules come from SQL Server. That’s the reason why SQL Server discards trailing spaces (look at “B” = “B “). Also it uses a collation for comparison and ordering. In my case it’s a Cyrillic_General_CI_AS, CI means Case Insensitive, AS means Accent Sensitive. So “B” = “b”. If you have another collation, the result might be different.

Just compare these examples with C# rules where “B” <> “b”, and “B” <> “B “. C# always returns the same result.

But when EF compares null strings, it shows they are equal like C# do. To do this, EF generate a special query for SQL Server to receive the result in C# logic. When you look at SELECT command generated by EF, you’ll find something interesting.

EF adds a second condition when both operands are null. That’s why EF shows the result in C# style while SQL Server executes all the comparison as it did forever.

Entity Framework has a dedicated parameter that could change the null comparison logic to be implemented by SQL Server. In EF 6.0 it’s a UseDatabaseNullSemantics. When you set it to true (the default is false), you’ll get a SQL Server rules for null equality. Some previous versions of EF uses an opposite parameter UseCSharpNullComparisonBehavior.

This time the SQL query would not have an additional condition for nulls.

UPDATE:
I’ve missed an important option that influence SQL behavor (thanks to Arthur Zubarev for his valuable comment). There’s an option ANSI_NULLS. If you set it to false, so SQL Server would think that nulls are equal. But you can hardly see it in action because SQL Server Native Client driver automatically set it to ON when connecting.

Calling SQL Server Stored Procedure With Table-Valued Parameter in C#

In one of my previous post I described how to use table-value parameter in T-SQL code. Today I will show how to use it in your .NET application.

This is a sample C# console application. The most important things you should pay attention to are the following:

  1. create DataTable with structure equivalent to table type defined in SQL database (in my case string for varchar(20), and Int16 for smallint)
  2. fill your DataTable with data
  3. add parameter to SqlCommand object with type SqlDbType.Structured
  4. assign DataTable to parameter’s Value property