// 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);
}
}