In a SQL Server database I have a table called users which has a column named signature. This column is of type Image.

My production SQL Server is located in a shared hosting environment.

One problem I’ve been facing lately is that I need permission to execute an UPDATE statement to insert a signature image for a given user. This problem occurs because to insert a signature image for a given user I have to execute a BULK statement like this for example:

--Update existing user
UPDATE users SET [signature] =(SELECT MyImage.* from Openrowset
Bulk 'C:\MyProject\trunk\MyCompany.Product\MyCompany.Product.Web\
Images\Signature.jpg', Single_Blob) MyImage) where Id 
= '1111aaaa-1111-11aa-a111-111111a1a1a1'

The query above works fine in my local machine but when I tried to execute it on the remote/production server, I got this beautiful message:You do not have permission to use the bulk load statement.

In the shared hosting environment the execution of Bulk command is disabled by default for security reasons. This is annoying but totally understandable!

Using Bulk load - allows the user to populate a database from a file. It’s not available in shared environment because it is necessary to insert client files on SQL server locally (the production server).

So… great! I need a way to bypass this limitation because I won’t spend tubes of money paying a dedicated server… it’d make sense if and only if I needed a dedicated server.

1st try: import a specific user row from my local SQL Server to the remote instance using SQL Server Management Studio Import task. I got an error about constraint key violation because I already had the same row (for that user) on both databases. It’s just a matter of updating the signature column in the production database. This seemed to be a pain path.

2nd try: consider a dedicated server? No thanks… hehehe

3rd try and solution: a few days later I found myself thinking about this problem again (this signature column updating thing is a recurring task) and so I decided to find another way and it came to light - link the remote server to my local SQL Server Express instance and write a beautiful SQL query that does the job.

First I stopped in this excellent blog post with a step by step guide written by jen: Create Linked Server SQL Server 2008
This post provided everything I needed to link both SQL Server instances.

To make sure you have linked your server correctly, you can execute this query in your local server:

select server_id, name, product, provider, data_source, [catalog], is_linked
from sys.servers

The above query gave me this result:

Figure 1 - Linked servers linked to my local SQL Server Express instance

Then I Googled about Update with Select + SQL Server or something like that and found this StackOverflow question: Updating a table with multiple values from a select statement where the date matches. Lieven’s answer helped. I just had to adapt it to my case. This is the SQL code that does the dirty work:

UPDATE  U
SET     U.[signature] = users.[signature]
FROM    [LOCAWEB].[laudotech].[dbo].[users] U INNER JOIN users
ON users.id = U.id AND
U.id = '1234aaaa-5678-90aa-b123-456789a0a1a2'

The above query must be executed within the context of the local SQL Server instance of course. Where the linked server resides.

To give you a view… this is how all this is configured inside SQL Server Management Studio (SSMS):

Figure 2 - SSMS Object Explorer and the Linked Server LOCAWEB in my local SQL Server Express instance

There’s so many things one can do with SSMS that I feel really happy in learning one more of those things. Last week I blogged about Import/Export SQL Server database data with SSMS. Take a look at it.

Man! Have I said that I Iike working with databases!?

Hope it helps.

Have you ever wondered how to log the SQL generated by NHibernate?

This post tries to exemplify just that.

NHibernate uses HQL to leverage its expressiveness to the developer, but behind the scenes there is an engine that transforms the HQL into pure SQL that is executed against the database. This SQL can be logged so that you can see its structure and get a snapshot of what the database engine receives.

log4net is a logging tool that helps the developer see what SQL NHibernate is generating under the covers.

This is a brief description of log4net taken from its homepage:

log4net is a tool to help the programmer output log statements to a variety of output targets.

First and foremost you need to enable NHibernate logging in its configuration file. The property that sets this is hibernate.show_sql.

<add key="hibernate.show_sql" value="true" />

The following piece of code shows how to configure an appender and a logger that makes use of the appender. This code is kept inside the Web.config file in the log4net configuration section:

<appender name="NHibernateRollingFileAppender" type="log4net.Appender.RollingFileAppender">
    <file value="LogNHibernate.txt"/>
    <appendToFile value="true"/>
    <rollingStyle value="Size"/>
    <datePattern value="yyyyMMdd"/>
    <maxSizeRollBackups value="10"/>
    <maximumFileSize value="10MB"/>
    <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date - %message%newline"/>
    </layout>
</appender>

<logger name="NHibernateLogger" additivity="false">
    <level value="DEBUG"/> <!-- ALL, DEBUG, INFO, WARN, ERROR, FATAL or OFF -->
    <appender-ref ref="NHibernateRollingFileAppender"/>
</logger>

I’ll describe each part of the above code.

<appender name="NHibernateRollingFileAppender" type="log4net.Appender.RollingFileAppender">

Appender is an output destination. In this case its a RollingFileAppender. It writes logging events to a file in the file system.

<file value="LogNHibernate.txt"/>

The file property specifies the name of the file that’ll store the logs.

<appendToFile value="true"/>

The appendToFile property is set to true so that the appender will overwrite existing files.

<rollingStyle value="Size"/>

The rollingStyle property set how to roll log files. In this case the appender will roll log files based on the file size.

<datePattern value="yyyyMMdd"/>

To change the rolling period we need to set the datePattern property. It would be used if we adopted a rollingStyle based on Date instead of Size.

<maxSizeRollBackups value="10"/>
<maximumFileSize value="10MB"/>

Up to 10 old files of 10MB each will be kept. These rolled files will be named: LogNHibernate.txt.1, LogNHibernate.txt.2, LogNHibernate.txt.3, etc...

<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date - %message%newline"/>
</layout>

A layout enables us to customize the output format. This is accomplished by associating a layout with an appender.

The PatternLayout, lets the user specify the output format according to conversion patterns similar to the C language printf function.

<logger name="NHibernateLogger" additivity="false">

This is the logger and its additivity property controls appender accumulation, that is, how the logs are printed in the hierarchy of loggers.

For example, the output of a log statement of logger NHibernateLogger will go to all the appenders in NHibernateLogger and its ancestors. This is the meaning of the term "appender additivity".

<level value="DEBUG"/>

The level property controls the amount of information you want to be written to the log.

<appender-ref ref="NHibernateRollingFileAppender"/>

The property appender-ref specifies what appenders this logger uses.

That’s it! :-)

With this basic configuration you can start you search for points where NHibernate is generating to much queries where it shouldn’t.

I’m currently working on a performance branch where I’m learning how to deal with NHibernate lazy configuration.

This process of logging the SQL generated by NHibernate plays a great role when one is solving the bottlenecks involved in performance implications.

Just one note: keep in mind that the process of logging is by itself an onerous one. The amount of data that gets written by NHibernate is expressive and depending on the level of information you set inside the logger, the file size will grow very fast.

Hope this helps the fellow developers.

References

log4net homepage
http://logging.apache.org/log4net

log4net introduction
http://logging.apache.org/log4net/release/manual/introduction.html

log4net configuration examples
http://logging.apache.org/log4net/release/config-examples.html

LINQ
The LINQ Project is a codename for a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities.

My bachelor's degree graduation project 

As a result of my graduation project in the computer engineering course I ended up with a concise document describing the idea behind LINQ. The document is available only in Portuguese so that I think it's a valuable source of information to people that know Portuguese given the fact that great material about LINQ is only available in English.

