Procedimiento almacenado en xml con access

Question

Hola a todos:

Tengo una serie de procedimientos almacenados (uno por cada where de selección de "códigos", que aparte de tener que generar un elevado número de procedimientos almacenados (tantos como "códigos" tenga la cláusula "WHERE" de la select, en el momento de llamar al mismo para añadirle los parámetros, se genera un gran cantidad de líneas de código. Son unas 1400 selects que generan una brutalidad de código al ir las haciendo todas a través de dichos procedimientos y evidentemente totalmente parametrizadas, siendo en la asignación de los parámetros donde realmente se genera ese excesivo código, debido a la cantidad de registros que se tienen que añadir en cada consulta de selección.

Bueno, he visto por ahí que se podía quizás hacer dichos procedimientos almacenados en formato "xml", del cual estoy intentando buscar información ya que no he trabajado nunca con dicho formato, pero parece ser, que si podría solucionar con el mismo la ingente cantidad de líneas de código que se crean al añadir valor a los parámetros. Os pongo un ejemplo de como realizo la creación del procedimiento almacenado en "Access" y de como llamo al mismo en un procedimiento cualquiera. Total, la pregunta es si este mismo se podría trabajr en "xml" y como lo podría hacer o bien, donde puedo encontrar una explicación amplia sobre dicho tema, ya que parece ser que el valor de los parámetros referentes a "códigos", se podría realizar en una sola línea.

Aquí creo el procedimiento almacenado en "access"

 Private Sub CreacionprocFormulasBalance_15_digitos()
        Try
            modInicio.ConexionBaseDatos()
            Using Cnn As New OleDbConnection(strConexion)
                Dim cmd As OleDbCommand = Cnn.CreateCommand
                cmd.CommandText = "CREATE PROCEDURE procFormulasBalance15dig " & _
                                  "([@empresa] Text(3), [@planconta] Text(12), [@codigo1] Text(7), [@codigo2] Text(7), [@codigo3] Text(7), " & _
                                  "[@codigo4] Text(7), [@codigo5] Text(7), [@codigo6] Text(7), [@codigo7] Text(7), [@codigo8] Text(7), [@codigo9] Text(7), [@codigo10] Text(7), " & _
                                  "[@codigo11] Text(7), [@codigo12] Text(7), [@codigo13] Text(7), [@codigo14] Text(7), [@codigo15] Text(7)) AS " & _
                                  "SELECT Ejer_01, Ejer_02, Ejer_03, Ejer_04, Ejer_05, Ejer_06, Ejer_07, Ejer_08, Ejer_09, Ejer_10, Ejer_11, Ejer_12 " & _
                                  "FROM Balances WHERE idEmpresa = [@empresa] AND PlanConta = [@planconta] " & _
                                  "AND [Cód_GC] IN ([@codigo1], [@codigo2], [@codigo3], [@codigo4], [@codigo5], [@codigo6], [@codigo7], [@codigo8], [@codigo9], [@codigo10], " & _
                                  "[@codigo11], [@codigo12], [@codigo13], [@codigo14], [@codigo15])"
                Cnn.Open()
                cmd.ExecuteNonQuery()
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

Aquí llamo al procedimiento almacenado en "access".

Public Sub Ventas_ModExp()         Dim valor As Integer = ObtenerPeriodos(m_strPerAnualesAño(0))
           Select Case m_strCodPais
                Case "34" 'España

                    For i As Integer = 0 To valor
                        'Inicializamos la variable a 0
                        m_decVentas(i) = 0
                    Next

                    Try
                        modInicio.ConexionBaseDatos()
                        Using Cnn As New OleDbConnection(strConexion)
                            Cnn.Open()
                            Dim cmd As OleDbCommand = Cnn.CreateCommand()

                            Select Case strPlanConta
                                Case "PLAN 2007"
                                    cmd.CommandText = "procFormulasBalance15dig"
                                    cmd.CommandType = CommandType.StoredProcedure
                                    With cmd.Parameters
                                        .Clear()
                                        .AddWithValue("@empresa", m_strCod_Empresa)
                                        .AddWithValue("@planconta", "PLAN 2007")
                                        .AddWithValue("@codigo1", "700")
                                        .AddWithValue("@codigo2", "701")
                                        .AddWithValue("@codigo3", "702")
                                        .AddWithValue("@codigo4", "703")
                                        .AddWithValue("@codigo5", "704")
                                        .AddWithValue("@codigo6", "706")
                                        .AddWithValue("@codigo7", "7060")
                                        .AddWithValue("@codigo8", "7061")
                                        .AddWithValue("@codigo9", "7062")
                                        .AddWithValue("@codigo10", "7063")
                                        .AddWithValue("@codigo11", "708")
                                        .AddWithValue("@codigo12", "7080")
                                        .AddWithValue("@codigo13", "7081")
                                        .AddWithValue("@codigo14", "7082")
                                        .AddWithValue("@codigo15", "7083")
                                    End With
                                 nd Select

                            Using rst As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult)
                                While rst.Read
                                    For i As Integer = 0 To valor
                                        m_decVentas(i) = m_decVentas(i) + CDec(rst.GetDouble(i)) / m_decValorDivisor
                                    Next i
                                End While
                            End Using
                        End Using

                    Catch ex As Exception
                        'Se ha producido un error
                        MessageBox.Show(ex.Message)
                    End Try
            End Select
End sub

Os quedo muy agracecida por la aportación de vuestra información.

Un saludo a todos.

Gemma

Answer 1

"gemma_campillo" escribió:

> he visto por ahí que se podía quizás hacer dichos
> procedimientos almacenados en formato "xml", ...

Gemma, los procedimientos o consultas almacenadas de Access hay que crearlos en la propia base de datos de Access, y que yo sepa, éstos no pueden estar en formato XML.

Ahora bien, si te refieres a guardar en un archivo XML el contenido de la consulta CREATE PROCEDURE, lo puedes hacer, como también las puedes guardar en un simple archivo de texto plano o en otra base de datos de Access. Pero ¿qué ocurriría si algún "usuario listillo" abre el archivo XML o TXT con el Bloc de Notas de Windows, y te modifica la sintaxis de las consultas CREATE PROCEDURE existentes en dicho archivo? ;-)

