public static List<Auspiciante> getAllAuspiciantes()
{
OdbcConnection con = ConexionBD.ObtenerConexion();
DataSet ds = new DataSet();
List<Auspiciante> listaAuspiciantes = new List<Auspiciante>();
try
{
OdbcCommand cmd = new OdbcCommand("SELECT a.id, a.imagen FROM auspiciante a", con);
cmd.CommandType = CommandType.Text;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Auspiciante a = new Auspiciante();
a.IdAuspiciante = dr.GetInt32(dr.GetOrdinal("id"));
a.ImagenAuspiciante = ImagenDAL.getImagen(con,dr.GetInt32(dr.GetOrdinal("imagen")));
listaAuspiciantes.Add(a);
}
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener los auspiciantes. " + e.Message);
}
return listaAuspiciantes;
}
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;
}
/// <summary>
/// Setea la lista de imagenes de una noticia
/// </summary>
/// <returns></returns>
public static List<Imagen> getImagenes(Noticia noticia, OdbcConnection con)
{
List<Imagen> listaImagenes = new List<Imagen>();
OdbcDataReader dr = null;
String query = "SELECT i.id, i.pathBig, i.pathSmall, i.portada, i.pathMedium FROM imagen i, imagen_x_noticia n WHERE i.id=n.idImagen AND n.idNoticia=" + noticia.IdNoticia;
try
{
OdbcCommand cmd = new OdbcCommand(query, con);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
Imagen imagen = new Imagen();
imagen.IdImagen = dr.GetInt32(0);
imagen.PathBig = dr.GetString(1);
imagen.PathSmall = dr.GetString(2);
imagen.Portada = dr.GetBoolean(3);
imagen.PathMedium = dr.GetString(4);
listaImagenes.Add(imagen);
}
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message);
}
return listaImagenes;
}
/// <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);
}
}
}
public static OdbcDataReader getODBC()
{
string strConn = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=False;Dbq=";//Driver驱动程序,ReadOnly=False必须有，否则插入数据会报错,Dbq
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
file.Multiselect = false;
if (file.ShowDialog() == DialogResult.Cancel)
{
return(null);
}
var path = file.FileName;
strConn += path;
System.Data.Odbc.OdbcConnection odbcConn = new System.Data.Odbc.OdbcConnection(strConn);//ODBC连接
try
{
odbcConn.Open(); //打开连接
string sSQL = "SELECT * FROM [appstore_games$]"; //注意格式，SQL语句查询表格所有的数据
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(sSQL, odbcConn); //初始化ODBC命令
mConn = odbcConn;
// comm.ExecuteNonQuery();//执行SQL语句
return(command.ExecuteReader());
}
catch (Exception excp)
{
throw excp;
}
}
/// <summary>
/// Método que permite obtener el nivel de estructura de un empleado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <returns></returns>
public string ObtenerNivelEstructura(int EmpleadoId)
{
string Response = string.Empty;
try
{
string QueryString = @"SELECT NivelDepartamento
FROM vwEmpleados
WHERE idEmpleado = '" + EmpleadoId.ToString() + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Response = reader.GetString(0);
}
}
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.ObtenerNivelEstructura - " + ex.Message);
}
return(Response);
}
/// <summary>
/// Permite valorar si el empleado tiene justificado el día ingresado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <param name="Fecha"></param>
/// <returns></returns>
public bool GetJustificacion(int EmpleadoId, DateTime Fecha)
{
bool DiaJustificado = false;
try
{
string QueryString = @"EXEC stp_GetJustificacionFalta " + EmpleadoId + ", '" + Fecha.ToString("yyyyMMdd") + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DiaJustificado = reader.GetBoolean(0);
}
reader.Close();
reader.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.GetJustificacion - " + ex.Message);
}
return(DiaJustificado);
}
/// <summary>
/// SELECT compania AS id, razon_social AS compania FROM companias
/// </summary>
/// <returns></returns>
public List <Catalogo> CatalogoCompanias()
{
List <Catalogo> Companias = new List <Catalogo>();
try
{
string QueryString = @"SELECT compania AS id, razon_social AS compania FROM companias";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
Catalogo compania;
while (reader.Read())
{
compania = new Catalogo();
compania.id = reader.GetInt32(0);
compania.Descripcion = reader.GetString(1);
Companias.Add(compania);
}
compania = new Catalogo();
compania.id = 1000;
compania.Descripcion = "TODAS";
Companias.Add(compania);
}
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.CatalogoCompanias - " + ex.Message);
}
return(Companias);
}
/// <summary>
/// Obtiene la lista de empleados de la nómina
/// </summary>
/// <returns></returns>
public List <Catalogo> CatalogoEmpleados()
{
List <Catalogo> Empleados = new List <Catalogo>();
try
{
string QueryString = @"SELECT idEmpleado, NombreEmpleado FROM vwEmpleados";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
Catalogo Empleado;
while (reader.Read())
{
Empleado = new Catalogo();
Empleado.id = reader.GetInt32(0);
Empleado.Descripcion = reader.GetString(1);
Empleados.Add(Empleado);
}
}
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.CatalogoEmpleados - " + ex.Message);
}
return(Empleados);
}
/// <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();
}
}
private String addUser()
{
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_addUser]( ?, ?, ?, ? )}";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = dbConnection;
cmd.Parameters.Add("@hawkId", OdbcType.NVarChar, 400).Value = txtHealthcareID.Text;
cmd.Parameters.Add("@administrator", OdbcType.Bit).Value = chkAdmin.IsChecked;
cmd.Parameters.Add("@name", OdbcType.NVarChar, 400).Value = txtName.Text;
cmd.Parameters.Add("@numRecords", OdbcType.Int);
cmd.Parameters["@numRecords"].Direction = System.Data.ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
dbConnection.Close();
return cmd.Parameters["@numRecords"].Value.ToString();
}
}
catch (Exception)
{
return null;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string htmlstr = "<h2> </h2> <table style='width:100%;'>";
string cust_id = DropDownList1.SelectedItem.ToString();
string name;
string sex;
string dob;
string relationship;
string constr = Session["connection"].ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
string sql = "select * from NOMINEE where cust_id = '" + cust_id + "';";
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
name = reader["name"].ToString();
sex = reader["sex"].ToString();
dob = reader["dob"].ToString();
relationship = reader["relationship"].ToString();
htmlstr += "<tr><td class='style2'>Name:</td><td class='style1'>" + name + "</td><tr><td class = 'style2'>Sex:</td><td class = 'style1'>" +sex + "</td></tr><tr><td class='style2'> Relationship:</td><td class = 'style1'>" + relationship + "</td></tr><tr><td class='style2'>DOB:</td><td class = 'style1'>" + dob + "</td></tr><tr><td class='style2'>Customer ID :</td><td class = 'style1'>" + cust_id + "</td></tr>";
}
table_data.InnerHtml = htmlstr;
}
public string getPredictionWS(string currency_name)
{
string connString = "DSN=MySQLODBC;UID=root;PWD=admin";
OdbcConnection conn = new OdbcConnection(connString);
OdbcCommand comm = new OdbcCommand();
comm.Connection = conn;
comm.CommandTimeout = 300;
comm.CommandText = "select CurrencyID from currency where CurrencyName = '" + currency_name + "'";
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow row = ds.Tables[0].Rows[0];
string currency_id = row[0].ToString();
comm.CommandText = "select predictsignal,predictprice,recognitionrate from currencyprediction where CurrencyID = " + currency_id + "";
OdbcDataAdapter da1 = new OdbcDataAdapter(comm);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
string predictionarray;
predictionarray = ds1.Tables[0].Rows[0].ItemArray[0] + "," + ds1.Tables[0].Rows[0].ItemArray[1] + "," + ds1.Tables[0].Rows[0].ItemArray[2];
return predictionarray;
}
/// <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 static void queryCodeFile(object sender, string strType, string strParam)
{
try
{
System.Data.DataTable dt = new System.Data.DataTable();
List <System.Web.UI.WebControls.ListItem> listItem = new List <System.Web.UI.WebControls.ListItem>();
string strSql = "Select item_code, description From code_file Where check_flag='Y' And item_type=?" + strParam;
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(strSql);
cmd.Parameters.Add("@Param", System.Data.Odbc.OdbcType.Char).Value = strType;
Dev.ODBCSql.MyODBCConnection conn = new Dev.ODBCSql.MyODBCConnection();
dt = conn.GetData(cmd);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
listItem.Add(new System.Web.UI.WebControls.ListItem(dt.Rows[i]["item_code"].ToString().Trim() + " " + dt.Rows[i]["description"].ToString().Trim(), dt.Rows[i]["item_code"].ToString().Trim()));
}
((System.Web.UI.WebControls.DropDownList)sender).Items.AddRange(listItem.ToArray());
((System.Web.UI.WebControls.DropDownList)sender).DataBind();
}
}
catch (Exception ex)
{
PublicLib.handleError("", "PublicLib", ex.Message);
}
}
public void setClientDBData(string clientId)
{
try
{
#region get values from DB and set the clients prop
string cmd = "SELECT smoker_code, smoker_value, smoking_stat_assess_date, ss_demographics_dict_8_code,ss_demographics_dict_8_value " +
"FROM patient_current_demographics " +
"WHERE PATID = ?";
using (OdbcConnection con = new OdbcConnection(ConnectionStringPM))
{
con.Open();
using (var command = new OdbcCommand(cmd, con))
{
command.Parameters.Add(new OdbcParameter("PATID", clientId));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
this.client.Id = clientId;
this.client.LGBT = reader["ss_demographics_dict_8_code"].ToString();
this.client.SmokerStatus = reader["smoker_code"].ToString();
}
}
}
}
status = "ClientId = " + clientId + "<br>" +
"LGBT = " + this.client.LGBT + "<br>" +
"Smoker = " + this.client.SmokerStatus + "<br>";
#endregion
}
catch (Exception exc) { status = exc.ToString(); }
}
public static void queryDscweek(object sender)
{
try
{
System.Data.DataTable dt = new System.Data.DataTable();
List <System.Web.UI.WebControls.ListItem> listItem = new List <System.Web.UI.WebControls.ListItem>();
string strSql = "Select b.doc_code, b.emp_name From dscdocm b Inner Join dscweek a On a.emp_id=b.emp_id Where p_yymm=? And shift_no='A13'";
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(strSql);
cmd.Parameters.Add("@Param1", System.Data.Odbc.OdbcType.Char).Value = DateTime.Now.ToString("yyyyMM");
//cmd.Parameters.Add("@Param2", System.Data.Odbc.OdbcType.Char).Value = DateTime.Now.ToString("yyyyMM");
Dev.ODBCSql.MyODBCConnection conn = new Dev.ODBCSql.MyODBCConnection();
dt = conn.GetData(cmd);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
listItem.Add(new System.Web.UI.WebControls.ListItem(dt.Rows[i]["doc_code"].ToString().Trim() + " " + dt.Rows[i]["emp_name"].ToString().Trim(), dt.Rows[i]["doc_code"].ToString().Trim()));
}
((System.Web.UI.WebControls.DropDownList)sender).Items.AddRange(listItem.ToArray());
((System.Web.UI.WebControls.DropDownList)sender).DataBind();
}
}
catch (Exception ex)
{
PublicLib.handleError("", "PublicLib", ex.Message);
}
}
public OdbcDataAdapter (OdbcCommand selectCommand)
{
DeleteCommand = null;
InsertCommand = null;
SelectCommand = selectCommand;
UpdateCommand = null;
}
protected void Button1_Click(object sender, EventArgs e)
{
string[] strArray = new string[5] { "Delhi", "Mumbai", "Kolkata", "Chennai", "Chandigarh" };
foreach (string str in strArray)
{
OdbcConnection oledbConn = new OdbcConnection("DSN=exceldb");
try
{
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OdbcCommand cmd = new OdbcCommand("SELECT * FROM [Details$]", oledbConn);
//cmd.Parameters.AddWithValue("@city",str);where city=@city
// Create new OleDbDataAdapter
OdbcDataAdapter oleda = new OdbcDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
// DataSet ds = new DataSet();
DataTable dt = new DataTable();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(dt);
/* if(dt.Rows.Count>0)
{
ID = dt.Rows[0]["ID"].ToString(); //Where ColumnName is the Field from the DB that you want to display
name= dt.Rows[0]["Name"].ToString();
Address = dt.Rows[0]["Address"].ToString();
EmailAddress = dt.Rows[0]["emailaddress"].ToString();
if(EmailAddress!=null)
{
if(EmailAddress==)
}
EmailContent = dt.Rows[0]["emailcontent"].ToString();
}
*/
DataView dv = new DataView(dt);
// dv.Sort = "emailcontents";
// dv.Sort = "Name";
// Bind the data to the GridView
Grdexcel.DataSource = dv;
Grdexcel.DataBind();
// cmd = new OdbcCommand("delete FROM [ter$] where Address like 'M%'", oledbConn);
// cmd.ExecuteNonQuery();
//cmd.CommandType
}
catch (Exception ex)
{
Alert.Show("Sorry");
}
finally
{
// Close connection
oledbConn.Close();
}
}
}
/// <summary>
/// Permite obtener las vacaciones tomadas según el tiempo de antiguedad
/// </summary>
/// <param name="EmpleadoId"></param>
/// <param name="FechaInicio"></param>
/// <param name="FechaFin"></param>
/// <returns></returns>
public static int GetTakenVacations(int EmpleadoId, int Antiguedad)
{
int TakenVacations = 0;
try
{
string QueryString = "SELECT SUM(dias_descanso) FROM vacaciones_empleado WHERE id = " + EmpleadoId + " AND antiguedad = " + Antiguedad;
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
TakenVacations = reader.GetInt32(0);
}
}
}
}
catch (Exception exc)
{
//Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
}
return(TakenVacations);
}
/// <summary>
/// Permite obtener la cantidad de días que el empleado ha gozado de vacaciones en el periodo definido entre FechaInicio y FechaFin
/// </summary>
/// <param name="empleado"></param>
/// <param name="FechaInicio"></param>
/// <param name="FechaFin"></param>
/// <returns></returns>
public static int GetTakenVacations(int EmpleadoId, DateTime FechaInicio, DateTime FechaFin)
{
int TakenVacations = 0;
try
{
string QueryString = "SELECT SUM(VACACIONES.dias_descanso) AS VacacionesTomadas " +
" FROM vacaciones_empleado VACACIONES "+
"INNER JOIN vwEmpleados EMPLEADOS " +
" ON VACACIONES.id = EMPLEADOS.idEmpleado " +
" AND VACACIONES.compania = EMPLEADOS.idCompania "+
" WHERE EMPLEADOS.idEmpleado = " + EmpleadoId +
" AND VACACIONES.fecha_inicio BETWEEN '"+ FechaInicio.ToString("yyyy-MM-dd HH:mm:ss") + "' " +
" AND '"+ FechaFin.ToString("yyyy-MM-dd HH:mm:ss") + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
TakenVacations = reader.GetInt32(0);
}
}
}
}
catch (Exception exc)
{
//Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
}
return(TakenVacations);
}
internal object TraerValor(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;
return(comando.ExecuteScalar());
}
else
{
if (TipoBase == "sql")
{
SqlCommand comando = new SqlCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as SqlConnection;
return(comando.ExecuteScalar());
}
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;
return(comando.ExecuteScalar());
}
}
}
//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;
}
/// <summary>
/// Permite obtener el puesto de un empleado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <returns></returns>
public string ObtenerPuestoEmpleado(int EmpleadoId)
{
string Puesto = string.Empty;
try
{
string QueryString = @"SELECT Puesto
FROM vwEmpleados
WHERE idEmpleado = " + EmpleadoId.ToString();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Puesto = reader.GetString(0);
}
}
}
}
catch (Exception ex)
{
//
}
return(Puesto);
}
public string GR_STR(string si_id, OdbcConnection conn, string rcx)
{
string gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR7;
if (rcx == "RC0" || rcx == "RC2" || rcx == "RC4") { }
else if (rcx == "RC1" || rcx == "RC3") { gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR9; }
else if (rcx == "RCFIE") { gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR15; }
else if (rcx == "RCFJH") gr_format = RC_Html_EXCEL_TAG.EXCEL_GR_TR31;
using (OdbcDataReader dr = new OdbcCommand(String.Format("select gi from field_item where fi_id='{0}' and not gi is null;", si_id), conn).ExecuteReader())
{
if (dr.Read())
{
int gi=-1;
if (int.TryParse(dr[0].ToString(),out gi) && gi > 0)
{
using (OdbcDataReader dr0 = new OdbcCommand(String.Format("select gr_rc, name,classno,gr_period,gr_date from gt_item where gi='{0}'", gi), conn).ExecuteReader())
{
if (dr0.Read())
{
return string.Format(gr_format, "GR", dr0[0], dr0[1], dr0[2], dr0[3], dr0[4]);
}
}
}
}
}
using (OdbcDataReader dr = new OdbcCommand(String.Format("select gr_rc, name,classno,gr_period,gr_date from field_gr where fi_id='{0}'", si_id), conn).ExecuteReader())
{
if (dr.Read())
{
return string.Format(gr_format, "GR", dr[0], dr[1], dr[2], dr[3], dr[4]);
}
}
return "";
}
//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;
}
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();
}
}
}
public UserInfo GetUser(string userId)
{
string queryString = "SELECT * FROM UserDetail WHERE UserId Like '" + userId + "'";
using (OdbcConnection con = new OdbcConnection(ConnectionString))
{
con.Open();
using (OdbcCommand cmd = new OdbcCommand(queryString, con))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
UserInfo user = new UserInfo()
{
UserId = reader.GetString(reader.GetOrdinal("UserId")),
EmailId = reader.GetString(reader.GetOrdinal("EmailId")),
LastLogIn = reader.GetDateTime(reader.GetOrdinal("LastLogIn")),
RegisteredTimestamp = reader.GetDateTime(reader.GetOrdinal("RegisteredTimestamp"))
};
return user;
}
}
}
return null;
}
/// <summary>
/// Use the Username property to fill in the remainder of the fields if the User exists in the database.
/// </summary>
/// <returns>True, if the record was found. Else the return value will be false.</returns>
public bool Find()
{
string mySelectQuery = "select AES_DECRYPT(Password, 'key'), idNumber, UserClass from User where Username = '******'";
OdbcConnection myConnection = GetConnection();
OdbcCommand myCommand = new OdbcCommand(mySelectQuery, myConnection);
myConnection.Open();
bool recordFound = false;
OdbcDataReader myReader;
myReader = myCommand.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (myReader.Read())
{
m_Password = myReader.GetString(0);
m_idNumber = myReader.GetInt32(1);
m_UserClass = myReader.GetString(2);
recordFound = true;
}
}
finally
{
myReader.Close();
myConnection.Close();
}
return recordFound;
}
/// <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 Jugador getJugador_plantelActual(int id)
{
OdbcConnection conexion = null;
OdbcCommand cmd = null;
Jugador jugador = new Jugador();
try
{
conexion = ConexionBD.ObtenerConexion();
String getJugador = "select j.id, j.nombreApellido, j.posicion, j.idPlantel " +
"from jugador j where j.idPlantel = 1 and j.id = " + id;
cmd = new OdbcCommand(getJugador, conexion);
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
jugador.IdJugador = Convert.ToInt32(dt.Rows[0]["id"].ToString());
jugador.NombreApellido = dt.Rows[0]["nombreApellido"].ToString();
jugador.Posicion = dt.Rows[0]["posicion"].ToString();
jugador.Foto = ImagenDAL.getImagenJugador(conexion, id);
}
catch (Exception e)
{
throw new SportingException("Ocurrio un error al intentar obtener los datos de un jugador. " + e.Message);
}
finally
{
cmd.Connection.Close();
}
return jugador;
}
/// <summary>
/// retorna una imagen
/// </summary>
/// <returns></returns>
public static Imagen getImagen(OdbcConnection con, int idImagen)
{
DataSet ds = new DataSet();
Imagen imagen = new Imagen();
try
{
OdbcCommand cmd = new OdbcCommand("SELECT i.id, i.pathBig, i.pathSmall, i.portada, i.pathMedium FROM imagen i WHERE i.id=" + idImagen, con);
cmd.CommandType = CommandType.Text;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
imagen.IdImagen = dr.GetInt32(0);
imagen.PathBig = dr.GetString(1);
imagen.PathSmall = dr.GetString(2);
imagen.Portada = dr.GetBoolean(3);
imagen.PathMedium = dr.GetString(4);
}
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener la imagen. " + e.Message);
}
return imagen;
}
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();
}
}
}
//initializing the leader handler
public void BeginHandling()
{
try
{
//writer.WriteLine("I am not done");
command = " SELECT T.Number, T.Assigner, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" +
" FROM Ticket AS T, Ticket_Information AS I" +
" WHERE T.Number=I.Number AND I.Assigned='" + userName +
"' AND (I.Status = 'Assigned' OR I.Status='Waiting' OR I.Status='Work_In_Progress') AND I.Due_Date > #" + System.DateTime.Now + "#";
sqlCommand = new OdbcCommand(command, dbConn);
dbReader = sqlCommand.ExecuteReader();
//Console.WriteLine("I am done");
while (dbReader.Read())
{
line = "";//empty the line so you can read the next row
for (int i = 0; i < dbReader.FieldCount - 1; i++)
{
line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas
}
writer.WriteLine(line);
writer.Flush();
}
writer.WriteLine(".");// end of reading from the database
writer.Flush();
DB_Handler.DisposeAll(sqlCommand, dbReader);
WaitForQueries();
//do not forget to close the reader
}
catch
{
Console.WriteLine(" The Database is down please try again later");//for debugging server
}
}
protected void Button1_Click1(object sender, EventArgs e)
{
int i;
string htmlstr = "<table style='width:100%;'>";
string data;
string sql = TextBox1.Text;
string constr = Session["connection"].ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
int col_count = reader.FieldCount;
int count1;
while (reader.Read())
{
htmlstr += "<tr>";
count1 = col_count;
i = 0;
while (count1 != 0)
{
data = reader[i].ToString();
htmlstr += "<td class='style2'>" + data + "</td>";
count1--;
i++;
}
htmlstr += "</tr>";
}
htmlstr += "</table>";
table_data.InnerHtml = htmlstr;
}
/// <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
}
}
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;
}
public bool Select(string tableName , ref DataTable dataTable)
{
if ( connection == null )
return false ;
try
{
dataTable = new DataTable("ODBCDataTable");
string query = "SELECT * FROM " + OnGetTableName(tableName) ;
OdbcCommand cmd = new OdbcCommand(query, connection);
OdbcDataReader dataReader = cmd.ExecuteReader();
dataTable.Load(dataReader) ;
dataReader.Close();
}
catch( Exception ex )
{
if(!tableName.Equals("checkDataTable"))
{
Debug.Log( ex.ToString() ) ;
}
Close() ;
return false ;
}
return true ;
}
public static DataTable getDataTableImagenes(int id)
{
OdbcConnection con = ConexionBD.ObtenerConexion();
DataSet ds = new DataSet();
List<Noticia> listaNoticias = new List<Noticia>();
DataTable dataTable = null;
try
{
OdbcCommand cmd = new OdbcCommand("SELECT i.pathBig, i.pathSmall, i.pathMedium FROM imagen_x_noticia ixn, imagen i" +
" WHERE ixn.idNoticia = " + id + " AND i.id = ixn.idImagen", con);
cmd.CommandType = CommandType.Text;
dataTable = new DataTable();
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataTable);
}
catch (Exception e)
{
throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message);
}
finally
{
con.Close();
}
return dataTable;
}
public static System.Data.Odbc.OdbcDataReader ExecuteReaderQueryOnDB(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();
return(QCommand.ExecuteReader());
}
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;
}
}
/// <summary>
/// Permite obtener la fecha de antigüedad del empleado ingresado como parámetro
/// </summary>
/// <param name="EmpladoId"></param>
/// <returns>DateTime</returns>
public static DateTime GetAntiquity(int EmpleadoId)
{
DateTime AntiquityDate = new DateTime();
try
{
string QueryString = "SELECT fecha_antiguedad " +
" FROM Empleados EMP "+
"INNER JOIN vwEmpleados VISTA " +
" ON VISTA.idEmpleado = EMP.id "+
" AND VISTA.idCompania = EMP.compania "+
" WHERE VISTA.idEmpleado = "+ EmpleadoId;
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
AntiquityDate = reader.GetDateTime(0);
}
}
}
}
catch (Exception exc)
{
//Agregar bitácora para marcar las excepciones surgidas en la clase de acceso a datos.
}
return(AntiquityDate);
}
/// <summary>
/// initialization casting for InitializeDataAccess()
/// </summary>
/// <param name="type"></param>
/// <param name="ConnectionString"></param>
/// <param name="Query"></param>
private void castProvider(ProviderType type, string ConnectionString, string Query = null)
{
switch (type)
{
case ProviderType.Oledb:
conn = new OleDbConnection(ConnectionString);
cmd = new OleDbCommand(Query, (OleDbConnection)conn);
da = new OleDbDataAdapter();
break;
case ProviderType.Odbc:
conn = new OdbcConnection(ConnectionString);
cmd = new OdbcCommand(Query, (OdbcConnection)conn);
da = new OdbcDataAdapter();
break;
case ProviderType.SqlClient:
conn = new SqlConnection(ConnectionString);
cmd = new SqlCommand(Query, (SqlConnection)conn);
da = new SqlDataAdapter();
break;
//case ProviderType.OracleClient:
// conn = new OracleConnection(ConnectionString);
// cmd = new OracleCommand(Query,(OracleConnection)conn);
// break;
}
}
public Database(string connectionString)
{
OC = new OdbcConnection(connectionString);
dbCommand = new OdbcCommand();
dbCommand.Connection = OC;
OC.Open();
}
static void Main(string[] args)
{
//"DRIVER={MySQL ODBC 5.2w Driver};SERVER=localhost;DATABASE=config;UID=root;PASSWORD=liu355dq;"
string MyConString = "DRIVER={MySQL ODBC 5.2w Driver};" +
"SERVER=localhost;" +
"DATABASE=g_db;" +
"UID=root;" +
"PASSWORD=liu355dq;";
OdbcConnection myconnection = new OdbcConnection(MyConString);
OdbcCommand cmd = new OdbcCommand("select * from g_operator");
cmd.Connection = myconnection;
myconnection.Open();
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[1]);
}
myconnection.Close();
Console.ReadKey();
}
protected void Page_Load(object sender, EventArgs e)
{
// string ImageId = System.Convert.ToString(Request.QueryString["name"]);
int articleID = System.Convert.ToInt32(Request.QueryString["textid"]);
string MyConString = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection connection = new OdbcConnection(MyConString);
string sqlNews1 = "select * FROM latestnews";
OdbcCommand commandNews1 = new OdbcCommand(sqlNews1, connection);
connection.Open();
OdbcDataReader dr = commandNews1.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
Response.ContentType = dr["date"].ToString();
Response.ContentType = dr["title"].ToString();
Response.ContentType = dr["content"].ToString();
}
connection.Close();
string selectNews = "SELECT date, title, content FROM latestnews ORDER BY date DESC";
OdbcCommand command = new OdbcCommand(selectNews, connection);
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}
/// <summary>
/// Obtiene el parámetro máximo de antiguedad de la tabla de vacaciones
/// </summary>
/// <returns></returns>
public static int MaximaAntiguedadVacaciones(int EmpleadoId)
{
int AntVacaciones = 0;
try
{
string QueryString = @"SELECT MAX (antiguedad) FROM vacaciones_empleado WHERE id =" + EmpleadoId.ToString();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CadenaConexion))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
AntVacaciones = Convert.ToInt32(reader.GetString(0));
}
}
}
}
catch (Exception ex)
{
//
}
return(AntVacaciones);
}
private void btnSignIn_Click(object sender, EventArgs e)
{
OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
OdbcCommand cmd = new OdbcCommand("SELECT EMPLOYEEPASSWORD FROM EMPLOYEE WHERE LASTNAME = '" + txtUserName.Text + "'AND EMPLOYEETYPE = 'Foreman';", Conn);
Conn.Open();
Object EmpPass = cmd.ExecuteScalar();
cmd.Dispose();
Conn.Close();
if (txtUserName.Text == "")
{
MessageBox.Show("User name is empty!");
}
else if (Convert.ToString(EmpPass) == "")
{
MessageBox.Show("Password is empty!");
}
else if (Convert.ToString(EmpPass) != txtPassword.Text)
{
MessageBox.Show("Wrong password! please type again!");
}
else
{
string Emp = Convert.ToString(EmpPass);
var frm = new Form1(Emp);
frm.ShowDialog();
//this.Visible = false
}
}
public string getQuotesWS(string currency_name)
{
string connString = "DSN=MySQLODBC;UID=root;PWD=admin";
OdbcConnection conn = new OdbcConnection(connString);
OdbcCommand comm = new OdbcCommand();
comm.Connection = conn;
comm.CommandText = "select CurrencyID from currency where CurrencyName = '" + currency_name + "'";
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow row = ds.Tables[0].Rows[0];
string currency_id = row[0].ToString();
comm.CommandText = "select currentprice from currencyprediction where CurrencyID = " + currency_id + "";
OdbcDataAdapter da1 = new OdbcDataAdapter(comm);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
DataRow row1 = ds1.Tables[0].Rows[0];
string currentQuotePrice = row1[0].ToString();
return currentQuotePrice;
}
private void InsertNotes()
{
Notes = Interaction.InputBox("Enter Note:", "TeleRad - Client", " ");
string strSQL = "";
string centre = " ";
Myconnectionclass CN2 = new Myconnectionclass();
OdbcCommand Cmd2;
OdbcDataReader Rs2;
CN2.OpenConnection();
Cmd2 = new System.Data.Odbc.OdbcCommand("SELECT * from UserMaster WHERE UserName='******'", CN2.DBConnection);
Rs2 = Cmd2.ExecuteReader();
if (Rs2.HasRows)
{
centre = Rs2["Centre"].ToString();
}
Rs2.Close();
Cmd2.Dispose();
CN2.closeconnection();
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand("SELECT * from Reports WHERE StudyUID='" + StID + "'", CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
if (Rs1.HasRows)
{
if (Notes.Length <= 0)
{
strSQL = "UPDATE Reports SET IsNotes=0, Centre='" + centre + "' WHERE StudyUID='" + StID + "'";
}
else
{
strSQL = "UPDATE Reports SET Notes='" + Notes + "', IsNotes=1, Centre='" + centre + "' WHERE StudyUID='" + StID + "'";
}
}
else
{
if (Notes.Length <= 0)
{
strSQL = "INSERT INTO Reports (StudyUID, IsNotes,Centre) VALUES ('" + StID + "',1,'" + centre + "')";
}
else
{
strSQL = "INSERT INTO Reports (StudyUID, Notes, IsNotes,Centre) VALUES ('" + StID + "', '" + Notes + "', 1,'" + centre + "')";
}
}
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
CN.OpenConnection();
Cmd = new System.Data.Odbc.OdbcCommand(strSQL, CN.DBConnection);
Rs1 = Cmd.ExecuteReader();
Rs1.Close();
Cmd.Dispose();
CN.closeconnection();
MessageBox.Show("Successfully Sent Study", "TeleRad - Client", MessageBoxButtons.OK);
}
public DataTable ExecuteSQLQuery(string sQuery, string sCompanyCode, OdbcParameter[] param)
{
string sFuncName = "ExecuteSQLQuery()";
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;
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
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;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
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(oDs.Tables[0]);
}
public DataTable ExecuteNonQuery(string sQuery, OdbcParameter[] param)
{
string sFuncName = "ExecuteNonQuery";
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Starting Function", sFuncName);
}
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
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;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
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(oDs.Tables[0]);
}
static List <etf_epf_record> GetListETF(string filter = "")
{
System.Data.Odbc.OdbcConnection l_objConnection = new System.Data.Odbc.OdbcConnection();
System.Data.Odbc.OdbcCommand l_objCmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader l_objRdr = null;
l_objConnection.ConnectionString = "DSN=PRODDSN_LIVE;PWD=INTERNET;UID=INTERNET";
l_objConnection.Open();
l_objCmd.Connection = l_objConnection;
l_objCmd.CommandType = CommandType.Text;
string sql = string.Empty;
if (string.IsNullOrEmpty(filter))
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' order by etdate";
}
else
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' and etref in " + filter + " order by etdate";
}
l_objCmd.CommandText = sql;
if (l_objRdr != null)
{
l_objRdr.Close();
}
l_objRdr = l_objCmd.ExecuteReader();
List <etf_epf_record> list = new List <etf_epf_record>();
if (l_objRdr.HasRows)
{
while (l_objRdr.Read())
{
etf_epf_record file = new etf_epf_record();
file.etref = l_objRdr["etref"].ToString().Trim();
file.etreg = l_objRdr["etreg"].ToString().Trim();
file.etamt = l_objRdr["etamt"].ToString().Trim();
file.etpflg = l_objRdr["etpflg"].ToString().Trim();
file.etdate = l_objRdr["etdate"].ToString().Trim();
file.ettime = l_objRdr["ettime"].ToString().Trim();
file.etptyp = l_objRdr["etptyp"].ToString().Trim();
file.etfrpd = l_objRdr["etfrpd"].ToString().Trim(); //From Period
file.ettopd = l_objRdr["ettopd"].ToString().Trim(); //To Period
file.etnomb = l_objRdr["etnomb"].ToString().Trim(); //EMployeed
file.etadat = l_objRdr["etadat"].ToString().Trim();
file.etcopr = l_objRdr["etcopr"].ToString().Trim();
file.ettran = l_objRdr["ettran"].ToString().Trim();
list.Add(file);
}
}
return(list);
}
protected void btnOK_Click(object sender, EventArgs e)
{
//System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection ();
System.Data.Odbc.OdbcConnection conn;
System.Data.Odbc.OdbcDataAdapter ap;
System.Data.Odbc.OdbcDataReader apcheck;
System.Data.Odbc.OdbcCommand objcommand;
objcommand = new System.Data.Odbc.OdbcCommand();
DataSet ds = new DataSet();
//string connectionString = "Data Source=(local);Initial Catalog=Northwind; User ID=Admin; Password=misdyu";
string constr = "FIL=MS Access;SERVER=localhost;DSN=Pform;UID=root;PWD=;OPTION=3";
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
//採用ODBC，則禁止使用sqlcommand相關指令，會出現語法錯誤，一直抓不到bug
objcommand.Connection = conn;
objcommand.CommandText = "Select * From accountID Where Name = '" + TextBox1.Text + "'";
try
{
// Process data here.
apcheck = objcommand.ExecuteReader();
if (apcheck.HasRows)
{
Label1.Text = "帳號：該帳戶己註冊過";
apcheck.Close();
conn.Close();
}
else
{
conn.Close();
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
string sql1 = "Insert into accountID (Name, Pass, Email, Address, Gender, Idt) values ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "' , '" + TextBox3.Text + "' , '" + RadioButtonList1.Text + "', '" + DropDownList1.Text + "')";
ap = new System.Data.Odbc.OdbcDataAdapter(sql1, conn);
ap.Fill(ds);
//傳統寫法
Response.Redirect("ShowData.aspx?ID=" + TextBox1.Text + "&Password="******"&Msg=" + DropDownList1.Text);
//使用字串參數的型式,ERROR會出現兩次註冊
//Response.Redirect(string.Format("ShowData.aspx?ID={0}&Password={1}&Msg={2}", TextBox1.Text, TextBox2.Text, DropDownList1.Text));
}
}
catch (Exception ex)
{
//新版不支援下行
// MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
/// <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();
}
private void ListTables(string Database)
{
if (OdbcCon.State == ConnectionState.Open)
{
OdbcCom = new System.Data.Odbc.OdbcCommand("SELECT * FROM [mydb].[dbo].[user]", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
Console.WriteLine("Content in Table [" + Database + "].[dbo].[user] :\r\n");
while (OdbcDR.Read())
{
Console.WriteLine("Values >> " + OdbcDR[0] + " , " + OdbcDR[1] + " , " + OdbcDR[2] + " , " + OdbcDR[3] + " , " + OdbcDR[4] + "\r\n");
}
}
}
/// <summary>
/// Obtiene la información complementaria de un empleado desde la lectura del head count
/// </summary>
/// <returns></returns>
public List <ComplementoEmpleado> ObtenerInformacionComplementaria()
{
List <ComplementoEmpleado> InformacionComplementaria = new List <ComplementoEmpleado>();
try
{
string QueryString = @"SELECT id, RazonSocial, Nomina, CentroCostos FROM headcount";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
ComplementoEmpleado Employer;
string [] CentroCostos;
while (reader.Read())
{
Employer = new ComplementoEmpleado();
Employer.EmpleadoId = reader.GetInt32(0);
Employer.RazonSocial = reader.GetString(1);
Employer.Nomina = reader.GetString(2);
CentroCostos = reader.GetString(3).Split(' ');
Employer.ClaveCentroCostos = CentroCostos.Length > 0 ? CentroCostos[0] : "";
if (Employer.ClaveCentroCostos != "")
{
for (int i = 1; i < CentroCostos.Length; i++)
{
Employer.DescripcionCentroCostos += CentroCostos[i] + " ";
}
}
InformacionComplementaria.Add(Employer);
}
reader.Close();
}
connection.Close();
}
}
catch (Exception exc)
{
Log.EscribeLog("Error: EslabonDA.ObtenerInformacionComplementaria - " + exc.Message);
}
return(InformacionComplementaria);
}
internal DataTable TraerDataTable(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;
MySqlDataReader reader = comando.ExecuteReader();
DataTable Tabla = new DataTable();
Tabla.Load(reader);
return(Tabla);
}
else
{
if (TipoBase == "sql")
{
SqlCommand comando = new SqlCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = odbc;
comando.Connection = con as SqlConnection;
SqlDataReader reader = comando.ExecuteReader();
DataTable Tabla = new DataTable();
Tabla.Load(reader);
return(Tabla);
}
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;
System.Data.Odbc.OdbcDataReader reader = comando.ExecuteReader();
DataTable Tabla = new DataTable();
Tabla.Load(reader);
return(Tabla);
}
}
}
public void Connect()
{
string Conn = @"DRIVER={AspenTech SQLplus};HOST=" + IP21_Host + @";PORT=" + IP21_Port;
Cmdr = new System.Data.Odbc.OdbcCommand();
Cmdr.Connection = new System.Data.Odbc.OdbcConnection(Conn);
Cmdr.CommandTimeout = 15;
Cmdw = new System.Data.Odbc.OdbcCommand();
Cmdw.Connection = new System.Data.Odbc.OdbcConnection(Conn);
Cmdw.CommandTimeout = 15;
try
{
Cmdr.Connection.Open();
Cmdw.Connection.Open();
}
catch (System.Data.Odbc.OdbcException e)
{
Console.WriteLine("Connection failed {0}", e.Message);
}
}
public DataTable Execute(string Command)
{
DataTable dt = null;
if (Conn != null)
{
try
{
Conn.Open();
dt = new DataTable();
System.Data.Odbc.OdbcCommand oCmd = Conn.CreateCommand();
oCmd.CommandText = Command;
dt.Load(oCmd.ExecuteReader());
Conn.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
return(dt);
}
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);
}
