SSIS – The value violated the integrity constraints for the column

Possibly save 1 hour of your time: I got this error when I am extracting data from a file and pushing those data to database.

This issue stems from the data missing values. Hence, it was trying to insert NULL values into a non-null column.

This is easily fixed by changing the database table column to allow NULL values. If you don’t expect null values, then you will have to filter those data out before inserting into the database.

Advertisements

log4net not logging exceptions to event viewer

Possibly save 1 hour of your time: We use log4net to log errors to event viewer on our application servers.

So for some reason, the event viewer is not showing the log when there is supposed to be a logged error due to exception.

You need to initialize the log4net with the following statement:

log4net.Config.XmlConfigurator.Configure();

In my web.config/app.config, I have the following configuration:

log4net configuration

Introducing Web UI Framework Blazor for .NET

Possibly save 1 hour of your time: This framework is current in preview mode. It is not official yet.

This framework allows you to use C#/Razor and HTML to build your Web UI that runs in the browser with WebAssembly. So supposely you don’t need to use JavaScript to build interactive UI componet. Instead, you will use C#/Razor.

To learn more, check out:

Get started building .NET web apps that run in the browser with Blazor

Github:

Also check out WebAssembly:

 

System.Web.Hosting.HostingEnvironment.MapPath returns null in unit test

Possibly save 1 hour of your time: There are times when you need to access a file that is included within your application. This could be a data file. In this case, you may need to grab the path of this file on your server.

You can use System.Web.Hosting.HostingEnvironment.MapPath(“~/yourPath”) but this returns null in your functional unit tests.

An easy way to fix this is to check if it is running in a hosted environment. If it is not, then use AppDomain.CurrentDomain.BaseDirectory.


private static string MapPath(string filePath)
{
var hostingRoot = System.Web.Hosting.HostingEnvironment.IsHosted
? System.Web.Hosting.HostingEnvironment.MapPath("~/")
: AppDomain.CurrentDomain.BaseDirectory;

return Path.Combine(hostingRoot, filePath.Substring(1).Replace('/', '\\'));
}

Introducing Microsoft machine learning library for .NET (ML.NET)

Possibly save 1 hour of your time: This is a quick introduction to Microsoft machine learning library ML.NET.

Microsoft

Github

The library is only supported since .NET Core 2.0.

To start, you can create a .NET Core 2.0 console app. Then you can use nuget to find package Microsoft.ML and install it to your project.

Here are some features of the library:

ml_net

 

Package is not compatible with Package supports netstandard2.0

Possibly save 1 hour of your time: When you try to install a nuget package with a dependency on .NET Standard 2.0, it gives you this error.

.NET Standard 2.0 is supported by .NET Core 2.

You can right click your project >> select properties. Check to see what .NET Core you are using.

dot net core 2

If you don’t have .NET Core 2.0, the easiest way is to download latest is to go into Visual Studio > Tools > Extensions and Updates > Under Updates > Product Updates > you should be able to see Visual Studio Update.

You can also download SDK and Runtime here https://www.microsoft.com/net/download/visual-studio-sdks

For more information on .NET Standard 2.0, please see https://blogs.msdn.microsoft.com/dotnet/2017/08/14/announcing-net-standard-2-0/

 

 

LINQ query should not include your class projection to improve performance

Possibly save 1 hour of your time: I have found that performance of your LINQ query could be impact especially for large datasets when you project your results directly to a class object. It is better to project to .NET object after the sql query results came back first.

var results = (from a in context.TableA
                    join b in context.TableB on a.ID equals b.ID
                    select new YourClassName
                    {
                        Year = a.Year,
                        Name = b.Name
                    });
var list = results.ToList();

The code above is much slower than the code below for large datasets.

var results = (from a in context.TableA
                    join b in context.TableB on a.ID equals b.ID
                    select new
                    {
                        Year = a.Year,
                        Name = b.Name
                    });
var list = results.ToList();

var returnList = list.Select(x => new YourClassName
                {
                    Year = x.Year,
                    Name = x.Name
                }).ToList();

 

Request to run job refused because the job is already running from a request by user

Possibly save 1 hour of your time: I have a job that can take awhile to finish on development environment. I have started running the job and realize something I have missed. Now I want to stop or cancel the job, but I get this error telling me that the job is already running.

Solution: The easiest way is to restart the SQL Server Agent Service by going to SQL Server Configuration Manager or sqlservermanager11.msc in command prompt.

 

 

 

Float, Double, Decimal in C#

Possibly save 1 hour of your time: There are times where you have to do some calculations on your data in .NET. If the data are based on different data types such as float, double or decimal, you will get error when you do calculations and storing the result in a certain datatype.

The best is to find out which of these fields have the most precision required or basically what is the biggest number possible.

Float, Double, Decimal is the biggest from the left to right respectively.

In my case, there are some big numbers so I just store them as decimals in both .NET and SQL.

Here is a good explanation on how SQL datatypes map to .NET datatypes:

https://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx

(SQL -> .NET)

float -> double

decimal -> decimal

 

Error: System.InvalidOperationException : The specified cast from a materialized ‘System.Double’ type to the ‘System.Decimal’ type is not valid.

This happens when my SQL datatype is float but in .NET the datatype is decimal. Since I need to do calculations using divide operator, I will change SQL datatype from float to decimal(30,15).

 

Web API – return huge dataset via many records

Possibly save 1 hour of your time: My Web API returns many records and it breaks after it returns some where above 2 million records.

I tried the following but it did not work. According to documentation, it only works if you are using the JavaScriptSerializer Class. The max value is based on Int32.

At this moment, I don’t have any solution yet. The only thing I can think of is that the consumer can hit the database directly if accessible rather than calling the API. Or break down the query on the API to smaller data sets and retrieve partition subsets and aggregate them again.

Of course, you have to reason whether to bring all data back to memory and store on server cache is gonna help with front end performance. This will also require huge memory footprint on the server.

If you don’t need all data at once and can do specific query to minimize the number of data records, this might also perform better rather than trying to deal with all data at once.

  [Test, MaxTime(90000)]
        public void GetLotsOfData_ViaHttpCallThroughWebApi()
        {
            string apiUrl = "https://yourapi.com/api/data";

            HttpClient client = new HttpClient();
            var response = client.GetAsync(apiUrl).ConfigureAwait(false);
            var results = response.GetAwaiter().GetResult();

            Assert.That(results.IsSuccessStatusCode);
        }