SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.CodeDom.Compiler;
using System.CodeDom;

namespace DBHelper
{
   /// <summary>
   /// Summary description for Class1.
   /// </summary>
   public class SP
   {
      SqlCommand getSP;
      SqlConnection conn;
      CodeDomProvider cp;
      ICodeGenerator codegen;
      ICodeCompiler compiler;

      public SP(SqlConnection conn, CodeDomProvider cp)
      {
         this.conn = conn;
         getSP = new SqlCommand();
         getSP.Connection = conn;
         getSP.CommandType = CommandType.Text;
         this.cp = cp;
         compiler = cp.CreateCompiler();
         codegen = cp.CreateGenerator();
         allsp = new SPCollection();
         GetSPs();
      }

      SPCollection allsp;

      public SPCollection Names 
      {
         get {return allsp;}
      }
      
      private void GetSPs()
      {
         allsp.Clear();
         getSP.CommandText = String.Format("SELECT DISTINCT SPECIFIC_NAME FROM {0}.INFORMATION_SCHEMA.PARAMETERS", 
            conn.Database);
         conn.Open();
         SqlDataReader reader = getSP.ExecuteReader(CommandBehavior.CloseConnection);
         while (reader.Read())
         {
            allsp.Add(reader.GetString(0));
         }
         reader.Close();
      }

      public CompilerResults Compile(string[] storedprocs, string ns, string classname, string asmname)
      {
         allsp.Clear();
         allsp.AddRange(storedprocs);
         return Compile(ns, classname, asmname);
      }

      public CompilerResults Compile(string ns, string classname, string asmname)
      {
         CompilerParameters param = new CompilerParameters();
         param.OutputAssembly = asmname;
         param.WarningLevel = 4;
         param.ReferencedAssemblies.Add("System.dll");
         param.ReferencedAssemblies.Add("System.Data.dll");
         param.CompilerOptions = "/t:library";
         CodeCompileUnit cunit = new CodeCompileUnit();
         cunit.Namespaces.Add(Generate(ns, classname));
         return compiler.CompileAssemblyFromDom(param, cunit);
      }

      public void CreateFile(string[] storedprocs, string ns, string classname, string filename)
      {
         allsp.Clear();
         allsp.AddRange(storedprocs);
         CreateFile(ns, classname, filename);
      }

      public void CreateFile(string ns, string classname, string filename)
      {
         StreamWriter writer = File.CreateText(filename);
         CodeGeneratorOptions codegenopt = new CodeGeneratorOptions();
         codegenopt.BlankLinesBetweenMembers = true;
         codegenopt.IndentString = "   ";
         codegenopt.BracingStyle = "C";
         CodeNamespace cns = Generate(ns, classname);
         codegen.GenerateCodeFromNamespace(cns, writer, codegenopt);
         writer.Close();
       }

