예제 #1
0
        private void button1_Click(object sender, EventArgs e)
        {
            List<String> campos = new List<String>();
            List<String> t = new List<String>();
            foreach (int check in checkedListBoxColumna.CheckedIndices)
            {

                campos.Add(checkedListBoxColumna.Items[check].ToString());
                t.Add(tipos[check].ToString());
            }
            tipoFuncion tf;
            switch(comboBox2.Text){
                case "Contador":
                    tf = tipoFuncion.cont;
                    break;
                case "Suma":
                    tf = tipoFuncion.suma;
                    break;
                case "Minimo":
                    tf = tipoFuncion.min;
                    break;
                case "Maximo":
                    tf = tipoFuncion.max;
                    break;
                case "Promedio":
                    tf = tipoFuncion.avg;
                    break;
                default:
                    MessageBox.Show("Operacion no valida");
                    tf = tipoFuncion.cont;
                    break;
            }
            String res;
            if (tf == tipoFuncion.cont)
            {
                res = "";
            }
            else
            {
                res = checkedListBoxResumen.CheckedItems[0].ToString();
            }
            CreadorDeCubos cc = new CreadorDeCubos(cadenaDeConexion, esquema, campos, t, tf, res, textBoxQuery.Text,tipos,TodosLosCampos);
            cc.creaTabla();
            cc.creaCubexeles();

            DBConnection myDB = new DBConnection(cadenaDeConexion);
            myDB.connect();
            DataTable dt = myDB.ejecutaQuery("select * from " + esquema+".dbo.cube");
            dataGridViewCubo.DataSource = dt;

            MessageBox.Show("Terminado");
        }
예제 #2
0
        public void creaTabla()
        {
            DBConnection db = new DBConnection(CadenaDeConexion);
            db.connect();

            StringBuilder cmd = new StringBuilder();
            cmd.Append(" IF OBJECT_ID('");
            cmd.Append(Esquema);
            cmd.Append(".dbo.cube', 'U') IS NOT NULL");
            cmd.Append(" DROP TABLE ");
            cmd.Append(Esquema);
            cmd.Append(".dbo.cube");
            db.ejecutaComando(cmd.ToString());

            cmd = new StringBuilder();

            cmd.Append("Create table ");
            cmd.Append(Esquema);
            cmd.Append(".dbo.cube(");
            for (int i = 0; i < campos.Count; i++)
            {
                if (i != 0)
                {
                    cmd.Append(",");
                }
                cmd.Append(campos[i]);
                cmd.Append(" ");
                cmd.Append("Text");//tipos[i]);
            }
            if (campos.Count > 0)
            {
                cmd.Append(",");
            }
            if (tipoF == tipoFuncion.cont)
            {
                cmd.Append("Total");
            }
            else
            {
                cmd.Append(columnaResumen);
            }

            cmd.Append(" ");
            cmd.Append("int");
            cmd.Append(")");
            String debug = cmd.ToString();
            db.ejecutaComando(cmd.ToString());
        }
예제 #3
0
        private void buttonObtenInfo_Click(object sender, EventArgs e)
        {
            DBConnection myDB = new DBConnection(cadenaDeConexion);
            myDB.connect();
            DataTable dt = myDB.ejecutaQuery(textBoxQuery.Text);
            dataGridViewQuery.DataSource = dt;
            tipos = new List<String>();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                switch (dt.Columns[i].DataType.ToString()) {
                    case "System.Int32":
                        tipos.Add("int");
                        break;
                    case "System.Int64":
                        tipos.Add("bigint");
                        break;
                    case "System.String":
                        tipos.Add("varchar(500)");
                        break;
                    case "System.Byte[]":
                        tipos.Add("varbinary(50)");
                        break;
                    case "System.Int16":
                        tipos.Add("smallint");
                        break;
                    case "System.Boolean":
                        tipos.Add("bit");
                        break;
                    default:
                        MessageBox.Show("No encontrado " + dt.Columns[i].DataType.ToString());
                        tipos.Add("varchar(500)");
                        break;

                }
            }
            checkedListBoxColumna.Items.Clear();
            checkedListBoxResumen.Items.Clear();
            TodosLosCampos = new List<string>();
            foreach(DataGridViewColumn column  in dataGridViewQuery.Columns){
                checkedListBoxColumna.Items.Add(column.Name);
                checkedListBoxResumen.Items.Add(column.Name);
                TodosLosCampos.Add(column.Name);
            }
        }
