Como Derrubar Conexões do PostgreeSQL(Timeout while getting a connection from pool)

Estava com o seguinte problema ocorrendo intermitente quando executava uma Function usando C# com NpgSQL: “Timeout while getting a connection from pool”, identifiquei que estava com problema para fechar as conexões ao executar a função e corrigi, porém ainda existia conexões pendentes travadas, segue comandos para executar para derrubar conexões ativas do PostgreSQL . Segue Link de referencia, eu testei nas duas versões e funcina normalmente: LINK

Até versão PostgreSQL 9.1:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'Nome_Banco_Dados'
  AND procpid <> pg_backend_pid();

Após versão PostgreSQL 9.2:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'Nome_Banco_Dados'
  AND pid <> pg_backend_pid();

Outra dica importante é desabilitar o Pooling na connection string que pode causar um problema em concorrencia, mas isso depende do cenario da sua aplicação, o Pooling de conexão é para fazer um cache de conexões de banco de dados mantido para que as conexões podem ser reutilizados, quando são necessárias futuras solicitações para o banco de dados. Ligado ajuda a melhorar o desempenho de executar comandos numa base de dados, porém é necessário gerenciar isso para não travar as conexões.

Anúncios

Como executar procedure usando ADO.Net

O que é mais visto hoje em dia é a utilização de ORM(NHibernate ou Enitity Framework), na minha opnião é uma forma mais estruturada e melhor para fazer a camada de dados, porém atualmente ainda existe muitos sistemas que utilizam a camada de dados com ADO.Net, tanto para apenas manutenção de sistemas quanto para sistemas novos. Por isso acho interessante resaltar como utilizar as classes nativas do ADO.Net para executar Funções no SqlServer  ou Stored Procedures.

Quando se executa comandos SQL diretamente com SqlCommand não é necessário definir o tipo de comando, mas para Stored Procedures é necessário definir a propriedade CommandType para StoredProcedure assim informando como o SqlCommand deve tratar o comando.

Quando a procedure é de retorno simples, quando nao tem parametros de output, é possivel fazer uma chamada utilizando o ExecuteScalar para trazer o retorno, como mostrado abaixo:

