示例#1
1
    /// <summary>
    /// Setea la lista de imagenes de una noticia
    /// </summary>
    /// <returns></returns>
    public static List<Imagen> getImagenes(Noticia noticia, OdbcConnection con)
    {
        List<Imagen> listaImagenes = new List<Imagen>();
        OdbcDataReader dr = null;

        String query = "SELECT i.id, i.pathBig, i.pathSmall, i.portada, i.pathMedium FROM imagen i, imagen_x_noticia n WHERE i.id=n.idImagen AND n.idNoticia=" + noticia.IdNoticia;

        try
        {
            OdbcCommand cmd = new OdbcCommand(query, con);
            cmd.CommandType = CommandType.Text;
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Imagen imagen = new Imagen();
                imagen.IdImagen = dr.GetInt32(0);
                imagen.PathBig = dr.GetString(1);
                imagen.PathSmall = dr.GetString(2);
                imagen.Portada = dr.GetBoolean(3);
                imagen.PathMedium = dr.GetString(4);
                listaImagenes.Add(imagen);
            }
        }
        catch (Exception e)
        {
            throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message);
        }

        return listaImagenes;
    }
示例#2
0
        /// <summary>
        /// Obtiene la lista de empleados de la nómina
        /// </summary>
        /// <returns></returns>
        public List <Catalogo> CatalogoEmpleados()
        {
            List <Catalogo> Empleados = new List <Catalogo>();

            try
            {
                string QueryString = @"SELECT idEmpleado, NombreEmpleado FROM vwEmpleados";
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        Catalogo Empleado;
                        while (reader.Read())
                        {
                            Empleado             = new Catalogo();
                            Empleado.id          = reader.GetInt32(0);
                            Empleado.Descripcion = reader.GetString(1);
                            Empleados.Add(Empleado);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.EscribeLog("Error: ElsabonDA.CatalogoEmpleados - " + ex.Message);
            }
            return(Empleados);
        }
        public void setClientDBData(string clientId)
        {
            try
            {
                #region get values from DB and set the clients prop
                string cmd = "SELECT smoker_code, smoker_value, smoking_stat_assess_date, ss_demographics_dict_8_code,ss_demographics_dict_8_value " +
                                "FROM patient_current_demographics " +
                                "WHERE PATID = ?";
                using (OdbcConnection con = new OdbcConnection(ConnectionStringPM))
                {
                    con.Open();
                    using (var command = new OdbcCommand(cmd, con))
                    {
                        command.Parameters.Add(new OdbcParameter("PATID", clientId));

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                this.client.Id = clientId;
                                this.client.LGBT = reader["ss_demographics_dict_8_code"].ToString();
                                this.client.SmokerStatus = reader["smoker_code"].ToString();
                            }
                        }
                    }
                }
                status = "ClientId = " + clientId + "<br>" +
                         "LGBT = " + this.client.LGBT + "<br>" +
                         "Smoker = " + this.client.SmokerStatus + "<br>";
                #endregion
            }
            catch (Exception exc) { status = exc.ToString(); }
        }
        public MyConnectionOdbc(DataProvider dataProvider)
            : base(dataProvider)
        {
            // Crear conexión
            OdbcConnection conn = new OdbcConnection(@"provider = sqlodbc;
                data source = .\sqlexpress; trusted connection = yes;");

            try
            {
                // Abrir conexión
                conn.Open();
                Console.WriteLine("Conexión establecida.");

                // Detalles de la conexión
                this.DetallesConexion(conn);
            }
            catch (OdbcException ex)
            {
                // Desplegar excepción o error
                Console.WriteLine("Error: " + ex.Message + ex.StackTrace);
            }
            finally
            {
                //Cerrar la conexión
                conn.Close();
            }
        }
示例#5
0
        /// <summary>
        /// Permite obtener las vacaciones tomadas según el tiempo de antiguedad
        /// </summary>
        /// <param name="EmpleadoId"></param>
        /// <param name="FechaInicio"></param>
        /// <param name="FechaFin"></param>
        /// <returns></returns>
        public static int GetTakenVacations(int EmpleadoId, int Antiguedad)
        {
            int TakenVacations = 0;

            try
            {
                string QueryString = "SELECT SUM(dias_descanso) FROM vacaciones_empleado WHERE id = " + EmpleadoId + " AND antiguedad = " + Antiguedad;
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            TakenVacations = reader.GetInt32(0);
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                //Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
            }
            return(TakenVacations);
        }
示例#6
0
文件: Program.cs 项目: budlion/DSTPRJ
        static void Main(string[] args)
        {
            //"DRIVER={MySQL ODBC 5.2w Driver};SERVER=localhost;DATABASE=config;UID=root;PASSWORD=liu355dq;"

            string MyConString = "DRIVER={MySQL ODBC 5.2w Driver};" +

                               "SERVER=localhost;" +

                               "DATABASE=g_db;" +

                               "UID=root;" +

                               "PASSWORD=liu355dq;";

            OdbcConnection myconnection = new OdbcConnection(MyConString);
            OdbcCommand cmd = new OdbcCommand("select * from g_operator");
            cmd.Connection = myconnection;
            myconnection.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader[1]);
            }
            myconnection.Close();

            Console.ReadKey();
        }
示例#7
0
        public static System.Data.Odbc.OdbcDataReader ExecuteReaderQueryOnDB(string sSQL, string s_ConnectionString)
        {
            System.Data.Odbc.OdbcConnection QConnection = null;
            System.Data.Odbc.OdbcCommand    QCommand    = null;
            try
            {
                QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
                QCommand    = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);

                QConnection.Open();

                return(QCommand.ExecuteReader());
            }
            finally
            {
                if (QCommand != null)
                {
                    QCommand.Dispose();
                }
                QCommand = null;
                if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed)
                {
                    QConnection.Close();
                }
                if (QConnection != null)
                {
                    QConnection.Dispose();
                }
                QConnection = null;
            }
        }
    public string getPredictionWS(string currency_name)
    {
        string connString = "DSN=MySQLODBC;UID=root;PWD=admin";
        OdbcConnection conn = new OdbcConnection(connString);
        OdbcCommand comm = new OdbcCommand();
        comm.Connection = conn;
        comm.CommandTimeout = 300;
        comm.CommandText = "select CurrencyID from currency where CurrencyName = '" + currency_name + "'";
        conn.Open();

        OdbcDataAdapter da = new OdbcDataAdapter(comm);
        DataSet ds = new DataSet();
        da.Fill(ds);

        DataRow row = ds.Tables[0].Rows[0];
        string currency_id = row[0].ToString();

        comm.CommandText = "select predictsignal,predictprice,recognitionrate from currencyprediction where CurrencyID = " + currency_id + "";
        OdbcDataAdapter da1 = new OdbcDataAdapter(comm);
        DataSet ds1 = new DataSet();
        da1.Fill(ds1);

        string predictionarray;
        predictionarray = ds1.Tables[0].Rows[0].ItemArray[0] + "," + ds1.Tables[0].Rows[0].ItemArray[1] + "," + ds1.Tables[0].Rows[0].ItemArray[2];

        return predictionarray;
    }
