Using Linq for SQL with FOR XML procedures

The problem is

I have a stored procedure in MS SQL that ends with SELECT ... FOR XML, and formerly I used XmlReader to access the data from it (by calling SqlCommand.ExecuteXmlReader())

Now I'm trying to figure out how to access to this using LINQ
With VS constructor I drag and drop this procedure into Linq To SQL data class designer.

It creates something like that

[Function(Name="dbo.my_Procedure")]
public ISingleResult my_Procedure([Parameter(Name="param1", DbType="VarChar(50)")] string param1, ... all other params)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo) (MethodInfo.GetCurrentMethod())), param1, ... all other params);
return ((ISingleResult)(result.ReturnValue));
}


Strange thing that actually procedure returns not a sinlgle line but a collection of arbitrary broken XML source (I'm just wondering what parameter controls the size of each piece)

So to put all XML together you need to create a stream (memory stream) or file and an XmlTextWriter object, opened over the stream and a code like that to select all parts of the XML


System.Collections.Generic.IEnumerable rawXMLLines =
(new my_ProcedureDataContext()).my_Procedure(param1, ....)
.Select(my_Procedure => my_Procedure.XML_F52E2B61_18A1_11d1_B105_00805F49916B);

foreach (string rawXML in rawXMLLines)
{
writer.WriteRaw( rawXML );
}

Well, not so much development work but...

Seems, that we lost here the main advantage of the SELECT ... FOR XML clause - an ability to write Xml to the output stream directly from XmlReader (that we get from ExecuteXmlReader()) , which is the most effective way if you are working with large XML files.

In the example, shown above, the results of the procedure is extracted by LINQ engine to the collection of strings before we start writing these strings one by one into output stream with XmlWriter.

Strange Oracle behaviour

In our project we have one very complicated query that involves 10 or more tables joined with left joins or inner joins

IN EACH TABLE there was a field DELETED.

Developer who writes a SQL query made a mistake forgetting to add an alias to the DELETED field in the following WHERE clause

select ...
from table1 t1 join table2 t2 on t1.table1_id = t2.table1_id etc...
WHERE DELETED = 0

and what was strange - there were no compilation errors, oracle executes that query but guess from what table it takes DELETED field? I don't know! But not from the first table :) This behaviour leads to the bug that was very hard to find, debug and correct.

Finally when developer corrected where condition, it starts work as expected

WHERE t1.DELETED = 0

It is not the first case that makes me furrious about Oracle!

Architecture of .NET projects with LINQ

LINQ promises a lot of advantages and simplify most things that usually should be implemented in Data acess layer

However, currently I can't find any suitable design patterns that can show how one can build a huge project (say 100+) tables avoiding copy-paste code.

Recently, I found a post Building Multi-Tier Web Application in .NET 3.5 Framework Using LINQ to SQL but there is no answer to how to avoid a lot of copy-paste code.

So, I think I should play more with that code, generic classes and new C# 3.0 language features...

Using Transactions with Strongly Typed datasets

Strongly typed datasets , generated by Visual Studio 2005 have a lot of disadvantages and developer of a real-world application usually have to write a lot of additional code in order to use them.

One of the issues - you cannot use SqlTransaction object with auto-generated datasets unless you write some additional code in partial classes of data adapters. Surprisingly, most .NET developers even don't care about that. But if you develop dataset composed of several tables that together mean one business entity, one may expect that if any exception during update of this entity happens, all changes should be rolled back, otherwise you may get inconsistent data.

In my example I created a Windows Console Application in VS 2005 C#. Then using database explorer connected to AdventureWorks database (that comes with MS SQL Server 2005 examples) created a dataset with the tables Customer (mapped to Sales.Customer), Address (mapped to Person.Customer) and CustomerAddress (mapped to Sales.CustomerAddress)

Supposing that I will have a form on UI where I want to edit customer's data and customer's addresses as well. It is not so unusual scenario, and I naturally will want to save all this in one transaction.

In order to do this I have to created a partial classes for all table adapters to make transaction property available to set from an external class, i.e.:

namespace TestDatasetTransaction.CustomerDataSetTableAdapters
{
partial class CustomerTableAdapter
{
public void SetTransaction(SqlTransaction tran)
{
this._adapter.UpdateCommand.Transaction = tran;
this._adapter.InsertCommand.Transaction = tran;
this._adapter.DeleteCommand.Transaction = tran;
}
}

partial class AddressTableAdapter
{
public void SetTransaction(SqlTransaction tran)
{
this._adapter.UpdateCommand.Transaction = tran;
this._adapter.InsertCommand.Transaction = tran;
this._adapter.DeleteCommand.Transaction = tran;
}
}

partial class CustomerAddressTableAdapter
{
public void SetTransaction(SqlTransaction tran)
{
this._adapter.UpdateCommand.Transaction = tran;
this._adapter.InsertCommand.Transaction = tran;
this._adapter.DeleteCommand.Transaction = tran;
}
}
}

in Visual Studio designer I created a

then I wrote the following code in the console's main class to prove that transactions really works.


using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace TestDatasetTransaction
{
class Program
{
static void Main(string[] args)
{
int customerID = 1;

CustomerDataSet customer = new CustomerDataSet();

//fill all records in CustomerDataSet with data for one customer
CustomerDataSetTableAdapters.CustomerTableAdapter customerTableAdapter = new CustomerDataSetTableAdapters.CustomerTableAdapter();
customerTableAdapter.FillByCustomerID(customer.Customer, customerID);

CustomerDataSetTableAdapters.AddressTableAdapter addressTableAdapter = new CustomerDataSetTableAdapters.AddressTableAdapter();
addressTableAdapter.FillByCustomerID(customer.Address, customerID);

CustomerDataSetTableAdapters.CustomerAddressTableAdapter customerAddressTableAdapter = new CustomerDataSetTableAdapters.CustomerAddressTableAdapter();
customerAddressTableAdapter.FillByCustomerID(customer.CustomerAddress, customerID);

Console.WriteLine("Current Customer's ModifiedDate is : {0:g}", customer.Customer[0].ModifiedDate);

//make some changes
//this change is valid
customer.Customer[0].ModifiedDate = DateTime.Now;
Console.WriteLine("Will change Customer's ModifiedDate to : {0:g}", customer.Customer[0].ModifiedDate);

//this change is not valid, sql server will not accept such date
customer.Address[0].ModifiedDate = DateTime.MinValue;
Console.WriteLine("Will change Address ModifiedDate to : {0:g}", customer.Address[0].ModifiedDate);



using (SqlConnection conn = new SqlConnection(
TestDatasetTransaction.Properties.Settings.Default.AdventureWorksConnectionString))
{
conn.Open();
SqlTransaction tran = null;
try
{
tran = conn.BeginTransaction();

customerTableAdapter.Connection = conn;
addressTableAdapter.Connection = conn;
customerAddressTableAdapter.Connection = conn;

customerTableAdapter.SetTransaction(tran);
addressTableAdapter.SetTransaction(tran);
customerAddressTableAdapter.SetTransaction(tran);

customerTableAdapter.Update(customer);
addressTableAdapter.Update(customer);
customerAddressTableAdapter.Update(customer);

tran.Commit();

}
catch (Exception ex)
{
if (tran != null) tran.Rollback();
Console.WriteLine(ex.Message);
}
tran.Dispose();

customerTableAdapter.FillByCustomerID(customer.Customer, customerID);


conn.Close();
}

Console.WriteLine("Finally Customer's ModifiedDate is : {0:g}", customer.Customer[0].ModifiedDate);
Console.WriteLine();
Console.WriteLine("Press a key");
Console.ReadKey();

}
}
}

-------------

The console output was respectivly


Current Customer's ModifiedDate is : 19.03.2008 17:16
Will change Customer's ModifiedDate to : 19.03.2008 17:33
Will change Address ModifiedDate to : 01.01.0001 0:00
Over SqlDateTime. Should be from 1/1/1753 12:00:00 AM to
12/31/9999 11:59:59 PM.
Finally Customer's ModifiedDate is : 19.03.2008 17:16
Press a key


------------

So, the transaction really works, and because of an exception happened during update of the 2nd table, all changes made to the first table was rolled back.

Of course the more simple solution is to use the TransactionScope() object from System.Transaction.dll, however this will not work on all platforms and databases. Also it seems, that an internal mechanism that exist in System.Transaction that is responsible for transparent (from developer's point of view) transaction enlisting from different resource managers should have affect on performance. If I will have some time, I will try to make a small performance test comparing TransactionScope() and SqlTransaction later...

Applying Gamma correction to an Image

Recently, one of my tasks was to apply non-linear gamma correction to JPEG image. Using .NET Framework 2.0 System.Drawing.Imaging library one can reach this target quickly, however there is one big notice about working with JPEGs: Never do any transformations with JPEG images, first confert them to 32 bit Argb! If you pbey this, you may catch System.OutOfMemoryException. Also I was found in one post (can't find that link... :( ) that this format is also most efficient in terms of performance...

public static Bitmap CorrectGamma(Image source, decimal gamma)
{
Bitmap intermediate = new Bitmap(source.Width, source.Height, PixelFormat.Format32bppPArgb);

// Create an ImageAttributes object and set the gamma
ImageAttributes imageAttr = new ImageAttributes();
imageAttr.SetGamma(Convert.ToSingle(gamma));

Rectangle rect = new Rectangle(0, 0, source.Width, source.Height);
using (Graphics g = Graphics.FromImage(intermediate))
{
g.DrawImage(source, rect, 0, 0, source.Width, source.Height, GraphicsUnit.Pixel);
}

Bitmap corrected = new Bitmap(source.Width, source.Height, PixelFormat.Format32bppPArgb);
using (Graphics g = Graphics.FromImage(corrected))
{
g.DrawImage(intermediate, rect, 0, 0, intermediate.Width, intermediate.Height, GraphicsUnit.Pixel, imageAttr);
}

intermediate.Dispose();
return corrected;

}

It is essential that image is transformed once from JPEG to Format32bppPArgb (I described this in another post) and only after that it is transfomed second time, applying ImageAttributes. Doing both at one time will cause OutOfMemoryException, but of course you can try this out for yourself :)

Converting JPEG to BMP and other issues with Graphics

I was wondering why it is so difficult to find an easy solution using .NET Fremework 2.0 to convert JPEG image into BMP format (why there is a tone of examples to do BMP to JPEG convertion)

So here is my C# code, may be someone will find it helpfull


public static Image ConvertToBMP(Image source)
{
Bitmap result = new Bitmap(source.Width, source.Height, PixelFormat.Format32bppPArgb);

Rectangle rect = new Rectangle(0, 0, source.Width, source.Height);
using (Graphics g = Graphics.FromImage(result))
{
g.DrawImage(source, rect, 0, 0, source.Width, source.Height, GraphicsUnit.Pixel);
}

return result;

}

I didn't tested yet this for all image types, however for JPEG it works :)