Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Utilities » Downloads
 
Add your own
alternative version

SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

, 7 Dec 2002
This a small tool that will generate static methods in a class that acts as wrapper for SQL stored procedures. It either outputs a source file or a compiled assembly. Also supports automatic DataSet generation.
dbhelper-1.0.zip
DBHelper
CVS
Base
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
SP
CVS
Base
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
SPTestApp
App.ico
CVS
Base
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.1.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.2.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.3.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.4.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.5.demo.zip
dbhelper-1.5.src.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.6.demo.zip
dbhelper-1.6.src.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Diff
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-16demo.zip
dbhelper-16src.zip
Baserev
Entries
Repository
Root
dbh.gif
Baserev
Entries
Repository
Root
DBHelper.csproj.user
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
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;
         
      }
   }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The BSD License

About the Author

leppie
Software Developer
South Africa South Africa
No Biography provided
Follow on   Twitter

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 8 Dec 2002
Article Copyright 2002 by leppie
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid