How to remove an identity from a column in SQL Server

Removing an identity in SQL Server

We have a table with an identity column. We have to delete the identity from this column.
Unfortunately, in SQL Server, once we have set an identity, we cannot delete it: to remove an identity we must drop the column.


If we want to preserve the data in our column, we must follow some simple steps.
Let's see these steps with an example.


This is our table:

CREATE TABLE dbo.Log
    (
    ID           INT IDENTITY NOT NULL,
    ERROR        VARCHAR(1000),
    STACKTRACE   VARCHAR(1000),
    ...
    CONSTRAINT   PK_LOG PRIMARY KEY (ID)
    )
GO



1. add a new column identical to the column with the identity

ALTER TABLE Log ADD ID_Temp INT


2. copy the content of the column ID into the new column

UPDATE Log SET ID_Temp = ID


3. drop the old column

ALTER TABLE Log DROP COLUMN ID

Actually, in our example table we have to drop the primary key before we can drop the column, so the right sequence of statements is this one:

ALTER TABLE Log DROP PK_LOG
ALTER TABLE Log DROP COLUMN ID


4. rename the new column

sp_rename 'dbo.LOG.LGNUM2.ID_Temp', 'ID', 'COLUMN'


5. in our example we should also establish the primary key again

ALTER TABLE Log ADD PRIMARY KEY (ID)


In this way we can in the end delete an identity, preserving all existing data in our table.

.NET WCF webservices: single wsdl before framework 4.5

How to generate a single wsdl with a self hosted WCF web service

WCF services use to export multiple wsdl files.
This works fine when you remain within Windows environment, but it can generate problems when you have to interoperate with different systems.

With .NET framework 4.5 you can directly ask the webservice to generate a single wsdl file. But what if you are using an older framework?

In my case I had to receive data from a system integration program which could not import a wsdl splitted into different files: I got 2 wsdl file and 4 XSD files.

Multiple XSD files

First of all I searched the internet for a solution to the XSD file splitting issue.

I discovered that it was necessary to create a new endpoint behavior and enforce it to the endpoint.
I found a couple of implementations of what I needed: the first one is WcfExtras, and the other one is FlatWsdl.

WcfExtras is good, but it looks like it works well if you use the config file.
My webservice adds the endpoints and the bindings programmatically, so the config file is not used.

Then I tried FlatWsdl. Actually I used a piece of code from FlatWsdl that I found here.

This is the piece of code:


using System.Collections;
using System.Collections.Generic;
using System.ServiceModel.Channels;
using System.ServiceModel.Description;
using System.ServiceModel.Dispatcher;
using System.Xml.Schema;
using System.ServiceModel.Configuration;
using ServiceDescription = System.Web.Services.Description.ServiceDescription;

namespace Thinktecture.ServiceModel.Extensions.Description
{
    public class FlatWsdl : BehaviorExtensionElement, IWsdlExportExtension, IEndpointBehavior
    {
        public void ExportContract(WsdlExporter exporter, WsdlContractConversionContext context)
        {
        }

        public void ExportEndpoint(WsdlExporter exporter, WsdlEndpointConversionContext context)
        {
            XmlSchemaSet schemaSet = exporter.GeneratedXmlSchemas;

            foreach (ServiceDescription wsdl in exporter.GeneratedWsdlDocuments)
            {
                List<XmlSchema> importsList = new List<XmlSchema>();

                foreach (XmlSchema schema in wsdl.Types.Schemas)
                {
                    AddImportedSchemas(schema, schemaSet, importsList);
                }

                wsdl.Types.Schemas.Clear();

                foreach (XmlSchema schema in importsList)
                {
                    RemoveXsdImports(schema);
                    wsdl.Types.Schemas.Add(schema);
                }
            }
        }

        private void AddImportedSchemas(XmlSchema schema, XmlSchemaSet schemaSet, List<XmlSchema> importsList)
        {
            foreach (XmlSchemaImport import in schema.Includes)
            {
                ICollection realSchemas =
                    schemaSet.Schemas(import.Namespace);

                foreach (XmlSchema ixsd in realSchemas)
                {
                    if (!importsList.Contains(ixsd))
                    {
                        importsList.Add(ixsd);
                        AddImportedSchemas(ixsd, schemaSet, importsList);
                    }
                }
            }
        }

        private void RemoveXsdImports(XmlSchema schema)
        {
            for (int i = 0; i < schema.Includes.Count; i++)
            {
                if (schema.Includes[i] is XmlSchemaImport)
                    schema.Includes.RemoveAt(i--);
            }
        }

        public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters)
        {
        }

        public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)
        {
        }

        public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher)
        {
        }

        public void Validate(ServiceEndpoint endpoint)
        {
        }

        public override System.Type BehaviorType
        {
            get { return typeof(FlatWsdl); }
        }

        protected override object CreateBehavior()
        {
            return new FlatWsdl();
        }
    }
}


After copying this code (special thanks to Christian Weyer), all I had to do is adding the new behavior to my endpoints:

var ep = _hostTMS.AddServiceEndpoint(servEndpoint, binding, "");
ep.Behaviors.Add(New FlatWsdl());


In this way I obtained a wsdl which included all the previously splitted XSD pieces.

Multiple wsdl files

Anyway I hadn't still finished: in fact I had 2 wsdl files, referencing each other via <wsdl:import>.
I need one independent wsdl file, so my result wasn't still correct.

I found out that the WCF service splits wsdl file when the contract, the implementation and the binding have a different namespace.

I had defined the namespace in the binding, but I had forgotten to define it in the contract and in the implementation.
This is what I did to solve this issue:

binding.Namespace = "http://MyNamespace";
...

[ServiceContract(Namespace = "http://MyNamespace")]
Public Interface IMyService
{
   ...

}

[ServiceBehavior(Namespace= "http://MyNamespace")]
Public Class MyServiceImpl : IMyService
{
   ...
}

For the details, read this post.

In this way I finally obtained one single wsdl!

Using Log4Net with Visual Basic .NET

Using Log4Net withVisual Basic .NET

Log4Net is the porting of Log4J into the .NET environment.
This is a simple introduction about using Log4Net with Visual Basic .NET.

Getting Log4Net dll and including it in a project

• Download Log4Net from the Apache site: https://logging.apache.org/log4net/
• then unzip the downloaded file into a folder, i.e. C:\Log4Net
• In this way, in the folder C:\Log4Net\log4net-x.x.xx\bin\net  (where x.x.xx is the current version), you should find some subfolders, named as the .NET framework versions: 1.0, 1.1, 2.0, 3.5, 4.0, 4.5, ...
• Inside each of them there is a release folder, and inside release ther's log4net.dll, which is the file you need.
• In you project, add a reference to log4net.dll. 

Log4Net configuration file

Log4Net uses a configuration file named in this way:

<assemblyname>.log4net


For example:
VatCalculator.exe.log4net  
MathLibrary.dll.log4net


This is an example of Log4Net configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <log4net>
    <appender name="MyLogAppender" type="log4net.Appender.RollingFileAppender">
      <file value="MyLog.log" />
      <appendToFile value="true" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="3" />
      <maximumFileSize value="500KB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %-5level - %message%newline" />
      </layout>
    </appender>
    <logger name="MyExampleLogger">
      <level value="ALL" />
      <appender-ref ref="MyLogAppender" />
    </logger>
  </log4net>
</configuration>


You can see that there are 2 main sections:

• appender
• logger

The appender defines file name, type and behaviour.
The logger  specifies the name you use in your code to refer the logger.
In this example in the code we refer to the logger using the name MyExampleLogger.
This logger is linked to the appender MyLogAppender through the appender-ref tag.

In this way you can change the appender (so file name, behaviour, etc.) without changing the code.

The main features ot the appender section are:

• the appender type: in this case we use the type log4net.Appender.RollingFileAppender, which intruduces a sort of rotation: when the log file reaches a certain condition (in this case the size), it's renamed (file name gets a suffix) and a new file is created. This process goes on till tha max number of files is reached, then the oldest file is deleted.
  In this way the log dimensions is kept under control.
• the file tag sets the log file name
• if the appendToFile tag is set to true, when the program starts and finds an existing log file, it appends new row to it; otherwise it creates a new file
• The rollingStyle tag decides what is the conditions that forces the creation of a new file; in this case Size means that the condition is the size value you can find in the tag maximumFileSize.
• maxSizeRollBackups is the max number of old log files kept, before the oldest one is deleted.
• The layout tag contains information on how the log file rows are formatted

 Adding the configuration file to the project

• Add a text file to the project, remember to use the right name assemblyname.exe.log4net
• Paste the configuration from above into the file and make your customizations
• In the file properties, set the property "Copy to output directory" to the value "Copy if newer"
• Add this line to the AssemblyInfo.vb file:
  
  <Assembly: log4net.Config.XMLConfigurator(ConfigFile:="EasyStorLibCustom.dll.log4net", Watch:=True)>
  
  Note that specifying Watch:=True forces the program to reload the configuration when it's changed: in this way you can chage the configuration without restarting you application
 

Using the logger in the code

• First of all a logger object must be created:
  
      Private _logger As log4net.ILog
  ...
  _logger = log4net.LogManager.GetLogger("MyExampleLogger")
  
  Note that the logger name is the one declarated in the logger name tag in the configuration file

• Now the logger can be used to log informations. Log4Net provides some mothods to write the log file. The difference between them is the level of detail: you can write normal informations, debug, errors, fatal errors.
  The level can be logged in the file: see this config file extract, where the level is specified in the format:
  
     <conversionPattern value="%date [%thread] %-5level - %message%newline" />
  
  You can specify what levels you want to log, declaring it in the config file:
  For exapmple,
  
     <level value="ALL" />
  
  means that all level must be logged.
  
  These are the levels:

   ALL
   DEBUG
   INFO
   WARN
   ERROR
   FATAL
   OFF 

The logger will be forced to log all the levels below the specified level.

  
• The logger mothods reflect the levels:
  
        _logger.Debug(message)
   _logger.Info(message)
   _logger.Warn(message)
   _logger.Error(message)
   _logger.Fatal(message)

     

 

How to get a list from a dataview using Linq

How to get a list from a dataview using Linq

In .Net is common to get data from a database into a dataview.
To use these data it can be useful to convert them into a list.

This is how to do that in VB.Net.

dim dataFromDB as DataView = GetDV("select ...")

dim dataList as List(Of String) = _
dataFromDB.Cast(Of DataRowView).Select(Function(dr) dr("fieldName").ToString).ToList


How to change a table name in Microsoft SQL Server

How to change a table name in Microsoft SQL Server