USEFUL T-SQL QUERIES

Introduction:

In this article we will look at some of the basic T-SQL Queries. These T-SQL Queries are very commonly used in most of the applications and I would like to point out that how easily some of these task can be performed.

Returning String Instead of NULL:

Consider a situation in which your database table contains NULL and you don't want to return NULL but some message. Like suppose you have a Person table and a Phone Table and a person does not have a phone number so we can easily return a message saying "No Phone Number Found" instead of returning a NULL.

SELECT P.Name, 'PhoneNumber' = CASE WHEN Ph.PhoneNumber IS NULL THEN 'No Phone Number Exists' ELSE Ph.PhoneNumber END FROM tblPerson P, tblPhone Ph WHERE P.PersonID = Ph.PersonID

The heart and soul of this simple query is the CASE Statement where we check that if the field is NULL or not. If the field is NULL we just replace it with a personal message. You can also have multiple WHEN Statements checking for different conditions.

Update:

You can also use the ISNULL method of the SQL SERVER 2000 to do the same operation. Check out the code below which returns the same result.

SELECT P.Name, Ph.PhoneNumber, ISNULL(Ph.CellNumber,'No Phone Number') AS CellNumber
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID

Assigning Text to NULL Fields in Database Table:

In this small query we will see how we can assign text to the fields in the database that are NULL.

UPDATE tblPhone SET PhoneNumber = 'No Phone' WHERE PhoneNumber IS NULL

TABLE DATATYPE in SQL SERVER 2000:

Did you know that there is TABLE DataType in SQL SERVER 2000? Yes you can use it just like a TABLE and perform operations on that. Let's take a look at a simple example.

DECLARE @MyTable TABLE(PersonID int , Name nvarchar(50) ) -- TABLE DATATYPe INSERT INTO @MyTable(PersonID,Name) SELECT PersonID,Name FROM tblPerson SELECT * FROM @MyTable

The above example just shows how you can create a simple TABLE DataType and populate it with the Data from Person table.

FOR XML:

FOR XML can be used in different way but the most flexible is FOR XML EXPLICIT in which the developer has complete control over the returned XML. Let's take a look at a very simple example:

SELECT 1 AS TAG,
NULL AS PARENT,
PersonID AS [Person!1!PersonID],
NAME AS [Person!1!NAME!ELEMENT]
FROM
tblPerson
FOR XML EXPLICIT

And this will generate the following XML:

Person PersonID="38">AzamSaifJohnMarryNeno

Transferring Data Using SqlBulkCopy Class

Introduction:

Transferring data from one source to another is a common practice in software development. This operation is preformed in many different scenarios which includes migration of the old system to the new system, backing up the data and collecting data from different publishers. ASP.NET 2.0 includes the SqlBulkCopy class that helps to copy the data from different data sources to SQL SERVER database. In this article I will demonstrate the different aspects of the SqlBulkCopy class.

Database Design:

The database design is pretty simple as it is based on the Products table in the Northwind database. I have created three more tables in the Northwind database. Check out the database diagram below to have better idea.

The Products_Archive and Products_Latest have the same schema as the Products table while the Products_TopSelling table is different. I will explain the purpose of Products_TopSelling table later in this article.

The Products_Archive table contains 770,000 rows. You don’t have to worry about how the rows got there; you just need to think how to move all those rows in the Products_Latest table.

Transferring Data from Products_Archive to Products_Latest:

SqlBulkCopy contains an instance method WriteToServer which is used to transfer the data from the source to the destination. WriteToServer method can perform action of DataRow[] array, DataTable and DataReader. Depending on the situation you can choose the container you like but in most cases choosing DataReader is a good idea. This is because DataReader is a forward-only, read-only stream. It does not hold the data and thus is much faster then DataTable and DataRows[]. The code below is used to transfer the data from the source table to the destination table.

private static void PerformBulkCopy()

{

string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";

// get the source data

using (SqlConnection sourceConnection = new SqlConnection(connectionString))

{

SqlCommand myCommand = new SqlCommand("SELECT * FROM Products_Archive", sourceConnection);

sourceConnection.Open();

SqlDataReader reader = myCommand.ExecuteReader();

// open the destination data

using (SqlConnection destinationConnection = new SqlConnection(connectionString))

{

// open the connection

destinationConnection.Open();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))

{

bulkCopy.BatchSize = 500;

bulkCopy.NotifyAfter = 1000;

bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);

bulkCopy.DestinationTableName = "Products_Latest";

bulkCopy.WriteToServer(reader);

}

}

reader.Close();

}

}

There are couple of points to mention here. First I am using the DataReader to fetch to the rows from the database table. SqlBulkCopy class object “bulkCopy” sets the DestinationTableName property to the destination table which in this case is “Products_Latest”. Products_Latest is the destination table since the data is transferred from the Products_Archive table to the Products_Latest table. The bulkCopy object also exposes the SqlRowsCopied event which is fired after the rows identified by the NotifyAfter property has reached. This means the event will be fired after every 1000 rows since NotifyAfter is set to 1000.

The BatchSize property is very important as most of the performance depends on it. The BatchSize means that how many rows will be send to the database at one time to initiate the data transfer. I have set the BatchSize to 500 which means that once, the reader has read 500 rows they will be sent to the database to perform the bulk copy operation. By default the BatchSize is “1” which means that each row is sent to the database as a single batch.

