C# OleDb.OleDbDataAdapter类代码示例

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

        //計算每個點的Frequency Table
        private void BFT_Click(object sender, EventArgs e)
        {
            DataTable FreqTable = new DataTable();
            GetDataSet.Open();
            OleDbDataAdapter dafretable = new OleDbDataAdapter("SELECT * FROM [01.csv]", GetDataSet);
            dafretable.Fill(FreqTable);

            foreach (DataRow row in FreqTable.Rows)
            {
                node[(int)row[0]].Visit[(int)row[1]] = node[(int)row[0]].Visit[(int)row[1]] + 1;  //計算每個點拜訪每個地點分別次數
                node[(int)row[0]].VTotal = node[(int)row[0]].VTotal + 1;                          //計算每個點總拜訪次數
            }

            for (int x = 1; x < 100; x++)
            {
                for (int y = 1; y < (locanum + 1); y++)
                {
                    if (node[x].VTotal != 0)
                    {
                        node[x].FTable[y] = (node[x].Visit[y] / node[x].VTotal) * 10;                  //計算頻率
                    }

                }
            }
            //label1.Text = node[121].FTable[363].ToString();
            GetDataSet.Close();
            state.Text = "BFT over.";
        }

        private void cbxclub_DropDownOpened(object sender, EventArgs e)
        {
            try
            {
                if (cbxseason.SelectedItem == null || cbxzone.SelectedItem == null || cbxdivision.SelectedItem == null)
                {
                    MessageBox.Show("Select Season, Zone, Division");
                }

                string strRetrieve = "";
                strRetrieve = "select * from clubdetails";
                //  cbxclub.Items.Clear();
                OleDbDataAdapter adpt = new OleDbDataAdapter(strRetrieve, Database.getConnection());
                DataTable dt = new DataTable();
                adpt.Fill(dt);

                foreach (DataRowView dr in dt.DefaultView)
                {
                    //   List<Team> lstFilter = Database.GetEntityList<Team>( false, false,false,Database.getConnection(),RecordStatus true);
                    //cbxclub.Items.Add(dr["ClubName"]);
                    //cbxteam.Items.Refresh();

                    if (!cbxclub.Items.Contains(dr["ClubName"]))// For remove list duplicacy
                    {
                        cbxclub.Items.Add(dr["ClubName"]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Create an Excel file with 2 columns: name and score:
        //Write a program that reads your MS Excel file through
        //the OLE DB data provider and displays the name and score row by row.

        static void Main()
        {
            OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=D:\Telerik\DataBases\HW\ADONET\06. ReadExcel\Table.xlsx;Extended Properties=""Excel 12.0 XML;HDR=Yes""");
            OleDbCommand myCommand = new OleDbCommand("select * from [Sheet1$]", dbConn);
            dbConn.Open();
            //First way
            //using (dbConn) - I think it is better to use dbConn in using clause, but for the demo issues i dont use using.
            //{
            OleDbDataReader reader = myCommand.ExecuteReader();

            while (reader.Read())
            {
                string name = (string)reader["Name"];
                double score = (double)reader["Score"];
                Console.WriteLine("{0} - score: {1}", name, score);
            }
            //}

            dbConn.Close();
            //Second way
            dbConn.Open();
            Console.WriteLine();
            Console.WriteLine("Second Way");
            Console.WriteLine("----------");
            DataTable dataSet = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", dbConn);
            adapter.Fill(dataSet);

            foreach (DataRow item in dataSet.Rows)
            {
                Console.WriteLine("{0}|{1}", item.ItemArray[0], item.ItemArray[1]);
            }
            dbConn.Close();
        }

        private static void PrintSpreadsheet(OleDbConnectionStringBuilder connestionString)
        {
            DataSet sheet1 = new DataSet();
            using (OleDbConnection connection = new OleDbConnection(connestionString.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sheet1$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.Fill(sheet1);
                }
                connection.Close();
            }
            
            var table = sheet1.Tables[0];

            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    Console.Write("{0, -20} ", row[column]);
                }

                Console.WriteLine();
            }
        }

        private void FormFirma_Load(object sender, EventArgs e)
        {
            PutBaze = System.IO.File.ReadAllText(Application.StartupPath + "\\Ponuda\\BazaPonuda.txt");
            string PutKriterij =System.IO.File.ReadAllText(@"privPonuda.txt");

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+PutBaze;

            //----------SQL instrukcija-----------\\

            string sql = "SELECT * FROM sve WHERE Firma LIKE '" + PutKriterij + "%'";

            //klase za povezivanje na ACCESS bazu podataka//
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
            //\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\

             ds = new DataSet();         //kreira noi objekt(kopiju) DataSet()-a
            conn.Open();                        //otvara spoj s bazom podataka

            adapter.Fill(ds, "sve");       //puni se DataSet s podacima tabele t_razred
            conn.Close();                       //zatvara se baza podataka

            //nakon zatvaanja BP imamo odgovarajuće podatke u ds objektu ( DataSet() )
            dataGridView2.DataSource = ds;      //upisivanje podataka id ds u dataGridView2
            dataGridView2.DataMember = "sve";
            ukupnaCijenaDataGridViewTextBoxColumn.DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("de-DE");

            this.dataGridView2.Sort(firmaDataGridViewTextBoxColumn, ListSortDirection.Ascending);
            System.IO.File.Delete(@"privPonuda.txt");
        }

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

        /// <summary>
        /// Opens an Excel file and imports worksheet into a DataTable.  Worksheet Name is required.
        /// </summary>
        public static DataTable Build(string FilePath, string WorksheetName)
        {
            if (Path.GetExtension(FilePath) == ".xls")
            {
                // If anything goes wrong, "using" will force disposal of the connection to the file
                using (OleDbConnection conn = BuildExcelConnection(FilePath))
                {
                    // "Connect" to the XLS file
                    conn.Open();

                    // Get a DataAdapter to the query
                    string ExcelQuery = String.Format(SelectWorksheetQueryTemplate, WorksheetName);
                    OleDbDataAdapter Adapter = new OleDbDataAdapter(ExcelQuery, conn);

                    // Populate DataTable using DataAdapter
                    DataTable dataTable = new DataTable();
                    Adapter.Fill(dataTable);

                    // Close the connection
                    conn.Close();

                    // Finished
                    return dataTable;
                }
            }
            if (Path.GetExtension(FilePath) == ".xlsx")
            {
                return OpenXmlExcelToDataTableBuilder.Build(FilePath, WorksheetName);
            }

            throw new ArgumentException("Invalid file extension specified on Excel data file:" + FilePath);
        }

        /// <summary>
        /// 从EXCEL中获取数据(放入dataset中)
        /// </summary>
        /// <param name="postedFile"></param>
        /// <param name="context"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet GetDataFromUploadFile(this HttpPostedFile postedFile, HttpContext context, string tableName)
        {
            string directory = context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY);

            if (!Directory.Exists(directory))
                Directory.CreateDirectory(directory);

            string filename = postedFile.FileName;
            //将文件上传至服务器
            postedFile.SaveAs(context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename);

            string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

            //string sqlin = "SELECT * FROM [" + "ConstructPlanDevice" + "$]";
            //OleDbCommand oleCommand = new OleDbCommand(sqlin, new OleDbConnection(conn));

            //OleDbDataAdapter adapterIn = new OleDbDataAdapter(oleCommand);
            //DataSet dsIn = new DataSet();
            //adapterIn.Fill(dsIn, tableName);

            OleDbConnection conn1 = new OleDbConnection(conn);
            conn1.Open();
            string name = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+name+"]", conn1);
            DataSet dsIn1 = new DataSet();
            odda.Fill(dsIn1, tableName);
            conn1.Close();
            return dsIn1;
        }

 /// <summary>
 /// initialization casting for InitializeDataAccess()
 /// </summary>
 /// <param name="type"></param>
 /// <param name="ConnectionString"></param>
 /// <param name="Query"></param>
 private void castProvider(ProviderType type, string ConnectionString, string Query = null)
 {
     switch (type)
     {
         case ProviderType.Oledb:
             conn = new OleDbConnection(ConnectionString);
             cmd = new OleDbCommand(Query, (OleDbConnection)conn);
             da = new OleDbDataAdapter();
             break;
         case ProviderType.Odbc:
             conn = new OdbcConnection(ConnectionString);
             cmd = new OdbcCommand(Query, (OdbcConnection)conn);
             da = new OdbcDataAdapter();
             break;
         case ProviderType.SqlClient:
             conn = new SqlConnection(ConnectionString);
             cmd = new SqlCommand(Query, (SqlConnection)conn);
             da = new SqlDataAdapter();
             break;
         //case ProviderType.OracleClient:
         //    conn = new OracleConnection(ConnectionString);
         //    cmd = new OracleCommand(Query,(OracleConnection)conn);
         //    break;
     }
 }

        public Empresa ObtenerEmpresa(string RUC)
        {
            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;
            obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc ='" + RUC + "'";
            obj_Comando.CommandType = CommandType.Text;
            obj_Adapter.SelectCommand = obj_Comando;
            obj_Adapter.Fill(obj_Data);
            obj_Conexion.Close();

            Empresa empresa = new Empresa(); ;
            foreach (var dr_Fila in obj_Data.Tables[0].Rows)
            {
                DataRow fila = (DataRow)dr_Fila;
                empresa.RUC = fila[0].ToString();
                empresa.nombrecomercial = fila[1].ToString();
                empresa.direccion = fila[5].ToString();
                empresa.telefono = fila[6].ToString();
                empresa.Estado = fila[3].ToString();
            }

            return empresa;
        }

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

 public DataView ExceltoDataView(string strFilePath)
 {
     DataView dv;
     try
     {
         OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");
         conn.Open();
         object[] CSs0s0001 = new object[4];
         CSs0s0001[3] = "TABLE";
         DataTable tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, CSs0s0001);
         string tableName = Convert.ToString(tblSchema.Rows[0]["TABLE_NAME"]);
         if (tblSchema.Rows.Count > 1)
         {
             tableName = "sheet1$";
         }
         string sql_F = "SELECT * FROM [{0}]";
         OleDbDataAdapter adp = new OleDbDataAdapter(string.Format(sql_F, tableName), conn);
         DataSet ds = new DataSet();
         adp.Fill(ds, "Excel");
         dv = ds.Tables[0].DefaultView;
         conn.Close();
     }
     catch (Exception)
     {
         Exception strEx = new Exception("請確認是否使用模板上傳(上傳的Excel中第一個工作表名稱是否為Sheet1)");
         throw strEx;
     }
     return dv;
 }

        public static DataTable GetExcelDataTable(string filePath, string sql)
        {
            try
            {
                //Office 2003
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;Readonly=0'");

                //Office 2007
                OleDbConnection conn =
                    new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                                        ";Extended Properties='Excel 12.0 Xml;HDR=YES'");

                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dt.TableName = "tmp";
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                MessageBox.Show("Please input correct SQL Command for your file!");
                return null;
            }
        }