예제 #4
0
        private void generaQuery()
        {
            // agrupar por los que tienen 0, los
            DBConnection db = new DBConnection(CadenaDeConexion);
            db.connect();

            StringBuilder cmd = new StringBuilder();
            cmd.Append("IF OBJECT_ID('dbo.#temp', 'U') IS NOT NULL ");
            cmd.Append(" DROP TABLE dbo.#temp ");
            cmd.Append(" create table dbo.#temp ( ");

            for(int i=0; i<todoCampo.Count;i++){
                if(i!= 0){
                    cmd.Append(",");
                }
                cmd.Append(todoCampo[i]);
                cmd.Append(" ");
                cmd.Append(todoTipo[i]);
            }
            cmd.Append(" ) ");
            cmd.Append(" Insert into dbo.#temp ");
            cmd.Append(query);

            /* generar tabla estilo lo siguiente
             *
             IF OBJECT_ID('dbo.#temp', 'U') IS NOT NULL  DROP TABLE dbo.#temp
             create table dbo.#temp ( usuario int,problema int,puntos int,hora bigint,primero int )
             Insert into dbo.#temp SELECT *  FROM [Karelotitlan].[dbo].[UsuarioProblema]

             select CAST( CAST(usuario AS nvarchar) as text), CAST( CAST(problema AS nvarchar) AS text), sum(primero) from
            dbo.#temp
            group by usuario,problema

             Insert into Karelotitlan.dbo.cube
             select CAST( CAST(usuario AS nvarchar) as text), CAST( CAST(problema AS nvarchar) AS text), sum(primero) from
            dbo.#temp
            group by usuario,problema

             drop table dbo.#temp
             *
             */

            cmd.Append(" Insert into ");
            cmd.Append(Esquema);
            cmd.Append(".dbo.cube ");

            cmd.Append("select ");

            // lista de parametreos si tiene 0 hacer CAST si no poner 0
            for (int i = 0; i < campos.Count; i++)
            {
                if (i != 0)
                {
                    cmd.Append(",");
                }
                if (elegidos[i] == 0)
                {
                    //cmd.Append(" CAST ( CAST ( ");
                    cmd.Append(campos[i]);
                    //cmd.Append(" AS nvarchar) AS text) ");
                }
                else
                {
                    cmd.Append(" '(ALL)' ");
                }
            }
            if (campos.Count > 0)
            {
                cmd.Append(",");
            }
            switch (this.tipoF)
            {
                case tipoFuncion.cont:
                    cmd.Append(" count(*) ");
                    break;
                case tipoFuncion.avg:
                    cmd.Append(" AVG(");
                    cmd.Append(columnaResumen);
                    cmd.Append(") ");
                    break;
                case tipoFuncion.max:
                    cmd.Append(" MAX(");
                    cmd.Append(columnaResumen);
                    cmd.Append(") ");
                    break;
                case tipoFuncion.min:
                    cmd.Append(" MIN(");
                    cmd.Append(columnaResumen);
                    cmd.Append(") ");
                    break;
                case tipoFuncion.suma:
                    cmd.Append(" SUM(");
                    cmd.Append(columnaResumen);
                    cmd.Append(") ");
                    break;
            }
            cmd.Append(" from dbo.#temp ");

            // lista de grupos

            int agrupados = 0;
            for (int i = 0; i < campos.Count; i++)
            {
                if (elegidos[i] == 0)
                {
                    if (agrupados > 0)
                    {
                        cmd.Append(",");
                    }
                    else {
                        cmd.Append(" group by ");
                    }
                    cmd.Append(campos[i]);
                    agrupados++;
                }
            }

            cmd.Append(" drop table dbo.#temp ");

            String debug = cmd.ToString();
            db.ejecutaComando(cmd.ToString());
        }