// Iniciar Sessão public OleDbDataReader IniciarSessao(mdlFuncionario _funcionario) { try { AbrirConexao(); ComandoDB = new OleDbCommand("select * from tb_Funcionario where Email = @Email and Senha = @Senha and Status = @Status", ConexaoDB); var pmtEmail = ComandoDB.CreateParameter(); pmtEmail.ParameterName = "@Email"; pmtEmail.DbType = DbType.String; pmtEmail.Value = _funcionario.Email; ComandoDB.Parameters.Add(pmtEmail); var pmtSenha = ComandoDB.CreateParameter(); pmtSenha.ParameterName = "@Senha"; pmtSenha.DbType = DbType.String; pmtSenha.Value = _funcionario.Senha; ComandoDB.Parameters.Add(pmtSenha); var pmtStatus = ComandoDB.CreateParameter(); pmtStatus.ParameterName = "@Status"; pmtStatus.DbType = DbType.Boolean; pmtStatus.Value = _funcionario.Status; ComandoDB.Parameters.Add(pmtStatus); DataReaderDB = ComandoDB.ExecuteReader(); return(DataReaderDB); } catch (Exception ex) { throw new Exception(ex.Message); } }
private void button2_Click(object sender, System.EventArgs e) { button1.Text = "TOTAL PAID"; const String sql = @"SELECT ReceiptID,ID,Name,Paid,Due FROM lastpays where [Dateofpayment] >= ? AND [Dateofpayment] < ?"; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = sql; OleDbParameter pFrom = cmd.CreateParameter(); pFrom.OleDbType = OleDbType.Date; pFrom.Value = dateTimePicker1.Value.Date; cmd.Parameters.Add(pFrom); OleDbParameter pTo = cmd.CreateParameter(); pTo.OleDbType = OleDbType.Date; pTo.Value = dateTimePicker2.Value.Date; cmd.Parameters.Add(pTo); OleDbDataAdapter da = new OleDbDataAdapter(selectCommand: cmd); DataTable data = new DataTable(); da.Fill(data); dataGridView1.DataSource = data; int sum = 0; for (int i = 0; i < dataGridView1.Rows.Count; ++i) { sum += Convert.ToInt32(dataGridView1.Rows[i].Cells[3].Value); } textBox1.Text = sum.ToString(); }
public bool CadastrarTecnicoMDL(global::CamadaModelos.mdlTecnico _mdlTecnico) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Usuario\source\repos\Chamados\Chamados\bin\Debug\chamadosint.tcm"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string Query = "insert into tb_tecnicos(Nome, Ramal) values(@Nome, @Ramal)"; OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB); var pmtNome = cmd.CreateParameter(); pmtNome.ParameterName = "@Nome"; pmtNome.DbType = DbType.String; pmtNome.Value = _mdlTecnico.Nome; cmd.Parameters.Add(pmtNome); var pmtRamal = cmd.CreateParameter(); pmtRamal.ParameterName = "@Ramal"; pmtRamal.DbType = DbType.String; pmtRamal.Value = _mdlTecnico.Ramal; cmd.Parameters.Add(pmtRamal); if (cmd.ExecuteNonQuery() > 0) { ConexaoDB.Close(); return(true); } else { ConexaoDB.Close(); return(false); } }
public static Boolean UpdateGrammarWithPoPInfo(int[] pop) { using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbCommand query = new OleDbCommand("UPDATE Grammars SET city_id = ?, county_id = ?, country_id = ? WHERE Grammar = ?", connection); var city_param = query.CreateParameter(); city_param.Value = pop[3].ToString(); var county_param = query.CreateParameter(); county_param.Value = pop[2].ToString(); var country_param = query.CreateParameter(); country_param.Value = pop[1].ToString(); var grammar_param = query.CreateParameter(); grammar_param.Value = pop[0].ToString(); query.Parameters.Add(city_param); query.Parameters.Add(county_param); query.Parameters.Add(country_param); query.Parameters.Add(grammar_param); return(query.ExecuteNonQuery() == 1); } }
protected void changesUserInfo() { OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings ["onlineStoreConnString"].ConnectionString; conn.Open(); OleDbCommand comm = conn.CreateCommand(); comm.CommandText = "UPDATE Users SET Email=?, Passwd=? WHERE UserID=?"; OleDbParameter param; param = comm.CreateParameter(); param.DbType = DbType.String; param.Direction = ParameterDirection.Input; param.Value = txtEmail.Text; comm.Parameters.Add(param); param = comm.CreateParameter(); param.DbType = DbType.String; param.Direction = ParameterDirection.Input; param.Value = txtPassword.Text; comm.Parameters.Add(param); param = comm.CreateParameter(); param.DbType = DbType.String; param.Direction = ParameterDirection.Input; param.Value = Session["LoggedInId"]; comm.Parameters.Add(param); int totalCount = comm.ExecuteNonQuery(); conn.Close(); }
public ActionResult Create(Tarefas task) { var c1 = new Conexao(); c1.conexaodb.Open(); string query = "INSERT INTO tbl_tarefas (nome_tarefa, descricao_tarefa, data_tarefa) VALUES (@Nome, @Descricao, @Data)"; OleDbCommand cmd = new OleDbCommand(query, c1.conexaodb); var pmtNome = cmd.CreateParameter(); pmtNome.ParameterName = "@Nome"; pmtNome.DbType = DbType.String; pmtNome.Value = task.NomeTarefa; cmd.Parameters.Add(pmtNome); var pmtDescricao = cmd.CreateParameter(); pmtDescricao.ParameterName = "@Descricao"; pmtDescricao.DbType = DbType.String; pmtDescricao.Value = task.DescricaoTarefa; cmd.Parameters.Add(pmtDescricao); var pmtData = cmd.CreateParameter(); pmtData.ParameterName = "@Data"; pmtData.DbType = DbType.DateTime; pmtData.Value = task.DataTarefa; cmd.Parameters.Add(pmtData); cmd.ExecuteNonQuery(); c1.conexaodb.Close(); return(RedirectToAction("Index")); }
public static bool AlteraEmpresaMDL(global::helpdesk2018.Model.mdlManutencaoEmpresas _mdlmanutencaoempresa) { Conexao conexao = new Conexao(); conexao.abrir(); string Query = @" update tb_empresas set nome = @nome, telefone = @telefone, endereco = @endereco, ativa = @ativa where idempresa = @idempresa "; OleDbCommand cmd = new OleDbCommand(Query, conexao.GetConexao()); cmd.CommandType = CommandType.Text; OleDbParameter pmtnome = cmd.CreateParameter(); pmtnome.ParameterName = "@nome"; pmtnome.DbType = DbType.String; pmtnome.Value = _mdlmanutencaoempresa.Nome; cmd.Parameters.Add(pmtnome); OleDbParameter pmttelefone = cmd.CreateParameter(); pmttelefone.ParameterName = "@telefone"; pmttelefone.DbType = DbType.String; pmttelefone.Value = _mdlmanutencaoempresa.Telefone; cmd.Parameters.Add(pmttelefone); OleDbParameter pmtendereco = cmd.CreateParameter(); pmtendereco.ParameterName = "@endereco"; pmtendereco.DbType = DbType.String; pmtendereco.Value = _mdlmanutencaoempresa.Endereco; cmd.Parameters.Add(pmtendereco); OleDbParameter pmtativa = cmd.CreateParameter(); pmtativa.ParameterName = "@ativa"; pmtativa.DbType = DbType.Boolean; pmtativa.Value = _mdlmanutencaoempresa.Ativa; cmd.Parameters.Add(pmtativa); OleDbParameter pmtempresa = cmd.CreateParameter(); pmtempresa.ParameterName = "@idempresa"; pmtempresa.DbType = DbType.Int16; pmtempresa.Value = _mdlmanutencaoempresa.ID; cmd.Parameters.Add(pmtempresa); int resultado = cmd.ExecuteNonQuery(); conexao.Fechar(); return(resultado > 0); } // fim altera motivo
/// <summary> /// Incorporar Lecturas /// </summary> /// <param name="sigpiDao"></param> /// <param name="Tabla"></param> /// <param name="FechaIncorporacion"></param> /// <param name="Lecturas"></param> /// <returns></returns> public bool IncorporarLecturas(SIGPIDao sigpiDao, string Tabla, DateTime FechaIncorporacion, List <Lectura> Lecturas) { string sSqlDelete = "DELETE FROM " + Tabla + " WHERE FECHA = ?"; OleDbCommand command = new OleDbCommand(sSqlDelete, sigpiDao.LocalDBConnection); OleDbParameter paramFecha = command.CreateParameter(); paramFecha.Value = FechaIncorporacion; command.Parameters.Add(paramFecha); try { command.ExecuteNonQuery(); } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); return(false); } //command = sigpiDao.LocalDBConnection.CreateCommand(); command.Parameters.Clear(); command.CommandText = "INSERT INTO " + Tabla + " (codigo, lectura, fecha,x,y) VALUES (?,?,?,?,?)"; OleDbParameter paramCodigo = command.CreateParameter(); OleDbParameter paramValor = command.CreateParameter(); OleDbParameter paramX = command.CreateParameter(); OleDbParameter paramY = command.CreateParameter(); command.Parameters.Add(paramCodigo); command.Parameters.Add(paramValor); command.Parameters.Add(paramFecha); command.Parameters.Add(paramX); command.Parameters.Add(paramY); foreach (Lectura lectura in Lecturas) { //command.CommandText = "INSERT INTO " + Tabla + " (codigo, lectura, fecha) VALUES (" + lectura.Codigo // + "," + lectura.Valor.ToString().Replace(',','.') + ",'" + FechaIncorporacion + "')"; //System.Windows.Forms.MessageBox.Show(command.CommandText + "\n" + lectura.Codigo.ToString() + " :: " + lectura.Valor.ToString() + " :: " + FechaIncorporacion.ToShortDateString()); paramCodigo.Value = lectura.Codigo; paramValor.Value = lectura.Valor; paramFecha.Value = FechaIncorporacion; paramX.Value = lectura.X; paramY.Value = lectura.Y; try { command.ExecuteNonQuery(); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message + "\n" + command.CommandText + "\n" + lectura.Codigo.ToString() + " :: " + lectura.Valor.ToString() + " :: " + FechaIncorporacion.ToShortDateString(), "IncorporarLecturas"); return(false); } } return(true); }
public void Editar(Tarefas aluno) { try { string conexaoAccess = ConfigurationManager.ConnectionStrings["conexaoAccess"].ToString(); OleDbConnection conexaoDb = new OleDbConnection(conexaoAccess); conexaoDb.Open(); string query = "UPDATE PIM_TABELA SET aluno = ?, Tarefa = ?, Data_Entrega = ? WHERE Código = ?"; OleDbCommand cmd = new OleDbCommand(query, conexaoDb); var parametroNome = cmd.CreateParameter(); parametroNome.ParameterName = "@Nome"; parametroNome.DbType = DbType.String; parametroNome.Value = aluno.Nome; cmd.Parameters.Add(parametroNome); var parametroTarefa = cmd.CreateParameter(); parametroTarefa.ParameterName = "@Tarefa"; parametroTarefa.DbType = DbType.String; parametroTarefa.Value = aluno.Tarefa; cmd.Parameters.Add(parametroTarefa); var parametroData = cmd.CreateParameter(); parametroData.ParameterName = "@Data"; parametroData.DbType = DbType.String; parametroData.Value = aluno.Data; cmd.Parameters.Add(parametroData); var pmtId = cmd.CreateParameter(); pmtId.ParameterName = "@Id"; pmtId.DbType = DbType.Int32; pmtId.Value = aluno.codigo; cmd.Parameters.Add(pmtId); if (cmd.ExecuteNonQuery() > 0) { conexaoDb.Close(); } else { conexaoDb.Close(); } } catch (Exception ex) { } }
protected void DoLogin(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["OnlineStoreConnString"].ConnectionString; try { conn.Open(); OleDbCommand comm = conn.CreateCommand(); comm.Connection = conn; comm.CommandText = "SELECT UserId,FirstName,LastName FROM Users WHERE Username = ? AND passwd=?"; OleDbParameter param = comm.CreateParameter(); param.DbType = DbType.String; param.Direction = ParameterDirection.Input; param.Value = txtUserName.Text; comm.Parameters.Add(param); param = comm.CreateParameter(); param.DbType = DbType.String; param.Direction = ParameterDirection.Input; param.Value = txtPassword.Text; comm.Parameters.Add(param); OleDbDataReader reader = comm.ExecuteReader(); if (reader.HasRows) { reader.Read(); Session["LoggedInId"] = reader["UserID"]; Session["FirstName"] = reader["FirstName"]; Session["LastName"] = reader["LastName"]; lblResults.Text = ""; panelLogin.Visible = false; Response.Redirect("Cart.aspx"); } else { lblResults.Text = "User Name or Password are incorrect."; } } catch { errorMessage.Text = "Database error occured"; } finally { if (conn != null) { conn.Close(); } } }
// "UPDATE PIM_TABELA SET aluno = ?, Tarefa = ?, Data_Entrega = ? WHERE Código = ?" public bool Cadastrar(Tarefas aluno) { try { string conexaoAccess = ConfigurationManager.ConnectionStrings["conexaoAccess"].ToString(); OleDbConnection conexaoDb = new OleDbConnection(conexaoAccess); conexaoDb.Open(); string query = "INSERT INTO PIM_TABELA (aluno, Tarefa, Data_Entrega) VALUES (@Nome, @Tarefa, Data)"; OleDbCommand cmd = new OleDbCommand(query, conexaoDb); var parametroNome = cmd.CreateParameter(); parametroNome.ParameterName = "@Nome"; parametroNome.DbType = DbType.String; parametroNome.Value = aluno.Nome; cmd.Parameters.Add(parametroNome); var parametroTarefa = cmd.CreateParameter(); parametroTarefa.ParameterName = "@Tarefa"; parametroTarefa.DbType = DbType.String; parametroTarefa.Value = aluno.Tarefa; cmd.Parameters.Add(parametroTarefa); var parametroData = cmd.CreateParameter(); parametroData.ParameterName = "@Data"; parametroData.DbType = DbType.String; parametroData.Value = aluno.Data; cmd.Parameters.Add(parametroData); if (cmd.ExecuteNonQuery() > 0) { conexaoDb.Close(); return(true); } else { conexaoDb.Close(); return(false); } } catch (Exception ex) { return(false); } }
public bool AbrirSoChamado(global::CamadaModelos.mdlEmpresa _mdlEmpresa) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.tcm"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string Query = "insert into tb_chamados(aberto, SendoAtendido, fk_idempresa, data) values(@aberto, @SendoAtendido, @fk_idempresa, @data);"; OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB); var pmtAbrirChamado = cmd.CreateParameter(); pmtAbrirChamado.ParameterName = "@aberto"; pmtAbrirChamado.DbType = DbType.String; pmtAbrirChamado.Value = "1"; cmd.Parameters.Add(pmtAbrirChamado); var pmtSendoAtendido = cmd.CreateParameter(); pmtSendoAtendido.ParameterName = "@SendoAtendido"; pmtSendoAtendido.DbType = DbType.String; pmtSendoAtendido.Value = "0"; cmd.Parameters.Add(pmtSendoAtendido); var pmtfkidempresa = cmd.CreateParameter(); pmtfkidempresa.ParameterName = "@fk_idempresa"; pmtfkidempresa.DbType = DbType.String; pmtfkidempresa.Value = _mdlEmpresa.ID; cmd.Parameters.Add(pmtfkidempresa); DateTime dataAtu = DateTime.Now; var pmtData = cmd.CreateParameter(); pmtData.ParameterName = "@data"; pmtData.DbType = DbType.String; pmtData.Value = dataAtu; cmd.Parameters.Add(pmtData); if (cmd.ExecuteNonQuery() > 0) { ConexaoDB.Close(); return(true); } else { ConexaoDB.Close(); return(false); } }
/// <summary> /// Calcula promedio de temperatura /// </summary> /// <param name="SigpiDao"></param> /// <param name="sTablaConsolidados"></param> /// <param name="sTablaLecturas"></param> /// <param name="FechaCalculos"></param> /// <param name="sCampoPromedioTemp"></param> /// <param name="NumDias"></param> /// <returns></returns> public bool CalcularResultadosTemperatura(SIGPIDao SigpiDao, string sTablaConsolidados, string sTablaLecturas, DateTime FechaCalculos, string sCampoPromedioTemp, int NumDias) { string sSQL = "DROP TABLE " + sTablaConsolidados; try { SigpiDao.EjecutarSentenciaSinQuery(sSQL); } catch (Exception) { //throw new Exception(e.Message); } DateTime FechaCalculosInicial = FechaCalculos.AddDays(-1 * (NumDias - 1)); //sSQL = "SELECT CODIGO, Avg(LECTURA) AS T5, Max(LECTUS_TEMPE.FECHA) AS FECHA INTO " + // sTablaConsolidados + " From " + sTablaLecturas + // " WHERE FECHA <=#" + FechaCalculos.ToString("MM/dd/yyyy") + "#" + // " AND FECHA >=#" + FechaCalculosInicial.ToString("MM/dd/yyyy") + "#" + // " GROUP BY CODIGO"; sSQL = "SELECT CODIGO, Avg(LECTURA) AS " + sCampoPromedioTemp + ", Max(LECTUS_TEMPE.FECHA) AS FECHA, " + NumDias.ToString() + " AS NO_DIAS INTO " + sTablaConsolidados + " From " + sTablaLecturas + " WHERE FECHA <= ?" + " AND FECHA >= ?" + " GROUP BY CODIGO"; OleDbCommand command = SigpiDao.LocalDBConnection.CreateCommand(); command.CommandText = sSQL; OleDbParameter paramFechaCalculos = command.CreateParameter(); OleDbParameter paramFechaCalculosInicial = command.CreateParameter(); paramFechaCalculos.Value = FechaCalculos; paramFechaCalculosInicial.Value = FechaCalculosInicial; command.Parameters.Add(paramFechaCalculos); command.Parameters.Add(paramFechaCalculosInicial); try { command.ExecuteNonQuery(); } catch (Exception e) { throw new Exception(e.Message); } return(true); }
public static SubsidiaryContent[] GetSubsidiaryContentsFromGrammar(string grammarId) { ArrayList subsidiaryContents = new ArrayList(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbCommand query = new OleDbCommand("SELECT Grammars.Grammar, sc.Sub_contents, Sub_Contents_Id as sub_id FROM Grammars INNER JOIN (Grammar_Subsidiary_Contents gsc INNER JOIN Subsidiary_Contents sc ON gsc.Sub_contents = sc.Sub_contents_Id) ON Grammars.Grammar = gsc.Grammar WHERE Grammars.Grammar = ?", connection); OleDbParameter param_grammar = query.CreateParameter(); param_grammar.Value = grammarId; query.Parameters.Add(param_grammar); OleDbDataReader reader = query.ExecuteReader(); while (reader.Read()) { SubsidiaryContent newSC = new SubsidiaryContent(); newSC.Sub_content_id = Convert.ToInt32(reader["sub_id"]); newSC.Sub_content_name = reader["Sub_contents"].ToString(); subsidiaryContents.Add(newSC); } } return((SubsidiaryContent[])subsidiaryContents.ToArray(typeof(SubsidiaryContent))); }
public static Library[] GetHoldingLibrariesFromGrammar(string grammarId) { ArrayList libraries = new ArrayList(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbCommand query = new OleDbCommand("SELECT Grammars.Grammar, hl.Description as lib_desc, hl.Library as code FROM Grammars INNER JOIN( Grammars_Holding_Libraries ghl INNER JOIN Holding_Libraries hl ON ghl.Library = hl.Library ) ON Grammars.Grammar = ghl.Grammar WHERE Grammars.Grammar = ? ", connection); OleDbParameter param_grammar = query.CreateParameter(); param_grammar.Value = grammarId; query.Parameters.Add(param_grammar); OleDbDataReader reader = query.ExecuteReader(); while (reader.Read()) { Library newLib = new Library(); newLib.Code = reader["code"].ToString(); newLib.Library_name = reader["lib_desc"].ToString(); libraries.Add(newLib); } } return((Library[])libraries.ToArray(typeof(Library))); }
public static Reference[] GetReferenceDataFromGrammar(string grammarId) { ArrayList references = new ArrayList(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbCommand query = new OleDbCommand("SELECT Grammars.Grammar, Also_In.Also_In as ref_id, Desciption_Also_In as description, [Agrupación] as group_id FROM Grammars INNER JOIN(Grammars_Also_In INNER JOIN Also_In ON Grammars_Also_In.Also_In = Also_In.Also_In) ON Grammars.Grammar = Grammars_Also_In.Grammar WHERE Grammars.Grammar = ?", connection); OleDbParameter param_grammar = query.CreateParameter(); param_grammar.Value = grammarId; query.Parameters.Add(param_grammar); OleDbDataReader reader = query.ExecuteReader(); while (reader.Read()) { Reference newRef = new Reference(); newRef.Reference_id = Convert.ToInt32(reader["ref_id"]); newRef.Group = Convert.ToInt32(reader["group_id"]); newRef.Description = reader["description"].ToString(); references.Add(newRef); } } return((Reference[])references.ToArray(typeof(Reference))); }
public static Imprint GetImprintDataFromGrammar(string grammarId) { Imprint res = new Imprint(); using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbCommand query = new OleDbCommand("SELECT Grammars.*, City as city_name, County as county_name, Country as country_name FROM Grammars, Cities c, Counties co, Country cr WHERE Grammar = ? and Grammars.city_id = c.City_id and Grammars.county_id = co.County_Id and Grammars.country_id = cr.Country_Id", connection); var queryParam = query.CreateParameter(); queryParam.Value = grammarId.ToString(); query.Parameters.Add(queryParam); OleDbDataReader reader = query.ExecuteReader(); if (reader.Read()) { res.Grammar_id = Convert.ToInt32(grammarId); res.City_name = reader["city_name"].ToString(); res.County_name = reader["county_name"].ToString(); res.Country_name = reader["country_name"].ToString(); res.City_id = Convert.ToInt32(reader["city_id"]); res.County_id = Convert.ToInt32(reader["county_id"]); res.Country_id = Convert.ToInt32(reader["country_id"]); res.Printers = reader["Printers"].ToString(); res.Booksellers = reader["Booksellers"].ToString(); res.Price = reader["Price"].ToString(); res.Description = reader["Physical_Description"].ToString(); } } return(res); }
public DataTable PesquisaFollowUPIndividual(global::CamadaModelos.mdlEmpresa _mdlEmpresa) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\EP3.tcm"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string Query = "Select chvent, M_desc, Dt_fwu " + "From fwu " + "where chvent=@id order by Dt_fwu desc"; OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB); cmd.CommandType = CommandType.Text; OleDbParameter pmtID = cmd.CreateParameter(); pmtID.ParameterName = "@id"; pmtID.DbType = DbType.String; pmtID.Value = _mdlEmpresa.chvvnda; cmd.Parameters.Add(pmtID); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable empresas = new DataTable(); da.Fill(empresas); ConexaoDB.Close(); return(empresas); }
public DataTable PesquisarMDL(global::CamadaModelos.mdlEmpresa _mdlEmpresa) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\EMPRESARIO3\Dados\EP3.mdb"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string Query = "select chvbfj, cnpjcpf, rzs from bfj where "; if (_mdlEmpresa.FiltroAbrirChamado == "CNPJ") { //Query += "cnpjcpf LIKE \"%\" + @Filtro + \"%\""; procura qualquer parte Query += "cnpjcpf LIKE + @Filtro + \"%\""; } else if (_mdlEmpresa.FiltroAbrirChamado == "Nome") { Query += "rzs LIKE \"%\" + @Filtro + \"%\""; } Query += " order by rzs"; OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB); cmd.CommandType = CommandType.Text; OleDbParameter pmtFiltro = cmd.CreateParameter(); pmtFiltro.ParameterName = "@Filtro"; pmtFiltro.DbType = DbType.String; pmtFiltro.Value = _mdlEmpresa.FiltrotxtProcurar; cmd.Parameters.Add(pmtFiltro); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable empresas = new DataTable(); da.Fill(empresas); return(empresas); }
public static bool VerificarDuplicidade(string nome) { Conexao conexao = new Conexao(); conexao.abrir(); string qexiste = "select count(1) from tb_empresas where nome = @nome"; OleDbCommand cmdver = new OleDbCommand(qexiste, conexao.GetConexao()); cmdver.CommandType = CommandType.Text; OleDbParameter pmtnome = cmdver.CreateParameter(); pmtnome.ParameterName = "@nome"; pmtnome.DbType = DbType.String; pmtnome.Value = nome; cmdver.Parameters.Add(pmtnome); int verif = (int)cmdver.ExecuteScalar(); // int existeok = 0; if (verif > 0) { // found = true; return(true); } else { // found = false; return(false); } }
public DataTable PesquisarTelefones(global::CamadaModelos.mdlEmpresa _mdlEmpresa) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\EP3.tcm"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string Query = "SELECT tel1, tel2, tel3, tel4, tel5 FROM ende WHERE chvbfj=@id"; OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB); cmd.CommandType = CommandType.Text; OleDbParameter pmtID = cmd.CreateParameter(); pmtID.ParameterName = "@id"; pmtID.DbType = DbType.String; pmtID.Value = _mdlEmpresa.ID; cmd.Parameters.Add(pmtID); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable telefones = new DataTable(); da.Fill(telefones); ConexaoDB.Close(); return(telefones); }
public static bool VerificarDuplicidade(string Nome) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Usuario\source\repos\Chamados\Chamados\bin\Debug\chamadosint.tcm"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string qexiste = "select count(1) from tb_tecnicos where Nome = @Nome"; OleDbCommand cmdver = new OleDbCommand(qexiste, ConexaoDB); cmdver.CommandType = CommandType.Text; OleDbParameter pmtnome = cmdver.CreateParameter(); pmtnome.ParameterName = "@Nome"; pmtnome.DbType = DbType.String; pmtnome.Value = Nome; cmdver.Parameters.Add(pmtnome); int verif = (int)cmdver.ExecuteScalar(); if (verif > 0) { return(true); } else { return(false); } }
public bool MudarStatusSendoAtendido(global::CamadaModelos.mdlEmpresa _mdlEmpresa) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica2\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.tcm"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string Query = "update tb_chamados " + "set " + "SendoAtendido='1' " + "where id = @id"; OleDbCommand cmd = new OleDbCommand(Query, ConexaoDB); cmd.CommandType = CommandType.Text; var pmtID = cmd.CreateParameter(); pmtID.ParameterName = "@id"; pmtID.DbType = DbType.String; pmtID.Value = _mdlEmpresa.ID; cmd.Parameters.Add(pmtID); cmd.ExecuteNonQuery(); int resultado = cmd.ExecuteNonQuery(); ConexaoDB.Close(); return(resultado > 0); }
public static void ConvertNamedParametersToPositionalParameters(this OleDbCommand command) { //1. Find all occurrences parameters references in the SQL statement (such as @MyParameter). //2. Find the corresponding parameter in the command's parameters list. //3. Add the found parameter to the newParameters list and replace the parameter reference in the SQL with a question mark (?). //4. Replace the command's parameters list with the newParameters list. var newParameters = new List <OleDbParameter>(); command.CommandText = Regex.Replace(command.CommandText, "(@\\w*)", match => { var parameter = command.Parameters.OfType <OleDbParameter>().FirstOrDefault(a => a.ParameterName == match.Groups[1].Value); if (parameter != null) { var parameterIndex = newParameters.Count; var newParameter = command.CreateParameter(); newParameter.OleDbType = parameter.OleDbType; newParameter.ParameterName = "@parameter" + parameterIndex.ToString(); newParameter.Value = parameter.Value; newParameters.Add(newParameter); } return("?"); }); command.Parameters.Clear(); command.Parameters.AddRange(newParameters.ToArray()); }
protected void CancelOrder(object sender, CommandEventArgs e) { LinkButton source = (LinkButton)sender; String OrderID = source.CommandArgument; OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["OnlineStoreConnString"].ConnectionString; conn.Open(); OleDbCommand comm = conn.CreateCommand(); comm.Connection = conn; comm.CommandText = "DELETE * FROM Orders WHERE OrderID=? AND IsCart=true"; OleDbParameter param; param = comm.CreateParameter(); param.DbType = DbType.String; param.Direction = ParameterDirection.Input; param.Value = Int32.Parse(OrderID); comm.Parameters.Add(param); int totalCount = comm.ExecuteNonQuery(); conn.Close(); if (totalCount != 0) { Page.Response.Redirect(Page.Request.Url.ToString(), true); } }
private void loadReportTous1(string s) { try { FicheRetrait rpt = new FicheRetrait(); OleDbCommand cmd = new OleDbCommand(@"SELECT Sortie.date_sortie, Sortie.montant, Client.matricule, Client.nom, Client.postnom, Client.prenom, Compte.designation, Compte.typeCompte FROM (Client INNER JOIN Compte ON Client.id=Compte.id_Client) INNER JOIN Sortie ON Compte.id=Sortie.id_compte where Compte.typeCompte=@string", Factory.Instance.connectDB()); OleDbDataAdapter sa = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); IDataParameter paramString = cmd.CreateParameter(); paramString.ParameterName = "@string"; paramString.Value = Convert.ToString(s); cmd.Parameters.Add(paramString); cmd.ExecuteNonQuery(); sa.Fill(ds, "doc"); rpt.SetDataSource(ds.Tables["doc"]); crvEntree.ReportSource = rpt; crvEntree.Refresh(); sa.Dispose(); ds.Dispose(); cmd.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Erreur de l'afichage du rapport", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
/// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSql">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSql, byte[] data) { using (OleDbConnection conn = ConnectionMgr.GetOleDbConnection()) { int ret = 0; conn.Open(); OleDbCommand cmd = conn.CreateCommand(); cmd.CommandText = strSql; OleDbParameter para = cmd.CreateParameter(); para.Direction = ParameterDirection.Input; para.DbType = DbType.Binary; para.Value = data; cmd.Parameters.Add(para); try { ret = cmd.ExecuteNonQuery(); } catch (DbException ex) { throw ex; } finally { conn.Close(); } return(ret); } }
/// <summary> /// make Parameter and add it to the command object given /// </summary> /// <param name="command_" >OleDbCommand</param> /// <exception cref="Exception">On error throws an exception</exception> public void to_command(ref OleDbCommand command_) { try { OleDbParameter param = command_.CreateParameter(); param.ParameterName = this.name_; param.OleDbType = this.type_; param.Direction = this.direction_; if (this.size_ != 0) { param.Size = this.size_; } if (this.direction_ == ParameterDirection.InputOutput || this.direction_ == ParameterDirection.Input) // if it is an input parameter... { param.Value = this.value_; } command_.Parameters.Add(param); } catch (Exception) { throw new Error("Cannot add parameter to command object"); } }
public List <MyReg> GetRegs(UInt64 progID) { List <MyReg> regLst = new List <MyReg>(); var qry = "SELECT * FROM program_user WHERE programID = ?"; var cmd = new OleDbCommand(qry, m_cnn); cmd.Parameters.Add(cmd.CreateParameter()); cmd.Parameters[0].Value = progID; var reader = cmd.ExecuteReader(); while (reader.Read()) { regLst.Add(new MyReg() { ID = Convert.ToUInt64(reader["ID"]), zNote = Convert.ToString(reader["zNote"]), nStatus = int.Parse(reader["nStatus"].ToString()), zStatus = Convert.ToString(reader["zStatus"]), userID = Convert.ToUInt64(reader["userID"]), programID = Convert.ToUInt64(reader["programID"]) }); } reader.Close(); return(regLst); }
public static bool VerificarDuplicidade(string CNPJ) { string ConexaoAccess = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\REP_SERVER\publica\Dropbox\Thiago\Meus Documentos\Visual Studio 2017\Chamados\Chamados\bin\Debug\chamadosint.mdb"; OleDbConnection ConexaoDB = new OleDbConnection(ConexaoAccess); ConexaoDB.Open(); string qexiste = "select count(1) from tb_empresas where CNPJ = @CNPJ"; OleDbCommand cmdver = new OleDbCommand(qexiste, ConexaoDB); cmdver.CommandType = CommandType.Text; OleDbParameter pmtcnpj = cmdver.CreateParameter(); pmtcnpj.ParameterName = "@CNPJ"; pmtcnpj.DbType = DbType.String; pmtcnpj.Value = CNPJ; cmdver.Parameters.Add(pmtcnpj); int verif = (int)cmdver.ExecuteScalar(); if (verif > 0) { return(true); } else { return(false); } }