Dúvida no acesso ao ADO.NET SqlClient

Daniel,   De uma lida neste artigo: https://www.devmedia.com.br/articles/viewcomp.asp?comp=14708   Ele vai elucidar todas as suas duvidas quanto ao ADO.Net.   Mas deixe-me te perguntar uma coisa, vc esta começando um projeto novo, correto? Vc esta pensando em desenvolver sua propria biblioteca de Acesso a dados?   Caso contrario, recomendo que use as bibliotecas gratuitas da MS, o DAAB, data access aplication blocks. Ja tem todos os metodos que precisa.   Abraços Att Luiz Maia

Luiz, a resposta sobre o projeto novo é sim e não.

Não porque eu continuo no meu projeto com L2E na empresa, sendo esse o único projeto em desenvolvimento aqui.

Sim porque esse projeto é para a certificação do curso de C# que eu fiz aqui na DevMedia. Como o projeto pede acesso a dados ADO.NET SqlClient, e o curso não aborda o tema, eu pedi algumas referências.

Sobre suas perguntas:

Vc esta pensando em desenvolver sua propria biblioteca de Acesso a dados?
Não estou pensando, aliás como eu faria uma biblioteca, não sei como fazer.
  Caso contrario, recomendo que use as bibliotecas gratuitas da MS, o DAAB, data access aplication blocks.Como eu utilizaria, eu também não conheço, única metodologia que conheço é L2E, meu primeiro projeto profissional.


