[DotNetDevelopment] SQL 2005 table-valued parameters

[jay]

I created a class for passing tabled-valued parameters to stored
procedures (just uint[] and string[] right now), which I wanted to
share and get feedback on.  Has anyone done something similiar to
this?  Here it is:

    //Class specifically for calling stored procedures.
    //
    //Uses SqlParameter class to allow for less network overhead
    //
    //Based off concepts in these articles:
    //http://www.sommarskog.se/arrays-in-sql-2005.html
    //http://www.davidhayden.com/blog/dave/archive/2006/11/01/
SqlCommandBuilderDeriveParameters.aspx
    //Note that SQL 2008 allows table-valued parameters.
    public class Sp
    {
        public SqlConnection conn;
        public DataSet ds = null;
        private int _FixedStringSize = 0;

        public Sp()
        {
            conn = new SqlConnection( ConfigurationManager.AppSettings
["connectDB"] );
        }

        public void exec( string sp, params object[] args )
        {
            SqlCommand cmd = _run( sp, args );
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public int num( string sp, params object[] args )
        {
            SqlCommand cmd = _run( sp, args );
            int n = (int)cmd.ExecuteScalar();
            conn.Close();
            return n;
        }

        public object scalar( string sp, params object[] args )
        {
            SqlCommand cmd = _run( sp, args );
            object o = cmd.ExecuteScalar();
            conn.Close();
            return o;
        }

        public string str( string sp, params object[] args )
        {
            SqlCommand cmd = _run( sp, args );
            string s = (string)cmd.ExecuteScalar();
            conn.Close();
            return s;
        }

        public DataTable tbl( string sp, params object[] args )
        {
            SqlCommand cmd = _run( sp, args );
            ds = new DataSet();
            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            sqlAdapter.SelectCommand = cmd;
            sqlAdapter.SelectCommand.CommandTimeout = 180;
            sqlAdapter.Fill( ds );
            conn.Close();
            return ( ds.Tables.Count > 0 ) ? ds.Tables[0] : null;
        }

        private SqlCommand _run( string sp, params object[] args )
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            conn.Open();
            SqlCommandBuilder.DeriveParameters( cmd );
            int idx = 0;
            foreach( SqlParameter p in cmd.Parameters )
            {
                if( p.Direction == ParameterDirection.Input ||
p.Direction == ParameterDirection.InputOutput )
                {
                    switch( args[idx].GetType().Name )
                    {
                        case "String":
                            p.Value = (string)args[idx];
                            break;
                        case "Int32":
                            p.Value = (int)args[idx];
                            break;

                        case "String[]":
                            if( _FixedStringSize > 0 )
                            {
                                //Use SELECT * FROM fixstring_single
(@str,_FixedStringSize) to get into table (fastest)
                                StringBuilder sb = new StringBuilder
();
                                string[] sArr = (string[])args[idx];
                                for( int i = 0; i < sArr.Length; i++ )
                                {
                                    sb.Append( sArr[i] );
                                    for( int j = 0; j <
_FixedStringSize - sArr[i].Length; j++ ) sb.Append( " " );
                                }
                                p.Value = sb.ToString();
                            }
                            else
                            {
                                //Use SELECT * FROM chunk_split_me
(@str) to get into table (fastest without using CLR or fixed length
strings)
                                p.Value = String.Join( ",", (string[])
args[idx] );
                            }
                            break;

                        case "UInt32[]":
                            //Use SELECT * FROM fixbinary_single(@bin)
to get into table; parameter must be varbinary(max)
                            p.Value = UIntsToBytes( (uint[])args
[idx] );
                            break;
                    }
                    idx++;
                }
            }
            return cmd;
        }

        public int FixedStringSize
        {
            get { return _FixedStringSize; }
            set { _FixedStringSize = value; }
        }

        byte[] UIntsToBytes( uint[] uints )
        {
            int ifrom = uints.GetLowerBound( 0 );
            int ito = uints.GetUpperBound( 0 );
            int l = ( ito - ifrom + 1 ) << 2; //*8 for ulong
            byte[] ret = new byte[l];
            int retind = 0;
            for( int i = ifrom; i <= ito; i++ )
            {
                uint v = uints[i];
                ret[retind++] = (byte)( v >> 24 );
                ret[retind++] = (byte)( v >> 16 );
                ret[retind++] = (byte)( v >> 8 );
                ret[retind++] = (byte)v;
            }
            return ret;
        }
    }