/// <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>
/// 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);
}
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 MyConnectionOdbc(DataProvider dataProvider)
: base(dataProvider)
{
// Crear conexión
OdbcConnection conn = new OdbcConnection(@"provider = sqlodbc;
data source = .\sqlexpress; trusted connection = yes;");
try
{
// Abrir conexión
conn.Open();
Console.WriteLine("Conexión establecida.");
// Detalles de la conexión
this.DetallesConexion(conn);
}
catch (OdbcException ex)
{
// Desplegar excepción o error
Console.WriteLine("Error: " + ex.Message + ex.StackTrace);
}
finally
{
//Cerrar la conexión
conn.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);
}
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();
}
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;
}
}
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;
}
private void SAP_EditObj1251(System.Collections.Specialized.NameValueCollection P, HttpResponse httpResponse)
{
System.Data.Odbc.OdbcConnection conn = HELPERS.NewOdbcConn_FORCE();
IAuthRow1251 engine1251 = new IAuthRow1251(conn);
int IDobjtoedit = int.Parse(P["IDentit"]);
returnGetAuthRow1251 curobj =
engine1251.GetAuthRow1251(IDobjtoedit);
if (
(curobj.RangeLow == P["c_u_RangeLow"])
&&
(curobj.RangeHigh == P["c_u_RangeHigh"])
)
{
httpResponse.Write("No change was noted, so no action was performed.");
return;
}
engine1251.SetAuthRow1251
(IDobjtoedit, P["c_u_RangeLow"], P["c_u_RangeHigh"], curobj.SAPauthObjID, curobj.SAPauthFieldID, curobj.TcodeAssignmentSetID, curobj.SAProleID,
curobj.EditStatus | 8 /*modified*/);
}
private void SAP_ToggleDeleteStatusOf1251row(System.Collections.Specialized.NameValueCollection P, HttpResponse httpResponse)
{
System.Data.Odbc.OdbcConnection conn = HELPERS.NewOdbcConn_FORCE();
IAuthRow1251 engine1251 = new IAuthRow1251(conn);
int IDobjtoedit = int.Parse(P["IDentit"]);
returnGetAuthRow1251 curobj =
engine1251.GetAuthRow1251(IDobjtoedit);
int newEditStatus;
if (0 != (curobj.EditStatus & 4))
{
newEditStatus = curobj.EditStatus & (8 + 2 + 1);
}
else
{
newEditStatus = curobj.EditStatus + 4;
}
engine1251.SetAuthRow1251
(IDobjtoedit, curobj.RangeLow, curobj.RangeHigh, curobj.SAPauthObjID, curobj.SAPauthFieldID, curobj.TcodeAssignmentSetID, curobj.SAProleID,
newEditStatus);
}
private void Connect(string Database)
{
string Server = "MyPC\\SQLEXPRESS";
string Username = "******";
string Password = "";
ConStr = "Driver={SQL Server};";
ConStr += "Server=" + Server + ";";
ConStr += "User ID=" + Username + ";";
ConStr += "Password="******";";
ConStr += "Initial Catalog=" + Database + ";";
OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);
try
{
Console.WriteLine("Openning connection...\r\n");
if (OdbcCon.State == ConnectionState.Closed)
{
OdbcCon.Open();
}
Console.WriteLine("ODBC Connection State = " + OdbcCon.State + "\r\n");
}
catch (System.Data.Odbc.OdbcException Ex)
{
Console.WriteLine(Ex.Message + "\r\n");
Console.WriteLine("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + 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);
}
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();
}
}
}
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
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string ConnectionString = "";
// ConnectionString =
//@"DRIVER=MapR Drill ODBC Driver;
//AdvancedProperties={CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=local;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5};
//Catalog=DRILL;
//Schema=;
//AuthenticationType=No Authentication;
//ConnectionType=Direct;
//Host=192.168.2.113;
//Port=31010;
//";
// ConnectionString = "DRIVER=MapR Drill ODBC Driver;Catalog=DRILL;Schema=hbase;ConnectionType=Direct;Host=192.168.2.113;Port=31010";
ConnectionString = "DRIVER =MapR Drill ODBC Driver; AdvancedProperties ={ CastAnyToVarchar = true; HandshakeTimeout = 5; QueryTimeout = 180; TimestampTZDisplayTimezone = local; ExcludedSchemas = sys,INFORMATION_SCHEMA; NumberOfPrefetchBuffers = 5}; Catalog = DRILL; Schema =; AuthenticationType = No Authentication; ConnectionType = ZooKeeper; ZKQuorum = dataNode04:2181,dataNode03: 2181,dataNode02: 2181,nameNode: 2181; ZKClusterID = drillbits1; ";
System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(ConnectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM `hbase`.`tab` ";
System.Data.Odbc.OdbcDataAdapter ad = new System.Data.Odbc.OdbcDataAdapter(cmd);
DataSet myds = new DataSet();
ad.Fill(myds);
}
internal OdbcTransaction(OdbcConnection connection, IsolationLevel isolevel, OdbcConnectionHandle handle) {
OdbcConnection.VerifyExecutePermission();
_connection = connection;
_isolevel = isolevel;
_handle = handle;
}
public string[] GetMostPopularTags()
{
string[] result = new string[25];
//SELECT *, count(*) as ct FROM `3dr`.`associatedkeywords` inner join (select * from 3dr.keywords) as r on associatedkeywords.keywordid = r.id group by keyword order by ct desc limit 25
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "{SELECT *, count(*) as ct FROM `3dr`.`associatedkeywords` inner join (select * from 3dr.keywords) as r on associatedkeywords.keywordid = r.id group by keyword order by ct desc limit 25}";
using (var resultSet = command.ExecuteReader())
{
int i = 0;
while (resultSet.Read())
{
result[i] = resultSet["keyword"].ToString() + "." + resultSet["ct"].ToString();
i++;
}
}
}
return(result);
}
}
/// <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 string[] GetMostPopularDevelopers()
{
string[] result = new string[15];
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "{Select sponsorname, count(sponsorname) as ct from contentobjects where sponsorname != '' group by sponsorname union Select developername, count(developername) as ct from contentobjects where developername != '' group by developername union Select artistname, count(artistname) as ct from contentobjects where artistname != '' group by artistname order by ct desc limit 15}";
using (var resultSet = command.ExecuteReader())
{
int i = 0;
while (resultSet.Read())
{
result[i] = resultSet["sponsorname"].ToString();
result[i] += " (" + resultSet["ct"].ToString() + ")";
i++;
}
}
}
return(result);
}
}
/// <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);
}
static internal List <SqlSchemaInfo> GetSchemaInfo(System.Data.Odbc.OdbcConnection con, List <SqlSchemaInfo> schemaList)
{
try
{
DataTable tbl = con.GetSchema(System.Data.Odbc.OdbcMetaDataCollectionNames.Tables);
foreach (DataRow row in tbl.Rows)
{
SqlSchemaInfo ssi = new SqlSchemaInfo();
ssi.Type = "TABLE";
ssi.Name = (string)row["table_name"];
schemaList.Add(ssi);
}
tbl = con.GetSchema(System.Data.Odbc.OdbcMetaDataCollectionNames.Views);
foreach (DataRow row in tbl.Rows)
{
SqlSchemaInfo ssi = new SqlSchemaInfo();
ssi.Type = "VIEW";
ssi.Name = (string)row["table_name"];
schemaList.Add(ssi);
}
}
catch
{
}
return(schemaList);
}
public static DbConnection GetOpenConnection(AdoProviderType providerType, string connStr, int retryCount, int retryDelay)
{
DbConnection conn = null;
switch (providerType)
{
case AdoProviderType.SqlProvider:
conn = new SqlConnection(connStr);
break;
case AdoProviderType.OleProvider:
conn = new OleDbConnection(connStr);
break;
case AdoProviderType.OdbcProvider:
conn = new OdbcConnection(connStr);
break;
default:
throw new Exception("This provider type is not supported.");
}
if (conn != null)
{
conn.Open();
int attemptCount = 0;
while (conn.State!=ConnectionState.Open && ++attemptCount < retryCount)
{
System.Threading.Thread.Sleep(retryDelay);
conn.Open();
}
if (conn.State != ConnectionState.Open)
throw new Exception("Unable to open connection using specified connection string.");
}
return conn;
}
/// <summary>
/// Llena una tabla SQL con los resultados de una consulta ODBC (Fox o relativity)
/// </summary>
/// <param name="keyOdbc">Clave que identifica la cadena de conexión ODBC</param>
/// <param name="sentenciaOdbc">Sentencia odbc que realiza la consulta</param>
/// <param name="keySql">Clave que identifica la cadena de conexión SQL</param>
/// <param name="TablaDestino">Tabla que se llenará con la consulta</param>
/// <returns>Resultado del proceso </returns>
public string sqlInsert(string keyOdbc, string sentenciaOdbc, string keySql, string TablaDestino)
{
string strConnString = ConfigurationManager.ConnectionStrings[keyOdbc].ConnectionString;
OdbcConnection OdbcCon = new System.Data.Odbc.OdbcConnection();
OdbcCon.ConnectionString = strConnString;
OdbcCon.Open();
OdbcCommand commandSourceData = new OdbcCommand(sentenciaOdbc, OdbcCon);
OdbcDataReader reader = commandSourceData.ExecuteReader();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConectaSql(keySql)))
{
bulkCopy.DestinationTableName = TablaDestino;
try
{
bulkCopy.WriteToServer(reader);
return("OK");
}
catch (Exception ex)
{
return("NO" + ex.Message);
}
finally
{
reader.Close();
}
}
}
/// <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;
}
/// <summary>
/// Realiza una consulta de tipo ODBC mediante sentencia Sql estandar.
/// </summary>
/// <param name="NomTabla">Nombre de la tabla que almacena el resultado de la consulta.</param>
/// <param name="sentenciaSql">cadena con la sentencia sql que realiza la consulta</param>
/// <param name="key">Nombre que identifica el string de conexión</param>
/// <returns>Retorna un dataset con los resultados de la consulta</returns>
public DataSet ConsultaOdbc(string NomTabla, string sentenciaSql, string key)
{
DataSet dsBasico = new DataSet();
string strConnString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
OdbcConnection OdbcCon = new System.Data.Odbc.OdbcConnection();
OdbcCon.ConnectionString = strConnString;
OdbcCon.Open();
OdbcDataAdapter DataAdapterOdbc = new OdbcDataAdapter();
try
{
DataAdapterOdbc = new OdbcDataAdapter(sentenciaSql, OdbcCon);
DataAdapterOdbc.Fill(dsBasico, NomTabla);
return(dsBasico);
}
catch (Exception r)
{
string m = r.Message;
return(dsBasico);
}
finally
{
DataAdapterOdbc.Dispose();
OdbcCon.Close();
}
}
private static void SetupSqlServerOdbc(Cfg.Configuration cfg)
{
var connStr = cfg.Properties[Cfg.Environment.ConnectionString];
using (var conn = new OdbcConnection(connStr.Replace("Database=nhibernateOdbc", "Database=master")))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "drop database nhibernateOdbc";
try
{
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e);
}
cmd.CommandText = "create database nhibernateOdbc";
cmd.ExecuteNonQuery();
}
}
}
private void btnOpenFile_Click(object sender, RoutedEventArgs e)
{
try
{
OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection();
string fileName = "DUTY.DBF";
//OpenFileDialog openFileDialog = new OpenFileDialog();
//if (openFileDialog.ShowDialog() == true)
//{
//lblStatus.Content = "File conversion is in progress.. Please wait..!!";
obdcconn.ConnectionString = GetConnectionString(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
obdcconn.Open();
OdbcCommand oCmd = obdcconn.CreateCommand();
oCmd.CommandText = "SELECT * FROM " + System.Configuration.ConfigurationSettings.AppSettings["InputPath"];
/*Load data to table*/
DataTable dt1 = new DataTable();
dt1.Load(oCmd.ExecuteReader());
string currentPath = Path.GetFullPath(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
currentPath = Directory.GetParent(currentPath).FullName + "\\";
obdcconn.Close();
WriteDataToTemplate(currentPath, fileName, dt1);
//DataSetIntoDBF(fileName, currentPath, dt1);
//lblStatus.Content = "File conversion completed, file is in " + dir + "\\" + fileName;
//}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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;
}
}
public IEnumerable <ContentObject> GetContentObjectsByField(string field, string value, string identity)
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "{CALL GetContentObjectsByField(?,?,?)}";
command.Parameters.AddWithValue("field", field);
command.Parameters.AddWithValue("val", value);
command.Parameters.AddWithValue("uname", identity);
using (var resultSet = command.ExecuteReader())
{
while (resultSet.Read())
{
var co = new ContentObject();
FillContentObjectLightLoad(co, resultSet);
objects.Add(co);
}
}
}
return(objects);
}
}
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>
///
/// </summary>
/// <param name="query"></param>
/// <param name="count"></param>
/// <param name="start"></param>
/// <returns></returns>
public IEnumerable <ContentObject> GetObjectsWithRange(string query, int count, int start, SortOrder order, string username)
{
List <ContentObject> objects = new List <ContentObject>();
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
using (var command = conn.CreateCommand())
{
command.CommandText = query;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("s", start);
command.Parameters.AddWithValue("length", count);
command.Parameters.AddWithValue("sortOrder", (order == SortOrder.Descending) ? "DESC" : "ASC");
command.Parameters.AddWithValue("uname", username);
using (var resultSet = command.ExecuteReader())
{
while (resultSet.Read())
{
var co = new ContentObject();
FillContentObjectLightLoad(co, resultSet);
LoadReviews(co, conn);
objects.Add(co);
}
}
}
if (_TotalObjects < 0)
{
setContentObjectCount(conn, username);
}
}
return(objects);
}
public static OdbcConnection CreatConnection()
{
string driver = @"SQL Server";
string servidor = @"EMYLLAINE";
string baseDeDados = @"Itens";
//string usuario = "sa";
//string senha = "123";
StringBuilder cnn = new StringBuilder();
cnn.Append("driver=");
cnn.Append(driver);
cnn.Append(";server=");
cnn.Append(servidor);
cnn.Append(";database=");
cnn.Append(baseDeDados);
cnn.Append(";Trusted_Connection=yes");
//cnn.Append(usuario);
//cnn.Append(";pwd=");
//cnn.Append(senha);
OdbcConnection odbcCon = new OdbcConnection(cnn.ToString());
odbcCon.Open();
return odbcCon;
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
/// <param name="filename"></param>
/// <returns></returns>
public bool RemoveSupportingFile(ContentObject co, string filename)
{
System.Data.Odbc.OdbcConnection connection = GetConnection();
{
using (var command = connection.CreateCommand())
{
command.CommandText = "{CALL DeleteSupportingFile(?,?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("pid", co.PID);
command.Parameters.AddWithValue("filename", filename);
var result = command.ExecuteReader();
List <SupportingFile> remove = new List <SupportingFile>();
foreach (SupportingFile t in co.SupportingFiles)
{
if (t.Filename == filename)
{
remove.Add(t);
}
}
foreach (SupportingFile t in remove)
{
if (t.Filename == filename)
{
co.SupportingFiles.Remove(t);
}
}
}
}
return(true);
}
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;
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
public void InsertContentRevision(ContentObject co)
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
int id = 0;
using (var command = conn.CreateCommand())
{
command.CommandText = "{CALL InsertContentObject(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); }";
command.CommandType = System.Data.CommandType.StoredProcedure;
var properties = co.GetType().GetProperties();
foreach (var prop in properties)
{
if (prop.PropertyType == typeof(String) && prop.GetValue(co, null) == null)
{
prop.SetValue(co, String.Empty, null);
}
}
FillCommandFromContentObject(co, command);
id = int.Parse(command.ExecuteScalar().ToString());
}
SaveKeywords(conn, co, id);
}
}
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 "";
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public IEnumerable <ContentObject> GetAllContentObjects()
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandText = "{CALL GetAllContentObjects()}";
command.CommandType = System.Data.CommandType.StoredProcedure;
using (var resultSet = command.ExecuteReader())
{
while (resultSet.Read())
{
var co = new ContentObject();
FillContentObjectFromResultSet(co, resultSet);
LoadReviews(co, conn);
co.Keywords = LoadKeywords(conn, co.PID);
objects.Add(co);
}
}
}
return(objects);
}
}
public static OdbcConnection ObtenerConexion()
{
mySqlConeccion = new OdbcConnection("Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=colchoneria;User=root;Password=; Option=3;");
mySqlConeccion.Open();
return mySqlConeccion;
}
/// <summary>
/// Initialises a new instance of the OdbcCommand class
/// </summary>
/// <param name="commandText">T-SQL statement or name of the command being executed</param>
/// <param name="connection">Open connection to the database</param>
public OdbcCommand(String commandText, odbc.OdbcConnection connection)
: base(commandText)
{
this.commandType = CommandType.Text;
Connection = connection;
}
// Check esistenza account
public bool GetAccountExists(String username, out String error)
{
bool exists = false;
error = "";
try
{
// Connesione db
OdbcConnection odbc = new OdbcConnection("DSN=nwn_master_server");
odbc.Open();
using (OdbcCommand dbcmd = odbc.CreateCommand()) {
// Query
String query = "SELECT username FROM " + TABLE_ACCOUNTS + " WHERE username='******'";
dbcmd.CommandText = query;
using (OdbcDataReader dbreader = dbcmd.ExecuteReader()) {
while(dbreader.Read()){
if ((String)dbreader["username"] == username)
exists = true;
}
dbreader.Close();
}
}
odbc.Close();
}
catch (Exception e) {
error = e.StackTrace;
}
// Exit
return exists;
}
/// <summary>
/// connect to the database
/// </summary>
public void ConnectToDatabase()
{
if (connectString == "")
{
throw new Exception("Connection string cannot be empty.");
}
else
{
try
{
if (dbConnection == null)
{
dbConnection = new OdbcConnection(connectString);
dbConnection.Open();
return;
}
if (dbConnection.State.ToString() == "Closed")
{
dbConnection.Open();
}
}
catch (Exception ex)
{
throw new Exception("Database Connect Errors: " + ex.Message);
}
}
}
/// <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);
}
protected void TestMySQLSettings_Click(object sender, EventArgs e)
{
if (MySQLPassword2.Text != MySQLPassword1.Text)
{
BlankStatus();
testMySQLStatus.Text = "Passwords Must Match";
return;
}
string ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=" + MySQLIP.Text + ";Port=" + MySQLPort.Text + ";Database=3dr;User="******";Password="******";Option=3";
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
try
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
BlankStatus();
SaveMySQLSettings.Enabled = true;
testMySQLStatus.Text = "Test Successful";
}
}
catch (Exception ex)
{
BlankStatus();
SaveMySQLSettings.Enabled = false;
testMySQLStatus.Text = "Test Failed: " + ex.Message;
}
}
Updatecheckmarks();
}
/// <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);
}
/// <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();
}
}
}
/// <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>
/// Executa uma query de busca com retorno de dados
/// </summary>
/// <param name="sql">comando sql para aquela ação</param>
/// <returns>Um dataset contendo os dados buscados</returns>
public DataSet BuscaDados(string sql)
{
//Instancia um objeto de dataset para retornar
DataSet dados = new DataSet();
using(OdbcConnection conn = new OdbcConnection(this.ConnectionString))
{
try
{
//Abre a conexão com o servidor de banco
conn.Open();
//Cria um objeto de reader do odbc, para retornar dados
OdbcDataAdapter read = new OdbcDataAdapter(sql, conn);
//Preenche o DataSet instanciado no incio do codigo
read.Fill(dados);
}
catch (Exception ex)
{
//Lança um erro, caso aconteça
throw new Exception(ex.Message);
}
finally
{
//Fecha conexão
conn.Close();
}
}
return dados;
}
public Database(string connectionString)
{
OC = new OdbcConnection(connectionString);
dbCommand = new OdbcCommand();
dbCommand.Connection = OC;
OC.Open();
}
public static DataTable ExecuteQuery(string dsnConnection, string query, params object[] queryParams)
{
try
{
using (var connection = new OdbcConnection(dsnConnection))
{
connection.Open();
var queryCommand = connection.CreateCommand();
queryCommand.CommandText = query;
queryCommand.CommandTimeout = 1;
var i = 0;
foreach (var queryParam in queryParams)
{
queryCommand.Parameters.Add("@p" + i, OdbcType.DateTime).Value = queryParam;
i++;
}
var dataSet = new DataSet();
var da = new OdbcDataAdapter(queryCommand);
da.Fill(dataSet);
return dataSet.Tables[0];
}
} catch(Exception ex)
{
Logging.LogError(1, "Error executing query {0} on connection {1} message is {2}", query, dsnConnection, ex.Message);
throw;
}
}
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;
}
public static void Load_Sport_TXT_TO_DB_ByTableNameAndFieldName(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,Encoding.Default);
string line = null;
while ((line = sr.ReadLine()) != null)
{
string[] str_a = line.Split(';');
String[] fieldnames = field_names.Split(',');
if (str_a.Length == fieldnames.Length)
{
Lib.inc_cmd(field_names, table_name, str_a, conn);
}
}
conn.Close();
}
}
}
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;
}
/* queries for all students (IDs, First names, last names)
* whose ID numbers contain the values provided by the student W# field
* in the current form. */
private void button1_Click(object sender, System.EventArgs e)
{
//Refresh the listview.
searchView.Items.Clear();
string SearchTerm = searchField.Text;
System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(dsnSource);
try
{
connection.Open();
System.Data.Odbc.OdbcCommand command = connection.CreateCommand();
command.CommandText = "SELECT ID, FIRST_NAME, LAST_NAME FROM G_PERSON WHERE ID like '%" + SearchTerm + "%'";
System.Data.Odbc.OdbcDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
String id = Reader["ID"].ToString();
String fname = Reader["FIRST_NAME"].ToString();
String lname = Reader["LAST_NAME"].ToString();
ListViewItem item = new ListViewItem(new[] { id, fname, lname });
searchView.Items.Add(item);
}
connection.Close();
} catch (Exception s) {
MessageBox.Show("Could not connect at this time. Please try again later.");
MessageBox.Show(s.ToString());
}
}
public HandleSystem(OdbcConnection dbConn, StreamReader reader, StreamWriter writer)
{
this.dbConn = dbConn;
this.reader = reader;
this.writer = writer;
BeginHandling();
}
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;
}
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;
}
// CSVファイルをDataTableに取得する
private DataTable getDtFromCsv(string csvDir, string csvFileName)
{
DataTable dt = null;
//接続文字列
string conString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvDir + ";Extensions=asc,csv,tab,txt;";
OdbcConnection con = new System.Data.Odbc.OdbcConnection(conString);
string commText = "SELECT * FROM [" + csvFileName + "]";
using (OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(commText, con))
{
//DataTableに格納する
using (dt = new DataTable())
{
da.Fill(dt);
}
}
if (dt == null)
{
throw new Exception("CSVのテーブル展開に失敗しました。");
}
return(dt);
}