Anyone writing CLR managed code in SQL 2005 yet?

[DiverMarv]

I'm experimenting with it, so I can start comparing. But unfortunately I'm getting an error. So if anyone has any ideas with this I appreciate any input.

Code following (in C#)

using System;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace SQLManagedCode.Movies
{
    public class GetMovies
    {
        public static void sp_GetMovieList()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Context Connection=true";

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"SELECT	MovieID, MovieTitle, YearReleased, IMDBPage
                                FROM	dbo.MyMovies_Movies";

            conn.Open();

            ArrayList movies = new ArrayList();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                movies.Add(new Movie(dr.GetInt32(0), dr.GetString(1), dr.GetInt32(2), dr.GetString(3)));
            }
            //SqlContext.Pipe.Send(dr);

            foreach (Movie m in movies)
            {
                m.OutputMovies(SqlContext.Pipe);
            }

            dr.Close();
            conn.Close();
        }
    }

    public class Movie
    {
        private int movieID;
        private string movieTitle;
        private int yearReleased;
        private string imdbPage;
        private string checkedOutTo;
        private string genreList;

        // Set up the metadata for the return
        SqlMetaData[] metadata = new SqlMetaData[] {
            new SqlMetaData("MovieID", SqlDbType.Int),
            new SqlMetaData("MovieTitle", SqlDbType.VarChar),
            new SqlMetaData("YearReleased", SqlDbType.Int),
            new SqlMetaData("IMDBPage", SqlDbType.VarChar),
            new SqlMetaData("CheckedOutTo", SqlDbType.VarChar),
            new SqlMetaData("GenreList", SqlDbType.VarChar)
        };


        public Movie(int movieID, string movieTitle, int yearReleased, string imdbPage)
        {
            this.movieID = movieID;
            this.movieTitle = movieTitle;
            this.yearReleased = yearReleased;
            this.imdbPage = imdbPage;
        }

        public void OutputMovies(SqlPipe pipe)
        {
            SqlDataRecord record = new SqlDataRecord();
            pipe.SendResultsStart(record);
            
            record.SetInt32(0, movieID);
            record.SetString(1, movieTitle);
            record.SetInt32(2, yearReleased);
            record.SetString(3, imdbPage);
            record.SetString(4, "Checked Out To"); // 
            record.SetString(5, "Genre List"); // 
            pipe.SendResultsRow(record);
            
            pipe.SendResultsEnd();
        }
    }
}

 

[DiverMarv]

I forgot to paste in my error message! I get this when I try to run the stored procedure based on the assembly (compiled from the code in the previous post). This is in SQL 2005 Server Management Studio:

A .NET Framework error occurred during execution of user defined routine or aggregate 'GetMovies': 
System.ArgumentException: The dbType VarChar is invalid for this constructor.
System.ArgumentException: 
   at Microsoft.SqlServer.Server.SqlMetaData.Construct(String name, SqlDbType dbType)
   at Microsoft.SqlServer.Server.SqlMetaData..ctor(String name, SqlDbType dbType)
   at SQLManagedCode.Movies.Movie..ctor(Int32 movieID, String movieTitle, Int32 yearReleased, String imdbPage)
   at SQLManagedCode.Movies.GetMovies.sp_GetMovieList()
. User transaction, if any, will be rolled back.