Generador de código DML para SQL

Introducción

En pocas palabras, esta utilidad sirve para "como su nombre lo dice…" generar el código DML (Data Manipulation Language) necesario para ejecutar las acciones típicas sobre una tabla en SQL Server 2000 tales como SELECT, INSERT, UPDATE o DELETE. Digo SQL Server 2000 porque es la única versión de SQL en la que he probado. Es posible que también funcione para SQL Server 7 pero no lo aseguro.

Una peque reseña

En primer lugar saludos a todos quienes visitan esta estupenda Web, esta es la segunda vez que colaboro aquí, cierta ves lo hice como hace 4 años con un reproductor de mp3, pero en fin eso ya es historia.

Bueno, hace mucho tiempo desde que empecé con este lió de desarrollar aplicaciones que trabajan con datos me dí cuenta que era muy tedioso eso de ir escribiendo todo el código para realizar un simple select, insert, update o delete, sobretodo si la tabla con la que estaba trabajando tenía como 30 campos, entonces empecé a preguntarme en mis adentros muy adentros… ¿habrá alguna forma de evitarme todos eso y que se me genere solito el código tan solo con decir que tipo de acción quiero ejecutar sobre cierta tabla? "y estoy seguro que no soy el único que se preguntó eso". Para buscar respuestas me dirigí al famosísimo y muy conocido Google, el que todo lo sabe, pero nunca encontré algo parecido "puede ser que sí lo haya pero no lo he encontrado", así que no me quedó de otra que hacerlo yo mismo.

El trabajo fue duro pero el fin lo recompensa. El resultado es una clase que primero la hice para una aplicación desarrollada en Visual Basic 6.0 y funcionó de maravilla y ahora con esto de las nuevas tecnologías y en vista que necesito lo mismo para mis nuevas aplicaciones con .NET, la transformé a VB.NET

El código:

Bueno, sin más palabras, les presento a continuación el código de la Super Clase:

Option Strict Off
Imports System.Data.SqlClient