示例#9
0
        private void SAP_EditObj1251(System.Collections.Specialized.NameValueCollection P, HttpResponse httpResponse)
        {
            System.Data.Odbc.OdbcConnection conn = HELPERS.NewOdbcConn_FORCE();
            IAuthRow1251 engine1251 = new IAuthRow1251(conn);

            int IDobjtoedit = int.Parse(P["IDentit"]);

            returnGetAuthRow1251 curobj =
                engine1251.GetAuthRow1251(IDobjtoedit);

            if (
                (curobj.RangeLow == P["c_u_RangeLow"])
                &&
                (curobj.RangeHigh == P["c_u_RangeHigh"])
                )
            {
                httpResponse.Write("No change was noted, so no action was performed.");
                return;
            }


            engine1251.SetAuthRow1251
                (IDobjtoedit, P["c_u_RangeLow"], P["c_u_RangeHigh"], curobj.SAPauthObjID, curobj.SAPauthFieldID, curobj.TcodeAssignmentSetID, curobj.SAProleID,
                curobj.EditStatus | 8 /*modified*/);
        }
示例#10
0
        private void SAP_ToggleDeleteStatusOf1251row(System.Collections.Specialized.NameValueCollection P, HttpResponse httpResponse)
        {
            System.Data.Odbc.OdbcConnection conn = HELPERS.NewOdbcConn_FORCE();
            IAuthRow1251 engine1251 = new IAuthRow1251(conn);

            int IDobjtoedit = int.Parse(P["IDentit"]);

            returnGetAuthRow1251 curobj =
                engine1251.GetAuthRow1251(IDobjtoedit);

            int newEditStatus;

            if (0 != (curobj.EditStatus & 4))
            {
                newEditStatus = curobj.EditStatus & (8 + 2 + 1);
            }
            else
            {
                newEditStatus = curobj.EditStatus + 4;
            }

            engine1251.SetAuthRow1251
                (IDobjtoedit, curobj.RangeLow, curobj.RangeHigh, curobj.SAPauthObjID, curobj.SAPauthFieldID, curobj.TcodeAssignmentSetID, curobj.SAProleID,
                newEditStatus);
        }
示例#11
0
        private void Connect(string Database)
        {
            string Server   = "MyPC\\SQLEXPRESS";
            string Username = "******";
            string Password = "";

            ConStr  = "Driver={SQL Server};";
            ConStr += "Server=" + Server + ";";
            ConStr += "User ID=" + Username + ";";
            ConStr += "Password="******";";
            ConStr += "Initial Catalog=" + Database + ";";
             
                OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);

            try
            {
                Console.WriteLine("Openning connection...\r\n");
                if (OdbcCon.State == ConnectionState.Closed)
                {
                    OdbcCon.Open();
                }
                Console.WriteLine("ODBC Connection State = " + OdbcCon.State + "\r\n");
            }
            catch (System.Data.Odbc.OdbcException Ex)
            {
                Console.WriteLine(Ex.Message + "\r\n");
                Console.WriteLine("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message);
            }
        }
示例#12
0
        public static OdbcDataReader getODBC()
        {
            string         strConn = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=False;Dbq=";//Driver驱动程序,ReadOnly=False必须有,否则插入数据会报错,Dbq
            OpenFileDialog file    = new OpenFileDialog();

            file.Filter           = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            file.Multiselect      = false;
            if (file.ShowDialog() == DialogResult.Cancel)
            {
                return(null);
            }
            var path = file.FileName;

            strConn += path;
            System.Data.Odbc.OdbcConnection odbcConn = new System.Data.Odbc.OdbcConnection(strConn);//ODBC连接
            try
            {
                odbcConn.Open();                                                                         //打开连接
                string sSQL = "SELECT * FROM [appstore_games$]";                                         //注意格式,SQL语句查询表格所有的数据
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(sSQL, odbcConn); //初始化ODBC命令
                mConn = odbcConn;
                // comm.ExecuteNonQuery();//执行SQL语句
                return(command.ExecuteReader());
            }
            catch (Exception excp)
            {
                throw excp;
            }
        }
示例#13
0
        /// <summary>
        /// Método que permite obtener el nivel de estructura de un empleado
        /// </summary>
        /// <param name="EmpleadoId"></param>
        /// <returns></returns>
        public string ObtenerNivelEstructura(int EmpleadoId)
        {
            string Response = string.Empty;

            try
            {
                string QueryString = @"SELECT NivelDepartamento
                                         FROM vwEmpleados
                                        WHERE idEmpleado = '" + EmpleadoId.ToString() + "'";
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Response = reader.GetString(0);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.EscribeLog("Error: ElsabonDA.ObtenerNivelEstructura - " + ex.Message);
            }
            return(Response);
        }
示例#14
0
        /// <summary>
        /// Permite valorar si el empleado tiene justificado el día ingresado
        /// </summary>
        /// <param name="EmpleadoId"></param>
        /// <param name="Fecha"></param>
        /// <returns></returns>
        public bool GetJustificacion(int EmpleadoId, DateTime Fecha)
        {
            bool DiaJustificado = false;

            try
            {
                string QueryString = @"EXEC stp_GetJustificacionFalta " + EmpleadoId + ", '" + Fecha.ToString("yyyyMMdd") + "'";
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            DiaJustificado = reader.GetBoolean(0);
                        }
                        reader.Close();
                        reader.Dispose();
                    }
                    connection.Close();
                    connection.Dispose();
                }
            }
            catch (Exception ex)
            {
                Log.EscribeLog("Error: ElsabonDA.GetJustificacion - " + ex.Message);
            }
            return(DiaJustificado);
        }
