eXtreme Programming

How to Convert rows to CSV in T-SQL

2011-12-13T16:52:00.000Z

To convert several rows to unique row in CSV using T-SQL, use the following code:

DECLARE @myList varchar(MAX)
SELECT @myList = coalesce(@myList + ',', '') + UserName 
FROM [User]

SELECT @myList

WCF: The maximum string content length quota (8192) has been exceeded

2011-12-13T16:39:00.003Z

A very annoying error I received when passing a very large string to a WCF Service was:

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter . The InnerException message was 'There was an error deserializing the object of type System.String. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 8, position 9944.'. Please see InnerException for more details.

This is a cryptic error and only after some investigation I found the solution:

The readerQuotas element must be configured on the binding configuration:

<binding name="basicHttpBinding_IMyService" maxReceivedMessageSize="63400320">
      <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647"
       maxBytesPerRead="2147483647"   maxNameTableCharCount="2147483647"/>
</binding><br />

This is a simple example, you must configure it for your own needs.
I have added the additional configuration elements to show the other options.

How to bulk insert efficiently

2011-12-13T15:20:00.000Z

Using an ORM like Entity Framework is not efficient to perform bulk operations.
To bulk insert efficiently, the SqlBulkCopy class must be used.

To insert a generic list it must be converted to a DataTable:

        public static DataTable ConvertToDataTable<T>(IList<T> list)
        {
            PropertyDescriptorCollection propertyDescriptorCollection = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < propertyDescriptorCollection.Count; i++)
            {
                PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
                Type propType = propertyDescriptor.PropertyType;
                if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    table.Columns.Add(propertyDescriptor.Name, Nullable.GetUnderlyingType(propType));
                }
                else
                {
                    table.Columns.Add(propertyDescriptor.Name, propType);
                }
            }
            object[] values = new object[propertyDescriptorCollection.Count];
            foreach (T listItem in list)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = propertyDescriptorCollection[i].GetValue(listItem);
                }
                table.Rows.Add(values);
            }
            return table;
        }

Then the SqlBulkCopy can be used. In the example the user table is bulk inserted:

DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
    {
        sqlBulkCopy.ColumnMappings.Add("UserID", "UserID");
        sqlBulkCopy.ColumnMappings.Add("UserName", "UserName");
        sqlBulkCopy.ColumnMappings.Add("Password", "Password");
        sqlBulkCopy.DestinationTableName = "User";
        sqlBulkCopy.WriteToServer(dt);
    }
}

Recover the mirror database

2011-12-05T16:34:00.001Z

Database mirroring is a software solution for increasing database availability.
Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
When the mirror is removed from the Primary database the mirrored one sometimes doesn't recover and it says "Recovering".
If the database doesn't leave this state, the following TSQL command can help:

RESTORE DATABASE myDatabase WITH RECOVERY

This will allow to recover the former mirror database.

ValidateRequest=“false” doesn't work in Asp.Net 4

2011-11-14T15:16:00.001Z

Before Asp.Net 4.0 is some scenarios it was useful to disable the automatic request validation performed by the .Net Framework to prevent Script Attacks.

This could be done by going to web.config or the page and setting

ValidateRequest="false"

In Asp.Net 4.0 this feature only works if we set requestValidationMode="2.0" :

Transaction management

2011-11-10T12:02:00.001Z

In the context of a transaction each work-unit performed in a database must either complete in its entirety or rollback entirely.
In SQL Server an "normal" exception does not rollback a transaction by default.

In the old days the T-SQL to manage a transaction was tedious.
An error variable must be checked to rollback the transaction if needed.
Example:

BEGIN TRANSACTION 

...
IF (@@ERROR <> 0)
BEGIN
   ROLLBACK TRANSACTION 
    RAISERROR ('Error Description.', 16, 1)
END

COMMIT TRANSACTION

But the newer versions of SQL Server support TRY CATCH block, that simplifies the process:

BEGIN TRY
BEGIN TRANSACTION

...

IF @@TRANCOUNT > 0
BEGIN
    COMMIT TRANSACTION
