DataGrid and OleDb Connection Test

1. Windows 10 x64 @ ASUS X450J

2. Visual Studio 2019

3. Install Microsoft Access Database Engine 2010 可轉散發套件

https://www.microsoft.com/zh-tw/download/details.aspx?id=13255

4. Northwind.xls (Customer Sheet)

Ex1 Console (.NET Framework)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace oledbEx1

{

    using System.Data;

    using System.Data.OleDb;

    class Program

    {

        static void Main(string[] args)

        {

            DataTable DT = OleDbGetTable("SELECT * FROM [Customers$]", "Northwind.xlsx");

            Console.WriteLine(DT.Rows.Count);

            Console.ReadLine();

        }

        static public DataTable OleDbGetTable(string SQL, string path1)

        {

            DataSet DS = new DataSet();

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

            conn.Open();

            OleDbDataAdapter apt = new OleDbDataAdapter(SQL, conn);

            apt.Fill(DS);

            conn.Close();

            return DS.Tables[0];

        }

    }

}

 Ex2 Form (.NET Framework)

namespace FormEx1

{

    using System.Data.OleDb;

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

            DataGridView dataGridView1 = new DataGridView();

            this.Controls.Add(dataGridView1);

            dataGridView1.Dock = DockStyle.Fill;

            DataTable DT = OleDbGetTable("SELECT * FROM [Customers$]", "Northwind.xlsx");

            dataGridView1.DataSource = DT.DefaultView;

        }

        public DataTable OleDbGetTable(string SQL, string path1)

        { ...}

  }

}

Ex3 Form (.NET Core 3.x)

nuget Add System.Data.OleD

Ex4 WPF (.NET Framework)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Data;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Imaging;

using System.Windows.Navigation;

using System.Windows.Shapes;

namespace WPFEx1

{

    using System.Data.OleDb;

    using System.Data;

    /// <summary>

    /// MainWindow.xaml 的互動邏輯

    /// </summary>

    public partial class MainWindow : Window

    {

        public MainWindow()

        {

            InitializeComponent();

            DataGrid dataGridView1 = new DataGrid();

            Grid G1 = new Grid();

            this.Content = G1;

            G1.Children.Add(dataGridView1);

            DataTable DT = OleDbGetTable("SELECT * FROM [Customers$];", "Northwind.xlsx");

            dataGridView1.ItemsSource = DT.DefaultView;

        }

        public DataTable OleDbGetTable(string SQL, string path1)

        {... }

   

    }

}

Ex5 WPF (.NET Core 3.x)

nuget Add System.Data.OleD

Ex6 Web (.NET Framework)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace WebApp1

{

    using System.Data;

    using System.Data.OleDb;

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            DataGrid dataGridView1 = new DataGrid();

            form1.Controls.Add(dataGridView1);

            DataTable DT = OleDbGetTable("SELECT * FROM [Customers$];", Server.MapPath("Northwind.xlsx"));

            dataGridView1.DataSource = DT.DefaultView;

            dataGridView1.DataBind();

       }

        public DataTable OleDbGetTable(string SQL, string path1)

        { ...   }

    }

}