//int totalRecords = 0; public void ChangePassword( string appName, string username, string newPassword, string newSalt, int passwordFormat, string newPasswordAnswer) { using (FbCommand cmd = new FbCommand(MsSqlDbAccess.GetObjectName("P_changepassword"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new FbParameter("@i_applicationname", FbDbType.VarChar)); cmd.Parameters[0].Value = appName; // Nonstandard args cmd.Parameters.Add(new FbParameter("@I_USERNAME", FbDbType.VarChar)); cmd.Parameters[1].Value = username; cmd.Parameters.Add(new FbParameter("@i_password", FbDbType.VarChar)); cmd.Parameters[2].Value = newPassword; cmd.Parameters.Add(new FbParameter("@i_passwordsalt", FbDbType.VarChar)); cmd.Parameters[3].Value = newSalt; cmd.Parameters.Add(new FbParameter("@i_passwordformat", FbDbType.VarChar)); cmd.Parameters[4].Value = passwordFormat; cmd.Parameters.Add(new FbParameter("@i_passwordanswer", FbDbType.VarChar)); cmd.Parameters[5].Value = newPasswordAnswer; _dbAccess.ExecuteNonQuery(cmd ); } }
public int atualizar(dsFROGIOS.FORNECEDORRow fornecedor) { FbCommand comando = null; string sql = "UPDATE FORNECEDOR " + "SET FORNECEDOR_ENDERECO = " + paramEndereco + ", " + "FORNECEDOR_BAIRRO = " + paramBairro + ", " + "FORNECEDOR_PERIMETRO = " + paramPerimetro + ", " + "FORNECEDOR_CIDADE = " + paramCidade + ", " + "FORNECEDOR_UF = " + paramUf + ", " + "FORNECEDOR_CEP = " + paramCep + ", " + "FORNECEDOR_TELEFONE = " + paramTelefone + ", " + "FORNECEDOR_CELULAR = " + paramCelular + ", " + "FORNECEDOR_EMAIL = " + paramEmail + ", " + "FORNECEDOR_OBSERVACOES = " + paramObservacoes + " " + "WHERE FORNECEDOR_CODIGO = " + paramCodigo + ";"; comando = new FbCommand(sql, Conexao.getConexao, Conexao.getTransacao); comando.Parameters.AddWithValue(paramCodigo, fornecedor.FORNECEDOR_CODIGO); comando.Parameters.AddWithValue(paramEndereco, fornecedor.FORNECEDOR_ENDERECO); comando.Parameters.AddWithValue(paramBairro, fornecedor.FORNECEDOR_BAIRRO); comando.Parameters.AddWithValue(paramPerimetro, fornecedor.FORNECEDOR_PERIMETRO); comando.Parameters.AddWithValue(paramCidade, fornecedor.FORNECEDOR_CIDADE); comando.Parameters.AddWithValue(paramUf, fornecedor.FORNECEDOR_UF); comando.Parameters.AddWithValue(paramCep, fornecedor.FORNECEDOR_CEP); comando.Parameters.AddWithValue(paramTelefone, fornecedor.FORNECEDOR_TELEFONE); comando.Parameters.AddWithValue(paramCelular, fornecedor.FORNECEDOR_CELULAR); comando.Parameters.AddWithValue(paramEmail, fornecedor.FORNECEDOR_EMAIL); comando.Parameters.AddWithValue(paramObservacoes, fornecedor.FORNECEDOR_OBSERVACOES); return comando.ExecuteNonQuery(); }
public string ActualizarSubtipoMateriaPrima(int AId, string ASubtipo) { int pAffected = 0; string pSentencia = "UPDATE DRASMATPRIMAD SET SUBTIPO = @SUBTIPO WHERE ID = @ID"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@ID", FbDbType.Integer).Value = AId; com.Parameters.Add("@SUBTIPO", FbDbType.VarChar).Value = ASubtipo; try { con.Open(); pAffected = com.ExecuteNonQuery(); } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } if (pAffected > 0) return ObtenerSubtipoMateriaPrima(AId); else return null; }
public void GuardarLogTarimaRegresada(int AFolioTarima, string AMotivo, string AUsuario) { Tarima pTarima = ObtenerTarima(AFolioTarima); string pSentencia = "INSERT INTO LOG_TARIMAS_REGRESADAS (ID_TARIMA, CAJAS_TARIMA, KILOS_TARIMA, USUARIO, MOTIVO) VALUES (@FOLIOTARIMA, @CAJAS, @KILOS, @USUARIO, @MOTIVO)"; FbConnection con = _Conexiones.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@FOLIOTARIMA", FbDbType.Integer).Value = AFolioTarima; com.Parameters.Add("@CAJAS", FbDbType.Integer).Value = pTarima.Cajas; com.Parameters.Add("@KILOS", FbDbType.Numeric).Value = pTarima.Kilos; com.Parameters.Add("@USUARIO", FbDbType.VarChar).Value = AUsuario; com.Parameters.Add("@MOTIVO", FbDbType.VarChar).Value = AMotivo; try { con.Open(); com.ExecuteNonQuery(); } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } }
public Row[] Select() { FbTransaction transaction = connection.BeginTransaction(); FbCommand cmd = new FbCommand(selectCommand, connection, transaction); List<Row> rows = new List<Row>(); Row nr; using (FbDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { nr = new Row(); nr.ID = dr.GetInt32(0); nr.Name = dr.GetString(1); nr.Description = dr.GetString(2); using (System.IO.Stream stream = dr.GetStream(3)) { using (System.IO.MemoryStream mStream = new System.IO.MemoryStream()) { stream.CopyTo(mStream); nr.Image = mStream.ToArray(); } } rows.Add(nr); } } transaction.Commit(); return rows.ToArray(); }
protected FbCommand BuildCommand(FbConnection connection, string collectionName, string[] restrictions) { string filter = String.Format("CollectionName='{0}'", collectionName); StringBuilder builder = this.GetCommandText(restrictions); DataRow[] restriction = connection.GetSchema(DbMetaDataCollectionNames.Restrictions).Select(filter); FbTransaction transaction = connection.InnerConnection.ActiveTransaction; FbCommand command = new FbCommand(builder.ToString(), connection, transaction); if (restrictions != null && restrictions.Length > 0) { int index = 0; for (int i = 0; i < restrictions.Length; i++) { string rname = restriction[i]["RestrictionName"].ToString(); if (restrictions[i] != null) { // Catalog, Schema and TableType are no real restrictions if (!rname.EndsWith("Catalog") && !rname.EndsWith("Schema") && rname != "TableType") { string pname = String.Format(CultureInfo.CurrentUICulture, "@p{0}", index++); command.Parameters.Add(pname, FbDbType.VarChar, 255).Value = restrictions[i]; } } } } return command; }
public IModel Select(FbConnection conn, FbTransaction trans, string code) { string query = "SELECT * FROM models WHERE modelcode = UPPER(@modelcode)"; IModel ret = null; try { using (FbCommand cmd = new FbCommand(query, conn, trans)) { cmd.Parameters.AddWithValue("modelcode", code.ToUpper()); using (FbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { ret = new Model { Id = reader.GetInt32(0), Code = reader.GetString(1), ObjectIdRoot = reader.GetInt32(2) }; } } } } catch (Exception ex) { throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message)); } return ret; }
public bool ExisteClasificacionCorte(ClasificacionCorte AClasificacionCorte) { string pSentencia = "SELECT ID FROM CLASIFICACIONCORTES WHERE UPPER(TRIM(DESCRIPCION)) = @DESCRIPCION"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@DESCRIPCION", FbDbType.VarChar).Value = AClasificacionCorte.Descripcion.ToUpper().Trim(); try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { if ((int)reader["ID"] == AClasificacionCorte.Id) return false; else return true; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return false; }
public ClasificacionCorte ClasificacionCorteObtener(int AClave) { ClasificacionCorte pResult = null; string pSentencia = "SELECT ID, DESCRIPCION, LOTES FROM CLASIFICACIONCORTES WHERE ID = @ID"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@ID", FbDbType.Integer).Value = AClave; try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { pResult = ReaderToEntidad(reader); } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return pResult; }
public BaseTreeNavigator() { InitializeComponent(); //SetConnections(); // get the connection string from app.config and create the connections string sqlConnectionString = ConfigurationManager.ConnectionStrings["sqlServerConnectionString"].ConnectionString; string fbConnectionString = ConfigurationManager.ConnectionStrings["firebirdConnectionString"].ConnectionString; //sql server sqlConnection = new SqlConnection(sqlConnectionString); sqlConnectionDaughter = new SqlConnection(sqlConnectionString); sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection; sqlCommandDaughter = new SqlCommand(); sqlCommandDaughter.Connection = sqlConnectionDaughter; //firebird fbConnection = new FbConnection(fbConnectionString); fbConnectionDaughter = new FbConnection(fbConnectionString); fbCommand = new FbCommand(); fbCommand.Connection = fbConnection; fbCommandDaughter = new FbCommand(); fbCommandDaughter.Connection = fbConnectionDaughter; }
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 string BuscaUltimoNumeroConhecimento(string sEmp) { try { string sQuery = ""; if (belStatic.sNomeEmpresa.ToUpper().Equals("SICUPIRA") || belStatic.sNomeEmpresa.ToUpper().Equals("TRANSLILO") || belStatic.sNomeEmpresa.ToUpper().Equals("GCA")) { string sGenerator = "CONHECIM_CTE" + belStatic.CodEmpresaCte; sQuery = "SELECT GEN_ID(" + sGenerator + ", 0 ) FROM RDB$DATABASE"; using (FbCommand cmd = new FbCommand(sQuery, cx.get_Conexao())) { cx.Open_Conexao(); return Convert.ToString(cmd.ExecuteScalar()); } } else { sQuery = "select max(c.cd_conheci) from conhecim c where c.cd_empresa = '" + sEmp + "'"; using (FbCommand cmd = new FbCommand(sQuery, cx.get_Conexao())) { cx.Open_Conexao(); return Convert.ToString(cmd.ExecuteScalar()); } } } catch (Exception ex) { throw ex; } finally { cx.Close_Conexao(); } }
public void PopulainfCarga(belinfCte objbelinfCte, FbConnection conn, string sCte) { try { StringBuilder sQuery = new StringBuilder(); sQuery.Append("Select "); sQuery.Append("coalesce(conhecim.ds_prodpred,'')proPred, "); sQuery.Append("coalesce(nfconhec.vl_nf,'')vMerc "); sQuery.Append("from conhecim "); sQuery.Append("join nfconhec on conhecim.nr_lanc = nfconhec.nr_lancconhecim "); sQuery.Append("join empresa on conhecim.cd_empresa = empresa.cd_empresa "); sQuery.Append("where conhecim.nr_lanc ='" + sCte + "' "); sQuery.Append("and empresa.cd_empresa ='" + belStatic.CodEmpresaCte + "'"); FbCommand fbConn = new FbCommand(sQuery.ToString(), conn); fbConn.ExecuteNonQuery(); FbDataReader dr = fbConn.ExecuteReader(); objbelinfCte.infCTeNorm = new belinfCTeNorm(); objbelinfCte.infCTeNorm.infCarga = new belinfCarga(); while (dr.Read()) { objbelinfCte.infCTeNorm.infCarga.vCarga += Convert.ToDecimal(dr["vMerc"].ToString().Replace(".", ",")); objbelinfCte.infCTeNorm.infCarga.proPred =belUtil.TiraSimbolo( dr["proPred"].ToString(),""); } } catch (Exception ex) { throw ex; } }
public tcDadosConstrucaoCivil RettcDadosConstrucaoCivil(FbConnection Conn, string sNota) { try { StringBuilder sQuery = new StringBuilder(); sQuery.Append("select coalesce(clifor.cd_art,'') Art, coalesce(clifor.cd_obra,'')CodigoObra {0}"); sQuery.Append("from nf inner join clifor on nf.cd_clifor = clifor.cd_clifor {0}"); sQuery.Append("where nf.cd_nfseq = '{1}' and nf.cd_empresa = '{2}' {0}"); string sQueryEnd = string.Format(sQuery.ToString(), Environment.NewLine, sNota, belStatic.codEmpresaNFe); FbCommand cmd = new FbCommand(sQueryEnd, Conn); Conn.Open(); FbDataReader dr = cmd.ExecuteReader(); objtcDadosConstrucaoCivil = new tcDadosConstrucaoCivil(); while (dr.Read()) { objtcDadosConstrucaoCivil.Art = dr["Art"].ToString(); objtcDadosConstrucaoCivil.CodigoObra = dr["CodigoObra"].ToString(); } } catch (Exception) { throw; } finally { Conn.Close(); } return objtcDadosConstrucaoCivil; }
public string RetornaGenString(string sGen, int Tamanho) { string sNumArq = ""; try { FbCommand sSql = new FbCommand(); sSql.Connection = cx.get_Conexao(); cx.Open_Conexao(); sSql.CommandText = "SP_CHAVEPRI"; sSql.CommandType = CommandType.StoredProcedure; sSql.Parameters.Clear(); sSql.Parameters.Add("@SNOMEGENERATOR", FbDbType.VarChar, 31).Value = "GEN_NOMEARQXML"; sNumArq = sSql.ExecuteScalar().ToString(); } catch (FbException Ex) { Console.WriteLine("Erro.: ", Ex.Message); } finally { cx.Close_Conexao(); } return sNumArq.PadLeft(Tamanho, '0'); }
public List<string> BuscaCodigoEmpresas() { List<string> slCodigos = new List<string>(); belConnection cx = new belConnection(); try { using (FbCommand cmd = new FbCommand("Select empresa.cd_empresa from empresa order by empresa.cd_empresa", cx.get_Conexao())) { cx.Open_Conexao(); cmd.ExecuteNonQuery(); FbDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { slCodigos.Add(dr["cd_empresa"].ToString()); } } } catch (Exception ex) { throw; } finally { cx.Close_Conexao(); } return slCodigos; }
public void Create(IEnumerable<string> columnDefinitions) { var sb = new StringBuilder(); sb.Append("CREATE TABLE "); sb.Append(quoter.QuoteTableName(Name)); sb.Append(" ("); foreach (string definition in columnDefinitions) { sb.Append(definition); sb.Append(", "); } sb.Remove(sb.Length - 2, 2); sb.Append(")"); var s = sb.ToString(); using (var command = new FbCommand(s, Connection, Transaction)) command.ExecuteNonQuery(); processor.AutoCommit(); processor.LockTable(Name); }
public void AlterarSenha(Usuario obj) { FbCommand sqlCommand = new FbCommand(); try { sqlCommand.Connection = this._conexao; sqlCommand.CommandText = "UPDATE SYS_USERS SET USUARIOWEB = @USUARIO, SENHAWEB = @SENHA WHERE CODIGO = @CODIGO"; sqlCommand.Parameters.AddWithValue("@USUARIO", obj.NomeUsuario); sqlCommand.Parameters.AddWithValue("@SENHA", obj.Senha); sqlCommand.Parameters.AddWithValue("@CODIGO", obj.Codigo); sqlCommand.ExecuteNonQuery(); } catch (FbException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { sqlCommand.Dispose(); } }
public void InsertNullGuidTest() { // Insert the Guid var id = GetId(); FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (INT_FIELD, GUID_FIELD) VALUES (@IntField, @GuidValue)", Connection); insert.Parameters.Add("@IntField", FbDbType.Integer).Value = id; insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = DBNull.Value; insert.ExecuteNonQuery(); insert.Dispose(); // Select the value using (FbCommand select = new FbCommand("SELECT * FROM GUID_TEST WHERE INT_FIELD = @IntField", Connection)) { select.Parameters.Add("@IntField", FbDbType.Integer).Value = id; using (FbDataReader r = select.ExecuteReader()) { if (r.Read()) { if (!r.IsDBNull(1)) { throw new Exception(); } } } } }
public Destino DestinoObtener(int AClave){ Destino pResult = null; string pSentencia = "SELECT CLAVE, DESTINO FROM DRASDEST WHERE CLAVE = @CLAVE"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@CLAVE", FbDbType.Integer).Value = AClave; try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()){ pResult = ReaderToEntidad(reader); } } finally { if (con.State == System.Data.ConnectionState.Open){ con.Close(); } } return pResult; }
public string fNIP(string sNIP) { string sReturn = "0"; string query = "select count(*) from ad_firms where (UPPER(TAXID) = UPPER('" + sNIP + "'));"; //Open connection if (this.OpenConnection() == true) { //Create Command FbCommand cmd = new FbCommand(query, connection); //Create a data reader and Execute the command FbDataReader dataReader = cmd.ExecuteReader(); //MessageBox.Show(query, "qry", MessageBoxButtons.OK); //Read the data and store them in the list while (dataReader.Read()) { sReturn = dataReader[0].ToString() + ""; } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); return sReturn; } else { return sReturn; } }
public void CancelarNFseSistema(string sNumNfse, FbConnection Conn) { try { StringBuilder sSql = new StringBuilder(); sSql.Append("update nf "); sSql.Append("set cd_recibocanc = '"); sSql.Append("CANCELADA"); sSql.Append("' "); sSql.Append("where "); sSql.Append("cd_empresa = '"); sSql.Append(belStatic.codEmpresaNFe); sSql.Append("' "); sSql.Append("and "); sSql.Append("cd_numero_nfse = '"); sSql.Append(sNumNfse); sSql.Append("'"); using (FbCommand cmdUpdate = new FbCommand(sSql.ToString(), Conn)) { cmdUpdate.ExecuteNonQuery(); } } catch (Exception ex) { throw; } }
public bool ExisteDestino(Destino ADestino) { string pSentencia = "SELECT CLAVE FROM DRASDEST WHERE UPPER(TRIM(DESTINO)) = @DESTINO"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@DESTINO", FbDbType.VarChar).Value = ADestino._Destino.ToUpper().Trim(); try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { int clave = (int)reader["CLAVE"]; if (clave == ADestino.Clave) return false; else return true; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return false; }
public Produto buscarCodmat(String codmat) { SigletonConexaoFB.carregaStrcnx(); Produto resultado = new Produto(); FbCommand command = new FbCommand(); FbDataReader reader; try { command.Connection = SigletonConexaoFB.getConexao(); command.CommandText = "SELECT CODMAT,DESCRICAO,UNID FROM TB_MATERIAL " + "WHERE CODMAT='" + codmat + "'"; command.Connection.Open(); reader = command.ExecuteReader(); if (reader.Read()) { resultado.Codmat = reader["CODMAT"].ToString(); resultado.Descricao = reader["DESCRICAO"].ToString(); resultado.Unid = reader["UNID"].ToString(); } reader.Close(); } catch (Exception ex) { throw new Exception("Erro na busca de produto pelo código: " + ex.Message); } finally { command.Connection.Close(); } return resultado; }
public int ObtenerCajasAplicadasCorte(string AFecha, int ACamara, string AEmbarcado) { string pSentencia = "SELECT COUNT(*) FROM DRASCORT WHERE "+ "FECHA = @FECHA "+ "AND CAMARA = @CAMARA "+ "AND EMBARCADO = @EMBARCADO "+ "AND ENTRADA_APLICADA = 'Si'"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@FECHA", FbDbType.TimeStamp).Value = AFecha; com.Parameters.Add("@CAMARA", FbDbType.Integer).Value = ACamara; com.Parameters.Add("@EMBARCADO", FbDbType.VarChar).Value = AEmbarcado; try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { return (int)reader["COUNT"]; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return 0; }
public TcPedidoCancelamento BuscaDadosParaCancelamento(FbConnection Conn, string sCodCancelamento, string sSequencia) { try { StringBuilder sQuery = new StringBuilder(); sQuery.Append("select cidades.cd_municipio, empresa.cd_cgc Cnpj, empresa.cd_inscrmu, "); sQuery.Append("coalesce(nf.cd_numero_nfse,'')cd_numero_nfse "); sQuery.Append("from nf inner join empresa on nf.cd_empresa = empresa.cd_empresa "); sQuery.Append("inner join cidades on (cidades.nm_cidnor = empresa.nm_cidnor) "); sQuery.Append("where nf.cd_nfseq = '" + sSequencia + "' and "); sQuery.Append("nf.cd_empresa = '" + belStatic.codEmpresaNFe + "'"); FbCommand Command = new FbCommand(sQuery.ToString(), Conn); FbDataReader dr = Command.ExecuteReader(); dr.Read(); TcPedidoCancelamento objCancelamento = new TcPedidoCancelamento(); objCancelamento.InfPedidoCancelamento = new tcInfPedidoCancelamento(); objCancelamento.InfPedidoCancelamento.CodigoCancelamento = sCodCancelamento; objCancelamento.InfPedidoCancelamento.IdentificacaoNfse = new tcIdentificacaoNfse(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.CodigoMunicipio = dr["cd_municipio"].ToString(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Numero = dr["cd_numero_nfse"].ToString(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Cnpj = dr["Cnpj"].ToString(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.InscricaoMunicipal = dr["cd_inscrmu"].ToString(); return objCancelamento; } catch (Exception) { throw; } }
public HistAtualizReg buscarAtualizLicenca(int id_licenca_cliente) { SigletonConexaoFB.carregaStrcnx(); HistAtualizReg resultado = new HistAtualizReg(); FbCommand command = new FbCommand(); FbDataReader reader; try { command.Connection = SigletonConexaoFB.getConexao(); command.CommandText = "SELECT ID,ID_LICENCA_CLIENTE,DATA_ENVIO,QTDE_LICENCA,LIBERADO_ATE,VERSAO_ANIEL " + "FROM TB_ATUALIZ_REG_ANIEL WHERE ID_LICENCA_CLIENTE=" + id_licenca_cliente.ToString() + " AND LIBERADO_CLIENTE='" + 'S' + "'"; command.Connection.Open(); reader = command.ExecuteReader(); if (reader.Read()) { resultado.Id = int.Parse(reader["ID"].ToString()); resultado.Licenca_cliente.Id = int.Parse(reader["ID_LICENCA_CLIENTE"].ToString()); resultado.Data_envio = DateTime.Parse(reader["DATA_ENVIO"].ToString()); resultado.Qtde_licenca = int.Parse(reader["QTDE_LICENCA"].ToString()); resultado.Liberado_ate = DateTime.Parse(reader["LIBERADO_ATE"].ToString()); resultado.Versao_aniel = reader["VERSAO_ANIEL"].ToString(); } reader.Close(); } catch (Exception ex) { throw new Exception("Erro ao carregar dados do registro do Aniel: " + ex.Message); } finally { command.Connection.Close(); } return resultado; }
public bool AtualizHistRegAniel(int id_atualiz_reg, String usuario) { bool resultado = false; SigletonConexaoFB.carregaStrcnx(); FbCommand command = new FbCommand(); try { command.Connection = SigletonConexaoFB.getConexao(); command.CommandText = "UPDATE TB_ATUALIZ_REG_ANIEL SET LIBERADO_CLIENTE='" + 'N' + "'," + " USUARIO_ATUALIZ='" + usuario + "'," + " DATA_ATUALIZ=CURRENT_DATE," + " HORA_ATUALIZ=CURRENT_TIME" + " WHERE ID=" + id_atualiz_reg.ToString(); command.Connection.Open(); command.ExecuteNonQuery(); resultado = true; } catch (Exception ex) { throw new Exception("Erro ao registrar a atualização de registro do Aniel: " + ex.Message); } finally { command.Connection.Close(); } return resultado; }
public void DeletaTramite(int idTramite) { string query = "select datarecebimento , usuariorecebimento from webtramite where id = " + idTramite; var connection = Persist.GetConn.getConn(); var cmd = new FbCommand(query, connection); var datarecebimento = string.Empty; var usuariorecebimento = string.Empty; using (connection) { connection.Open(); var dr = cmd.ExecuteReader(); while (dr.Read()) { datarecebimento = dr["datarecebimento"].ToString(); usuariorecebimento = dr["usuariorecebimento"].ToString(); } } if (string.IsNullOrEmpty(datarecebimento) && string.IsNullOrEmpty(usuariorecebimento)) { string sql = "delete from webtramite where id = " + idTramite; AcessoDados.AcessoDados.executar(sql); } }
private void button1_Click(object sender, EventArgs e) { string query = "insert into VENTA (ID_CLIENTE, VEN_CODI, VEN_ESTA, CREATED_BY, CREATED_AT) values ( '1', '1', 'a', '1', '2015/06/01')"; FbCommand insert = new FbCommand(query, conexion); insert.ExecuteNonQuery(); //conexion.Close(); }
public static DbCommand CreateCommand(TypeDatabase typeOfDatabase, String cmdText, DbConnection connection) { DbCommand cmd = null; switch (typeOfDatabase) { case TypeDatabase.Postgresql: cmd = new Npgsql.NpgsqlCommand(cmdText, (Npgsql.NpgsqlConnection)connection); break; case TypeDatabase.Oracle: cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(cmdText, (Oracle.ManagedDataAccess.Client.OracleConnection)connection); break; case TypeDatabase.Firebird: cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(cmdText, (FbConnection)connection); break; default: throw new NotImplementedException(); } return(cmd); }
public FbDataAdapter(FbCommand selectCommand) : base() { this.SelectCommand = selectCommand; }
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { if (datasetConfig.DatasetTableName != "") //Add the posibility to use a specific table_name (for using the table more then ones) { this.dataTable = datasetConfig.DatasetTableName; } else { this.dataTable = dataTable; } this.datasetConfig = datasetConfig; this.fieldList = datasetConfig.DatasetConfigRows; //Look if Table exists, when not, create it! try { string sql = "SELECT * FROM " + dataTable + ";"; myCmd = new FbCommand(sql, myDBConn); myReader = myCmd.ExecuteReader(); } catch (FbException ex) { /*if (ex.ErrorCode == 0) * { * try * { * string sql = "CREATE TABLE " + dataTable + " (id INTEGER PRIMARY KEY ASC AUTOINCREMENT); "; * * myCmd = new FbCommand(sql, myDBConn); * myCmd.ExecuteNonQuery(); * * sql = "SELECT * FROM " + dataTable + ";"; * myCmd = new FbCommand(sql, myDBConn); * myReader = myCmd.ExecuteReader(); * } * catch (System.Data.SQLite.SQLiteException ex_ex) * { * throw ex_ex; * } * } * else * { * throw ex; * }*/ } //Look for the Fields, create or alter them! List <String> existDBFelderliste = new List <string>(); for (int n = 0; n < myReader.FieldCount; n++) { existDBFelderliste.Add(myReader.GetName(n)); } myReader.Close(); foreach (DatasetConfigRow myFeld in fieldList) { foreach (string existMyFeld in existDBFelderliste) { if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower()) { goto nextFeld; } } //Feld existiert nicht -> erzeugen string sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType; try { myCmd = new FbCommand(sql, myDBConn); myCmd.ExecuteNonQuery(); } catch (FbException ex) { throw ex; } nextFeld: //Irgendeine anweisung, da sonst der Sprung nicht geht... { } } //Create Insert Command string wertliste = "", felderliste = ""; foreach (DatasetConfigRow myFeld in fieldList) { if (wertliste != "") { wertliste += ","; felderliste += ","; } felderliste += myFeld.DatabaseField; wertliste += "@" + myFeld.DatabaseField; } insertCommand = "INSERT INTO " + this.dataTable + "(" + felderliste + ") values(" + wertliste + ")"; }
protected override bool _internal_Write() { //Look if the Connection is still open.. try { string sql = "SELECT id FROM " + dataTable + " WHERE id = 0"; myCmd = new FbCommand(sql, myDBConn); myCmd.ExecuteNonQuery(); } catch (Exception) { myDBConn.Close(); //Verbindung schließen! myDBConn.Open(); if (myDBConn.State != System.Data.ConnectionState.Open) { Logging.LogText("Error ReConnecting to Database! Dataset:" + datasetConfig.Name, Logging.LogLevel.Error); return(false); } myDBConn.ChangeDatabase(myConfig.DatabaseFile); } //Add the Fields to the Database myCmd = new FbCommand(insertCommand, myDBConn); for (int n = 0; n < _maxAdd; n++) //foreach (IEnumerable<object> values in _intValueList) { myCmd.Parameters.Clear(); IEnumerable <object> values = _intValueList[n]; using (IEnumerator <DatasetConfigRow> e1 = fieldList.GetEnumerator()) using (IEnumerator <object> e2 = values.GetEnumerator()) { while (e1.MoveNext() && e2.MoveNext()) { //foreach (DatasetConfigRow field in fieldList) //{ DatasetConfigRow field = e1.Current; Object value = e2.Current; //values[fnr++]; switch (field.PLCTag.TagDataType) { case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LInt: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LWord: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Int: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Dint: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Word: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Dword: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Byte: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDByte: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDWord: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDDWord: myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String) { Value = value.ToString() }); break; case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Float: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LReal: myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String) { Value = value.ToString().Replace(',', '.') }); break; case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.DateTime: myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String) { Value = ((DateTime)value).ToString("dd-MM-yyyy HH:mm:ss") }); break; case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.String: case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.CharArray: myCmd.Parameters.Add(new FbParameter("@" + field.DatabaseField, System.Data.DbType.String) { Value = (String)value }); break; } } } int tryCounter = 0; nomol: try { myCmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } } /* * //Ringpufferarchiv... * if (myProtokollDaten.MaxDatasets > 0) * { * string delstr = "DELETE FROM " + myProtokollDaten.DataBaseTable + " WHERE id <= (SELECT max(id) FROM " + myProtokollDaten.DataBaseTable + ") - (" + myProtokollDaten.MaxDatasets.ToString() + ")"; * myCmd.CommandText = delstr; * myCmd.ExecuteNonQuery(); * } */ return(true); }
public static void DeriveParameters(FbCommand command) { if (command.CommandType != CommandType.StoredProcedure) { throw new InvalidOperationException("DeriveParameters only supports CommandType.StoredProcedure."); } var spName = command.CommandText.Trim(); var quotePrefix = "\""; var quoteSuffix = "\""; if (spName.StartsWith(quotePrefix) && spName.EndsWith(quoteSuffix)) { spName = spName.Substring(1, spName.Length - 2); } else { spName = spName.ToUpperInvariant(); } var paramsText = string.Empty; command.Parameters.Clear(); var dataTypes = command.Connection.GetSchema("DataTypes").DefaultView; var spSchema = command.Connection.GetSchema( "ProcedureParameters", new string[] { null, null, spName }); // SP has zero params. or not exist // so check whether exists, else thow exception if (spSchema.Rows.Count == 0) { if (command.Connection.GetSchema("Procedures", new string[] { null, null, spName }).Rows.Count == 0) { throw new InvalidOperationException("Stored procedure doesn't exist."); } } foreach (DataRow row in spSchema.Rows) { dataTypes.RowFilter = string.Format( "TypeName = '{0}'", row["PARAMETER_DATA_TYPE"]); var parameter = command.Parameters.Add( "@" + row["PARAMETER_NAME"].ToString().Trim(), FbDbType.VarChar); parameter.FbDbType = (FbDbType)dataTypes[0]["ProviderDbType"]; parameter.Direction = (ParameterDirection)row["PARAMETER_DIRECTION"]; parameter.Size = Convert.ToInt32(row["PARAMETER_SIZE"], CultureInfo.InvariantCulture); if (parameter.FbDbType == FbDbType.Decimal || parameter.FbDbType == FbDbType.Numeric) { if (row["NUMERIC_PRECISION"] != DBNull.Value) { parameter.Precision = Convert.ToByte(row["NUMERIC_PRECISION"], CultureInfo.InvariantCulture); } if (row["NUMERIC_SCALE"] != DBNull.Value) { parameter.Scale = Convert.ToByte(row["NUMERIC_SCALE"], CultureInfo.InvariantCulture); } } } }
private async Task <DataTable> GetSchemaTableImpl(AsyncWrappingCommonArgs async) { CheckState(); if (_schemaTable != null) { return(_schemaTable); } DataRow schemaRow = null; var tableCount = 0; var currentTable = string.Empty; _schemaTable = GetSchemaTableStructure(); /* Prepare statement for schema fields information */ var schemaCmd = new FbCommand(GetSchemaCommandText(), _command.Connection, _command.Connection.InnerConnection.ActiveTransaction); try { schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31); schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31); await schemaCmd.PrepareImpl(async).ConfigureAwait(false); _schemaTable.BeginLoadData(); for (var i = 0; i < _fields.Count; i++) { var isKeyColumn = false; var isUnique = false; var isReadOnly = false; var precision = 0; var isExpression = false; /* Get Schema data for the field */ schemaCmd.Parameters[0].Value = _fields[i].Relation; schemaCmd.Parameters[1].Value = _fields[i].Name; var reader = await schemaCmd.ExecuteReaderImpl(CommandBehavior.Default, async).ConfigureAwait(false); try { if (await reader.ReadImpl(async).ConfigureAwait(false)) { isReadOnly = (IsReadOnly(reader) || IsExpression(reader)) ? true : false; isKeyColumn = (reader.GetInt32(2) == 1) ? true : false; isUnique = (reader.GetInt32(3) == 1) ? true : false; precision = reader.IsDBNull(4) ? -1 : reader.GetInt32(4); isExpression = IsExpression(reader); } } finally { #if NET48 || NETSTANDARD2_0 reader.Dispose(); #else await async.AsyncSyncCallNoCancellation(reader.DisposeAsync, reader.Dispose).ConfigureAwait(false); #endif } /* Create new row for the Schema Table */ schemaRow = _schemaTable.NewRow(); schemaRow["ColumnName"] = GetName(i); schemaRow["ColumnOrdinal"] = i; schemaRow["ColumnSize"] = _fields[i].GetSize(); if (_fields[i].IsDecimal()) { schemaRow["NumericPrecision"] = schemaRow["ColumnSize"]; if (precision > 0) { schemaRow["NumericPrecision"] = precision; } schemaRow["NumericScale"] = _fields[i].NumericScale * (-1); } schemaRow["DataType"] = GetFieldType(i); schemaRow["ProviderType"] = GetProviderType(i); schemaRow["IsLong"] = _fields[i].IsLong(); schemaRow["AllowDBNull"] = _fields[i].AllowDBNull(); schemaRow["IsRowVersion"] = false; schemaRow["IsAutoIncrement"] = false; schemaRow["IsReadOnly"] = isReadOnly; schemaRow["IsKey"] = isKeyColumn; schemaRow["IsUnique"] = isUnique; schemaRow["IsAliased"] = _fields[i].IsAliased(); schemaRow["IsExpression"] = isExpression; schemaRow["BaseSchemaName"] = DBNull.Value; schemaRow["BaseCatalogName"] = DBNull.Value; schemaRow["BaseTableName"] = _fields[i].Relation; schemaRow["BaseColumnName"] = _fields[i].Name; _schemaTable.Rows.Add(schemaRow); if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation) { tableCount++; currentTable = _fields[i].Relation; } await schemaCmd.Close(async).ConfigureAwait(false); } if (tableCount > 1) { foreach (DataRow row in _schemaTable.Rows) { row["IsKey"] = false; row["IsUnique"] = false; } } _schemaTable.EndLoadData(); } finally { #if NET48 || NETSTANDARD2_0 schemaCmd.Dispose(); #else await async.AsyncSyncCallNoCancellation(schemaCmd.DisposeAsync, schemaCmd.Dispose).ConfigureAwait(false); #endif } return(_schemaTable); }
public override DataTable GetSchemaTable() { CheckState(); if (_schemaTable != null) { return(_schemaTable); } DataRow schemaRow = null; int tableCount = 0; string currentTable = string.Empty; _schemaTable = GetSchemaTableStructure(); /* Prepare statement for schema fields information */ FbCommand schemaCmd = new FbCommand( GetSchemaCommandText(), _command.Connection, _command.Connection.InnerConnection.ActiveTransaction); schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31); schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31); schemaCmd.Prepare(); _schemaTable.BeginLoadData(); for (int i = 0; i < _fields.Count; i++) { bool isKeyColumn = false; bool isUnique = false; bool isReadOnly = false; int precision = 0; bool isExpression = false; /* Get Schema data for the field */ schemaCmd.Parameters[0].Value = _fields[i].Relation; schemaCmd.Parameters[1].Value = _fields[i].Name; using (FbDataReader r = schemaCmd.ExecuteReader()) { if (r.Read()) { isReadOnly = (IsReadOnly(r) || IsExpression(r)) ? true : false; isKeyColumn = (r.GetInt32(2) == 1) ? true : false; isUnique = (r.GetInt32(3) == 1) ? true : false; precision = r.IsDBNull(4) ? -1 : r.GetInt32(4); isExpression = IsExpression(r); } } /* Create new row for the Schema Table */ schemaRow = _schemaTable.NewRow(); schemaRow["ColumnName"] = GetName(i); schemaRow["ColumnOrdinal"] = i; schemaRow["ColumnSize"] = _fields[i].GetSize(); if (_fields[i].IsDecimal()) { schemaRow["NumericPrecision"] = schemaRow["ColumnSize"]; if (precision > 0) { schemaRow["NumericPrecision"] = precision; } schemaRow["NumericScale"] = _fields[i].NumericScale * (-1); } schemaRow["DataType"] = GetFieldType(i); schemaRow["ProviderType"] = GetProviderType(i); schemaRow["IsLong"] = _fields[i].IsLong(); schemaRow["AllowDBNull"] = _fields[i].AllowDBNull(); schemaRow["IsRowVersion"] = false; schemaRow["IsAutoIncrement"] = false; schemaRow["IsReadOnly"] = isReadOnly; schemaRow["IsKey"] = isKeyColumn; schemaRow["IsUnique"] = isUnique; schemaRow["IsAliased"] = _fields[i].IsAliased(); schemaRow["IsExpression"] = isExpression; schemaRow["BaseSchemaName"] = DBNull.Value; schemaRow["BaseCatalogName"] = DBNull.Value; schemaRow["BaseTableName"] = _fields[i].Relation; schemaRow["BaseColumnName"] = _fields[i].Name; _schemaTable.Rows.Add(schemaRow); if (!String.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation) { tableCount++; currentTable = _fields[i].Relation; } /* Close statement */ schemaCmd.Close(); } if (tableCount > 1) { foreach (DataRow row in _schemaTable.Rows) { row["IsKey"] = false; row["IsUnique"] = false; } } _schemaTable.EndLoadData(); /* Dispose command */ schemaCmd.Dispose(); return(_schemaTable); }
public void RemovePreparedCommand(FbCommand command) { _preparedCommands.Remove(command); }