示例#15
0
        /// <summary>
        /// SELECT compania AS id, razon_social AS compania FROM companias
        /// </summary>
        /// <returns></returns>
        public List <Catalogo> CatalogoCompanias()
        {
            List <Catalogo> Companias = new List <Catalogo>();

            try
            {
                string QueryString = @"SELECT compania AS id, razon_social AS compania FROM companias";
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        Catalogo compania;
                        while (reader.Read())
                        {
                            compania             = new Catalogo();
                            compania.id          = reader.GetInt32(0);
                            compania.Descripcion = reader.GetString(1);
                            Companias.Add(compania);
                        }
                        compania             = new Catalogo();
                        compania.id          = 1000;
                        compania.Descripcion = "TODAS";
                        Companias.Add(compania);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.EscribeLog("Error: ElsabonDA.CatalogoCompanias - " + ex.Message);
            }
            return(Companias);
        }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string[] strArray = new string[5] { "Delhi", "Mumbai", "Kolkata", "Chennai", "Chandigarh" };
        foreach (string str in strArray)
        {
           OdbcConnection oledbConn = new OdbcConnection("DSN=exceldb");
        try
        {
            // Open connection
            oledbConn.Open();

            // Create OleDbCommand object and select data from worksheet Sheet1
            OdbcCommand cmd = new OdbcCommand("SELECT * FROM [Details$]", oledbConn);
            //cmd.Parameters.AddWithValue("@city",str);where city=@city
            // Create new OleDbDataAdapter
            OdbcDataAdapter oleda = new OdbcDataAdapter();

            oleda.SelectCommand = cmd;

            // Create a DataSet which will hold the data extracted from the worksheet.
            // DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(dt);
            /*      if(dt.Rows.Count>0)
                  {
                       ID = dt.Rows[0]["ID"].ToString(); //Where ColumnName is the Field from the DB that you want to display
                       name= dt.Rows[0]["Name"].ToString();
                       Address = dt.Rows[0]["Address"].ToString();
                       EmailAddress = dt.Rows[0]["emailaddress"].ToString();
                       if(EmailAddress!=null)
                       {
                        if(EmailAddress==)
                       }
                       EmailContent = dt.Rows[0]["emailcontent"].ToString();
                   }
              */
            DataView dv = new DataView(dt);
            // dv.Sort = "emailcontents";
            // dv.Sort = "Name";

            // Bind the data to the GridView
            Grdexcel.DataSource = dv;
            Grdexcel.DataBind();
            // cmd = new OdbcCommand("delete FROM [ter$] where Address like 'M%'", oledbConn);
            // cmd.ExecuteNonQuery();
            //cmd.CommandType
        }
        catch (Exception ex)
        {
            Alert.Show("Sorry");
        }
        finally
        {
            // Close connection
            oledbConn.Close();
        }
        }
    }
        private void btnSignIn_Click(object sender, EventArgs e)
        {
            OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
            OdbcCommand cmd = new OdbcCommand("SELECT EMPLOYEEPASSWORD FROM EMPLOYEE WHERE LASTNAME = '" + txtUserName.Text + "'AND EMPLOYEETYPE = 'Foreman';", Conn);
            Conn.Open();
            Object EmpPass = cmd.ExecuteScalar();
            cmd.Dispose();
            Conn.Close();

            if (txtUserName.Text == "")
            {
                MessageBox.Show("User name is empty!");
            }
            else if (Convert.ToString(EmpPass) == "")
            {
                MessageBox.Show("Password is empty!");
            }
            else if (Convert.ToString(EmpPass) != txtPassword.Text)
            {
                MessageBox.Show("Wrong password! please type again!");
            }
            else
            {
                string Emp = Convert.ToString(EmpPass);
                var frm = new Form1(Emp);
                frm.ShowDialog();
                //this.Visible = false
            }
        }
示例#18
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        string ConnectionString = "";

//        ConnectionString =
//@"DRIVER=MapR Drill ODBC Driver;
//AdvancedProperties={CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=local;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5};
//Catalog=DRILL;
//Schema=;
//AuthenticationType=No Authentication;
//ConnectionType=Direct;
//Host=192.168.2.113;
//Port=31010;
//";
        // ConnectionString = "DRIVER=MapR Drill ODBC Driver;Catalog=DRILL;Schema=hbase;ConnectionType=Direct;Host=192.168.2.113;Port=31010";
        ConnectionString = "DRIVER =MapR Drill ODBC Driver; AdvancedProperties ={ CastAnyToVarchar = true; HandshakeTimeout = 5; QueryTimeout = 180; TimestampTZDisplayTimezone = local; ExcludedSchemas = sys,INFORMATION_SCHEMA; NumberOfPrefetchBuffers = 5}; Catalog = DRILL; Schema =; AuthenticationType = No Authentication; ConnectionType = ZooKeeper; ZKQuorum = dataNode04:2181,dataNode03: 2181,dataNode02: 2181,nameNode: 2181; ZKClusterID = drillbits1; ";

        System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(ConnectionString);
        conn.Open();
        var cmd = conn.CreateCommand();

        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = "SELECT * FROM `hbase`.`tab` ";
        System.Data.Odbc.OdbcDataAdapter ad = new System.Data.Odbc.OdbcDataAdapter(cmd);
        DataSet myds = new DataSet();

        ad.Fill(myds);
    }
 internal OdbcTransaction(OdbcConnection connection, IsolationLevel isolevel, OdbcConnectionHandle handle) {
     OdbcConnection.VerifyExecutePermission();
     
     _connection = connection;
     _isolevel   = isolevel;
     _handle = handle;
 }
示例#20
0
        public string[] GetMostPopularTags()
        {
            string[] result = new string[25];
            //SELECT *, count(*) as ct FROM `3dr`.`associatedkeywords`  inner join (select * from 3dr.keywords) as r on associatedkeywords.keywordid = r.id group by keyword order by ct desc limit 25
            System.Data.Odbc.OdbcConnection conn = GetConnection();
            {
                List <ContentObject> objects = new List <ContentObject>();

                using (var command = conn.CreateCommand())
                {
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = "{SELECT *, count(*) as ct FROM `3dr`.`associatedkeywords`  inner join (select * from 3dr.keywords) as r on associatedkeywords.keywordid = r.id group by keyword order by ct desc limit 25}";

                    using (var resultSet = command.ExecuteReader())
                    {
                        int i = 0;
                        while (resultSet.Read())
                        {
                            result[i] = resultSet["keyword"].ToString() + "." + resultSet["ct"].ToString();
                            i++;
                        }
                    }
                }

                return(result);
            }
        }
 /// <summary>
 /// initialization casting for InitializeDataAccess()
 /// </summary>
 /// <param name="type"></param>
 /// <param name="ConnectionString"></param>
 /// <param name="Query"></param>
 private void castProvider(ProviderType type, string ConnectionString, string Query = null)
 {
     switch (type)
     {
         case ProviderType.Oledb:
             conn = new OleDbConnection(ConnectionString);
             cmd = new OleDbCommand(Query, (OleDbConnection)conn);
             da = new OleDbDataAdapter();
             break;
         case ProviderType.Odbc:
             conn = new OdbcConnection(ConnectionString);
             cmd = new OdbcCommand(Query, (OdbcConnection)conn);
             da = new OdbcDataAdapter();
             break;
         case ProviderType.SqlClient:
             conn = new SqlConnection(ConnectionString);
             cmd = new SqlCommand(Query, (SqlConnection)conn);
             da = new SqlDataAdapter();
             break;
         //case ProviderType.OracleClient:
         //    conn = new OracleConnection(ConnectionString);
         //    cmd = new OracleCommand(Query,(OracleConnection)conn);
         //    break;
     }
 }
