"Read the best books first, or you may not have a chance to read them at all.." -- Thoreau

Sql Server 2005 BETA 1 "SQLEXPRESS" was released to the MSDN Subscriber community on July 1, 2004. One of the most interesting new features (and there are many) is the ability to host the CLR runtime and assemblies with code for functions, stored procedures and UDTs (User-Defined Types) written in C# or VB.NET, right inside SQL Server. Once you get the various syntax issues out of the way (there is an extreme lack of good documentation as of this writing in early July, 2004), a whole new world opens up to you as a developer.

Have some cool .NET code you've written that you'd like to make into a user - defined function callable from T-SQL? No problem - now you can, and that's just the beginning! In this article, we'll put together a C# class that gets a stock quote over the web from Yahoo Finance, compile it into a .NET Class library, and demonstrate how the function can be called with normal SQL Code - right from out of SQL Server 2005. But first, let's go over a few SQLEXPRESS issues:

SQL Server Express (2005)

Developers have been complaining that they cannot connect to SQL Server Express. That is because the default instance name in the various dialogs is not populated (or populated incorrectly). Enter ".\SQLEXPRESS" as the server name and you will be able to connect.

I would not recommend installing SQL Server Express alone, unless you do so with either one of the Web Developer Express versions, or with the full Visual Studio.NET 2005 Beta. This is because the only real way you can program against SQL Server Express (currently) is from within the VS.NET IDE - which now has vastly improved Data - related capabilities, including the ability to create and design databases from scratch.

The API's available to program against SQL Server Express are the same as those for SQL Server 2005, so that users have a seamless experience if they choose to move to other editions of SQL Server 2005. All of the new features in SQL Server 2005, such as common language runtime (CLR) integration, new data types such as VARCHAR(MAX) and XML, user-defined types, and user-defined aggregates are supported. Also, SQL Server Express databases can attach to SQL Server 2005, and applications programmed with a SQL Server Express instance will work equally well with a SQL Server 2005 instance. Replication and SQL Service Broker functionality is also available. There is also supposed to be a new "XM Enterprise Manager" like clone, but I have not seen it available yet.

The sa login: SQL requires a strong sa password for security reasons, and during GUI installs and silent SQL authentication mode installs, the user must provide a strong sa password. However, for silent Windows authentication installs, the sa password is not a requirement. The reason is that when using Windows authentication mode, the silent SQL Server Express setup provides a random strong sa password if the password is not specified by the user. The setup also disables the sa account in this case, so that you must explicitly enable sa at a later stage using T-SQL or the Express Manager tool if you wish to use it. This is done so that the ISVs do not have to provide the password when using windows authentication, so that the mass deployment scenarios are not blocked.

NOTE: You can enable mixed - mode authentication in the Registry by changing the LoginMode value from 1 to "2", here:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

LoginMode =2

Of course, since you still don't know the strongly named "sa" password from when it was installed, you can still add a new user in SQLCMD and make them a sysadmin with:

SQLCMD -S.\SQLEXPRESS -E

sp_addLogin 'username', 'pass'
GO
sp_addsrvrolemember 'username', 'sysadmin'
GO

Don't forget to give yourself a gold star for remembering to stop and restart the SQLEXPRESS service to get that registry change to take.

NOTE: Your database / server access options at this point (until they release the new XM GUI tool) consist of working out of the VS.NET IDE, or using the provided SQLCMD command - line tool, which is extremely similar to OSQL. As a matter of fact, ALL of the SQL SERVER 2005 code you will see in the sample application below was tried out and brought up to "working condition" using the SQLCMD.EXE utility first. Also, Lloyd Sheen has developed a nice little QA clone for SQLEXPRESS that you can find out about on the SQLEXPRESS newsgroup. With Lloyd's permission, we are also hosting a copy of his latest zip file here.

Connection Strings

Typical Connection Strings look like:

Server=.\SQLEXPRESS;Integrated Security=True;Database=DIARY

or:

Server=PETER\SQLEXPRESS;User ID=peter;password=xyzabc;Database=DIARY;Persist Security Info=True

You can also use the "Add new Item" wizard in an Application and add a new blank database, which results in a connection
string like the following:

Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=C:\VS2005\WindowsApplication1\WindowsApplication1\TESTING.mdf

This is a new behavior with SQL Server 2005- the ability to create and attach a database at runtime. The Database can be deployed along with the application, using typical XCOPY - style deployment. Within Visual Studio.NET, you can add tables, indexes, stored procedures and everything else - without ever leaving the comfy confines of the IDE.

Networking support for SQL Express

Only the shared memory on the local machine is accessible by default for SQL Server Express, although the user can explicitly turn on other supported protocols such as TCP/IP and Named Pipes. VIA and HTTP protocols are not supported in SQL Server Express. With only shared memory available by default, connections from a remote machine to SQL Server Express will fail unless the networking is turned on. To turn networking on, you can do one of the following:

Use SQL Computer Manager to enable relevant protocols and start SQL Browser.

Use DISABLENETWORKPROTOCOLS=0 in the setup command line, if you know in advance that networking support is needed (this is not an option with the integrated VS.NET 2005 install).

Use SMO-based scripting to enable the protocols.

Service Broker

Service Broker is basically an asynchronous programming framework for database applications.  This means (among many other things) that you can establish a reliable, asynchronous, bi-directional communications sessions between a client and server. Aside from the obvious benefits of a reliable connection - client able to run even if the network is temporarily unavailable, the asynchronous, bidirectional nature of the communications means that the client can queue up work as fast as the user can enter it for the server to process when it has processor cycles available and the server can send data to the client without the client requesting it, even if the client is not on line at the time. Service Broker must be running with, for example, the TCP library enabled, in order to access SQLExpress from another machine.

