Click here to Skip to main content
Click here to Skip to main content

Application DataBlock Extender Code Generator using SQLDMO

, 4 Apr 2005
Rate this:
Please Sign up or sign in to vote.
Use this Windows application to generate complete Data Access Layer for your applications.

Sample Image - DataBlock_Extender.gif

Motivation

One common problem which arises when using SQL in Microsoft Application Data Block as well as in ADO and ADO.NET, is the absence of syntax checking. For example, if you pass a mistyped name of the stored procedure to the compiler - the error will be generated not at compile time but at run-time, thus making code developing more tiresome.

Introduction

This article will show how to auto generate Data Access Layer for your application using SQLDMO and Microsoft Application Data Block.

The SQLDMO object comes from the SQLDMO.dll that ships with SQL Server 2000. The DLL itself is a COM object and you must refer it from your .NET project as such.

The Data Access Block provides static methods located in the SqlHelper class that encapsulates the most common data access tasks performed with Microsoft SQL Server. It is very helpful but still does not eliminate hard coding of your stored procedures' names.

Using DataBlock :

DataSet oDs= SqlHelper.ExecuteDataset(ConnString,"GetClientInfo",ClientID);

Using DataBlock With Extender:

DataSet oDs= DataAccessLayer.GetClientInfo.ExecuteDataset(ConnString,ClientID);

This Windows application allows to generate complete Data Access Layer for your applications. Each stored procedure is represented as a separate class with static methods which are the same as static method of SQLHelper class of Data Block. When using Extended code generated by this tool, developer will have all stored procedures and their parameters in the Intellisense window.

Sample screenshot

Sample screenshot

Code Description

On the form load event, application reads from registry to get SQL Server information and all other Generator options to eliminate typing every time user wants to regenerate code. On form closing event, application saves information back to the registry.

private void frmMain_Load(object sender, System.EventArgs e)
{
    cboModifier.Text=Settings.Get("MethodMod");
    txtServer.Text = Settings.Get("Server");
    txtDatabase.Text = Settings.Get("Database");
    txtOutfile.Text = Settings.Get("Outfile");
    txtNamespaceRoot.Text = Settings.Get("NamespaceRoot");
    string s = Settings.Get("UseIntegratedSecurity");
    cbIntegratedSecurity.Checked = bool.Parse( s == "" ? "false" : s );
    txtUserid.Text = Settings.Get("Userid");
    txtPassword.Text = Settings.Get("Password");
    btnBrowse.Enabled=txtOutfile.Text.Trim().Length>0;
    rbFilterYes.Checked=Settings.Get("Filtering").ToLower()=="true";
    rbStartsWith.Checked=Settings.Get("FilterOption")=="StartsWith";
    txtFilter.Text=Settings.Get("FilterExpression");
    rbFilterNo.Checked=!rbFilterYes.Checked;
    rbContains.Checked=!rbStartsWith.Checked;
}
private void frmMain_Closing(object sender, System.ComponentModel.CancelEventArgs e)
{
    Settings.Set("MethodMod",cboModifier.Text);
    Settings.Set("Server", txtServer.Text);
    Settings.Set("Database", txtDatabase.Text);
    Settings.Set("Outfile", txtOutfile.Text);
    Settings.Set("NamespaceRoot", txtNamespaceRoot.Text);
    Settings.Set("Userid", txtUserid.Text);
    Settings.Set("Password", txtPassword.Text);
    Settings.Set("UseIntegratedSecurity", cbIntegratedSecurity.Checked.ToString());
    Settings.Set("Filtering",rbFilterYes.Checked.ToString());
    Settings.Set("FilterOption",rbStartsWith.Checked?"StartsWith":"Contains");
    Settings.Set("FilterExpression",txtFilter.Text.Trim());
}

Settings Class is just the helper class to work with registry. User can apply custom filter to generate classes for the stored procedures based on custom naming convention. After applying all settings and options user clicks Generate button to generate code.

private void btnGenerate_Click(object sender, System.EventArgs e)
{
  try
  {
    this.Cursor=Cursors.WaitCursor;
    Core.Generator objGenerator=new Core.Generator(); 

    objGenerator.Generate(txtServer.Text, txtDatabase.Text, txtUserid.Text, 
      txtPassword.Text, txtOutfile.Text, txtNamespaceRoot.Text,
      cboModifier.Text,rbFilterNo.Checked?
      Core.FilterOption.None:rbStartsWith.Checked? 
      Core.FilterOption.StartsWith:Core.FilterOption.Contains,
      txtFilter.Text.Trim() );
    System.Diagnostics.Process.Start(txtOutfile.Text);
  }
  catch (Exception ex)
  {
    MessageBox.Show(this,ex.Message,"Error",MessageBoxButtons.OK,
       MessageBoxIcon.Error);
  }
  finally
  {
    this.Cursor=Cursors.Default;
  }
}