示例#22
0
        public string[] GetMostPopularDevelopers()
        {
            string[] result = new string[15];

            System.Data.Odbc.OdbcConnection conn = GetConnection();
            {
                List <ContentObject> objects = new List <ContentObject>();

                using (var command = conn.CreateCommand())
                {
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = "{Select sponsorname, count(sponsorname) as ct from contentobjects where sponsorname != ''   group by sponsorname union Select developername, count(developername) as ct from contentobjects where developername != '' group by developername union Select artistname, count(artistname) as ct from contentobjects where artistname != ''  group by artistname order by ct desc limit 15}";

                    using (var resultSet = command.ExecuteReader())
                    {
                        int i = 0;
                        while (resultSet.Read())
                        {
                            result[i]  = resultSet["sponsorname"].ToString();
                            result[i] += " (" + resultSet["ct"].ToString() + ")";
                            i++;
                        }
                    }
                }

                return(result);
            }
        }
示例#23
0
        /// <summary>
        /// Obtiene el parámetro máximo de antiguedad de la tabla de vacaciones
        /// </summary>
        /// <returns></returns>
        public static int MaximaAntiguedadVacaciones(int EmpleadoId)
        {
            int AntVacaciones = 0;

            try
            {
                string QueryString = @"SELECT MAX (antiguedad) FROM vacaciones_empleado WHERE id =" + EmpleadoId.ToString();
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AntVacaciones = Convert.ToInt32(reader.GetString(0));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //
            }
            return(AntVacaciones);
        }
示例#24
0
        static internal List <SqlSchemaInfo> GetSchemaInfo(System.Data.Odbc.OdbcConnection con, List <SqlSchemaInfo> schemaList)
        {
            try
            {
                DataTable tbl = con.GetSchema(System.Data.Odbc.OdbcMetaDataCollectionNames.Tables);
                foreach (DataRow row in tbl.Rows)
                {
                    SqlSchemaInfo ssi = new SqlSchemaInfo();

                    ssi.Type = "TABLE";
                    ssi.Name = (string)row["table_name"];
                    schemaList.Add(ssi);
                }
                tbl = con.GetSchema(System.Data.Odbc.OdbcMetaDataCollectionNames.Views);
                foreach (DataRow row in tbl.Rows)
                {
                    SqlSchemaInfo ssi = new SqlSchemaInfo();

                    ssi.Type = "VIEW";
                    ssi.Name = (string)row["table_name"];
                    schemaList.Add(ssi);
                }
            }
            catch
            {
            }
            return(schemaList);
        }
示例#25
0
        public static DbConnection GetOpenConnection(AdoProviderType providerType, string connStr, int retryCount, int retryDelay)
        {
            DbConnection conn = null;
            switch (providerType)
            {
                case AdoProviderType.SqlProvider:
                    conn = new SqlConnection(connStr);
                    break;
                case AdoProviderType.OleProvider:
                    conn = new OleDbConnection(connStr);
                    break;
                case AdoProviderType.OdbcProvider:
                    conn = new OdbcConnection(connStr);
                    break;
                default:
                    throw new Exception("This provider type is not supported.");
            }


            if (conn != null)
            {
                conn.Open();
                int attemptCount = 0;
                while (conn.State!=ConnectionState.Open && ++attemptCount < retryCount)
                {
                    System.Threading.Thread.Sleep(retryDelay);
                    conn.Open();
                }
                if (conn.State != ConnectionState.Open)
                    throw new Exception("Unable to open connection using specified connection string.");
            }
            return conn;
        }
示例#26
0
        /// <summary>
        /// Llena una tabla SQL con los resultados de una consulta ODBC (Fox o relativity)
        /// </summary>
        /// <param name="keyOdbc">Clave que identifica la cadena de conexión ODBC</param>
        /// <param name="sentenciaOdbc">Sentencia odbc que realiza la consulta</param>
        /// <param name="keySql">Clave que identifica la cadena de conexión SQL</param>
        /// <param name="TablaDestino">Tabla que se llenará con la consulta</param>
        /// <returns>Resultado del proceso </returns>
        public string sqlInsert(string keyOdbc, string sentenciaOdbc, string keySql, string TablaDestino)
        {
            string         strConnString = ConfigurationManager.ConnectionStrings[keyOdbc].ConnectionString;
            OdbcConnection OdbcCon       = new System.Data.Odbc.OdbcConnection();

            OdbcCon.ConnectionString = strConnString;
            OdbcCon.Open();
            OdbcCommand    commandSourceData = new OdbcCommand(sentenciaOdbc, OdbcCon);
            OdbcDataReader reader            = commandSourceData.ExecuteReader();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConectaSql(keySql)))
            {
                bulkCopy.DestinationTableName = TablaDestino;

                try
                {
                    bulkCopy.WriteToServer(reader);
                    return("OK");
                }
                catch (Exception ex)
                {
                    return("NO" + ex.Message);
                }
                finally
                {
                    reader.Close();
                }
            }
        }
示例#27
0
    /// <summary>
    /// retorna una imagen
    /// </summary>
    /// <returns></returns>
    public static Imagen getImagen(OdbcConnection con, int idImagen)
    {
        DataSet ds = new DataSet();
        Imagen imagen = new Imagen();
        try
        {
            OdbcCommand cmd = new OdbcCommand("SELECT i.id, i.pathBig, i.pathSmall, i.portada, i.pathMedium FROM imagen i WHERE i.id=" + idImagen, con);
            cmd.CommandType = CommandType.Text;
            OdbcDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                imagen.IdImagen = dr.GetInt32(0);
                imagen.PathBig = dr.GetString(1);
                imagen.PathSmall = dr.GetString(2);
                imagen.Portada = dr.GetBoolean(3);
                imagen.PathMedium = dr.GetString(4);
            }
        }
        catch (Exception e)
        {
            throw new SportingException("Ocurrio un problema al intentar obtener la imagen. " + e.Message);
        }
        return imagen;
    }
