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 void SavePointTest()
{
FbCommand command = new FbCommand();
Console.WriteLine("Iniciada nueva transaccion");
Transaction = Connection.BeginTransaction("InitialSavePoint");
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = "insert into TEST (INT_FIELD) values (200) ";
command.ExecuteNonQuery();
Transaction.Save("FirstSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (201) ";
command.ExecuteNonQuery();
Transaction.Save("SecondSavePoint");
command.CommandText = "insert into TEST (INT_FIELD) values (202) ";
command.ExecuteNonQuery();
Transaction.Rollback("InitialSavePoint");
Transaction.Commit();
command.Dispose();
}
protected string _executeNonQuery(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms, bool isReturning)
{
string ret = "";
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
foreach (var prm in prms)
cmd.Parameters.AddWithValue(prm.Key, prm.Value);
if (isReturning)
{
FbParameter outparam = new FbParameter("@out", FbDbType.VarChar)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outparam);
cmd.ExecuteNonQuery();
ret = outparam.Value as string;
}
else
cmd.ExecuteNonQuery();
}
return ret;
}
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 InsertGuidTest()
{
FbCommand createTable = new FbCommand("CREATE TABLE GUID_TEST (GUID_FIELD CHAR(16) CHARACTER SET OCTETS)", Connection);
createTable.ExecuteNonQuery();
createTable.Dispose();
Guid newGuid = Guid.Empty;
Guid guidValue = Guid.NewGuid();
// Insert the Guid
FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (GUID_FIELD) VALUES (@GuidValue)", Connection);
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = guidValue;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
FbCommand select = new FbCommand("SELECT * FROM GUID_TEST", Connection);
using (FbDataReader r = select.ExecuteReader())
{
if (r.Read())
{
newGuid = r.GetGuid(0);
}
}
Assert.AreEqual(guidValue, newGuid);
}
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 string RetPrestadorEmail()
{
belConnection cx = new belConnection();
try
{
string sMsgPadraoEmail = "{5}Razão Social:{1}{0}{5}E-mail: {2}{0}{5}CCM :{3}{0}{5}CNPJ:{4}{0}{0}";
StringBuilder sQuery = new StringBuilder();
sQuery.Append(" select empresa.cd_cgc, empresa.cd_inscrmu, empresa.cd_email, empresa.nm_empresa from empresa ");
sQuery.Append(" where empresa.cd_empresa = '" + belStatic.codEmpresaNFe + "'");
FbCommand Comand = new FbCommand(sQuery.ToString(), cx.get_Conexao());
cx.Open_Conexao();
Comand.ExecuteNonQuery();
FbDataReader dr = Comand.ExecuteReader();
dr.Read();
objtcIdentificacaoPrestador = new tcIdentificacaoPrestador();
return string.Format(sMsgPadraoEmail, "<br>",
dr["nm_empresa"].ToString(),
dr["cd_email"].ToString(),
dr["cd_inscrmu"].ToString(),
dr["cd_cgc"].ToString(), " ");
}
catch (Exception ex)
{
throw;
}
finally
{
cx.Close_Conexao();
}
}
public void AbortTransaction()
{
FbTransaction transaction = null;
FbCommand command = null;
try
{
transaction = this.Connection.BeginTransaction();
command = new FbCommand("ALTER TABLE \"TEST\" drop \"INT_FIELD\"", this.Connection, transaction);
command.ExecuteNonQuery();
transaction.Commit();
transaction = null;
}
catch (Exception)
{
transaction.Rollback();
transaction = null;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
public void PopulaDadosCancelamento(string sCodConhecimento, string sJustificativa)
{
try
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append("Select ");
sQuery.Append("conhecim.cd_chavecte chCTe, ");
sQuery.Append("conhecim.cd_nprotcte nProt ");
sQuery.Append("from conhecim ");
sQuery.Append("where conhecim.cd_conheci ='" + sCodConhecimento + "' ");
sQuery.Append("and conhecim.cd_empresa = '" + belStatic.CodEmpresaCte + "'");
FbCommand fbConn = new FbCommand(sQuery.ToString(), cx.get_Conexao());
cx.Open_Conexao();
fbConn.ExecuteNonQuery();
FbDataReader dr = fbConn.ExecuteReader();
dr.Read();
objBelCancelaCte.versao = "1.03";
objBelCancelaCte.Id = "ID" + dr["chCTe"].ToString();
objBelCancelaCte.tpAmb = belStatic.TpAmb.ToString();
objBelCancelaCte.xServ = "CANCELAR";
objBelCancelaCte.chCTe = dr["chCTe"].ToString();
objBelCancelaCte.nProt = dr["nProt"].ToString();
objBelCancelaCte.xJust = sJustificativa;
}
catch (Exception)
{
throw;
}
finally { cx.Close_Conexao(); }
}
//Ingresa Clase a la Base de Datos
public Clase ClaseInsertar(Clase AClase)
{
string pSentencia = "INSERT INTO DRASCLA (CLAVE, DESCRIPCION, MANEJAEXTRA, TIPO, ACTIVO) VALUES (@CLAVE, @DESCRIPCION, @MANEJAEXTRA, @TIPO, @ACTIVO)";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@CLAVE", FbDbType.Integer).Value = AClase.Clave;
com.Parameters.Add("@DESCRIPCION", FbDbType.VarChar).Value = AClase.Descripcion;
com.Parameters.Add("@MANEJAEXTRA", FbDbType.VarChar).Value = AClase.Manejaextra;
com.Parameters.Add("@TIPO", FbDbType.VarChar).Value = AClase.Tipo;
com.Parameters.Add("@ACTIVO", FbDbType.SmallInt).Value = AClase.Activo;
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return AClase;
}
private void btnCheck_Click(object sender, EventArgs e)
{
try
{
if (txtPath.Text.Split('@').Length == 2)
{
FbConnectionStringBuilder cnString = new FbConnectionStringBuilder();
cnString.DataSource = txtPath.Text.Split('@')[0];
cnString.Database = txtPath.Text.Split('@')[1];
cnString.UserID = "SYSDBA";
cnString.Password = "******";
cnString.Charset = "win1251";
cnString.Dialect = 3;
using (FbConnection cn = new FbConnection(cnString.ToString()))
{
cn.Open();
FbCommand cmd = new FbCommand("SELECT PRICE.* FROM PRICE", cn);
cmd.ExecuteNonQuery();
}
MessageBox.Show("ok");
}
else
{
MessageBox.Show("Не верный формат строки!");
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message + "\n" + ex.Source);
}
}
public void Editar(Projeto obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "UPDATE PROJETOS SET CLIENTE = @CLIENTE, DESCRICAO = @DESCRICAO, EMPRESA = @EMPRESA, FILIAL = @FILIAL, " +
"HORASGERENTE = @HORAGERENTE, HORASCONSULTOR = @HORACONSULTOR, HORASCOORDENADOR = @HORACOORDENADOR, " +
"META = @META " +
"WHERE CODIGO = @CODIGO";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public void Cadastrar(Projeto obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "INSERT INTO PROJETOS (CLIENTE, EMPRESA, FILIAL, HORASGERENTE, HORASCONSULTOR, HORASCOORDENADOR, DESCRICAO, META) " +
"VALUES (@CLIENTE, @EMPRESA, @FILIAL, @HORAGERENTE, @HORACONSULTOR, @HORACOORDENADOR, @DESCRICAO, @META)";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public string CambiarResumenVerificado(DateTime AFecha, int AArea, int AUsuario)
{
string pLotes = ObtenerLotesArea(AArea);
string[] words = pLotes.Split(',');
string pLote = "";
for (int i = 0; i < words.Length; i++)
{
if (i == 0)
pLote = pLote + "'" + words[i] + "'";
else
pLote = pLote + ",'" + words[i] + "'";
}
string Fecha = AFecha.ToString("MM.dd.yyyy") + ", 00:00:00.000";
string pSentencia = "UPDATE DRASRESUMENCORTES SET VERIFICADO = 'Si', USUARIO_ADMIN = @USUARIO_ADMIN WHERE FECHA ='" + Fecha + "' AND LOTE IN (" + pLote + ")";
Console.WriteLine(pSentencia);
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@USUARIO_ADMIN", FbDbType.VarChar).Value = AUsuario;
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
InsertarVerificada(AFecha, AArea);
return "La producción se marco como buena";
}
public CanalProgramado InsertarLoteSacrificio(CCall ACanal)
{
string pSentencia = "INSERT INTO DRASCCALL (GRANJA, LOTE, FECHA, CANALES) VALUES (@GRANJA, @LOTE, @FECHA, @CANALES) RETURNING CLAVE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@GRANJA", FbDbType.Integer).Value = ACanal.Granja;
com.Parameters.Add("@LOTE", FbDbType.VarChar).Value = ACanal.Lote;
com.Parameters.Add("@FECHA", FbDbType.Integer).Value = ACanal.Fecha;
com.Parameters.Add("@CANALES", FbDbType.Integer).Value = ACanal.Canales;
FbParameter pOutParameter = new FbParameter("@CLAVE", FbDbType.Integer);
pOutParameter.Direction = ParameterDirection.Output;
com.Parameters.Add(pOutParameter);
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return ObtenerCanalProgramado((int)pOutParameter.Value);
}
public bool CamaraEliminarValidacion(int AClave, out string AMensajeError)
{
bool pResult = true;
AMensajeError = "";
string pSentencia = "DELETE FROM DRASVALIDAPTOSCAMARA WHERE ID = @CLAVE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@CLAVE", FbDbType.Integer).Value = AClave;
try
{
con.Open();
try
{
com.ExecuteNonQuery();
}
catch(Exception ex)
{
AMensajeError = ex.Message;
pResult = false;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return pResult;
}
public int atualizar(dsFROGIOS.CLIENTERow cliente)
{
FbCommand comando = null;
string sql =
"UPDATE CLIENTE "
+ "SET "
+ "CLIENTE_TIPO = " + paramTipo + ", "
+ "CLIENTE_TELEFONE = " + paramTelefone + ", "
+ "CLIENTE_CELULAR = " + paramCelular + ", "
+ "CLIENTE_EMAIL = " + paramEmail + ", "
+ "CLIENTE_ENDERECO = " + paramEndereco + ", "
+ "CLIENTE_BAIRRO = " + paramBairro + ", "
+ "CLIENTE_PERIMETRO = " + paramPerimetro + ", "
+ "CLIENTE_CIDADE = " + paramCidade + ", "
+ "CLIENTE_UF = " + paramUf + ", "
+ "CLIENTE_CEP = " + paramCep + " "
+ "WHERE CLIENTE_CODIGO = " + paramCodigo + ";";
comando = new FbCommand(sql, Conexao.getConexao, Conexao.getTransacao);
comando.Parameters.AddWithValue(paramCodigo, cliente.CLIENTE_CODIGO);
comando.Parameters.AddWithValue(paramTipo, cliente.CLIENTE_TIPO);
comando.Parameters.AddWithValue(paramTelefone, cliente.CLIENTE_TELEFONE);
comando.Parameters.AddWithValue(paramCelular, cliente.CLIENTE_CELULAR);
comando.Parameters.AddWithValue(paramEmail, cliente.CLIENTE_EMAIL);
comando.Parameters.AddWithValue(paramEndereco, cliente.CLIENTE_ENDERECO);
comando.Parameters.AddWithValue(paramBairro, cliente.CLIENTE_BAIRRO);
comando.Parameters.AddWithValue(paramPerimetro, cliente.CLIENTE_PERIMETRO);
comando.Parameters.AddWithValue(paramCidade, cliente.CLIENTE_CIDADE);
comando.Parameters.AddWithValue(paramUf, cliente.CLIENTE_UF);
comando.Parameters.AddWithValue(paramCep, cliente.CLIENTE_CEP);
return comando.ExecuteNonQuery();
}
public void Cadastrar(Agendamento obj)
{
FbCommand sqlCommand = new FbCommand();
try
{
sqlCommand.Connection = this._conexao;
sqlCommand.CommandText = "INSERT INTO AGENDAMENTO (EMPRESA, FILIAL, FUNCIONARIO, CLIENTE,STATUS,DATAPREVISTO, " +
"INICIOPREVISTO,FIMPREVISTO,TRASLADOPREVISTO, RESUMOAGENDAMENTO) " +
"VALUES (@EMPRESA,@FILIAL,@FUNCIONARIO,@CLIENTE, @STATUS, @DATAPREVISTO, @INICIOPREVISTO, " +
"@FIMPREVISTO,@TRASLADOPREVISTO, @RESUMOAGENDAMENTO)";
sqlCommand.Parameters.AddRange(this.ParametrizarComando(obj));
sqlCommand.ExecuteNonQuery();
}
catch (FbException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
}
}
public LoteNoInventariable InsertarLoteNoInventariable(LoteNoInventariable ALote)
{
string pSentencia = "INSERT INTO LOTES_NO_INVENTARIABLES (LOTE) VALUES (@LOTE) RETURNING ID";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@LOTE", FbDbType.Integer).Value = ALote.Lote;
FbParameter pOutParameter = new FbParameter("@ID", FbDbType.Integer);
pOutParameter.Direction = ParameterDirection.Output;
com.Parameters.Add(pOutParameter);
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return ObtenerLote((int)pOutParameter.Value);
}
public void InsertNullGuidTest()
{
FbCommand createTable = new FbCommand("CREATE TABLE GUID_TEST (INT_FIELD INTEGER, GUID_FIELD CHAR(16) CHARACTER SET OCTETS)", Connection);
createTable.ExecuteNonQuery();
createTable.Dispose();
// Insert the Guid
FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (INT_FIELD, GUID_FIELD) VALUES (@IntField, @GuidValue)", Connection);
insert.Parameters.Add("@IntField", FbDbType.Integer).Value = this.GetId();
insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = DBNull.Value;
insert.ExecuteNonQuery();
insert.Dispose();
// Select the value
FbCommand select = new FbCommand("SELECT * FROM GUID_TEST", Connection);
using (FbDataReader r = select.ExecuteReader())
{
if (r.Read())
{
if (!r.IsDBNull(1))
{
throw new Exception();
}
}
}
}
public bool EliminarUsuarioPistola(int AClave, out string AMensajeError)
{
bool pResult = true;
AMensajeError = "";
string pSentencia = "UPDATE DRASUSUAP SET ACTIVO='No' WHERE ID=@CLAVE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@CLAVE", FbDbType.Integer).Value = AClave;
try
{
con.Open();
try
{
com.ExecuteNonQuery();
}
catch(Exception ex)
{
AMensajeError = ex.Message;
pResult = false;
}
}
finally
{
if (con.State == System.Data.ConnectionState.Open){
con.Close();
}
}
return pResult;
}
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 belEscrituracao(belInfNFe pInfNFe, FbConnection pfbConexao)
{
bProdutorRural = false;
objInfNFe = pInfNFe;
string scdClifor = string.Empty;
Conexao = pfbConexao;//MontaConexaoEscritor();
if (objInfNFe.Empresa != null)
{
string sTipoLanc = TipoLancamento();
string sDoc = string.Empty;
#region Clientes/Fornecedores
if (objInfNFe.BelDest.Cnpj != null)
{
sDoc = FormataString(objInfNFe.BelDest.Cnpj.ToString(), "CNPJ");
}
else
{
sDoc = FormataString(objInfNFe.BelDest.Cpf.ToString(), "CPF");
}
if (!RegistroExiste("CLIFOR", (objInfNFe.BelDest.Cnpj != null ? "CD_CGC = '" : "CD_CPF ='") + sDoc + "'", "CD_CLIFOR"))
{
scdClifor = CadastraCliFor(sDoc);
}
else
{
scdClifor = BuscaCodigoClifor(sDoc);
}
//OS_28165
String sQuery = string.Format("UPDATE CLIFOR SET CD_MUNIC = '{0}' WHERE CD_CLIFOR = '{1}'", objInfNFe.BelDest.Cmun, scdClifor);
using (FbCommand cmd = new FbCommand(sQuery, Conexao))
{
if (Conexao.State != ConnectionState.Open)
{
Conexao.Open();
}
cmd.ExecuteNonQuery();
}
objInfNFe.Cdclifor = scdClifor;
#endregion
bool bSaida = true; // NotaSaida();
if (sTipoLanc == "E")
{
bSaida = false;
}
Escritura(bSaida);
}
}
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 InsertTest()
{
FbConnectionStringBuilder csb = base.BuildConnectionStringBuilder();
csb.Enlist = true;
using (TransactionScope scope = new TransactionScope())
{
using (FbConnection c = new FbConnection(csb.ToString()))
{
c.Open();
string sql = "insert into TEST (int_field, date_field) values (1002, @date)";
using (FbCommand command = new FbCommand(sql, c))
{
command.Parameters.Add("@date", FbDbType.Date).Value = DateTime.Now.ToString();
int ra = command.ExecuteNonQuery();
Assert.AreEqual(ra, 1);
}
}
scope.Complete();
}
}
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 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();
}
}
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 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 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 override void Rollback(string savePointName)
#endif
{
EnsureSavePointName(savePointName);
EnsureCompleted();
try
{
var command = new FbCommand($"ROLLBACK WORK TO SAVEPOINT {savePointName}", _connection, this);
#if NET48 || NETSTANDARD2_0
using (command)
#else
using (command)
#endif
{
command.ExecuteNonQuery();
}
}
catch (IscException ex)
{
throw FbException.Create(ex);
}
}
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 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 + ")";
}