/// <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;
        }

        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(); }
        }

		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();
				}
			}
		}

        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();
            }
        }

    /// <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;
    }

        // 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='" + 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;
        }

        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;
        }

        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;
            }
        }

    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 [email protected]
            // 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();
        }
        }
    }

        /* 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());
            }
        }

    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_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;
    }

 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();
         }
     }
 }

        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;
            }
        }

        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;
        }

    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();
    }

 /// <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;
     }
 }

        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
            }
        }

        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();
        }

        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;
        }