END

END TRY
BEGIN CATCH

 DECLARE @ErrorMessage VARCHAR(4000)
  
 SET @ErrorMessage = 'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '') + ' Line: ' + 
CAST(ERROR_LINE() AS VARCHAR(10)) + ' Message: ' + ERROR_MESSAGE()
  
 IF @@TRANCOUNT > 0
 BEGIN
  ROLLBACK TRANSACTION;
 END
  
 RAISERROR (@ErrorMessage, 16, 1)  

END CATCH;

Another way of doing the same is using SET XACT_ABORT. I don't recommend using it, but in some temporary scripts it can be useful.

The SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a statement raises a run-time error.

SET XACT_ABORT ON
BEGIN TRANSACTION

....

COMMIT TRANSACTION

Note also that the RAISERROR stament does not honor the XACT_ABORT setting, so if an error occurs there is no automatic rollback even if XACT_ABORT is ON.

How to stop windows time synchronization

2011-10-28T11:37:00.001+01:00

In a testing scenario where there are calculations depending on the current time, it may be useful to set the windows time to a fixed date.

The problem is that the NTP time source in Windows Server 2008 will sync the time with the internet or the active directory. To stop this synchronization, the service windows time can be stopped or even un-installed.

This is where de command w32tm comes in:

w32tm /unregister

Also to stop the windows time service:

net stop "windows time"

Now the date can be fixed.

The problem is that the windows authentication is going to fail when trying to connect those servers. The problem isn’t a issue if the testing scenario is to set the sql server database server date and you can use sql server authentication.

Good testing.

WCF And Debugging Exceptions

2011-09-01T12:36:00.000+01:00

A common problem in WCF is to receive the exceptions on client when debugging.
The WCF behaviors configuration allows this using the includeExceptionDetailInFaults, but to receive the inner expection the returnUnknownExceptionsAsFaults must also be set to true.
The maxItemsInObjectGraph is only to avoid the error of exceeding the maximum number of items that can be serialized or deserialized.

<behaviors>
    <serviceBehaviors>
        <behavior name="MyServiceBehavior" returnUnknownExceptionsAsFaults="True">          
            <serviceDebug includeExceptionDetailInFaults="true" />
            <dataContractSerializer maxItemsInObjectGraph="2147483647" />
        </behavior>
    </serviceBehaviors>
</behaviors>

Run Visual Studio in the context of an administrator account

2011-04-20T16:06:00.001+01:00

If visual studio displays the error "... run Visual Studio in the context of an administrator account" a simple fix can be made:
1) Goto C:\Program Files (x86)\Common Files\Microsoft shared\MSEnv
2) Find the file VSLauncher.exe
3) Right click on it, select Properties, and then the Compatibility tab
4) Check the box for Run this program as an administrator

From now on when a solution file is opened the visual studio runs in the context of an administrator.

The same procedure must also be made for any shortcut to visual studio that you may have.

How to set a date to the first / last day in TSQL

2011-04-12T16:58:00.000+01:00

To set a date to the first / last day in TSQL:

First day:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)

Last Day :

SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

How to convert a table column in comma separated values (CSV)

2011-04-11T15:34:00.000+01:00

To convert a table column in comma separated values (CSV), I use the following snippet:

DECLARE @table TABLE
(
    Id INT
)

INSERT INTO @table
VALUES (1),(5),(88), (99)

SELECT SUBSTRING(
(
SELECT ',' + CAST(Id AS VARCHAR(200))
FROM @table 
FOR XML PATH('')
)
,2,2000) AS VAL

Web developer tools for the browser

2011-03-24T18:24:00.000Z

The Web Developer extension adds various web developer tools to a browser. The extension is available for Firefox and Chrome, and will run on any platform that these browsers support including Windows, Mac OS X and Linux.

For more information press here.

IIS7 Sessions Getting Crossed / Mixed Up / Copied

2011-03-01T13:48:00.002Z

I have detected a problem on a web farm:
IIS 7 Sessions of the useres were getting Crossed / Mixed Up / Copied. A user was getting the session of another!!!!

