//luodaan uusi bisnes public BLbusiness(string name, string katuosoite, string postinro, string kaupunki, OdbcConnection conn) { //business address this.address = new BLaddress(conn, katuosoite, postinro, kaupunki); /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO business (BusinessName, Address_AddressId) VALUES (?, ?)"; insertcmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name; insertcmd.Parameters.Add("@Address_AddressId", OdbcType.Int).Value = this.address.id; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT BusinessId, BusinessName FROM business WHERE BusinessName = ?"; cmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; this.name = rows.GetString(1); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select same in all BL files*/ }
public ContentResult Create(string json) { OdbcConnection hiveConnection = new OdbcConnection("DSN=Hadoop Server;UID=hadoop;PWD=hadoop"); hiveConnection.Open(); Stream req = Request.InputStream; req.Seek(0, SeekOrigin.Begin); string request = new StreamReader(req).ReadToEnd(); ContentResult response; string query; try { query = "INSERT INTO TABLE error_log (json_error_log) VALUES('" + request + "')"; OdbcCommand command = new OdbcCommand(query, hiveConnection); command.ExecuteNonQuery(); command.CommandText = query; response = new ContentResult { Content = "{status: 1}", ContentType = "application/json" }; hiveConnection.Close(); return response; } catch(WebException error) { response = new ContentResult { Content = "{status: 0, message:" + error.Message.ToString()+ "}" }; System.Diagnostics.Debug.WriteLine(error.ToString()); hiveConnection.Close(); return response; } }
void abrirdbf(string tabla) { strRutaDbf = System.Configuration.ConfigurationSettings.AppSettings["PathDbfs"]; strSelect = "SELECT * FROM " + tabla + ";"; strConexion = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + strRutaDbf + ";"; OdbcConnection dbConexionDbf = new System.Data.Odbc.OdbcConnection(strConexion); try { dbConexionDbf.Open(); OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(strSelect, dbConexionDbf); da.Fill(dsDocumentos, tabla); dbConexionDbf.Close(); } catch (Exception ex) { MessageBox.Show("Error al abrir la base de datos\n" + ex.Message); return; } finally { if (dbConexionDbf != null && oleConexion.State != ConnectionState.Closed) { dbConexionDbf.Close(); } } }
//constructor public BLfeedback(int customerid, int servicepackageid, OdbcConnection conn) { /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO feedback (Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId) VALUES (?,?,?)"; insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid; this.timestamp = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); insertcmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp; insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT FeedbackId, Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId FROM feedback WHERE Customer_CustomerId = ? AND Timestamp = ? AND ServicePackage_ServicePackageId = ?"; cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid; cmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp; cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; int id2; int.TryParse(rows.GetString(1), out id2); this.customerid = id2; this.timestamp = rows.GetString(2); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select same in all BL files*/ }
//constructor public BLcustomer(string name, string phonenumber, string email, OdbcConnection conn) { /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO customer (CustomerName, PhoneNro, Email) VALUES (?, ?, ?)"; insertcmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name; insertcmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber; insertcmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT CustomerId, CustomerName, PhoneNro, Email FROM customer WHERE CustomerName = ? AND PhoneNro = ? AND Email = ?"; cmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name; cmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber; cmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; this.name = rows.GetString(1); this.phonenumber = rows.GetString(2); this.email = rows.GetString(3); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select same in all BL files*/ }
//constructor public BLcomment(string name, int feedbackid, OdbcConnection conn) { /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO comment (Text, Feedback_FeedbackId) VALUES (?, ?)"; insertcmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name; insertcmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT CommentId, Text, Feedback_FeedbackId FROM comment WHERE Text = ? AND Feedback_FeedbackId = ?"; cmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name; cmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; this.name = rows.GetString(1); int id2; int.TryParse(rows.GetString(2), out id2); this.feedbackid = id2; } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select same in all BL files*/ }
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(); } }
//constructor public BLaddress(OdbcConnection conn, string katuosoite, string postinro, string kaupunki) { //constructing attributes this.city = new BLcity(conn, kaupunki); this.zipcode = new BLzipcode(conn, postinro); /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO address (City_CityId, ZIPCode_ZIPCodeId, StreetAddress) VALUES (?, ?, ?)"; insertcmd.Parameters.Add("@City_CityId", OdbcType.Int).Value = this.city.id; insertcmd.Parameters.Add("@ZIPCode_ZIPCodeId", OdbcType.Int).Value = this.zipcode.id; insertcmd.Parameters.Add("@StreetAddress", OdbcType.VarChar, 50).Value = katuosoite; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT AddressId, StreetAddress FROM address WHERE StreetAddress = ?"; cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 50).Value = katuosoite; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; this.name = rows.GetString(1); } catch (Exception ex) { this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select same in all BL files*/ }
protected void btnLoginOK_Click(object sender, EventArgs e) { string connection = ConfigurationSettings.AppSettings["connectionString"]; OdbcConnection dbCon = new OdbcConnection(connection); string sql = "SELECT seq FROM profile WHERE (MEMBER_NAME = @userid) AND (MEMBER_PWD = @pwd)"; OdbcCommand dbCmd = new OdbcCommand(sql, dbCon); dbCmd.Parameters.Add("@userid", OdbcType.VarChar, 50, "MEMBER_NAME"); dbCmd.Parameters.Add("@pwd", OdbcType.VarChar, 30, "MEMBER_PWD"); dbCon.Open(); dbCmd.Parameters["@userid"].Value = txtName.Text; dbCmd.Parameters["@pwd"].Value = txtPwd.Text; if (dbCmd.ExecuteScalar() == null) { ClientScriptManager cs = Page.ClientScript; cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./DefaultLogin.aspx');", true); cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('Incorrect ID/PW');", true); dbCmd.ExecuteNonQuery(); dbCon.Close(); } else { dbCmd.CommandText = "select * from profile where (MEMBER_NAME = @userid)"; OdbcDataReader reader = dbCmd.ExecuteReader(); reader.Read(); //add session Session.Add("USER_ID", txtName.Text); Session.Add("USER_NAME", reader["MEMBER_NAME"].ToString()); Session.Add("USER_PWD", reader["MEMBER_PWD"].ToString()); reader.Close(); dbCmd.ExecuteNonQuery(); dbCon.Close(); Response.Redirect("./Board_list.aspx"); //if (Session["USER_ID"] != null) //{ // namelbl2.Text = Session["USER_ID"].ToString() + "Welcom."; // //hide login // LoginForm.Visible = false; // //dis login // loginchk.Visible = true; //} } }
public BLservicepackage(string name, int service, OdbcConnection conn) { /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO servicepackage (ServicePackageName, Service_ServiceId) VALUES (?, ?)"; insertcmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name; insertcmd.Parameters.Add("@Service_ServiceId", OdbcType.Int).Value = service; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT ServicePackageId, ServicePackageName FROM servicepackage WHERE ServicePackageName = ?"; cmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; this.name = rows.GetString(1); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select same in all BL files*/ }
//constructor public BLzipcode(OdbcConnection conn, string postinro) { /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO zipcode (ZIPCodeNumber) VALUES (?)"; insertcmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT ZIPCodeId, ZIPCodeNumber FROM zipcode WHERE ZIPCodeNumber = ?"; cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; this.name = rows.GetString(1); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select kaikissa osoite BL:ssä sama*/ }
//constructor public BLcity(OdbcConnection conn, string kaupunki) { /*start copy pasta insert/select same in all BL files*/ try { //parameterized queries conn.Open(); OdbcCommand insertcmd = conn.CreateCommand(); insertcmd.CommandText = "INSERT IGNORE INTO city (CityName) VALUES (?)"; insertcmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki; insertcmd.ExecuteNonQuery(); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } try { //parameterized queries conn.Open(); OdbcCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT CityId, CityName FROM city WHERE CityName = ?"; cmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki; OdbcDataReader rows = cmd.ExecuteReader(); rows.Read(); int id; int.TryParse(rows.GetString(0), out id); this.id = id; this.name = rows.GetString(1); } catch (Exception ex) { this.id = -1; this.name = ex.ToString(); conn.Close(); return; } finally { conn.Close(); } /*end copy pasta insert/select same in all BL files*/ }
public static IEnumerable <String> sqlReadQuery(string vOdbcName, string queryString = "") { var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vOdbcName); using (OdbcConnection conn = new System.Data.Odbc.OdbcConnection()) { using (OdbcCommand cmnd = new OdbcCommand(queryString, conn)) { try { vCurrODBC.DSNStr = "DSN=" + vOdbcName; if (vCurrODBC.Login != null) { vCurrODBC.DSNStr = vCurrODBC.DSNStr + ";Uid=" + vCurrODBC.Login + ";Pwd=" + vCurrODBC.Password + ";"; } conn.ConnectionString = vCurrODBC.DSNStr; conn.ConnectionTimeout = 5; conn.Open(); } catch (Exception e) { In2SqlSvcTool.ExpHandler(e, "In2SqlSvcODBC.ReadData", queryString); conn.Close(); conn.Dispose(); yield break; } OdbcDataReader rd = cmnd.ExecuteReader(); string strRow = ""; while (rd.Read()) { strRow = ""; for (int i = 0; i < rd.FieldCount; i++) { strRow = strRow + '"' + rd.GetString(i) + '"'; if (i < rd.FieldCount - 1) { strRow += ","; } } yield return(strRow); conn.Close(); conn.Dispose(); } } } }
private void Disconnect() { if (OdbcCon.State == ConnectionState.Open) { OdbcCon.Close(); } }
/// <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); }
protected void Page_Load(object sender, EventArgs e) { string cate = Request.QueryString["category"]; int artID = System.Convert.ToInt32(Request.QueryString["articleID"]); string MyConString = ConfigurationSettings.AppSettings["connectionString"]; OdbcConnection connection = new OdbcConnection(MyConString); // build our query statement string sqlText = "select * FROM articles"; OdbcCommand command = new OdbcCommand(sqlText, connection); // open the database and get a datareader connection.Open(); OdbcDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection); connection.Close(); string selectSQL = "SELECT * FROM articles WHERE articleID = '" + artID + "'"; OdbcCommand cmd1 = new OdbcCommand(selectSQL, connection); OdbcDataAdapter adapter = new OdbcDataAdapter(cmd1); DataSet ds = new DataSet(); adapter.Fill(ds); DataList10.DataSource = ds; DataList10.DataBind(); }
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(); } }
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) { Button2.Enabled = false; if (Session["auth"] == null) { Response.Redirect("Homepage.aspx"); } ListItem newItem = new ListItem(); string constr = Session["connection"].ToString(); OdbcConnection cn = new OdbcConnection(constr); cn.Open(); string sql = "select pay_no from PAYMENT"; OdbcCommand cmd = new OdbcCommand(sql, cn); OdbcDataReader reader; reader = cmd.ExecuteReader(); while (reader.Read()) { newItem = new ListItem(); newItem.Text = reader["pay_no"].ToString(); DropDownList1.Items.Add(newItem); } reader.Close(); cn.Close(); }
public void load_list() { try { string workerName = "admin"; //Session["USER_ID"].ToString(); string connection = ConfigurationSettings.AppSettings["ConnectionString"]; OdbcConnection dbCon = new OdbcConnection(connection); dbCon.Open(); string sql = "select count(*) from Board"; OdbcCommand cmd = new OdbcCommand(sql, dbCon); total = int.Parse(cmd.ExecuteScalar().ToString()); cmd.CommandText = "select * from Board order by headnum DESC, depth ASC"; OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd); DataSet ds = new DataSet(); dbAdap.Fill(ds); dbAdap.Update(ds); GridView1.DataSource = ds; GridView1.DataBind(); dbCon.Close(); } catch { } }
public static string constr = "DSN=ground_tariff"; //@"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=d:\documents and settings\axkhan2\desktop\437proj\GHEC Rates NL.xls;DefaultDir=d:\documents and settings\axkhan2\desktop\437proj\;"; static void Main() { OdbcConnection conxls = new OdbcConnection(constr); try { conxls.Open(); OdbcDataAdapter da = new OdbcDataAdapter("select * from [Sheet1$]", conxls); DataSet set = new DataSet(); da.Fill(set, "mytariff"); DataTable table = set.Tables[0]; DataView dv = new DataView(table); // Console.WriteLine(dv[0]); //InsertData(rdxls); } catch(OdbcException od) { Console.WriteLine(od.Message); } finally { conxls.Close(); } }
public DataSet OpenCsv(string strFilePath) { if (!File.Exists(strFilePath)) { return null; } string strFolderPath = Path.GetDirectoryName(strFilePath); string strCSVFile = Path.GetFileName(strFilePath); DataSet ds = null; string strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strFolderPath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False"; try { using (OdbcConnection conn = new OdbcConnection(strConnection.Trim())) { conn.Open(); string strSql = "select * from [" + strCSVFile + "]"; OdbcDataAdapter odbcDAdapter = new OdbcDataAdapter(strSql, conn); ds = new DataSet(); odbcDAdapter.Fill(ds, "table"); //ds.Tables[0].Rows[0]["zh"] = "12345678901234567890"; conn.Close(); } return ds; } catch (Exception e) { throw e; } return ds; }
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(); } } }
/// <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(); } } }
protected void Page_Load(object sender, EventArgs e) { // Cria lista para o objeto List<agenda> msg = new List<agenda>(); // Adiciona dados ao objeto try { using (OdbcConnection connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MySQLServerConnStr"].ConnectionString)) { connection.Open(); using (OdbcCommand command = new OdbcCommand("SELECT * FROM agenda", connection)) using (OdbcDataReader dr = command.ExecuteReader()) { while (dr.Read()) { msg.Add(new agenda(Int32.Parse(dr["id"].ToString()), dr["nome"].ToString(), dr["telefone"].ToString())); } dr.Close(); } connection.Close(); } } catch (Exception ex) { Response.Write("An error occured: " + ex.Message); } // Adiciona objeto a sessão Session["registros"] = msg; // Cria pagina no método HTTP GET if (Request.HttpMethod.Equals("GET")) doGet(); }
/* 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()); } }
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); } }
public bool existDoc(string sSIIDOC, string sSIINUM) { bool bResp = false; OdbcConnection conn = new OdbcConnection(); conn.ConnectionString = this.sConnection; try { conn.Open(); string sQuery = "SELECT 1 FROM " + sTable + "WHERE SIIDOC=@val1 WHERE SIINUM=@val2"; using (OdbcCommand cmd = new OdbcCommand(sQuery, conn)) { cmd.Parameters.Add("@val1", OdbcType.VarChar).Value = sSIIDOC; cmd.Parameters.Add("@val2", OdbcType.VarChar).Value = sSIINUM; using (OdbcDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string sResp = reader.GetString(0); if (sResp.Substring(0) == "1") bResp = true; else bResp = false; } } } } catch (Exception ex) { bResp = false; } finally { conn.Close(); } return bResp; }
/// <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(); } }
public void LoadDB() { _MCDB = new DataSet(); try { // Creates and opens an ODBC connection String strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + _DBPath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;HDR=NO;"; String sql_select; OdbcConnection conn; conn = new OdbcConnection(strConnString.Trim()); conn.Open(); //Creates the select command text sql_select = "select * from [" + this._DBFileName.Trim() + "]"; //Creates the data adapter OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn); //Fills dataset with the records from CSV file obj_oledb_da.Fill(_MCDB, "csv"); //closes the connection conn.Close(); } catch (Exception e) //Error { Console.WriteLine("Error in: " + e.ToString()); } }
public static bool credentials_correct(string driver, string user, string password) { crypt crypt = new crypt(); string encPass = crypt.HashString(password); try { OdbcConnection con = new OdbcConnection("Driver=" + driver + ";Server=" + server + ";Database=" + dbase + ";UID=" + uid + ";PWD=" + crypt.DecryptString(pwd) + ";OPTION=3"); OdbcCommand com = new OdbcCommand("SELECT user FROM users where user = '******' and password = '******'", con); con.Open(); OdbcDataReader dr = com.ExecuteReader(); int tmpInt = 0; while (dr.Read()) { tmpInt++; } dr.Close(); con.Close(); return tmpInt > 0; } catch { return false; } }
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(); }
//public static string GetAssessmentResult(int aoid) //{ // Assessment ass = new Assessment(); // string tmp=""; // return ass.GetAssessmentResult(aoid,out tmp); //} protected void ButtonViewResult_Click(object sender, EventArgs e) { using (OdbcConnection connection = new OdbcConnection(connectionString)) { using (OdbcCommand command = new OdbcCommand()) { command.Connection = connection; command.CommandText = "{CALL ScoreDetailTable_InsertNow(?)}"; command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = 999; command.Parameters.AddWithValue("@AOID", aid); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } int n = command.ExecuteNonQuery(); } catch (Exception ex) { } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } } } }
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 List<DataImportColumn> GetTableDataColumnList(string request) { List<DataImportColumn> lstDc = new List<DataImportColumn>(); using (OdbcConnection conn = new OdbcConnection(_connectionString)) { OdbcCommand command = new OdbcCommand(request, conn); conn.Open(); OdbcDataReader dr = command.ExecuteReader(); while (dr.Read()) { lstDc.Add(new DataImportColumn() { Name = dr[0].ToString() , DataType = dr[3].ToString() , Size = dr[2].ToString() }); } dr.Close(); dr.Dispose(); command.Dispose(); conn.Close(); } return lstDc; }
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 void getCCTVDataByDeviceName(string devName, ref int cctvid, ref int preset) { OdbcConnection cn = new OdbcConnection(Global.Db2ConnectionString); OdbcCommand cmd=new OdbcCommand("select CAMERA_ID,PRESET_ID from tblCCTVPreset where devicename='"+devName+"'"); cmd.Connection = cn; OdbcDataReader rd; try { cn.Open(); rd = cmd.ExecuteReader(); if (rd.Read()) { cctvid = System.Convert.ToInt32(rd[0]); preset = System.Convert.ToInt32(rd[1]); } else { cctvid = -1; preset = -1; } } catch (Exception ex) { RemoteInterface.ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace); } finally { cn.Close(); } }
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(); } } }
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]); }
public void LoadDataFileExcel(string FileName, string SheetName) { Table = new DataTable("datastore"); string strConnString = null; strConnString = "DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);ReadOnly=1;FIL=excel 12.0;DBQ=" + FileName + ";"; string strSQL = null; strSQL = "SELECT * FROM [" + SheetName + "$]"; System.Data.Odbc.OdbcConnection cnn = new System.Data.Odbc.OdbcConnection(strConnString); cnn.Open(); try { OdbcCommand cmd = new OdbcCommand(); cmd.Connection = cnn; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = strSQL; OdbcDataReader reader = cmd.ExecuteReader(); object[] fields = new object[reader.FieldCount]; Table.Load(reader); cnn.Close(); } catch (Exception ex) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } throw ex; } }
public static IEnumerable <String> SqlReadDataValue(string vOdbcName, string queryString = "") { var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vOdbcName); using (OdbcConnection conn = new System.Data.Odbc.OdbcConnection()) { using (OdbcCommand cmnd = new OdbcCommand(queryString, conn)) { try { vCurrODBC.DSNStr = "DSN=" + vOdbcName; if (vCurrODBC.Login != null) { vCurrODBC.DSNStr = vCurrODBC.DSNStr + ";Uid=" + vCurrODBC.Login + ";Pwd=" + vCurrODBC.Password + ";"; } conn.ConnectionString = vCurrODBC.DSNStr; conn.ConnectionTimeout = 5; conn.Open(); } catch (Exception e) { In2SqlSvcTool.ExpHandler(e, "In2SqlSvcODBC.ReadData", queryString); conn.Close(); conn.Dispose(); yield break; } OdbcDataReader rd = cmnd.ExecuteReader(); while (rd.Read()) { yield return(rd["value"].ToString());//.Split(',').ToList(); ; } conn.Close(); conn.Dispose(); } } }
/// <summary> /// disconnect from database /// </summary> public void DisconnectFromDatabase() { try { if (dbConnection != null && dbConnection.State.ToString() == "Open") { dbConnection.Close(); } } catch (Exception ex) { throw new Exception("Database Disconnect Errors: " + ex.Message); } }
/// <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); }
public static void WriteToExcel(string filePath, string sheetName, string value, string columnName) { try { string conStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" + @"DBQ=" + filePath + ";ReadOnly = 0;"; // String sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + filePath + ";Extended Properties='Excel 12.0 XML;HDR=YES'"; OdbcConnection objConn = new System.Data.Odbc.OdbcConnection(conStr); objConn.Open(); // OdbcCommand objCmdSelect = new OdbcCommand($"UPDATE [{sheetName}$] SET [" + columnName + "]='" + value + "'", objConn); // objCmdSelect.ExecuteNonQuery(); objConn.Close(); } catch (Exception e) { Debug.WriteLine("System did not enter the file number in excel" + " " + e.Message); } }
public static DataTable ReadDbf(string filePath, string tableName) { string fileName = Path.GetFileName(filePath); filePath = Path.GetDirectoryName(filePath); OdbcConnection conn = null; string connectStr = "Driver={Microsoft dBASE Driver (*.dbf)}; Dbq=" + filePath; conn = new System.Data.Odbc.OdbcConnection(connectStr); string sql = "select * from " + fileName; System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(sql, conn); conn.Open(); DataTable dt = new DataTable(); da.Fill(dt); conn.Close(); return(dt); }
public static void WriteDataToTemplate(string filePath, string fileName, DataTable table) { //CopyTemplateToWorkingDir(filePath, fileName); try { OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection(GetConnectionString(dir + fileName)); OdbcCommand oCmd = null; oCmd = obdcconn.CreateCommand(); obdcconn.Open(); oCmd.CommandText = "INSERT INTO " + fileName + " VALUES ('0000',' 0000','0000','0000','0','0000',0,0,0,'')"; oCmd.ExecuteNonQuery(); oCmd = obdcconn.CreateCommand(); oCmd.CommandText = "DELETE FROM " + fileName + " WHERE BOARD <> '0000';"; var affected = oCmd.ExecuteNonQuery(); try { var count = 0; foreach (DataRow row in table.Rows) { count++; oCmd = obdcconn.CreateCommand(); string insertSql = "INSERT INTO " + fileName + " VALUES("; string values = ""; for (int i = 0; i < table.Columns.Count; i++) { string value = ReplaceEscape(row[i].ToString()); switch (i) { case 1: value = " " + value; break; case 2: value = value.PadRight(6, ' '); break; case 6: value = CalculateDay(value); values = values + value + ","; values = values + "0,"; values = values + "0,"; break; case 7: values = "'" + value + "'," + values; break; default: break; } if (i != 6 && i != 0 && i != 7) { values = values + "'" + value + "',"; } } values = values.Substring(0, values.Length - 1) + ")"; insertSql = insertSql + values; oCmd.CommandText = insertSql; oCmd.ExecuteNonQuery(); oCmd.Dispose(); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } oCmd = obdcconn.CreateCommand(); oCmd.CommandText = "DELETE FROM " + fileName + " WHERE BOARD='0000'"; oCmd.ExecuteNonQuery(); obdcconn.Close(); CopyTemplateToWorkingDir(System.Configuration.ConfigurationSettings.AppSettings["OutputPath"], fileName); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } }
public Int16 ExecuteNonQuery_DR(string sQuery, OdbcParameter[] param) { string sFuncName = "ExecuteNonQuery_DR"; Int16 iResult = 0; 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(); DbDataReader oDr; 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; oDr = oCmd.ExecuteReader(); if (oDr.Read() && oDr.GetValue(0) != DBNull.Value) { iResult = Convert.ToInt16(oDr[0]); } else { iResult = 0; } 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(iResult); }
private void LoadView() { System.Data.Odbc.OdbcConnection OdbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString); try { DataTable dt = new DataTable(); dg = item_list; StringBuilder sCSVBuilder = new StringBuilder(); System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand(); OdbcConnection.Open(); oCmd.Connection = OdbcConnection; string selectCmd; if (ConfigurationManager.AppSettings["RX_V6"] == "yes") { selectCmd = String.Format( " SELECT distinct A.ORDTIT, 0 as ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, '' as ORPKCT, B.*, 0 as OCLNX$" + " FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" + //" LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" + " WHERE A.OTINV# = {0} ", invoiceNo); } else if (ConfigurationManager.AppSettings["RX"] == "yes") { selectCmd = String.Format( " SELECT distinct A.ORDTIT, 0 as ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, '' as ORPKCT, B.*, C.OCLNX$" + " FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" + " LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" + " WHERE A.OTINV# = {0} ", invoiceNo); } else { selectCmd = String.Format( " SELECT distinct A.ORDTIT, A.ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, A.ORPKCT, B.*, C.OCLNX$" + " FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" + " LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" + " WHERE A.OTINV# = {0} ", invoiceNo); } oCmd.CommandText = selectCmd; System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd); da.Fill(dt); sCSVBuilder.Append("Invoice No, Invoice Date, Amount\n"); sCSVBuilder.Append(invoiceNo + "," + invoiceDate + "," + amount.Replace("$", "") + "\n\n"); for (int k = 0; k <= dg.Columns.Count - 1; k++) { if (ConfigurationManager.AppSettings["RX"] == "yes") { if ((k != 2) && (k != 9)) { sCSVBuilder.Append(dg.Columns[k].HeaderText + ","); } } else { if (k != 3) { sCSVBuilder.Append(dg.Columns[k].HeaderText + ","); } } } sCSVBuilder.Append("\n"); for (int i = 0; i <= dt.Rows.Count - 1; ++i) { DataRow dr = dt.Rows[i]; if (dr["ITINVT"].ToString().Trim() == "Z") { dr["ORDTIT"] = dr["ORUSIT"]; } double extPrice = 0; try { extPrice = Convert.ToDouble(dr["ORDTSQ"]) * Convert.ToDouble(dr["OTCACT"]); } catch { } dr["EXTPRICE"] = String.Format("{0:f}", extPrice); double retailPrice = 0; try { retailPrice = Convert.ToDouble(dr["ORDTSP"]) / Convert.ToDouble(dr["ORPKCT"]); } catch { } dr["RETAIL"] = String.Format("{0:f}", retailPrice); sCSVBuilder.Append(dr["ORDTIT"].ToString() + ","); sCSVBuilder.Append("\"" + dr["ITDESC"].ToString().Trim() + "\"" + ","); if (ConfigurationManager.AppSettings["RX"] == "yes") { if (dr["ITMNDC"].ToString().Trim() == "") { sCSVBuilder.Append("\"\"" + ","); } else { sCSVBuilder.Append("\"" + dr["ITMNDC"].ToString().Trim() + " *\"" + ","); } } else { if (dr["ITMUPC"].ToString().Trim() == "") { sCSVBuilder.Append("\"\"" + ","); } else { sCSVBuilder.Append("\"" + dr["ITMUPC"].ToString().Trim() + " *\"" + ","); } } sCSVBuilder.Append(dr["OTCACT"].ToString() + ","); sCSVBuilder.Append(dr["EXTPRICE"].ToString() + ","); sCSVBuilder.Append(dr["ITSIZE"].ToString() + ","); sCSVBuilder.Append(dr["ORDTQT"].ToString() + ","); sCSVBuilder.Append(dr["ORDTSQ"].ToString() + ","); if (ConfigurationManager.AppSettings["RX"] != "yes") { sCSVBuilder.Append(dr["RETAIL"].ToString() + ","); } sCSVBuilder.Append("\n"); } sCSV = sCSVBuilder.ToString(); DataView dv = new DataView(dt); dg.DataSource = dv; dg.DataBind(); } catch (Exception ex) { } finally { OdbcConnection.Close(); } }
public static void dumpOdbctoCsv(string vOdbcName, string vSqlCommand, string vCsvFile) { try { int i = 0; string DsnConn = In2SqlSvcODBC.getODBCProperties(vOdbcName, "DSNStr"); if (DsnConn == null | DsnConn == "") { MessageBox.Show("Please make the connection by expand list on the left pane ", "sql run event", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } using (OdbcConnection conn = new System.Data.Odbc.OdbcConnection()) { using (OdbcCommand cmnd = new OdbcCommand(vSqlCommand, conn)) { conn.ConnectionString = DsnConn; conn.ConnectionTimeout = 5; conn.Open(); In2SqlSvcTool.addSqlLog(vOdbcName, vSqlCommand); OdbcDataReader rd = cmnd.ExecuteReader(); object[] output = new object[rd.FieldCount]; using (var textWriter = new StreamWriter(@vCsvFile)) { var writer = new CsvWriter(textWriter, CultureInfo.InvariantCulture); writer.Configuration.Delimiter = ","; writer.Configuration.ShouldQuote = (field, context) => true; for (int j = 0; j < rd.FieldCount; j++) { output[j] = rd.GetName(j); writer.WriteField(rd.GetName(j)); } writer.NextRecord(); while (rd.Read()) { rd.GetValues(output); writer.WriteField(output); writer.NextRecord(); i++; } conn.Close(); conn.Dispose(); } } } MessageBox.Show("Export completed. \n\r File name is " + vCsvFile + " \n\r Row count:" + i, "csv export", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception e) { if (e.HResult != -2147024809) { In2SqlSvcTool.ExpHandler(e, "dumpOdbctoCsv"); } } }
/// <summary> /// Permite obtener la lista de permisos, incapacidades y vacaciones solicitadas durante el periodo que se ingresa como parámetro /// </summary> /// <param name="FechaInicio"></param> /// <param name="FechaFin"></param> /// <returns></returns> public List <DiasJustificados> ObtenerDiasJustificados(DateTime FechaInicio, DateTime FechaFin) { List <DiasJustificados> Lista = new List <DiasJustificados>(); FechaInicio = FechaInicio.AddMonths(-2); //Va y verifica si existen días de meses pasados FechaFin = FechaFin.AddDays(1); try { string QueryString = @" DECLARE @FECHA_INICIO DATETIME DECLARE @FECHA_FIN DATETIME SET @FECHA_INICIO = '" + FechaInicio.ToString("yyyyMMdd") + @"' SET @FECHA_FIN = '" + FechaFin.ToString("yyyyMMdd") + @"' SELECT A.ID, A.FECHA_DESDE, DATEDIFF(day, A.FECHA_DESDE, A.FECHA_HASTA) + 1 AS C_DIAS, A.CONCEPTO FROM ( SELECT r.id AS ID, CONVERT(DATETIME, dbo.ScalarSplit(r.referencia, ' ', 1)) AS FECHA_DESDE, CONVERT(DATETIME, dbo.ScalarSplit(r.referencia, ' ', 2)) AS FECHA_HASTA, d.descripcion AS CONCEPTO FROM recibos r INNER JOIN dips d on d.dip = r.dip WHERE r.dip IN ('4090', '7470', '4070') AND r.fecha_registro >= @FECHA_INICIO UNION SELECT c.id AS ID, CONVERT(DATETIME, dbo.ScalarSplit(c.referencia, ' ', 1)) AS FECHA_DESDE, CONVERT(DATETIME, dbo.ScalarSplit(c.referencia, ' ', 2)) AS FECHA_HASTA, d.descripcion AS CONCEPTO FROM captura c INNER JOIN dips d on d.dip = c.dip WHERE c.dip IN ('4090', '7470', '4070') AND c.fecha_registro >= @FECHA_INICIO ) AS A WHERE (A.FECHA_DESDE BETWEEN @FECHA_INICIO AND @FECHA_FIN) UNION SELECT id AS ID, fecha_incapacidad_desde AS FECHA_DESDE, dias_incapacidad AS C_DIAS, 'INCAPACIDAD' AS CONCEPTO FROM incapacidades WHERE (fecha_incapacidad_desde BETWEEN @FECHA_INICIO AND @FECHA_FIN) UNION SELECT id AS ID, fecha_inicio AS FECHA_DESDE, dias_descanso AS C_DIAS, 'VACACIONES' AS CONCEPTO FROM vacaciones_empleado WHERE (fecha_inicio BETWEEN @FECHA_INICIO AND @FECHA_FIN)"; 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()) { DiasJustificados Modelo; while (reader.Read()) { Modelo = new DiasJustificados(); Modelo.EmpleadoId = reader.GetInt32(0); Modelo.FechaDesde = reader.GetDateTime(1); Modelo.NumeroDias = reader.GetInt32(2); Modelo.Concepto = reader.GetString(3); Lista.Add(Modelo); } reader.Close(); reader.Dispose(); } connection.Close(); connection.Dispose(); } } catch (Exception ex) { Log.EscribeLog("Error: ElsabonDA.ObtenerDiasJustificados - " + ex.Message); } return(Lista); }
private void LoadView() { System.Data.Odbc.OdbcConnection OdbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString); try { DataTable dt = new DataTable(); dg = item_list; System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand(); OdbcConnection.Open(); oCmd.Connection = OdbcConnection; string selectCmd; if (ConfigurationManager.AppSettings["RX"] == "yes") { selectCmd = String.Format( " SELECT distinct A.CRITEM, CRSEQ, '' as CIGITM, A.CRCRTT, A.CRQTSR, B.ITDESC, B.ITSIZE, B.ITINVT, B.ITMUPC" + " FROM FPCRDDTL A LEFT JOIN FPITMMAS B ON A.CRITEM=B.ITMNUM" + // " LEFT OUTER JOIN FPCIGTAX Z ON Z.CIGSHP = A.CRITEM" + " WHERE A.CRARNO = {0} ", creditNo); } else { selectCmd = String.Format( " SELECT distinct A.CRITEM, CRSEQ, Z.CIGITM, A.CRCRTT, A.CRQTSR, B.ITDESC, B.ITSIZE, B.ITINVT, B.ITMUPC" + " FROM FPCRDDTL A LEFT JOIN FPITMMAS B ON A.CRITEM=B.ITMNUM" + " LEFT OUTER JOIN FPCIGTAX Z ON Z.CIGSHP = A.CRITEM" + " WHERE A.CRARNO = {0} ", creditNo); } oCmd.CommandText = selectCmd; System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd); da.Fill(dt); sCSV = ""; for (int k = 0; k <= dg.Columns.Count - 1; k++) { sCSV += dg.Columns[k].HeaderText + ","; } sCSV += "\n"; for (int i = 0; i <= dt.Rows.Count - 1; ++i) { DataRow dr = dt.Rows[i]; if (dr["CIGITM"].ToString().Trim() != "") { dr["CRITEM"] = dr["CIGITM"]; } sCSV += dr["CRITEM"].ToString() + ","; sCSV += "\"" + dr["ITDESC"].ToString().Trim() + "\"" + ","; sCSV += "\"" + dr["ITMUPC"].ToString().Trim() + "\"" + ","; sCSV += dr["CRCRTT"].ToString() + ","; sCSV += dr["ITSIZE"].ToString() + ","; sCSV += dr["CRQTSR"].ToString() + ","; sCSV += "\n"; } DataView dv = new DataView(dt); dg.DataSource = dv; dg.DataBind(); } catch (Exception ex) { } finally { OdbcConnection.Close(); } }
public bool Open(string connectionString, bool testIt) { int pos = connectionString.IndexOf(":"); if (pos == -1) { return(false); } try { Close(); string type = connectionString.Substring(0, pos); string connStr = connectionString.Substring(pos + 1, connectionString.Length - pos - 1); switch (type.ToLower()) { case "oledb": oledbConnection = new OleDbConnection(connStr); if (testIt) { oledbConnection.Open(); oledbConnection.Close(); } break; case "odbc": odbcConnection = new OdbcConnection(connStr); if (testIt) { odbcConnection.Open(); odbcConnection.Close(); } break; case "oracleclient": case "oracle": oracleConnection = new OracleConnection(connStr); if (testIt) { oracleConnection.Open(); oracleConnection.Close(); } break; case "sqlclient": case "sql": sqlConnection = new SqlConnection(connStr); if (testIt) { sqlConnection.Open(); sqlConnection.Close(); } break; case "npgsql": try { dbFactory = DataProvider.PostgresProvider; dbConnection = dbFactory.CreateConnection(); dbConnection.ConnectionString = connStr; if (testIt) { dbConnection.Open(); dbConnection.Close(); } break; } catch (Exception ex) { _errMsg = ex.Message; return(false); } break; default: _errMsg = "Test Connection is not supported for '" + type + "'"; return(false); } return(true); } catch (Exception ex) { _errMsg = ex.Message; return(false); } }
private void LoadItems(string sCustNo, string sDateStart, string sDateEnd, string sBrandFamily, double dTargetPct) { System.Data.Odbc.OdbcConnection odbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString); OdbcDataReader odbcDataReader = null; string selectCmd = " SELECT I.ITVSUF, G.GREFDT, 0 AS TOTAL, '' AS PCT, 0 AS SALES, 0 AS CREDITS, " + " (SELECT SUM(S.WKQTY) FROM FPSLSBRN S" + " LEFT JOIN FPITMMAS I1 ON S.WKITEM = I1.ITMNUM" + " WHERE S.WKSHIP = " + sCustNo + " AND S.WKTYPE = 'S' " + " AND I1.ITVSUF = I.ITVSUF " + " AND S.WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd + " AND I1.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')) AS SALES_COUNT, " + " (SELECT SUM(C.WKQTY) FROM FPSLSBRN C" + " LEFT JOIN FPITMMAS I2 ON C.WKITEM = I2.ITMNUM" + " WHERE C.WKSHIP = " + sCustNo + " AND C.WKTYPE = 'C' " + " AND I2.ITVSUF = I.ITVSUF " + " AND C.WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd + " AND I2.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')) AS CREDITS_COUNT" + " FROM FPSLSBRN A " + " LEFT JOIN FPITMMAS I ON A.WKITEM = I.ITMNUM" + " LEFT JOIN FPSYGREF G ON G.GREFCD = 'BF' AND I.ITVSUF = G.GREFKY" + " WHERE WKSHIP = " + sCustNo + " AND WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd + " AND I.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')" + " GROUP BY ITVSUF, GREFDT"; try { DataTable dt = new DataTable(); odbcConnection.Open(); OdbcCommand odbcCommand = new OdbcCommand(selectCmd, odbcConnection); System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(odbcCommand); da.Fill(dt); double dTotal = 0; foreach (DataRow dr in dt.Rows) { try { dr["SALES"] = Convert.ToInt32(dr["SALES_COUNT"]); } catch { dr["SALES"] = 0; } try { dr["CREDITS"] = Convert.ToInt32(dr["CREDITS_COUNT"]); } catch { dr["CREDITS"] = 0; } dr["TOTAL"] = Convert.ToInt32(dr["SALES"]) - Convert.ToInt32(dr["CREDITS"]); dTotal += Convert.ToInt32(dr["TOTAL"]); } sales_total.Text = dTotal.ToString("#0"); foreach (DataRow dr in dt.Rows) { double dPct = 0.0; try { dPct = (Convert.ToInt32(dr["TOTAL"]) / dTotal) * 100; } catch { dPct = 0; } dr["PCT"] = dPct.ToString("#0.00"); if (Convert.ToInt32(dr["ITVSUF"]) == Convert.ToInt32(brand_family_dropdown.SelectedValue)) { sales_current_pct.Text = dr["PCT"].ToString(); sales_brand.Text = dr["TOTAL"].ToString(); double dAddUnits = 0; try { dAddUnits = (dTargetPct / 100 * dTotal) - Convert.ToInt32(dr["TOTAL"]); sales_additional.Text = dAddUnits.ToString("#0"); } catch { } } } DataView dv = new DataView(dt); item_list.DataSource = dv; item_list.DataBind(); } catch (Exception ex) { } finally { if (odbcDataReader != null) { odbcDataReader.Close(); } odbcConnection.Close(); } }