February 2005 - Posts
IntelliSense is now even smarter. It will not automatically filter what it brings up based upon what kind of statement you are using. For example, in a catch statement, it will only return exceptions. This is just one more little thing that they have added to the IDE to make our lives a little easier.
How many times have you been writing code and you start to type a statement and you notice Intellisense isn't picking it up? A new IDE feature in Visual C# will now pop up a smart tag and detect which namespace reference you are missing. It will give you the option of automatically adding the using statement or specifying the fullly qualified name. It sounds like magic to me so we will see if it actually works.
Yesterday, we covered the INTERSECT operator and today I will talk about the opposite of that, the EXCEPT operator. This operator can be used to return records that exist in one table but not the other. Previously you could do this with the NOT EXISTS operator, but the syntax was a lot more code as you can see below.
SELECT Table1.Column1, Table1.Column2, Table3.Column3 FROM Table1
WHERE NOT EXISTS
(SELECT Column1, Column2, Column3 FROM Table2
WHERE Table1.Column1 = Table2.Column1
AND Table1.Column2 = Table2.Column2
AND Table1.Column3 = Table2.Column3)
That's a ton of code that can be simply replaced by the code below:
SELECT Column1, Column2, Column3 FROM Table1
EXCEPT
SELECT Column1, Column2, Column3 FROM Table2
This is another useful time saving operator that will prove useful in the future.
C2 said that the Indigo stuff was interesting, but requested that I stop talking about stuff that won't be here for another 18 months. Therefore, I am going back to some SQL Server 2005 stuff. I will continue to talk more and more about SQL Server 2005 just because there are so many new features and there are not a lot of articles out there on it.
Today's topic is the INTERSECT operator. This is a new operator that allows you to join two sets of data on all columns in the select clause. For example, if you had two tables with similar schemas and Both Table1 and Table2 have columns named Column1, Column2, and Column3, the INTERSECT operator can be used.
If you wanted to find out which items in Table2 have exact duplicates in Table1, you would have to write something like this today:
SELECT Table1.Column1, Table1.Column2, Table3.Column3 FROM Table1 INNER JOIN
ON Table1.Column1 = Table2.Column1
AND Table1.Column2 = Table2.Column2
AND Table1.Column3 = Table2.Column3
Clearly, that's a bad time if you had a table with a lot of columns. Now all you have to do is:
SELECT Column1, Column2, Column3 FROM Table1
INTERSECT
SELECT Column1, Column2, Column3 FROM Table2
I believe this statement will be extermely useful when needing to compare the data in two tables.
Creating a client application for Indigo is somewhat similar to that of web services. It uses a proxy class just like a call to a web service does. A new tool, svcutil, can be used to generate a proxy by looking at the Indigo service in a DLL or it can look at the service directly and obtain a WSDL definition for it just like with web services. I am guessing this process will be integrated into the IDE.
Here is an example of a client class:
using System.ServiceModel;
public class MyClassClient
{
public static void Main()
{
// instantiate the proxy class
MyClassProxy myClass = new MyClassProxy();
// call methods of proxy class
Console.WriteLine(myClass.IsBobbyLame().ToString());
}
}
The above example would call the Indigo service and get the response of true and write it to the screen.
The service that I described on Friday can be hosted in a variety of ways. The first way is by using something called Windows Activation Service. I have no idea what this is but supposedly it functions similar to IIS but can be used for Indigo services. If anyone wants to figure out what that is and where it comes from, please let me know.
The second way is that it can be hosted using a console application or more likely a windows service. To do this a generic instance of the ServiceHost class is used. You simply pass the class name of your Indigo service as the generic parameter of the ServiceHost class. Once you have instantiated the class, you simply call the Open method to start accepting connections.
using System.ServiceModel;
public class MyClassHost
{
public static void Main()
{
// instantiate the indigo service using a generic
ServiceHost<MyClass> myClass =
new ServiceHost<MyClass>();
// open the indigo service so that it will accept connections
myClass.Open();
}
}
The last thing that is necessary to host an Indigo service is to define a binding in a configuration file. A binding simply specifies how the service can be accessed (i.e.: SOAP/HTTP, SOAP/TCP, etc.) The complete list of bindings is available in the article.
A typical configuration file would look like this:
<configuration>
<system.serviceModel>
<services>
<service serviceType="MyClass">
<endpoint
contractType="MyClass"
bindingType="basicProfileHttpBinding />
</service>
</services>
</system.serviceModel>
</configuration>
In this case the binding type is BasicProfileHttpBinding which is effectively SOAP over HTTPS. Tomorrow, I will talk about creating an Indigo client.
Indigo really isn't .NET 2.0 related because it won't be released until Longhorn comes out (which makes it probably 2.1 or 3.0). I am going to start talking about it more though, so that you have an idea of what it brings to the table. Indigo combines all of Microsoft's previous distributed computing technologies (i.e.: Web Services, .NET Remoting, Enterprise Services (DCOM), Web Services Enhancements, and MSMQ). Although fundamentally Indigo is not web services, it is based upon SOAP and objects are created in some what of a similar manner. Indigo gives support for a Service Oriented Architecture (SOA). Service Oriented is a new buzz word that you will start to hear more and more as Indigo gets closer. Indigo is transport independant. This means that it doesn't have to be exposed via SOAP/HTTP through IIS. It can be exposed using a Windows Service via TCP and a variety of other options.
To use Indigo, a new namespace System.ServiceModel will be available. The new attributes ([ServiceContract], [OperationContract], and [DataContract]) are used in lieu of a web service's [WebMethod] attribute. An Indigo service is simply a class marked with the ServiceContract attribute. To make a method accessible via Indigo, a method must have an OperationContract attribute. This attribute overrides any accessors (i.e.: public, private, protected), that you may have specified on a particular method. If the OperationContract attribute is not on the method even if it is public, the method will not be exposed. Conversely if a method is marked private, but the OperationContract attribute is present, the method will be exposed.
Here is an example of a simple Indigo service:
using System.ServiceModel;
[ServiceContract]
class MyClass
{
// Method will be exposed because it has the attribute
[OperationContract]
private bool IsBobbyLame()
{
return true;
}
// method will also be exposed
[OperationContract]
public bool IsCoreyLame()
{
return false;
}
// method will not be exposed
public bool IsMarcusLame()
{
return true;
}
}
So in this example the IsBobbyLame() and IsCoreyLame() methods will be exposed via Indigo. The IsMarcusLame() method will not be exposed, so the world will never know if Marcus is lame or not. Who am I kidding? We all know the answer to that.
This is a simple Indigo service. In the following tips, I will discuss how to make an Indigo client. If you want to know more now, again you can go read the article at:
Jeff Prosise from Wintellect has posted some training videos on ASP.NET 2.0. Of the things covered are Web Forms, State Management, Security, Master Pages and Site Navigation, Data Access, and Application Infrastructure. If you aren't too familiar with ASP.NET 2.0, these might be good to check out to get an overview of what is new.
A few things have been marked for deprecation in a future version of SQL Server (these will still be included in 2005, but possibly not in the future). One of the features to be removed is the shortened syntax for outer joins (*=).
For example, currently I use the syntax:
SELECT e.[ErrorId], [ErrorKey], [ErrorType], [SessionId], [Description], [ServerName], [ErrorDateTime]
FROM [ErrorLog] e, ErrorLogSession s
WHERE E.ErrorId *= s.ErrorId
Instead of the syntax:
SELECT e.[ErrorId], [ErrorKey], [ErrorType], [SessionId], [Description], [ServerName], [ErrorDateTime]
FROM [ErrorLog] e
LEFT OUTER JOIN ErrorLogSession s ON e.ErrorId = s.ErrorId
I have always been a fan of writing procedures the first way. It's an older way of doing a join but it is a lot less clutter in your stored procedures.
Again, this feature is marked for deprecation in a future version of SQL Server (not including 2005). Therefore, if you have any stored procedures that use this syntax, you have a mere three to five years before you need to do anything about it.
A new article on Indigo just came out. This article goes through what Indigo is, what it supports, and how it will change things. I recommend that everyone read it, but since no one probably will, I will touch on some of the key points in the next few days.
Exception handling in T-SQL has always been rather lacking. Now, they have added try/catch support similar to that of .NET. This is typically used when you are using a transaction and performing multiple inserts, updates, or deletes. The following example shows the use of TRY / CATCH.
/* not exactly sure what this does yet, but it appears to be required */
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
/* update statement 1 */
/* update statement 2 */
END TRAN
END TRY
BEGIN CATCH TRAN_ABORT
DECLARE @ErrorNumber int
/* contains ErrorNumber for exception (i.e.: constraint violation, null violation, etc.) */
SET @ErrorNumber = @@error
/* do something based upon the @ErrorNumber */
END CATCH
This should make it easier to have more advanced T-SQL Stored Procedures that have proper exception handling.
The OUTPUT clause is a useful new statement that can be used to return records that were inserted or deleted after an insert or delete respectively. This can be useful if you want to return the row that was just inserted from a stored procedure.
In the example below, a temporary table is created. The schema of this temporary table would match that of the table that you are inserted into. The OUTPUT clause on the INSERT statement is the used to directed the inserted values into the temporary table. Lastly, a select is performed on the temporary table.
CREATE PROC MyInsert @Description varchar(64)
AS
/* This would be a temporary table */
DECLARE @TempTable TABLE (RecordId in, Description varchar(64))
/* Insert a new item and output into a temp table */
INSERT INTO MyTable (Description)
OUTPUT inserted.* INTO @TempTable
VALUES(@Description)
/* return the values of the temporary table */
SELECT * From @TempTable
Triggers can now also be implemented through .NET code. Triggers are similar to stored procedures but they can only have a return type of void. A new object, SqlTriggerContext, provides access to the INSERTED and DELETED tables that you might have used in the past with a T-SQL trigger. Unlike Triggers in SQL Server 2000, triggers (both T-SQL and CLR) can be tied to statements over than INSERT, UPDATE, and DELETE. For example, you can create a trigger that is executed every time a new table is created, etc.
Here is an example, create a new class and then create a new public static method.
public static void TableCreated()
{
// this gets you access to the trigger
SqlTriggerContext triggerContext = SqlContext.GetTriggerContext();
// determine what kind of action just occurred
if (trigger.TriggerAction == TriggerAction.CreateTable)
{
// do something (i.e.: send e-mail, insert a record somewhere, etc.)
}
}
There are several new data types available now for interacting with SQL Server. Any time you write a User Defined Aggreate , User Defined Function, or any other CLR object for SQL Server, you will use these new data types. Unlike traditional .NET data types, all of the Sql data types implement the INullable interface. This means that any instanace of this type will have an IsNull property to tell you if the data type is null.
The following types in SQL Server, char, varchar, nchar, nvarchar, ntext, and text get mapped to the SqlString data type. Binary, varbinary, image, and timestamp get mapped to the SqlBinary or SqlBytes data types. The bit data type gets mapped to SqlBoolean. Tinyint, smallint, int, and bigint get mapped to SqlByte, SqlInt16, SqlInt32, and SqlInt64 respectively. DateTime gets mapped to SqlDateTime.
There are several other data types but you can probably guess the pattern by now. All of these types are exposed through the System.Data.SqlTypes namespace.
User Defined Aggregates in SQL Server 2005 are one of the first new CLR objects that I will be talking about. UDAs as they are often abbreviated are commonly used to concatenate strings of data. For example, you can use them to create a comma seperate lis of E-mail addresses, etc.
As you have heard you can write .NET code and place it on a SQL Server to handle special functions. To do this, you make use of the new System.Data.SqlServer namespace.
To create a User Defined Aggregate, you must define four public methods: Init, Accumulate, Merge, and Terminate.
The Init method simply initializes the value you are aggregating to some value (i.e.: 0 for integers, a new string builder for strings, etc.)
public void Init()
{
returnString = new StringBuilder();
}
The Accumulate method is responsible for addiing new data to the existing data.
public void Accumulate(SqlString myString)
{
// append the new string with a comma
returnString.Append(myString + ", ");
}
The Merge method is used to accumulate items at the object level. In this case if this class was called Concatenate, the Merge method would look like this.
public void Merge(Concatenate myConcatenate)
{
returnString.Append(myConcatenate.returnString.ToString());
}
Lastly, the Terminate method does any special formatting before sending the aggregated value back. For example, this method could remove the command at the end of the string.
public SqlString Terminate()
{
return returnList.ToString();
}
If you notice there are some new datatypes (i.e.: SqlString, SqlDecimal). Basically these types match to SQL Server types. I'll cover these more in the future.
To use a UDA, an assembly has to be compiled and registered with SQL Server. Assuming the assembly was called MyAggregate.dll, you would register it like this:
CREATE ASSEMBLY MyAggregate
FROM 'MyAggregate.dll'
Note, you would have to specify the full path to the DLL on the SQL Server. Next you have to register the Aggregate in the assembly.
CREATE AGGREGATE Concatenate (@value varchar(max))
RETURNS varchar(max)
EXTERNAL NAME MyAggregate:Concatenate
To acctually use the aggregate in a query, you could do something like this:
SELECT dbo.Concatenate(EmailAddress) FROM blah
Today's tip has gone kind of lengthy, so if you are interested in the future, there is more information in the books we have.
More Posts
Next page »