''' <summary>
''' Encapsula métodos necesarios para obtener información
''' interna de tablas de SQL Server.
''' </summary>
''' <remarks>Victor Viscarra © 2007</remarks>
Public Class CN_SQL

  Private cs As String

  Public Enum SQLTYPE
    eSelect
    eInsert
    eUpdate
    eDelete
  End Enum


  Public Sub New(ByVal CadenaConexion As String)
    cs = CadenaConexion
  End Sub

  ''' <summary>
  ''' Obtiene la lista de campos que contiene una tabla.
  ''' </summary>
  ''' <param name=Tabla">Nombre de la tabla.</param>"
  Private Function GetListaCampos(ByVal Tabla As String) As DataTable
    Dim dt As New DataTable
    Dim sql As String
    Dim cpk As Integer

    sql = "select a.name as Nombre, cast(a.colorder as int) as Orden,"
    sql += " cast(b.name as varchar) as Tipo,"
    sql += " Length = "
    sql += "   case"
    sql += "     when b.name = 'char' or b.name = 'varchar' then cast(a.length as int)"
    sql += "     else cast(a.xprec as int)"
    sql += "   end,"
    sql += "'' as Descripcion, 0 as isPK, a.isNullable,"
    sql += " isIdentity = "
    sql += "   case"
    sql += "     when a.autoval is null then 0"
    sql += "     else 1"
    sql += "   end"
    sql += " from syscolumns a inner join systypes b"
    sql += " on (a.xtype = b.xtype and a.xusertype = b.xusertype)"
    sql += " where a.id = (object_id(N'[dbo].[{0}]'))"
    sql += " order by colorder"

    sql = String.Format(sql, Tabla)

    Try
      Dim da As New SqlDataAdapter(sql, cs)
      da.Fill(dt)

      ' Llena los datos de descripción y primary key.
      If Not dt Is Nothing Then
        For Each dr As DataRow In dt.Rows
          dr("Descripcion") = Me.GetDesCampo(Tabla, dr("Nombre").ToString)
          dr("isPK") = Me.IsPKCampo(Tabla, dr("Nombre").ToString)
          If CInt(dr("isPK")) = 1 Then cpk += 1
        Next

      End If
    Catch ex As Exception
      Throw ex
    End Try

    Return dt
  End Function

  ''' <summary>
  ''' Obtiene la descripción de un campo de la Base de Datos.
  ''' </summary>
  ''' <param name=Tabla">Nombre de la tabla propietaria del campo</param>"
  ''' <param name=Campo">Campo del que se quiere obtener la descripción</param>"
  Private Function GetDesCampo(ByVal Tabla As String, ByVal Campo As String) As String
    Dim sql As String
    Dim ret As String = Nothing
    Dim o As Object
    Dim cn As New SqlConnection(cs)

    sql = "select value from ::fn_listextendedproperty ('MS_Description','user','dbo','table',"
    sql += "'{0}','column','{1}')"
    sql = String.Format(sql, Tabla, Campo)

    Try
      Dim cm As New SqlCommand

      cn.Open()
      With cm
        .Connection = cn
        .CommandType = CommandType.Text
        .CommandText = sql
        .ExecuteNonQuery()
        o = .ExecuteScalar()
      End With

      If Not o Is Nothing Then
        ret = o.ToString()
      End If

    Catch ex As Exception
      If TypeOf ex Is InvalidCastException Then
        ret = Nothing
      Else
        Throw ex
      End If
    Finally
      If cn.State <> ConnectionState.Open Then
        cn.Close()
      End If
      cn.Dispose()
    End Try

    Return ret
  End Function

  ''' <summary>
  ''' Obtiene un valor que indica si un campo forma parte del Primary Key
  ''' de la tabla de acuerdo al parámetro pasado.
  ''' </summary>
  ''' <param name=Tabla">Nombre de la tabla propietaria del campo</param>"
  ''' <param name=Campo">Campo del que se quiere obtener la información</param>"
  Private Function IsPKCampo(ByVal Tabla As String, ByVal Campo As String) As Integer
    Dim ret As Integer
    Dim c As String = String.Empty
    Dim sql As String
    Dim o As Object
    Dim cn As New SqlConnection(cs)

    sql = "select 'T' from sysindexkeys ik, sysindexes i"
    sql += " Where ik.id = i.id"
    sql += " and ik.indid = i.IndId"
    sql += " and ik.colid = (select colid from  syscolumns"
    sql += " where id = (object_id(N'[dbo].[{0}]'))"
    sql += " and name = '{1}')"
    sql += " and i.id = (object_id(N'[dbo].[{0}]'))"
    sql += " and i.name in (SELECT name"
    sql += " From sysobjects"
    sql += " where (xtype = 'PK'))"

    sql = String.Format(sql, Tabla, Campo)

    Try
      Dim cm As New SqlCommand
      cn.Open()

      With cm
        .Connection = cn
        .CommandType = CommandType.Text
        .CommandText = sql
        o = .ExecuteScalar()
      End With

      If Not o Is Nothing Then
        c = o.ToString()
      End If

      If c = "T" Then ret = 1
    Catch ex As Exception
      If TypeOf ex Is InvalidCastException Then
        ret = 0
      Else
        Throw ex
      End If
    Finally
      If cn.State <> ConnectionState.Open Then
        cn.Close()
      End If
      cn.Dispose()
    End Try

    Return ret
  End Function

  ''' <summary>
  ''' Genera una Sentencia SQL (DML) de acuerdo al nombre de la tabla y tipo
  ''' que se desee.
  ''' </summary>
  ''' <param name=Tabla">Nombre de la tabla</param>"
  ''' <param name=TipoDML">Tipo de DML que se quiere obtener</param>"
  Public Function GeneraSQL(ByVal Tabla As String, ByVal TipoDML As SQLTYPE) As String
    Dim i, controlPK, fc As Integer
    Dim sql As String = String.Empty
    Dim sql2 As String
    Dim dt As DataTable

    ' Obtiene todos los datos necesarios de cada
    ' campo existente de la tabla
    Try
      dt = Me.GetListaCampos(Tabla)
    Catch ex As Exception
      dt = Nothing
      Throw ex
    End Try

    If Not dt Is Nothing Then
      fc = dt.Rows.Count - 1

      Select Case TipoDML
        Case SQLTYPE.eSelect
          ' Genera la cadena SQL <<Select>>
          sql = "SELECT "
          For Each dr As DataRow In dt.Rows
            sql += dr("Nombre")
            If i < fc Then
              sql += ", "
              i += 1
            Else
              sql += vbCrLf + "FROM {0}"
            End If
          Next

        Case SQLTYPE.eInsert
          ' Genera la cadena SQL <<Insert Into>>
          sql = "INSERT INTO {0}" + vbCrLf + " ("
          sql2 = "VALUES" + vbCrLf + " ("

          For Each dr As DataRow In dt.Rows
            If dr("isIdentity") = 0 Then
              sql += dr("Nombre")
              sql2 += "@" + dr("Nombre")
              If i < fc Then
                sql += ", "
                sql2 += ", "
              Else
                sql += ")"
                sql2 += ")"
                sql += vbCrLf + sql2
              End If
            End If
            i += 1
          Next

        Case SQLTYPE.eUpdate
          ' Genera la cadena SQL <<Update>>
          sql = "UPDATE {0}" + vbCrLf + "SET "
          controlPK = 0
          For Each dr As DataRow In dt.Rows
            If dr("isPK") = 0 Then
              controlPK += 1
              sql += dr("Nombre") + " = @" + dr("Nombre")
              If i < fc Then
                sql += ", " + vbCrLf
              Else
                sql += vbCrLf + "WHERE "
              End If
            End If
            i += 1
          Next

          If controlPK = 0 Then
            sql = "NO SE PUEDE GENERAR EL COMANDO UPDATE PARA LA TABLA {0}"
            sql += " DEBIDO A QUE NO POSEE CAMPOS ACTUALIZABLES."
            sql = String.Format(sql, Tabla)
            Throw New Exception(sql)
          End If

          controlPK = 0
          i = 0
          For Each dr As DataRow In dt.Rows
            If CInt(dr("isPK")) = 1 Then
              controlPK += 1
              sql += dr("Nombre") + " = @" + dr("Nombre")
              sql += vbCrLf + " AND "
            End If
          Next

          If controlPK = 0 Then
            sql = "NO SE PUEDE GENERAR EL COMANDO UPDATE PARA LA TABLA {0}"
            sql += " DEBIDO A QUE NO POSEE PRIMARY KEY."
            sql = String.Format(sql, Tabla)
            Throw New Exception(sql)
            Exit Select
          End If

          sql = sql.Substring(0, sql.Length - 7)

        Case SQLTYPE.eDelete
          ' Genera la cadena SQL <<Delete>>
          sql = "DELETE FROM {0}"
          sql += vbCrLf + "WHERE "

          controlPK = 0
          For Each dr As DataRow In dt.Rows
            If CInt(dr("isPK")) = 1 Then
              controlPK += 1
              sql += dr("Nombre") + " = @" + dr("Nombre")
              sql += vbCrLf + " AND "
            End If
          Next

          If controlPK = 0 Then
            sql = "NO SE PUEDE GENERAR EL COMANDO UPDATE PARA LA TABLA {0}"
            sql += " DEBIDO A QUE NO POSEE PRIMARY KEY."
            sql = String.Format(sql, Tabla)
            Throw New Exception(sql)
            Exit Select
          End If

          sql = sql.Substring(0, sql.Length - 7)
      End Select

      If Not String.IsNullOrEmpty(sql) Then
        sql = String.Format(sql, Tabla)
      End If

    End If

    Return sql
  End Function