示例#28
0
        /// <summary>
        /// Realiza una consulta de tipo ODBC mediante sentencia Sql estandar.
        /// </summary>
        /// <param name="NomTabla">Nombre de la tabla que almacena el resultado de la consulta.</param>
        /// <param name="sentenciaSql">cadena con la sentencia sql que realiza la consulta</param>
        /// <param name="key">Nombre que identifica el string de conexión</param>
        /// <returns>Retorna un dataset con los resultados de la consulta</returns>
        public DataSet ConsultaOdbc(string NomTabla, string sentenciaSql, string key)
        {
            DataSet        dsBasico      = new DataSet();
            string         strConnString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
            OdbcConnection OdbcCon       = new System.Data.Odbc.OdbcConnection();

            OdbcCon.ConnectionString = strConnString;
            OdbcCon.Open();

            OdbcDataAdapter DataAdapterOdbc = new OdbcDataAdapter();

            try
            {
                DataAdapterOdbc = new OdbcDataAdapter(sentenciaSql, OdbcCon);
                DataAdapterOdbc.Fill(dsBasico, NomTabla);
                return(dsBasico);
            }
            catch (Exception r)
            {
                string m = r.Message;
                return(dsBasico);
            }
            finally
            {
                DataAdapterOdbc.Dispose();
                OdbcCon.Close();
            }
        }
		private static void SetupSqlServerOdbc(Cfg.Configuration cfg)
		{
			var connStr = cfg.Properties[Cfg.Environment.ConnectionString];

			using (var conn = new OdbcConnection(connStr.Replace("Database=nhibernateOdbc", "Database=master")))
			{
				conn.Open();

				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = "drop database nhibernateOdbc";

					try
					{
						cmd.ExecuteNonQuery();
					}
					catch(Exception e)
					{
						Console.WriteLine(e);
					}

					cmd.CommandText = "create database nhibernateOdbc";
					cmd.ExecuteNonQuery();
				}
			}
		}
        private void btnOpenFile_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection();
                string         fileName = "DUTY.DBF";
                //OpenFileDialog openFileDialog = new OpenFileDialog();
                //if (openFileDialog.ShowDialog() == true)
                //{
                //lblStatus.Content = "File conversion is in progress.. Please wait..!!";
                obdcconn.ConnectionString = GetConnectionString(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
                obdcconn.Open();
                OdbcCommand oCmd = obdcconn.CreateCommand();
                oCmd.CommandText = "SELECT * FROM " + System.Configuration.ConfigurationSettings.AppSettings["InputPath"];

                /*Load data to table*/

                DataTable dt1 = new DataTable();
                dt1.Load(oCmd.ExecuteReader());

                string currentPath = Path.GetFullPath(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
                currentPath = Directory.GetParent(currentPath).FullName + "\\";
                obdcconn.Close();

                WriteDataToTemplate(currentPath, fileName, dt1);
                //DataSetIntoDBF(fileName, currentPath, dt1);
                //lblStatus.Content = "File conversion completed, file is in " + dir + "\\" + fileName;
                //}
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
示例#31
0
        private String addUser()
        {
            try
            {
                Contract contract = new Contract();
                String cxnString = "Driver={SQL Server};Server=HC-sql7;Database=REVINT;Trusted_Connection=yes;";
                using (OdbcConnection dbConnection = new OdbcConnection(cxnString))
                {
                    //open OdbcConnection object
                    dbConnection.Open();

                    OdbcCommand cmd = new OdbcCommand();

                    cmd.CommandText = "{CALL [REVINT]." + contract.getSchema() + ".[OCP_addUser]( ?, ?, ?, ? )}";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Connection = dbConnection;

                    cmd.Parameters.Add("@hawkId", OdbcType.NVarChar, 400).Value = txtHealthcareID.Text;
                    cmd.Parameters.Add("@administrator", OdbcType.Bit).Value = chkAdmin.IsChecked;
                    cmd.Parameters.Add("@name", OdbcType.NVarChar, 400).Value = txtName.Text;
                    cmd.Parameters.Add("@numRecords", OdbcType.Int);
                    cmd.Parameters["@numRecords"].Direction = System.Data.ParameterDirection.ReturnValue;

                    cmd.ExecuteNonQuery();

                    dbConnection.Close();

                    return cmd.Parameters["@numRecords"].Value.ToString();
                }
            }
            catch (Exception)
            {
                return null;
            }
        }
示例#32
0
        public IEnumerable <ContentObject> GetContentObjectsByField(string field, string value, string identity)
        {
            System.Data.Odbc.OdbcConnection conn = GetConnection();
            {
                List <ContentObject> objects = new List <ContentObject>();

                using (var command = conn.CreateCommand())
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.CommandText = "{CALL GetContentObjectsByField(?,?,?)}";
                    command.Parameters.AddWithValue("field", field);
                    command.Parameters.AddWithValue("val", value);
                    command.Parameters.AddWithValue("uname", identity);

                    using (var resultSet = command.ExecuteReader())
                    {
                        while (resultSet.Read())
                        {
                            var co = new ContentObject();
                            FillContentObjectLightLoad(co, resultSet);
                            objects.Add(co);
                        }
                    }
                }

                return(objects);
            }
        }
示例#33
0
    protected void Page_Load(object sender, EventArgs e)
    {
        //   string ImageId = System.Convert.ToString(Request.QueryString["name"]);

        int articleID = System.Convert.ToInt32(Request.QueryString["textid"]);

        string MyConString = ConfigurationSettings.AppSettings["connectionString"];

        OdbcConnection connection = new OdbcConnection(MyConString);

        string sqlNews1 = "select * FROM latestnews";
        OdbcCommand commandNews1 = new OdbcCommand(sqlNews1, connection);
        connection.Open();
        OdbcDataReader dr = commandNews1.ExecuteReader(CommandBehavior.CloseConnection);

        if (dr.Read())
        {
            Response.ContentType = dr["date"].ToString();
            Response.ContentType = dr["title"].ToString();
            Response.ContentType = dr["content"].ToString();

        }
        connection.Close();

        string selectNews = "SELECT date, title, content FROM latestnews ORDER BY date DESC";
        OdbcCommand command = new OdbcCommand(selectNews, connection);
        OdbcDataAdapter adapter = new OdbcDataAdapter(command);
        DataSet ds = new DataSet();

        adapter.Fill(ds);

        DataList1.DataSource = ds;
        DataList1.DataBind();
    }
示例#34
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="query"></param>
        /// <param name="count"></param>
        /// <param name="start"></param>
        /// <returns></returns>
        public IEnumerable <ContentObject> GetObjectsWithRange(string query, int count, int start, SortOrder order, string username)
        {
            List <ContentObject> objects = new List <ContentObject>();

            System.Data.Odbc.OdbcConnection conn = GetConnection();
            {
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = query;
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("s", start);
                    command.Parameters.AddWithValue("length", count);
                    command.Parameters.AddWithValue("sortOrder", (order == SortOrder.Descending) ? "DESC" : "ASC");
                    command.Parameters.AddWithValue("uname", username);

                    using (var resultSet = command.ExecuteReader())
                    {
                        while (resultSet.Read())
                        {
                            var co = new ContentObject();

                            FillContentObjectLightLoad(co, resultSet);
                            LoadReviews(co, conn);
                            objects.Add(co);
                        }
                    }
                }
                if (_TotalObjects < 0)
                {
                    setContentObjectCount(conn, username);
                }
            }

            return(objects);
        }
        public static OdbcConnection CreatConnection()
        {
            string driver = @"SQL Server";
            string servidor = @"EMYLLAINE";
            string baseDeDados = @"Itens";
            //string usuario = "sa";
            //string senha = "123";

            StringBuilder cnn = new StringBuilder();
            cnn.Append("driver=");
            cnn.Append(driver);
            cnn.Append(";server=");
            cnn.Append(servidor);
            cnn.Append(";database=");
            cnn.Append(baseDeDados);
            cnn.Append(";Trusted_Connection=yes");
            //cnn.Append(usuario);
            //cnn.Append(";pwd=");
            //cnn.Append(senha);

            OdbcConnection odbcCon = new OdbcConnection(cnn.ToString());
            odbcCon.Open();

            return odbcCon;
        }