Different BatchSize will give you different results. You should test that which batch size suits your needs.

Transferring Data Between Tables of Different Mappings:

In the above example both the tables had the same schema. Sometimes, you need to transfer the data between tables whose schema is different. Suppose you want to transfer all the product name and quantity from the Products_Archive table to the Products_TopSelling table. The schema in the two tables is different as they have different column names. This is also visible in the image above under the database design section.

private static void PerformBulkCopyDifferentSchema()

{

string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";

DataTable sourceData = new DataTable();

// get the source data

using (SqlConnection sourceConnection = new SqlConnection(connectionString))

{

SqlCommand myCommand = new SqlCommand("SELECT TOP 5 * FROM Products_Archive", sourceConnection);

sourceConnection.Open();

SqlDataReader reader = myCommand.ExecuteReader();

// open the destination data

using (SqlConnection destinationConnection = new SqlConnection(connectionString))

{

// open the connection

destinationConnection.Open();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))

{

bulkCopy.ColumnMappings.Add("ProductID", "ProductID");

bulkCopy.ColumnMappings.Add("ProductName", "Name");

bulkCopy.ColumnMappings.Add("QuantityPerUnit", "Quantity");

bulkCopy.DestinationTableName = "Products_TopSelling";

bulkCopy.WriteToServer(reader);

}

}

reader.Close();

}

}

The ColumnMappings collection is used to map the column between the source table and the destination table.

Transferring Data from XML File to Database Table:

The data source is not only limited to database tables but you can also use XML files. Here is a very simple XML file which is used as a source for the bulk copy operation.

(Products.xml)

private static void PerformBulkCopyXMLDataSource()

{

string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";

DataSet ds = new DataSet();

DataTable sourceData = new DataTable();

ds.ReadXml(@"C:\Products.xml");

sourceData = ds.Tables[0];

// open the destination data

using (SqlConnection destinationConnection = new SqlConnection(connectionString))

{

// open the connection

destinationConnection.Open();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))

{

// column mappings

bulkCopy.ColumnMappings.Add("productID", "ProductID");

bulkCopy.ColumnMappings.Add("productName", "Name");

bulkCopy.DestinationTableName = "Products_TopSelling";

bulkCopy.WriteToServer(sourceData);

}

}

}

The file is first read into the DataTable and then fed to the WriteToServer method of the SqlBulkCopy class. Since, the destination table is Products_TopSelling we had to perform the column mapping.

Conclusion:

In this article I demonstrated how to use the SqlBulkCopy class which is introduced in .NET 2.0. SqlBulkCopy class makes it easier to transfer the data from a source to the SQL SERVER database.

How to convert a string to proper case

To convert a string to proper case we will have to use the System.Globalization namespace.

To do this we will use the following code.

string myString = "VikRAM’s WebSite HaVinG a post oN pRoper cAsE";

TextInfo TI = new CultureInfo("en-US",false).TextInfo;

Response.Write (TI.ToTitleCase( myString ));

[Note: Generally, title casing converts the first character of a word to uppercase and converts the rest of the letters to lowercase.]

Sending Email asynchronously in Asp.Net 2.0

Another of the new feature in asp.net 2.0 is the support to send emails asynchronously. This is a very important feature. With the help of this feature you don’t need to wait for the email to be sent before performing other tasks in the page. But instead these tasks can be performed while the mail is being sent asynchronously.

To send Emails asynchronously we need to wire up a sendComplete event, create a send complete event and then call the sendAsync event.

To do this first create an object and assign it the mail object. We can access this object in the call back.

object userState = mail;

Now we need to wire up the event when the async send is complete.

smtp.SendCompleted += new SendCompletedEventHandler(SmtpClient_OnCompleted);

Now start the asynchronous call



smtp.SendAsync( mail, userState );

We have to write the wired-up method that will be invoked when the send is complete.

public static void SmtpClient_OnCompleted(object sender, AsyncCompletedEventArgs e)

{

MailMessage mail= (MailMessage)e.UserState;

string subject = mail.Subject;

if (e.Cancelled)

{ Console.WriteLine("Send canceled for mail with subject [{0}].", subject); }

if (e.Error != null)

{ Console.WriteLine("Error {1} occurred when sending mail [{0}] ", subject, e.Error.ToString()); }

else

{ Console.WriteLine("Message [{0}] sent.", subject ); }

That’s all you have to do to send the emails asynchronously

Registering the user control and custom control in the web.config

In asp.Net 1.X we had to import and use both customs server controls and user control on a page by adding the @Register directives to the top of the page. Once registered developers could then declare these controls anywhere on the page using the tag prefix and tag names configured in the @Register directive.

This is fine but if we have too many user controls across the sites (and that too ascx files) then it can be painful to manage across the site.

The control declaration is much cleaner and easier to manage in Asp.Net 2.0. Instead of duplicating them on all your pages, just declare them once within the new pages->controls section with the web.config file of your application

The controls need to be added in the controls tag inside the pages tag which will be inside the system.web tag.







An important this to note here is to use the ~ path as the user control can be used anywhere in the site. The “~” will resolve the control from the root of the web site.

Once the control is registered in the web.config file the control can be used by any page or user control in the site.

Another Important thing to note is that there is no performance difference in either registering the controls in the web.config or on the top of the page as they get compiled down to the same instruction in both the scenarios.