> Tengo una serie de procedimientos almacenados
> (uno por cada where de selección de "códigos", ...

Esto no lo entiendo. Si lo que varía es el valor de la cláusula WHERE, ¿por qué hay que crear esa cantidad tan grande de procedimientos almacendados que comentas? ¿No bastaría con un único procedimiento almacenado lllamado procFormulasBalance15dig?

 

---

Enrique Martínez
  [MS MVP - VB]

Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

Si usas Visual Basic .NET y deseas ser productivo y feliz, activa la instrucción Option Strict.

Answer 2

Hola querido Enrique:

He entendido la primera parte de tu respuesta, referente a los "xml", nada, se queda como está.

Sobre la segunda parte: Esto no lo entiendo. Si lo que varía es el valor de la cláusula WHERE, ¿por qué hay que crear esa cantidad tan grande de procedimientos almacendados que comentas? ¿No bastaría con un único procedimiento almacenado lllamado procFormulasBalance15dig?, mira maestro, lo que quiero decir, en que los wheres hacen referencia a 15 códigos, 90 códigos o 1 código, entonces tengo creado un procedimiento almacenado por la cantidad exacta de códigos que lleve cada select en su where, en este caso 3 procedimientos almacenados para entendernos, uno por 15, otro por 90 y otro por 1., y así va perfecto, pero el problema es, como muestro en la llamada al procedimiento, tengo que crear um procedimiento por la cantidad de dígot o códigos que tenga la where en sus parámetros "códigos". Por ello, si la where tiene 18 códigos, pues creo un procedimiento para esos 18 códigos, que evidentemente, lo aprovecho cuando en otras selects me vengan con 18 códigos. La duda era, si esa cantidad de líneas que se generan cuando creamos el valor de cada parámetro, se podían reducir, lo cual ya me lo has dejado muy claro como siempre.

Nada, a seguir como lo estoy haciendo que además va perfecto, solo el problemilla de las líneas de código, porque cuando te viene una select con 100 códigos, opues eso, a rellenar 100 líneas de código, pero bueno, es lo que hay, las relleno y ya está. Así ya llevo muchas y si no dices lo contrario, así seguiré.

Bueno querido Enrique un fuerte abrazo como siempre y muchas gracias por estar siempre ahí con tus excepcionales y didácticas respuestas.

Gemma.

Answer 3

Como estás trabajando con una base de datos de Access, bien sabes que los parámetros se toman por posición y no por nombre, aunque soy consciente que especificar el nombre sirve para clarificar los parámetros que se deben de pasar, aparte que te serviría por si el día de mañana quieres crear el procedimiento almacenado en una base de SQL Server.

Si quieres reducir el código de la sintaxis de la consulta CREATE PROCEDURE, utiliza el signo ? en lugar de [@planconta], por ponerte un ejemplo.

La consulta para crear el procedimiento procFormulasBalance15dig sería así:

cmd.CommandText = _
 "CREATE PROCEDURE procFormulasBalance15dig " & _
 "([@empresa] Text(3), [@planconta] Text(12), [@codigo1] Text(7)," & _
 "[@codigo2] Text(7), [@codigo3] Text(7), [@codigo4] Text(7), " & _
 "[@codigo5] Text(7), [@codigo6] Text(7), [@codigo7] Text(7), " & _
 "[@codigo8] Text(7), [@codigo9] Text(7), [@codigo10] Text(7), " & _
 "[@codigo11] Text(7), [@codigo12] Text(7), [@codigo13] Text(7), " & _
 "[@codigo14] Text(7), [@codigo15] Text(7)) AS " & _
 "SELECT Ejer_01, Ejer_02, Ejer_03, Ejer_04, Ejer_05, Ejer_06, " & _
 "Ejer_07, Ejer_08, Ejer_09, Ejer_10, Ejer_11, Ejer_12 " & _
 "FROM Balances " & _
 "WHERE idEmpresa = ? AND PlanConta = ? " & _
 "AND [Cód_GC] IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

Si no me he equivocado son 17 parámetros en total, lo que significa que tienes que indicar 17 caracteres de cierre de interrogación '?' después de los nombres de los campos que se especifican tras la cláusula WHERE existente en la consulta.

Y los valores los tendrás que añadir a la colección Parameters de un objeto OleDbCommand en el mismo orden en el que se encuentran declarados en la consulta CREATE PROCEDURE.

Como te he indicado anteriormente, te podrías servir de otra base de datos de Access, que actúe de base de datos auxiliar, para almacenar en ella la sintaxis SQL de las definiciones de los procedimientos almacenados que vayas a utilizar.

Por ejemplo, en una tabla llamada SintaxisProcedimientos crearías dos campos:

   NombreProcedimiento   Texto    Clave Principal
   SintaxisSQL                      Memo

E insertarías los registros en la misma de la siguiente manera:

   NombreProcedimiento                      SintaxisSQL
   ===================          ========

   procFormulasBalance15dig                CREATE PROCEDURE procFormulasBalance15dig ...
   otroNombreProcedimiento                CREATE PROCEDURE otroNombreProcedimiento...

Por supuesto, a ésta base de datos auxiliar la protegerías con una contraseña para que el "usuario listillo" no tenga tan fácil el poder modificar los valores existentes. ;-)

