public void FillFourni() { OleDbCommand Com; OleDbDataReader Dr; try { string DBcom = "SELECT ID_FOURNISSEUR,NOM_FOURNISSEUR FROM FOURNISSEUR"; Com = new OleDbCommand(DBcom, DB); Dr = Com.ExecuteReader(); if (Dr.HasRows == false) { return; } while (Dr.Read()) { Fourni_Code.Add(Dr.GetString(0)); Fourni_Name.Add(Dr.GetString(1)); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); return; } }
public Produtoo FindById(int IdProduto) { try { OpenConnection(); Cmd = new SqlCommand("select * from Produto where IdProduto = @v1", Con); Cmd.Parameters.AddWithValue("@v1", IdProduto); Dr = Cmd.ExecuteReader(); if (Dr.Read()) { Produtoo p = new Produtoo(); p.IdProduto = Dr.GetInt32(0); p.Nome = Dr.GetString(1); p.Preco = Dr.GetDouble(2); p.Quantidade = Dr.GetInt32(3); p.DataCompra = Dr.GetDateTime(4); return(p); } return(null); } catch { throw new Exception("Erro ao Consultar Produto"); } finally { CloseConnection(); } }
public void setFornecedores(string cnpj) { try { Conectar(); Cmd = new SqlCommand("ListarFornecedor", Con); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.AddWithValue("@CNPJ", cnpj); Dr = Cmd.ExecuteReader(); if (Dr.HasRows) { while (Dr.Read()) { fornecedor.cnpj = Dr.GetString(0); fornecedor.descFor = Dr.GetString(1); fornecedor.contatoFor = Dr.GetString(2); fornecedor.enderecoFor = Dr.GetString(3); fornecedor.cidade = Dr.GetString(4); } } } finally { Desconectar(); } }
public List <Produtoo> FindAll() { try { OpenConnection(); Cmd = new SqlCommand("Select * From Produto", Con); Dr = Cmd.ExecuteReader(); List <Produtoo> lista = new List <Produtoo>(); while (Dr.Read()) { Produtoo p = new Produtoo(); p.IdProduto = Dr.GetInt32(0); p.Nome = Dr.GetString(1); p.Preco = Dr.GetDouble(2); p.Quantidade = Dr.GetInt32(3); p.DataCompra = Dr.GetDateTime(4); lista.Add(p); } return(lista); } catch { throw new Exception("Erro ao consultar"); } finally { CloseConnection(); } }
public void setFuncionario(int idFunc) { try{ Conectar(); Cmd = new SqlCommand("ListarFuncionario", Con); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.AddWithValue("@idFuncionario", idFunc); Dr = Cmd.ExecuteReader(); if (Dr.HasRows) { while (Dr.Read()) { Funcionario.idFuncionario = Dr.GetInt32(0); Funcionario.nomeFunc = Dr.GetString(1); Funcionario.senhaFunc = Dr.GetString(2); Funcionario.RG = Dr.GetString(3); Funcionario.salario = Dr.GetDouble(4); Funcionario.email = Dr.GetString(5); Funcionario.telefone = Dr.GetString(6); Funcionario.cep = Dr.GetString(7); } } } finally { Desconectar(); } }
public double getProduto(int codBarras) { double precoUnit = 0; try { Conectar(); Cmd = new SqlCommand("valorUnit", Con); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.AddWithValue("@codBarras", codBarras); Dr = Cmd.ExecuteReader(); if (Dr.HasRows) { while (Dr.Read()) { Produto.idProduto = Dr.GetInt32(0); Produto.descProd = Dr.GetString(1); Produto.preco = Dr.GetDouble(2); Produto.maisValia = Dr.GetDouble(3); } } } finally{ Desconectar(); } return(precoUnit); }
public bool ReabreChamadoDAO() { try { SqlCommand Cmd; SqlDataReader Dr; using (SqlConnection Con = new Conexao().ConexaoDB()) { Cmd = new SqlCommand(@" SELECT * from Chamado where idChamado = @Chamado", Con); Cmd.Parameters.AddWithValue("@Chamado", ObjChamado.Id); Dr = Cmd.ExecuteReader(); while (Dr.Read()) { ObjChamado.Id = Dr.GetInt32(0); ObjChamado.IdCliente = Dr.GetInt32(1); ObjChamado.Assunto = Dr.GetString(2); ObjChamado.Descricao = Dr.GetString(3); ObjChamado.IdStatus = Dr.GetInt32(4); ObjChamado.IdServico = Dr.GetInt32(7); ObjChamado.IdPrioridade = Dr.GetInt32(8); } Dr.Close(); Cmd = new SqlCommand(@" UPDATE [dbo].[Chamado] SET dataFechamento = '2000-01-01 00:00:00.000' ,idStatusChamado = 4 ,ContReabertura = @ContReabertura WHERE idChamado = @Chamado and idEmpresa = @Empresa;", Con); Cmd.Parameters.AddWithValue("@Chamado", ObjChamado.Id); Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa); Cmd.Parameters.AddWithValue("@ContReabertura", new ManterChamadoDAO(ObjChamado).GetContReaberturaDAO() + 1); Cmd.ExecuteNonQuery(); return(true); } } catch (SqlException) { throw; } }
public bool Acesso = false; //Informa se o login é valido public bool Acessar(int userType, string login, String senha) { try { Conectar(); Cmd = new SqlCommand("AcessoSistema", Con); //Procedimento que retorna o login Cmd.CommandType = CommandType.StoredProcedure; Cmd.Parameters.AddWithValue("@userType", userType); Cmd.Parameters.AddWithValue("@login", login); Cmd.Parameters.AddWithValue("@senha", senha); Dr = Cmd.ExecuteReader(); if (Dr.HasRows) //Caso seja o sql tenha retorno, o valor é tido como valido { if (userType == 1) { while (Dr.Read()) { Funcionario.idFuncionario = Dr.GetInt32(0); Funcionario.nomeFunc = Dr.GetString(1); } } else { while (Dr.Read()) { Administrador.idAdmin = Dr.GetInt32(0); Administrador.nomeAdmin = Dr.GetString(1); } } Acesso = true; } } catch (Exception e) { throw new Exception("Erro ao cadastrar: " + e.Message); } finally { Desconectar(); } return(Acesso); }
public List <Servico> ConsultaServicosByTipoDAO() { try { SqlDataReader Dr; SqlCommand Cmd; List <Servico> ServicoList = new List <Servico>(); using (SqlConnection Con = new Conexao().ConexaoDB()) { Cmd = new SqlCommand(@" SELECT * FROM [dbo].[Servico] WHERE ativo = 1 and idTipo = @IdTipo", Con); Cmd.Parameters.AddWithValue("@IdTipo", ObjServico.IdTipo); Dr = Cmd.ExecuteReader(); while (Dr.Read()) { Servico Servicos = FactoryServico.GetNewServico(); Servicos.Id = Dr.GetInt32(0); Servicos.IdTipo = Dr.GetInt32(1); Servicos.NomeServ = Dr.GetString(2); Servicos.DescServ = Dr.GetString(3); Servicos.Sla = Dr.GetInt32(4); Servicos.IdStatus = Dr.GetInt32(5); ServicoList.Add(Servicos); } return(ServicoList); } } catch (SqlException) { throw; } }
public List <courseClass> fetchCourses(int fetch, int jump) { fetch = fetch <= 0 ? 20 : fetch; jump = jump < 0 ? 0 : jump; List <courseClass> response = new List <courseClass>(); try { SqlConnection con = new SqlConnection(data); SqlCommand cmd = new SqlCommand("SELECT * FROM courseData ORDER BY created DESC", con); SqlDataReader Dr; con.Open(); Dr = cmd.ExecuteReader(); while (Dr.Read()) { try { courseClass reader = new courseClass(); //reader.courseId = Dr.GetGuid(0); //reader.name = Dr.GetString(1) == null ? "GeneralName" : Dr.GetString(1); //reader.creator = Dr.GetGuid(2); //reader.company = Dr.GetGuid(3); //reader.status = Dr.GetString(4) == null ? "GeneralName" : Dr.GetString(4); //reader.information = Dr.GetString(5) == null ? "GeneralName" : Dr.GetString(5); //reader.price = Dr.GetInt32(7).ToString() == null ? 0 : Dr.GetInt32(7); //reader.currency = Dr.GetString(8) == null ? "GeneralName" : Dr.GetString(8); //reader.duration = Dr.GetInt32(9).ToString() == null ? 0 : Dr.GetInt32(9); //reader.durationUnit = Dr.GetString(10) == null ? "GeneralName" : Dr.GetString(10); // Experimental reader.courseId = Dr.GetGuid(0); if (Dr.GetString(1) != null) { reader.name = Dr.GetString(1); } if (Dr.GetGuid(2) != null) { reader.creator = Dr.GetGuid(2); } //if (Dr.GetGuid(3) != null) { reader.company = Dr.GetGuid(3); } reader.created = Dr.GetDateTime(4); if (Dr.GetString(5) != null) { reader.status = Dr.GetString(5); } if (Dr.GetString(6) != null) { reader.information = Dr.GetString(6); } reader.price = 49; reader.currency = "EUR"; reader.duration = 6; reader.durationUnit = "DAY"; response.Add(reader); } catch (Exception ex) { courseClass reader = new courseClass(); reader.status = ex.ToString(); response.Add(reader); continue; } } Dr.Close(); con.Close(); if (response.Count <= 0) { for (int i = 0; i < 5; i++) { courseClass reader = new courseClass(); reader.name = "MyCourse" + i; reader.information = "My new Course is about something cool"; response.Add(reader); } } return(response); } catch (SqlException ex) { courseClass reader = new courseClass(); reader.courseId = new Guid(); reader.name = ""; reader.creator = new Guid(); reader.company = new Guid(); reader.created = DateTime.Now; reader.status = ""; reader.information = ex.ToString(); reader.img = ""; reader.price = 0; reader.currency = ""; reader.duration = 0; reader.durationUnit = ""; response.Add(reader); } catch (Exception e) { // Add error reporting courseClass reader = new courseClass(); reader.courseId = new Guid(); reader.name = ""; reader.creator = new Guid(); reader.company = new Guid(); reader.created = DateTime.Now; reader.status = ""; reader.information = e.ToString(); reader.img = ""; reader.price = 1; reader.currency = ""; reader.duration = 1; reader.durationUnit = ""; response.Add(reader); } return(response); }
/// <summary> /// Read in data, pivot & buffer for supplied set of rows. /// This is called for retrieval only, not for search /// </summary> /// <param name="eqp"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { DbCommandMx drd; int rowsFetched = 0, vosCreated = 0; MetaTable mt; MetaColumn mc = null; DateTime dt; PivotMetaBroker mb; List <GenericMetaBroker> mbList; string cid, pivotKey, tableFilter, s, txt, tok; int fci, mci, pvi, pci, si, i1; object[] vo = null; object o; if (!PivotInCode) // let Oracle do the pivoting? { base.ExecuteQuery(eqp); return; } // Self-pivot. Read & buffer data for all query tables from same Source/TableFilterColumns for key set if we are the first table for Source int t0 = TimeOfDay.Milliseconds(); Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo; mt = eqp.QueryTable.MetaTable; string sourceKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql MultiTablePivotBrokerTypeData mpd = (MultiTablePivotBrokerTypeData)mbsi[sourceKey]; if (mpd.FirstTableName != mt.Name) { return; // retrieve data for all tables when we see first table } mpd.ClearBuffers(); // Build sql StringBuilder sb = new StringBuilder(); // build filter to select for desired metatable tableFilter = ""; if (mt.TableFilterColumns.Count == 1) { // build single in list if single filter column foreach (string mtName in mpd.MbInstances.Keys) { mt = MetaTableCollection.Get(mtName); if (sb.Length > 0) { sb.Append(","); } sb.Append(mt.TableFilterValues[0]); tableFilter = mt.TableFilterColumns[0] + " in (" + sb.ToString() + ")"; } } else // multiple table filter columns, build and/or expressions { foreach (string mtName in mpd.MbInstances.Keys) { mt = MetaTableCollection.Get(mtName); if (sb.Length > 0) { sb.Append(" or "); } tableFilter = "(" + GetTableFilterCriteria(mt) + ")"; sb.Append(tableFilter); } tableFilter = "(" + sb.ToString() + ")"; } string sql = "select * from " + mt.TableMap + " where "; if (tableFilter != "") { sql += tableFilter + " and "; } sql += mt.KeyMetaColumn.ColumnMap + " in (<list>) "; // Read unpivoted data, merge/pivot & buffer pivoted rows List <string> keySubset = eqp.SearchKeySubset; if (keySubset == null) { keySubset = GetPreviewSubset(); // assume previewing of single table if no subset } List <string> parmList = new List <string>(); for (i1 = 0; i1 < keySubset.Count; i1++) // copy keys to parameter array properly normalized { string key = CompoundId.NormalizeForDatabase((string)keySubset[i1], Qt.MetaTable); if (key != null) { parmList.Add(key); } } drd = new DbCommandMx(); drd.PrepareListReader(sql, DbType.String); drd.ExecuteListReader(parmList); while (drd.Read()) { rowsFetched++; string tableFilterKey = ""; // get column values to identify table for (fci = 0; fci < mt.TableFilterColumns.Count; fci++) { o = drd.GetObjectByName(mt.TableFilterColumns[fci]); if (o == null) { s = ""; } else { s = o.ToString(); } if (tableFilterKey != "") { tableFilterKey += ", "; } tableFilterKey += s; } mt = mpd.TableFilterValuesToMetaTableDict[tableFilterKey]; if (mt == null) { continue; // continue if don't know about this table } if (!mpd.MbInstances.ContainsKey(mt.Name)) { continue; // have row hash for broker? } int mbIdx = 0; mb = (PivotMetaBroker)mpd.GetFirstBroker(mt.Name, out mbList); while (true) // copy out for each metabroker { mt = mb.Qt.MetaTable; if (mt == null) { continue; } if (mb.MultipivotRowDict == null) { mb.MultipivotRowDict = new Dictionary <string, object[]>(); } string rowKey = ""; for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++) { o = drd.GetObjectByName(mt.PivotMergeColumns[mci]); if (o == null) { s = "<null>"; } else { s = o.ToString(); } rowKey += "<" + s + ">"; } if (mb.MultipivotRowDict.ContainsKey(rowKey)) // have entry for row? { vo = (object[])mb.MultipivotRowDict[rowKey]; } else // new row, create vo for it & fill in merged column values { vo = new Object[mb.SelectList.Count]; for (si = 0; si < mb.SelectList.Count; si++) // transfer non-pivoted values { mc = mb.SelectList[si]; if (mc.PivotValues != null) { continue; // skip pivoted cols for now } for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++) { if (Lex.Eq(mc.ColumnMap, mt.PivotMergeColumns[mci])) { o = drd.GetObjectByName(mt.PivotMergeColumns[mci]); if (mc.IsKey) // normalize cid adding prefix as needed { o = CompoundId.Normalize(o.ToString(), mt); } vo[si] = o; break; } } } mb.MultipivotRowDict[rowKey] = vo; vosCreated++; } // Pivot out data based on pivot column values if (mb.PivotKeys == null) { // build set of pivot keys for the pivoted columns in the table if not done yet mb.PivotKeys = new string[mb.SelectList.Count]; for (si = 0; si < mb.SelectList.Count; si++) { mc = mb.SelectList[si]; if (mc.PivotValues == null) { continue; // skip non-pivoted cols } pivotKey = ""; for (pvi = 0; pvi < mc.PivotValues.Count; pvi++) { pivotKey += "<" + mc.PivotValues[pvi].ToLower() + ">"; } mb.PivotKeys[si] = pivotKey; } } pivotKey = ""; for (pci = 0; pci < mt.PivotColumns.Count; pci++) { // build pivot key for this unpivoted row o = drd.GetObjectByName(mt.PivotColumns[pci]); if (o == null) { s = "<null>"; } else { s = o.ToString().ToLower(); } pivotKey += "<" + s + ">"; } for (si = 0; si < mb.SelectList.Count; si++) // transfer pivoted values { if (mb.PivotKeys[si] == null || // skip non-pivoted cols pivotKey != mb.PivotKeys[si]) { continue; // and non-matches } mc = mb.SelectList[si]; int ci = drd.Rdr.GetOrdinal(mc.ColumnMap); if (mc.DataType == MetaColumnType.Integer) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetInt(ci); } else // value with possible resultId, linking information { txt = drd.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Number) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetDouble(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.QualifiedNo) { // todo } else if (mc.DataType == MetaColumnType.String) { if (!mc.DetailsAvailable) { vo[si] = drd.GetString(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Date) { if (!mc.DetailsAvailable) // simple scalar value { vo[si] = drd.GetDateTime(ci); } else // value with possible resultId, linking information { txt = Dr.GetString(ci); // todo: fix for annotation vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc); } } else if (mc.DataType == MetaColumnType.Structure) { // structures come in as compound identifiers (todo: fix for annotation) tok = Dr.GetValue(si).ToString(); cid = CompoundId.Normalize(tok, Qt.MetaTable); vo[si] = cid; } else if (mc.DataType == MetaColumnType.MolFormula) { vo[si] = drd.GetString(ci); } else if (mc.DataType == MetaColumnType.DictionaryId) { try // Id may be string or integer value { vo[si] = drd.GetString(ci); } catch (Exception ex) { vo[si] = drd.GetInt(ci); } } else if (mc.DataType == MetaColumnType.Image) { try // Id may be string or integer value { vo[si] = drd.GetString(ci); } catch (Exception ex) { vo[si] = drd.GetInt(ci); } } else if (mc.DataType == MetaColumnType.Hyperlink) { txt = drd.GetString(ci); Hyperlink hlink = new Hyperlink(); vo[si] = hlink; hlink.Text = txt; } } if (mbList == null) { break; // single broker } mbIdx++; // go to next broker if (mbIdx >= mbList.Count) { break; // at end of brokers? } mb = (PivotMetaBroker)mbList[mbIdx]; } // end of broker loop } // end of read loop drd.Dispose(); return; }