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;
}
}
}