Cuando desde tu aplicación principal desees obtener la sintaxis de algún procedimiento, te conectarías con la base de datos auxiliar y ejecutarías el método ExecuteReader de un objeto OleDbCommand para obtener la sintaxis correspondiente a un procedimiento concreto, pasándole el resultado obtenido a la propiedad CommandText del objeto OleDbCommand que vayas a utilizar para ejecutar la consulta CREATE PROCEDURE en la base de datos principal de tu aplicación.

Otra opción factible para no utilizar otra base de datos, es hacer lo mismo pero guardando el nombre y la sintaxis SQL de los procedimientos en los Recursos de tu aplicación, tal y como aparece en la siguiente imagen:

Y para obtener el valor de alguna cadena, en tu módulo de utilidades insertarías la siguiente función:

        ''' <summary>
        ''' Obtiene el valor del recurso de cadena especificado.
        ''' </summary>
        ''' <author>Enrique Martínez Montejo - 2008</author>
        ''' <param name="resourceName">Nombre del recurso que se desea recuperar.</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Friend Function GetResourceString(resourceName As String) As String

            Try
                Dim rm As Resources.ResourceManager = My.Resources.ResourceManager

                ' Obtengo el valor del recurso especificado.
                '
                Dim resource As String = rm.GetString(resourceName)

                rm = Nothing

                Return resource

            Catch ex As Exception
                Return String.Empty

            End Try

        End Function

Y le asignarías el valor a la propiedad CommandText del objeto OleDbCommand de la siguiente manera:

   cmd.CommandText = GetResourceString("procFormulasBalance15dig")

Como podrás observar, tienes varias alternativas para no escribir el código SQL en la propia aplicación, aunque obviamente, quieras o no quieras, te guste o no te guste, al final lo vas a tener que escribir, bien en un sitio u en otro.

¿Me he explicado o necesitas alguna aclaración? ;-))

---

Enrique Martínez
  [MS MVP - VB]

Nota informativa: La información contenida en este mensaje, así como el código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o sugerido en el presente mensaje.

Si esta respuesta le ha resultado útil, recuerde marcarla como satisfactoria.

Si usas Visual Basic .NET y deseas ser productivo y feliz, activa la instrucción Option Strict.

Answer 4

Hola Enrique:

Perdón por responderte tan tarde, pero acabo de llegar y me pongo con esto otra vez.

Evidentemente tu explicación como siempre no necesita aclaraciones, voy a mirarme lo que me has indicado y a ver por cuál de las opciones propuestas por ti se acomoda mejor a mis necesidades.

Muchísimas gracias otra vez por tu calidad de respuesta, nunca sabrás bien lo que me llegan a ayudar.

Enrique, un fuerte abrazo como siempre.

Gemma.