C# OleDbDataAdapter.Fill方法代码示例

        public static int SaveRecord(string sql)
        {
            const int rv = 0;
            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
                using (var conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
                    var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
                    comm.ExecuteNonQuery();

                    var ds = new DataSet();
                    var adapt = new OleDbDataAdapter(cmGetID);
                    adapt.Fill(ds);
                    adapt.Dispose();
                    cmGetID.Dispose();

                    return int.Parse(ds.Tables[0].Rows[0][0].ToString());

                }
            }
            catch (Exception)
            {
            }
            return rv;
        }

        //=====================================================================
        /// <summary>
        /// Constructor
        /// </summary>
        public CheckBoxListTestForm()
        {
            InitializeComponent();

            demoData = new DataSet();
            productData = new DataSet();

            try
            {
                using(var dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\TestData.mdb"))
                {
                    // Load some data for the demo
                    OleDbCommand cmd = new OleDbCommand("Select * From DemoTable Order By Label", dbConn);
                    cmd.CommandType = CommandType.Text;
                    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

                    adapter.Fill(demoData);

                    // Use a named table for this one
                    adapter.TableMappings.Add("Table", "ProductInfo");
                    cmd.CommandText = "Select * From ProductInfo Order By ProductName";
                    adapter.Fill(productData);
                }
            }
            catch(OleDbException ex)
            {
                MessageBox.Show(ex.Message);
            }

            cboDataSource.SelectedIndex = 0;

            pgProps.SelectedObject = cblDemo;
            pgProps.Refresh();
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable DT = new DataTable("GeneralTable");
            DataTable DT2 = new DataTable("GeneralTable");
            OleDbDataAdapter DA = new OleDbDataAdapter("SELECT id_kind_m, name_kind_m FROM kind_m", MySchool.feedbackConnection);
            try
            {
                DA.Fill(DT);
                DDLMSGType.DataSource = DT;
                DDLMSGType.DataTextField = "name_kind_m";
                DDLMSGType.DataValueField = "id_kind_m";
                DDLMSGType.DataBind();
                DA.SelectCommand.CommandText = "SELECT TypeID, TypeName FROM JobTypes";
                DA.Fill(DT2);
                DDLSenderType.DataSource = DT2;
                DDLSenderType.DataTextField = "TypeName";
                DDLSenderType.DataValueField = "TypeID";
                DDLSenderType.DataBind();

            }
            catch (Exception ex)
            {
                LblStatus.Text = ex.Message;
                LblStatus.ForeColor = System.Drawing.Color.Red;
                LblStatus.Visible = true;
            }
        }
    }

 public void GetData()
 {
     try
     {
         con = new OleDbConnection(cs);
         con.Open();
         cmd = new OleDbCommand("SELECT StockId as [Stock ID], (productName) as [Product Name],Features,sum(Quantity) as [Quantity],Price,sum(TotalPrice) as [Total Price] from Config,Stock where Config.ConfigID=Stock.ConfigID group by Stockid, productname,features,price having sum(Quantity > 0)  order by Productname", con);
         var myDA = new OleDbDataAdapter(cmd);
         var myDataSet = new DataSet();
         myDA.Fill(myDataSet, "Stock");
         myDA.Fill(myDataSet, "Config");
         dataGridView1.DataSource = myDataSet.Tables["Stock"].DefaultView;
         dataGridView1.DataSource = myDataSet.Tables["Config"].DefaultView;
         con.Close();
         string[] header = new string[]
         {
             "รหัส","ชื่อสินค้า","รายละเอียด","จำนวน","ราคา","รวม"
         };
         for (int i = 0; i < header.Length; i++)
         {
             dataGridView1.Columns[i].HeaderText = header[i];
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "ล้มเหลว", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
 }

        /**
         * Function that accesses the database and creates a DataTable of Parts with the matching SN
         *
         * @param SN           SN to search for
         * @return dt           Table of vehicles with matching SN
         */
        public DataTable SearchPart( string SN )
        {
            OleDbCommand viewPart = cn.CreateCommand();
            OleDbCommand viewEngine = cn.CreateCommand();
            OleDbCommand viewTire = cn.CreateCommand();
            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter();

            viewPart.CommandText = "SELECT * FROM Part WHERE Part.SerialNumber = @SerialNumber";
            viewEngine.CommandText = "SELECT Part.SerialNumber, VIN, PartName, Manufacturer, HorsePower, Cylinders FROM Part INNER JOIN Engine ON Part.SerialNumber = Engine.SerialNumber WHERE Part.SerialNumber = @SerialNumber";
            viewTire.CommandText = "SELECT Part.SerialNumber, VIN, PartName, Manufacturer, Type, TireSize FROM Part INNER JOIN Tire ON Part.SerialNumber = Tire.SerialNumber WHERE Part.SerialNumber = @SerialNumber";
            viewPart.Parameters.AddWithValue("@SerialNumber", SN);
            viewEngine.Parameters.AddWithValue("@SerialNumber", SN);
            viewTire.Parameters.AddWithValue("@SerialNumber", SN);

            da.SelectCommand = viewEngine;
            da.Fill(dt);
            da.SelectCommand = viewTire;
            da.Fill(dt);
            if (dt.Rows.Count == 0 )
            {
                dt.Columns.Clear();
                da.SelectCommand = viewPart;
                da.Fill(dt);
            }
            return dt;
        }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="commandType"></param>
 /// <param name="commandText"></param>
 /// <param name="commandParameters"></param>
 /// <param name="ds"></param>
 /// <param name="tableName"></param>
 /// <returns></returns>
 public override DataSet ExecuteDataset(CommandType commandType, string commandText,
                                        QueryParameterCollection commandParameters, DataSet ds, string tableName)
 {
     try
     {
         var cmd = new OleDbCommand();
         PrepareCommand(cmd, commandType, commandText, commandParameters);
         var adapter = new OleDbDataAdapter(cmd);
         if (Equals(tableName, null) || (tableName.Length < 1))
         {
             adapter.Fill(ds);
         }
         else
         {
             adapter.Fill(ds, tableName);
         }
         base.SyncParameter(commandParameters);
         cmd.Parameters.Clear();
         return ds;
     }
     catch
     {
         if (trans != null)
         {
             RollbackTransaction();
         }
         throw;
     }
     finally
     {
         Close();
     }
 }

        public static void Main(string[] args)
        {
            WriteToEventLog("Calling AEPM Service...");

            var dt = new DataTable();
            var dset = new DataSet();
            Guid = args[0];

            //retrieve any records needing updating
            try
            {

                 using(var conn = new OleDbConnection(ConfigurationManager.AppSettings["AS400ConnectionStringDev"]))

                {
                    string sql = GetUnprocessMasterRecsString();
                    var objDataAdapter = new OleDbDataAdapter();
                    var cmd = new OleDbCommand(sql, conn);

                    objDataAdapter.SelectCommand = cmd;
                    objDataAdapter.SelectCommand.CommandTimeout = 0;

                    dt.Clear();
                    dset.Clear();

                    objDataAdapter.Fill(dt);
                    objDataAdapter.Fill(dset, "currentSelections");

                    var cb = new OleDbCommandBuilder(objDataAdapter);
                    AddParameters(cb);

                    //update records to 'S' for submitted
                    for (int i = 0; i < dset.Tables["currentSelections"].Rows.Count; i++)
                    {
                        dset.Tables["currentSelections"].Rows[i]["G_RETRN"] = 'S';
                    }

                    objDataAdapter.Update(dset, "currentSelections");

                    //submit records asynch
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        TestAsync(dt, i);
                    }

                }
            }
            catch (Exception ex)
            {
                WriteToEventLog(ex);
            }
        }

    public static string[] BilgiAl(string urunid)
    {
        string sonuc = "işleminiz gerçekleştirildi.";
        string[] s = new string[1];
        cmdd.Connection = baglanti;
        if (baglanti.State == ConnectionState.Open)
            baglanti.Close();
        try
        {
            baglanti.Open();
            cmdd.CommandText = "select * from sepet where URUNID = " + urunid;
            adapter = new OleDbDataAdapter(cmdd);
            sepettable = new DataTable();
            adapter.Fill(sepettable);
            int yandex = 0;
            int yindex = 0;
            //sonuc = sepettable.Rows[0][2].ToString();
            for(int i = 0; i < sepettable.Rows.Count; i++)
            {
                cmdd.CommandText = "select * from sepet where SIPID = " + sepettable.Rows[i][1].ToString() + " and not URUNID = " + urunid;
                adapter2 = new OleDbDataAdapter(cmdd);
                tmptable = new DataTable();
                adapter.Fill(tmptable);

                yandex += tmptable.Rows.Count;
            }

            s = new string[yandex];

            for (int i = 0; i < sepettable.Rows.Count; i++)
            {
                cmdd.CommandText = "select * from sepet where SIPID = " + sepettable.Rows[i][1].ToString() + " and not URUNID = " + urunid;
                adapter2 = new OleDbDataAdapter(cmdd);
                tmptable = new DataTable();
                adapter.Fill(tmptable);

                for (int j = 0; j < tmptable.Rows.Count; j++)
                {
                    s[yindex++] = tmptable.Rows[j][2].ToString();
                }
            }
        }
        catch (Exception ex)
        {
            sonuc = "hata : " + ex.Message;
        }

        baglanti.Close();
        return s;
    }

    protected void btTaiLen_Click1(object sender, EventArgs e)
    {
        if (Request.Cookies["ADMIN"] == null) return;
        if (FileUpload1.HasFile)
            try
            {
                string path = Server.MapPath(@"EXCEL\") + "Question_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xlsx";
                FileUpload1.SaveAs(path);
                string txt = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;", path);
                OleDbConnection con = new OleDbConnection(txt);
                con.Open();
                txt = "Select * FROM [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(txt, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.Close();

                gvCauHoi.DataSource = ds;
                gvCauHoi.DataBind();
                string temp;
                foreach (GridViewRow dr in gvCauHoi.Rows)
                {
                    temp = Server.HtmlDecode(dr.Cells[0].Text);
                    if (temp.StartsWith("#"))
                        dr.CssClass = "Q1";
                    else if (temp.StartsWith("$")) // Không đảo phương án trả lời
                        dr.CssClass = "Q2";
                    else if (temp.StartsWith("*")) // Phương án trả lời đúng
                        dr.CssClass = "A1";
                }
            }
            catch (Exception ex) { lbError.Text = ex.Message; }
    }

        public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow)
        {
            var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName);

            using (var excelConnection = new OleDbConnection(connectionString))
            {
                excelConnection.Open();

                if (sheetName == null)
                {
                    var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (excelSchema != null)
                    {
                        sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
                    }
                }

                var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection);

                using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
                {
                    var dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            actionForEachRow(reader);
                        }
                    }
                }
            }
        }

 /// <summary>
 /// 执行查询语句，返回DataSet
 /// </summary>
 /// <param name="SQLString">查询语句</param>
 /// <returns>DataSet</returns>
 public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms)
 {
     using (OleDbConnection connection = new OleDbConnection(connectionString))
     {
         OleDbCommand cmd = new OleDbCommand();
         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
         using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
         {
             DataSet ds = new DataSet();
             try
             {
                 da.Fill(ds, "ds");
                 cmd.Parameters.Clear();
             }
             catch (OleDbException e)
             {
                 //LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms });
                 throw;
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
             return ds;
         }
     }
 }

        private void DisplayStudents_Load(object sender, EventArgs e)
        {
            connection = new OleDbConnection();
            command = new OleDbCommand();
            adapter = new OleDbDataAdapter();
            dataset = new DataSet();

            connection.ConnectionString =
                @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Students.accdb;" +
                "Persist Security Info=False";

            command.Connection = connection;
            command.CommandText = "SELECT * FROM Student";

            adapter.SelectCommand = command;

            try
            {
                adapter.Fill(dataset, "Student");
            }
            catch (OleDbException)
            {
                MessageBox.Show("Error occured while connecting to database.");
                Application.Exit();
            }

            PopulateFields(0);
        }

        private void Autocomplete()
        {
            try
            {
                con = new OleDbConnection(cs);
                con.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT distinct ProductName FROM product", con);
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds, "Product");
                AutoCompleteStringCollection col = new AutoCompleteStringCollection();
                int i = 0;
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    col.Add(ds.Tables[0].Rows[i]["productname"].ToString());

                }
                txtProductName.AutoCompleteSource = AutoCompleteSource.CustomSource;
                txtProductName.AutoCompleteCustomSource = col;
                txtProductName.AutoCompleteMode = AutoCompleteMode.Suggest;

                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

    public DataSet GetDataSet(string filepath, string excelFileExtension)
    {
        try {
            System.Data.OleDb.OleDbConnection oledbcon = null;
            string strConn = string.Empty;
            switch (excelFileExtension.Trim()) {
                case "xls":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\"");
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
                    break;
                case "xlsx":
                    oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
                   + ";Extended Properties=Excel 12.0;";
                    break;
            }

            //excel
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
            System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon);
            //select
            DataSet ds = new DataSet();
            oledbAdaptor.Fill(ds);
            oledbcon.Close();
            return ds;
        } catch (Exception ex) {
            throw ex;
        }
    }

        public ICollection<Empresa> ConsultarEmpresa(string RUC, string nombreComercial)
        {
            OleDbConnection obj_Conexion = new OleDbConnection();
            OleDbCommand obj_Comando = new OleDbCommand();
            OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
            DataSet obj_Data = new DataSet();
            obj_Conexion.ConnectionString = "Server=VIRTUALXP-50904\\SQL2008; Provider=SQLOLEDB; User ID=sa; Initial Catalog=Ventas; password=royal2008;";
            obj_Conexion.Open();
            obj_Comando.Connection = obj_Conexion;
            if (RUC == null && nombreComercial == null)
                obj_Comando.CommandText = "Select * From Contribuyentes";
            else if (RUC != null && nombreComercial == null)
                obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc Like '%" + RUC + "%'";
            else
                obj_Comando.CommandText = "Select * From Contribuyentes Where RazonSocial Like '%" + nombreComercial + "%'";
            obj_Comando.CommandType = CommandType.Text;
            obj_Adapter.SelectCommand = obj_Comando;
            obj_Adapter.Fill(obj_Data);
            obj_Conexion.Close();

            List<Empresa> empresas = new List<Empresa>();
            Empresa empresa;
            foreach (var dr_Fila in obj_Data.Tables[0].Rows)
            {
                DataRow fila = (DataRow)dr_Fila;
                empresa = new Empresa();
                empresa.RUC = fila[0].ToString();
                empresa.nombrecomercial = fila[1].ToString();
                empresa.Estado = fila[3].ToString();
                empresas.Add(empresa);
            }

            return empresas;
        }