Bashar Kokash' Blog

Hotfix for C#

2008-11-06T12:01:00Z

After istalling .NET SP1, we all may encountered false C# compilation errors appear in the Error List for ASP.NET Website projects.

Yes, that's because SP1 adds a new feature "live reporting of compilation errors" but unfortunately caused this bug, to solve this problem, I had to turn this feature off and loose its effects, but recently Microsoft has released a new hotfix that will solve this problem, you can download it here.

Thanks to chkdsk command...

2008-09-25T06:15:00Z

I had accidentally unplugged the power of my PC at office while it's running. At first, I thanked GOD that I wasn't in a middle of a long programming task. I plugged the power again and started the PC, unfortunately the windows couldn't start at all, even on safe mode. After a while I decided to use the Windows XP CD, but I don't know why the setup couldn't read the file system on C drive, and suggested to format this drive even when I tried to install a new Windows version on D drive. Of course I will never format a drive while I don't have a recent backup of the data.

As a final solution I selected the Repair option using the Console during the Windows XP setup, and a DOS cmd was lunched, I couldn't access c drive and I get a file corrupted message, but when I tried CHKDSK command to check C for errors I was surprised that it could be able to start and check the disk for errors.

When restarting then everything was alright. Thanks chkdsk you save my PC.

Microsoft Visual Studio 2008 Service Pack 1

2008-08-12T08:20:00Z

Microsoft released the Visual Studio 2008 Service Pack 1 that includes numerous bug fixes, as well as updates.More Details

You can download the update here.

LINQPAD: Greate tool for LINQ

2008-08-11T11:11:00Z

This is an amaizing free tool for LINQ.

LINQPad lets you interactively query SQL databases in a modern query language: LINQ

To download LINQPAD, please visit: http://www.linqpad.net/

Using Unit Test in Visual Studio 2008 Professional

2008-07-20T12:50:00Z

I would like to share you my article on Defaultdotaspx.com

It describes the basics of unit testing then a few steps about how to use unit tests in Visual studio 2008.

I hope this article helps to understand the basics so more complex scenarios would be easier.

Using Unit Test in Visual Studio 2008 Professional

Class browser

2008-07-13T12:25:00Z

My brother Bassl started to blog about windows clients applications, today he posted a new class browser tool, I think it's great and useful, but still need some more debugging since it's the first version, anyway you can browse your classes here, test it and give him your feedback.

Tools to block and eradicate SQL injection

2008-06-25T06:31:00Z

SQL injection is one of the most common attacks on websites, so Microsoft has released an advisory for the recent SQL Injection attacks

Joe Stanger has a great post describing those tools for It professionals as well as developers.

SQL CLR Limitations in SQL Server 2005

2008-06-25T06:03:00Z

Referring to my previous post about How to implement user defined aggregates in SQL Server 2005, I had a couple of emails about some problems when data size exceeds the 8KB which was a limit in SQL Server 2005.

I forgot to mention this point in my previous post, also I have to say that this problem or limitation is not a limitation any more in SQL Server 2008.

Measuring SQLCR Performance using SQL Server Profiler

2008-06-09T08:38:00Z

Recently, I've been doing a lot of invoking .NET Assemblies within SQL Server 2005, I bloged an overview of SQLCLR here, also I posted about how to implement aggregation function in C# here.

Actually invoking .NET code has many advantages but at the same time we should be aware of the performance, that's why I tested my CountNulls Aggregation function - created in a previous post - with the SQL Server Profiler tool.

I tested the function on the Person.Contact table that is in the AdventureWorks Database which contains 19972 records.

SELECT dbo.CountNulls(MiddleName) from Person.Contact

The duration time was ranging between 93 and 154 milliseconds

While the following T-SQL statement gives the same results but with duration time ranges from 1 to 3 milliseconds maximum.

Select count(*) as from Person.Contact where MiddleName is null

As a result we should not move to use SQLCLR unless there is a clear sign, that means to implement a function in .NET we have to give T-SQL a chance and only when T-SQL fails we then use .NET Assemblies.

How to implement your own aggregate function in SQLCLR, SQL Server 2005

2008-06-07T08:15:00Z

I posted earlier an overview about the SQLCLR component of the SQL Server 2005, and showed how to implement a simple user-defined function using Visual Studio 2008 and C#, this post will show the importance of .NET Framework to SQLCLR by adding more functionality to SQL Server 2005.

Introduction:

Using T-SQL we can create user-defined function, stored procedures and triggers, but we can't do is to add a user-defined aggregate function. An aggregate function operates against a collection of values, but returns only a single value, for example the sum(), avg(), and count() functions. Each of those functions works on all the rows in a specified column, but only returns one value like the sum , the average, or the number of rows.

But what if I needed to create my own aggregate function that does a special job, using T-SQL there is no direct way, but we can use cursors and loop for each row which may result complex code with a bad performance, fortunately SQLCLR provides us the ability to implement user-defined aggregate.

Implementation:

The following will show how to implement a simple aggregate function using C# and Visual Studio 2008.

The aggregate function that was chosen for this example is a simple COUNTNULL aggregate function that returns the number of NULL rows in a specified column, of course you can do it easily in T-SQL but this is just a simple example.

Inside your Database project in Visual Studio add an new item and select Aggregate, and a new class is added to the project, actually it's a structure.

Each aggregate function needs to be in a separate struct, notice this struct is marked as [Serializable] and [SqlUserDefinedAggregate(Format.Native)].

SQL Server 2005 needs to store effective representation of the aggregate on disk so the aggregate should be Serializable and SqlUserDefinedAggregate attribute indicates that this class or struct is a user-defined aggregate, and the property Format.Native tells the .NET Framework to handle the serialization process using a simple algorithm and it works only with value types that's why the aggregate is defined as a struct.

You can also specify the property name in the SqlUserDefinedAggregate attribute to the name of you Aggregate function "The name that is called in SQL Server 2005".
[SqlUserDefinedAggregate (Format.Native,Name = "CountNulls")]

Now, inside the class we have to implement four methods in order the aggregate to work, there are init, accumulate, merge and terminate.

Init() method is used in order to initialize any needed resources or variables and is called only once when the aggregate is called.

Accumulate() method is the actual implementation of the aggregate function and is called once for each row.

Merge() method is used because sometimes if the the column to aggregate contains a large number of data the SQLCLR may split those rows into groups and aggregate each group individually and then merge the results of those group again, then the Merge() method is called.

Terminate() method is used free any used resources and to return the result of this aggregate function.

Below is the code for the CountNulls aggregate function with some comments:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.Native,Name = "CountNulls")]
public struct CountNulls
{
    private int Counter; // field to count the not null rows
    public void Init()
    {
        Counter = 0;      // initialization
    }
    public void Accumulate(object Value)
    {
        if (Value == DBNull.Value) // count just the rows that are not equal to NULL
            Counter++;
    }
    public void Merge(CountNulls Group)
    {
        this.Counter += Group.Counter; // when merge is needed the counter of other groups should be added
    }

    public SqlString Terminate()
    {
        return new SqlString(Counter.ToString()); //returning the results
    }
}

After you compile this class you have to deploy it in order to make available to be used inside SQL Server 2005

Finally go to SQL Server Management Studio and use your aggregate function as follows:

dbo.CountNulls(EndDate)from HumanResources.EmployeeDepartmentHistory

Summary:

Each class or struct should be public and dedicated for only one aggregate function.

Serialization is required so SQLCLR could store the representation of the aggregate on disk.

Note that this aggregate function can only take one parameter, we can't implement aggregation with more than one parameter in SQL Server 2005, but we can with SQL Server 2008 and I'll post about it later.

Microsoft 2008 Package

2008-06-05T07:26:00Z

Recently I have been trying a lot of Microsoft new products, Visual Stdio 2008, SQL Server 2008 but I've never got to try Windows Server 2008, and while I was searching around for those new products I ran into this amazing downloadable web cast called Dynamic IT.

Bob Kelly introduces all those products, not only describing the new features but also showing live demos.

You can download this Dynamic IT here

SQL CLR overview

2008-06-04T07:34:00Z

Introduction:

SQL Server 2005 with it's built in T-SQL as well as SQL SERVER Management Studio are just amazing, you can build your database, create your own procedures, functions and triggers. You can also access data, process information and update your records.

Till now this is all fine, but what if you wanted to perform a complex processing a on the data, or apply a certain algorithm that require a lot of loops then you have to deal with a complex T-SQL code and a lot of cursors, which is not recommended for performance issues.

The solution is to use the SQL CLR which is built into the SQL SERVER 2005 that allows us to run and execute .NET code with in SQL Server 2005 environment.SQL CLR enables developers to add user-defied functions,stored procedures and triggers as in T-SQL, but what it adds is the ability to define your own types as well as your own aggregate functions.

Please read this introduction to the SQL CLR for more information.

Simple Example:

This following is an example on how to use .NET code inside SQL Server 2005:

First, run Visual Studio 2008 or 2005, then create a new project from File -> New -> Project.

Form the Project type chose Database project and select the SQL Server Project template and click OK.