After some research I found that IIS 7 now has the ability to cache dynamic content as well.
It is a new feature in IIS7, this version of IIS introduced some new caching features.
(i) IIS7 automatically caches static content, such as HTML pages, images, and style sheets.
(ii) IIS7 now has the ability to cache dynamic content as well.

In IIS7, disable the caching for .aspx pages in any directory with an asp.net page that depends on the session state. The steps to do this are:
1. Run the Server Management console and navigate to Roles -> Web Server (IIS) -> Internet Information Services.
2. Select the site you wish to modify.
3. Select the folder that contains the .aspx pages you need to turn caching off for.
4. In the Feature View, double-click “Output Caching”.
5. If there is a rule there already for the .aspx extension double click it. Otherwise right click and select “Add…”
6. Enter .aspx for the “File name extension”
7. Check "User-mode caching", "Prevent all caching", check "Kernel-mode caching" and "Prevent all caching"

For more detailed information press here.

HTML : white space nowrap

2010-12-29T18:15:00.000Z

In the gold days to avoid a white space wrap we could use the nobr tag. Now since the tag is deprecated we must use css:

.nobr { white-space:nowrap; }

This class can be applied to a span with the text we need to avoid the wrap.

Immediate Window is missing in Visual Studio 2010 Menu

2010-12-16T11:11:00.000Z

When the Immediate Window is missing in Visual Studio Menu, just do the following steps:
1) In the menu, select View -> Other Windows -> Command Window
2) Type immed in the command window and the It will bring the Immediate Window
3) Type cmd inside the Immediate Window and it will bring the Command Window back again

Fix the MaxItemsInObjectGraph quota error

2010-12-15T11:28:00.000Z

When there is a great amount of data to send to a WCF service, the following error occurs:

Maximum number of items that can be serialized or deserialized in an object graph is '65536'. Change the object graph or increase the MaxItemsInObjectGraph quota.

To fix the error the MaxItemsInObjectGraph must be defined on the wcf service server and the client.

On the server:

<system.serviceModel>   
    <services>
        <service behaviorConfiguration="Service1Behavior" name="Service1">
           <endpoint address="" binding="basicHttpBinding" contract="IService1"></endpoint>
        </service>
    </services>
        <behaviors>
            <serviceBehaviors>
                <behavior name="Service1Behavior">          
                    <serviceMetadata httpGetEnabled="true"/>
                    <serviceDebug includeExceptionDetailInFaults="true"/>
                    <dataContractSerializer maxItemsInObjectGraph="2147483647"/>
                </behavior>
            </serviceBehaviors>
        </behaviors>
    </system.serviceModel>
</configuration>

On the client:

<system.serviceModel>
    <client>
        <endpoint address="http://localhost/Service1.svc" behaviorConfiguration="Service1Behavior" binding="basicHttpBinding" contract="IService1Event" name="Service1">
      </endpoint>
    </client>
    <behaviors>
        <endpointBehaviors>
            <behavior name="Service1Behavior">
              <dataContractSerializer maxItemsInObjectGraph="2147483647"/>    
            </behavior>
        </endpointBehaviors>
</behaviors>
</system.serviceModel>

Optimize tempdb Performance

2010-12-14T12:03:00.000Z

To optimize tempdb Performance in a production environment:
1) Set the recovery model of tempdb to SIMPLE.
2) Allow for tempdb files to automatically grow as required.
3) Set the file growth increment to a reasonable size. Set it to 10% of the file size, with a minimnum of 10MB
4) create one data file for each CPU on the server, note that a dual-core CPU is considered to be two CPUs.
5) Make each data file the same size; this allows for optimal proportional-fill performance.
6) Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
7) Put the tempdb database on dedicated disks.

For more information: MSDN Optimizing tempdb Performance

LINQ To Objects: Left Join

2010-11-15T10:31:00.000Z

Linq to Objects is a powerfull technology. One of the many features it has it the power to perform a left join between objects. The syntax for a left join is not like in SQL.

A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. Calling DefaultIfEmpty on the results of a group join returns the result of a left join.

Example:

   1:  class User

   2:  {

   3:       public int UserID { get; set; }

   4:       public string Name{ get; set; }

   5:  }

6:

   7:  class Order

   8:  {

   9:       public int OrderID { get; set; }

  10:       public int UserID { get; set; }

  11:       public int ProductID { get; set; }

  12:  }

13:

  14:  List<User> users = UserService.List();

  15:  List<Order> orders = OrderService.ListByUser(userID);

16:

  17:  var q = from o in orders

  18:          join u in users on o.UserID = u.UserID into joinedUsers

  19:          from UserOrders in joinedUsers.DefaultIfEmpty()

  20:          select new UserOrder()

  21:          {

  22:              OrderID = o.OrderID,

  23:              ProductID = o.ProductID,

  24:              Name = UserOrders.Name

  25:          }

  26:  var result = q.ToList();

How to use ASP.Net validators with a User Control?

2010-11-11T16:27:00.001Z

ASP.Net validators and user controls (ascx) are great. They save us a lot of time.
But when we mix a User Control and a ASP.Net validator the following error occurs:

Control 'UserSelector' referenced by the ControlToValidate property of 'rvUserSelector' cannot be validated.

The solution to this problem is to use the attribute ValidationProperty on the User Control:

[ValidationProperty("UserID")]
public partial class UserSelector : UserControl

In this example the UserID property of the User Control is used by the validators to perform the validation.

To end, I would like to thank you Alexandre Simões for his help in solving this issue.

KBxxxxxxx does not apply, or is blocked by another condition on your computer

2010-10-21T10:47:00.000+01:00

When installing a visual studio 2010 hotfix I got the error:

KBxxxxxxx does not apply, or is blocked by another condition on your computer

To fix it i had to goto Programs and Features in the control panel, selected Microsoft Visual Studio 2010 and selected Unistall/Change. Then I made a repair.
When the repair finished I executed the hotfix and it installed.

Missing Edit WCF Configuration menu option in Visual Studio

2010-10-20T18:15:00.000+01:00

Normally the context menu option Edit WCF Configuration for a app.config or web.config is not shown.
To fix this issue open up the Tools menu in Visual Studio and choose Wcf Service Configuration Editor.
The tool will open, close it straight away and then right-click in your config file.
The Edit WCF Configuration context menu is now visible.

Hotfixes available for ‘scrolling context menu’ problem

2010-10-18T09:47:00.000+01:00

Finally, the hotfix for ‘scrolling context menu’ problem in visual 2010. The context menus had scrollbars even when there is sufficient screen real estate to show the menu without one.

For more information and installation:
http://blogs.msdn.com/b/visualstudio/archive/2010/10/14/hotfixes-available-for-scrolling-context-menu-problem.aspx

Get only the Date Part of a DateTime

2010-10-12T17:05:00.000+01:00

To get only the Date Part of a DateTime in an efficient way, use the following code:

SELECT CAST(FLOOR( CAST( GETDATE() AS FLOAT )) AS DATETIME)

How to Convert a String to Title Case

2010-10-12T14:37:00.001+01:00

To convert a string Title Case, simply use the TextInfo class:

using System.Globalization;
using System.Threading;

CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
TextInfo textInfo = cultureInfo.TextInfo;

string result = textInfo.ToTitleCase("this is test");

If the string is in uppercase first convert it to lowercase using the ToLower() method.

Fix error :Unable to generate a temporary class (result=1). error CS2001: Source file ‘C:\WINDOWS\TEMP\file.cs’ could not be found error CS2008: No inputs specified

2010-09-03T19:30:00.000+01:00

When a service in ASP.Net is executed the error:

Unable to generate a temporary class (result=1). error CS2001: Source file ‘C:\WINDOWS\TEMP\filename.cs’ could not be found error CS2008: No inputs specified

can occur.

The problem are the permissions on the C:\WINDOWS\TEMP folder.
To fix it, give the user of the Application Pool full control on that folder.