C# System.Data.OleDb.OleDbDataAdapter.Fill方法代码示例

 public DataTable SelectToDataTable(string sql)
 {
     //set connection
     System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
     con.ConnectionString = connectString;
     //set commandtext
     System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
     command.CommandText = sql;
     command.Connection = con;
     //set adapter
     System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
     adapter.SelectCommand = command;
     //creat a datatable
     DataTable dt = new DataTable();
     try
     {
         //open this connection
         con.Open();
         adapter.Fill(dt);
     }
     catch (Exception ex)
     {
         //throw new Exception
         con.Close();
     }
     finally
     {
         //close this connection
         con.Close();
     }
     //return a datatable
     return dt;
 }

        protected void Application_Start(Object sender, EventArgs e)
        {
            System.Data.DataSet ds;
            System.Data.OleDb.OleDbConnection oleDbConnection1;
            System.Data.OleDb.OleDbDataAdapter daAttendees;
            System.Data.OleDb.OleDbDataAdapter daRooms;
            System.Data.OleDb.OleDbDataAdapter daEvents;

            oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
            oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Inetpub\wwwroot\PCSWebApp3\PCSWebApp3.mdb;Mode=ReadWrite|Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
            oleDbConnection1.Open();
            ds = new DataSet();
            daAttendees = new System.Data.OleDb.OleDbDataAdapter(
                "SELECT * FROM Attendees", oleDbConnection1);
            daRooms = new System.Data.OleDb.OleDbDataAdapter(
                "SELECT * FROM Rooms", oleDbConnection1);

            daEvents = new System.Data.OleDb.OleDbDataAdapter(
                "SELECT * FROM Events", oleDbConnection1);
            daAttendees.Fill(ds, "Attendees");
            daRooms.Fill(ds, "Rooms");
            daEvents.Fill(ds, "Events");
            oleDbConnection1.Close();

            Application["ds"] = ds;
        }

 public System.Collections.Generic.List<string> GetColumnNames(string file, string table)
 {
     System.Data.DataTable dataSet = new System.Data.DataTable();
     string connString = "";
     if (Global.filepassword != "")
     {
         if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
             connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
         else
             connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
     }
     else
     {
         if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
             connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file;
         else
             connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Persist Security Info=False;";
     }
     using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString))
     {
         connection.Open();
         System.Data.OleDb.OleDbCommand Command = new System.Data.OleDb.OleDbCommand("SELECT * FROM " + table, connection);
         using (System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter(Command))
         {
             dataAdapter.Fill(dataSet);
         }
     }
     System.Collections.Generic.List<string> columns = new System.Collections.Generic.List<string>();
     for (int i = 0; i < dataSet.Columns.Count; i++)
     {
         columns.Add(dataSet.Columns[i].ColumnName);
     }
     return columns;
 }

        public int AddEvent(String eventName, String eventRoom,
			String eventAttendees, String eventDate)
        {
            System.Data.OleDb.OleDbConnection oleDbConnection1;
            System.Data.OleDb.OleDbDataAdapter daEvents;
            DataSet ds;

            oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
            oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\\Inetpub\\wwwroot\\PCSWebApp3\\PCSWebApp3.mdb";
            String oleDbCommand = "INSERT INTO Events (Name, Room, AttendeeList," +
                " EventDate) VALUES ('" + eventName + "', '" +
                eventRoom + "', '" + eventAttendees + "', '" +
                eventDate + "')";
            System.Data.OleDb.OleDbCommand insertCommand =
                new System.Data.OleDb.OleDbCommand(oleDbCommand,
                oleDbConnection1);
            oleDbConnection1.Open();

            int queryResult = insertCommand.ExecuteNonQuery();
            if (queryResult == 1)
            {
                daEvents = new System.Data.OleDb.OleDbDataAdapter(
                    "SELECT * FROM Events", oleDbConnection1);
                ds = (DataSet)Application["ds"];
                ds.Tables["Events"].Clear();
                daEvents.Fill(ds, "Events");
                Application.Lock();
                Application["ds"] = ds;
                Application.UnLock();
                oleDbConnection1.Close();
            }
            return queryResult;
        }

 public System.Data.DataTable ExecuteScalar(string sql, ADODB.Connection cn2)
 {
     rs.Open(sql, cn2, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, -1);
     System.Data.OleDb.OleDbDataAdapter ol = new System.Data.OleDb.OleDbDataAdapter();
     System.Data.DataTable dt = new System.Data.DataTable();
     ol.Fill(dt, rs);
     rs.Close();
     return dt;
 }

 public static DataTable GetDataTableExcel(string strFileName, string Table)
 {
     System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");
     conn.Open();
     string strQuery = "SELECT * FROM [" + Table + "]";
     System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
     System.Data.DataSet ds = new System.Data.DataSet();
     adapter.Fill(ds);
     return ds.Tables[0];
 }

        protected void Button1_Click(object sender, EventArgs e)
        {
            var dt2 = new DataTable();

            System.Data.OleDb.OleDbDataAdapter ol = new System.Data.OleDb.OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            ol.Fill(dt2, product.selectTest2(test.cn));
            gvProduct.DataSource = dt2;
            gvProduct.DataBind();
        }

        public DataSet Method1()
        {
            string s = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Northwind1.mdb") + ";Persist Security Info=False";
            string z = "select * from Клиенты";
            System.Data.OleDb.OleDbConnection olcon = new System.Data.OleDb.OleDbConnection(s);
            System.Data.OleDb.OleDbCommand olcom = new System.Data.OleDb.OleDbCommand(z, olcon);
            System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter(olcom);
            System.Data.DataSet DS = new System.Data.DataSet();
            DA.Fill(DS);

            return DS;
        }

        public DataTable GetSheetDataForInsert(string qrySecondPart)
        {
            var conn = new System.Data.OleDb.OleDbConnection(
                String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';", _path));
            DataSet ds = new DataSet();

            var adapter = new System.Data.OleDb.OleDbDataAdapter(
                qrySecondPart, conn);
            adapter.Fill(ds);

            return ds.Tables[0];
        }

 public static DataTable GetDataTable(string strFileName)
 {
     ADODB.Connection oConn = new ADODB.Connection();
     oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";", "", "", 0);
     string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
     ADODB.Recordset rs = new ADODB.Recordset();
     System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
     DataTable dt = new DataTable();
     rs.Open(strQuery, "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";",
         ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
     adapter.Fill(dt, rs);
     return dt;
 }

 public System.Data.DataTable Read(string filename)
 {
     var excelDataAdapter = new System.Data.OleDb.OleDbDataAdapter();
     _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + "; Extended Properties = Excel 8.0;";
     var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString);
     excelConn.Open();
     var dtProtocol = new System.Data.DataTable { TableName = "Protocol" };
     var excelCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [протокол плавки$]", excelConn);
     excelDataAdapter.SelectCommand = excelCommand;
     excelDataAdapter.Fill(dtProtocol);
     excelConn.Close();
     return dtProtocol;
 }

 public static DataTable GetDataTableFromCSV(string strFileName)
 {
     try
     {
         System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
         conn.Open();
         string strQuery = "Select * from [" + System.IO.Path.GetFileName(strFileName) + "]";
         System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
         System.Data.DataSet ds = new System.Data.DataSet();
         da.Fill(ds);
         return ds.Tables[0];
     }
     catch (Exception ex) { }
     return new DataTable();
 }

        public void ConvertExcelToCsv(int worksheetNumber = 1)
        {
            if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
            if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

            // connection string
            var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
            var cnn = new System.Data.OleDb.OleDbConnection(cnnStr);

            // get schema, then data
            var dt = new DataTable();
            try
            {
                cnn.Open();
                var schemaTable = cnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
                string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
                string sql = String.Format("select * from [{0}]", worksheet);
                var da = new System.Data.OleDb.OleDbDataAdapter(sql, cnn);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                throw e;
            }
            finally
            {
                // free resources
                cnn.Close();
            }

            // write out CSV data
            using (var wtr = new StreamWriter(csvOutputFile))
            {
                foreach (DataRow row in dt.Rows)
                {
                    bool firstLine = true;
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
                        var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
                        wtr.Write(String.Format("\"{0}\"", data));
                    }
                    wtr.WriteLine();
                }
            }
        }

        private DataSet GetMeSomeDataIntoDataSet(string sql)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Jason\Dropbox\Devry\CIS336\SimpleQueryAnalyzer\SimpleQueryAnalyzer\northwind.mdb;";
            conn.Open();
            System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = sql;

            System.Data.DataSet ds = new DataSet();
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();

            da.SelectCommand = comm;
            da.Fill(ds);

            return ds;
        }

        private static DataSet CreateDataSet()
        {
            DataSet ds = new DataSet();

            IDbConnection connection = new System.Data.OleDb.OleDbConnection();
            connection.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Database Password=;Data Source=""NWind.mdb"";Password=;Jet OLEDB:Engine Type=3;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False";

            IDbDataAdapter adapterSender = new System.Data.OleDb.OleDbDataAdapter();
            IDbCommand oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
            oleDbSelectCommand1.Connection = connection;
            adapterSender.SelectCommand = oleDbSelectCommand1;

            oleDbSelectCommand1.CommandText = "SELECT TOP 10 OrderID, CustomerID , Freight, OrderDate, ShipVia  FROM Orders";
            //oleDbSelectCommand1.CommandText = "SELECT *  FROM Orders";
            adapterSender.Fill(ds);
            ds.Tables[0].TableName = "Orders";

            return ds;
        }