Then New Database Reference window is shown you can select the server name and then chose the database you want to work with, or you can attach a new database file to the server, test the connection and if every thing is good, just click OK and a dialog box will ask you if you want to enable debugging the .NET code for the current connection.

Now to add a user defined function, right click on the project title then select Add -> User-Defined Function

A new C# class will be opened with the following code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString HelloWorld()
    {
        return new SqlString("Hello");
    }
};

Notice the name spaces that are already added to the class in order to make use of SQLCLR.

As we can see above the function should be statice and should be attributed with [Microsoft.SqlServer.Server.SqlFunction] just to tell the SQLCLR to consider this function a user defined function so we can add it and use it in our T-SQL code, we can also add any other not attributed functions, but will not be recognized as user defined functions by the SQLCLR.

This user defined function returns a string but it's not of the class String it's of the class SqlString, the integer is also SqlInt32 etc...

Now, we have implemented our function we can build our .NET class, and it will be compiled as a .NET library class.

The last step now is to deploy this user defined function, in Visual Studio it's so easy just go to Build menu and then select Deploy Solution, then VS will take care of creating an assembly in the SQL Server and will also create the user defined function and attached it with the function in that assembly, of course we can do it using T-SQL but VS takes care of every thing.

Before calling the function we have to enable the SQLCLR inside the SQL Server because it's disabled by default for security reasons.

sp_configure 'clr_enabled',1

reconfigure

What else we have to do? actually nothing, but calling our user defined function that is written in C# but inside the SQL Server 2005 using a single select as follows:

select dbo.HelloWorld()

Conclusion

At the end, SQL CLR is a great feature of the SQL Server 2005 that enables the use of the great features of the .NET Framework and classes. It doesn't require too much code but results a lot of benifits.

Visual Studio 2008 SP1 Beta installation note

2008-05-24T07:31:00Z

Visual Studio 2008 Service Pack 1 Beta released and you can download it here.

But if you previously installed a Visual Studio 2008 Hotfix, you must run the Hotfix Cleanup Utility before installing Visual Studio 2008 SP1 Beta.You can download the Visual Studio 2008 Hotfix Cleanup Utility for Installing Visual Studio 2008 SP1 Beta here.

And if you are using Windows VISTA you should install Service Pack 1 first.

Default dot aspx

2008-05-14T09:59:00Z

Default.aspx

No, it's not the name of a page written in ASP.NET, it's a new great website for learning ASP.NET and all relevant technologies.

It contains a lot of topics like: ASP.NET, SQL, JavaScript, OOP,XML, etc...

Tutorials are presented in terms of question and answers, which is suitable for new ASP.NET programmers.

By the way, you can ask your own questions there.

Transforming XML using XSLT

2008-05-13T07:38:00Z

Dealing with xml files as a place to store data is very common in ASP.NET, and it's very easy to implement, just use the XmlDataSource and set the DataFile property to your xml file then set the DataSourceID of the data control (GrideView, DataList...) to the XmlDataSource.

If data records in the xml file are described as attributes, then there is no problem while displaying the data records directly in the GrideView as the following example:

But what if the xml file was written in different way and the previous attributes are represented as child nodes instead, so the previous xml will look something like this:

<People>
    <Person>
      <name>
        Bashar Kokash
      </name>
      <age>
        22
      </age>
    </Person>
    <Person>
      <name>
        Bassl Kokash
      </name>
      <age>
        21
      </age>
    </Person>
    <Person>
      <name>
        Morhaf Musa
      </name>
      <age>
        25
      </age>
    </Person>
</People>

Simply we can rewrite the xml file to be just like what we want ie; as records each record is represented by node and a set of attributes. Actually it's not that simple when dealing with large amount of data stored as xml. Instead we can Transform the xml file into the desired xml using the XSL Transformation.

To write code in XSLT add an XSLT File to your project, remove the Template section then format the template of the xml file that you want to get using XSLT tags, for the previous xml the following XSLT code will transform the xml hierarchical nodes into xml records as in the first example:

<xsl:template match="/">
    <xsl:for-each select="People">
      <xsl:element name="People">
        <xsl:for-each select ="Person">
          <xsl:element name ="Person">
          <xsl:attribute name="name">
            <xsl:value-of select ="name"/>
          </xsl:attribute>
          <xsl:attribute name ="age">
            <xsl:value-of select ="age"/>
          </xsl:attribute>
          </xsl:element>
        </xsl:for-each>
      </xsl:element>
    </xsl:for-each>
</xsl:template>

Then as a final step when setting the xml file to the XmlDataSource we have also to set the XSLT transformation file. Now we can bind our xml file to the GrideView having no problems.