This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Changes for an SQLCLR stored procedure written in Visual Studio 2005 Beta 2 - source code Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

There are some notable changes from beta1 to beta2 when creating code for SQLCLR. I talked about a few of them here. Let's look at some code that reflects those changes.  For some reason my cut and paste isn’t carrying over the syntax coloring from Beta2 IDE, so I had to do color this one by hand.

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlServer;

using System.Data.SqlTypes;

public class Procs

{

    // uses SqlDataReader and returns results via SqlPipe

    [SqlProcedure]

    public static void getAuthorsByState(SqlString state)

    {

        SqlCommand cmd = SqlContext.GetCommand();

        cmd.CommandText = "select * from authors where state = @state";

        cmd.Parameters.Add("@state", SqlDbType.VarChar);

        cmd.Parameters[0].Value = state;

        SqlDataReader rdr = cmd.ExecuteReader();

        SqlPipe pipe = SqlContext.GetPipe;

        pipe.Send(rdr);

    }

}

Now lets look at the changes needed to make it work in Beta 2

using System;

using System.Data;

using System.Data.SqlClient; // Added this for SqlCommand object

using Microsoft.SqlServer.Server; // This used to be System.Data.SqlServer

using System.Data.SqlTypes;

public class Procs

{

    // uses SqlDataReader and returns results via SqlPipe

    [SqlProcedure]

    public static void getAuthorsByState(SqlString state)

    {

        // This used to be SqlContext.GetCommand()

        SqlCommand cmd = new SqlCommand("Context Connection=true");

        cmd.CommandText = "select * from authors where state = @state";

        cmd.Parameters.Add("@state", SqlDbType.VarChar);

        cmd.Parameters[0].Value = state;

        SqlDataReader rdr = cmd.ExecuteReader();

        // This used to be SqlContext.GetPipe();

        SqlPipe pipe = SqlContext.Pipe;

        pipe.Send(rdr);

    }

}