log4net AdoNetAppender stops logging / does not recover if database connection is lost / database is down, ReconnectOnError

I use the Apache log4net AdoNetAppender to log information from my application into a SQL database. In case, the network connection to the database is lost or the database is down for some reason, the AdoNetAppender quits logging (forever) to the database and does not recover even if the database is online again.

How-I-fixed-it:
I found the right hints here:
https://www.mail-archive.com/log4net-user@logging.apache.org/msg04655.html
http://logging.apache.org/log4net/release/sdk/log4net.Appender.AdoNetAppender.ReconnectOnError.html

Add the tag:

to the log4net XML config file:



   

Keep in mind, that it then tries to reconnect to the database until the connect times out. This blocks the thread of the application!

You can add:
Connect Timeout=1 to the connectionString  property to Limit the blocking to 1 second:
   
   
   
  


   
   
   
   
  

Where can I find online tutorials to learn C# .NET programming?

I was looking for online tutorials to learn and improve C# programming. Here is just a list of tutorials I found to remember them and to share.

List of C# tutorials:
http://www.tutorialspoint.com/csharp/index.htm
http://www.csharp-station.com/
http://www.csharpme.de/
http://msdn.microsoft.com/en-us/library/aa288436%28v=vs.71%29.aspx
http://openbook.galileocomputing.de/csharp/

C# LINQ Query on SQL-Server View using Entity Framework 5 returns duplicate rows because of non-unique values in records first colum

In my C# VS2012 Project I'm using EF5 to query an SQL Server 2008 R2 database. Today we run into the strange issue that a query on a view  returned the expected number of rows, but the data contained many duplicate records. Running the created SQL-query in Management Studio, the results were fine.

How-I-fixed-it:
I found the right hints here: http://stackoverflow.com/questions/3977920/entity-framework-view-return-duplicate-records
and here:
http://jepsonsblog.blogspot.in/2011/11/enitity-framework-duplicate-rows-in.html?showComment=1348809764880#c1389404724781617559

The Problem was, that the FIRST column of my view was not a unique key, which seems to confuse EF.

Running the query in Management Studio returned:

Col1,Col2,Col3
1,0,1
1,1,1
1,2,1
2,0,7

In my code the result list of records was:
Col1,Col2,Col3
1,0,1
1,0,1
1,0,1
2,0,7

According to http://stackoverflow.com/questions/3977920/entity-framework-view-return-duplicate-records it's important, that the first colum of the query result contains a unique key:

When including a view in your Entity Model, the model seems to simply use the first not-nullable columns as primary key (as all columns used in the primary key should be non-nullable).

The suggestion how to fix it is to use the ROW_NUMBER () OVER () SQL to create an additional FIRST column in you view which has unique keys. Then update the model in Visual Studio.

SQLCLR SQL-Server UDF/SP binary parameter is cut after 8000 Bytes

I created an SQLCLR C# Stored Procedure which expects a paramter as large byte[] and returns the result in a large byte[].

Using the SQLBinary class as default, i run into the problem that the parameter was cut after 8000 Bytes.

How-i-fixed-it:
I found the right hints here:
http://stackoverflow.com/questions/840552/clr-udf-returning-varbinarymax

SqlBinary is VARBINARY(8000) while the default for SqlBytes is VARBINARY(MAX)

I found two ways to overcome the 8000 Bytes limit:

For the large return value i used:
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Data", SqlDbType.VarBinary, -1));

For the input Parameter i used:
SqlBytes roiBytes

C# SQLCLR SqlMetaData with SqlDbType.Float column getting InvalidCastException on SqlDataRecord.SetFloat

In my SQLCLR C# Stored Procedure on SQL Server 2008 R2 i returned the results using a SqlDataRecord that has a column of SqlDbType.Float.

Trying to set the value using the SetFloat Method, i got an InvalidCastException.

How-i-fixed-it:
I found out that SqlDbType.Float is equal to a System.Double data type. Using the SetDouble Method it works fine.

Here i found the right hints:
http://msdn.microsoft.com/de-de/library/vstudio/system.data.sqldbtype(v=vs.80).aspx

http://msdn.microsoft.com/de-de/library/vstudio/microsoft.sqlserver.server.sqldatarecord.setfloat.aspx

http://msdn.microsoft.com/de-de/library/vstudio/microsoft.sqlserver.server.sqldatarecord.setdouble.aspx






 



 

SQLCLR error: Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)

I was referencing the "System.Drawing" DLL (Version 2.0.0.0) in my SQLCLR Assembly Project.
It was running fine for ages on the Server until i deployed a new version of my Assembly to it today:
Error message when you execute a CLR routine or use an assembly in SQL Server: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)"

How-i-fixed-it:
I was able to fix my problem by executing this T-SQL command on my database:

Alter Assembly [System.Drawing]
From 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll'

The issue is also discussed here:
http://social.technet.microsoft.com/Forums/sqlserver/en-US/17df0715-7608-480d-8ad6-78dcf6c9d43a/different-version-in-gac-and-SQL

Link to Microsoft Knowledge Base Article:
http://support.microsoft.com/kb/949080

How to have different local web.config settings for a Visual Studio ASP.NET C# Web TFS Project?

Today we started an ASP.NET C# Project under VisualStudio 2012 using Team Foundation Server. We ran into the problem that we need to store some local path settings in the web.config file, but they are different on every developers computer.

How-I-fixed-it:
The solution is, to split the web.config up into several files and just reference the files using the configSource="sectionName.config" Attribute in the corresponding section in the web.config. Don't add these local files to the project, to not get them overwritten by each other.

Here's a sample:

web.config:
<applicationSettings>

<KCM.Properties.Settings configSource="KCM.Properties.Settings.config" />

</applicationSettings>

KCM.Properties.Settings.config:

xml version="1.0" encoding="utf-8" ?>

<KCM.Properties.Settings>

<setting name="DataStoragePath" serializeAs="String">

<value>D:\inetpub\ftproot\</value>

</setting>

</KCM.Properties.Settings>
Here i found the right information to get the solution:

http://code-inside.de/blog/2009/11/03/howto-eine-eigene-configsection-schreiben-custom-configsections/

http://blog.andreloker.de/post/2008/06/16/Keep-your-config-clean-with-external-config-files.aspx

http://www.tomot.de/en-us/article/5/asp.net/how-to-use-web.config-transforms-to-replace-appsettings-and-connectionstrings

https://devio.wordpress.com/2013/04/24/handling-web-config-variants-in-source-control-based-solutions/

Where can i find a free hosted Team Foundation Server Service?

I was looking for a free hosted service of Microsoft Team Foundation Server to have version control for my Visual Studio software projects.

I found out, that Microsoft itself offers a free plan for up to 5 users under: http://tfs.visualstudio.com/

What it (currently) includes :

·         Up to 5 users

·         Unlimited number of projects

·         Version control (TFVC or Git)

·         Work item tracking

·         Agile planning tools

·         Virtual team rooms

·         Build (still in preview)

·         Test management (still in preview)

Can't open sqlproj with Visual Studio 2012? Install SQL Server Data Tools.

I recently updated to Visual Studio 2012 Ultimate.
When i tried to open an existing SQL Server SQLCLR Project (*.sqlproj), it says that the sqlproj is incompatible with the version I am running.

How-i-fixed-it:
I installed the SQL Server Data Tools from http://msdn.microsoft.com/en-us/data/tools.aspx.

I found the solution here:

http://stackoverflow.com/questions/11906691/visual-studio-2012-csproj-and-sqlproj

and here:
http://www.comoke.com/index.php/2012/10/visual-studio-2012-database-project-sqlproj-project-type-not-supported/

and here:
http://blog.tentaclesoftware.com/archive/2013/02/11/visual-studio-2010-and-2012-sqlproj-interoperability.aspx

SQL Server Message 6260, system.overflowexception because of arithmetic overflow, error caused by divison by 0

One of my C# SQLCLR table valued function started to throw an exception with some recently added data to the database:

SQL Server Message 6260
system.overflowexception Arithmetic Overflow

How-i-fixed-it:
It turned out to be caused by a divison by 0 happening at runtime because of bad records in the data. I added some code to check for this which fixed the problem of the arithemtic overlow.

Matlab Application Type Library MLApp COM-Object not shown in Visual Studio 2012 Add Reference Dialog

I wanted to add a reference to the Matlab Application Type Library MLApp to my C#-Project to call Matlab using COM, but the add reference dialog did not list it.

How-i-fixed-it:

1. Open a windows command prompt as administrator
2. Navigate to your Matlab installation directory\bin
3. matlab.exe /register
4. In the matlab command windows enter: !matlab -regserver

Here i found the right hints:
http://www.mathworks.de/support/solutions/en/data/1-6JHG55/

How to update Linq to SQL dbml database layout in Visual Studio 2012?

I have not been working with the Linq to SQL tool before and had to make some changes to an existing Visual Studio 2012 project. Some new colums were added to a database table and i needed to update the corresponding dbml file in the Visual Studio project.

How-i-fixed-it:
I found the solution here: http://stackoverflow.com/questions/1110171/how-to-update-linq-to-sql-dbml-file

Delete the modified tables from the designer, and drag them back onto the designer surface from the Database Explorer. I have found that, for this to work reliably, you have to:
a. Refresh the database schema in the Database Explorer (right-click, refresh)
b. Save the designer after deleting the tables
c. Save again after dragging the tables back.

Use System.Drawing.dll from SQLCLR C# code on SQL-Server

I created a SQLCLR-Assembly that uses the System.Drawing Namespace. I was not able to deploy it from Visual Studio to the SQL Server because of the error message:

SQL72014: .Net SqlClient Data Provider: Meldung 6503, Ebene 16, Status 12, Zeile 1 Die system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.-Assembly wurde im SQL-Katalog nicht gefunden.

How-i-fixed-it:

In SQL-Server Management Studio open up a new query:

ALTER DATABASE your_db_name
SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [System.Drawing]
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll'
WITH PERMISSION_SET = UNSAFE
GO

Maybe you receive the following error:

Msg 33009, Level 16, State 2, Line 2
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION Statement.
Here if found the solution: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81989

USE your_db_name
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
 

C# HRESULT comparison

Thanks to Rick Strahl for this great blog entry that saved me a lot of time solving a COM Interop issue:


COMException exposes this HRESULT as an int value in the Exception's ErrorCode property. Now in my brain freeze haze I tried something like this:

try{ this.OutputBuffer = this.ComInstance.GetType().InvokeMember("ProcessHit", BindingFlags.InvokeMethod, null, this.ComInstance, new object[1] { this.CompleteInputBuffer }) as string;}catch (COMException ex){ // *** RPC Server Unavailable if ( ex.ErrorCode == 0x800706ba ) { // try to reload the server if (this.LoadServer()) return this.CallComServer(); } this.ErrorMessage = ex.Message; return false;}catch (Exception ex){ this.ErrorMessage = ex.Message; return false;}finally{ this.EndRequest();}

Now that seems like it should work well enough right?

Eh, not quite. The problem is that the constant hex value is created as a uint while the HRESULT is a signed integer. The code compiles just fine but at runtime the comparision may fail (depending on the HRESULT's actual value - high values like the one above (and aren't just about all COM errors 0x800... ) will fail.

So quick - how do you create a signed hex constant?

I don't know, but this works as expected:

if ( (uint) ex.ErrorCode == 0x800706ba )