Friday, January 22, 2010

LINQ To SQL Cheat Sheet

Damien has created a cool cheat sheet for LINQ to SQL. I highly recommend this. This covers both C# and VB. They are separate pages, so you can print just for your desired language. You can download from the following link:

Thursday, January 21, 2010

Bulk / Batch database operations like INSERT, UPDATE and DELETE in LINQ To SQL

I have recently written an article about bulk insert / update / delete operations using LINQ To SQL. It involves sending data in IQueryable objects as XML to the database stored procedures and running the set based SQL operations.


Wednesday, January 20, 2010

LINQ To SQL and VARCHAR(1) Fields

You don't know it until you experience it!

Mapping between data types between database systems and programming languages has always been an issue. Though LINQ To SQL maps the datatypes of every field to it's corresponding datatypes.

One of these mappings, is the mapping between VARCHAR fields in the database and .net datatypes. When we create LINQ To SQL entity in our dbml files for a table in the database, all VARCHAR fields are correspondingly created as String fields in the LINQ To SQL entity. This is with exception to VARCHAR fields with width equal to one (1). VARCHAR(1) fields are mapped to character datatype.

This seems to make sense. This is because that is exactly what might be intended when this field was defined as VARCHAR(1). So what is the issue??

The issue comes at runtime for all these VARCHAR(1) fields which are NULLABLE too. Whenever there is NULL in such fields. The system is not able to load corresponding value in LINQ To SQL entity. This is because there is nothing like empty character. You would never know about this issue until you run your system and you try to load data with NULL values in this field. This results in the exception with following message:

'String must be exactly one character long'

Now we know about the problem. How can we avoid this problem to occur at runtime. The solution is very simple. After generation of Entity by LINQ To SQL designer, change all these character datatypes to String. This would cause empty string to be loaded when NULL data is loaded in such a field.

Hope this would help you!!!

Monday, January 18, 2010

Converting Input Text to Title Case in .net

We are so used to TitleCases in word processor applications. When it comes to string manipulation in .net, the only options for case conversion options available seems to be as follows:

1. Change Text to Lower case
2. Change Text to Upper Case

We need sometime to convert our text to Title Case in our .net applications. There are two options available out of the box.

1. Using TextInfo class available in CultureInfo in System.Globalization namespace.
2. Using StrConv function avaliable in Microsoft.VisualBasic.

Here StrConv is a port of VB6 to .net. So most of us developers don't prefer to use it. If you are planning to use it in C# code, you would have to reference Microsoft.VisualBasic.

Dim text As String = "tEXT fOR cASE cONVERSION"
Dim convertedText As String = StrConv(text, VbStrConv.ProperCase)

As discussed, TextInfo is generally the most preferred option. To use TextInfo, we have to get a CultureInfo object. We might get it from current thread for the culture of the environment of thread being executed. We might also get TextInfo object by creating in instance of CultureInfo explicitly. The example code using StrConv() is as follows:

Dim text As String = "tEXT fOR cASE cONVERSION"
Dim textCaseConverter As TextInfo =
Dim textAfterCaseConversion As String = textCaseConverter.ToTitleCase(text)

We might also get an instance of TextInfo explicitly as follows:

Dim culture As New System.Globalization.CultureInfo("en-US", False)

Remember that there is one limitation when are using TextInfo to convert to Title Cases. It returns nothing when the input text is all upper case. This can be worked around by changing the text to any of upper / lower case before inputting it to the toTitleCase() method.

In addition to converting input text to Title case, TextInfo also supports converting text to upper and lower cases.

LINQ To SQL Command Time out

What should we do when our commands are timing out if we have already optimized our queries / procedures. We know that these commands should take that long. we just want our code to be supporting this delay and not throw any kind of exception.

This is a general scenario we face when we are dealing with data access. In ADO.Net, we access database by creating connections and command objects. If we know that our commands will take long, we increase time out for our commands.

But how should we do that in LINQ to SQL? We don't create any connections are command objects explicitly. Rather we have datacontexts and collections. We generally face timeout issues when we call stored procedures using LINQ To SQL DataContexts. The easiest answer is to change the time out for all data access.

Before presenting the solution let me make this know that LINQ to SQL classes are implemented using Partial classes. You can add definitions to these partial classes in your code. Now, we can define various event handlers in these partial classes other than various other implementation details. One of the event is OnCreated. Here is the solution of this problem by defining OnCreated event handler in partial class of DataContext. The code is as follows:

Private Sub OnCreated()
Me.CommandTimeout = Integer.MaxValue
End Sub

Thursday, January 7, 2010

GetField() and SetField() for LINQ to SQL

LINQ To DataSet has GetField() and SetField() methods. We can pass them the name of field to get / set the value of the field. If we look at LINQ to SQL, we are not able to find out any equivalent methods like that.

To work around and get this functionality, there is a workaround. This workaround involves our time tested reflection technique. The vb code for this is as follows:

Dim myConnectionString as string = "" 'assign connection string here
Dim columnName as string = "" 'assign column name here

Using db as new myDataContext(myConnectionString)
Dim myValues = (From customer in Customers Select customer.GetType().GetProperty(columnName).GetValue(customer, nothing))
End Using

Here we have used GetProperty() method to get the PropertyInfo object. PropertyInfo is defined in System.Reflection namespace.

Though we are getting this great flexibility but this flexibility is coming with a cost. If you use SQL debug visualizer to look at the SQL resulting with the above LINQ query, you will realize that the resulting SQL select all column from the database instead of just that column specified in GetProperty() method. This is an additional cost other than the runtime cost associated with reflection.