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(truenot)
public class Parameter
{
SqlParameter par;
public Parameter(SqlParameter par)
{
this.par = par;
}
internal SqlParameter SqlParameter
{
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 ParameterCollection Parameters
{
get { return new ParameterCollection(command.Parameters);}
}
*/
[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.Parameters.Add( new CodeParameterDeclarationExpression(typeof(bool), "filltable"));
method.Statements.Add( new CodeVariableDeclarationStatement(
typeof(SqlCommand), "cmd", new CodeObjectCreateExpression(typeof(SqlCommand))));
method.Statements.Add( new CodeVariableDeclarationStatement(typeof(int), "result",
new CodePrimitiveExpression(0)));
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(
/*(par.SqlParameter.IsNullable) ? typeof(object) : */
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;
//if
method.Parameters.Add(cpar);
method.Statements.Add(
//new CodeMethodInvokeExpression(
//new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("cmd"),
//"Parameters"), "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 CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("connection"),
"Open"));
CodeConditionStatement choice = new CodeConditionStatement(
new CodeBinaryOperatorExpression(
new CodeVariableReferenceExpression("table"),
CodeBinaryOperatorType.IdentityInequality,
new CodePrimitiveExpression(null)
));
choice.TrueStatements.Add( new CodeVariableDeclarationStatement(typeof(SqlDataReader), "reader",
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("cmd"),
"ExecuteReader")
));
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 CodeMethodInvokeExpression( new CodeVariableReferenceExpression("reader"),
"GetFieldType", new CodeVariableReferenceExpression("i"))));
forfield.Statements.Add( new CodeVariableDeclarationStatement(
typeof(string), "name",
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("reader"),
"GetName", new CodeVariableReferenceExpression("i"))));
forfield.Statements.Add( new CodeMethodInvokeExpression(
new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("table"),
"Columns"), "Add",
new CodeVariableReferenceExpression("name"),
new CodeVariableReferenceExpression("type")));
tabletest.TrueStatements.Add(forfield);
//tabletest.TrueStatements.Add( new CodeSnippetExpression("reader.Close()"));
//tabletest.TrueStatements.Add( new CodeMethodReturnStatement(
// new CodeVariableReferenceExpression("table.Columns.Count")
// ));
choice.TrueStatements.Add(tabletest);
choice.TrueStatements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("table"), "Clear"));
CodeIterationStatement whileread = new CodeIterationStatement(
//not sure how to handle this in VB and JS
new CodeSnippetStatement(""),
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("reader"),
"Read"),
//not sure how to handle this in VB and JS
new CodeSnippetStatement("result = result + 1"));
whileread.Statements.Add( new CodeVariableDeclarationStatement(typeof(DataRow), "row",
new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("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 CodeMethodInvokeExpression(
new CodePropertyReferenceExpression( new CodeVariableReferenceExpression("table"),
"Rows"), "Add",
new CodeVariableReferenceExpression("row")));
choice.TrueStatements.Add(whileread);
choice.TrueStatements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("reader"), "Close"));
choice.FalseStatements.Add( new CodeAssignStatement(
new CodeVariableReferenceExpression("result"),
new CodeMethodInvokeExpression( new CodeVariableReferenceExpression("cmd"),
"ExecuteNonQuery")
));
method.Statements.Add( choice);
method.Statements.Add( new CodeMethodInvokeExpression(
new CodeVariableReferenceExpression("connection"),
"Close"));
method.Statements.Add( new CodeMethodReturnStatement(
new CodeSnippetExpression("result")));
cclass.Members.Add(method);
}
}
cns.Types.Add(cclass);
return cns;
}
}
}