public string getContasPagadoras(string processo)
{
string retorno = string.Empty;
string sql = "SELECT c.concod, c.connome, fr.foncod, fr.fonnome, cl.valor "+
"FROM webprocesso_contasliquidacao cl "+
"join conta c on c.concod = cl.idconta "+
"join fonterecurso fr on fr.foncod = c.confonterecurso "+
"join webprocesso proc on proc.id = cl.idprocesso "+
"where proc.numero =" + processo;
FbConnection conn = Persist.GetConn.getConn();
FbCommand cmd = new FbCommand(sql, conn);
FbDataAdapter dta = new FbDataAdapter(cmd);
System.Data.DataTable dt = new System.Data.DataTable();
dta.Fill(dt);
if (dt.Rows.Count == 0)
{
retorno = "Processo sem conta bancaria informada !!!";
ASPxGridView1.Visible = false;
ASPxRoundPanel1.Visible = true;
}
else {
ASPxRoundPanel1.Visible = false;
ASPxGridView1.DataSource = dt;
ASPxGridView1.DataBind();
}
return retorno;
}
public static DataSet ConsultaDataSet(string strSql)
{
abrirConn();
sqlcmd.CommandTimeout = timeout;
sqlcmd.CommandText = strSql;
DataSet dts = new DataSet();
if (!strSql.Equals(""))
{
try
{
FbDataAdapter dta = new FbDataAdapter(sqlcmd);
dta.Fill(dts);
}
catch (FbException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Message[i] + "\n");
}
Debug.Print(errorMessages.ToString());
Console.WriteLine(errorMessages.ToString());
}
}
fecharConn();
return dts;
}
public void PreencherGrid()
{
using (FbConnection connection = DaoBanco.GetInstancia().getConexao())
{
try
{
string mSQL = "SELECT * FROM USUARIOS";
FbCommand cmd = new FbCommand(mSQL, connection);
FbDataAdapter da = new FbDataAdapter(cmd);
dtUsuario = new DataTable();
da.Fill(dtUsuario);
this.gdUsuarios.DataSource = dtUsuario;
this.gdUsuarios.Columns["ID_USUARIO"].HeaderText = "Usuário";
this.gdUsuarios.Columns["ID_USUARIO"].Width = 70;
this.gdUsuarios.Columns["NOME"].HeaderText = "Nome";
this.gdUsuarios.Columns["NOME"].Width = 280;
this.gdUsuarios.Columns["LOGIN"].HeaderText = "Login";
this.gdUsuarios.Columns["SENHA"].Visible = false;
}
catch (FbException fbex)
{
MessageBox.Show("Erro de acesso a dados do Usuário : " + fbex.Message, "Erro");
}
}
}
public DataTable PesquisaGridViewContingencia(string sCampos)
{
try
{
DataTable dt = new DataTable();
string sQuery = "Select "
+ sCampos
+ " from conhecim c inner join remetent r on c.cd_remetent = r.cd_remetent"
+ " where conhecim.st_contingencia ='S' and (conhecim.st_cte='N' or conhecim.st_cte is null)";
FbDataAdapter da = new FbDataAdapter(sQuery, cx.get_Conexao());
dt.Clear();
cx.Open_Conexao();
da.Fill(dt);
da.Dispose();
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cx.Close_Conexao();
}
}
public DataTable PesquisaGridView(string sCampos, string sWhere)
{
try
{
DataTable dt = new DataTable();
string sQuery = "Select "
+ sCampos
+ " from conhecim c inner join remetent r on c.cd_remetent = r.cd_remetent"
+ " Where " + sWhere;
FbDataAdapter da = new FbDataAdapter(sQuery, cx.get_Conexao());
dt.Clear();
cx.Open_Conexao();
da.Fill(dt);
da.Dispose();
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cx.Close_Conexao();
}
}
public void PreencherGrid()
{
using (FbConnection connection = DaoBanco.GetInstancia().getConexao())
{
try
{
string mSQL = "SELECT * FROM PECAS";
FbCommand cmd = new FbCommand(mSQL, connection);
FbDataAdapter da = new FbDataAdapter(cmd);
dtPeca = new DataTable();
da.Fill(dtPeca);
this.gdPeca.DataSource = dtPeca;
this.gdPeca.Columns["ID_PECA"].HeaderText = "Código";
this.gdPeca.Columns["ID_PECA"].Width = 70;
this.gdPeca.Columns["PECA"].HeaderText = "Peça";
this.gdPeca.Columns["PECA"].Width = 300;
this.gdPeca.Columns["OBS"].HeaderText = "Observaçoes";
this.gdPeca.Columns["OBS"].Width = 510;
}
catch (FbException fbex)
{
MessageBox.Show("Erro de acesso a dados da peça : " + fbex.Message, "Erro");
}
}
}
public void DeleteTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 10;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public List<Indicador> Listar(string empresa, string filial)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "SELECT EMPRESA, FILIAL, CODIGO, DESCRICAO FROM INDICADOR "+
"WHERE ((INDICADOR.EMPRESA = @EMPRESA) OR (INDICADOR.EMPRESA = '**')) "+
"AND ((INDICADOR.FILIAL = @FILIAL) OR (INDICADOR.FILIAL = '**'))";
sqlCommand.Parameters.AddWithValue("@EMPRESA", empresa);
sqlCommand.Parameters.AddWithValue("@FILIAL", filial);
FbDataAdapter sqlAdapter = new FbDataAdapter();
sqlAdapter.SelectCommand = sqlCommand;
DataTable dtIndicador = new DataTable();
sqlAdapter.Fill(dtIndicador);
return this.ConverteDataTableEmList(dtIndicador).ToList();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public DataTable BuscaDadosNF()
{
DataTable dt = new DataTable();
try
{
sSQL = "SELECT " + this.sCampos + " FROM " + this.sTabela;
if (!(this.sInner.Equals(String.Empty)))
sSQL += this.sInner.ToString();
if (!(this.sWhere.Equals(String.Empty)))
sSQL += " WHERE " + this.sWhere + " ";
if (!(this.sOrder.Equals(String.Empty)))
sSQL += " ORDER BY " + this.sOrder;
FbDataAdapter Da = new FbDataAdapter(sSQL, cx.get_Conexao());
cx.Open_Conexao();
dt.Clear();
Da.Fill(dt);
Da.Dispose();
}
catch (Exception)
{
throw;
}
finally { cx.Close_Conexao(); }
return dt;
}
public DataTable RetornaTabelaUsuariosCadastrados()
{
FbConnection conex = new FbConnection(conexao);
string query = "SELECT CDUSUARIO, DSUSUARIO, DSMAE, DTNASCIMENTO FROM TBUSUARIO";
FbCommand comando = new FbCommand(query, conex);
try
{
conex.Open();
FbDataAdapter datareader = new FbDataAdapter(comando);
DataTable usuarios = new DataTable();
datareader.Fill(usuarios);
return usuarios;
}
catch (Exception err)
{
throw err;
}
finally
{
conex.Close();
}
}
public DataTable RetornaNomeUsuarioCadastrado(string prontuario)
{
FbConnection conex = new FbConnection(conexao);
string query = "SELECT * FROM TBUSUARIO WHERE CDUSUARIO = '"+prontuario+"'";
FbCommand comando = new FbCommand(query, conex);
try
{
conex.Open();
FbDataAdapter datareader = new FbDataAdapter(comando);
DataTable usuarios = new DataTable();
datareader.Fill(usuarios);
return usuarios;
}
catch (Exception err)
{
throw err;
}
finally
{
conex.Close();
}
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
cadena = "User=SYSDBA;Password=masterkey;Database=C:\\Users\\Juan\\Desktop\\SAMYK\\samyk\\database.FDB;Datasource=localhost;Port=3050";
conexion = new FbConnection(cadena);
conexion.Open();
}
catch (Exception ex)
{
cadena = "User=SYSDBA;Password=masterkey;Database=C:\\Users\\Ezequiel-Pc\\Desktop\\samyk\\samyk\\database.FDB;Datasource=localhost;Port=3050";
conexion = new FbConnection(cadena);
conexion.Open();
}
try
{
MessageBox.Show("conexion establecida");
DataTable dtDatos = new DataTable();
string qry = armarQry();
FbDataAdapter mdaDatos = new FbDataAdapter(qry, conexion);
mdaDatos.Fill(dtDatos);
dataGridView1.DataSource = dtDatos;
}
catch (Exception ex)
{
MessageBox.Show("errdfsdfsdfsdfsdfsdfsdor" + ex);
}
finally
{
}
}
public FbCommandBuilder(FbDataAdapter adapter)
: base()
{
this.DataAdapter = adapter;
this.QuotePrefix = "\"";
this.QuoteSuffix = "\"";
this.ConflictOption = ConflictOption.OverwriteChanges;
}
protected override DbDataAdapter GetAdapterDb(DbCommand com)
{
if (Adapter == null)
{
Adapter = new FbDataAdapter();
}
com.CommandTimeout = CommandTimeout;
Adapter.SelectCommand = com;
return Adapter;
}
public static DataTable EleentsToDataTable(string command)
{
var dt = new DataTable();
FbConnection con = new FbConnection(ConStr.ConnectionString);
con.Open();
FbDataAdapter da = new FbDataAdapter(command, con);
da.Fill(dt);
con.Close();
return dt;
}
/// <summary>
/// 执行一段SQL查询，返回一个数据集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>数据集</returns>
public override System.Data.DataSet RunSql(string sql, IEnumerable<System.Data.IDbDataParameter> parameters)
{
using (var dbConnection = CreateDbConnection(true))
{
var cmd = CreateDbCommand(sql, parameters, dbConnection);
var adapter = new FbDataAdapter((FbCommand)cmd);
System.Data.DataSet dataSet = new System.Data.DataSet();
adapter.Fill(dataSet);
return dataSet;
}
}
public static DataTable Consultar(string QrySql)
{
DataTable Table = new DataTable();
OpenConection();
adapter = new FbDataAdapter(QrySql, conexao);
adapter.Fill(Table);
CloseConection();
return Table;
}
void FillEditionsList()
{
FbCommand cmd = new FbCommand(@"select id, number, end_date, start_date, state, tickets
from get_editions_list",
_fbConnection);
FbDataAdapter da = new FbDataAdapter(cmd);
_editionsTable.Clear();
da.Fill(_editionsTable);
_editionsGrid.SetDataSource(_editionsTable);
}
public int UpdateTable(DataSet dataSet)
{
DbCommand sQLStringCommand = db.GetSQLStringCommand("SELECT * FROM " + dataSet.Tables[0].TableName);
sQLStringCommand.Connection = db.OpenConnection();
FbDataAdapter adapter = new FbDataAdapter();
FbCommandBuilder builder = new FbCommandBuilder(adapter);
adapter.SelectCommand = (FbCommand)sQLStringCommand;
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.RowUpdated += new FbRowUpdatedEventHandler(adapter_RowUpdated);
return adapter.Update(dataSet.Tables[0]);
}
protected void ASPxGridView1_Init(object sender, EventArgs e)
{
string sql = string.Empty;
DataTable dtable = new DataTable();
FbConnection conn = Persist.GetConn.getConn();
if (Request.QueryString["IDCONTA"] != null)
{
if (Request.QueryString["UNDUNIFICADO"] != null)
{
sql = "select * from vw_fluxus_agendados FA "+
"JOIN webprocesso PROC ON PROC.id = FA.procid "+
"JOIN webprocesso_contasliquidacao CL ON CL.idprocesso = PROC.id "+
"WHERE FA.temcontas IS NOT NULL "+
"AND "+
"FA.undunificado = '"+ Request.QueryString["UNDUNIFICADO"] +"' "+
"AND "+
"CL.idconta = " + Request.QueryString["IDCONTA"].ToString();
conn.Open();
FbCommand cmd = new FbCommand(sql, conn);
FbDataAdapter dta = new FbDataAdapter(cmd);
dta.Fill(dtable);
ASPxGridView1.DataSource = dtable;
ASPxGridView1.DataBind();
conn.Close();
conn.Dispose();
}
}
else {
if (Request.QueryString["UNDUNIFICADO"] != null)
{
sql = "select * from vw_fluxus_agendados FA " +
"JOIN webprocesso PROC ON PROC.id = FA.procid " +
"WHERE FA.temcontas IS NULL " +
"AND " +
"FA.undunificado = '" + Request.QueryString["UNDUNIFICADO"].ToString() + "'";
conn.Open();
FbCommand cmd = new FbCommand(sql, conn);
FbDataAdapter dta = new FbDataAdapter(cmd);
dta.Fill(dtable);
ASPxGridView1.DataSource = dtable;
ASPxGridView1.DataBind();
conn.Close();
conn.Dispose();
}
}
}
public void PreencherGrid()
{
using (FbConnection connection = DaoBanco.GetInstancia().getConexao())
{
try
{
string mSQL = "SELECT * FROM fornecedores";
FbCommand cmd = new FbCommand(mSQL, connection);
FbDataAdapter da = new FbDataAdapter(cmd);
dtCliente = new DataTable();
da.Fill(dtCliente);
this.gdFornecedor.DataSource = dtCliente;
this.gdFornecedor.Columns["ID_FORNECEDOR"].HeaderText = "Código";
this.gdFornecedor.Columns["ID_FORNECEDOR"].Width = 50;
this.gdFornecedor.Columns["NOME_FANT"].HeaderText = "Razão Social";
this.gdFornecedor.Columns["NOME_FANT"].Width = 280;
this.gdFornecedor.Columns["RG_INSC"].HeaderText = "CPF/CNPJ";
this.gdFornecedor.Columns["RG_INSC"].Width = 280;
this.gdFornecedor.Columns["CPF_CNPJ"].HeaderText = "CPF/CNPJ";
this.gdFornecedor.Columns["FONEF"].HeaderText = "Telefone";
this.gdFornecedor.Columns["FONEC"].HeaderText = "Celular";
this.gdFornecedor.Columns["CIDADE"].HeaderText = "Cidade";
this.gdFornecedor.Columns["CIDADE"].Width = 150;
this.gdFornecedor.Columns["EMAIL"].HeaderText = "E-mail";
this.gdFornecedor.Columns["EMAIL"].Width = 150;
this.gdFornecedor.Columns["PESSOA"].Visible = false;
this.gdFornecedor.Columns["RAZAO"].Visible = false;
this.gdFornecedor.Columns["NASC_FUNDA"].Visible = false;
this.gdFornecedor.Columns["ENDERECO"].Visible = false;
this.gdFornecedor.Columns["NUMERO"].Visible = false;
this.gdFornecedor.Columns["CEP"].Visible = false;
this.gdFornecedor.Columns["BAIRRO"].Visible = false;
this.gdFornecedor.Columns["UF"].Visible = false;
this.gdFornecedor.Columns["FONEX"].Visible = false;
this.gdFornecedor.Columns["OBS"].Visible = false;
}
catch (FbException fbex)
{
MessageBox.Show("Erro de acesso a dados do cliente : " + fbex.Message, "Erro");
}
finally
{
}
}
}
void FillPunctsList()
{
FbCommand cmd = new FbCommand("select id, title from get_puncts_list",
_connection);
FbDataAdapter da = new FbDataAdapter(cmd);
DataTable _punctsList = new DataTable();
da.Fill(_punctsList);
_punctCB.DisplayMember = "Title";
_punctCB.ValueMember = "Id";
_punctCB.DataSource = _punctsList;
}
public System.Data.DataTable getFonteDeRecursoPorUnidadeOrcamentaria(string undOrc)
{
System.Data.DataTable dt = new System.Data.DataTable();
string sql = "select DISTINCT foncod, fonnome from fonterecurso fr " +
"join dotacoesporfonte dpf on fr.foncod = dpf.dpfcodfonte " +
"join dotacoes d on d.dotreduzido = dpf.dpfreduzido " +
"where d.dotcodorgao||D.dotcodunidade = '"+ undOrc +"'";
FbConnection conn = Persist.GetConn.getConn();
FbCommand cmd = new FbCommand(sql, conn);
conn.Open();
FbDataAdapter dta = new FbDataAdapter(cmd);
dta.Fill(dt);
conn.Close();
return dt;
}
public static DataSet GetSchemaFireBird(string connectstring, string tableName)
{
var sql = "select * from " + tableName + " where 1=0;";
var fbConnection = new FbConnection {ConnectionString = connectstring};
fbConnection.Open();
var ds = new DataSet();
var adapter = new FbDataAdapter(sql, fbConnection);
adapter.Fill(ds);
//For Get Lenght
var dataTables = adapter.FillSchema(ds, SchemaType.Source);
ds.Tables.Clear();
ds.Tables.Add(dataTables[0]);
return ds;
}
public DataTable fnGetFields()
{
// Initializate
cm = new FbCommand();
dt = new DataTable();
// Command attributes
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "spFields_SEL";
cm.Connection = (FbConnection)objMainBE.oConnection;
// Stored Procedure Atributtes
if (ObjMainBE.sItem == "")
{
cm.Parameters.AddWithValue("@w_sItem", DBNull.Value);
}
else
{
cm.Parameters.Add("@w_sItem", ObjMainBE.sItem);
}
try
{
// Execute
da = new FbDataAdapter(cm);
da.Fill(dt);
da.Dispose();
return dt;
}
finally
{
cm.Dispose();
dt.Dispose();
}
}
public DataTable fnSearchBank()
{
// Initializate
cm = new FbCommand();
dt = new DataTable();
// Command attributes
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "spBank_SEL_ALL";
cm.Connection = (FbConnection)objBankBE.oConnection;
// Stored Procedure Atributtes
if (ObjBankBE.nBank == 0)
{
cm.Parameters.AddWithValue("@sCommand", DBNull.Value);
}
else
{
cm.Parameters.Add("@sCommand", FbDbType.Char).Value = objBankBE.sSearchCommand;
}
try
{
// Execute
da = new FbDataAdapter(cm);
da.Fill(dt);
da.Dispose();
return dt;
}
finally
{
cm.Dispose();
dt.Dispose();
}
}
public DataTable GetDadosToReport(string sStatus, string sDtInicio, string sDtFinal)
{
if (sStatus == "A")
{
sStatus = "";
}
string squery = "SELECT * FROM SP_RELNF_FRETE_POR_STATUS ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')";
squery = string.Format(squery,
daoStatic.CD_EMPRESA,
daoStatic.CD_EMPRESA,
daoStatic.CD_CLIFOR,
daoStatic.CD_CLIFOR,
" ",
"|||||||",
" ",
"|||||||",
sDtInicio,
sDtFinal,
sStatus);
FbCommand command = new FbCommand();
command.Connection = new FbConnection(daoStatic.sConn);
command.CommandText = squery.ToString();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Clear();
DataTable dtReturn = new DataTable();
try
{
command.Connection.Open();
FbDataAdapter dp = new FbDataAdapter(command);
dp.Fill(dtReturn);
}
catch (Exception)
{
throw;
}
finally
{
command.Connection.Close();
}
return dtReturn;
}
public static DataTable SelectData(string query)
{
//if (showSql)
// TextShowForm.CreateSimple(null, query, true);
OpenConnection();
FbCommand command = new FbCommand(query, mConnection);
command.Transaction = mConnection.BeginTransaction(IsolationLevel.RepeatableRead);
FbDataAdapter adapter = new FbDataAdapter(command);
DataTable newdata = new DataTable();
try
{
adapter.Fill(newdata);
}
finally
{
command.Transaction.Commit();
}
return newdata;
}
public void FillTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
Console.WriteLine();
Console.WriteLine("DataAdapter - Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
internal override void LoadAll()
{
try
{
var cn = new FbConnection(_dbRoot.ConnectionString);
var adapter = new FbDataAdapter(BuildSql(Procedure.Name), cn);
var metaData = new DataTable();
adapter.Fill(metaData);
metaData.Columns["PARAMETER_DIRECTION"].ColumnName = "PARAMETER_TYPE";
PopulateArray(metaData);
FixupDataTypes(metaData);
}
catch (Exception ex)
{
string m = ex.Message;
}
}
