SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

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

namespace DBHelper
{
#if(IDEA)
   
   public class Parameter 
   {
      SqlParameter par;

      public Parameter(SqlParameter par)
      {
         this.par = par;
      }

      internal SqlParameter Parameter
      {
         get { return par;}
      }

      [Bindable(true)]
      public string Name 
      {
         get {return par.ParameterName;}
         //set {par.ParameterName = value;}
      }

      [Bindable(true)]
      public Type Type 
      {
         get { return TypeMapping.Mapping[par.SqlDbType];}
      }

      [Bindable(true)]
      public bool IsNullable 
      {
         get {return par.IsNullable;}
         set {par.IsNullable = value;}
      }

      [Bindable(true)]
      public object Value 
      {
         get {return par.Value;}
         set {par.Value = value;}
      }
   }
#endif

   [DefaultProperty("Name")]
   public class StoredProc 
   {
      string name;
      bool create = true;
      bool createstrong = false;
      SqlCommand command;

      public StoredProc(string name)
      {
         this.name = name;
      }

//      public SqlCommand Command 
//      {
//         get {return command;}
//         set {command = value;}
//      }

      public void SetCommand(SqlCommand com)
      {
         this.command = com;
      }

      [Bindable(true)]
      public SqlParameterCollection Parameters 
      {
         get 
         {
            if (command != null)
               return command.Parameters;
            else return null;
         }
      }

      [Bindable(true)]
      public string Name 
      {
         get {return name;}
      }

      [Bindable(true)]
      public bool Create 
      {
         get {return create;}
         set {create = value;}
      }

      [Bindable(true)]
      public bool CreateStrong 
      {
         get {return createstrong;}
         set {createstrong = value;}
      }

      public override string ToString()
      {
         return name;
      }
   }
   /// <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;
         Init(cp);
      }

      private void Init(CodeDomProvider cp)
      {
         this.cp = cp;
         compiler = cp.CreateCompiler();
         codegen = cp.CreateGenerator();
         allsp = new SPCollection();
         GetSPs();
         GetParameters();
      }

      SPCollection allsp;

      public CodeDomProvider Provider 
      {
         get { return cp;}
         set 
         {
            Init(value);
         }
      }
      
      [Bindable(true)]
      public SPCollection Names 
      {
         get {return allsp;}
         set {allsp = value;}
      }
      
      private void GetSPs()
      {
         allsp.Clear();
         getSP.CommandText = String.Format("SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE (ROUTINE_TYPE = 'PROCEDURE') ORDER BY ROUTINE_NAME");
         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.IncludeDebugInformation = true;
         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 void GetParameters()
      {
         foreach (StoredProc sp in allsp)
         {
            getSP.CommandText = String.Format(@"SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE (SPECIFIC_NAME = '{0}') ORDER BY ORDINAL_POSITION", sp);
 
            SqlCommand com = new SqlCommand();
            com.CommandText = sp.Name;
            com.CommandType = CommandType.StoredProcedure;

            conn.Open();
            SqlDataReader reader = getSP.ExecuteReader(CommandBehavior.CloseConnection);
            
            while (reader.Read())
            {
               SqlParameter par = new SqlParameter(reader.GetString(reader.GetOrdinal("PARAMETER_NAME")),
                  (SqlDbType) Enum.Parse(typeof(SqlDbType), reader.GetString(reader.GetOrdinal("DATA_TYPE")), true)
                  );
               switch (reader.GetString(reader.GetOrdinal("PARAMETER_MODE")))
               {
                  case "INOUT":
                     par.Direction = ParameterDirection.InputOutput;
                     break;
                  case "OUT":
                     //this seems to cause problems setting it to out only...???
                     par.Direction = ParameterDirection.InputOutput;
                     break;
                  default:
                     par.Direction = ParameterDirection.Input;
                     break;
               }
               //par.IsNullable = reader.GetBoolean(reader.GetOrdinal(
               com.Parameters.Add(par);
            }
            reader.Close();
            sp.SetCommand(com);
         }
      }

      public 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(StoredProc sp in allsp)
         {
            if (sp.Create)
            {
            
               //SqlCommand com = sp.GetCommand();
               //generate the code
            
               CodeMemberMethod method = new CodeMemberMethod();
               method.Name = sp.Name.Replace(" ","_");
               method.ReturnType = new CodeTypeReference("System.Int32"); 
               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 sp.Parameters)
               {
                  CodeParameterDeclarationExpression cpar = new CodeParameterDeclarationExpression(
                     TypeMapping.Mapping[par.SqlDbType], par.ParameterName.Replace("@","") + "_param");
                  if (par.Direction == ParameterDirection.InputOutput)
                     cpar.Direction = FieldDirection.Ref;
                  if (par.Direction == ParameterDirection.Output)
                     cpar.Direction = FieldDirection.Out;
                  method.Parameters.Add(cpar);

                  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.Columns.Count"), CodeBinaryOperatorType.ValueEquality,
                  new CodePrimitiveExpression(0)
                  ));
            
               tabletest.TrueStatements.Add( new CodeAssignStatement(
                  new CodeVariableReferenceExpression("table.TableName"),
                  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);
               //tabletest.TrueStatements.Add( new CodeSnippetExpression("reader.Close()"));
               //tabletest.TrueStatements.Add( new CodeMethodReturnStatement( 
               //   new CodeVariableReferenceExpression("table.Columns.Count")
               //   ));

               method.Statements.Add(tabletest);

               method.Statements.Add( new CodeMethodInvokeExpression( 
                  new CodeVariableReferenceExpression("table"), "Clear"));
               method.Statements.Add( new CodeVariableDeclarationStatement(typeof(int), "result", 
                  new CodePrimitiveExpression(0)));

               CodeIterationStatement whileread = new CodeIterationStatement(
                  new CodeSnippetStatement(""), 
                  new CodeSnippetExpression("reader.Read()"),
                  new CodeSnippetStatement("result = result + 1"));
               whileread.Statements.Add( new CodeVariableDeclarationStatement(typeof(DataRow), "row",
                  new CodeSnippetExpression("table.NewRow()")));
               whileread.Statements.Add( new CodeVariableDeclarationStatement(typeof(object[]), "rowdata",
                  new CodeArrayCreateExpression(typeof(object), new CodePropertyReferenceExpression(
                  new CodeVariableReferenceExpression("reader"), "FieldCount"))));
               whileread.Statements.Add( new CodeMethodInvokeExpression( 
                  new CodeVariableReferenceExpression("reader"), "GetValues", 
                  new CodeVariableReferenceExpression("rowdata")));
               whileread.Statements.Add( new CodeAssignStatement( 
                  new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("row"),"ItemArray"),
                  new CodeVariableReferenceExpression("rowdata")));
               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("result")));
               cclass.Members.Add(method);
            }
         }
         cns.Types.Add(cclass);
         return cns;
         
      }
   }
}