End Class

Si revisamos un poco el código nos damos cuenta de que la clase se divide en cuatro funciones:

• GetListaCampos: Obtiene la lista de campos que contiene la tabla, además de el tipo de datos, el orden en la tabla, la longitud, la descripción, un indicador si el campo es parte del Primary Key, un indicador si el campo acepta datos Nulos y un indicador si el campo es tipo Identity (Autoincremeto).
• GetDesCampo: Esta es la función que obtiene la descripción de un campo.
• IsPKCampo: Obtiene el indicador si un campo es parte del Primary Key de la tabla.
• GeneraSQL: Esta es la super función que genera la sentencia DML de acuerdo al nombre de la tabla y tipo que se desee. Y como se podrán dar cuenta controla bien en la inserción cuando un campo es Identity y el DML lo genera con parámetros.

Nota:
Esta clase, además de servirte para la Generación de Código DML "que es su principal objetivo", también puedes darle varias utilidades debido a la información que te devuelve, puedes por ejemplo realizar una rutina para que te asigne automática y dinámicamente los Tooltips de los campos en un formulario de datos, o tal vez asignar el MaxLength, o realizar una validación de los campo nulos, todo solo con un poco de imaginación, ingenio y un algo de trabajo.

Espero que les sirva de mucho y que le saquen el jugo a la clase. Y ya saben, cualquier duda o comentario... 1800-AUXILIO, o me lo escriben por correo. Hasta una próxima Colaboración.

Espacios de nombres usados en el código de este artículo:

System.Data

System.Data.SqlClient