C# OdbcDataAdapter.Fill方法代码示例

        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));
        }

        /// <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"]
                    );
            }
        }

    //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 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;
    }

    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 [email protected]
            // Create new OleDbDataAdapter
            OdbcDataAdapter oleda = new OdbcDataAdapter();

            oleda.SelectCommand = cmd;

            // Create a DataSet which will hold the data extracted from the worksheet.
            // DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(dt);
            /*      if(dt.Rows.Count>0)
                  {
                       ID = dt.Rows[0]["ID"].ToString(); //Where ColumnName is the Field from the DB that you want to display
                       name= dt.Rows[0]["Name"].ToString();
                       Address = dt.Rows[0]["Address"].ToString();
                       EmailAddress = dt.Rows[0]["emailaddress"].ToString();
                       if(EmailAddress!=null)
                       {
                        if(EmailAddress==)
                       }
                       EmailContent = dt.Rows[0]["emailcontent"].ToString();
                   }
              */
            DataView dv = new DataView(dt);
            // dv.Sort = "emailcontents";
            // dv.Sort = "Name";

            // Bind the data to the GridView
            Grdexcel.DataSource = dv;
            Grdexcel.DataBind();
            // cmd = new OdbcCommand("delete FROM [ter$] where Address like 'M%'", oledbConn);
            // cmd.ExecuteNonQuery();
            //cmd.CommandType
        }
        catch (Exception ex)
        {
            Alert.Show("Sorry");
        }
        finally
        {
            // Close connection
            oledbConn.Close();
        }
        }
    }

        /// <summary>
        /// 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 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 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 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 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 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();
    }