using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Configuration;

namespace Andrey01
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            //1. retorna a configuração do banco atual para persistência
            string bancoAtual = ConfigurationManager.AppSettings["bancoAtual"];

            //2. retorna o provider/string de conexão na seção <connectionstrings> 
            // de acordo com a chave da conexão recuperada
            string provider = ConfigurationManager.ConnectionStrings[bancoAtual].ProviderName;
            string strConexao = ConfigurationManager.ConnectionStrings[bancoAtual].ConnectionString;


            //3. configura o factory com o provider recuperado
            DbProviderFactory factory = DbProviderFactories.GetFactory(provider);

            //4. cria um objeto do tipo DbConnection representando a conexão com o banco de daddos
            DbConnection conn = factory.CreateConnection();

            //5. configura a string de conexão
            conn.ConnectionString = strConexao;

            //6. abre o banco de dados correspondente
            conn.Open();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string bancoAtual = ConfigurationManager.AppSettings["bancoAtual"];
            string provider = ConfigurationManager.ConnectionStrings[bancoAtual].ProviderName;
            string strConexao = ConfigurationManager.ConnectionStrings[bancoAtual].ConnectionString;

            DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = strConexao;
            conn.Open();

            //cria e configura um objeto do tipo DbCommand retornado pelo FactoryProvider
            DbCommand command = factory.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = "SELECT * from PRODUCTS";
            command.Connection = conn;

            //cria e configura um objeto do tipo DbDataReader contendo o retorno dos dados da SELECT
            DbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                comboBox1.Items.Add(reader[1].ToString());
                //código para recuperar os valores retornados
            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            string bancoAtual = ConfigurationManager.AppSettings["bancoAtual"];
            string provider = ConfigurationManager.ConnectionStrings[bancoAtual].ProviderName;
            string strConexao = ConfigurationManager.ConnectionStrings[bancoAtual].ConnectionString;

            DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = strConexao;
            conn.Open();

            //cria e configura um objeto do tipo DbCommand retornado pelo FactoryProvider
            DbCommand command = factory.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = "INSERT INTO Categories (CategoryName) values ('YA')";
            command.Connection = conn;

            //executa a operação
            command.ExecuteNonQuery();

        }
    }
}