Execute SQL Server Store Procedure C# ADO.Net Wrapper Code Generator

namespace Microshaoft
{
    using System;
    using System.IO;
    using System.Data;
    using System.Data.SqlClient;
    using System.CodeDom;
    using System.CodeDom.Compiler;
    using Microsoft.CSharp;
    using Microshaoft.Data;
    public class Class1
    {
        [STAThread]
        static void Main(string[] args)
        {
            string spName = "sp_test";
            //spName = args[0];
            string _ConnectionString = @"application name=test;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=VNet3Center;Data Source=.\sqlexpress";
            SqlConnection sc = new SqlConnection(_ConnectionString);
            string s = GenerateCode(spName, true, sc, "Microshaoft", "Class1");
            //string s = "asdsad";
            System.Windows.Forms.Clipboard.SetDataObject(s);
            Console.WriteLine(s);
        }
        public static string GenerateCode
            (
                string spName
                , bool includeReturnValueParameter
                , SqlConnection connection
                , string nameSpace
                , string className
            )
        {
            SqlParameter[] spa = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
            int length = spa.Length;
            CSharpCodeProvider provider = new CSharpCodeProvider();
            //ICodeGenerator generator = provider.CreateGenerator();
            CodeCompileUnit ccu = new CodeCompileUnit();
            CodeNamespace cn = new CodeNamespace(nameSpace); //添加名稱空間
            cn.Imports.Add(new CodeNamespaceImport("System"));
            cn.Imports.Add(new CodeNamespaceImport("System.Data"));
            cn.Imports.Add(new CodeNamespaceImport("System.Data.SqlClient"));
            //cn.Imports.Add(new CodeNamespaceImport("Microshaoft.Data"));
            CodeTypeDeclaration ctd = new CodeTypeDeclaration(className);
            System.CodeDom.CodeMemberField cmf = new CodeMemberField(new CodeTypeReference(typeof(string)),"_ConnectionString");
            cmf.Attributes = MemberAttributes.Static | MemberAttributes.Public;
            cmf.InitExpression =  new CodeSnippetExpression("@\"" + connection.ConnectionString + "\"" );//new CodePrimitiveExpression(connection.ConnectionString);
            ctd.Members.Add(cmf);
            //int rows = spa.Length;
            CodeMemberMethod cmm = new CodeMemberMethod();
            CodeSnippetStatement css = null;
            css = new CodeSnippetStatement("SqlConnection connection = new SqlConnection(_ConnectionString);");
            cmm.Statements.Add(css);
            //css = new CodeSnippetStatement("SqlParameter[] parameters = new SqlParameter[" + (length+1).ToString() + "];");
            //cmm.Statements.Add(css);
            css = new CodeSnippetStatement(string.Format("SqlCommand command = new SqlCommand(\"{0}\", connection);", spName));
            cmm.Statements.Add(css);
            css = new CodeSnippetStatement("command.CommandType = CommandType.StoredProcedure;");
            cmm.Statements.Add(css);
            cmm.ReturnType = new CodeTypeReference(typeof(int));
            cmm.Name = "ExecProc_" + spName;
            cmm.Attributes = MemberAttributes.Public| MemberAttributes.Final | MemberAttributes.Static;
            int i = 1;
            CodeStatementCollection csc = new CodeStatementCollection();
            foreach (SqlParameter parameter in spa)
            {
                string paramName = "p_" + parameter.ParameterName.Substring(1);
                string paramTypeName = GetTypeName(parameter.SqlDbType);
                CodeTypeReference paramType = new CodeTypeReference(paramTypeName);
                //paramType.ToString();
                CodeParameterDeclarationExpression cpde = new CodeParameterDeclarationExpression(paramType, paramName);
                //css = new CodeSnippetStatement("parameters[" + i.ToString() + "] = new SqlParameter(\"" + parameter.ParameterName + "\", SqlDbType." + parameter.SqlDbType + ");");
                //css = new CodeSnippetStatement("SqlParameter parameter" + i.ToString() + " = new SqlParameter(\"" + parameter.ParameterName + "\", SqlDbType." + parameter.SqlDbType + ");");
                //cmm.Statements.Add(css);
                if (paramTypeName == "System.String")
                {
                    css = new CodeSnippetStatement(string.Format("SqlParameter parameter" + i.ToString() + " = command.Parameters.Add(\"{0}\", SqlDbType.{1}, {2});", parameter.ParameterName, parameter.SqlDbType, parameter.Size));
                    cmm.Statements.Add(css);
                }
                else if (paramTypeName == "System.String")
                {
                }
                else
                {
                    css = new CodeSnippetStatement(string.Format("SqlParameter parameter" + i.ToString() + " = command.Parameters.Add(\"{0}\", SqlDbType.{1});", parameter.ParameterName, parameter.SqlDbType));
                    cmm.Statements.Add(css);
                }
                if(parameter.Direction != ParameterDirection.Input)
                {
                    cpde.Direction = FieldDirection.Out;
                    css = new CodeSnippetStatement("parameter" + i.ToString() + ".Direction = ParameterDirection.Output;");
                    cmm.Statements.Add(css);
                    css = new CodeSnippetStatement(paramName + " = null;");
                    csc.Add(css);
                    css = new CodeSnippetStatement("if (parameter" + i.ToString() + ".Value != DBNull.Value)");
                    csc.Add(css);
                    css = new CodeSnippetStatement("{");
                    csc.Add(css);
                    CodeSnippetExpression cse = new CodeSnippetExpression(paramName);
                    CodeCastExpression cce = new CodeCastExpression(paramTypeName,new CodeSnippetExpression("parameter" + i.ToString() + ".Value") );
                    CodeAssignStatement cas = new CodeAssignStatement(cse,cce);
                    csc.Add(cas);
                    css = new CodeSnippetStatement("}");
                    csc.Add(css);
                }
                else
                {
                    string ss = "";
                    if (paramTypeName == "System.String")
                    {
                        ss = string.Format("parameter{0}.Value = ({1} != null ? (object) {1} : DBNull.Value);", i, paramName);
                    }
                    else
                    {
                        ss = string.Format("parameter{0}.Value = {1};", i, paramName);
                    }
                    css = new CodeSnippetStatement(ss);
                    cmm.Statements.Add(css);
                }
                cmm.Parameters.Add(cpde);
                i++;
            }
//            css = new CodeSnippetStatement("SqlParameter parameterReturn = new SqlParameter(\"@RETURN_VALUE\", SqlDbType.Int);");
            css = new CodeSnippetStatement("SqlParameter parameterReturn = command.Parameters.Add(\"@RETURN_VALUE\", SqlDbType.Int);");
            cmm.Statements.Add(css); 
            css = new CodeSnippetStatement("parameterReturn.Direction = ParameterDirection.ReturnValue;");
            cmm.Statements.Add(css); 
            css = new CodeSnippetStatement("connection.Open();");
            cmm.Statements.Add(css);
            css = new CodeSnippetStatement("//SqlDataAdapter sda = new SqlDataAdapter(command);");
            cmm.Statements.Add(css);
            css = new CodeSnippetStatement("//DataSet ds = new DataSet();");
            cmm.Statements.Add(css);
            css = new CodeSnippetStatement("//sda.Fill(ds);");
            cmm.Statements.Add(css);
            css = new CodeSnippetStatement("command.ExecuteNonQuery();");
            cmm.Statements.Add(css);
            css = new CodeSnippetStatement("connection.Close();");
            cmm.Statements.Add(css);
            for (int j =0 ;j < csc.Count; j++)
            {
                cmm.Statements.Add(csc[j]);
            }
            css = new CodeSnippetStatement("//return ds.Tables[0];");
            cmm.Statements.Add(css);
            if (includeReturnValueParameter)
            {
                //CodeSnippetExpression cse = new CodeSnippetExpression("p_RETURN_VALUE");
                CodeCastExpression cce = new CodeCastExpression(typeof(int),new CodeSnippetExpression("parameterReturn.Value"));
                //CodeAssignStatement cas = new CodeAssignStatement(cse,cce);
                //cmm.Statements.Add(cas);
                cmm.Statements.Add(new CodeMethodReturnStatement(cce));
            }
            ctd.Members.Add(cmm);
            cmm = new CodeMemberMethod();
            cmm.Name = "Main";
            cmm.Attributes = MemberAttributes.Public| MemberAttributes.Final | MemberAttributes.Static;
            ctd.Members.Add(cmm);
            cn.Types.Add(ctd);
            ccu.Namespaces.Add(cn);
            using(StreamWriter sw = new StreamWriter(@"test.cs",false)  )
            {
                provider.GenerateCodeFromCompileUnit(ccu,sw,new CodeGeneratorOptions());
                sw.Close();
            }
            string s = null;
            using (StreamReader sr = new StreamReader(@"test.cs"))
            {
                s = sr.ReadToEnd();
                sr.Close();
            }
            return s;
        }
        public static string GenerateCodeWithSqlHelper
            (
                string spName
                , bool includeReturnValueParameter
                , SqlConnection connection
                , string nameSpace
                , string className
            )
        {
            SqlParameter[] spa = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
            int length = spa.Length;
            CSharpCodeProvider provider = new CSharpCodeProvider();
            //ICodeGenerator generator = provider.CreateGenerator();
            CodeCompileUnit ccu = new CodeCompileUnit();
            CodeNamespace cn = new CodeNamespace(nameSpace); //添加名稱空間
            cn.Imports.Add(new CodeNamespaceImport("System"));
            cn.Imports.Add(new CodeNamespaceImport("System.Data"));
            cn.Imports.Add(new CodeNamespaceImport("System.Data.SqlClient"));
            cn.Imports.Add(new CodeNamespaceImport("Microshaoft.Data"));
            CodeTypeDeclaration ctd = new CodeTypeDeclaration(className);
            System.CodeDom.CodeMemberField cmf = new CodeMemberField(new CodeTypeReference(typeof(string)),"_ConnectionString");
            cmf.Attributes = MemberAttributes.Static | MemberAttributes.Public;
            cmf.InitExpression =  new CodeSnippetExpression("@\"" + connection.ConnectionString + "\"" );//new CodePrimitiveExpression(connection.ConnectionString);
            ctd.Members.Add(cmf);
            //int rows = spa.Length;
            CodeMemberMethod cmm = new CodeMemberMethod();
            CodeSnippetStatement css = null;
            css = new CodeSnippetStatement("SqlParameter[] parameters = new SqlParameter[" + (length+1).ToString() + "];");
            cmm.Statements.Add(css);
            cmm.ReturnType = new CodeTypeReference(typeof(int));
            cmm.Name = "ExecProc_" + spName;
            cmm.Attributes = MemberAttributes.Public| MemberAttributes.Final | MemberAttributes.Static;
            int i = 0;
            CodeStatementCollection csc = new CodeStatementCollection();
            foreach(SqlParameter parameter in spa)
            {
                string paramName = "p_" + parameter.ParameterName.Substring(1);
                string paramTypeName = GetTypeName(parameter.SqlDbType);
                CodeTypeReference paramType = new CodeTypeReference(paramTypeName);
                paramType.ToString();
                CodeParameterDeclarationExpression cpde = new CodeParameterDeclarationExpression(paramType, paramName);
                css = new CodeSnippetStatement("parameters[" + i.ToString() + "] = new SqlParameter(\"" + parameter.ParameterName + "\", SqlDbType." + parameter.SqlDbType + ");");
                cmm.Statements.Add(css);
                if(parameter.Direction != ParameterDirection.Input)
                {
                    cpde.Direction = FieldDirection.Out;
                    if (paramTypeName == "System.String")
                    {
                        css = new CodeSnippetStatement("parameters[" + i.ToString() + "].Size = " + parameter.Size + ";");
                        cmm.Statements.Add(css);
                    }
                    css = new CodeSnippetStatement("parameters[" + i.ToString() + "].Direction = ParameterDirection.Output;");
                    cmm.Statements.Add(css);
                    css = new CodeSnippetStatement(paramName + " = null;");
                    csc.Add(css);
                    css = new CodeSnippetStatement("if (parameters[" + i.ToString() + "].Value != System.DBNull.Value)");
                    csc.Add(css);
                    css = new CodeSnippetStatement("{");
                    csc.Add(css);    
                    CodeSnippetExpression cse = new CodeSnippetExpression(paramName);
                    CodeCastExpression cce = new CodeCastExpression(paramTypeName,new CodeSnippetExpression("parameters[" + i.ToString() + "].Value") );
                    CodeAssignStatement cas = new CodeAssignStatement(cse,cce);
                    csc.Add(cas);
                    css = new CodeSnippetStatement("}");
                    csc.Add(css);    
                }
                else
                {
                    css = new CodeSnippetStatement("parameters[" + i.ToString() + "].Value = " + paramName + ";");
                    cmm.Statements.Add(css);
                }
                cmm.Parameters.Add(cpde);
                i++;
            }
            css = new CodeSnippetStatement("parameters[" + length.ToString() + "] = new SqlParameter(\"@RETURN_VALUE\", SqlDbType.Int);");
            cmm.Statements.Add(css); 
            css = new CodeSnippetStatement("parameters[" + length.ToString() + "].Direction = ParameterDirection.ReturnValue;");
            cmm.Statements.Add(css); 
            css = new CodeSnippetStatement("SqlConnection connection = new SqlConnection(_ConnectionString);");
            cmm.Statements.Add(css);
            css = new CodeSnippetStatement("SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, \""+ spName+"\", parameters);");
            cmm.Statements.Add(css);
            for (int j =0 ;j<csc.Count; j++)
            {
                cmm.Statements.Add(csc[j]);
            }
            if (includeReturnValueParameter)
            {
                //CodeSnippetExpression cse = new CodeSnippetExpression("p_RETURN_VALUE");
                CodeCastExpression cce = new CodeCastExpression(typeof(int),new CodeSnippetExpression("parameters[" + i.ToString() + "].Value") );
                //CodeAssignStatement cas = new CodeAssignStatement(cse,cce);
                //cmm.Statements.Add(cas);
                cmm.Statements.Add( new CodeMethodReturnStatement( cce));
            }
            ctd.Members.Add(cmm);
            cmm = new CodeMemberMethod();
            cmm.