public int inserir(Voos voo)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO voo(codigo, data, origem, destino) VALUES(?, ?, ?, ?)";
OdbcParameter codigo = new OdbcParameter("?", voo.Codigo);
OdbcParameter data = new OdbcParameter("?", voo.Data);
OdbcParameter origem = new OdbcParameter("?", voo.Origem);
OdbcParameter destino = new OdbcParameter("?", voo.Destino);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(codigo);
comando.Parameters.Add(data);
comando.Parameters.Add(origem);
comando.Parameters.Add(destino);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
public int inserir(Aviao aviao)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO aviao(numeroSerie, modelo) VALUES(?, ?)";
OdbcParameter numeroSerie = new OdbcParameter("?", aviao.NumeroSerie);
OdbcParameter modelo = new OdbcParameter("?", aviao.Modelo);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(numeroSerie);
comando.Parameters.Add(modelo);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
private bool isRegisteredUser()
{
try
{
Contract contract = new Contract();
String cxnString = "Driver={SQL Server};Server=HC-sql7;Database=REVINT;Trusted_Connection=yes;";
using (OdbcConnection dbConnection = new OdbcConnection(cxnString))
{
//open OdbcConnection object
dbConnection.Open();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "{CALL [REVINT]." + contract.getSchema() + ".[OCP_selectNumberOfUsers]( ?, ? )}";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = dbConnection;
cmd.Parameters.Add("@hawkId", OdbcType.NVarChar, 400).Value = Environment.UserName;
cmd.Parameters.Add("@numUsers", OdbcType.Int);
cmd.Parameters["@numUsers"].Direction = System.Data.ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
dbConnection.Close();
return cmd.Parameters["@numUsers"].Value.ToString().Equals("1");
}
}
catch(Exception)
{
return false;
}
}
/// <summary>
/// ��Ʈw�s�W��k
/// </summary>
/// <param name="insert"></param>
public void insert(ICommand insert)
{
string myCmd = insert.getCommand();
try
{
cmd = new OdbcCommand(myCmd, GetConn());
//cmd = new IBM.Data.DB2.DB2Command(myCmd, GetConn());
cmd.ExecuteNonQuery();
}
catch (OdbcException dobcEx)
{
if (dobcEx.ErrorCode == -2146232009)
{
return;
}
try
{
lock (typeof(OdbcConnection))
{
//cmd = new IBM.Data.DB2.DB2Command(myCmd, GetConn());
cmd = new OdbcCommand(myCmd, GetConn());
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// insert data into corresponding database tables
/// </summary>
public void InsertDataIntoDBtables(DbConnect dbConnect, DataTable[] dataTables)
{
#if DEBUG
logWriter = new StreamWriter("dbInsertErrorLog.txt", true);
#endif
try
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
foreach (DataTable dataTable in dataTables)
{
InsertionSqlString insertSqlStr = new InsertionSqlString(dataTable.TableName);
foreach (DataRow dRow in dataTable.Rows)
{
try
{
for (int colI = 0; colI < dataTable.Columns.Count; colI++)
{
string colName = dataTable.Columns[colI].ColumnName;
insertSqlStr.AddKeyValuePair(colName, dRow[colName]);
}
insertCommand.CommandText = insertSqlStr.ToString();
insertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
// throw new Exception (string.Format ("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
#if DEBUG
logWriter.WriteLine(string.Format("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
logWriter.WriteLine(insertCommand.CommandText);
#endif
}
finally
{
insertSqlStr.ClearSqlInsertString();
}
}
// commit the insertion
insertCommand.CommandText = "Commit";
insertCommand.ExecuteNonQuery();
}
insertCommand.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
#if DEBUG
logWriter.Close();
#endif
}
}
protected void btnLoginOK_Click(object sender, EventArgs e)
{
string connection = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection dbCon = new OdbcConnection(connection);
string sql = "SELECT seq FROM profile WHERE (MEMBER_NAME = @userid) AND (MEMBER_PWD = @pwd)";
OdbcCommand dbCmd = new OdbcCommand(sql, dbCon);
dbCmd.Parameters.Add("@userid", OdbcType.VarChar, 50, "MEMBER_NAME");
dbCmd.Parameters.Add("@pwd", OdbcType.VarChar, 30, "MEMBER_PWD");
dbCon.Open();
dbCmd.Parameters["@userid"].Value = txtName.Text;
dbCmd.Parameters["@pwd"].Value = txtPwd.Text;
if (dbCmd.ExecuteScalar() == null)
{
ClientScriptManager cs = Page.ClientScript;
cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./DefaultLogin.aspx');", true);
cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('Incorrect ID/PW');", true);
dbCmd.ExecuteNonQuery();
dbCon.Close();
}
else
{
dbCmd.CommandText = "select * from profile where (MEMBER_NAME = @userid)";
OdbcDataReader reader = dbCmd.ExecuteReader();
reader.Read();
//add session
Session.Add("USER_ID", txtName.Text);
Session.Add("USER_NAME", reader["MEMBER_NAME"].ToString());
Session.Add("USER_PWD", reader["MEMBER_PWD"].ToString());
reader.Close();
dbCmd.ExecuteNonQuery();
dbCon.Close();
Response.Redirect("./Board_list.aspx");
//if (Session["USER_ID"] != null)
//{
// namelbl2.Text = Session["USER_ID"].ToString() + "Welcom.";
// //hide login
// LoginForm.Visible = false;
// //dis login
// loginchk.Visible = true;
//}
}
}
/// <summary>
/// Insert one datarow into database table
/// </summary>
/// <param name="thisEntrySameSeqEntriesTable"></param>
public void InsertDataIntoDb(DbConnect dbConnect, DataRow dRow)
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
InsertionSqlString insertSqlStr = new InsertionSqlString(dRow.Table.TableName);
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
try
{
for (int colI = 0; colI < dRow.Table.Columns.Count; colI++)
{
string colName = dRow.Table.Columns[colI].ColumnName;
insertSqlStr.AddKeyValuePair(colName, dRow[colName]);
}
insertCommand.CommandText = insertSqlStr.ToString();
insertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(string.Format("{0} Insertion Errors: {1}", dRow.Table.TableName, ex.Message));
}
finally
{
insertSqlStr.ClearSqlInsertString();
insertCommand.Dispose();
}
}
protected void Button3_Click(object sender, EventArgs e)
{
string fl = DropDownList2.SelectedItem.Text;
string f = fl.Remove(fl.Length - 4);
Response.Write(f);
StringBuilder sb = new StringBuilder();
string sf = null;
for (int i = 0; i < GridView1.HeaderRow.Cells.Count; i++)
{
sb.Append(GridView1.HeaderRow.Cells[i].Text).Append(" ").Append("varchar(50),");
}
sf = sb.ToString();
string tex = sf.Remove(sf.Length - 1);
try
{
conn.Open();
OdbcCommand cmd = new OdbcCommand("create table " + f + "(" + tex + ")", conn);
cmd.ExecuteNonQuery();
Label1.Visible = true;
Label1.Text = "TABLE CREATED SUCESSFULLY";
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
conn.Close();
}
}
public static void deleteNoticia(int id)
{
OdbcConnection conexion = null;
OdbcCommand cmd = null;
try
{
conexion = ConexionBD.ObtenerConexion();
//Borro todas las imagenes de la noticia
String borrarImagenes = " delete i from imagen i where i.id in "
+ " ( select ixn.idImagen from imagen_x_noticia ixn "
+ " where idNoticia =" + id.ToString() + ") ";
cmd = new OdbcCommand(borrarImagenes, conexion);
cmd.ExecuteNonQuery();
//borro la noticia
String deleteNoticia = "DELETE FROM noticia WHERE id = " + id.ToString();
cmd = new OdbcCommand(deleteNoticia, conexion);
cmd.ExecuteNonQuery();
conexion.Close();
}
catch (Exception e)
{
throw new SportingException("Ocurrio un error al intentar borrar una noticia. " + e.Message);
}
finally
{
cmd.Connection.Close();
}
}
//delete record
public bool delete_record(string id)
{
bool stat = false;
try
{
//get the connection
OdbcConnection con = getconnection();
//open the created connection
con.Open();
//create new sql command
OdbcCommand cmd = new OdbcCommand("delete from school_events where EVENT_ID='" + id + "' ", con);
//execute the quary
cmd.ExecuteNonQuery();
//if quary excecution is success the change stat as true
stat = true;
//close the connection
con.Close();
}
catch (Exception ss)
{
string dd = ss.ToString();
}
return stat;
}
public void AddMediaTrack(MediaTrackerModel track)
{
var existsSql = string.Format("select * from mediatracker2 where mediaid={0} and contentid={1} and propertyid={2}",
track.MediaId, track.ContentId, track.PropertyId);
using (var conn = new OdbcConnection(DbConnString))
{
try
{
conn.Open();
var odbcCommand = new OdbcCommand(existsSql, conn);
var rdr = odbcCommand.ExecuteReader();
var exists = rdr.HasRows;
rdr.Close();
if (exists) return;
var insertSql =
string.Format(
"insert into mediatracker (mediaid,contentid,propertyid) values ({0},{1},{2})",
track.MediaId, track.ContentId, track.PropertyId);
var comm = new OdbcCommand(insertSql, conn);
comm.ExecuteNonQuery();
}
catch (OdbcException e)
{
LogHelper.Error<OdbcException>(e.Message, e);
}
}
}
//delete the record
public bool delete_record(string id, string type, string activity, decimal year)
{
bool stat = false;
try
{
//set up new connection
OdbcConnection con = getconnection();
//open the setup coneection
con.Open();
//set command
OdbcCommand cmd = new OdbcCommand("delete from extra_activity where ADMISSION_NO='" + id + "' and ACTIVITY_CODE='" + type + "' and ACTIVITYSUB_CODE='" + activity + "' and ACTIVITY_YEAR='" + year + "'", con);
//execute the command
cmd.ExecuteNonQuery();
//set status as true
stat = true;
//close the connection
con.Close();
}
catch (Exception ss)
{
string s = ss.Message;
stat = false;
}
return stat;
}
static void Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null)
return;
OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
string dbName = Environment.GetEnvironmentVariable ("MONO_TESTS_SQL_DB");
Assert.AreEqual (dbName, conn.Database, "#1");
OdbcCommand cmd = new OdbcCommand ("CREATE DATABASE aфbиc", conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
try {
Assert.AreEqual (dbName, conn.Database, "#2");
conn.ChangeDatabase ("aфbиc");
Assert.AreEqual ("aфbиc", conn.Database, "#3");
conn.ChangeDatabase (dbName);
Assert.AreEqual (dbName, conn.Database, "#4");
} finally {
conn.Dispose ();
conn = new OdbcConnection (CreateOdbcConnectionString ());
conn.Open ();
cmd = new OdbcCommand ("DROP DATABASE aфbиc", conn);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(strutextBox.Text))
{
MessageBox.Show("Insert New Structure.");
return;
}
String pidd = null;
using (cmd = new OdbcCommand("Select Structure from `pap`.`structure` where Structure='" + strutextBox.Text + "'", cn))
{
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
pidd = reader.GetString(0);
}
reader.Close();
}
if (pidd != null)
{
MessageBox.Show("The New Semi Permanent Structure Already Exists");
return;
}
cn.Open();
using (cmd = new OdbcCommand("INSERT INTO `pap`.`structure` (`Strid` ,`Structure` ,`Rate`)VALUES (NULL , '" + strutextBox.Text + "', '0');", cn))
cmd.ExecuteNonQuery();
strutextBox.Text = "";
cn.Close();
}
}
public Boolean executeQuery(String query)
{
using (OdbcConnection connection =
new OdbcConnection(connectionString))
{
// The insertSQL string contains a SQL statement that
// inserts a new row in the source table.
OdbcCommand command = new OdbcCommand(query, connection);
// Open the connection and execute the insert command.
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
// The connection is automatically closed when the
// code exits the using block.
return true;
}
}
private static void ExecuteSQL(string sql, OdbcConnection conn)
{
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
}
public int inserir(Aeroportos aeroporto)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO aeroporto(nome, cidade, codigo) VALUES(?, ?, ?)";
OdbcParameter nome = new OdbcParameter("?", aeroporto.Nome);
OdbcParameter cidade = new OdbcParameter("?", aeroporto.Cidade);
OdbcParameter codigo = new OdbcParameter("?", aeroporto.Codigo);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(nome);
comando.Parameters.Add(cidade);
comando.Parameters.Add(codigo);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
//public static string GetAssessmentResult(int aoid)
//{
// Assessment ass = new Assessment();
// string tmp="";
// return ass.GetAssessmentResult(aoid,out tmp);
//}
protected void ButtonViewResult_Click(object sender, EventArgs e)
{
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
using (OdbcCommand command = new OdbcCommand())
{
command.Connection = connection;
command.CommandText = "{CALL ScoreDetailTable_InsertNow(?)}";
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 999;
command.Parameters.AddWithValue("@AOID", aid);
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
int n = command.ExecuteNonQuery();
}
catch (Exception ex)
{ }
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
}
// execute SQL statement
public int ExecSQL(cDB obj, string sql, bool is_quary)
{
dbCmd = new OdbcCommand(sql, dbCon);
dbCon.Open();
if(is_quary)
{
// select
using(OdbcDataReader dbr = dbCmd.ExecuteReader())
{
int count;
object[] row;
while (dbr.Read())
{
// read each row at a time
count = dbr.FieldCount;
row = new object[count];
dbr.GetValues(row);
obj.ReadData(row);
}
}
}
else
{
// delete, create, update, insert
dbCmd.ExecuteNonQuery();
}
return 0;
}
public void AddArbeitsAuftrag(ArbeitsAuftrag aa)
{
if (aa.Uebergabedaten.ID <= 0)
aa.Uebergabedaten.ID = this.getDatenID();
OdbcCommand c;
c = new OdbcCommand("insert into daten (did, typ_tname, data) values (?, ?, ?)", con);
c.Parameters.Add("did", OdbcType.Int);
c.Parameters.Add("typ_tname", OdbcType.VarChar);
c.Parameters.Add("data", OdbcType.VarChar);
c.Parameters["did"].Value = aa.Uebergabedaten.ID;
c.Parameters["typ_tname"].Value = aa.Uebergabedaten.Typ.Name;
c.Parameters["data"].Value = aa.Uebergabedaten.Daten;
c.ExecuteNonQuery();
c = new OdbcCommand("insert into AA (programm_pname, daten_did) values (?, ?)", con);
c.Parameters.Add("programm_pname", OdbcType.VarChar);
c.Parameters.Add("daten_did", OdbcType.Int);
c.Parameters["programm_pname"].Value = aa.program.Name;
c.Parameters["daten_did"].Value = aa.Uebergabedaten.ID;
c.ExecuteNonQuery();
}
/// <summary>
/// ����
/// </summary>
/// <param name="sql"></param>
public static int Execute(string sql, string connstr, OdbcParameter[] parameters)
{
int n = -1;
OdbcConnection conn = null;
try
{
conn = GetConnection(connstr);
OdbcCommand cmd = new OdbcCommand();//sql, conn);
cmd.CommandText = sql;
cmd.Connection = conn;
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.AddWithValue(parameters[i].ParameterName, parameters[i].Value);
}
conn.Open();
n = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
//MessageBox.Show( sql, "ϵͳ�����������Ա��ϵ", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw new DatabaseException(sql + "\n" + e.Message);
}
finally
{
try { conn.Close(); }
catch { }
}
conn = null;
return n;
}
/// <summary>
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
/// </summary>
/// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param>
public int ExecuteNonQuery(string sql)
{
using (odbcConnection = this.GetOdbcConnection())
{
if (odbcConnection == null)
{
return(-1);
}
try
{
if (odbcConnection.State == System.Data.ConnectionState.Closed)
{
odbcConnection.Open();
}
odbcCommand = new OdbcCommand(sql, odbcConnection);
return(odbcCommand.ExecuteNonQuery());
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(-1);
}
}
}
public bool AddTermCode(TermCode termCode)
{
bool result = false;
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
using (OdbcCommand command = new OdbcCommand())
{
command.Connection = connection;
command.CommandText = "{CALL TermCode_insert(?,?)}";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@AssessmentOID", termCode.AssessmentOID);
command.Parameters.AddWithValue("@TermCodeName", termCode.TermCodeName);
connection.Open();
int n = command.ExecuteNonQuery();
if (n > 0)
result = true;
else
result = false;
}
}
return result;
}
/// <summary>
/// Executa algum comando de inserção, exclusão ou edição
/// </summary>
/// <param name="sql">o sql a ser executado</param>
public void ExecutaComando(string sql)
{
//Usa a classe de conexão apenas nesse bloco
using(OdbcConnection conn = new OdbcConnection(this.ConnectionString))
{
try
{
//Abre conexão com o servidor de banco
conn.Open();
//instancia um objeto de comando, passando como parametro o sql e a conexão para o construtor
OdbcCommand comm = new OdbcCommand(sql, conn);
//Executa o comando
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
//Atira uma nova excessão, caso dê algum erro
throw new Exception(ex.Message);
}
finally
{
//Sempre fechará conexão, independente se der erro ou não
conn.Close();
}
}
}
public static void Load_Sport_GR_TB_TXT_TO_DB(string table_name,string field_names)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
using (OdbcConnection conn = new OdbcConnection(Basic_HTB_Info.Conn_Str))
{
conn.Open();
MessageBox.Show(String.Format("TRUNCATE TABLE {0};",table_name));
using (OdbcCommand cmd = new OdbcCommand(String.Format("TRUNCATE TABLE {0};",table_name), conn))
{
cmd.ExecuteNonQuery();
}
StreamReader sr = new StreamReader(ofd.FileName);
string line = null;
while ((line = sr.ReadLine()) != null)
{
string[] str_a = line.Split(',');
if (str_a.Length > 5)
{
Lib.inc_cmd(field_names, table_name, str_a, conn);
}
}
conn.Close();
}
}
}
//this function deletes the selected record
public bool delete_record(string student_id)
{
bool stat = false;
try
{
//get the connection
OdbcConnection con = connect.getconnection();
//open the created connection
con.Open();
//create new sql command
OdbcCommand cmd = new OdbcCommand("delete from student_warning where ADMISSION_NO='" + student_id + "'", con);
//execute the quary
cmd.ExecuteNonQuery();
//if quary excecution is success the change stat as true
stat = true;
}
catch (Exception ss)
{
string dd = ss.ToString();
Response.Write("<script>alert('Error while processing, Please try again')</script>");
}
finally
{
connect.close();
}
return stat;
}
internal void ejec_afectando_filas(string odbc, IDbConnection con)
{
string TipoBase = archivos.nombremotorbase();
if (TipoBase == "Mysql")
{
MySqlCommand comando = new MySqlCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as MySqlConnection;
comando.ExecuteNonQuery();
}
else
{
if (TipoBase == "sql")
{
SqlCommand comando = new SqlCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as SqlConnection;
comando.ExecuteNonQuery();
}
else
{
System.Data.Odbc.OdbcCommand comando = new System.Data.Odbc.OdbcCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as System.Data.Odbc.OdbcConnection;
comando.ExecuteNonQuery();
}
}
}
public void alteraProduto(Produto produto)
{
using (OdbcConnection conexao = ConexaoPadrao.createConnection())
{
string sql = "update PRODUTO set DESCRICAO = ?, SETOR = ?, ID_FORNECEDOR = ?, FABRICANTE = ?, VENCIMENTO = ?, QUANTIDADE = ?, UNIDADE = ?, VALOR_CUSTO = ?, VALOR_VENDA = ?, LUCRO = ?, TRIBUTACAO = ?, MINIMO_EM_ESTOQUE = ?, MAXIMO_EM_ESTOQUE = ?, CODIGO_BARRAS = ? where ID_PRODUTO = ?";
OdbcCommand command = new OdbcCommand(sql, conexao);
command.Parameters.AddWithValue("@DESCRICAO", produto.descricao);
command.Parameters.AddWithValue("@SETOR", produto.setor);
command.Parameters.AddWithValue("@ID_FORNECEDOR", produto.idFornecedor);
command.Parameters.AddWithValue("@FABRICANTE", produto.fabricante);
command.Parameters.AddWithValue("@VENCIMENTO", produto.vencimento);
command.Parameters.AddWithValue("@QUANTIDADE", produto.quantidade);
command.Parameters.AddWithValue("@UNIDADE", produto.unidade);
command.Parameters.AddWithValue("@VALOR_CUSTO", produto.valorCusto);
command.Parameters.AddWithValue("@VALOR_VENDA", produto.valorVenda);
command.Parameters.AddWithValue("@LUCRO", produto.lucro);
command.Parameters.AddWithValue("@TRIBUTACAO", produto.tributacao);
command.Parameters.AddWithValue("@MINIMO_EM_ESTOQUE", produto.minimoEmEstoque);
command.Parameters.AddWithValue("@MAXIMO_EM_ESTOQUE", produto.maximoEmEstoque);
command.Parameters.AddWithValue("@CODIGO_BARRAS", produto.codigoBarras);
command.Parameters.AddWithValue("@ID_PRODUTO", produto.id);
conexao.Open();
command.ExecuteNonQuery();
}
}
public void alteraAnamnese(Anamnese anamnese)
{
using (OdbcConnection conexao = ConexaoPadrao.createConnection())
{
string sql = "update ANAMNESE set ID_ANIMAL = ?, ID_VETERINARIO = ?,VACINADO = ?, DOENCAS = ?, TRATAMENTOS = ?, SISTEMA_DIGESTORIO = ?, SISTEMA_CARDIO_RESPIRATORIO = ?, SISTEMA_GENITO_URINARIO = ?, SISTEMA_NERVOSO_LOCOMOTOR = ?, PELES_ANEXOS = ?, MANEJO = ?, DATA = ?, where ID_ANAMNESE = ?";
OdbcCommand command = new OdbcCommand(sql, conexao);
command.Parameters.AddWithValue("@ID_ANIMAL", anamnese.idAnimal);
command.Parameters.AddWithValue("@ID_AVETERINARIO", anamnese.idVeterinario);
command.Parameters.AddWithValue("@VACINADO", anamnese.vacinado);
command.Parameters.AddWithValue("@DOENCAS", anamnese.doencas);
command.Parameters.AddWithValue("@TRATAMENTOS", anamnese.tratamentos);
command.Parameters.AddWithValue("@SISTEMA_DIGESTORIO", anamnese.sistemaDigestorio);
command.Parameters.AddWithValue("@SISTEMA_CARDIO_RESPIRATORIO", anamnese.sistemaCardioRespiratorio);
command.Parameters.AddWithValue("@SISTEMA_GENITO_URINARIO", anamnese.sistemaGenitoUrinario);
command.Parameters.AddWithValue("@SISTEMA_NERVOSO_LOCOMOTOR", anamnese.sistemaNervosoLocomotor);
command.Parameters.AddWithValue("@PELES_ANEXOS", anamnese.pelesAnexos);
command.Parameters.AddWithValue("@MANEJO", anamnese.manejo);
command.Parameters.AddWithValue("@DATA", anamnese.data);
command.Parameters.AddWithValue("@ID_ANAMNESE", anamnese.id);
conexao.Open();
command.ExecuteNonQuery();
}
}
public int deleteQuestionByExamId(int examinationId)
{
OdbcCommand command = new OdbcCommand("delete from question where examinationId=?", connection);
command.Parameters.Add(new OdbcParameter("examinationId", OdbcType.Int)).Value = examinationId;
command.Prepare();
return command.ExecuteNonQuery();
}
public int inserir(Reservas reserva)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO reserva(voo, assento, cpf_cliente) VALUES(?, ?, ?)";
OdbcParameter voo = new OdbcParameter("?", reserva.Voo);
OdbcParameter assento = new OdbcParameter("?", reserva.Assento);
OdbcParameter cpf = new OdbcParameter("?", reserva.Cpf_cliente);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(voo);
comando.Parameters.Add(assento);
comando.Parameters.Add(cpf);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
public int deleteQuestion(int id)
{
OdbcCommand command = new OdbcCommand("delete from question where id=?", connection);
command.Parameters.Add(new OdbcParameter("id", OdbcType.Int)).Value = id;
command.Prepare();
return command.ExecuteNonQuery();
}
//delete the record
public bool delete_record(string id, string date, string warn_code, string level)
{
bool stat = false;
try
{
//set up new connection
OdbcConnection con = getconnection();
//open the setup coneection
con.Open();
//set command
OdbcCommand cmd = new OdbcCommand("delete from student_warning where ADMISSION_NO='" + id + "' and WARN_DATE='" + date + "' and WARNING_CODE='" + warn_code + "' and LEVEL_CODE='" + level + "'", con);
//execute the command
cmd.ExecuteNonQuery();
//set status as true
stat = true;
//close the connection
con.Close();
}
catch (Exception ss)
{
string s = ss.Message;
stat = false;
}
return stat;
}
public ContentResult Create(string json)
{
OdbcConnection hiveConnection = new OdbcConnection("DSN=Hadoop Server;UID=hadoop;PWD=hadoop");
hiveConnection.Open();
Stream req = Request.InputStream;
req.Seek(0, SeekOrigin.Begin);
string request = new StreamReader(req).ReadToEnd();
ContentResult response;
string query;
try
{
query = "INSERT INTO TABLE error_log (json_error_log) VALUES('" + request + "')";
OdbcCommand command = new OdbcCommand(query, hiveConnection);
command.ExecuteNonQuery();
command.CommandText = query;
response = new ContentResult { Content = "{status: 1}", ContentType = "application/json" };
hiveConnection.Close();
return response;
}
catch(WebException error)
{
response = new ContentResult { Content = "{status: 0, message:" + error.Message.ToString()+ "}" };
System.Diagnostics.Debug.WriteLine(error.ToString());
hiveConnection.Close();
return response;
}
}
public static void ExecuteNonQueryOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
QCommand.ExecuteNonQuery();
}
finally
{
if (QCommand != null)
{
QCommand.Dispose();
}
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed)
{
QConnection.Close();
}
if (QConnection != null)
{
QConnection.Dispose();
}
QConnection = null;
}
}
public void Write_Value(string Tag_Name, double Value, string Quality = "Good")
{
Cmdw.CommandText =
@"UPDATE ip_analogdef
SET ip_input_value = " + Value.ToString("G", CultureInfo.InvariantCulture) + @", ip_input_quality = '" + Quality + @"'
WHERE name = '" + Tag_Name + @"'";
try
{
if (!IP21_Read_Only)
{
Cmdw.ExecuteNonQuery();
}
}
catch
{
Console.WriteLine("Write_Value failed");
}
}
/// <summary>
///
/// </summary>
/// <param name="dbConnect"></param>
/// <param name="insertString"></param>
public void InsertDataIntoDb(DbConnect dbConnect, string insertString)
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
insertCommand.CommandText = insertString;
insertCommand.ExecuteNonQuery();
insertCommand.Dispose();
}
static public bool KillODBCConnection(System.Data.Odbc.OdbcConnection myConn)
{
if (myConn != null)
{
if (myConn.State == System.Data.ConnectionState.Closed)
{
return(false);
}
try
{
string strSQL = "kill connection_id()";
System.Data.Odbc.OdbcCommand myCmd = new System.Data.Odbc.OdbcCommand(strSQL, myConn);
myCmd.CommandText = strSQL;
myCmd.ExecuteNonQuery();
}catch (Exception ex)
{
}
}
return(true);
}
internal void ejec_afectando_filasSp(string nombre, IDbConnection con, params System.Object[] parametros)
{
string TipoBase = archivos.nombremotorbase();
if (TipoBase == "Mysql")
{
MySqlCommand comando = new MySqlCommand();
comando.CommandType = CommandType.StoredProcedure;
comando.CommandText = nombre;
comando.Connection = con as MySqlConnection;
MySqlCommandBuilder.DeriveParameters(comando);
int limite = comando.Parameters.Count;
try
{
for (int i = 0; i < limite; i++)
{
comando.Parameters[i].Value = parametros[i];
}
}
catch (Exception ex)
{
throw ex;
}
comando.ExecuteNonQuery();
}
else
{
if (TipoBase == "sql")
{
SqlCommand comando = new SqlCommand();
comando.CommandType = CommandType.StoredProcedure;
comando.CommandText = nombre;
comando.Connection = con as SqlConnection;
SqlCommandBuilder.DeriveParameters(comando);
int limite = comando.Parameters.Count;
try
{
for (int i = 1; i < limite; i++)
{
comando.Parameters[i].Value = parametros[i - 1];
}
}
catch (Exception ex)
{
throw ex;
}
comando.ExecuteNonQuery();
}
else
{
System.Data.Odbc.OdbcCommand comando = new System.Data.Odbc.OdbcCommand();
comando.CommandType = CommandType.StoredProcedure;
comando.CommandText = nombre;
comando.Connection = con as System.Data.Odbc.OdbcConnection;
System.Data.Odbc.OdbcCommandBuilder.DeriveParameters(comando);
int limite = comando.Parameters.Count;
try
{
for (int i = 1; i < limite; i++)
{
comando.Parameters[i].Value = parametros[i - 1];
}
}
catch (Exception ex)
{
throw ex;
}
comando.ExecuteNonQuery();
}
}
}
/// <summary>
/// insert data into corresponding database tables
/// </summary>
public void BatchInsertDataIntoDBtables(DbConnect dbConnect, DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0)
{
return;
}
#if DEBUG
logWriter = new StreamWriter("dbInsertErrorLog.txt", true);
#endif
string executeBlock = "";
int rowCount = 1;
try
{
if (!dbConnect.IsConnected())
{
dbConnect.ConnectToDatabase();
}
System.Data.Odbc.OdbcCommand insertCommand = dbConnect.CreateCommand();
InsertionSqlString insertSqlStr = new InsertionSqlString(dataTable.TableName);
executeBlock += "EXECUTE BLOCK AS BEGIN ";
foreach (DataRow dRow in dataTable.Rows)
{
try
{
for (int colI = 0; colI < dataTable.Columns.Count; colI++)
{
string colName = dataTable.Columns[colI].ColumnName;
insertSqlStr.AddKeyValuePair(colName, dRow[colName]);
}
if (rowCount > numOfRowsAllowed ||
executeBlock.Length + insertSqlStr.ToString().Length > queryLength)
{
executeBlock += "END";
insertCommand.CommandText = executeBlock;
insertCommand.ExecuteNonQuery();
// commit the insertion
// insertCommand.CommandText = "Commit";
// insertCommand.ExecuteNonQuery();
executeBlock = "EXECUTE BLOCK AS BEGIN ";
rowCount = 1;
}
executeBlock += (insertSqlStr.ToString() + " ");
rowCount++;
}
catch (Exception ex)
{
// throw new Exception (string.Format ("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
#if DEBUG
logWriter.WriteLine(string.Format("{0} Insertion Errors: {1}", dataTable.TableName, ex.Message));
logWriter.WriteLine(insertCommand.CommandText);
#endif
executeBlock = "EXECUTE BLOCK AS BEGIN ";
rowCount = 1;
}
finally
{
insertSqlStr.ClearSqlInsertString();
// executeBlock += "END";
}
}
executeBlock += "END";
insertCommand.CommandText = executeBlock;
insertCommand.ExecuteNonQuery();
// commit the insertion
insertCommand.CommandText = "Commit";
insertCommand.ExecuteNonQuery();
insertCommand.Dispose();
}
catch (Exception ex)
{
Exception thisException = new Exception(executeBlock + " eror: " + ex.Message);
throw thisException;
}
finally
{
#if DEBUG
logWriter.Close();
#endif
}
}
public string ExecuteQuery(string sQuery, string sCompanyCode, OdbcParameter[] param)
{
string sFuncName = "ExecuteQuery()";
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
string[] sArray = sConstr.Split(';');
string sSplitCompany = sConstr.Split(';').Last();
string sSplit1 = sSplitCompany.Split('=').First();
string sCompanyGenerate = sSplit1 + "=" + sCompanyCode;
sConstr = sArray[0] + ";" + sArray[1] + ";" + sArray[2] + ";" + sArray[3] + ";" + sCompanyGenerate;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Connection String : " + sConstr, sFuncName);
}
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
oCmd.ExecuteNonQuery();
oCon.Close();
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with SUCCESS", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return("SUCCESS");
}