示例#36
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="co"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public bool RemoveSupportingFile(ContentObject co, string filename)
        {
            System.Data.Odbc.OdbcConnection connection = GetConnection();
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "{CALL DeleteSupportingFile(?,?)}";
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("pid", co.PID);
                    command.Parameters.AddWithValue("filename", filename);
                    var result = command.ExecuteReader();

                    List <SupportingFile> remove = new List <SupportingFile>();

                    foreach (SupportingFile t in co.SupportingFiles)
                    {
                        if (t.Filename == filename)
                        {
                            remove.Add(t);
                        }
                    }
                    foreach (SupportingFile t in remove)
                    {
                        if (t.Filename == filename)
                        {
                            co.SupportingFiles.Remove(t);
                        }
                    }
                }
            }
            return(true);
        }
示例#37
0
    public string getQuotesWS(string currency_name)
    {
        string connString = "DSN=MySQLODBC;UID=root;PWD=admin";
        OdbcConnection conn = new OdbcConnection(connString);
        OdbcCommand comm = new OdbcCommand();
        comm.Connection = conn;

        comm.CommandText = "select CurrencyID from currency where CurrencyName = '" + currency_name + "'";
        conn.Open();

        OdbcDataAdapter da = new OdbcDataAdapter(comm);
        DataSet ds = new DataSet();
        da.Fill(ds);

        DataRow row = ds.Tables[0].Rows[0];
        string currency_id = row[0].ToString();

        comm.CommandText = "select currentprice from currencyprediction where CurrencyID = " + currency_id + "";
        OdbcDataAdapter da1 = new OdbcDataAdapter(comm);
        DataSet ds1 = new DataSet();
        da1.Fill(ds1);

        DataRow row1 = ds1.Tables[0].Rows[0];
        string currentQuotePrice = row1[0].ToString();

        return currentQuotePrice;
    }
示例#38
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="co"></param>
        public void InsertContentRevision(ContentObject co)
        {
            System.Data.Odbc.OdbcConnection conn = GetConnection();
            {
                int id = 0;


                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "{CALL InsertContentObject(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); }";
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    var properties = co.GetType().GetProperties();
                    foreach (var prop in properties)
                    {
                        if (prop.PropertyType == typeof(String) && prop.GetValue(co, null) == null)
                        {
                            prop.SetValue(co, String.Empty, null);
                        }
                    }
                    FillCommandFromContentObject(co, command);
                    id = int.Parse(command.ExecuteScalar().ToString());
                }
                SaveKeywords(conn, co, id);
            }
        }
示例#39
0
        public string GR_STR(string si_id, OdbcConnection conn, string rcx)
        {
            string gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR7;
            if (rcx == "RC0" || rcx == "RC2" || rcx == "RC4") { }
            else if (rcx == "RC1" || rcx == "RC3") { gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR9; }

            else if (rcx == "RCFIE") { gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR15; }
            else if (rcx == "RCFJH") gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR31;
            using (OdbcDataReader dr = new OdbcCommand(String.Format("select gi  from field_item where fi_id='{0}' and not gi is null;", si_id), conn).ExecuteReader())
            {
                if (dr.Read())
                {
                    int gi=-1;
                    if (int.TryParse(dr[0].ToString(),out gi) && gi > 0)
                    {
                        using (OdbcDataReader dr0 = new OdbcCommand(String.Format("select gr_rc, name,classno,gr_period,gr_date  from gt_item where gi='{0}'", gi), conn).ExecuteReader())
                        {
                            if (dr0.Read())
                            {
                                return string.Format(gr_format, "GR", dr0[0], dr0[1], dr0[2], dr0[3], dr0[4]);
                            }
                        }
                    }
                }
            }

            using (OdbcDataReader dr = new OdbcCommand(String.Format("select gr_rc, name,classno,gr_period,gr_date  from field_gr where fi_id='{0}'", si_id), conn).ExecuteReader())
            {
                if (dr.Read())
                {
                    return string.Format(gr_format, "GR", dr[0], dr[1], dr[2], dr[3], dr[4]);
                }
            }
            return "";
        }
示例#40
0
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        public IEnumerable <ContentObject> GetAllContentObjects()
        {
            System.Data.Odbc.OdbcConnection conn = GetConnection();
            {
                List <ContentObject> objects = new List <ContentObject>();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "{CALL GetAllContentObjects()}";
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    using (var resultSet = command.ExecuteReader())
                    {
                        while (resultSet.Read())
                        {
                            var co = new ContentObject();

                            FillContentObjectFromResultSet(co, resultSet);
                            LoadReviews(co, conn);
                            co.Keywords = LoadKeywords(conn, co.PID);
                            objects.Add(co);
                        }
                    }
                }

                return(objects);
            }
        }
示例#41
0
文件: CAD.cs 项目: walter016/dise2015
        public static OdbcConnection ObtenerConexion()
        {
            mySqlConeccion = new OdbcConnection("Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=colchoneria;User=root;Password=; Option=3;");
            mySqlConeccion.Open();

            return mySqlConeccion;
        }
示例#42
0
        /// <summary>
        /// Initialises a new instance of the OdbcCommand class
        /// </summary>
        /// <param name="commandText">T-SQL statement or name of the command being executed</param>
        /// <param name="connection">Open connection to the database</param>
        public OdbcCommand(String commandText, odbc.OdbcConnection connection)
            : base(commandText)
        {
            this.commandType = CommandType.Text;

            Connection = connection;
        }
示例#43
0
        // Check esistenza account
        public bool GetAccountExists(String username, out String error)
        {
            bool exists = false;
            error = "";

            try
            {
                // Connesione db
                OdbcConnection odbc = new OdbcConnection("DSN=nwn_master_server");
                odbc.Open();

                using (OdbcCommand dbcmd = odbc.CreateCommand()) {
                    // Query
                    String query = "SELECT username FROM " + TABLE_ACCOUNTS + " WHERE username='******'";
                    dbcmd.CommandText = query;

                    using (OdbcDataReader dbreader = dbcmd.ExecuteReader()) {
                        while(dbreader.Read()){
                            if ((String)dbreader["username"] == username)
                                exists = true;
                        }

                        dbreader.Close();
                    }
                }

                odbc.Close();
            }
            catch (Exception e) {
                error = e.StackTrace;
            }

            // Exit
            return exists;
        }
示例#44
0
 /// <summary>
 /// connect to the database
 /// </summary>
 public void ConnectToDatabase()
 {
     if (connectString == "")
     {
         throw new Exception("Connection string cannot be empty.");
     }
     else
     {
         try
         {
             if (dbConnection == null)
             {
                 dbConnection = new OdbcConnection(connectString);
                 dbConnection.Open();
                 return;
             }
             if (dbConnection.State.ToString() == "Closed")
             {
                 dbConnection.Open();
             }
         }
         catch (Exception ex)
         {
             throw new Exception("Database Connect Errors: " + ex.Message);
         }
     }
 }
