2
I have an SQL database with two tables. One of them stores measures created(table 1) and other actions of these measures(table 2).
I have entered on form one datagridView
with the data of table 1 and created a column with the name "SITUACAO
" which indicates the status of the last action created for that measure. For example "late", "concluded" or "on time". This status is referenced to table 2.
The column "SITUACAO
" I filled it with a FOR
that goes all table 2 and checks the status of the action taken on that measure.
dtg.Rows[i].Cells["SITUACAO"].Value = "no prazo";
datagridView was populated correctly with the data and the column "SITUATION".
I would like to make a filter using the column "SITUATION"
`((DataTable)dtg.DataSource).DefaultView.RowFilter = string.Format("SITUACAO = 'no prazo'");`
Using the row filter
shows an error indicating that the column does not exist. I know the column "SITUATION" is not in the data source
.
- Is there any other way to make this filter?
Code
private void frmDossie_Load(object sender, EventArgs e)
{
ListarDossie();
}
private void ListarDossie()
{
try
{
novoDossie = new RegraNegocio.DossieRegraNegocio();
dtgDossie.DataSource = novoDossie.ListarDossie();
Status();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Status()
{
int linhas = dtgDossie.Rows.Count;
double dataSituacao;
DateTime dataFinal, dataInicial;
DataTable dadosTabela = new DataTable();
novoDossie = new RegraNegocio.DossieRegraNegocio();
dadosTabela = novoDossie.ListarAcoes();
int linhas2 = dadosTabela.Rows.Count;
if (linhas > 0)
{
for (int i = 0; i < linhas; i++)
{
for (int j = 0; j < linhas2; j++)
{
dataInicial = Convert.ToDateTime(dadosTabela.Rows[j]["DATA_INICIAL"]);
Convert.ToInt32(dataSituacao = DateTime.Today.Subtract(dataInicial).TotalDays);
string tabela = dadosTabela.Rows[j]["ID_DOSSIE"].ToString();
string dtg = dtgDossie.Rows[i].Cells["ID_DOSSIE"].Value.ToString();
switch (Convert.ToInt32(dadosTabela.Rows[j]["ID_TIPO_ACAO"]))
{
case 6: //Obra Concluida
if (tabela == dtg && string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString())==false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "OBRA CONCLUÍDA";
break;
case 5: //Envio CCO
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "CCO Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "CCO no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "CCO atrasada";
}
}
break;
case 4: //Construcao
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Construção Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Construção no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Construção atrasada";
}
}
break;
case 3: //Liberacao Obra
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Liberação Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Liberação no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Liberação atrasada";
}
}
break;
case 2: //Envio AF
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "AF Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Assinatura AF no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Assinatura AF atrasada";
}
}
break;
case 1: //Analise
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Análise Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Análise no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Análise atrasada";
}
}
break;
}
}
}
}
}
private void btnPesquisa_Click(object sender, EventArgs e)
{
((DataTable)dtgDossie.DataSource).DefaultView.RowFilter = string.Format("SITUACAO = 'Análise no Prazo'");
}
Layer Code Business Rule
public DataTable ListarDossie()
{
try
{
novoDossie = new AcessoDados.DossieAcessoDados();
DataTable dadosTabela = new DataTable();
dadosTabela = novoDossie.ListarDossie();
return dadosTabela;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable ListarAcoes()
{
try
{
novoDossie = new AcessoDados.DossieAcessoDados();
DataTable dadosTabela = new DataTable();
dadosTabela = novoDossie.ListarAcoes();
return dadosTabela;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
Access layer code data
public DataTable ListarDossie()
{
try
{
using (SqlConnection conexao = new SqlConnection(Conexao.stringConexao))
{
conexao.Open();
sql.Append("SELECT PROJETO.NUMERO_PROJETO, CLIENTES.NOME_CLIENTE, DOSSIE.*, TIPO_DOSSIE.TIPO_DOSSIE FROM DOSSIE");
sql.Append(" INNER JOIN PROJETO ON PROJETO.ID_PROJETO = DOSSIE.ID_PROJETO");
sql.Append(" INNER JOIN DADOS_OBRA ON PROJETO.ID_PROJETO = DADOS_OBRA.ID_PROJETO");
sql.Append(" INNER JOIN CLIENTES ON DADOS_OBRA.ID_CLIENTE = CLIENTES.ID_CLIENTE");
sql.Append(" INNER JOIN TIPO_DOSSIE ON TIPO_DOSSIE.ID_TIPO_DOSSIE = DOSSIE.ID_TIPO_DOSSIE");
comandoSql.CommandText = sql.ToString();
comandoSql.Connection = conexao;
dadosTabela.Load(comandoSql.ExecuteReader());
return dadosTabela;
}
}
catch (Exception)
{
throw new Exception("Ocorreu um erro no método ListarDossie. Caso o problema persista, entre em contato com o Administrador do sistema.");
}
}
public DataTable ListarAcoes()
{
try
{
using (SqlConnection conexao = new SqlConnection(Conexao.stringConexao))
{
conexao.Open();
sql.Append("SELECT * FROM ACAO_DOSSIE");
comandoSql.CommandText = sql.ToString();
comandoSql.Connection = conexao;
dadosTabela.Load(comandoSql.ExecuteReader());
return dadosTabela;
}
}
catch (Exception)
{
throw new Exception("Ocorreu um erro no método ListarAcoes. Caso o problema persista, entre em contato com o Administrador do sistema.");
}
}
Exists, post your code the name and structure of the tables
– Leandro Angelo
I added a piece of code to help solve my problem. See if it fits or needs some more. @Leandroangelo
– Antonio Ferreira