Poderia me dar alguns exemplos de como criar, qual a diferença entre criar e utilizar um pronto?
No caso do Linq, eu crio uma classe e um método pra cada inserção, cada alteração, pra cada tabela...Com uma biblioteca própria, eu não precisaria criar um método para cada tabela?
Vou passar um exemplo do que eu faço:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using ModelDB;using System.Windows.Forms;namespace Materiais.Classes.Fiscal{    public class ClassOrigemICMS    {        //Atributos        public int codigo { get; set; }        public string descricao { get; set; }        public static string tipoTransacao = "";        public static int staticCodigo = -1;        //Gets e Sets dos atributos static        public static void setTipoTransacao(string tipo)        {            tipoTransacao = tipo;        }        public static string getTipoTransacao()        {            return tipoTransacao;        }        public static void setStaticCodigo(int cod)        {            staticCodigo = cod;        }        public static int getStaticCodigo()        {            return staticCodigo;        }        //Metodo que retorna todos regitros da tabela OrigemICMS        public static IQueryable getAllOrigemICMS()        {            try            {                Entities db = new Entities();                var results = from p in db.OrigemIcms                              select new                              {                                  CodigoOrigemIcms = p.codigo,                                  Descricao = p.descricao                              };                return results;            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);                return null;            }        }        //Insere um novo registro na tabela OrigemICMS        public void insertOrigemICMS()        {            try            {                Entities db = new Entities();                OrigemIcms newOrigemIcms = new OrigemIcms();                newOrigemIcms.codigo = this.codigo;                newOrigemIcms.descricao = this.descricao;                db.AddToOrigemIcms(newOrigemIcms);                db.SaveChanges();                MessageBox.Show(ClassMessages.SucessInsert);            }            catch(Exception ex)            {                MessageBox.Show(ClassMessages.ErrorInsert + ex.Message);            }        }        //Altera um registro existente na tabela OrigemICMS        public void updateOrigemICMS()        {            /*             * //Busca a organiza‡Æo pelo c¢digo informado para atualizar o registro            var Atividade = dataClass.Atividades.Single(pa => pa.Id == dbAtividade.Id);            //Autalizando valores do registro retornado com os valores passados            Atividade.Id = dbAtividade.Id;            Atividade.IdTipoAtividade = dbAtividade.IdTipoAtividade;            Atividade.IdPessoa = dbAtividade.IdPessoa;            Atividade.IdStatusAtividade = dbAtividade.IdStatusAtividade;            Atividade.NomeAtividade = dbAtividade.NomeAtividade;            Atividade.DscPorque = dbAtividade.DscPorque;            Atividade.DscComo = dbAtividade.DscComo;            Atividade.DscOnde = dbAtividade.DscOnde;            Atividade.DscObservacao = dbAtividade.DscObservacao;            Atividade.DscOrcamento = dbAtividade.DscOrcamento;                  dataClass.SubmitChanges();             */            try            {                Entities db = new Entities();                var upOrigemICMS = db.OrigemIcms.First(pa => pa.codigo == staticCodigo);                upOrigemICMS.codigo = this.codigo;                upOrigemICMS.descricao = this.descricao;                db.SaveChanges();            }            catch (Exception ex)            {                MessageBox.Show(ClassMessages.ErrorUpdate                               + ex.Message,                               "Alterar Origem ICMS");            }            /*              * Metodo funcionando exceto a alteracao do ID            try            {                Entities db = new Entities();                var upOrigemICMS = (from p in db.OrigemIcms                                    where p.codigo == staticCodigo                                    select p).First();                //Implantar mudança de PK                upOrigemICMS.codigo = this.codigo;                upOrigemICMS.descricao = this.descricao;                db.SaveChanges();                MessageBox.Show(ClassMessages.SucessUpdate);            }            catch (Exception ex)            {                MessageBox.Show(ClassMessages.ErrorUpdate                               + ex.Message,                               "Alterar Origem ICMS");            }*/        }        //Busca um registro a partir de seu ID        //Instancia um obj da classe ClassOrigemICMS com os valores da tabela        //Retorna o objeto instanciado        public static ClassOrigemICMS selectOrigemICMSById(int cod)        {            try            {                Entities db = new Entities();                var registroOrigemICMS = (from p in db.OrigemIcms                                          where p.codigo == cod                                          select p).First();                ClassOrigemICMS newClassOrigemICMS = new ClassOrigemICMS();                newClassOrigemICMS.codigo = registroOrigemICMS.codigo;                newClassOrigemICMS.descricao = registroOrigemICMS.descricao;                return newClassOrigemICMS;            }            catch (Exception ex)            {                MessageBox.Show(ClassMessages.ErrorSelect                               + ex.Message,                               "Selecionar Origem ICMS");                return null;            }        }        //Lista um regitro e verifica se é único.        //Unico retorna 0        //Existente retorna -1        public static int checkUniqueOrigemICMSId(int cod)        {            int unique = 0;            try            {                Entities db = new Entities();                var uniqueOrigemICMS = from u in db.OrigemIcms                                  select new                                  {                                      u.codigo                                  };                foreach (var codOrigem in uniqueOrigemICMS)                {                    if (cod == codOrigem.codigo)                    {                        unique = -1;                        break;                    }                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }            return unique;        }    }}

Daniel, Sinceramente, desenvolver uma biblioteca de acesso é bobagem, ja que a MS disponibiliza uma gratuita,
Baixe ela aqui, e dê uma lida nas referencias. http://www.microsoft.com/downloads/details.aspx?FamilyID=1643758b-2986-47f7-b529-3e41584b6ce5&displaylang=en Segue um exemplo de um projeto meu mesmo que desenvolvi para um ecommerce, note que tenho 3 camadas muito bem definidas e usando a Enterprise Libray 4.1. Baixe o projeto aqui, e por favor não repasse, ok? http://video.devmedia.com.br/discovirtual/200237/CV_COMMERCE.zip
Abraços
Att
Luiz Maia

Luiz quanto ao primeiro link, sobre acesso do ADO.NET SqlProvider, não consegui adicionar o template SQL Server Database. Não existe esse item. Seria porque o projeto é Desktop e o exemplo Web?

Gostaria apenas do básico do ADO.NET

1-Como criar a conexao com um banco existente.

2-Um método pra select.

3-Um método pra insert.

4-Um método pra update.

5-Um método pra delete.

6-Abrir a conexão para executar uma transação.

7-Fechar a conexão depois de executada a transação.

Daniel,   Não precisa executar o projeto, apenas dê uma olhada dos arquivos, mas de qualquer forma, ai vão os exemplos que me pediu:     No web.config:   <connectionStrings>
            <add
             name="AdventureWorksDB"
              providerName=
              "System.Data.SqlClient"  
              connectionString=
              "server=localhost;
              database=AdventureWorks;
              UID=user;PWD=word;" />
          </connectionStrings>
  Metodos:   Method Description ExecuteDataSet Executes a SQL query or stored procedure and returns the results in the form of a DataSet object. ExecuteNonQuery Executes a SQL query or stored procedure and returns the number of records affected. ExecuteReader Executes a SQL query or stored procedure and returns the results in the form of an IDataReader object. ExecuteScalar Executes a SQL query or stored procedure and returns the first column of the first row in the result set. LoadDataSet Executes a command and adds a new DataTable to the existing DataSet. UpdateDataSet Synchronizes the DataSet contents with the database by executing appropriate INSERT, UPDATE, and DELETE statements.
SELECT: void Page_Load(object sender, EventArgs e)
     {
       Database db = DatabaseFactory.CreateDatabase();
       string sqlCommand = "Select " + "EmployeeID,
         NationalIDNumber," + "LoginID, Title from " +
         "HumanResources.Employee ";
       DbCommand dbCommand = db.GetSqlStringCommand
         (sqlCommand);
       using (IDataReader reader = db.ExecuteReader(
         dbCommand))
       {
         gridEmployees.DataSource = reader;
         gridEmployees.DataBind();
       }               
     }
  TRANSAÇÂO: Database db =
     DatabaseFactory.CreateDatabase();
   //Two operations, one to add the order //and another to add order details
   string sqlCommand = "InsertOrder";
   DbCommand orderCommand =
     db.GetStoredProcCommand(sqlCommand);
   //Add InsertOrder parameters
   sqlCommand = "InsertOrderDetails";
   DbCommand orderDetailsCommand =
   db.GetStoredProcCommand(sqlCommand);
   //Add InsertOrderDetails parameters
   using (DbConnection connection =
     db.CreateConnection())
   {
     connection.Open();
     DbTransaction transaction =
       connection.BeginTransaction();
     try
     {
       //Execute the InsertOrder
       db.ExecuteNonQuery(orderCommand,
         transaction);
       //Execute the InsertOrderDetails   
       db.ExecuteNonQuery(
         orderDetailsCommand,
         transaction);
       //Commit the transaction
       transaction.Commit();                   
     }
     catch
     {
       //Roll back the transaction.
       transaction.Rollback();
     }
   } 
    Abraços Att Luiz Maia

Seu exemplo está um pouco diferente do que eu estou utilizando no projeto aqui Luiz.
Estou usando alguns exemplos que achei na internet. Por enquanto so fim um select.
Vou postar minha classe de acesso a dados e uma classe de validação de login. A partir delas poderia me dar um exemplo de Insert, Update e Delete?



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

namespace DevParkWA.GD
{
    public class ClassDataAccess
    {
        //Atributos estáticos
        private static SqlConnection conn; //Abre e fecha uma conexão com o Sql
        private static SqlDataReader dr = null; //Le os registros

        //Método estático que abre uma conexão e retorna essa conexão aberta
        public static void OpenConnection()
        {
            //Seta um objeto capaz de ler o App.Config
            AppSettingsReader configurationAppSettings = new AppSettingsReader();

            //Monta parcialmente a string de conexão lendo a partir do App.Config
            string DataSource = configurationAppSettings.GetValue("Data Source", typeof(String)).ToString();
            string UserId = configurationAppSettings.GetValue("User ID", typeof(String)).ToString();
            string Password = configurationAppSettings.GetValue("Password", typeof(String)).ToString();
            string InitialCatalog = configurationAppSettings.GetValue("Initial Catalog", typeof(String)).ToString();

            //Monta a string de conexão
            string ConnectionString = "Data Source=" + DataSource + ";";
            ConnectionString += "User ID=" + UserId + ";";
            ConnectionString += "Password=" + Password + ";";
            ConnectionString += "Initial Catalog=" + InitialCatalog;

            //Cria uma instância do atributo SqlConnection estático em memória
            conn = new SqlConnection();

            try
            {
                //Atribui a string de conexão e abre a conexão
                conn.ConnectionString = ConnectionString;
                conn.Open();               
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro ao conectar com o Banco de Dados\n" +
                                "Verificar a configuração no arquivo App.config\n\n" +
                                ex.Message);
                conn = null;
            }
        }

        //Método estático que fecha uma conexão aberta
        public static void CloseConnection()
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        //Método estático que recebe como parâmetro uma Query e faz um Select
        public static SqlDataReader Select(string query)
        {
            try
            {
                // define um SqlDataReader nulo
                dr = null;

                // Passa conexão e query para o objeto command
                SqlCommand cmd = new SqlCommand(query, conn);

                // Usa conexão e obtêm o resultado da consulta
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        //Método estático que fecha um DataReader aberto
        public static void CloseDataReader()
        {
            if (dr != null)
            {
                dr.Close();
            }
        }
    }
}

////////////////////////////////////////////////////////////////////////




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


namespace DevParkWA.LN
{
    public class ClassLogin
    {
        //Atributos estáticos
        public static string Login = "";//Armazena o Login de quem está utilizando a aplicação no momento
        public static string Senha = "";//Armazena a Senha de quem está utilizando a aplicação no momento

        //Método estático que retorna o Login
        public static string getLogin()
        {
            return Login;
        }

        //Método estático que seta o Login
        public static void setLogin(string user)
        {
            Login = user;
        }

        //Método estático que retorna a Senha
        public static string getSenha()
        {
            return Senha;
        }

        //Método estático que seta a Senha
        public static void setSenha(string pass)
        {
            Senha = pass;
        }

        //Método estático que valida um login e retorna o status
        // 1 Usuário e Senha OK
        // 0 Usuário não encontrado
        // -1 Senha inválida
        public static int ValidaLogin(string user, string pass)
        {
            int usuario = -1;
            int senha = -1;
            int status = -1;//usuario e senha invalidos

            //Busca o usuário
            string queryUsuario = "select * from RH.FUNCIONARIO where DSC_LOGIN = '" +
                            user + "'";
           
            //Abre a conexão
            GD.ClassDataAccess.OpenConnection();

            //Le os registros
            SqlDataReader dr = GD.ClassDataAccess.Select(queryUsuario);

            while (dr.Read())
            {
                usuario++;
                Login = dr["DSC_LOGIN"].ToString();
            }

            //Fecha o Reader
            GD.ClassDataAccess.CloseDataReader();
           
            //Testa se encontrou o usuário
            if (usuario != -1)
            {
                status = 0;//usuario ok

                //Busca a senha
                string queryUsuarioSenha = "select * from RH.FUNCIONARIO where DSC_LOGIN = '" +
                            user + "' and DSC_SENHA = '" + pass + "'";

                dr = null;
                dr = GD.ClassDataAccess.Select(queryUsuarioSenha);

                while (dr.Read())
                {
                    senha++;
                    Senha = dr["DSC_SENHA"].ToString();
                }

                //Testa se encontrou a senha
                if (senha != -1)
                {
                    status = 1;//usuario e senha ok                   
                }
            }

            //Fecha o Reader e a conexão
            GD.ClassDataAccess.CloseDataReader();
            GD.ClassDataAccess.CloseConnection();
            return status;
        }
    }
}

Daniel,   Na verdade, este exemplo, vc esta criando um classe de acesso a dados, onde vc passa a string sql e o metodo se encarrega de realizar as execuções pertinentes. Como ja havia te dito, se for usar desta forma, use a Enteprise Library 4.1, ja tem isto tudo pronto, é so chamar os metodos.   Caso não queira, ate a tarde te mando um exemplo, pois tenho que codificar, ok?   Abraços Att Luiz Maia

Ok Luis, espero até a tarde.

Agora sim eu entendi o que você quis dizer com os métodos prontos. Eu não precisaria criar um método pra conexão, depois outro pra select, que é o que estou fazendo.
Mas pra aprendizado inicial (nunca usei ADO.NET, somente LINQ) seria bom aprender a fazer tudo na unha, pois muitas empresas trabalhar dessa forma.
Fico no aguardo

Daniel, blz? Seguinte, fiz esta classe, que chamo de Superclasse, nela eu tenho todos os metodos de acesso, execução e retorno:
  http://video.devmedia.com.br/discovirtual/200237/ACESSODADOS.zip     Para execução dos mesmo, tenho alguns exemplos aqui:   using System; using System.Web; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using AcessoDados; public class Rua { SuperClasse acesso = new SuperClasse(); public Rua() { acesso.CnnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } public DataTable BuscarRuas(string palavraChave) { DataTable resultado = new DataTable(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select * from ern_rua where nom_rua like '%" + palavraChave + "%' order by 2"; acesso.ExecuteQuery(ref cmd, ref resultado); return resultado; } public DataRow BuscarRua(long codigo, long codObra) { DataTable resultado = new DataTable(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select cod_rua, nom_rua, cod_obra from ern_rua where cod_rua = " + codigo + " and cod_obra = " + codObra; acesso.ExecuteQuery(ref cmd, ref resultado); try { return resultado.Rows[0]; } catch { return null; } } public void Salvar(long codRua, string nomRua, long codObra) { try { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "insert into ern_rua (cod_rua, nom_rua, cod_obra) values ('" + codRua + "', '" + nomRua + "', '" + codObra + "')"; acesso.ExecuteQuery(ref cmd); } catch (SqlException err) { throw new Exception(err.Message); } } public int Excluir(long codigo, long codObra) { int qtdRegAfetado = 0; try { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "delete from ern_rua where cod_rua = " + codigo; acesso.ExecuteQuery(ref cmd); qtdRegAfetado = 1; } catch { qtdRegAfetado = 0; } return qtdRegAfetado; } }   De uma olhada na Biblioteca Acesso, na superclasse.cs, la tem todos os metodos que precisa, e acima estao as chamadas para os mesmos, incluindo, excluir, inserir, alterar e selecionar.   Se quiser, pode usar esta DLL em seus projetos, eu usava-a ha tempos atras, agora so uso DAAB.   Abraços Att Luiz Maia

Luiz, com base no que você me passou, posso te passar o meu projeto de certificação, pra você dar uma olhada em como ele está ficando?
Pra ver se eu estou conseguindo aplicar as 3 camadas de uma maneira legal...

Onde eu poderia upar pra postar pra você?

Bom, vou postar meu projeto de qualquer forma:

http://video.devmedia.com.br/discovirtual/225991/Projetos/DevParkWA.rar

ainda tem somente uma tela funcional, Cadastro >> Funcionário

Só rodar o scriptizinho e criar o banco, setar o app.config e dar uma olhadinha!

Blz Daniel,   Vou dar uma olhada, ok? Abraços   Ate mais Luiz Maia

Como não fiz a documentação do projeto, vale lembrar que criando a base de dados, também é criado o usuário Master senha 123456
Esse usuário tem acesso a todas as transações do sistema, inclusive as vitais:
Cadastro Funcionário, Preço.
Cancelamento de estadia.
Relatório de Movimento

Todos os outros usuários tem acesso limitado a funcionalidades básicas do sistema, como cadastro de veículo, entrada e saida de estadia.

Daniel,   Esta ficando bacana seu projeto, dei uma olhada rapida e sugiro vc alterar duas coisas, por enquanto:   1 - Não use o tipo money em banco de dados, use sempre decimal, o money tem problemas de acordo com a linguagem do servidor e gera uma serie de transtornos com ponto e virgula, entao so use Decimal para moeda, ok?   2 - Não concatene stuings usando + quando for uma string grande, use sempre o StrinBuilder, é muito mais rapido e confiavel.   Errado: string queryUsuario = "select * from RH.FUNCIONARIO where DSC_LOGIN = '" + user + "'"; Correto: StringBuilder sb = new StringBuilder(); sb.Append("select * from RH.FUNCIONARIO where DSC_LOGIN = '"); sb.Append(user + "' ");   3 - POr ultimo NUNCA concatene parametros de entrada de login e senha na query sql diretamente, dê uma pesquisada em SQL injection e vera que desta forma é muito simples burlar seu sistema de login. Sempre use SqlParameter e SQLCommand para casos de login e senha.   No mais, abraços e esta no caminho correto. Att Luiz Maia 

Opa, valeu Luiz, estou tentando aplicar muitos dos conhecimentos que eu tenho e os que aprendi.
Ainda faltou bastante coisa, como uma classe só pra mensagens de erro e seus tratamentos, outra pras mensagens básicas, como: Deseja alterar, Deletado com sucesso e etc... Pois quando alguem pede pra alterar as mensagems, ou padronizar, haja paciencia em procurar no código...
Mas não apliquei por falta de tempo mesmo.

Quanto a suas sugestões:
  1 - Não use o tipo money em banco de dados...
-Eu estou utilizando a base de dados que a DevMedia me deu pra desenvolver o projeto. Assim é que estava, inclusive com alguns error, do tipo 2  campos timestamp na mesma tabela. Acho que é proposital pra simular um ambiente real, que você recebe algo "pronto", mas na verdade tem que modificar pois não funciona. E depois ainda leva a culpa por estourar o prazo!
Mas vou modificar.
  2 - Não concatene stuings usando + quando for uma string grande, use sempre o StrinBuilder, é muito mais rapido e confiavel
Essa eu não conhecia, vou utilizar inclusive no meu projeto.
  3 - POr ultimo NUNCA concatene parametros de entrada de login e senha na query sql diretamente.
Nossa agora que eu vi, o cara pode meter um ';drop table xxx--
e dropar minha tabela!!!!!

Nossa que furada fantástica!!!

Daniel,
por falta de retorno estamos encerrando o chamado. CASO TENHA DÚVIDAS SOBRE O ASSUNTO AQUI TRATADO, BASTA POSTAR A DUVIDA AQUI MESMO QUE O CONSULTOR VOLTARÁ A LHE ATENDER E O CHAMADO SERÁ REABERTO.