public void Connect()
{
myConnection = new OdbcConnection(dbInfo);
myConnection.Open();
myQuery = myConnection.CreateCommand();
Console.Write("DB connect");
}
public OdbcDataReader ExecuteQuery(string queryStr)
{
try
{
OdbcCommand command = new OdbcCommand(queryStr, dbConn);
command.CommandType = System.Data.CommandType.Text;
return command.ExecuteReader();
}
catch (Exception ex)
{
exception = ex;
return null;
}
}
private void addComboBox(string ConnectionString)
{
string querystring = "select * from model order by name asc";
OdbcCommand command = new OdbcCommand(querystring);
cbx_SelModel.Items.Clear();
using (OdbcConnection connection = new OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
OdbcDataReader dr = command.ExecuteReader();
while (dr.Read())
{
cbx_SelModel.Items.Add(dr["name"]);
}
if (cbx_SelModel.Items.Count > 0)
{
cbx_SelModel.SelectedIndex = 0;
}
}
//MessageBox.Show(selectModelCB.Items[3].ToString());
}
private bool Check_Table(string table)
{
// 테이블 체크
string queryString = "SHOW TABLES LIKE '" + table + "'";
OdbcCommand command = new OdbcCommand(queryString);
try
{
using (OdbcConnection connection = new OdbcConnection("dsn=" + mySetting.Info_DBConnection))
{
command.Connection = connection;
connection.Open();
OdbcDataReader dr = command.ExecuteReader();
dr.Read();
// 테이블 생성시 무조건 Column을 하나 이상 포함해야 하기 때문에 0번이 없으면 해당 테이블이 존재하지 않음
if (dr[0] != DBNull.Value)
{
// Table이 존재함
}
dr.Close();
}
}
catch (InvalidOperationException ex)
{
// Table이 없음
return(false);
}
catch (Exception e)
{
return(false);
}
return(true);
}
public static void executequery(CommandType type, string commandtext)
{
try
{
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = commandtext;
cmd.Connection = conn;
if (type == CommandType.StoredProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch
{
conn.Close();
}
}
private void actualizar()
{
/*
* programador: Julio Ernesto Tánchez Vides
* descripcion: obtiene las consultas creadas por usuario
*/
try
{
OdbcConnection conexion = DB.getConnection(); // obtiene conexion con la DB
string sql = string.Format("SELECT id_query, usu_codigo, nombre_query, select_query FROM tbl_query WHERE usu_codigo = " + lbl_usuario.Text + " AND status = 0;");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
dgv_query.Rows.Clear();
while (reader.Read())
{
dgv_query.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3));
}
conexion.Close(); // cierre de conexion con la DB
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// 建立数据库命令执行对象
/// </summary>
/// <param name="strCmd">要执行的SQL语句</param>
/// <param name="conn">数据库连接对象</param>
/// <returns>数据库命令对象</returns>
public static IDbCommand CreateCommand(string strCmd, IDbConnection conn)
{
IDbCommand cmd = null;
switch (databaseType)
{
case "SQLServer":
cmd = new SqlCommand(strCmd, (SqlConnection)conn);
break;
case "Oledb":
cmd = new OleDbCommand(strCmd, (OleDbConnection)conn);
break;
case "Oracle":
//cmd = new OracleCommand(strCmd, (OracleConnection)conn);
break;
case "Odbc":
cmd = new OdbcCommand(strCmd, (OdbcConnection)conn);
break;
}
return(cmd);
}
public DataTable consulta()
{
OdbcCommand comando;
DataTable list = new DataTable();
String comand = "SELECT * FROM piloto";
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
OdbcDataAdapter da = new OdbcDataAdapter(comando);
list.Clear();
da.Fill(list);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return(list);
}
public void IncrementOdometer(Functions funcCode)
{
if (ENABLE_DB_WRITE)
{
int current_value = GetOdometerValue(funcCode);
int rowsAffected = 0;
string SQL = @"UPDATE GEN_ODOMETER SET ODO = ? WHERE (FUNCID = ? AND USERID = ?);";
using (OdbcCommand comm = new OdbcCommand(SQL, conn)) {
comm.Parameters.AddWithValue("@odo", ++current_value);
comm.Parameters.AddWithValue("@app", funcCode);
comm.Parameters.AddWithValue("@user", GetCurrentAuthor());
try {
rowsAffected = comm.ExecuteNonQuery();
} catch (InvalidOperationException ioe) {
throw ioe;
}
}
if (rowsAffected == 0)
{
SQL = @"INSERT INTO GEN_ODOMETER (ODO, FUNCID, USERID) VALUES (?, ?, ?);";
using (OdbcCommand comm = new OdbcCommand(SQL, conn)) {
comm.Parameters.AddWithValue("@odo", 1);
comm.Parameters.AddWithValue("@app", (int)funcCode);
comm.Parameters.AddWithValue("@user", GetCurrentAuthor());
try {
rowsAffected = comm.ExecuteNonQuery();
} catch (InvalidOperationException ioe) {
throw ioe;
}
}
}
}
}
private void deptos()
{
OdbcConnection conexion = TaquillaDB.getDB();
try
{
string sql = string.Format("SELECT * FROM DEPARTAMENTO;");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
comboBox1.Items.Clear();
comboBox2.Items.Clear();
while (reader.Read())
{
comboBox1.Items.Add(reader.GetString(1));
comboBox2.Items.Add(reader.GetString(1));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
this.Close();
}
conexion.Close();
}
private void cargaSucursales()
{
try
{
OdbcConnection conexion = ASG_DB.connectionResult();
string sql = string.Format("SELECT NOMBRE_SUCURSAL FROM SUCURSAL WHERE ESTADO_TUPLA = TRUE;");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
comboBox1.Items.Clear();
comboBox1.Items.Add(reader.GetString(0));
while (reader.Read())
{
comboBox1.Items.Add(reader.GetString(0));
}
//comboBox1.SelectedIndex = 0;
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
private void okButton_Click(object sender, EventArgs e)
{
if (bookingsListView.SelectedIndices.Count > 0)
{
BookingInformation objectBookingInformation = new BookingInformation();
// if only one matching result, open the booking form for that user
OdbcCommand objectOdbcCommand = objectOdbcConnection.CreateCommand();
objectOdbcCommand.CommandText = "select * from guest"
+ " join Booking"
+ " on guest.guestID = Booking.guestID"
+ " join Rooms on Booking.roomID = rooms.roomID"
+ " join Roomtype on Roomtype.roomTypeId = rooms.roomTypeId "
+ " where guest.guestId = ? and rooms.roomnumber = ? ";
//ListViewItem li = bookingsListView.
string guestId = bookingsListView.SelectedItems[0].SubItems[0].Text;
string roomNumber = bookingsListView.SelectedItems[0].SubItems[6].Text;
objectOdbcCommand.Parameters.Add("guestId", OdbcType.NVarChar).Value = guestId;
objectOdbcCommand.Parameters.Add("roomnumber", OdbcType.Int).Value = roomNumber;
//objectOdbcCommand.Parameters.Add("guestLname", OdbcType.NVarChar).Value = textBox2.Text;
OdbcDataReader dbReader = objectOdbcCommand.ExecuteReader();
dbReader.Read();
objectBookingInformation.readBookingObject(dbReader);
dbReader.Close();
objectOdbcCommand.Dispose();
objectBookingForm = new Booking(objectWelcomeForm, objectOdbcConnection, objectBookingInformation);
objectBookingForm.Show();
this.Close();
}
else
{
}
}
private void CaricaEventi()
{
listaEventi.Rows.Clear();
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
conn.Open();
OdbcCommand cm = new OdbcCommand();
cm.Connection = conn;
cm.CommandText = "SELECT Nome,Giorno,Prenotazione FROM SerataDanzante WHERE Giorno>='" + DateTime.Now.ToShortDateString() + "'";
OdbcDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
List <string> riga = new List <string>();
riga.Add(dr["Nome"].ToString());
riga.Add(DateTime.Parse(dr["Giorno"].ToString()).ToShortDateString());
riga.Add(bool.Parse(dr["Prenotazione"].ToString()).ToString());
listaEventi.Rows.Add(riga.ToArray());
}
dr.Close();
conn.Close();
}
public OdbcDataReader Modificar(string[] datos, string[] campos)
{
string query = "";
int n = 1;
query += " set ";
for (int i = 2; i < datos.Length; i++)
{
query += campos[n];
query += " = '";
query += datos[i];
if (i == datos.Length - 1)
{
query += "'";
}
else
{
query += "',";
}
n++;
}
try
{
cn.conexionbd();
string consulta = "UPDATE " + datos[0] + query + " where " + campos[0] + " = '" + datos[1] + "';";
comm = new OdbcCommand(consulta, cn.conexionbd());
OdbcDataReader mostrar = comm.ExecuteReader();
return(mostrar);
}
catch (Exception err)
{
Console.WriteLine(err.Message);
return(null);
}
}
public string exist(int idCAf, int folio)
{
string respuesta = "False";
try
{
BaseDato con = new BaseDato();
OdbcConnection conexion = con.ConnectPostgres();
OdbcCommand select = new OdbcCommand();
select.Connection = conexion;
select.CommandText = "SELECT * FROM folio where idcaf = " + idCAf + " and folio = " + folio + ";";
OdbcDataReader reader = select.ExecuteReader();
if (reader.RecordsAffected != 0)
{
respuesta = "True";
}
}
catch (Exception ex)
{
throw new Exception("Error" + ex.Message);
}
return(respuesta);
}
protected void ddJuegos_SelectedIndexChanged(object sender, EventArgs e)
{
OdbcConnection miConexion = conectarBD();
if (miConexion != null)
{
String var1 = ddJuegos.SelectedValue;
String query = "select claveJ from juegos where nombre='" + var1 + "'";
//ahora la clave J
OdbcCommand cmd = new OdbcCommand(query, miConexion);
OdbcDataReader rd = cmd.ExecuteReader();
rd.Read();
//lo que se trajo el reader lo voy a guardar en:
int clavej = rd.GetInt16(0);
//solo cambio el query 2
String query2 = "select nombre,resumen,consola,fechaLanzamiento from juegos where claveJ=" + clavej + "";
OdbcCommand cmd2 = new OdbcCommand(query2, miConexion);
OdbcDataReader rd2 = cmd2.ExecuteReader();
gvJuegos.DataSource = rd2;
gvJuegos.DataBind();
rd.Close();
rd2.Close();
}
}
private void caricaEventi_DoWork(object sender, DoWorkEventArgs e)
{
idEvento = new List <int>();
nomeEvento = new List <string>();
dataEvento = new List <DateTime>();
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
conn.Open();
OdbcCommand cm = new OdbcCommand();
cm.CommandText = "SELECT IDSerata,Nome,Giorno FROM SerataDanzante WHERE Giorno>='" + DateTime.Now.ToShortDateString() + "' AND Prenotazione=1";
cm.Connection = conn;
OdbcDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
idEvento.Add(int.Parse(dr["IDSerata"].ToString()));
nomeEvento.Add(dr["Nome"].ToString());
dataEvento.Add(DateTime.Parse(dr["Giorno"].ToString()));
}
dr.Close();
conn.Close();
}
private void btn_buscar_Click(object sender, EventArgs e)
{
string nombres = "";
int cont = 0;
nombres = textBox1.Text;
DataTable fill = new DataTable();
//MessageBox.Show(nombres);
try
{
OdbcCommand = new OdbcCommand(
string.Format("SELECT id_cliente, nombres, apellidos, telefono, estado, incidencia FROM tbl_cliente WHERE nombres ='{0}'", nombres),
seguridad.Conexion.ObtenerConexionODBC()
); //se realiza el query para la consulta de todos los registros de la tabla persona
OdbcDataAdapter = new OdbcDataAdapter(); //se crea un sqlDataAdaptor
OdbcDataAdapter.SelectCommand = OdbcCommand; //ejecutamos el query de consulta
OdbcDataAdapter.Fill(fill); //poblamos el sqlDataAdaptor con el resultado del query
}
catch (Exception Ex)
{
MessageBox.Show("No es posible obtener el cliente", "Error al Realizar la Consulta", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
dataGridView1.DataSource = fill;
DataRow dt1 = fill.Rows[0];
//DataColumn dt2 = fill.Columns[5];
string incidencia = fill.Rows[0][5].ToString();
string id_cliente = Convert.ToString(dt1[0]);
textBox2.Text = Convert.ToString(dt1[0]);
textBox3.Text = Convert.ToString(incidencia);
//MessageBox.Show(Convert.ToString(dt1[0]));
//MessageBox.Show(Convert.ToString(incidencia));
// MessageBox.Show(Convert.ToString(dt2));
}
void llenartbl()
{
//codigo para llevar el DataGridView
OdbcCommand cod = new OdbcCommand();
cod.Connection = conn;
cod.CommandText = ("SELECT NoRegistro, Afiche, Trailer FROM multimedia " +
"WHERE estadoMultimedia=0");
try
{
OdbcDataAdapter eje = new OdbcDataAdapter();
eje.SelectCommand = cod;
DataTable datos = new DataTable();
eje.Fill(datos);
tblMulitimedia.DataSource = datos;
eje.Update(datos);
conn.Close();
}
catch (Exception e)
{
MessageBox.Show("ERROR" + e.ToString());
conn.Close();
}
}
//Bryan Mazariegos
//codigo para obtener correo
private void txt_codigo_TextChanged_1(object sender, EventArgs e)
{
if (boBandera == true && txt_codigo.Text != "")
{
try
{
string strSql = "SELECT correo_paciente FROM tbl_paciente WHERE pk_id_paciente = " + txt_codigo.Text + ";";
OdbcCommand command = new OdbcCommand(strSql, con.conexion());
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
txt_para.Text = reader[0].ToString();
bolBanderacorreo = true;
}
}
catch (Exception err)
{
bolBanderacorreo = false;
btn_enviar.Enabled = false;
limpiar();
MessageBox.Show(err.Message);
}
}
}
private void btnConferma_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Confermi la cancellazione dell'abbonamento " + listaTipologie.Text + "? Questo comporta anche l'eliminazione di tutti gli abbonamenti attivi e eventuali pagamenti sospesi.", "Conferma cancellazione", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.Yes)
{
List <int> idAbbonamenti = new List <int>();
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
conn.Open();
OdbcCommand cm = new OdbcCommand();
cm.CommandText = "SELECT IDAbbonamento FROM Abbonamento WHERE IDTipologia=" + idTipologie[listaTipologie.SelectedIndex];
cm.Connection = conn;
OdbcDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
idAbbonamenti.Add(int.Parse(dr["IDAbbonamento"].ToString()));
}
dr.Close();
for (int i = 0; i < idAbbonamenti.Count; i++)
{
cm.CommandText = "DELETE FROM Lezione WHERE IDAbbonamento=" + idAbbonamenti[i];
cm.ExecuteNonQuery();
}
cm.CommandText = "DELETE FROM Abbonamento WHERE IDTipologia=" + idTipologie[listaTipologie.SelectedIndex];
cm.ExecuteNonQuery();
cm.CommandText = "DELETE FROM Tipologia WHERE IDTipologia=" + idTipologie[listaTipologie.SelectedIndex];
cm.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Abbonamento cancellato. Il programma verrà riavviato per applicare le modifiche!");
this.Close();
}
}
public static bool AddErrorLog(OdbcConnection db, string ErrorMsg, int itemId)
{
String sql = "INSERT INTO errorLog "
+ "(item_id, errorTime, errorMsg) "
+ "VALUES( ?, ?, ?)";
OdbcCommand Command = new OdbcCommand(sql, db);
Command.Parameters.Add("@ID", OdbcType.Int).Value = itemId;
Command.Parameters.Add("@ErrorTime", OdbcType.DateTime).Value = DateTime.Now;
Command.Parameters.Add("@ErrorMsg", OdbcType.VarChar).Value = ErrorMsg;
//Returns 1 if successful
int result = Command.ExecuteNonQuery();
if (result > 0)
{
//Was successful in adding
return(true);
}
else
{
//failed to add
return(false);
}
} //end of AddRow
private void ingresaEspecias(string CodigoM, string NombreSucursal, string Proveedor, string Descripcion, string PrecioCompra, double TotalUnidades)
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("CALL INGRESA_ESPECIAS_SUCURSAL ('{0}','{1}','{2}','{3}',{4},{5},TRUE);", CodigoM, NombreSucursal, Proveedor, Descripcion, PrecioCompra, TotalUnidades);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
if (cmd.ExecuteNonQuery() == 1)
{
// MessageBox.Show("INGRESO CORRECTO MERCADERIA A SUCURSAL NUEVA");
for (int i = 0; i < dataGridView5.RowCount; i++)
{
presentacionesIngreso(CodigoM, dataGridView5.Rows[i].Cells[0].Value.ToString(), dataGridView5.Rows[i].Cells[1].Value.ToString(), dataGridView5.Rows[i].Cells[2].Value.ToString(), dataGridView5.Rows[i].Cells[3].Value.ToString(), dataGridView5.Rows[i].Cells[4].Value.ToString(), dataGridView5.Rows[i].Cells[5].Value.ToString(), dataGridView5.Rows[i].Cells[6].Value.ToString(), dataGridView5.Rows[i].Cells[7].Value.ToString(), dataGridView5.Rows[i].Cells[8].Value.ToString(), dataGridView5.Rows[i].Cells[9].Value.ToString(), dataGridView5.Rows[i].Cells[10].Value.ToString(), dataGridView5.Rows[i].Cells[11].Value.ToString(), dataGridView5.Rows[i].Cells[12].Value.ToString(), dataGridView5.Rows[i].Cells[13].Value.ToString(), dataGridView5.Rows[i].Cells[14].Value.ToString(), dataGridView5.Rows[i].Cells[15].Value.ToString(), NombreSucursal);
}
conexion.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
protected void btnEliminar_Click(object sender, EventArgs e)
{
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.CommandText = "DELETE FROM TBL_Transaccion WHERE NoUnidad = ? " + "&FechaAbordaje = ?";
cmd.Parameters.Add("@NoUnidad", OdbcType.VarChar).Value = txtNoUnidad.Value;
cmd.Connection = new OdbcConnection(Utilities.ObtenerCadenaConexion());
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
IrAlListadoPrincipal();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
if (cmd.Connection.State == System.Data.ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
void detail()
{
string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
//string checkUser;
//TextBox1.Text; //FORM_NO.ToString();
//TextBox2.Text; // EMP_NO.ToString();
string sql = "SELECT ICT_TELE.NEW_USER FROM ICT_TELE WHERE ICT_TELE.FORM_NO ='" + TextBox1.Text + "'";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.Connection.Open();
OdbcDataReader read = cmd.ExecuteReader();
read.Read();
if (read.HasRows)
{
checkUser = read["NEW_USER"].ToString();
}
cmd.Connection.Close();
if (checkUser == "" || checkUser == null)
{
GridView3.Visible = false;
UserDetails();
}
else
{
GridView2.Visible = false;
UserDetail2();
}
}
protected void txtmob_TextChanged(object sender, EventArgs e)
{
try
{
cmd = new OdbcCommand("select * from faculty where phone='" + txtmob.Text + "'", cn);
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
// Image2.Visible = true;
//Label3.Visible = true;
lblinfo111.Visible = true;
txtmail.Text = "";
}
}
catch (OdbcException ex)
{
lblinfo.Text = ex.Message;
}
finally
{
cn.Close();
}
}
void doupdateODBC()
{
Odbcmd = Program.OmaindbCon.CreateCommand();
Odbcmd.CommandText = "UPDATE STUDENTS set fullname = " + q(txtName.Text) + "," +
"sex = " + cb_value(cbSex) + "," +
"ilevel = " + cb_value(cbLevel) + "," +
"school = " + q(txtSchool.Text) + "," +
"center = " + cb_value(cbCenter) + "," +
"shirtsize=" + q(txtShirtSize.Text) + "," +
"schedule = " + cb_value(cbSchedule) + "," +
"s_email = " + q(txtEmail1.Text) + "," +
"email = " + q(txtEmail2.Text) + "," +
"remarks = " + q(txtRemarks.Text) + "," +
//"profilescan=" + q(txtProfile.Text) + "," +
"is_qualified = " + chktol(chkQualified.Checked).ToString() + "," +
"depslip = " + chktol(chkDepSlip.Checked).ToString() + "," +
"is_emailed = " + chktol(chkEmailed.Checked).ToString() +
" WHERE studid = " + Program.lstudid.ToString();
try
{
Odbcmd.ExecuteNonQuery();
MessageBox.Show("Update successful.");
if (MessageBox.Show("Create ERF?", "ERF", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
Program.createbarcode(Program.lstudid);
MessageBox.Show(Program.createprofile_OLEDB(Program.lstudid, true));
Odbcmd.CommandText = "UPDATE STUDENTS SET is_emailed = 0 where studid = " + Program.lstudid.ToString();
Odbcmd.ExecuteNonQuery();
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
}
private void cargaCredito()
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
dataGridView1.Rows.Clear();
string sql = string.Format("select * from caja_facturas where id_caja = {0} and tipo_factura = 'CREDITO';", codigoCaja);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1) + " " + reader.GetString(2), reader.GetString(3), reader.GetString(4), string.Format("{0:###,###,###,##0.00##}", reader.GetDouble(5)), string.Format("{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("{0:###,###,###,##0.00##}", reader.GetDouble(7)), reader.GetString(8));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1) + " " + reader.GetString(2), reader.GetString(3), reader.GetString(4), string.Format("{0:###,###,###,##0.00##}", reader.GetDouble(5)), string.Format("{0:###,###,###,##0.00##}", reader.GetDouble(6)), string.Format("{0:###,###,###,##0.00##}", reader.GetDouble(7)), reader.GetString(8));
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
private void eliminaDetalle(string codigos, string montos)
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("UPDATE DET_CAJA SET ESTADO_TUPLA = FALSE WHERE ID_DET_CAJA = {0};", codigos);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
if (cmd.ExecuteNonQuery() == 1)
{
//.Show("SE GENERO LA CUENTA CORRECTAMENTE");
actualizaTotal(montos);
}
else
{
MessageBox.Show("NO SE PUDO GENERAR NUEVA ORDEN DE COMPRA!", "GESTION COMPRAS", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
public int SetMETADATA_SubprToActivityList(int ID, string NodeType, bool?BOOLisKeyPoint, string Text, string ListIdsBusRoles, string ListIdsApps, int SubProcessID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "update \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" set \"c_u_NodeType\"=?,\"c_u_BOOLisKeyPoint\"=?,\"c_u_Text\"=?,\"c_u_ListIdsBusRoles\"=?,\"c_u_ListIdsApps\"=?,\"c_r_SubProcess\"=? where \"c_id\" = ?";
if (NodeType == null)
{
cmd.Dispose(); DBClose(); throw new Exception("NodeType must not be null!");
}
cmd.Parameters.Add("c_u_NodeType", OdbcType.NVarChar, 5);
cmd.Parameters["c_u_NodeType"].Value = (NodeType != null ? (object)NodeType : DBNull.Value);
cmd.Parameters.Add("c_u_BOOLisKeyPoint", OdbcType.Bit);
cmd.Parameters["c_u_BOOLisKeyPoint"].Value = (BOOLisKeyPoint != null ? (object)BOOLisKeyPoint : DBNull.Value);
cmd.Parameters.Add("c_u_Text", OdbcType.NVarChar, 500);
cmd.Parameters["c_u_Text"].Value = (Text != null ? (object)Text : DBNull.Value);
cmd.Parameters.Add("c_u_ListIdsBusRoles", OdbcType.NVarChar, 200);
cmd.Parameters["c_u_ListIdsBusRoles"].Value = (ListIdsBusRoles != null ? (object)ListIdsBusRoles : DBNull.Value);
cmd.Parameters.Add("c_u_ListIdsApps", OdbcType.NVarChar, 500);
cmd.Parameters["c_u_ListIdsApps"].Value = (ListIdsApps != null ? (object)ListIdsApps : DBNull.Value);
cmd.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
rv = cmd.ExecuteNonQuery();
if (rv != 1)
{
cmd.Dispose(); DBClose(); throw new Exception("Update resulted in " + rv.ToString() + " objects being updated!");
}
cmd.Dispose();
DBClose();
return(rv);
}
public List <Dictionary <string, object> > ExecuteSelectCommand(OdbcCommand command)
{
List <Dictionary <string, object> > tablaResult = new List <Dictionary <string, object> >();
try
{
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary <string, object> rowResult = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
rowResult.Add(reader.GetName(i), reader.GetValue(i));
}
tablaResult.Add(rowResult);
}
}
catch (MyOdbcException e)
{
throw new MyOdbcException("Error Informix OdbcDao: " + e.ToString());
}
return(tablaResult);
}
public OdbcCommand insertarFacturaD(string idProducto, string idFactura, string cantidad, string monto, string idSerie)
{
Conexion conexion = new Conexion();
OdbcCommand command = new OdbcCommand();
command.Connection = conexion.Conectar();
command.CommandText = "SELECT COUNT(*)+1 AS id FROM tbl_facturadetalle";
OdbcDataAdapter mySqlDataAdapter = new OdbcDataAdapter(command);
DataTable dataTable = new DataTable();
mySqlDataAdapter.Fill(dataTable);
int iConteo = 0;
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.Rows[0];
iConteo = Convert.ToInt32(row["id"]);
}
command.CommandText = "INSERT INTO tbl_facturadetalle " +
"VALUES (" + iConteo +
"," + cantidad + "," + monto + "," + idProducto + "," + idFactura + "," + idSerie +")";
return command;
}
public static void Connect()
{
myConnection = new OdbcConnection(dbInfo);
myConnection.Open();
myQuery = myConnection.CreateCommand();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
myQuery = dbController.ODBCCommand;
if (IsAuthenticate())
Response.Redirect("Owner.aspx");
}
public int ExecuteCommand(string cmdStr)
{
try
{
OdbcCommand command = new OdbcCommand(cmdStr, dbConn);
command.CommandType = System.Data.CommandType.Text;
return command.ExecuteNonQuery();
}
catch (Exception ex)
{
exception = ex;
return -1;
}
}
// End GetTable method.
/// <summary>
/// Removes the specified table from the database
/// </summary>
/// <param name="tableName">Name of the table to remove</param>
private void RemoveTable(string tableName)
{
// Check to see if the tablename is valid and if table is present.
if (String.IsNullOrEmpty(tableName) || !TableNameIsValid(tableName) || !TableIsPresent(tableName))
{
return;
}
// Execute command using ODBC connection to remove a table
try
{
// Delete the table using an sql statement.
string sql = "drop table " + tableName;
var di = PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return;
}
OdbcConnection connection = di.Connection;
var cmd = new OdbcCommand(sql, connection);
cmd.ExecuteScalar();
}
catch (Exception ex)
{
WriteError(new ErrorRecord(
ex,
"CannotRemoveSpecifiedTable",
ErrorCategory.InvalidOperation,
null));
}
}
public int Count(string tableName, string queryStr)
{
try
{
string command_str = "SELECT COUNT(*) FROM " + tableName;
if (!string.IsNullOrWhiteSpace(queryStr))
command_str += " WHERE " + queryStr;
OdbcCommand command = new OdbcCommand(command_str, dbConn);
command.CommandType = System.Data.CommandType.Text;
int count = (int)command.ExecuteScalar();
command = null;
return count;
}
catch (Exception ex)
{
exception = ex;
return -1;
}
}
/// <summary>
/// Retrieve the list of tables from the database.
/// </summary>
/// <returns>
/// Collection of DatabaseTableInfo objects, each object representing
/// information about one database table
/// </returns>
private Collection<DatabaseTableInfo> GetTables()
{
var results =
new Collection<DatabaseTableInfo>();
// Using the ODBC connection to the database get the schema of tables.
var di = PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return null;
}
OdbcConnection connection = di.Connection;
DataTable dt = connection.GetSchema("Tables");
int count;
// Iterate through all the rows in the schema and create DatabaseTableInfo
// objects which represents a table.
foreach (DataRow dr in dt.Rows)
{
var tableName = dr["TABLE_NAME"] as string;
DataColumnCollection columns = null;
// Find the number of rows in the table.
try
{
string cmd = "Select count(*) from \"" + tableName + "\"";
var command = new OdbcCommand(cmd, connection);
count = (int) command.ExecuteScalar();
}
catch
{
count = 0;
}
// Create the DatabaseTableInfo object representing the table.
var table =
new DatabaseTableInfo(dr, tableName, count, columns);
results.Add(table);
} // End foreach (DataRow...) block.
return results;
}
// End HasChildItems method.
/// <summary>
/// The Windows PowerShell engine calls this method when the New-Item
/// cmdlet is run. This method creates a new item at the specified path.
/// </summary>
/// <param name="path">The path to the new item.</param>
/// <param name="type">The type of object to create. A "Table" object
/// for creating a new table and a "Row" object for creating a new row
/// in a table.
/// </param>
/// <param name="newItemValue">
/// Object for creating a new instance at the specified path. For
/// creating a "Table" the object parameter is ignored and for creating
/// a "Row" the object must be of type string, which will contain comma
/// separated values of the rows to insert.
/// </param>
protected override void NewItem(
string path,
string type,
object newItemValue)
{
string tableName;
int rowNumber;
PathType pt = GetNamesFromPath(path, out tableName, out rowNumber);
if (pt == PathType.Invalid)
{
ThrowTerminatingInvalidPathException(path);
}
// Check to see if type is either "table" or "row", if not throw an
// exception.
if (!String.Equals(type, "table", StringComparison.OrdinalIgnoreCase)
&& !String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
WriteError(new ErrorRecord(new ArgumentException(
"Type must be either a table or row"),
"CannotCreateSpecifiedObject",
ErrorCategory.InvalidArgument,
path));
throw new ArgumentException("This provider can only create items of type \"table\" or \"row\"");
}
// Path type is the type of path of the container. So if a drive
// is specified, then a table can be created under it and if a table
// is specified, then a row can be created under it. For the sake of
// completeness, if a row is specified, then if the row specified by
// the path does not exist, a new row is created. However, the row
// number may not match as the row numbers only get incremented based
// on the number of rows
if (PathIsDrive(path))
{
if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
{
// Execute command using the ODBC connection to create a table.
try
{
// Create the table using an sql statement.
string newTableName = newItemValue.ToString();
string sql = "create table " + newTableName
+ " (ID INT)";
// Create the table using the Odbc connection from the drive.
var di = PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return;
}
OdbcConnection connection = di.Connection;
if (ShouldProcess(newTableName, "create"))
{
var cmd = new OdbcCommand(sql, connection);
cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
WriteError(new ErrorRecord(
ex,
"CannotCreateSpecifiedTable",
ErrorCategory.InvalidOperation,
path));
}
}
else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
throw new
ArgumentException(
"A row cannot be created under a database, specify a path that represents a Table");
}
}
else
{
if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
{
if (rowNumber < 0)
{
throw new
ArgumentException(
"A table cannot be created within another table, specify a path that represents a database");
}
else
{
throw new
ArgumentException(
"A table cannot be created inside a row, specify a path that represents a database");
}
}
else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
// The user is required to specify the values to be inserted
// into the table in a single string separated by commas.
var value = newItemValue as string;
if (String.IsNullOrEmpty(value))
{
throw new
ArgumentException(
"Value argument must have comma separated values of each column in a row");
}
string[] rowValues = value.Split(',');
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
if (rowValues.Length != table.Columns.Count)
{
string message = String.Format(
CultureInfo.CurrentCulture,
"The table has {0} columns and the value specified must have so many comma separated values",
table.Columns.Count);
throw new ArgumentException(message);
}
if (!Force && (rowNumber >= 0 && rowNumber < table.Rows.Count))
{
string message = String.Format(
CultureInfo.CurrentCulture,
"The row {0} already exists. To create a new row specify row number as {1}, or specify path to a table, or use the -Force parameter",
rowNumber,
table.Rows.Count);
throw new ArgumentException(message);
}
if (rowNumber > table.Rows.Count)
{
string message = String.Format(
CultureInfo.CurrentCulture,
"To create a new row specify row number as {0}, or specify path to a table",
table.Rows.Count);
throw new ArgumentException(message);
}
// Create a new row and update the row with the input
// provided by the user.
DataRow row = table.NewRow();
for (int i = 0; i < rowValues.Length; i++)
{
row[i] = rowValues[i];
}
table.Rows.Add(row);
if (ShouldProcess(tableName, "update rows"))
{
// Update the table from memory back to the data source.
da.Update(ds, tableName);
}
} // End else if (String...) block.
} // End else block.
}
public void DeleteInnerCommand (OdbcCommand odbcCommand, VirtuosoCommand outerCommand)
{
//statementList.Remove (odbcCommand.hstmt, outerCommand);
Remove (odbcCommand.hstmt, outerCommand);
}
static void Main(string[] args)
{
string constr = "server=localhost;user=root;database=lnm;port=3306;password='';";
MySqlConnection conn = new MySqlConnection(constr);
Console.WriteLine("Connecting to Mysql");
conn.Open();
//A select statement to get all rows that do not have code filled in as per said. Also These should contain the unused/ unchecked codes.
string strSQL = "SELECT * FROM `lnm`.`sms_in` WHERE `sms_text` LIKE '%Confirmed.%' AND `sender_number` IS NOT NULL AND (`code` = '' OR `code` IS NULL)";
MySqlCommand cmd = new MySqlCommand(strSQL, conn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string strText = dr[1].ToString();
//Console.WriteLine(strText);
//Console.ReadLine();
string[] arrText = strText.Split(' ');
//AA64SF914 Confirmed.on 17/2/14 at 12:02 PMKsh33,950.00 received from 254727137226 Mark ADAMS.New Account balance is Ksh2,550.00
//These are the values that should be inserted to the NULL columns
/*
Response.Write(arrText[0]); //TranCode
Response.Write(brk);
Response.Write(arrText[5]); //amount
Response.Write(brk);
Response.Write(arrText[9]); //fname
Response.Write(brk);
Response.Write(arrText[10]); //lname
Response.Write(brk);
Response.Write(arrText[8]); //msisdn
Response.Write(brk);
*/
//Ignore. These are the other text fields to be used as tests
//Here I'll be getting the row ID and the update will occur per Id That fulfills the said condition
string ID1 = dr["id"].ToString();
//Response.Write(ID1);
//****************************************
var charsToRemove = new string[] { "\n", "PMKsh", "AMKsh", "Ksh", ",", ".New", "'" };
string str = arrText[5];
string strfn = arrText[9];
string strln = arrText[10];
foreach (var c in charsToRemove)
{
str = str.Replace(c, string.Empty);
strfn = strfn.Replace(c, string.Empty);
strln = strln.Replace(c, string.Empty);
}
arrText[5] = str;
arrText[9] = strfn;
arrText[10] = strln;
/*//****************************************
Response.Write(brk);
Response.Write(str);
Response.Write(brk);
Response.Write(strln);
//****************************************
*/
strSQL = "Update `lnm`.`sms_in` SET `code` = '" + arrText[0] + "', `amount` = '" + arrText[5] + "', `firstname` = '" + arrText[9] + "', `lastname` = '" + arrText[10] + "', `msisdn` = '" + arrText[8] + "' WHERE `id` = " + ID1;
OdbcCommand update = new OdbcCommand(strSQL, con);
//Response.Write(brk);
//Response.Write(strSQL);
update.ExecuteNonQuery();
} //end of the while loop
}
