C# Odbc.OdbcDataAdapter类代码示例

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

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

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

    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 ExecuteQuery(string dsnConnection, string query, params object[] queryParams)
        {
            try
            {
                using (var connection = new OdbcConnection(dsnConnection))
                {
                    connection.Open();
                    var queryCommand = connection.CreateCommand();
                    queryCommand.CommandText = query;
                    queryCommand.CommandTimeout = 1;

                    var i = 0;
                    foreach (var queryParam in queryParams)
                    {
                        queryCommand.Parameters.Add("@p" + i, OdbcType.DateTime).Value = queryParam;
                        i++;
                    }

                    var dataSet = new DataSet();
                    var da = new OdbcDataAdapter(queryCommand);
                    da.Fill(dataSet);
                    return dataSet.Tables[0];
                }
            } catch(Exception ex)
            {
                Logging.LogError(1, "Error executing query {0} on connection {1} message is {2}", query, dsnConnection, ex.Message);
                throw;
            }
        }

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

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

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

 /// <summary>
 /// Required method for Designer support - do not modify
 /// the contents of this method with the code editor.
 /// </summary>
 private void InitializeComponent()
 {
     this.odbcDataAdapter = new System.Data.Odbc.OdbcDataAdapter();
     this.odbcSelectCommand1 = new System.Data.Odbc.OdbcCommand();
     this.odbcInsertCommand1 = new System.Data.Odbc.OdbcCommand();
     this.odbcConnection = new System.Data.Odbc.OdbcConnection();
     //
     // odbcDataAdapter
     //
     this.odbcDataAdapter.InsertCommand = this.odbcInsertCommand1;
     this.odbcDataAdapter.SelectCommand = this.odbcSelectCommand1;
     this.odbcDataAdapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                               new System.Data.Common.DataTableMapping("Table", "users", new System.Data.Common.DataColumnMapping[] {
                                                                                                                                                                                                        new System.Data.Common.DataColumnMapping("id", "id"),
                                                                                                                                                                                                        new System.Data.Common.DataColumnMapping("user", "user"),
                                                                                                                                                                                                        new System.Data.Common.DataColumnMapping("password", "password")})});
     //
     // odbcSelectCommand1
     //
     this.odbcSelectCommand1.CommandText = "SELECT id, `user`, password FROM users";
     this.odbcSelectCommand1.Connection = this.odbcConnection;
     //
     // odbcInsertCommand1
     //
     this.odbcInsertCommand1.CommandText = "INSERT INTO users(`user`, password) VALUES (?, ?)";
     this.odbcInsertCommand1.Connection = this.odbcConnection;
     this.odbcInsertCommand1.Parameters.Add(new System.Data.Odbc.OdbcParameter("user", System.Data.Odbc.OdbcType.VarChar, 30, "user"));
     this.odbcInsertCommand1.Parameters.Add(new System.Data.Odbc.OdbcParameter("password", System.Data.Odbc.OdbcType.VarChar, 30, "password"));
     //
     // odbcConnection
     //
     this.odbcConnection.ConnectionString = "STMT=;OPTION=3;DSN=test;UID=root;PASSWORD=;DESC=MySQL ODBC 3.51 Driver DSN;DATABA" +
         "SE=test;SERVER=localhost;PORT=3306";
 }

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