using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.CSharp;
using Microsoft.VisualBasic;
using System.CodeDom.Compiler;
using System.CodeDom;
namespace DBHelper
{
public class TypeMapping
{
static SqlDbTypeToTypeAssociation map;
public static SqlDbTypeToTypeAssociation Mapping
{
get {return map;}
}
static TypeMapping()
{
map = new SqlDbTypeToTypeAssociation();
map.Add(SqlDbType.BigInt, typeof(Int64));
map.Add(SqlDbType.Binary, typeof(Array));
map.Add(SqlDbType.Bit, typeof(Boolean ));
map.Add(SqlDbType.Char, typeof( String ));
map.Add(SqlDbType.DateTime, typeof( DateTime ));
map.Add(SqlDbType.Decimal, typeof( Decimal ));
map.Add(SqlDbType.Float, typeof( Double ));
map.Add(SqlDbType.Image, typeof( Array));
map.Add(SqlDbType.Int, typeof( Int32 ));
map.Add(SqlDbType.Money, typeof( Decimal));
map.Add(SqlDbType.NChar, typeof( String ));
map.Add(SqlDbType.NText, typeof( String ));
map.Add(SqlDbType.NVarChar, typeof( String ));
map.Add(SqlDbType.Real, typeof( Single ));
map.Add(SqlDbType.SmallDateTime, typeof( DateTime ));
map.Add(SqlDbType.SmallInt, typeof( Int16 ));
map.Add(SqlDbType.SmallMoney, typeof( Decimal));
map.Add(SqlDbType.Text, typeof( String ));
map.Add(SqlDbType.Timestamp, typeof( DateTime ));
map.Add(SqlDbType.TinyInt, typeof( Byte ));
map.Add(SqlDbType.UniqueIdentifier, typeof( Guid ));
map.Add(SqlDbType.VarBinary, typeof( Array ));
map.Add(SqlDbType.VarChar, typeof( String ));
map.Add(SqlDbType.Variant, typeof( Object ));
}
}
/// <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";
codegen.GenerateCodeFromNamespace(Generate(ns, classname), 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;
method.ReturnType = new CodeTypeReference("System.Int32");
method.Attributes = MemberAttributes.Public | MemberAttributes.Static;
CodeParameterDeclarationExpression connpar = new CodeParameterDeclarationExpression(typeof(SqlConnection), "connection");
//is this necesary? ask nick parker, doesnt seem like it
//if (cp is Microsoft.VisualBasic.VBCodeProvider)
// connpar.Direction = FieldDirection.Ref;
method.Parameters.Add( connpar);
method.Statements.Add( new CodeVariableDeclarationStatement(
typeof(SqlCommand), "cmd", new CodeObjectCreateExpression(typeof(SqlCommand))));
method.Statements.Add( new CodeSnippetExpression("cmd.Connection = 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(Int32), "result",
new CodeSnippetExpression("cmd.ExecuteNonQuery()")));
method.Statements.Add( new CodeSnippetExpression("cmd.ExecuteNonQuery()"));
method.Statements.Add( new CodeSnippetExpression("connection.Close()"));
method.Statements.Add( new CodeMethodReturnStatement(
new CodeSnippetExpression("result")));
cclass.Members.Add(method);
}
cns.Types.Add(cclass);
return cns;
}
}
/// <summary>
/// A collection of elements of type string
/// </summary>
public class SPCollection: System.Collections.CollectionBase
{
/// <summary>
/// Initializes a new empty instance of the SPCollection class.
/// </summary>
public SPCollection()
{
// empty
}
/// <summary>
/// Initializes a new instance of the SPCollection class, containing elements
/// copied from an array.
/// </summary>
/// <param name="items">
/// The array whose elements are to be added to the new SPCollection.
/// </param>
public SPCollection(string[] items)
{
this.AddRange(items);
}
/// <summary>
/// Initializes a new instance of the SPCollection class, containing elements
/// copied from another instance of SPCollection
/// </summary>
/// <param name="items">
/// The SPCollection whose elements are to be added to the new SPCollection.
/// </param>
public SPCollection(SPCollection items)
{
this.AddRange(items);
}
/// <summary>
/// Adds the elements of an array to the end of this SPCollection.
/// </summary>
/// <param name="items">
/// The array whose elements are to be added to the end of this SPCollection.
/// </param>
public virtual void AddRange(string[] items)
{
foreach (string item in items)
{
this.List.Add(item);
}
}
/// <summary>
/// Adds the elements of another SPCollection to the end of this SPCollection.
/// </summary>
/// <param name="items">
/// The SPCollection whose elements are to be added to the end of this SPCollection.
/// </param>
public virtual void AddRange(SPCollection items)
{
foreach (string item in items)
{
this.List.Add(item);
}
}
/// <summary>
/// Adds an instance of type string to the end of this SPCollection.
/// </summary>
/// <param name="value">
/// The string to be added to the end of this SPCollection.
/// </param>
public virtual void Add(string value)
{
this.List.Add(value);
}
/// <summary>
/// Determines whether a specfic string value is in this SPCollection.
/// </summary>
/// <param name="value">
/// The string value to locate in this SPCollection.
/// </param>
/// <returns>
/// true if value is found in this SPCollection;
/// false otherwise.
/// </returns>
public virtual bool Contains(string value)
{
return this.List.Contains(value);
}
/// <summary>
/// Return the zero-based index of the first occurrence of a specific value
/// in this SPCollection
/// </summary>
/// <param name="value">
/// The string value to locate in the SPCollection.
/// </param>
/// <returns>
/// The zero-based index of the first occurrence of the _ELEMENT value if found;
/// -1 otherwise.
/// </returns>
public virtual int IndexOf(string value)
{
return this.List.IndexOf(value);
}
/// <summary>
/// Inserts an element into the SPCollection at the specified index
/// </summary>
/// <param name="index">
/// The index at which the string is to be inserted.
/// </param>
/// <param name="value">
/// The string to insert.
/// </param>
public virtual void Insert(int index, string value)
{
this.List.Insert(index, value);
}
/// <summary>
/// Gets or sets the string at the given index in this SPCollection.
/// </summary>
public virtual string this[int index]
{
get
{
return (string) this.List[index];
}
set
{
this.List[index] = value;
}
}
/// <summary>
/// Removes the first occurrence of a specific string from this SPCollection.
/// </summary>
/// <param name="value">
/// The string value to remove from this SPCollection.
/// </param>
public virtual void Remove(string value)
{
this.List.Remove(value);
}
/// <summary>
/// Type-specific enumeration class, used by SPCollection.GetEnumerator.
/// </summary>
public class Enumerator: System.Collections.IEnumerator
{
private System.Collections.IEnumerator wrapped;
public Enumerator(SPCollection collection)
{
this.wrapped = ((System.Collections.CollectionBase)collection).GetEnumerator();
}
public string Current
{
get
{
return (string) (this.wrapped.Current);
}
}
object System.Collections.IEnumerator.Current
{
get
{
return (string) (this.wrapped.Current);
}
}
public bool MoveNext()
{
return this.wrapped.MoveNext();
}
public void Reset()
{
this.wrapped.Reset();
}
}
/// <summary>
/// Returns an enumerator that can iterate through the elements of this SPCollection.
/// </summary>
/// <returns>
/// An object that implements System.Collections.IEnumerator.
/// </returns>
public new virtual SPCollection.Enumerator GetEnumerator()
{
return new SPCollection.Enumerator(this);
}
}
/// <summary>
/// A dictionary with keys of type SqlDbType and values of type Type
/// </summary>
public class SqlDbTypeToTypeAssociation: System.Collections.DictionaryBase
{
/// <summary>
/// Initializes a new empty instance of the SqlDbTypeToTypeAssociation class
/// </summary>
public SqlDbTypeToTypeAssociation()
{
// empty
}
/// <summary>
/// Gets or sets the Type associated with the given SqlDbType
/// </summary>
/// <param name="key">
/// The SqlDbType whose value to get or set.
/// </param>
public virtual Type this[SqlDbType key]
{
get
{
return (Type) this.Dictionary[key];
}
set
{
this.Dictionary[key] = value;
}
}
/// <summary>
/// Adds an element with the specified key and value to this SqlDbTypeToTypeAssociation.
/// </summary>
/// <param name="key">
/// The SqlDbType key of the element to add.
/// </param>
/// <param name="value">
/// The Type value of the element to add.
/// </param>
public virtual void Add(SqlDbType key, Type value)
{
this.Dictionary.Add(key, value);
}
/// <summary>
/// Determines whether this SqlDbTypeToTypeAssociation contains a specific key.
/// </summary>
/// <param name="key">
/// The SqlDbType key to locate in this SqlDbTypeToTypeAssociation.
/// </param>
/// <returns>
/// true if this SqlDbTypeToTypeAssociation contains an element with the specified key;
/// otherwise, false.
/// </returns>
public virtual bool Contains(SqlDbType key)
{
return this.Dictionary.Contains(key);
}
/// <summary>
/// Determines whether this SqlDbTypeToTypeAssociation contains a specific key.
/// </summary>
/// <param name="key">
/// The SqlDbType key to locate in this SqlDbTypeToTypeAssociation.
/// </param>
/// <returns>
/// true if this SqlDbTypeToTypeAssociation contains an element with the specified key;
/// otherwise, false.
/// </returns>
public virtual bool ContainsKey(SqlDbType key)
{
return this.Dictionary.Contains(key);
}
/// <summary>
/// Determines whether this SqlDbTypeToTypeAssociation contains a specific value.
/// </summary>
/// <param name="value">
/// The Type value to locate in this SqlDbTypeToTypeAssociation.
/// </param>
/// <returns>
/// true if this SqlDbTypeToTypeAssociation contains an element with the specified value;
/// otherwise, false.
/// </returns>
public virtual bool ContainsValue(Type value)
{
foreach (Type item in this.Dictionary.Values)
{
if (item == value)
return true;
}
return false;
}
/// <summary>
/// Removes the element with the specified key from this SqlDbTypeToTypeAssociation.
/// </summary>
/// <param name="key">
/// The SqlDbType key of the element to remove.
/// </param>
public virtual void Remove(SqlDbType key)
{
this.Dictionary.Remove(key);
}
/// <summary>
/// Gets a collection containing the keys in this SqlDbTypeToTypeAssociation.
/// </summary>
public virtual System.Collections.ICollection Keys
{
get
{
return this.Dictionary.Keys;
}
}
/// <summary>
/// Gets a collection containing the values in this SqlDbTypeToTypeAssociation.
/// </summary>
public virtual System.Collections.ICollection Values
{
get
{
return this.Dictionary.Values;
}
}
}
}