SqlCommand command = new SqlCommand("NomeProcedure",con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Parametro1",SqlDbType.VarChar,250,"DescricaoParametro"));
command.Parameters[0].Value = "ValorParametro1";
command.Parameters.AddWithValue("@Parametro2" , "ValorParametro2" );
int retorno =  (int)command.ExecuteScalar();

Quando a procedure retorna valores de output é necessário no parametro do comando colocar a direção do parametro(ParameterDirection), nesse parametro não é necessário definir nenhum valor de entrada, mas apos a execução da Procedure, o parametro criado irá ter o valor de retorno, como mostrado abaixo:

SqlCommand command = new SqlCommand("NomeProcedure",con);
command.Parameters.Add(new SqlParameter("@Parametro1", SqlDbType.VarChar, 250, ParameterDirection.Output, false, 0, 10, "sourceColumn", DataRowVersion.Default, null));
command.Parameters.AddWithValue("@Parametro2" , "ValorParametro2" );
command.ExecuteNonQuery();
string retorno2 = command.Parameters[0].Value.ToString();

Como copiar uma linha de um DataTable para outro

Se voce está tentando copiar uma linha de um DataTable para outro e não está conseguindo aqui está a solução:

Quando você tenta executar esse código:

DataTable dt = new DataTable();
dt.Columns.Add("Coluna1");
dt.Columns.Add("Coluna2");

DataRow dr = dt.NewRow();
dr["Coluna1"] = "Valor 1";
dr["Coluna2"] = "Valor 2";

dt.Rows.Add(dr);
// Copia apenas o schema do DataTable
DataTable dt2 = dt.Clone();
// Aqui ocorre erro
dt2.Rows.Add(dr);

Esse codigo gera o erro: This row already belongs to this table.

Esse erro ocorre, pois você esta tentando colocar uma unica linha em dois DataTable, e um DataRow pode pertencer a apenas um DataTable.

Bom Agora vamos a Solução, no objeto DataTable existe um metodo chamado ImportRow(DataRow dr)  esse metodo cria um novo objeto DataRow, sem ligação com nenhum DataTable, e adicionar esse novo DataRow ao DataTable que chamou o metodo… Agora vamos a codificação da Solução

DataTable dt = new DataTable();
dt.Columns.Add("Coluna1");
dt.Columns.Add("Coluna2");

DataRow dr = dt.NewRow();
dr["Coluna1"] = "Valor 1";
dr["Coluna2"] = "Valor 2";

dt.Rows.Add(dr);
// Copia apenas o schema do DataTable
DataTable dt2 = dt.Clone();
// Agora funciona normalmente
dt2.ImportRow(dr);

Copiar Dados de um DataTable para outro

Para fazer uma copia identica de um DataTable voce precisa utilizar o método Copy() do DataTable, se deseja apeas copiar  schema das tabelas sem os dados o metodo que deve ser utilizado é o Clone().

Apenas para exemplificar o uso.

DataTable dt = new DataTable();
dt.Columns.Add("Coluna1");
dt.Columns.Add("Coluna2");

DataRow dr = dt.NewRow();
dr["Coluna1"] = "Valor 1";
dr["Coluna2"] = "Valor 2";

DataTable dt2 = dt.Clone();// Aqui a linha não sera copiada, apenas as colunas

DataTable dt3 = dt.Copy();// Aqui o DataTable inteiro sera copiado

IMPORTANTE:

Apenas definir um novo objeto DataTable e igualar ao seu DataTable diretamente, apenas ocorrera uma copia de de referencia. Isso e um problema pois quando voce alterar um DataTable o outro tambem sera altera.

Exemplo:

DataTable dt = new DataTable();
 dt.Columns.Add("Coluna1");
 dt.Columns.Add("Coluna2");

DataRow dr = dt.NewRow();
 dr["Coluna1"] = "Valor 1";
 dr["Coluna2"] = "Valor 2";

// dessa forma ao invez de criar um novo objeto, a unica coisa que sera feita
 // sera colocar a mesma referencia a um objeto em outra variavel
 DataTable dt2 = dt;

 dt2.Rows.Clear(); // e quado altera como e o mesmo objeto as duas variaveis ficam iguais.
 int quantidadeLinhas = dt.Rows.Count; // isso ira retornar 0

Definir nomes aparentes diferente dos nomes das colunas de um DataTable

Como o titulo não está auto explicativo vou explicar o cenario que esse post se aplica.

Você tem um DataTable montado e voce quer deixar o titulo da coluna que irá aparecer para o usuário com um nome(“Nome do Usuário”), mas você quer deixar o nome da coluna do DataTable com o nome igual do banco de dados(“str_usuario”). Se você precisa fazer isso sem definir as colunas diretamente em um controle Asp.Net ou WinForms, então leia o post inteiro.

Em uma DataTable é necessário definir as colunas antes de começar a adicionar linhas nele, essas colunas são representadas por objetos DataColumn.

Um objeto DataColumn possui as propriedades ColumnName e Caption.

A propriedade ColumnName define o nome da coluna que será utilizada na codificação para acessar essa coluna, exemplo:


DataTable dt = new DataTable("Nome_Tabela");
 DataColumn dc = dt.Columns.Add();
 dc.ColumnName = "str_usuario";
 string nomeColuna = dc.Caption; //Aqui será retornado o str_usuario e se voce colocar esse DataTable em um Grid ocorrerá o mesmo
DataColumn dc1 = dt.Columns["str_usuario"];

A propriedade Caption define o nome que irá aparecer para no controle que você definir esse DataTable como DataSource, se não for definido nada, por padrão, essa propriedade retorna o valor da propriedade ColumnName, exemplo:


DataTable dt = new DataTable("Nome_Tabela");
 DataColumn dc = dt.Columns.Add();
 dc.ColumnName = "str_usuario";
 dc.Caption = "Nome do Usuário";
DataColumn dc1 = dt.Columns["str_usuario"];

Se voce colocar esse trecho de código em um sistema e definir esse DataTable em qualquer controle que aceita DataSource, voce irá reparar que o nome das colunas que irá aparecer no visual, será o Caption, e quando for necessário acessar a coluna em seu DataTable voce pode buscar pelo nome da coluna mesmo.

Adicionar coluna Indentity em um DataTable

Em um objeto datatable as vezes é necessário criar uma colunu Indentity ou auto-increment para simular uma coluna no banco de dados, para efetuar é necessário apenas tres propriedades da sua DataColumn:

  • AutoIncrement: propriedade booleana para definir se a coluna será ou não autoincrement.
  • AutoIncrementSeed: propriedade numérica para definir o número inicial do auto increment.
  • AutoIncrementStep: propriedade numérica para definir o número que será somado a cada nova linha
Exemplo:
DataTable dt = new DataTable();
DataColumn dc1 = dt.Columns.Add("Id", typeof (int));
dc1.AutoIncrement = true;
dc1.AutoIncrementSeed = 1;
dc1.AutoIncrementStep = 1;
O que esse código vai gerar um DataTable com uma coluna chamada Id que a cada adição de linha, essa coluna irá obter um novo numero gerado automaticamente.A primeira linha desse DataTable irá ter o valor 1 a segunda 2 e assim segue.
IMPORTANTE
O que isso pode gerar de problema para voce:
Digamos que voce tenha um DataTable da tabela carro do seu banco de dados
-O Banco de dados já possui um carro com id 4 e vc adicionou um carro e com a auto-numeração  do DataTable ele está com o id 4, quando for adicionar esse registro em seu DataTable irá ocorrer um erro.
O que é recomendado que faça:
Definir o Seed e o Step para -1 assim não existirá conflito pois o banco de dados não gera numero negativos
DataTable dt = new DataTable();
DataColumn dc1 = dt.Columns.Add("Id", typeof (int));
dc1.AutoIncrement = true;
dc1.AutoIncrementSeed = -1;
dc1.AutoIncrementStep = -1;

Iniciando com ADO.Net

ADO.NET são várias classes que expõe serviços de acesso à dados para o programador .NET. O ADO.NET fornece um rico conjunto de componentes para a criação de aplicações distribuídas e compartilhamento de dados. O ADO.NET é parte integrante do NET Framework,que dá acesso relacional ao XML e à aplicação de dados. ADO.NET suporta uma variedade de

A estrutura do ADO.NET é dois tipos de classes, as classes conectadas e as classes desconectadas.

Classes desconectadas: São classes que não são fortemente ligadas com a conexão com a base de dados, assim essas informações podem ser criadas de qualquer outra fonte de dado, exemplo: Arquivo Binario, Arquivo Xml ou informações geradas pelo codigo.Exemplos:DataTable e DataSet.

Classes conectadas: São classes que são fortemente ligadas a base de dados, exemplo são as classes de conexão, classes de comando, entre outras.Exemplos: SqlConnection, DBConnection, SqlCommand.