public void readsequentialyodbc() { int iter = 0; int currentIndex = 0; int pageSize = 50; string sql = ConfigurationManager.ConnectionStrings["odbc"].ConnectionString; string sqltest = ConfigurationManager.ConnectionStrings["frymek"].ConnectionString; OdbcConnection connection = new OdbcConnection(sql); string orderSQL = "SELECT * FROM RREV.BGDTBIK order by BIK_ENTIDAD, BIK_CENTRO_ALTA, BIK_CUENTA"; // Assumes that connection is a valid SqlConnection object. OdbcDataAdapter adapter = new OdbcDataAdapter(orderSQL, connection); DataSet dataSet = new DataSet(); adapter.Fill(dataSet, currentIndex, pageSize, "Orders"); DataTable dataTable = dataSet.Tables[0]; while (dataTable.Rows.Count > 0) { Console.WriteLine("iteration: " + iter++); foreach (DataRow row in dataTable.Rows) { row.SetAdded(); } BultInsert(pageSize, sqltest, dataTable); currentIndex += pageSize; dataSet.Tables["Orders"].Rows.Clear(); adapter.Fill(dataSet, currentIndex, pageSize, "Orders"); dataTable = dataSet.Tables[0]; } Console.WriteLine(String.Format("done rows: {0} with iteration: {1}", iter * pageSize, iter)); }
private static void GetRealDataTimer(object source,ElapsedEventArgs e) { try { OdbcDataAdapter adapter = new OdbcDataAdapter("select A_CV from FIX", RealConnection); DataSet ds = new DataSet(); adapter.Fill(ds, "FIX"); for (int i = 0; i < 100; i++) { data[i] = ds.Tables[0].Rows[i]["A_CV"].ToString(); } adapter.Dispose(); // RealConnection..Close(); } catch (Exception ex) { for (int i = 0; i < 100; i++) { data[i] = "???"; } } }
/// <summary> /// before create a table, check if it exists or not /// </summary> /// <param name="tableName"></param> /// <returns></returns> public bool IsTableExist(DbConnect dbConnect, string tableName) { if (!dbConnect.IsConnected()) { dbConnect.ConnectToDatabase(); } OdbcCommand showTablesCommand = dbConnect.CreateCommand(); showTablesCommand.CommandText = @"SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$SYSTEM_FLAG = 0;"; DataTable tableNamesTable = new DataTable("Table Names"); System.Data.Odbc.OdbcDataAdapter adapter = new System.Data.Odbc.OdbcDataAdapter(); try { adapter.SelectCommand = showTablesCommand; adapter.Fill(tableNamesTable); } catch { showTablesCommand.CommandText = "Show tables"; adapter.SelectCommand = showTablesCommand; adapter.Fill(tableNamesTable); } foreach (DataRow dRow in tableNamesTable.Rows) { if (dRow[0].ToString().ToLower().Trim() == tableName.ToLower()) { return(true); } } showTablesCommand.Dispose(); return(false); }
public static string GetDataTable(ref DataTable _ConnectionDt, ref DataTable ReturnDt, string SelectCommand) { OdbcConnection odbc_Conn = new OdbcConnection(); OdbcDataAdapter odbcAdap = new OdbcDataAdapter(); OdbcCommand odbcCmd = new OdbcCommand(); string ErrMsg = string.Empty; try { ErrMsg = MakeInformixConnection(ref _ConnectionDt, ref odbc_Conn); if (ErrMsg == string.Empty) { odbcCmd.Connection = odbc_Conn; odbcCmd.CommandText = SelectCommand; odbcAdap.SelectCommand = odbcCmd; odbcAdap.Fill(ReturnDt); odbc_Conn.Close(); } return ErrMsg; } catch (Exception err) { return err.Message; } finally { odbcAdap.Dispose(); odbcCmd.Dispose(); odbc_Conn.Dispose(); } }
//get all records public DataTable getall() { try { DataTable dt_all; //create new connection with the databse OdbcConnection conn = getconnection(); //open the created connection conn.Open(); //declare dataadpter variable and hold the values OdbcDataAdapter adapter = new OdbcDataAdapter("select * from school_events ", conn); //create new data tbale object dt_all = new DataTable(); //fill the data table object through data adapter adapter.Fill(dt_all); //close the open clonnection conn.Close(); //return the filled data table object return dt_all; } catch (Exception ss) { string s = ss.Message; return null; } }
public void synchronizateDataSet() { this.SynchronizatedStartTime = DateTime.Now; OdbcConnection conn = new OdbcConnection(); conn.ConnectionString = this.DBConnectionSetting.ConnectionStr; try { foreach (var item in QuerySet_) { OdbcCommand sqlCommand = new OdbcCommand(); conn.Open(); // ------------------------------------------ Position -------------------------------------------- sqlCommand.CommandText = item.Value.Replace("_ReferenceDate_", this.ReferenceDate_.ToString("yyyyMMdd")).ToString(); OdbcDataAdapter dataAdapter = new OdbcDataAdapter(sqlCommand.CommandText, conn); positionDataSet_.Tables.Add(item.Key); dataAdapter.Fill(positionDataSet_.Tables[item.Key]); } } catch (Exception e) { ErrorManager.setError(e); } this.SynchronizatedEndTime = DateTime.Now; }
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; }
public static int JumlahSuratKeluar(string _reset_id, string _reset_value) { DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); if (_reset_id.ToLower() == "kategori".ToLower()) sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where kategori='" + _reset_value + "'"); else if (_reset_id.ToLower() == "tkkeamanan".ToLower()) sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where tk_keamanan='" + _reset_value + "'"); else if (_reset_id.ToLower() == "daily".ToLower()) sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where day('" + _reset_value + "')=day('" + _reset_value + "') and month('" + _reset_value + "')=month('" + _reset_value + "') and year('" + _reset_value + "')=year('" + _reset_value + "')"); else if (_reset_id.ToLower() == "monthly".ToLower()) sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where month('" + _reset_value + "')=month('" + _reset_value + "') and year('" + _reset_value + "')=year('" + _reset_value + "')"); else if (_reset_id.ToLower() == "yearly".ToLower()) sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar where year('" + _reset_value + "')=year('" + _reset_value + "')"); else sb.Append("SELECT SQL_NO_CACHE nomor_surat from surat_keluar"); OdbcDataAdapter da = new OdbcDataAdapter(sb.ToString(), T8Application.DBConnection); da.Fill(dt); return dt.Rows.Count; }
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 DataSet GetRealData() { //string RealConnectionSting = "DSN=FIX Dynamics Real Time Data";//实时数据库连接字符串 ////string HisConnectionSting = "DSN=FIX Dynamics Historical Data";//历史数据库连接字符串 //OdbcConnection RealConnection;//实时数据库连接 ////OdbcConnection HisConnection;//历史数据库连接 //DataSet errds = new DataSet(); try { //RealConnection = new OdbcConnection(RealConnectionSting); //RealConnection.Open(); OdbcDataAdapter adapter = new OdbcDataAdapter("select A_CV from FIX", RealConnection); DataSet ds = new DataSet(); adapter.Fill(ds, "FIX"); adapter.Dispose(); return ds; // RealConnection..Close(); } catch (Exception ex) { return new DataSet(); } }
protected void Page_Load(object sender, EventArgs e) { if (HttpContext.Current.Session["USER_ROLE"] != null) { string s = HttpContext.Current.Session["USER_ROLE"].ToString(); if (s != "Admin") { Response.Redirect("~/default.aspx"); } } else { Response.Redirect("~/default.aspx"); } int articleId = System.Convert.ToInt32(Request.QueryString["id"]); string MyConString = ConfigurationSettings.AppSettings["connectionString"]; OdbcConnection connection = new OdbcConnection(MyConString); string sqlText = "select * FROM News WHERE id = " + articleId; OdbcCommand command = new OdbcCommand(sqlText, connection); //category 1 string selectSQL = "SELECT id, name, description FROM image WHERE approval = 0 ORDER BY date ASC"; OdbcCommand cmd1 = new OdbcCommand(selectSQL, connection); OdbcDataAdapter adapter = new OdbcDataAdapter(cmd1); DataSet ds = new DataSet(); adapter.Fill(ds); // DataList1.DataSource = ds; // DataList1.DataBind(); }
/// <summary> /// 将数据读取到 DataSet 中. /// </summary> public void ReadDataToDataSet() { Console.WriteLine("使用DataAdapter,将数据填充到DataSet中,然后脱离数据库,直接对DataSet进行处理。"); // 建立数据库连接. OdbcConnection conn = new OdbcConnection(connString); // 创建一个适配器 OdbcDataAdapter adapter = new OdbcDataAdapter(SQL, conn); // 创建DataSet,用于存储数据. DataSet testDataSet = new DataSet(); // 执行查询,并将数据导入DataSet. adapter.Fill(testDataSet, "result_data"); // 关闭数据库连接. conn.Close(); // 处理DataSet中的每一行数据. foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows) { // 将检索出来的数据,输出到屏幕上. Console.WriteLine("Date:{0} ; Money:{1} ", testRow["SALE_DATE"], testRow["SUM_MONEY"] ); } }
public static DataTable getDataTableImagenes(int id) { OdbcConnection con = ConexionBD.ObtenerConexion(); DataSet ds = new DataSet(); List<Noticia> listaNoticias = new List<Noticia>(); DataTable dataTable = null; try { OdbcCommand cmd = new OdbcCommand("SELECT i.pathBig, i.pathSmall, i.pathMedium FROM imagen_x_noticia ixn, imagen i" + " WHERE ixn.idNoticia = " + id + " AND i.id = ixn.idImagen", con); cmd.CommandType = CommandType.Text; dataTable = new DataTable(); OdbcDataAdapter adapter = new OdbcDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill(dataTable); } catch (Exception e) { throw new SportingException("Ocurrio un problema al intentar obtener las imagenes de las noticias. " + e.Message); } finally { con.Close(); } return dataTable; }
// 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); }
private DataSet Read(string fileTableName) { writeSchema(fileTableName); try { string conn_excel_str = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + _CSVFileLocation + ";MaxScanRows=0;"; //Server.MapPath(".//" + csv_store_read) //Path mapped to csv file temp stored on the server string sql_select; OdbcConnection obj_oledb_con; OdbcDataAdapter obj_oledb_da; //Create connection to CSV file obj_oledb_con = new OdbcConnection(conn_excel_str); //Open the connection obj_oledb_con.Open(); //Fetch records from CSV sql_select = "select * from [" + fileTableName + "]"; obj_oledb_da = new OdbcDataAdapter(sql_select, obj_oledb_con); //Fill dataset with the records from CSV file DataSet ds = new DataSet(); obj_oledb_da.Fill(ds); //Close Connection to CSV file obj_oledb_con.Close(); return ds; } finally { deleteSchema(); } }
//get all the records of the table public DataTable databind() { try { //create data table object DataTable dt_select_record; //set up new connection OdbcConnection con = getconnection(); //open the setup coneection con.Open(); //cretae datapter OdbcDataAdapter adapter = new OdbcDataAdapter("select * from student_warning ", con); dt_select_record = new DataTable(); //fill the datatable adapter.Fill(dt_select_record); //close the connection con.Close(); return dt_select_record; } catch { return null; } }
public static string FetchTable(string SelectCommand, string XMLconnectionPhysicalFilePath, ref DataTable Dt) { OdbcConnection OdbcConn = new OdbcConnection(); OdbcCommand OdbcCmd = new OdbcCommand(); OdbcDataAdapter OdbcAdap = new OdbcDataAdapter(); try { string DbErr = InitializeODBCConnection(ref OdbcConn, XMLconnectionPhysicalFilePath); if (DbErr == string.Empty) { OdbcCmd.Connection = OdbcConn; OdbcCmd.CommandText = SelectCommand; OdbcAdap.SelectCommand = OdbcCmd; OdbcAdap.Fill(Dt); return string.Empty; } else return DbErr; } catch (Exception err) { return err.Message; } finally { OdbcAdap.Dispose(); OdbcCmd.Dispose(); OdbcConn.Close(); OdbcConn.Dispose(); } }
public static List<BusinessObjects.ContainerCargo> GetCargo(int ContainerID) { DataTable dt = new DataTable(); string sql = string.Format(@"SELECT * FROM Cargo where ContainerID={0}", ContainerID); OdbcDataAdapter adp = new OdbcDataAdapter(sql, Properties.Settings.Default.IESDBConn); adp.Fill(dt); List<BusinessObjects.ContainerCargo> lc = new List<BusinessObjects.ContainerCargo>(); foreach (DataRow row in dt.Rows) { BusinessObjects.ContainerCargo c = new BusinessObjects.ContainerCargo(); c.ID = int.Parse(row["ID"].ToString()); c.Description = row["Description"].ToString(); int p; int.TryParse(row["Pieces"].ToString(),out p); c.Pieces = p; p = 0; c.UOM = row["UOM"].ToString(); int.TryParse(row["GrossWeight"].ToString(), out p); c.GrossWeight = p; p = 0; int.TryParse(row["GrossWeight"].ToString(), out p); c.NetWeight = p; bool h; bool.TryParse(row["Hazardous"].ToString(),out h); c.Hazardous = h; lc.Add(c); } return lc; }
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; }
public static BusinessObjects.Client GetClient(string clientid) { DataTable dt = new DataTable(); BusinessObjects.Client c = new BusinessObjects.Client(); string sql = @"SELECT rtrim(ID) ID, TYPE, NAME,[Contact],[Phone],[Fax],[ReferenceNo] FROM CLIENT_DAT WHERE ID=?"; using (OdbcDataAdapter adp = new OdbcDataAdapter(sql, Properties.Settings.Default.IESDBConn)) { adp.SelectCommand.Parameters.Add(new OdbcParameter("@clientid", clientid)); adp.Fill(dt); } if (dt.Rows.Count > 0) { c.ClientID = dt.Rows[0]["ID"].ToString(); c.Type = dt.Rows[0]["TYPE"].ToString(); c.Name = dt.Rows[0]["NAME"].ToString(); c.Contact = dt.Rows[0]["Contact"].ToString(); c.Phone = dt.Rows[0]["Phone"].ToString(); c.Fax = dt.Rows[0]["Fax"].ToString(); c.ReferenceNo = dt.Rows[0]["ReferenceNo"].ToString(); return c; } return null; }
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 System.Data.DataSet ExecuteDatasetQueryOnDB(string sSQL, string sConnectionString) { System.Data.Odbc.OdbcDataAdapter QDataAdapter = null; DataSet QDataSet = null; try { QDataSet = new DataSet(); QDataAdapter = new System.Data.Odbc.OdbcDataAdapter(sSQL, sConnectionString); QDataAdapter.Fill(QDataSet); return(QDataSet); } finally { if (QDataSet != null) { QDataSet.Dispose(); } QDataSet = null; if (QDataAdapter != null) { QDataAdapter.Dispose(); } QDataAdapter = null; } }
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 DataTable GetData(string strConn, string strSql, int timeout) { DataTable dt = new DataTable("td"); using (OdbcConnection conn = new OdbcConnection(strConn)) { conn.Open(); OdbcCommand cmd = null; OdbcDataAdapter da = null; try { cmd = new OdbcCommand(strSql, conn) { CommandTimeout = timeout }; da = new OdbcDataAdapter { SelectCommand = cmd }; da.Fill(dt); return dt; } catch (Exception ex) { throw new Exception("error getting data " + ex.Message); } finally { if (da != null) { da.Dispose(); } if (cmd != null) { cmd.Dispose(); } conn.Close(); } } }
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; } }
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(); } } }
public static Jugador getJugador_plantelActual(int id) { OdbcConnection conexion = null; OdbcCommand cmd = null; Jugador jugador = new Jugador(); try { conexion = ConexionBD.ObtenerConexion(); String getJugador = "select j.id, j.nombreApellido, j.posicion, j.idPlantel " + "from jugador j where j.idPlantel = 1 and j.id = " + id; cmd = new OdbcCommand(getJugador, conexion); OdbcDataAdapter da = new OdbcDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); jugador.IdJugador = Convert.ToInt32(dt.Rows[0]["id"].ToString()); jugador.NombreApellido = dt.Rows[0]["nombreApellido"].ToString(); jugador.Posicion = dt.Rows[0]["posicion"].ToString(); jugador.Foto = ImagenDAL.getImagenJugador(conexion, id); } catch (Exception e) { throw new SportingException("Ocurrio un error al intentar obtener los datos de un jugador. " + e.Message); } finally { cmd.Connection.Close(); } return jugador; }
/// <summary> /// 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; }
//retrive all the grades from the database public DataTable get_all_grades() { try { DataTable dt_serachresult; //create new connection with the databse OdbcConnection conn = getconnection(); //open the created connection conn.Open(); //declare dataadpter variable and hold the values OdbcDataAdapter adapter = new OdbcDataAdapter("select distinct SC_GRADE from student_class ", conn); //create new data tbale object dt_serachresult = new DataTable(); //fill the data table object through data adapter adapter.Fill(dt_serachresult); //close the open clonnection conn.Close(); //return the filled data table object return dt_serachresult; } catch { return null; } }
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 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 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(); } }
/// <summary> /// Retorna un datatable con todas las noticias. /// Cada noticia con el pathmedium de su imagen de portada. /// </summary> /// <returns></returns> public static DataTable getDataTableNoticias() { DataTable dataTable = new DataTable(); String query = "SELECT n.id, n.titulo, n.descripcion, n.principal, i.pathBig "+ "FROM noticia n, imagen_x_noticia ixn, imagen i "+ "WHERE ixn.idImagen = i.id AND ixn.idNoticia = n.id AND i.portada = 1"; using (OdbcConnection con = new OdbcConnection(Constantes.CONNECTION_STRING)) { using (OdbcCommand cmd = new OdbcCommand(query, con)) { con.Open(); cmd.CommandType = CommandType.Text; try { dataTable = new DataTable(); OdbcDataAdapter adapter = new OdbcDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill(dataTable); } catch (Exception e) { //StreamWriter sw = new StreamWriter(HttpContext.Current.Server.MapPath("~") + "log.txt", true); //sw.WriteLine(e.Message); //sw.Flush(); //sw.Close(); //throw new SportingException("Ocurrio un problema al intentar obtener todas las noticias. " + e.Message); throw e; } } } return dataTable; }
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 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]); }
}// readTableDetails public bool readTableContent(string TableName, ref DataTable TableData, OdbcConnection conn) { bool RetValue = true; try { OdbcCommand odbcCommand1 = new OdbcCommand(); odbcCommand1.CommandText = "SELECT * FROM " + TableName + ";"; odbcCommand1.Connection = conn; //odbcCommand1. System.Data.Odbc.OdbcDataAdapter adapt = new System.Data.Odbc.OdbcDataAdapter(odbcCommand1); adapt.SelectCommand = odbcCommand1; adapt.Fill(TableData); // to verify its existence // odbcCommand1.CommandText = "SELECT * FROM all_objects WHERE object_type in ('TABLE', 'VIEW') and object_name = '"+ TableName +"';"; //System.Data.Odbc.OdbcDataAdapter adapt = new System.Data.Odbc.OdbcDataAdapter("SELECT * FROM " + TableName, conn); //adapt.ContinueUpdateOnError = true; //adapt.Fill(TableData); } catch (OdbcException odbcEx) { MessageBox.Show( "Message: " + odbcEx.Message + "" + "Source: " + odbcEx.Source + "\r\n" + "InnerException: " + odbcEx.InnerException + "\r\n" + "TargetSite: " + odbcEx.TargetSite + "\r\n" + "StackTrace: " + odbcEx.StackTrace, "OdbcException", MessageBoxButtons.OK, MessageBoxIcon.Error); RetValue = false; } catch (System.FormatException formatEx) { MessageBox.Show( "Message: " + formatEx.Message + "" + "Source: " + formatEx.Source + "\r\n" + "InnerException: " + formatEx.InnerException + "\r\n" + "TargetSite: " + formatEx.TargetSite + "\r\n" + "StackTrace: " + formatEx.StackTrace, "FormatException", MessageBoxButtons.OK, MessageBoxIcon.Error); RetValue = false; } catch (Exception ex) { MessageBox.Show( "Message: " + ex.Message + "" + "Source: " + ex.Source + "\r\n" + "InnerException: " + ex.InnerException + "\r\n" + "TargetSite: " + ex.TargetSite + "\r\n" + "StackTrace: " + ex.StackTrace, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); RetValue = false; } return(RetValue); }// readTableDetails
private void btnSearch_Click(object sender, EventArgs e) { string strSQL = ""; string varCode = ""; string varName = ""; string varPhys = ""; if (txtPatientID.Text.Length > 0) { varCode = "%" + txtPatientID.Text + "%"; } if (txtFirstName.Text.Length > 0) { varName = "%" + txtFirstName.Text + "%"; } if (txtReferringMD.Text.Length > 0) { varPhys = "%" + txtReferringMD.Text + "%"; } if (chkSearchDate.Checked) { strSQL = "SELECT * FROM PatientStudyView WHERE (PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "') and convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc"; } else { strSQL = "SELECT * FROM PatientStudyView WHERE PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "' order by StudyDate desc"; } if (string.IsNullOrEmpty(varCode) && string.IsNullOrEmpty(varName) && string.IsNullOrEmpty(varPhys) && chkSearchDate.Checked) { strSQL = "SELECT * FROM PatientStudyView WHERE convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc"; } CN1.OpenConnection(); CMD1 = new System.Data.Odbc.OdbcCommand(strSQL, CN1.DBConnection); //Dim dtst As New DataSet DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable(); System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter(); adptr.SelectCommand = CMD1; adptr.Fill(table); DataGridView1.Refresh(); DataGridView1.DataSource = null; DataGridView1.DataSource = table; this.Cursor = Cursors.Default; GridDesign(); CMD1.Dispose(); CN1.closeconnection(); }
protected void Button1_Click(object sender, EventArgs e) { csvFolder = Server.MapPath("~/Files/"); csvFile = Path.GetFileName(FileUpload1.PostedFile.FileName); csvPath = csvFolder + csvFile; csvFilename = csvFolder + "QzBank.csv"; //csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName); if (File.Exists(csvFilename)) { File.Delete(csvFilename); } FileUpload1.SaveAs(csvPath); File.Move(csvPath, csvFilename); connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + csvFolder + ";Extensions=csv,txt;"; //string connString = WebConfigurationManager.ConnectionStrings["CsvUTF8ConnectionString"].ConnectionString; using (OdbcConnection conn = new OdbcConnection(connString)) { string query = "SELECT * FROM QzBank.CSV"; using (OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(query, conn)) { da.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); sdt = dt; dt.Dispose(); da.Dispose(); } } connString1 = WebConfigurationManager.ConnectionStrings["QuizBankConnectionString"].ConnectionString; using (SqlConnection conn = new SqlConnection(connString1)) { conn.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn)) { bulkCopy.DestinationTableName = "dbo.QuizBank"; bulkCopy.WriteToServer(sdt); } conn.Dispose(); } }
private void btnYesterday_Click(object sender, EventArgs e) { CN1.OpenConnection(); CMD1 = new System.Data.Odbc.OdbcCommand("select * from PatientStudyView where datepart(dd,StudyDate)=" + DateTime.Now.AddDays(-1).Day.ToString() + " and datepart(MM,StudyDate)=" + DateTime.Now.AddDays(-1).Month.ToString() + " and datepart(yy,StudyDate)=" + DateTime.Now.AddDays(-1).Year.ToString() + "", CN1.DBConnection); //Dim dtst As New DataSet DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable(); System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter(); adptr.SelectCommand = CMD1; adptr.Fill(table); DataGridView1.Refresh(); DataGridView1.DataSource = null; DataGridView1.DataSource = table; this.Cursor = Cursors.Default; GridDesign(); CMD1.Dispose(); CN1.closeconnection(); }
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 void ReadDataBase() { System.Data.Odbc.OdbcDataAdapter dbAdapter = new System.Data.Odbc.OdbcDataAdapter(); dbAdapter.SelectCommand = this.dbCommand; DataSet dbDataSet = new DataSet(); ushort dimL = 0; string strSQL; strSQL = "SELECT Name,Code FROM DisplayDefination ORDER BY Code"; dbCommand.CommandText = strSQL; string strTableName = "DisplayDefination"; dbAdapter.Fill(dbDataSet, strTableName); dimL = (ushort)dbDataSet.Tables[strTableName].Rows.Count; for (ushort i = 0; i < dimL; i++) { string strCode = dbDataSet.Tables[strTableName].Rows[i]["Code"].ToString(); string[] strParameters = dbDataSet.Tables[strTableName].Rows[i]["Name"].ToString().Split('-'); string strName = strParameters[1]; ArchiveParameters.Add(strName, strCode); } }
}// read tablenames from DB public bool readTableDetails(string TableName, ref DataTable TableDesc, OdbcConnection conn, bool IsWildCardFilterOn) { bool RetValue = true; try { #region get User Id from connection string int start = conn.ConnectionString.IndexOf("UID=") + 4; int end = conn.ConnectionString.IndexOf(";", start); string uID = conn.ConnectionString.Substring((start), (end - start));// User ID #endregion string SemanticLenght = "BYTE"; try { // dependent on V_$NLS_PARAMETERS.NLS_LENGTH_SEMANTICS (CHAR or BYTE) // the select statement is different OdbcCommand odbcCommandSemantic = new OdbcCommand(); odbcCommandSemantic.CommandText = "SELECT VALUE FROM SYS.V_$NLS_PARAMETERS WHERE PARAMETER = 'NLS_LENGTH_SEMANTICS';"; odbcCommandSemantic.Connection = conn; DataTable SemanticTable = new DataTable(); System.Data.Odbc.OdbcDataAdapter ODBC_ADAPT_SEMANTIC = new System.Data.Odbc.OdbcDataAdapter(odbcCommandSemantic); ODBC_ADAPT_SEMANTIC.Fill(SemanticTable); SemanticLenght = SemanticTable.Rows[0]["VALUE"].ToString(); } catch (OdbcException ex) { MessageBox.Show(ex.Message, "*** ERROR GETTING PARAMETER 'NLS_LENGTH_SEMANTICS'. DEFAULT WILL BE 'BYTE' ***", MessageBoxButtons.OK, MessageBoxIcon.Error); SemanticLenght = "BYTE"; } OdbcCommand odbcCommand1 = new OdbcCommand(); if (IsWildCardFilterOn) { if (SemanticLenght.Equals("BYTE") || SemanticLenght.Equals("CHAR")) { odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, decode(CHAR_LENGTH, 0, DATA_PRECISION, CHAR_LENGTH) as DATA_LENGTH, NULLABLE, DATA_SCALE, OWNER FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "%' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;"; } else// BYTE { odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_SCALE FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "%' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;"; } } else { if (SemanticLenght.Equals("BYTE") || SemanticLenght.Equals("CHAR")) { odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, decode(CHAR_LENGTH, 0, DATA_PRECISION, CHAR_LENGTH) as DATA_LENGTH, NULLABLE, DATA_SCALE, OWNER FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;"; } else// BYTE { odbcCommand1.CommandText = "SELECT COLUMN_NAME, COLUMN_ID, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_SCALE FROM ALL_TAB_COLUMNS WHERE owner like '" + uID.ToUpper() + "' and TABLE_NAME = '" + TableName + "' ORDER BY COLUMN_ID;"; } } odbcCommand1.Connection = conn; System.Data.Odbc.OdbcDataAdapter adapt = new System.Data.Odbc.OdbcDataAdapter(odbcCommand1); adapt.Fill(TableDesc); DataTable all_constraints_tab = new DataTable(); // table for PK select odbcCommand1.CommandText = "SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '" + TableName.ToUpper() + "' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cons.owner like '" + uID.ToUpper() + "%' ORDER BY cols.POSITION;"; //odbcCommand1.CommandText = "SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '" // + TableName.ToUpper() + "' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.POSITION;"; adapt.SelectCommand = odbcCommand1; adapt.Fill(all_constraints_tab); // comments DataTable user_comment_tab = new DataTable(); // table for comments select odbcCommand1.CommandText = "select column_name, comments from user_col_comments where table_name like '" + TableName.ToUpper() + "%';"; adapt.SelectCommand = odbcCommand1; adapt.Fill(user_comment_tab); // NEW Columns for DataTable DataColumn PK = new DataColumn("PK"); // column for PK identification TableDesc.Columns.Add(PK); DataColumn COLUMN_COMMENT = new DataColumn("COLUMN_COMMENT"); // column for comment TableDesc.Columns.Add(COLUMN_COMMENT); int PKNO = 1; for (int j = 0; j < TableDesc.Rows.Count; j++) { TableDesc.Rows[j]["PK"] = ""; TableDesc.Rows[j]["COLUMN_COMMENT"] = ""; for (int i = 0; i < all_constraints_tab.Rows.Count; i++) { if (TableDesc.Rows[j]["COLUMN_NAME"].ToString() == all_constraints_tab.Rows[i][0].ToString()) { TableDesc.Rows[j]["PK"] = "PK " + (PKNO++); break; } } // comments for (int i = 0; i < user_comment_tab.Rows.Count; i++) { if (TableDesc.Rows[j]["COLUMN_NAME"].ToString() == user_comment_tab.Rows[i][0].ToString()) { TableDesc.Rows[j]["COLUMN_COMMENT"] = user_comment_tab.Rows[i][1].ToString(); break; } }// for (int i = 0; i < user_comment_tab.Rows.Count; i++) } } catch (OdbcException ex) { MessageBox.Show(ex.StackTrace + " : \r\n\r\n" + ex.Message, "OdbcException", MessageBoxButtons.OK, MessageBoxIcon.Error); RetValue = false; } return(RetValue); }// readTableDetails
private void btnYesterday_Click(object sender, EventArgs e) { string sqlQuery = ""; if (chkAll.Checked) { sqlQuery = "select * from PatientStudyView where datepart(dd,StudyDate)=" + DateTime.Now.AddDays(-1).Day.ToString() + " and datepart(MM,StudyDate)=" + DateTime.Now.AddDays(-1).Month.ToString() + " and datepart(yy,StudyDate)=" + DateTime.Now.AddDays(-1).Year.ToString() + ""; } else { string Mod = ""; bool MoreThanOne = false; if (chkCR.Checked) { Mod = "Modality='CR'"; MoreThanOne = true; } if (chkCT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='CT'"; } else { Mod = "Modality='CT'"; } MoreThanOne = true; } if (chkDX.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='DX'"; } else { Mod = "Modality='DX'"; } MoreThanOne = true; } if (chkES.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='ES'"; } else { Mod = "Modality='ES'"; } MoreThanOne = true; } if (chkMG.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='MG'"; } else { Mod = "Modality='MG'"; } MoreThanOne = true; } if (chkMR.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='MR'"; } else { Mod = "Modality='MR'"; } MoreThanOne = true; } if (chkNM.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='NM'"; } else { Mod = "Modality='NM'"; } MoreThanOne = true; } if (chkOT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='OT'"; } else { Mod = "Modality='OT'"; } MoreThanOne = true; } if (chkPT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='PT'"; } else { Mod = "Modality='PT'"; } MoreThanOne = true; } if (chkRF.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='RF'"; } else { Mod = "Modality='RF'"; } MoreThanOne = true; } if (chkRT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='RT'"; } else { Mod = "Modality='RT'"; } MoreThanOne = true; } if (chkSC.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='SC'"; } else { Mod = "Modality='SC'"; } MoreThanOne = true; } if (chkUS.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='US'"; } else { Mod = "Modality='US'"; } MoreThanOne = true; } if (chkXA.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='XA'"; } else { Mod = "Modality='XA'"; } MoreThanOne = true; } sqlQuery = "select * from PatientStudyView where datepart(dd,StudyDate)=" + DateTime.Now.AddDays(-1).Day.ToString() + " and datepart(MM,StudyDate)=" + DateTime.Now.AddDays(-1).Month.ToString() + " and datepart(yy,StudyDate)=" + DateTime.Now.AddDays(-1).Year.ToString() + " AND (" + Mod + ")"; } CN1.OpenConnection(); CMD1 = new System.Data.Odbc.OdbcCommand(sqlQuery, CN1.DBConnection); //Dim dtst As New DataSet DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable(); System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter(); adptr.SelectCommand = CMD1; adptr.Fill(table); DataGridView1.Refresh(); DataGridView1.DataSource = null; DataGridView1.DataSource = table; this.Cursor = Cursors.Default; GridDesign(); CMD1.Dispose(); CN1.closeconnection(); }
private void btnSearch_Click(object sender, EventArgs e) { if (txtPatientID.Text.Length <= 0 && txtFirstName.Text.Length <= 0 && txtReferringMD.Text.Length <= 0 && chkSearchDate.Checked == false) { return; } string strSQL = ""; string varCode = ""; string varName = ""; string varPhys = ""; if (txtPatientID.Text.Length > 0) { varCode = "%" + txtPatientID.Text + "%"; } if (txtFirstName.Text.Length > 0) { varName = "%" + txtFirstName.Text + "%"; } if (txtReferringMD.Text.Length > 0) { varPhys = "%" + txtReferringMD.Text + "%"; } if (chkAll.Checked) { if (chkSearchDate.Checked) { strSQL = "SELECT * FROM PatientStudyView WHERE (PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "') and convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc"; } else { strSQL = "SELECT * FROM PatientStudyView WHERE PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "' order by StudyDate desc"; } if (string.IsNullOrEmpty(varCode) && string.IsNullOrEmpty(varName) && string.IsNullOrEmpty(varPhys) && chkSearchDate.Checked) { strSQL = "SELECT * FROM PatientStudyView WHERE convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' order by StudyDate desc"; } } else { string Mod = ""; bool MoreThanOne = false; if (chkCR.Checked) { Mod = "Modality='CR'"; MoreThanOne = true; } if (chkCT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='CT'"; } else { Mod = "Modality='CT'"; } MoreThanOne = true; } if (chkDX.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='DX'"; } else { Mod = "Modality='DX'"; } MoreThanOne = true; } if (chkES.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='ES'"; } else { Mod = "Modality='ES'"; } MoreThanOne = true; } if (chkMG.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='MG'"; } else { Mod = "Modality='MG'"; } MoreThanOne = true; } if (chkMR.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='MR'"; } else { Mod = "Modality='MR'"; } MoreThanOne = true; } if (chkNM.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='NM'"; } else { Mod = "Modality='NM'"; } MoreThanOne = true; } if (chkOT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='OT'"; } else { Mod = "Modality='OT'"; } MoreThanOne = true; } if (chkPT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='PT'"; } else { Mod = "Modality='PT'"; } MoreThanOne = true; } if (chkRF.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='RF'"; } else { Mod = "Modality='RF'"; } MoreThanOne = true; } if (chkRT.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='RT'"; } else { Mod = "Modality='RT'"; } MoreThanOne = true; } if (chkSC.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='SC'"; } else { Mod = "Modality='SC'"; } MoreThanOne = true; } if (chkUS.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='US'"; } else { Mod = "Modality='US'"; } MoreThanOne = true; } if (chkXA.Checked) { if (MoreThanOne) { Mod = Mod + " OR Modality='XA'"; } else { Mod = "Modality='XA'"; } MoreThanOne = true; } if (chkSearchDate.Checked) { strSQL = "SELECT * FROM PatientStudyView WHERE (PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "') and convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' AND (" + Mod + ") order by StudyDate desc"; } else { strSQL = "SELECT * FROM PatientStudyView WHERE PatientID LIKE '" + varCode + "' OR PatientName_DICOM LIKE '" + varName + "' or ReferringPhysician_DICOM like '" + varPhys + "' AND (" + Mod + ") order by StudyDate desc"; } if (varCode.Length <= 0 && varName.Length <= 0 && varPhys.Length <= 0 && chkSearchDate.Checked) { strSQL = "SELECT * FROM PatientStudyView WHERE convert(date,StudyDate) BETWEEN '" + dtpDateFrom.Value.ToString("yyyy-MM-dd") + "' and '" + dtpDateTo.Value.ToString("yyyy-MM-dd") + "' AND (" + Mod + ") order by StudyDate desc"; } } CN1.OpenConnection(); CMD1 = new System.Data.Odbc.OdbcCommand(strSQL, CN1.DBConnection); //Dim dtst As New DataSet DicomServerDBDataSet1.PatientStudyViewDataTable table = new DicomServerDBDataSet1.PatientStudyViewDataTable(); System.Data.Odbc.OdbcDataAdapter adptr = new System.Data.Odbc.OdbcDataAdapter(); adptr.SelectCommand = CMD1; adptr.Fill(table); DataGridView1.Refresh(); DataGridView1.DataSource = null; DataGridView1.DataSource = table; this.Cursor = Cursors.Default; GridDesign(); CMD1.Dispose(); CN1.closeconnection(); }
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(); } }
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(); } }