      private CodeNamespace Generate(string ns, string classname)
      {
         CodeNamespace cns = new CodeNamespace(ns);
         cns.Imports.Add( new CodeNamespaceImport("System"));
         cns.Imports.Add( new CodeNamespaceImport("System.Data"));
         cns.Imports.Add( new CodeNamespaceImport("System.Data.SqlClient"));

         CodeTypeDeclaration cclass = new CodeTypeDeclaration(classname);
         cclass.IsClass = true;
         foreach(string sp in allsp)
         {

            getSP.CommandText = String.Format(@"SELECT * FROM {1}.INFORMATION_SCHEMA.PARAMETERS WHERE (SPECIFIC_NAME = '{0}') AND (PARAMETER_MODE = 'IN')", sp, conn.Database);
            conn.Open();
            SqlDataReader reader = getSP.ExecuteReader(CommandBehavior.CloseConnection);

            SqlCommand com = new SqlCommand();

            while (reader.Read())
            {
               com.Parameters.Add(
               reader.GetString(reader.GetOrdinal("PARAMETER_NAME")),
               (SqlDbType) Enum.Parse(typeof(SqlDbType), reader.GetString(reader.GetOrdinal("DATA_TYPE")), true)
                  );
            }
            reader.Close();
            //generate the code
            CodeMemberMethod method = new CodeMemberMethod();
            method.Name = sp.Replace(" ","_");
            method.ReturnType = new CodeTypeReference("System.Data.DataTable"); 
            method.Attributes = MemberAttributes.Public | MemberAttributes.Static;
            CodeParameterDeclarationExpression connpar = new CodeParameterDeclarationExpression(typeof(SqlConnection), "connection");
            method.Parameters.Add( connpar);
            method.Parameters.Add( new CodeParameterDeclarationExpression(typeof(DataTable), "table"));
            method.Statements.Add( new CodeVariableDeclarationStatement(
               typeof(SqlCommand), "cmd", new CodeObjectCreateExpression(typeof(SqlCommand))));
            
            CodeSnippetExpression cmdexp = new CodeSnippetExpression("cmd");
            method.Statements.Add( new CodeAssignStatement( 
               new CodePropertyReferenceExpression( cmdexp, "Connection"),
               new CodeVariableReferenceExpression("connection")
               ));
            method.Statements.Add( new CodeSnippetExpression("cmd.CommandText = \"" + sp + "\""));
            method.Statements.Add( new CodeSnippetExpression("cmd.CommandType = CommandType.StoredProcedure"));

            foreach (SqlParameter par in com.Parameters)
            {
               method.Parameters.Add(
                  new CodeParameterDeclarationExpression(
                  TypeMapping.Mapping[par.SqlDbType], par.ParameterName.Replace("@","") + "_param"));
               method.Statements.Add( new CodeSnippetExpression(
                  String.Format("cmd.Parameters.Add(\"{0}\", SqlDbType.{1}).Value = {2}", 
                  par.ParameterName, par.SqlDbType, par.ParameterName.Replace("@","") + "_param")
                  ));
            }
            method.Statements.Add( new CodeSnippetExpression("connection.Open()"));
            method.Statements.Add( new CodeVariableDeclarationStatement(typeof(SqlDataReader), "reader", 
               new CodeSnippetExpression("cmd.ExecuteReader(CommandBehavior.CloseConnection)")
               ));
            CodeConditionStatement tabletest = new CodeConditionStatement( new CodeBinaryOperatorExpression(
               new CodeSnippetExpression("table"), CodeBinaryOperatorType.IdentityEquality,
               new CodePrimitiveExpression(null)
               ));
            
            tabletest.TrueStatements.Add( new CodeAssignStatement(
               new CodeVariableReferenceExpression("table"),
               new CodeObjectCreateExpression( typeof(DataTable), new CodeSnippetExpression("\"" + sp + "\"")
               )));
            CodeIterationStatement forfield = new CodeIterationStatement(
               new CodeVariableDeclarationStatement(typeof(int), "i", new CodePrimitiveExpression(0)),
               new CodeBinaryOperatorExpression( 
                  new CodeVariableReferenceExpression("i"),
                  CodeBinaryOperatorType.LessThan,
                  new CodePropertyReferenceExpression(
                     new CodeVariableReferenceExpression("reader"),"FieldCount")),
               new CodeSnippetStatement("i = i + 1"));
            forfield.Statements.Add( new CodeVariableDeclarationStatement(
               typeof(Type), "type", new CodeSnippetExpression("reader.GetFieldType(i)")));
            forfield.Statements.Add( new CodeVariableDeclarationStatement(
               typeof(string), "name", new CodeSnippetExpression("reader.GetName(i)")));
            forfield.Statements.Add( new CodeSnippetExpression("table.Columns.Add(name, type)"));
            
            tabletest.TrueStatements.Add(forfield);

            method.Statements.Add(tabletest);
            
            CodeIterationStatement whileread = new CodeIterationStatement(
               new CodeSnippetStatement(""), 
               new CodeSnippetExpression("reader.Read()"),
               new CodeSnippetStatement(""));
            whileread.Statements.Add( new CodeVariableDeclarationStatement(typeof(DataRow), "row",
               new CodeSnippetExpression("table.NewRow()")));
            CodeIterationStatement forfield2 = new CodeIterationStatement( 
               new CodeVariableDeclarationStatement(typeof(int), "j", new CodePrimitiveExpression(0)),//forfield.InitStatement,
               new CodeBinaryOperatorExpression( 
                  new CodeVariableReferenceExpression("j"),
                  CodeBinaryOperatorType.LessThan,
                  new CodePropertyReferenceExpression(
                     new CodeVariableReferenceExpression("reader"),"FieldCount")),
               new CodeSnippetStatement("j = j + 1"),
               new CodeAssignStatement(
               new CodeIndexerExpression(new CodeSnippetExpression("row"), new CodeSnippetExpression("j")),
               new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("reader"), "GetValue",
               new CodeSnippetExpression("j"))
               )
               );
            whileread.Statements.Add( forfield2);
            whileread.Statements.Add( new CodeSnippetExpression("table.Rows.Add(row)"));
            method.Statements.Add(whileread);
                  
            method.Statements.Add( new CodeSnippetExpression("reader.Close()"));
            method.Statements.Add( new CodeMethodReturnStatement(
               new CodeSnippetExpression("table")));
            cclass.Members.Add(method);
         }
         cns.Types.Add(cclass);
         return cns;
      }
   }
}