NOTE: As of late July, 2004 the SQLExpress Beta 2 version has since been posted. This incorporates some bug fixes and also allows Mixed Mode authentication to be set up during the install phase. If you have already installed the BETA1, follow the instructions very carefully when attempting to install BETA 2. Particularly, SQLEXPRESS, the SQL Native Client, the Tools, and also DOTNET Framework 2.0 must all be UNINSTALLED first. Then replace the Framework using the latest DOTNETFX.EXE installer, and finally install SQLEXPRESS, and you should have no trouble with the rest. Also, for MSDN Subscribers, the DEVELOPER (Full edition) version of SQL Server 2005 BETA 2 is now available at Subscriber Downloads.

Show Me the Code!

First, let's breeze through the class that gets the stock quote from Yahoo finance:

using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
using System.Diagnostics;
using System.Data.Sql;
using System.Data.SqlServer;
namespace PAB.SqlExpress.Data
{   
    public class Stocks
    {
        public Stocks()
        {
        }        
        [SqlFunction(DataAccess = DataAccessKind.None)]
        public static string GetQuote(string symbol)
        {           
            string serverURL = 
                  @"http://finance.yahoo.com/d/quotes.csv?s=" + symbol + "&&f=st5l9c6p4b1a3&e=.csv";
            HttpWebRequest webreq = (HttpWebRequest)WebRequest.Create(serverURL);
            webreq.MaximumAutomaticRedirections = 60;
            //Retrieve  HttpWebResponse object from the Search server URL
            HttpWebResponse webresp = (HttpWebResponse)webreq.GetResponse();
          StreamReader strm = new StreamReader(webresp.GetResponseStream(), Encoding.ASCII);
           string res = strm.ReadToEnd();
            strm.Close();
            string[] theQuote = res.Split(Convert.ToChar(","));
            string tmpQuote = theQuote[2]; //last
            // remove any crap that yahoo puts in there
            tmpQuote = tmpQuote.Replace("<i>", "").Replace("</i>", "");
            return tmpQuote;
        }
    }
}

The above code should be self-explanatory to any developer advanced enough to be using Visual Studio.NET 2005. The only items of note are that we will need a reference to the sqlaccess assembly which resides (on my machine) at:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

The other item is the attribute " [SqlFunction(DataAccess = DataAccessKind.None)]". There are other enumerations when your code actually does make Data Access, and you will see them in Intellisense.

We compile this assembly and we are ready to bring it into SQL Server 2005. Now I have a Windows Forms "Test Harness" application that does essentially two thing:

1) In the Form_Load handler, I clear out any existing references to my assembly and the SQL Function that calls it (there is some kludge exception-handling code in the downloadable solution that runs around it if they don't exist and generate SqlExceptions).

2) I set up the assembly reference and create the function. This all happens pretty fast, so for testing purposes it's fine to do this. In production, you would have more complex code. Here is the Form_Load handler code, verbatim (except for breaking some long lines for formatting purposes):

private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection Conn = 
                new SqlConnection(@"Server=.\SQLEXPRESS;Integrated Security=True;Database=master");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType=CommandType.Text;
            cmd.Connection =Conn;
            Conn.Open();
            // Clear our function and Assembly out first, if still there...
            cmd.CommandText = @"DROP FUNCTION GetQuote";
            cmd.ExecuteNonQuery();
            cmd.CommandText = @"DROP ASSEMBLY PABSqlExpress";
            cmd.ExecuteNonQuery();
            // be sure to change the file path to match your environment...
            cmd.CommandText =
               @"CREATE ASSEMBLY PABSqlExpress FROM 'C:\VS2005\PAB.SqlExpress.Data\";
            cmd.CommandText+=
             @"PAB.SqlExpress.Data\bin\Debug\PAB.SqlExpress.dll' WITH PERMISSION_SET =UNSAFE";
            cmd.ExecuteNonQuery();
            cmd.CommandText ="CREATE FUNCTION GetQuote( @symbol NVARCHAR )";
            cmd.CommandText+=@"RETURNS NVARCHAR(10) AS EXTERNAL NAME";
            cmd.CommandText+=@"  [PABSqlExpress].[PAB.SqlExpress.Data.Stocks].GetQuote";
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            Conn.Close();
            txtDisplay.Text = "Assembly and function Set up OK!";
        }

Finally, in my Button_Click handler, after the user has entered a stock symbol:

 private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection Conn = 
                  new SqlConnection(@"Server=.\SQLEXPRESS;Integrated Security=True;Database=master");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = Conn;
            Conn.Open();
            string stockSymbol = this.txtSymbol.Text;
            cmd.CommandText = "SELECT dbo.GetQuote('" +stockSymbol + "')";
            SqlDataReader rdr = cmd.ExecuteReader();
            rdr.Read();
            txtDisplay.Text = rdr.GetString(0);
            cmd.Dispose();
            Conn.Close();
        }

As can be seen above, we are calling a .NET method on a class in an assembly as if it were a T-SQL function inside SQL Server. In point of fact, that is precisely what it is! The result on my test form, looks like so:

I'm really looking forward to working with SQL Server 2005, thanks to the hard-working folks in Redmond.

download the Visual Studio 2005 Beta 1 Solution that accompanies this article