示例#45
0
        /// <summary>
        /// Permite obtener el puesto de un empleado
        /// </summary>
        /// <param name="EmpleadoId"></param>
        /// <returns></returns>
        public string ObtenerPuestoEmpleado(int EmpleadoId)
        {
            string Puesto = string.Empty;

            try
            {
                string QueryString = @"SELECT Puesto
                                         FROM vwEmpleados
                                        WHERE idEmpleado = " + EmpleadoId.ToString();
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Puesto = reader.GetString(0);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //
            }
            return(Puesto);
        }
示例#46
0
    protected void TestMySQLSettings_Click(object sender, EventArgs e)
    {
        if (MySQLPassword2.Text != MySQLPassword1.Text)
        {
            BlankStatus();
            testMySQLStatus.Text = "Passwords Must Match";
            return;
        }
        string ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=" + MySQLIP.Text + ";Port=" + MySQLPort.Text + ";Database=3dr;User="******";Password="******";Option=3";

        using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(ConnectionString))
        {
            try
            {
                conn.Open();
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    BlankStatus();
                    SaveMySQLSettings.Enabled = true;
                    testMySQLStatus.Text      = "Test Successful";
                }
            }
            catch (Exception ex)
            {
                BlankStatus();
                SaveMySQLSettings.Enabled = false;
                testMySQLStatus.Text      = "Test Failed: " + ex.Message;
            }
        }
        Updatecheckmarks();
    }
示例#47
0
        /// <summary>
        /// Permite obtener la cantidad de días que el empleado ha gozado de vacaciones en el periodo definido entre FechaInicio y FechaFin
        /// </summary>
        /// <param name="empleado"></param>
        /// <param name="FechaInicio"></param>
        /// <param name="FechaFin"></param>
        /// <returns></returns>
        public static int GetTakenVacations(int EmpleadoId, DateTime FechaInicio, DateTime FechaFin)
        {
            int TakenVacations = 0;

            try
            {
                string QueryString = "SELECT SUM(VACACIONES.dias_descanso) AS VacacionesTomadas " +
                                     "	    FROM vacaciones_empleado VACACIONES "+
                                     "INNER JOIN vwEmpleados EMPLEADOS " +
                                     "        ON VACACIONES.id = EMPLEADOS.idEmpleado " +
                                     "	 	 AND VACACIONES.compania = EMPLEADOS.idCompania "+
                                     "     WHERE EMPLEADOS.idEmpleado = " + EmpleadoId +
                                     "	 	 AND VACACIONES.fecha_inicio BETWEEN '"+ FechaInicio.ToString("yyyy-MM-dd HH:mm:ss") + "' " +
                                     "		 AND '"+ FechaFin.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            TakenVacations = reader.GetInt32(0);
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                //Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
            }
            return(TakenVacations);
        }
示例#48
0
 /// <summary>
 /// Executa algum comando de inserção, exclusão ou edição
 /// </summary>
 /// <param name="sql">o sql a ser executado</param>
 public void ExecutaComando(string sql)
 { 
     //Usa a classe de conexão apenas nesse bloco
     using(OdbcConnection conn = new OdbcConnection(this.ConnectionString))
     {
         try
         {   
             //Abre conexão com o servidor de banco
             conn.Open();
             //instancia um objeto de comando, passando como parametro o sql e a conexão para o construtor
             OdbcCommand comm = new OdbcCommand(sql, conn);
             //Executa o comando
             comm.ExecuteNonQuery();
         }
         catch (Exception ex)
         {   
             //Atira uma nova excessão, caso dê algum erro
             throw new Exception(ex.Message);
         }
         finally
         {   
             //Sempre fechará conexão, independente se der erro ou não
             conn.Close();
         }
     }
 }
示例#49
0
        /// <summary>
        /// Permite obtener la fecha de antigüedad del empleado ingresado como parámetro
        /// </summary>
        /// <param name="EmpladoId"></param>
        /// <returns>DateTime</returns>
        public static DateTime GetAntiquity(int EmpleadoId)
        {
            DateTime AntiquityDate = new DateTime();

            try
            {
                string QueryString = "SELECT fecha_antiguedad " +
                                     "	    FROM Empleados EMP "+
                                     "INNER JOIN vwEmpleados VISTA " +
                                     "		  ON VISTA.idEmpleado = EMP.id "+
                                     "		 AND VISTA.idCompania = EMP.compania "+
                                     "	   WHERE VISTA.idEmpleado = "+ EmpleadoId;
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
                using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
                {
                    command.Connection = connection;
                    connection.Open();
                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            AntiquityDate = reader.GetDateTime(0);
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                //Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
            }
            return(AntiquityDate);
        }
示例#50
0
        /// <summary>
        /// Executa uma query de busca com retorno de dados
        /// </summary>
        /// <param name="sql">comando sql para aquela ação</param>
        /// <returns>Um dataset contendo os dados buscados</returns>
        public DataSet BuscaDados(string sql)
        {   
            //Instancia um objeto de dataset para retornar
            DataSet dados = new DataSet();

            using(OdbcConnection conn = new OdbcConnection(this.ConnectionString))
            {
                try
                {               
                    //Abre a conexão com o servidor de banco
                    conn.Open();
                    //Cria um objeto de reader do odbc, para retornar dados
                    OdbcDataAdapter read = new OdbcDataAdapter(sql, conn);

                    //Preenche o DataSet instanciado no incio do codigo
                    read.Fill(dados);
                }
                catch (Exception ex)
                {
                    //Lança um erro, caso aconteça
                    throw new Exception(ex.Message);
                }
                finally
                {
                    //Fecha conexão
                    conn.Close();
                }
            }

            return dados;
        }
示例#51
0
 public Database(string connectionString)
 {
     OC = new OdbcConnection(connectionString);
     dbCommand = new OdbcCommand();
     dbCommand.Connection = OC;
     OC.Open();
 }
示例#52
0
        public static DataTable ExecuteQuery(string dsnConnection, string query, params object[] queryParams)
        {
            try
            {
                using (var connection = new OdbcConnection(dsnConnection))
                {
                    connection.Open();
                    var queryCommand = connection.CreateCommand();
                    queryCommand.CommandText = query;
                    queryCommand.CommandTimeout = 1;

                    var i = 0;
                    foreach (var queryParam in queryParams)
                    {
                        queryCommand.Parameters.Add("@p" + i, OdbcType.DateTime).Value = queryParam;
                        i++;
                    }

                    var dataSet = new DataSet();
                    var da = new OdbcDataAdapter(queryCommand);
                    da.Fill(dataSet);
                    return dataSet.Tables[0];
                }
            } catch(Exception ex)
            {
                Logging.LogError(1, "Error executing query {0} on connection {1} message is {2}", query, dsnConnection, ex.Message);
                throw;
            }
        }
示例#53
0
        public UserInfo GetUser(string userId)
        {
            string queryString = "SELECT * FROM UserDetail WHERE UserId Like '" + userId + "'";
            using (OdbcConnection con = new OdbcConnection(ConnectionString))
            {
                con.Open();
                using (OdbcCommand cmd = new OdbcCommand(queryString, con))
                {
                    OdbcDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        UserInfo user = new UserInfo()
                        {
                            UserId = reader.GetString(reader.GetOrdinal("UserId")),
                            EmailId = reader.GetString(reader.GetOrdinal("EmailId")),
                            LastLogIn = reader.GetDateTime(reader.GetOrdinal("LastLogIn")),
                            RegisteredTimestamp = reader.GetDateTime(reader.GetOrdinal("RegisteredTimestamp"))
                        };

                        return user;
                    }
                }
            }

            return null;
        }