In addition to the intrinsic subjects related to the integration of the query language (SQL) into the programming language (C#), in this paper you'll also find information about the great language extensions that form the base of LINQ:

• Generics
• Anonymous methods
• Iterators
• Partial types
• Nullable types
• Query expressions
• Automatically implemented properties
• Implicitly typed local variables
• Extension methods
• Partial methods
• Lambda expressions
• Object initializers
• Collection initializers
• Anonymous types
• Implicitly typed arrays
• Expression trees

See the paper's abstract below (English/Português):

ABSTRACT

Macaferi, Leniel Braz de Oliveira. Query language integrated into the programming language. 2007. 96f. Monograph (bachelor’s degree in Computer Engineering) - Barra Mansa University Center, Barra Mansa, 2007. www.ubm.br

Data is the raw material of computation and is processed via software. Software products are generally structured in tiers, typically three, the data tier, the middle or business tier and the presentation or client tier. Each of these tiers has its own data model. These different paradigms cause the impedance mismatch problem between these three disparate models.

Instead of trying to unify at the data model level, a better approach is to unify at the level of algebraic operations that can be defined the same way over each data model. This allows us to define a single query language that can be used to query and transform any data model. All the data model need to do is to implement a small set of standard query operators, and each data model can do so in a way natural to itself.

The industry has reached a stable point in the evolution of object-oriented (OO) programming technologies. Programmers now take for granted the facilities of oriented programming languages and their features like classes, objects, methods and events. Such languages support the creation and use of higher order, functional style class libraries. The support is the result of new language extensions being developed. These extensions enable the construction of compositional application program interfaces (APIs) that have equal expressive power of query languages inside the programming language syntax. This makes it possible to implement the standard query operators. The standard query operators can be then applied to all sources of data, not just relational or XML domains.

This work aims to present and use the most important aspects of the language integrated query with special focus on the integration of the SQL query language into the C# programming language. Aspects as simplification of the way of writing queries, unification of the syntax for querying any data source, reinforcement of the connection between relational data and the object oriented world and less time spent in the software development process.

Keywords: query language, programming language, data models, SQL, C#, LINQ

RESUMO

Macaferi, Leniel Braz de Oliveira. Linguagem de pesquisa integrada à linguagem de programação. 2007. 96f. Monografia (bacharelado em Engenharia de Computação) - Centro Universitário de Barra Mansa, Barra Mansa, 2007. www.ubm.br

Dados formam a matéria prima da computação e são processados via software. Produtos de software são geralmente estruturados em camadas, tipicamente três: a camada de dados, a camada intermediária ou de lógica e a camada de apresentação ou do cliente. Cada uma destas camadas possui seu próprio modelo de dados. Estes diferentes paradigmas causam o problema da combinação mal sucedida entre estes três modelos completamente diferentes.

Ao invés de tentar unificar no nível do modelo de dados, uma melhor alternativa é unificar no nível das operações algébricas que podem ser definidas do mesmo modo sobre cada modelo de dados. Isto nos permite definir uma única linguagem de pesquisa que pode ser usada para pesquisar e transformar qualquer modelo de dados. Tudo o que os modelos de dados precisam implementar é um pequeno conjunto de operadores de pesquisa padrão, e cada modelo de dados pode fazer isto de uma maneira natural.

A indústria chegou a um ponto estável na evolução das tecnologias de programação orientada a objetos (OO). Desenvolvedores agora têm por certo as facilidades das linguagens de programação OO e seus ricos recursos iguais a classes, objetos, métodos e eventos. Tais linguagens suportam a criação e uso de bibliotecas de classe de estilo funcional de ordem maior. O suporte é o resultado das novas extensões de linguagem de programação que estão sendo desenvolvidas. Estas extensões permitem a criação de interfaces para programação de aplicativos (APIs) composicionais que possuem poderosas capacidades de pesquisa dentro da sintaxe da linguagem de programação. Isto torna viável a implementação dos operadores de pesquisa padrão. Os operadores de pesquisa padrão podem ser aplicados em todas as fontes de informação, não somente em domínios de bancos de dados relacionais ou XML.

Este trabalho visa apresentar e utilizar os aspectos mais importantes da linguagem integrada de pesquisa com foco na integração da linguagem de pesquisa SQL à linguagem de programação C#. Aspectos como a simplificação da maneira de escrever pesquisas, unificação da sintaxe para pesquisar qualquer fonte de dados, reforço da conexão entre dados relacionais e o mundo orientado a objetos e o menor tempo gasto no processo de desenvolvimento de software.

Palavras-chave: linguagem de pesquisa, linguagem de programação, modelos de dados, SQL, C#, LINQ

SUMÁRIO
1 INTRODUÇÃO 15
  1.1 Delimitação do tema 16
  1.2 Problema 16
  1.3 Enunciado das hipóteses 17
  1.4 Objetivos específicos e geral 18
  1.5 Justificativa do trabalho 18
2 FUNDAMENTAÇÃO TEÓRICA 19
  2.1 Linguagem de pesquisa 19
      2.1.1 Pesquisa 19
  2.2 Linguagem de programação 19
  2.3 Combinação mal sucedida entre as linguagens de pesquisa e de programação 20
  2.4 Programação orientada a objetos 24
      2.4.1 Classe e objeto 25
      2.4.2 Variável e tipo 25
      2.4.3 Membro 25
      2.4.4 Acessibilidade 25
      2.4.5 Método 26
      2.4.6 Parâmetro 26
      2.4.7 Troca de mensagem 26
      2.4.8 Herança 26
      2.4.9 Encapsulamento 26
      2.4.10 Abstração 27
      2.4.11 Polimorfismo 27
      2.4.12 Interface 27
      2.4.13 Delegate 27
  2.5 Banco de dados relacional 28
      2.5.1 Relação ou tabela 28
      2.5.2 Restrição 28
      2.5.3 Domínio de dado 28
      2.5.4 Chave primária 29
      2.5.5 Chave estrangeira 29
      2.5.6 Stored procedure 29
      2.5.7 View 29
      2.5.8 User defined function 30
  2.6 .NET Framework 30
      2.6.1 Principais recursos 32
      2.6.2 Arquitetura 33
      2.6.3 Infra-estrutura de linguagem comum 33
      2.6.4 Assemblies 34
      2.6.5 Metadados 35
      2.6.6 Biblioteca de classes base 35
  2.7 SQL 35
  2.8 C# 35
3 METODOLOGIA 37
  3.1 Extensões de linguagem 37
      3.1.1 Genéricos 37
      3.1.2 Métodos anônimos 38
      3.1.3 Iteradores 38
      3.1.4 Tipos parciais 40
      3.1.5 Tipos anuláveis 41
      3.1.6 Expressões de pesquisa 43
      3.1.7 Propriedades automaticamente implementadas 44
      3.1.8 Variáveis locais implicitamente tipificadas 45
      3.1.9 Métodos de extensão 46
      3.1.10 Métodos parciais 47
      3.1.11 Expressões lambda 49
      3.1.12 Inicializadores de objeto 50
      3.1.13 Inicializadores de coleção 50
      3.1.14 Tipos anônimos 51
      3.1.15 Arrays implicitamente tipificados 52
      3.1.16 Árvores de expressão 53
4 DESENVOLVIMENTO 54
  4.1 Linguagem de pesquisa integrada à linguagem de programação 54
      4.1.1 Operadores de pesquisa padrão 56
      4.1.2 Fonte de dados 61
      4.1.3 Operação de pesquisa 62
      4.1.4 Modelo de objetos 63
  4.2 Estudo de caso 64
      4.2.1 Classes do modelo de objetos 68
      4.2.2 DataContext 68
      4.2.3 Relacionamentos 69
      4.2.4 Pesquisa de dados 70
      4.2.5 Operações de insert, update e delete 71
5 CONCLUSÃO 73
  5.1 Avanços 73
  5.2 Limitações 74
  5.3 Trabalhos relacionados 74
  5.4 Trabalhos futuros 76
6 BIBLIOGRAFIA 78
ANEXOS 81

You can get a PDF copy of the full paper at:

https://drive.google.com/file/d/1nDbZXqKsE_jzxz4qB1ZOlgKu3LulSIKi/view?usp=sharing (Portuguese - Brazil)