Consulta no banco de dados com parâmetro e dataset
Bom dia!
Estou fazendo uma consulta no banco de dados conforme o código abaixo:
public Usuario GetUsuarioPorLogin(string login, string senha)
{
Usuario usuario = new Usuario(); StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT *");
sql.AppendLine(" FROM TB_USUARIO");
sql.AppendLine(" WHERE");
sql.AppendLine(" vcLogin = @login");
sql.AppendLine(" AND vcSenha = @senha"); DataSet dsTemp = null;
try
{
_cmd.Parameters.Clear();
_cmd.Parameters.Add(_conexao.CreateParameter("login", DbType.String, ParameterDirection.Input, 15, login));
_cmd.Parameters.Add(_conexao.CreateParameter("senha", DbType.String, ParameterDirection.Input, 30, senha)); _cmd.CommandText = sql.ToString();
_cmd.CommandType = CommandType.Text; _cmd.Connection = _conexao.CreateConnection(_cmd);
dsTemp = _conexao.CreateDataSet(_cmd, Conexao.CommandType.SelectCommand);
if (dsTemp.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsTemp.Tables[0].Rows)
{
usuario.Id = int.Parse(dr["id_usuario"].ToString());
usuario.NomeUsuario = dr["vcNome"].ToString();
usuario.Login = dr["vcLogin"].ToString();
usuario.Senha = dr["vcSenha"].ToString();
usuario.Email = dr["vcEMail"].ToString();
usuario.Ativo = bool.Parse(dr["btAtivo"].ToString());
}
}
}
catch
{
_conexao.RollbackTransaction(_cmd);
throw;
}
finally
{
_conexao.DisposeDataSet(dsTemp);
} return usuario;
} O usuário existe no banco conforme login e senha que eu passo, porém, não me retorna nenhuma linha de registro. Ao executar o profile do SQL Server, eu obtive a instrução abaixo: exec sp_executesql N'SELECT *
FROM vw_Usuarios
WHERE
vcLogin = @login
AND vcSenha = @senha
',N'@login nvarchar(15),@senha nvarchar(30)',@login=N'carlos',@senha=N' Beœü¯øh ùsö@' Se eu mudar o tipo de nvarchar para varchar, ele apresenta os dados do usuário que estão no banco (na tabela os campos estão como "char", do mesmo tamanho informado nos parâmetros). Ao fazer uma pesquisa sobre o assunto, alguns mencionam que isso é devido ao DbType.String. O código do método que cria parâmetro segue abaixo: public DbParameter CreateParameter(string nome, DbType tipo, ParameterDirection direcao, Int32 tamanho, object valor)
{
DbParameter param = _factory.CreateParameter(); param.ParameterName = nome;
param.DbType = tipo;
param.Direction = direcao;
param.Size = tamanho;
param.Value = valor; return param;
} public DbConnection CreateConnection(DbCommand command)
{
DbConnection conn = null;
if (command.Transaction == null)
{
conn = _factory.CreateConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings[_tipoConexao].ConnectionString; if (conn.State != System.Data.ConnectionState.Open) conn.Open();
}
else
{
conn = command.Connection;
} return conn;
} public DataSet CreateDataSet(DbCommand command, CommandType commandType)
{
DataSet ds = new DataSet();
DbDataAdapter da = _factory.CreateDataAdapter();
switch (commandType)
{
case CommandType.SelectCommand: da.SelectCommand = command; break;
case CommandType.InsertCommand: da.InsertCommand = command; break;
case CommandType.UpdateCommand: da.UpdateCommand = command; break;
case CommandType.DeleteCommand: da.DeleteCommand = command; break;
}
da.Fill(ds); return ds;
} Estou usando as classes do System.Data.Common porque tenho uma classe que, conforme o provider no arquivo de configuração (connectionString), ele instância as classes do System.Data.Common
de acordo. Por exemplo, se for Sql Server, instância do DbConnection, DbCommand, DbParameter para Sql Server, e assim por diante. Então, estou tendo esse problema e até agora não consegui resolver. Alguém poderia me ajudar nesse problema?
{
Usuario usuario = new Usuario(); StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT *");
sql.AppendLine(" FROM TB_USUARIO");
sql.AppendLine(" WHERE");
sql.AppendLine(" vcLogin = @login");
sql.AppendLine(" AND vcSenha = @senha"); DataSet dsTemp = null;
try
{
_cmd.Parameters.Clear();
_cmd.Parameters.Add(_conexao.CreateParameter("login", DbType.String, ParameterDirection.Input, 15, login));
_cmd.Parameters.Add(_conexao.CreateParameter("senha", DbType.String, ParameterDirection.Input, 30, senha)); _cmd.CommandText = sql.ToString();
_cmd.CommandType = CommandType.Text; _cmd.Connection = _conexao.CreateConnection(_cmd);
dsTemp = _conexao.CreateDataSet(_cmd, Conexao.CommandType.SelectCommand);
if (dsTemp.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsTemp.Tables[0].Rows)
{
usuario.Id = int.Parse(dr["id_usuario"].ToString());
usuario.NomeUsuario = dr["vcNome"].ToString();
usuario.Login = dr["vcLogin"].ToString();
usuario.Senha = dr["vcSenha"].ToString();
usuario.Email = dr["vcEMail"].ToString();
usuario.Ativo = bool.Parse(dr["btAtivo"].ToString());
}
}
}
catch
{
_conexao.RollbackTransaction(_cmd);
throw;
}
finally
{
_conexao.DisposeDataSet(dsTemp);
} return usuario;
} O usuário existe no banco conforme login e senha que eu passo, porém, não me retorna nenhuma linha de registro. Ao executar o profile do SQL Server, eu obtive a instrução abaixo: exec sp_executesql N'SELECT *
FROM vw_Usuarios
WHERE
vcLogin = @login
AND vcSenha = @senha
',N'@login nvarchar(15),@senha nvarchar(30)',@login=N'carlos',@senha=N' Beœü¯øh ùsö@' Se eu mudar o tipo de nvarchar para varchar, ele apresenta os dados do usuário que estão no banco (na tabela os campos estão como "char", do mesmo tamanho informado nos parâmetros). Ao fazer uma pesquisa sobre o assunto, alguns mencionam que isso é devido ao DbType.String. O código do método que cria parâmetro segue abaixo: public DbParameter CreateParameter(string nome, DbType tipo, ParameterDirection direcao, Int32 tamanho, object valor)
{
DbParameter param = _factory.CreateParameter(); param.ParameterName = nome;
param.DbType = tipo;
param.Direction = direcao;
param.Size = tamanho;
param.Value = valor; return param;
} public DbConnection CreateConnection(DbCommand command)
{
DbConnection conn = null;
if (command.Transaction == null)
{
conn = _factory.CreateConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings[_tipoConexao].ConnectionString; if (conn.State != System.Data.ConnectionState.Open) conn.Open();
}
else
{
conn = command.Connection;
} return conn;
} public DataSet CreateDataSet(DbCommand command, CommandType commandType)
{
DataSet ds = new DataSet();
DbDataAdapter da = _factory.CreateDataAdapter();
switch (commandType)
{
case CommandType.SelectCommand: da.SelectCommand = command; break;
case CommandType.InsertCommand: da.InsertCommand = command; break;
case CommandType.UpdateCommand: da.UpdateCommand = command; break;
case CommandType.DeleteCommand: da.DeleteCommand = command; break;
}
da.Fill(ds); return ds;
} Estou usando as classes do System.Data.Common porque tenho uma classe que, conforme o provider no arquivo de configuração (connectionString), ele instância as classes do System.Data.Common
de acordo. Por exemplo, se for Sql Server, instância do DbConnection, DbCommand, DbParameter para Sql Server, e assim por diante. Então, estou tendo esse problema e até agora não consegui resolver. Alguém poderia me ajudar nesse problema?
Carlos Nogueira
Curtidas 0
Respostas
Fabio Mans
05/07/2010
E se você alterar o campo para nvarchar?
Dica utilize um SqlHelper, você escreve demais para um simples métodos.
Fabio
nvarchar x varchar
A grosso modo, no mundo CHAR e VARCHAR, cada caractér ocupa 1 byte. Um byte é um conjunto de 8bits e considerando todas as posições desses bits (ligado e desligado) podemos ter 256 combinações (2^8). Isso quer dizer que um byte é capaz de representar 256 combinações diferentes. Para o alfabeto americano isso é mais que suficiente, para o alfabeto latino isso também é mais que suficiente.
O problema começa quando consideramos alfabetos árabes, asiáticos, gregos, etc. Nesse caso, se formos considerar todas as letras e caractéres possíveis iremos extrapolar todas as 256 combinações que 1 byte pode representar. Para essa situações surgiu o NVARCHAR e o NCHAR. Para esses tipos de dados cada caractér ocupa 2bytes. Se um byte pode expressar 256 combinações (2^8), dois bytes podem armazenar 65536 combinações (2^16). Com essa quantidade de combinações, é possível representar qualquer caractér existente só que o custo de armazenamento fica maior.
Se você utilizar os tipos CHAR e VARCHAR e tentar armazenar determinados caractéres, o universo de caractéres disponíveis ficará restrito a collation que você escolheu. Se você tentar armazenar outro caractér que não esteja contemplado por essa collation, esse caractér será convertido para algum aproximado. Se você escolher NCHAR e NVARCHAR, então essa limitação não ocorre.
GOSTEI 0
Carlos Nogueira
05/07/2010
Oi Fabio!
Consegui resolver este problema ao alterar o código demonstrado abaixo:
public DbParameter CreateParameter(string nome, DbType tipo, ParameterDirection direcao, Int32 tamanho, object valor)
{
DbParameter param = _factory.CreateParameter(); param.ParameterName = nome; string provider = System.Configuration.ConfigurationManager.ConnectionStrings[_tipoConexao].ProviderName;
switch (provider)
{
case "System.Data.SqlClient":
switch (tipo.ToString())
{
case "String":
param.DbType = (DbType)SqlDbType.VarChar;
break;
default:
param.DbType = tipo;
break;
}
break;
default:
param.DbType = tipo;
break;
} param.Direction = direcao;
param.Size = tamanho;
param.Value = valor; return param;
} É guambiarra, eu sei, queria fazer algo melhor como o que você citou, que é alterar o tipo do campo. O problema é que ao vai haver uma discussão interna na equipe com o DBA, pois ele justamente optou por esse tipo de campo (char) devido a justificativa do armazenamento. Eu decidi postar isso porque já trabalhei com banco de dados SQL Server outras vezes desta maneira e nunca havia me deparado com este problema. Só não compreendi a questão que você mencionou ao dizer que escrevi demais para um simples método.
{
DbParameter param = _factory.CreateParameter(); param.ParameterName = nome; string provider = System.Configuration.ConfigurationManager.ConnectionStrings[_tipoConexao].ProviderName;
switch (provider)
{
case "System.Data.SqlClient":
switch (tipo.ToString())
{
case "String":
param.DbType = (DbType)SqlDbType.VarChar;
break;
default:
param.DbType = tipo;
break;
}
break;
default:
param.DbType = tipo;
break;
} param.Direction = direcao;
param.Size = tamanho;
param.Value = valor; return param;
} É guambiarra, eu sei, queria fazer algo melhor como o que você citou, que é alterar o tipo do campo. O problema é que ao vai haver uma discussão interna na equipe com o DBA, pois ele justamente optou por esse tipo de campo (char) devido a justificativa do armazenamento. Eu decidi postar isso porque já trabalhei com banco de dados SQL Server outras vezes desta maneira e nunca havia me deparado com este problema. Só não compreendi a questão que você mencionou ao dizer que escrevi demais para um simples método.
GOSTEI 0
Fabio Mans
05/07/2010
Sim, veja como fica os meus métodos, veja que escrevo bem menos que você, porque eu utilizo um SQLHelper, muito mais fácil e menos trabalho.
DataTable
public static DataTable RetornaAutenticacao(FuncionarioEntity _funcionarioEntity)
{
using (DatabaseHelper db = new DatabaseHelper())
{
db.AddParameter("@matricula", _funcionarioEntity.Matricula);
using (SqlDataReader dr = (SqlDataReader)db.ExecuteReader("DESPESAS_TESOURARIA_AUTENTICACAO", CommandType.StoredProcedure))
{
DataTable dtResult = new DataTable();
dtResult.Load(dr);
dr.Close();
return dtResult;
}
}
}
Update
public static void AtualizarAdiantamento(DespesasEntity _despesasEntity)
{
try
{
using (DatabaseHelper db = new DatabaseHelper())
{
db.AddParameter("@IDDESPESAS", _despesasEntity.IdDespesas);
db.AddParameter("@ADIANTAMENTO", _despesasEntity.Adiantamento);
db.ExecuteNonQuery("DESPESAS_TESOURARIA_ATUALIZAR_ADIANTAMENTO", CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
Fábio
DataTable
public static DataTable RetornaAutenticacao(FuncionarioEntity _funcionarioEntity)
{
using (DatabaseHelper db = new DatabaseHelper())
{
db.AddParameter("@matricula", _funcionarioEntity.Matricula);
using (SqlDataReader dr = (SqlDataReader)db.ExecuteReader("DESPESAS_TESOURARIA_AUTENTICACAO", CommandType.StoredProcedure))
{
DataTable dtResult = new DataTable();
dtResult.Load(dr);
dr.Close();
return dtResult;
}
}
}
Update
public static void AtualizarAdiantamento(DespesasEntity _despesasEntity)
{
try
{
using (DatabaseHelper db = new DatabaseHelper())
{
db.AddParameter("@IDDESPESAS", _despesasEntity.IdDespesas);
db.AddParameter("@ADIANTAMENTO", _despesasEntity.Adiantamento);
db.ExecuteNonQuery("DESPESAS_TESOURARIA_ATUALIZAR_ADIANTAMENTO", CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
Fábio
GOSTEI 0
Carlos Nogueira
05/07/2010
Hummmm, entendo! Parece interessante, vou estudar mais a respeito.
Eu vi que no seu método de exemplo para buscar e retornar dados você faz faz uso de stored procedure né? Aqui foram dadas ordens para toda consulta (SELECT) fazer uso de view, porque disseram que a view é justamente para isso e é mais rápida que stored procedure e tudo mais. Não sei se isso é a verdade plena, tentei pesquisar pelo assunto para poder argumentar, mas não consegui encontrar algo válido. Dai um dos pontos que que meu método deve ter ficado maior que o seu. Mas gostei da sua idéia de usar stored procedure!
Vi também neste seu mesmo exemplo que você retorna um DataTable para quem está consumindo o método. E se você precisar algum dia alterar a persistência para DataSet, Linq to Entities, NHibernate ou outra tecnologia, você precisa mudar tanto o seu método de exemplo quanto o código de quem está consumindo aquele método?
Ah, e uma outra dúvida, pode ser que seu exemplo seja ótimo para otimizar o que eu fiz aqui devido a um problema. Como estou tentando elaborar um padrão para a equipe poder usar aqui, no caso de transações tive problema com o mesmo DbCommand executar mais de uma vez o ExecuteReader sem fechar o anterior. Ao trabalhar com transações usando DatabaseHelper e DataTable, este problema deixa de existir, isto é, você pode executar vários ExecuteReaders na mesma transação? (sem precisar fechar o anterior. Por causa dessa desoberta, mudei de DataReader para DataSet, onde este último não apresentou este problema).
Já está anotado, e DatabaseHelper vai ser um dos itens que irei estudar para aprender e aplicar. Obrigado pelas dicas Fabio!
GOSTEI 0
Fabio Mans
05/07/2010
Então eu não uso mais, agora só trabalho com o Entity, e faz mais de 2 anos que não uso DataTable e sim Generics, te passei o exemplo com DataTable porque vi que utiliza.
Fabio
Fabio
GOSTEI 0
Carlos Nogueira
05/07/2010
Ah bacana! Valeu pela dica!
Neste caso, pode finalizar este chamado. Obrigado pela atenção!
Carlos
GOSTEI 0