示例#54
0
 public static void Load_Sport_TXT_TO_DB_ByTableNameAndFieldName(string table_name, string field_names)
 {
     OpenFileDialog ofd = new OpenFileDialog();
     if (ofd.ShowDialog() == DialogResult.OK)
     {
         using (OdbcConnection conn = new OdbcConnection(Basic_HTB_Info.Conn_Str))
         {
             conn.Open();
             //MessageBox.Show(String.Format("TRUNCATE TABLE {0};", table_name));
             //using (OdbcCommand cmd = new OdbcCommand(String.Format("TRUNCATE TABLE {0};", table_name), conn))
             //{
              //   cmd.ExecuteNonQuery();
             //}
             StreamReader sr = new StreamReader(ofd.FileName,Encoding.Default);
             string line = null;
             while ((line = sr.ReadLine()) != null)
             {
                 string[] str_a = line.Split(';');
                 String[] fieldnames = field_names.Split(',');
                 if (str_a.Length == fieldnames.Length)
                 {
                     Lib.inc_cmd(field_names, table_name, str_a, conn);
                 }
             }
             conn.Close();
         }
     }
 }
    protected void Button1_Click1(object sender, EventArgs e)
    {
        int i;
        string htmlstr = "<table style='width:100%;'>";
        string data;
        string sql = TextBox1.Text;
        string constr = Session["connection"].ToString();
        OdbcConnection cn = new OdbcConnection(constr);
        cn.Open();
        OdbcCommand cmd = new OdbcCommand(sql, cn);
        OdbcDataReader reader;
        reader = cmd.ExecuteReader();
        int col_count = reader.FieldCount;
        int count1;

        while (reader.Read())
        {
            htmlstr += "<tr>";
            count1 = col_count;
            i = 0;
            while (count1 != 0)
            {
                data = reader[i].ToString();
                htmlstr += "<td class='style2'>" + data + "</td>";
                count1--;
                i++;
            }
            htmlstr += "</tr>";
        }
        htmlstr += "</table>";
        table_data.InnerHtml = htmlstr;
    }
        /* queries for all students (IDs, First names, last names)
         * whose ID numbers contain the values provided by the student W# field
         * in the current form. */
        private void button1_Click(object sender, System.EventArgs e)
        {
            //Refresh the listview.
            searchView.Items.Clear();
            string SearchTerm = searchField.Text;
            System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(dsnSource);

            try
            {
                connection.Open();
                System.Data.Odbc.OdbcCommand command = connection.CreateCommand();
                command.CommandText = "SELECT ID, FIRST_NAME, LAST_NAME FROM G_PERSON WHERE ID like '%" + SearchTerm + "%'";
                System.Data.Odbc.OdbcDataReader Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    String id = Reader["ID"].ToString();
                    String fname = Reader["FIRST_NAME"].ToString();
                    String lname = Reader["LAST_NAME"].ToString();
                    ListViewItem item = new ListViewItem(new[] { id, fname, lname });
                    searchView.Items.Add(item);

                }
                connection.Close();
            } catch (Exception s) {
                MessageBox.Show("Could not connect at this time. Please try again later.");
                MessageBox.Show(s.ToString());
            }
        }
示例#57
0
 public HandleSystem(OdbcConnection dbConn, StreamReader reader, StreamWriter writer)
 {
     this.dbConn = dbConn;
     this.reader = reader;
     this.writer = writer;
     BeginHandling();
 }
示例#58
0
    public bool AddTermCode(TermCode termCode)
    {
        bool result = false;

        using (OdbcConnection connection = new OdbcConnection(connectionString))
        {
            using (OdbcCommand command = new OdbcCommand())
            {
                command.Connection = connection;
                command.CommandText = "{CALL TermCode_insert(?,?)}";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@AssessmentOID", termCode.AssessmentOID);
                command.Parameters.AddWithValue("@TermCodeName", termCode.TermCodeName);
                connection.Open();
                int n = command.ExecuteNonQuery();
                if (n > 0)
                    result = true;
                else
                    result = false;
            }
        }

        return result;
    }
 protected void Button1_Click(object sender, EventArgs e)
 {
     string htmlstr = "<h2>&nbsp;</h2>&nbsp;<table style='width:100%;'>";
     string cust_id = DropDownList1.SelectedItem.ToString();
     string name;
     string sex;
     string dob;
     string relationship;
     string constr = Session["connection"].ToString();
     OdbcConnection cn = new OdbcConnection(constr);
     cn.Open();
     string sql = "select * from NOMINEE where cust_id = '" + cust_id + "';";
     OdbcCommand cmd = new OdbcCommand(sql, cn);
     OdbcDataReader reader;
     reader = cmd.ExecuteReader();
     while (reader.Read())
     {
         name = reader["name"].ToString();
         sex = reader["sex"].ToString();
         dob = reader["dob"].ToString();
         relationship = reader["relationship"].ToString();
         htmlstr += "<tr><td class='style2'>Name:</td><td class='style1'>" + name + "</td><tr><td class = 'style2'>Sex:</td><td class = 'style1'>" +sex + "</td></tr><tr><td class='style2'> Relationship:</td><td class = 'style1'>" + relationship + "</td></tr><tr><td class='style2'>DOB:</td><td class = 'style1'>" + dob + "</td></tr><tr><td class='style2'>Customer ID :</td><td class = 'style1'>" + cust_id + "</td></tr>";
     }
     table_data.InnerHtml = htmlstr;
 }
示例#60
0
        // CSVファイルをDataTableに取得する
        private DataTable getDtFromCsv(string csvDir, string csvFileName)
        {
            DataTable dt = null;

            //接続文字列
            string         conString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvDir + ";Extensions=asc,csv,tab,txt;";
            OdbcConnection con       = new System.Data.Odbc.OdbcConnection(conString);

            string commText = "SELECT * FROM [" + csvFileName + "]";

            using (OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(commText, con))
            {
                //DataTableに格納する
                using (dt = new DataTable())
                {
                    da.Fill(dt);
                }
            }

            if (dt == null)
            {
                throw new Exception("CSVのテーブル展開に失敗しました。");
            }
            return(dt);
        }