Generator Class

SQLDMO used to connect to the database and define all non-system stored procedures.

using System;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.CodeDom;
using System.CodeDom.Compiler;
using Microsoft.CSharp;
using SQLDMO;
namespace DataBlockExtender.Core
{
  public class Generator
  {
    public Generator()
    {

    }
    internal void Generate (string serverName, string databaseName, 
       string userid, string password, string outputFileName, 
       string rootNamespace,string methodMod, 
       FilterOption filterOption,string filterExpression)
    {
      if (methodMod.Length==0) methodMod="public";
      // open the connection to the server
      SQLServer server = new SQLServerClass();
      if (userid == null || userid == "")
        server.LoginSecure = true;
      server.Connect(serverName, userid, password);
      // get a reference to the database
      Database database = null;
      foreach(SQLDMO.Database temp in server.Databases)
        if (temp.Name.ToUpper() == databaseName.ToUpper())
        {
          database = temp;
          break;
        }

      if (database == null)
        throw new Exception(string.Format(
          "Could not locate database {0} via SQLDMO.", 
          databaseName));
        // prep the writer
      TextWriter tw = 
         new StreamWriter(new FileStream(outputFileName, 
         FileMode.Create));
      IndentedTextWriter writer = new IndentedTextWriter(tw);
      // write the file heading
      writer.WriteLine("/* Code Generated by Application DataBlock " +
              "Extender Code Generator");
      writer.WriteLine("/* Utillity created by Dmitry Shalimov to make " +
              "Data Layer creation and reusabillity easier");
      writer.WriteLine("/* For more information please " +
              "visit DmitryConsulting.com");
      writer.WriteLine("/* Software provided as is with no warranty");
      writer.WriteLine("/* For questions, comments or to report a bug " +
              "please email to info@dmitryConsulting.com");
      writer.WriteLine("*/" );
      writer.WriteLine("using System;");
      writer.WriteLine("using System.Data;");
      writer.WriteLine("using System.Data.SqlClient;");

      writer.WriteLine("using Microsoft.ApplicationBlocks.Data;");
      writer.WriteLine();
      writer.WriteLine(string.Format("namespace {0}", rootNamespace));
      OpenBrace(writer);
      // process the procs
      foreach (StoredProcedure sp in database.StoredProcedures)
      {
        if (sp.SystemObject == true)
          continue;

        StoredProcedureInfo proc = new StoredProcedureInfo(sp);
        if (filterOption==FilterOption.Contains)
        {

          if( proc.CsName.ToUpper().IndexOf(filterExpression.ToUpper())>=0 
            || 
            proc.SqlName.ToUpper().IndexOf(filterExpression.ToUpper())>=0 )
          {
            GenerateCodeForProc(writer, proc,methodMod); 
          }
        }
        else if (filterOption==FilterOption.StartsWith)
        {
          if( proc.CsName.ToUpper().StartsWith(filterExpression.ToUpper()) 
          || proc.SqlName.ToUpper().StartsWith(filterExpression.ToUpper()))
          {
            GenerateCodeForProc(writer, proc,methodMod); 
          }
        }
        else //No Filter
        {
          GenerateCodeForProc(writer, proc,methodMod); 
        }

      }
      // close the namespace
      CloseBrace(writer);
      // close the file
      writer.Close();
      tw.Close();
      writer=null;
      tw=null;
      // close the server connection
      server.Close();
    }
    private string ParameterListAndRightBracket(StoredProcedureInfo proc,
           bool AddDataType)
    {
      if (proc.Parameters.Length==0) return " )";
      int i=0;
      int intParmNumber=proc.Parameters.Length-1;
      string strRet=", ";
      foreach (StoredProcedureInfo.ParameterInfo p in proc.Parameters)
      {
        strRet+= (AddDataType? p.CsDataType:"") + " " + 
           p.CsName + ((i++<intParmNumber)? ", ": " )");
      }
      return strRet.Replace(", ,",",");
    }
    public void GenerateCodeForProc (IndentedTextWriter writer, 
          StoredProcedureInfo proc,string methodMod)
    {
      // the class itself

      string[] strCommandsDeclaration=
      {
        methodMod + " static DataSet ExecuteDataset(string strConnString ",
        methodMod + " static DataSet ExecuteDataset(SqlTransaction tx ",
        methodMod + " static int ExecuteNonQuery(string strConnString ",
        methodMod + " static int ExecuteNonQuery(SqlTransaction tx ",
        methodMod + " static SqlDataReader ExecuteReader(string strConnString ",
        methodMod + " static SqlDataReader ExecuteReader(SqlTransaction tx ",
        methodMod + " static object ExecuteScalar(string strConnString ",
        methodMod + " static object ExecuteScalar(SqlTransaction tx "
      };
      string[] strCommandsReturn=
      {
        "return SqlHelper.ExecuteDataset(strConnString , ",
        "return SqlHelper.ExecuteDataset(tx , ",
        "return SqlHelper.ExecuteNonQuery(strConnString , ",
        "return SqlHelper.ExecuteNonQuery(tx , ",
        "return SqlHelper.ExecuteReader(strConnString , ",
        "return SqlHelper.ExecuteReader(tx , ",
        "return SqlHelper.ExecuteScalar(strConnString , ",
        "return SqlHelper.ExecuteScalar(tx , "
      };

      writer.WriteLine();
      writer.WriteLine();
      writer.WriteLine(string.Format ("# region {0}", proc.CsName));
      writer.WriteLine(string.Format(methodMod + " class {0} ", proc.CsName));
      OpenBrace(writer);
      for (int j=0;j<=strCommandsDeclaration.Length-1;j++)
      {
        writer.WriteLine(strCommandsDeclaration[j]
          + ParameterListAndRightBracket(proc,true));
        OpenBrace(writer);
        writer.WriteLine("try");
        OpenBrace(writer);
        writer.WriteLine(strCommandsReturn[j] + 
          "\"" + proc.SqlName +"\" " + 
          ParameterListAndRightBracket(proc,false) + ";" );
        CloseBrace(writer);
        writer.WriteLine("catch (Exception ex)");
        OpenBrace(writer);
        writer.WriteLine("//ToDo : Handle Exception");
        writer.WriteLine("string strEx= ex.Message;");
        writer.WriteLine("strEx+=\"\";");
        writer.WriteLine("return " + 
          ((strCommandsDeclaration[j].IndexOf("static int")>=0)?"-1;":"null;"));
        CloseBrace(writer);

        CloseBrace(writer);
      }
      CloseBrace(writer);
      writer.WriteLine("# endregion");
    }

Example of generated Code from NorthWind Database:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
namespace test
{
  # region CustOrderHist
  public class CustOrderHist 
  {
    public static DataSet ExecuteDataset(string strConnString , 
          string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteDataset(strConnString , "CustOrderHist" ,
               CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return null;
      }
    }
    public static DataSet ExecuteDataset(SqlTransaction tx , string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteDataset(tx , "CustOrderHist" , CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return null;
      }
    }
    public static int ExecuteNonQuery(string strConnString , string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteNonQuery(strConnString , 
                  "CustOrderHist" , CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return -1;
      }
    }
    public static int ExecuteNonQuery(SqlTransaction tx , string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteNonQuery(tx , "CustOrderHist" , CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return -1;
      }
    }
    public static SqlDataReader ExecuteReader(string strConnString , 
         string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteReader(strConnString , 
                "CustOrderHist" , CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return null;
      }
    }
    public static SqlDataReader ExecuteReader(SqlTransaction tx , 
            string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteReader(tx , "CustOrderHist" , CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return null;
      }
    }
    public static object ExecuteScalar(string strConnString , string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteScalar(strConnString , 
               "CustOrderHist" , CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return null;
      }
    }
    public static object ExecuteScalar(SqlTransaction tx , string CustomerID )
    {
      try
      {
        return SqlHelper.ExecuteScalar(tx , "CustOrderHist" , CustomerID );
      }
      catch (Exception ex)
      {
        //ToDo : Handle Exception
        string strEx= ex.Message;
        strEx+="";
        return null;
      }
    }
  }
# endregion
//Other regions for other classes
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

dshalimov

United States United States
No Biography provided

Comments and Discussions

 
GeneralA simple but complete solution Pinmemberchris Liang30-Aug-05 9:31 
GeneralRe: A simple but complete solution - a free one! PinmemberThomas Schittli28-Sep-05 3:05 
General&quot;Application DataBlock&quot; PinsussAnonymous4-Apr-05 10:44 
GeneralRe: "Application DataBlock" Pinmemberdshalimov6-Apr-05 4:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 4 Apr 2005
Article Copyright 2005 by dshalimov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid