SoloCodigo

Hola, tengo una DLL con varias funciones, las cuales realizan consultas SQL, ya sea un SELECT, INSERT, UPDATE.

Estas funciones las llamo desde Excel, es decir creo una formula en Excel,por ejemplo: SALIDASALMACEN("001","1100","590","2012/10/01","2012/10/30")

Las funciones funciona bien, el problema es que cuando tengo varias celdas con formulas, digamos 100 y actualizo una celda usada en dichas formulas (por ejemplo las fechas) las formulas se recalculan y tarda alrededor de 15 minutos para hacer el calculo. Ya no se diga cuando tienes un archivo con muchas mas formulas.

Este es el codigo de la DLL :

using System;
using System.Runtime.InteropServices;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace Almacen
{
    [ComVisible(true)]
    [Guid("282A7142-F792-4CC8-AAF9-4558128EAE75")]

public class Salidas
{

[ComVisible(true)]
[DispId(0)]
public double SalidasInventario(string subalmacen, int departamento, int familia, string fechainicial, string fechafinal)
        {
            System.Data.SqlClient.SqlConnection con;
            System.Data.SqlClient.SqlCommand command;
            System.Data.SqlClient.SqlParameter retorno;
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
            try
            {
                if (con.State.Equals(ConnectionState.Closed))
                {
                    con.Open();
                }
                command = new System.Data.SqlClient.SqlCommand("salidasinventario", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                retorno = new System.Data.SqlClient.SqlParameter("GrandTotal", System.Data.SqlDbType.Float);
                retorno.Direction = System.Data.ParameterDirection.Output;
                command.Parameters.Add(retorno);
                command.Parameters.AddWithValue("subalmacen", subalmacen);
                command.Parameters.AddWithValue("depto", departamento);
                command.Parameters.AddWithValue("familia", familia);
                command.Parameters.AddWithValue("fecha_inicial", fechainicial);
                command.Parameters.AddWithValue("fecha_final", fechafinal);
                command.ExecuteNonQuery();
                return Convert.ToDouble(command.Parameters["Grandtotal"].Value);
            }
            catch (Exception)
            {
                return (0);
            }
        }


[DispId(1)]
public int InsertarConcepto(string descripcion, string tipo)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    System.Data.SqlClient.SqlParameter retorno;
    int existeconcepto;

    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_verificaconcepto", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        retorno = new System.Data.SqlClient.SqlParameter("existe", System.Data.SqlDbType.Int);
        retorno.Direction = System.Data.ParameterDirection.Output;
        command.Parameters.Add(retorno);
        command.Parameters.AddWithValue("descripcion", descripcion);
        command.Parameters.AddWithValue("tipo", tipo);
        command.ExecuteNonQuery();
        existeconcepto = Convert.ToInt32(command.Parameters["existe"].Value);
        if (existeconcepto > 0)
        {
            return (-1);
        }
        command = new System.Data.SqlClient.SqlCommand("costos_insertaconcep", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.AddWithValue("descripcion", descripcion);
        command.Parameters.AddWithValue("tipo", tipo);
        command.ExecuteNonQuery();
        return (1);
    }
    catch (Exception)
    {
        return (0);
    }
}


[DispId(2)]
public ArrayList CargarConceptos()
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    ArrayList resultados = new ArrayList();
    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_cargaconceptos", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.ExecuteNonQuery();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);
            resultados.Add(values);
        }

        string valorderetorno = "";
        string temp;
        foreach (object[] row in resultados)
        {
            string[] orderDetails = new string[row.Length];
            foreach (object column in row)
            {
                temp = Convert.ToString(column);
                valorderetorno += temp;
            }
        }
        return (resultados);
    }
    catch (Exception)
    {
        return (resultados);
    }
}


[DispId(3)]
public int InsertarHeader(string fecha, int cc, int departamento, string concepto, double cantidad)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    System.Data.SqlClient.SqlParameter retorno;
    int folio;

    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_insertaheader", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        retorno = new System.Data.SqlClient.SqlParameter("folio", System.Data.SqlDbType.Int);
        retorno.Direction = System.Data.ParameterDirection.Output;
        command.Parameters.Add(retorno);
        command.Parameters.AddWithValue("fecha", fecha);
        command.Parameters.AddWithValue("cc", cc);
        command.Parameters.AddWithValue("departamento", departamento);
        command.ExecuteNonQuery();
        folio = Convert.ToInt32(command.Parameters["folio"].Value);
        if (folio < 1)
        {
            return(folio);
        }
        command = new System.Data.SqlClient.SqlCommand("costos_insertadetalle", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.AddWithValue("folio", folio);
        command.Parameters.AddWithValue("concepto", concepto);
        command.Parameters.AddWithValue("cantidad", cantidad);
        command.ExecuteNonQuery();
        return (folio);
    }
    catch (Exception)
    {
        return (0);
    }
}


[DispId(4)]
public double Sumacargos(string fechainicial, string fechafinal, int cc, int depto)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    System.Data.SqlClient.SqlParameter retorno;
    double resultado;

    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_sumacargos", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        retorno = new System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.Int);
        retorno.Direction = System.Data.ParameterDirection.Output;
        command.Parameters.Add(retorno);
        command.Parameters.AddWithValue("fechai", fechainicial);
        command.Parameters.AddWithValue("fechaf", fechafinal);
        command.Parameters.AddWithValue("cc", cc);
        command.Parameters.AddWithValue("depto", depto);
        command.ExecuteNonQuery();
        resultado = Convert.ToDouble(command.Parameters["total"].Value);
        if (resultado < 0)
        {
            return (0);
        }
        return (resultado);
    }
    catch (Exception)
    {
        return (0);
    }
}


[DispId(5)]
public double Salidas_sinfamilia(string subalmacen, int departamento, string fechainicial, string fechafinal)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    System.Data.SqlClient.SqlParameter retorno;
    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_salidas_sinfamilia", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        retorno = new System.Data.SqlClient.SqlParameter("GrandTotal", System.Data.SqlDbType.Float);
        retorno.Direction = System.Data.ParameterDirection.Output;
        command.Parameters.Add(retorno);
        command.Parameters.AddWithValue("subalmacen", subalmacen);
        command.Parameters.AddWithValue("depto", departamento);
        command.Parameters.AddWithValue("fecha_inicial", fechainicial);
        command.Parameters.AddWithValue("fecha_final", fechafinal);
        command.ExecuteNonQuery();
        return Convert.ToDouble(command.Parameters["Grandtotal"].Value);
    }
    catch (Exception)
    {
        return (0);
    }
}


[DispId(6)]
public string busca_headers(int departamento, string fechai, string fechaf)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    ArrayList resultados = new ArrayList();
    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_busca_header", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.AddWithValue("departamento", departamento);
        command.Parameters.AddWithValue("fechainicial", fechai);
        command.Parameters.AddWithValue("fechafinal", fechaf);
        command.ExecuteNonQuery();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);
            resultados.Add(values);
        }

        string valorderetorno = "";
        string temp;
        foreach (object[] row in resultados)
        {
            string[] orderDetails = new string[row.Length];
            foreach (object column in row)
            {
                temp = Convert.ToString(column);
                valorderetorno += temp;
                valorderetorno += "|";
            }
            valorderetorno += "°";
        }
        return (valorderetorno);
    }
    catch (Exception)
    {
        return ("");
    }
}


[DispId(7)]
public string selecciona_cargo(int folio)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    ArrayList resultados = new ArrayList();
    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_selecciona_cargo", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.AddWithValue("folio", folio);
        command.ExecuteNonQuery();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);
            resultados.Add(values);
        }

        string valorderetorno = "";
        string temp;
        foreach (object[] row in resultados)
        {
            string[] orderDetails = new string[row.Length];
            foreach (object column in row)
            {
                temp = Convert.ToString(column);
                valorderetorno += temp;
                valorderetorno += "|";
            }
            valorderetorno += "°";
        }
        return (valorderetorno);
    }
    catch (Exception)
    {
        return ("");
    }
}



[DispId(8)]
public int borra_cargo(int folio)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    ArrayList resultados = new ArrayList();
    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_borra_cargo", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.AddWithValue("folio", folio);
        command.ExecuteNonQuery();
        return (1);
    }
    catch (Exception)
    {
        return (0);
    }
}



[DispId(9)]
public int actualizar_cargo(int folio,string concepto, double cantidad)
{
    System.Data.SqlClient.SqlConnection con;
    System.Data.SqlClient.SqlCommand command;
    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
        }
        command = new System.Data.SqlClient.SqlCommand("costos_actualiza_cargos", con);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.AddWithValue("folio", folio);
        command.Parameters.AddWithValue("concepto", concepto);
        command.Parameters.AddWithValue("cantidad", cantidad);
        command.ExecuteNonQuery();
        return (1);
    }
    catch (Exception)
    {
        return (0);
    }
}


[DispId(10)]
public int prueba_conexion()
{
    System.Data.SqlClient.SqlConnection con;
    con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "Server=XXXX;Database=XXXX;User Id=XXXX;Password=XXXX;";
    try
    {
        if (con.State.Equals(ConnectionState.Closed))
        {
            con.Open();
            return(1);
        }
        return (0);
    }
    catch (Exception)
    {
        return (-1);
    }
}


    }
}

Yo sospecho que es porque cada vez que mando llamar a una funcion se abre la conexion nuevamente.

Para eso hice especialmente la ultima funcion, checando si la conexion estaba abierta. El problema es que no se como hacer para que la conexion se mantenga abierta entre llamadas a la DLL. Alguien me podria decir como hacer eso? o en su caso como mejorar el rendimiento de las funciones.

Estoy usando SQL Server 2005. El detalle con trabajar directamente con las tablas esque tendria que poner en el codigo de Excel (VBA) los nombres de las tablas y los usuarios, y esto es un gran riesgo de seguridad.