Archive
Log4net in VB.NET
A long time ago I wrote this post.
I’m wondering if is still valid, because I was tasked to implement log4net in a .NET framework 4.8 VB.NET Windows Form project with various classes where to implement logging, and there are differences.
First, in every case, we need the NuGet package.
You can use the Visual Studio UI to search for it and install it, or just run this command from the Package Manager Console: PM> Install-Package log4net
Add a new file to your project in Visual Studio called log4net.config and be sure to set a property for the file.
Set Copy to Output Directory to Copy Always.
This is important because we need the log4net.config file to be copied to the bin folder when you build and run your app.
In every class, I needed only to create the variable at the class level, and then in the constructor configure log4net with XmlConfigure; for example:
Imports log4net
Imports log4net.Config
Public Class clsTest
Private Shared ReadOnly logger As ILog = LogManager.GetLogger(Reflection.MethodBase.GetCurrentMethod().DeclaringType)
Public Sub New()
XmlConfigurator.Configure(New System.IO.FileInfo("log4net.config"))
Try
Dim i As Int32
Dim z As Int32 = i / 0
Debug.Print(z)
Catch ex As Exception
logger.Error(ex.Message)
End
End Try
End Sub
End Class
The task was to dump the errors both in a text file and a SQL Server table.
This is the SQL schema:
CREATE TABLE [dbo].[InternLog](
[InternLogId] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL,
[Thread] [varchar](255) NULL,
[LogLevel] [varchar](50) NULL,
[Logger] [varchar](255) NULL,
[Message] [nvarchar](4000) NULL,
[Exception] [nvarchar](4000) NULL,
CONSTRAINT [PK_InternLog] PRIMARY KEY CLUSTERED
(
[InternLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
And this is the log4net.config:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
</configSections>
<log4net>
<appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="c:\work\log\amsalert.txt" />
<appendToFile value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="10MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date [%thread] %-5level %logger - %message%newline" />
</layout>
</appender>
<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
<bufferSize value="1" />
<!--Change to 10 or MORE This is critical, it means after 10 messages then the buffer goes to database-->
<connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<connectionString value="data source=SERVER;initial catalog=SOMEDB;integrated security=false;persist security info=True;User ID=sa;Password=somepwd" />
<commandText value="INSERT INTO InternLog ([Date],[Thread],[LogLevel],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
<commandType value="Text" />
<!--<commmandText value="dbo.procLog_Insert"/><commandType value="StoredProcedure"/>-->
<parameter>
<parameterName value="@log_date" />
<dbType value="DateTime" />
<layout type="log4net.Layout.RawTimeStampLayout" />
</parameter>
<parameter>
<parameterName value="@thread" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%thread" />
</layout>
</parameter>
<parameter>
<parameterName value="@log_level" />
<dbType value="String" />
<size value="50" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%level" />
</layout>
</parameter>
<parameter>
<parameterName value="@logger" />
<dbType value="String" />
<size value="255" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%logger" />
</layout>
</parameter>
<parameter>
<parameterName value="@message" />
<dbType value="String" />
<size value="4000" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%message" />
</layout>
</parameter>
<parameter>
<parameterName value="@exception" />
<dbType value="String" />
<size value="2000" />
<layout type="log4net.Layout.ExceptionLayout" />
</parameter>
</appender>
<root>
<level value="DEBUG" />
<appender-ref ref="RollingFileAppender" />
<appender-ref ref="AdoNetAppender" />
</root>
</log4net>
</configuration>
Holes in Sql table
I’m working on a microservices solution, where is generated a code inserted into a SQL Server table.
The question is: how to verify if there are no holes in generated codes?
My table GeneratedPdfs is something as:
The last eight chars of Id are generated from a counter, it is important that there are no holes: in the limited set in figure if for example there is no the 080021U00000005 code, is a problem.
In order to verify from TSQL this is the script:
SELECT CAST(RIGHT(Id, 8) AS int) + 1 FROM [dbo].[GeneratedPdfs]
EXCEPT
SELECT CAST(RIGHT(Id, 8) AS int) FROM [dbo].[GeneratedPdfs]
this gives at least 1 record, which is the latest reached number from the counter + 1, in practice if it gives 2509 then I have the last added record with code 080021U00002508
But if it gives some other records, the given code is the beginning of a hole in numbering.
I can verify with something as
SELECT TOP (10000) [Id] ,CAST(RIGHT(Id, 8) AS int)
FROM [dbo].[GeneratedPdfs]
ORDER BY id DESC
Ends of missed id ranges can be obtained with
SELECT CAST(RIGHT(Id, 8) AS int) - 1 FROM [dbo].[GeneratedPdfs]
EXCEPT
SELECT CAST(RIGHT(Id, 8) AS int) FROM [dbo].[GeneratedPdfs]
LINQ GroupBy
In order to create a group by query with LINQ with Window function SUM and MAX and so on this could be a sample
var query = (from deb in db.Debs
group deb by new
{
deb.NumJournal,
deb.YearJournal,
deb.Subagency.AgentId
} into q
select new Journal
{
NumJournal = q.Key.NumJournal,
YearJournal = q.Key.YearJournal,
AgentId = q.Key.AgentId,
AgentName = q.Max(z => z.Subagency.Agent.Name),
Total = q.Sum(z => z.Price)
}).Where(a => (a.KeyId == 9))
.ToList();
You can see is requested to use .Key. for referring to the grouping fields.
As result we are creating a List of a Journal POCO class.
Other functions as NTILE, RANK, LAG , LEAD are not provided with base LinQ.
There is the package Linq2db for this, but I believe that nothing can be optimized as a well written TSQL query.
Insert nullable value for Foreign Key
When a foreign key in a SQL Server table can be null, for example BankId, in order to insert a null value in a object using EF Core must be used the syntax default(long?), example:
DocInvoicePayment objDocPay = new DocInvoicePayment
{
// other fields...
BankId = idBank == 0 ? default(long?) : idBank
};
ALTER COLUMN in PostGres
There was a table in PostGres 11 where some fields were incorrectly transferred from SQL Server 2016, the problem was to convert a char(1) to integer.
if you try using SQL Manager Lite something as
ALTER TABLE public."ActivityLog" ALTER COLUMN "Id" TYPE INTEGER;
There is a complain as
ERROR: column "Id" cannot be cast automatically to type integer HINT: You might need to specify "USING "Id"::integer".
With a hint as
ALTER TABLE public."ActivityLog"
ALTER COLUMN "Id" TYPE INTEGER USING (trim("Id")::integer);
finally the field was converted.
This can obtained in edit field (IN SQL Manager Lite) changing the type in SERIAL and writing (trim(“Id”)::integer) in “Change by expression” (beware of char 34 around Id…copy & paste from the above code section)
SQLDMO for older programs
Sometimes i must maintain ancient software, Visual Basic 6 applications of the last millennium.
I still wondering why opening an Visual Basic 6 project after some years, i remember that was all ok and i’m the only one in the world that opens this ancient vm, magically some DLL reference is no more working.
So some code that was using SQL-DMO was causing the error “can’t create the object”.
In a folder there was a sqldmo.dll, no way to register/unregister, delete and the add the reference.
Fortunately i found this download, after the install pointing the dll reference to c:\Programs\Microsoft SQL Server\80\Tools\binn\sqldmo.dll (is a Windows XP vm…) my program can run; by the way connecting to SQL Server 2016 Developer inside a Docker Windows container.
SQL Server Developer 2016 containerized under Docker for Windows
Until the last format of my notebook (end of August: there was no way to repair Office 2016 , and after i got a malware from the infamous CCleaner 5.33) SQL Server Developer was installed with the other programs.
But this time my approach was “no more VmWare Workstation” because it conflicts with Hyper-V needed from Docker.
Currently i’m ok with Hyper-V even if there are issues, as the access from the vm to the host that i resolved with the mapping of the host disks as “Local resouces” in the remote desktop connection: not the better approach, but for me works.
Then I decided to use SQL Server 2016 Developer in a container: could be that one day in my pc there will be only Windows 10, Hyper-V, Docker and all of the programs (Office, Visual Studio, Android Studio, SQL Server..) will be containerized?
Installed Docker for Windows, i opened a cmd shell and launched
c:\>docker pull microsoft/mssql-server-windows-developer
After some Gb of download, the Docker page tell us that we can launch
c:\>docker run -d -p 1433:1433 -e sa_password=<SA_PASSWORD> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer
Well, at this point our server is running, the sa user password was initialized to the passed value and i can do
c:\>docker ps CONTAINER ID IMAGE <other columns..> 5fd521dc8565 microsoft/mssql-server-windows-developer
We have the CONTAINER ID that we can use for connecting to our instance, for example (note that we can use the entire CONTAINER ID but also only the first 3 chars)
docker exec -it 5fd sqlcmd -S. -Usa -P<SA_PASSWORD>
and if all is ok we can see the SQLcmd prompt, launch some commands:
1> select @@version
2> go
------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)
(1 rows affected)
3> Quit
Ok well, but at this point I would use SQL Server Management Studio, use my MDF files that are in the main pc filesystem.
First of all, by default the containers change the container name (sometimes with hilarious names, as “drunken_borg”) and the IP, which is tipically a private IP network 172.xx.xx.xx.
If you do a
c:\>docker stop 5fd
and then relaunch the “docker run” you will see (with a docker inspect CONTAINER_ID) that the container name and the IP are changed, and if some services rely on your SQL is a problem.
But for the moment we connect from SQL Management studio on the host, if our server has IP 172.26.80.30 we would try
But without success, we have an error about the certificate chain:
After some head scratching, i finally found that we need to set in Options >> Additional Connection Parameters this magic setting, TrustServerCertificate=True:
Done this, we can finally see our server:
But if we try to attach our MDF files that are in the host, no way, we can explore only the local c: (of the containerized Windows Server which runs our SQL instance)
The solution is to use the Docker Volume parameter: we need to changes some parameters in the SQL server starting, so close SQL Management Studio, run a
c:\>docker stop <CONTAINER ID>
and then
c:\>docker rm <CONTAINER ID>
The new command is:
c:\>docker run -d -p 1433:1433 --name sqlcontainer1 –hostname mysqlhost --ip 172.26.80.30 -v G:\DbAndMail:c:\data -e sa_password=<SA_PASSWORD> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer
In this command:
-p 1433:1433 : maps the local port 1433 to the 1433 port of the container
–name mysqlhost: we will have our defined host name, no “drunken_borg”
–ip 172.26.80.30: we define a fixed IP for our instance, so the connected clients that uses our SQL can rely on a certain value
-v G:\DbAndMail:c:\data : we map the host path G:\DbAndMail on the container c:\data; the path inside the container will be created if not exists.
Entering in SQL Management Studio, we can this time explore our host path mounted in c:\data in order to attach the already existents MDF files.
At this point if we launch
c:\>docker stop <CONTAINER ID>
and then
c:\>docker run -d -p 1433:1433 --name sqlcontainer1 …
Docker complains that sqlcontainer1 already exists, we need a docker rm.
But in this manner we lose the changes, if we attached an existing MDF in the previous SQL Management Studio session every previous change in the SQL data is maintained but we must re-attach the MDF, every configuration change for example
exec sp_configure "remote access", 1 reconfigure
is lost: is another instance.
So we need to see the NEW_CONTAINER_ID with
c:\>docker ps – a CONTAINER ID IMAGE … 05e581c12329 microsoft/mssql-server-windows-developer …
where we can see the stopped containers, and then launch
c:\>docker start -ai 05e CONTAINER ID IMAGE … 05e581c12329 microsoft/mssql-server-windows-developer … where we can see the stopped containers, and then launch c:\>docker start -ai 05e VERBOSE: Starting SQL Server VERBOSE: Changing SA login credentials VERBOSE: Started SQL Server. TimeGenerated EntryType Message ------------- --------- ------- 9/26/2017 4:55:56 PM Information Software Usage Metrics is enabled. 9/26/2017 4:55:55 PM Information Recovery is complete. This is an informatio... 9/26/2017 4:55:55 PM Information Service Broker manager has started. 9/26/2017 4:55:55 PM Information The Database Mirroring endpoint is in disab... 9/26/2017 4:55:55 PM Information The Service Broker endpoint is in disabled ... 9/26/2017 4:55:55 PM Information The tempdb database has 2 data file(s). …
We can see that in SQL Management Studio nothing was changed, we find our previously attached MDF and other changes.
SQL Server 2016 Service Pack 1 with updates
https://www.microsoft.com/en-us/download/confirmation.aspx?id=54613
But before is required to install the original Service Pack 1 :
https://www.microsoft.com/en-us/download/confirmation.aspx?id=54276
Updates can be verified at https://sqlserverbuilds.blogspot.it/
From COBOL to SharePoint 