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).

 

Advertisements

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);
        }

 

LINQ SQL – FULL OUTER JOIN

Possibly save 1 hour of your time: There is no equivalent of full outer join in LINQ that is same as SQL.

The solution is to do a left outer join and then a right outer join using DefaultIfEmpty and then use UNION to join these two results.

var leftouterjoin = (from items1 in table1
                    join items2 in table2
                    on new { items1.column1, items1.column2 } equals new { items2.column1, items2.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items1.column1, column2 = items1.column2, Value = items1.Value });

var rightouterjoin = (from items2 in table2
                    join items1 in table1
                    on new { items2.column1, items2.column2 } equals new { items1.column1, items1.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items2.column1, column2 = items2.column2, Value = items2.Value });

                var fullouterjoin = leftouterjoin.Union(rightouterjoin).ToList();

                var returnList = fullouterjoin.Select(x => new YourClass
                {
                    column1= x.column1,
                    column2= x.column2,
                    Value = x.Value,
                }).ToList();

If you have null values, your UNION may return duplicates. The key is to add Distinct() after your UNION().

There is a difference between SQL UNION (which performs an implicit DISTINCT) and LINQ Union (which required an explicit Distinct()).

LINQ System.NotSupportedException – The entity or complex type cannot be constructed in a LINQ to Entities query.

Possibly save 1 hour of your time: In LINQ to SQL syntax, you cannot project into your class directly. In the example below, we also show how to do joins on multiple columns using anonymous type.

You may get the following exception:

  • System.NotSupportedException : The entity or complex type ‘YourObject’ cannot be constructed in a LINQ to Entities query.

You cannot select new class from your class directly. You need to store result on a variable and execute the query and then use LINQ lambda syntax to project to your class.

 

var list = (from items1 in table1
join items2 in table2
on new { items1.column1, items1.column2} equals new { items2.column1, items2.column2}
select new { column1 = items1.column1, column2 = items1.column2, value = items2.Value }
).ToList();

var returnList = list.Select(x => new YourClass
{
column1= x.column1,
column2= x.column2,
Value = x.Value
}).ToList();

 

NUnit – test performance time on your method

Possibly save 1 hour of your time: There will be times you want to write some integration unit tests that hit the database to test performance.

One easy way to impose the maximum time in milliseconds on the unit test is using the MaxTime property available on NUnit and can be done as follows:

 [Test, MaxTime(2000)]
public void YourMethod()

Tip: You might also want to do an initial call to your database in OneTimeSetup to simulate first request will take longer time.

[OneTimeSetUp]
public void OneTimeSetUp()
{

// do a call to database to simulate that first request will take longer time

}

 

SSIS – File path is not valid – job failed on reading second file path

Possibly save 1 hour of your time: We have a SQL Server agent job that runs multiple SSIS packages. Each package is independent. Each package will process a data file that is located on another server. The data files for all packages are stored at same location. When the job runs, it always failed on the second package regardless of which data file it is looking at. So if step 1 ran, then step 2 will failed. The next time you run it, Step 2 will passed and Step 3 will failed on reading the file. The error will say the file path to the file is not valid.

Funny enough, when we give full administrator right to the identity running the job, all the steps passed.

We start thinking this was a permission issue so we check all the database permissions for this identity and could not find the issue.

Solution: We found out that there was a mapped share drive created. This was causing the issue. We use UNC path as environment variable to find the file. However the mapped share drive has the same beginning path like \\server\folder. Once we removed the mapped share drive, the job is reading subsequent file properly and behaving as expected.

SSIS – Could not find stored procedure sp_ssis_addlogentry

Possibly save 1 hour of your time: For some reason when the SSIS package was deployed in a different environment and when it ran, it gives this error.

The stored procedure is not automatically created.

Solution: In my case, the issue was actually related to the SQL Server Agent job not setup probably to tie to the environment variables. For some reason that causes this issue.

After the job steps have the environment variables linked, then job is running fine and the stored procedure was automatically created.

 

The type initializer for threw an exception static class

Possibly save 1 hour of your time: I got this error when I am creating unit test to test a static class.

The type initializer for ‘YourClassName‘ threw an exception.

You can debug and look at the details of the Inner Exception property.
In my case, I see this error.
{“Value cannot be null.\r\nParameter name: String”}

If you are still not sure, debug into your static class by putting a break point in the first line of the class.

My issue turns out that there was ConfigurationManager.AppSettings value missing in the app.config file.

T-SQL – split a string value based on comma or another delimiter

Possibly save 1 hour of your time: This shows how you can take a column that has data such as a string value with comma separated value or semi-colon separated value and process each value for other purpose. In my case I want to check for foreign key not exist.

</pre>
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
SELECT [column_with_delimiter_separated_values]
FROM [dbo].[YourTable]

DECLARE @textString varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @textString

DECLARE @tempSplitText TABLE
(
SplitString nvarchar(500)
)

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @delimiter char = ';'
DECLARE @pos INT = 0
DECLARE @len INT = 0
DECLARE @value varchar(500)

WHILE CHARINDEX(@delimiter, @textString, @pos+1)>0
BEGIN
set @len = CHARINDEX(@delimiter, @textString, @pos+1) - @pos
set @value = SUBSTRING(@textString, @pos, @len)
INSERT INTO @tempSplitText(SplitString) VALUES(RTRIM(LTRIM(@Value)))
set @pos = CHARINDEX(@delimiter, @textString, @pos+@len) +1
END

IF (LEN(@region) > @len) --deals with last element
BEGIN
set @value = SUBSTRING(@textString, @pos, LEN(@textString)-@pos+1)
INSERT INTO @tempSplitText(TextString) VALUES(RTRIM(LTRIM(@Value)))
END

FETCH NEXT FROM @MyCursor
INTO @textString
END;

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

select r. from @tempSplitText r
left join [dbo].[YourTableWithTheForeignKey] c on r.SplitString = c.ForeignKey
where c.ForeignKey is null
<pre>

 

SSIS – no column information was returned by the sql command

Possibly save 1 hour of your time: In SSIS, I have a data flow task that selects the source with some logic using table variable. It causes this compilation error when you try to save.

SSIS doesn’t know like using table variable in the SQL command.

Solution:

I have some complex logic so I need to use variable table. Temp table would most likely have same issue. So I just use a stored procedure instead. In my SQL command I have exec MyStoredProcedureName.