C# OleDbDataAdapter.FillSchema方法代码示例

        private static void ReadFromExcel()
        {
            DataTable dt = new DataTable("table");
            OleDbConnectionStringBuilder csBuilder = new OleDbConnectionStringBuilder();
            csBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csBuilder.DataSource = @"..\..\Table.xlsx";
            csBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection  = new OleDbConnection(csBuilder.ConnectionString))
            {
                connection.Open();
                string query = @"SELECT * FROM Sample";

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }

            foreach (DataRow row in dt.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
        }

        private static void InputDataFromExl(string directoryPath, string excelFileName)
        {
            DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = directoryPath + ReportsDirectory + excelFileName;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }

                connection.Close();
            }

            Console.WriteLine(dt.Rows[0].ItemArray[0]);
            int rowsCount = dt.Rows.Count - 1;
            for (int i = 2; i < rowsCount; i++)
            {
                foreach (var item in dt.Rows[i].ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
        }

    static void Main(string[] args)
    {
        DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = @"..\..\Table.xlsx";
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sheet2$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            foreach (DataRow row in dt.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
    }

        static void Main(string[] args)
        {
            //Using JET provider for xls files, because the newer ones do not work
            DataTable table = new DataTable("scores");
            string strAccessConn = string.Format(
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;",
                    "../../Table.xls");
            OleDbConnection dbCon = new OleDbConnection(strAccessConn);

            using (dbCon)
            {
                string selectSql = @"SELECT * FROM [Sheet2$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, dbCon))
                {
                    adapter.FillSchema(table, SchemaType.Source);
                    adapter.Fill(table);
                }            
            }

           // Console.WriteLine("Name, score:");
            foreach (DataRow row in table.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.Write(item + " ");
                }
                Console.WriteLine();
            }
        }

        public void GetTracks()
        {
            var dt = new DataTable("Track");

            using (var conn = new OleDbConnection())
            {
                conn.ConnectionString = GetConnectionString(Filename);
                conn.Open();

                var commandText = string.Format("SELECT * FROM [{0}${1}]", SheetName, SheetRange);

                using (var cmd = new OleDbCommand(commandText, conn))
                {

                    var adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }

            _reservedColumnsCount = 0;
            _tracks = new List<string>();
            foreach (DataColumn col in dt.Columns)
            {
                if (!MetadataFileFormat.GetReservedColumnNames().Contains(col.ColumnName))
                    _tracks.Add(col.ColumnName);
                else
                    _reservedColumnsCount++;
            }
        }

    private static void ReadExcelsFromDirectory(string filePath, List<SellsReport> reports)
    {
        DataTable dt = new DataTable("newtable");

        using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"))
        {
            connection.Open();
            string selectSql = @"SELECT * FROM [Sales$]";
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
            {
                adapter.FillSchema(dt, SchemaType.Source);
                adapter.Fill(dt);
            }
            connection.Close();
        }

       
        for (int i = 2; i < dt.Rows.Count - 2; i++)
        {
            string location = dt.Rows[0][0].ToString();

            SellsReport report = new SellsReport()
            {
                Location = location,
                ProductID = int.Parse(dt.Rows[i][0].ToString()),
                Quantity = int.Parse(dt.Rows[i][1].ToString()),
                UnitPrice = decimal.Parse(dt.Rows[i][2].ToString())
            };

            reports.Add(report);
        }
    }

        private static void ReadExcelsFromDirectory(string filePath)
        {
            DataTable dt = new DataTable("newtable");

            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\""))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            string location = dt.Rows[1][0].ToString();

            SuperMarket newSupermarket = new SuperMarket()
            {
                Name = location,
            };

            for (int i = 3; i < dt.Rows.Count - 1; i++)
            {
                int prodId = 0;
                string productId = dt.Rows[i][0].ToString();
                int.TryParse(productId, out prodId);
                if (prodId > 0)
                {
                    using (var ctx = new SupermarketEntities())
                    {
                        if (ctx.Products.Find(prodId) != null)
                        {
                            var supermarket = ctx.SuperMarkets.Where(s => s.Name == newSupermarket.Name).ToList();
                            if (supermarket.Count == 0)
                            {
                                ctx.SuperMarkets.Add(newSupermarket);
                                supermarket.Add(newSupermarket);
                            }

                            Sale newSale = new Sale()
                            {
                                ProductId = prodId,
                                SuperMarketId = supermarket[0].Id,
                                Date = DateTime.Now,
                                Quantity = int.Parse(dt.Rows[i][1].ToString()),
                                Price = decimal.Parse(dt.Rows[i][2].ToString()),
                                Sum = decimal.Parse(dt.Rows[i][3].ToString())
                            };

                            ctx.Sales.Add(newSale);
                            ctx.SaveChanges();
                        }
                    }
                }
            }
        }

        private static List<SaleInfo> ReadCurrentExcel(string path)
        {
            List<SaleInfo> allSales = new List<SaleInfo>();
            using (OleDbConnection conn = new OleDbConnection(path))
            {

                conn.Open();
                string command = @"select * from [Sales$]";
                OleDbDataAdapter adapter = new OleDbDataAdapter(command, conn);
                DataTable table = new DataTable();
                using (adapter)
                {
                    adapter.FillSchema(table, SchemaType.Source);
                    adapter.Fill(table);
                }

                int counter = 0;
                string[] splittedName = path.Split(new string[] { "-Sales-Report-" }, StringSplitOptions.RemoveEmptyEntries);
                string location = splittedName[0].Substring(splittedName[0].LastIndexOf('\\'));
                int dotIndex = splittedName[1].IndexOf('.');
                string date = splittedName[1].Substring(0, dotIndex);
                foreach (DataRow row in table.Rows)
                {
                    SaleInfo currentSale = new SaleInfo();
                    List<decimal> info = new List<decimal>();
                    bool getInside = false;

                    foreach (DataColumn  col in table.Columns)
                    {
                        if (row[col].ToString()!="")
                        {
                            info.Add(decimal.Parse(row[col].ToString()));
                           // Console.Write(row[col].ToString() + " ");
                            getInside = true;
                        }

                    }

                    if (getInside && counter != table.Rows.Count-1)
                    {
                        currentSale.ProductId = (int)info[0];
                        currentSale.Quantity = (int)info[1];
                        currentSale.UnitPrice = info[2];
                        currentSale.Sum = info[3];
                        currentSale.Location = location;
                        currentSale.SaleDate = DateTime.ParseExact(date, "dd-MMM-yyyy", CultureInfo.InvariantCulture);
                        allSales.Add(currentSale);
                    }
                    counter++;
                }

            }

            return allSales;
        }

        void ReadExcel()
        {
            var _oleConn = new OleDbConnection(ExcelConnection());
            _oleConn.Open();
            var _oleCmdSelect = new OleDbCommand("SELECT * FROM Sheet", _oleConn);
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
            oleAdapter.SelectCommand = _oleCmdSelect;
            DataTable dt = new DataTable("stock");
            oleAdapter.FillSchema(dt, SchemaType.Source);
            oleAdapter.Fill(dt);
            gridControl1.DataSource = dt;

            // select * from [sheetname$[range]
        }

        public DataSet GetWorkplace()
        {
            DataSet workplace;

            OleDbConnection connection = new OleDbConnection(strConnection);
            OleDbDataAdapter adaptor = new OleDbDataAdapter("SELECT * FROM *", connection);
            workplace = new DataSet();
            adaptor.FillSchema(workplace, SchemaType.Source);
            adaptor.Fill(workplace);

            adaptor.Dispose();
            connection.Close();

            return workplace;
        }

        static void Main(string[] args)
        {
            string filePath = @"D:\t\edp\Processed\mastinv_47100_4627900.xls";
            string connectionstring = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
            DataTable table = new DataTable();
            OleDbConnection con = new OleDbConnection(connectionstring);
            con.Open();
            OleDbDataAdapter adap = new OleDbDataAdapter("Select * from [data$]", con);
            OleDbCommand InsertC = new OleDbCommand("insert into [data$]([File name], [client], [batch], [scac], [carracct], [invoice number], [invoice date], [currency], [billed amount], [vat]) values('123', '123', '123', '123', '123', '123', '123','123', '123', '1234')", con);
            adap.InsertCommand = InsertC;
            adap.FillSchema(table, SchemaType.Mapped);
            adap.UpdateCommand = new OleDbCommand("update [data$] set batch = 'aasdf' where [file name] = '47100_4627900_00004.PDF'", con);
            //adap.UpdateCommand.ExecuteNonQuery();
            adap.InsertCommand.ExecuteNonQuery();
            con.Close();

        }

 public DataTable Resultado(string script, string tabla)
 {
     string sql = "";
     if(script == null) sql = "SELECT * FROM " + tabla;
     else sql = script;
     if (Conectar())
     {
         this.oda = new OleDbDataAdapter(sql, this.conn);
         ds = new DataSet();
         oda.FillSchema(ds, SchemaType.Source, tabla);
         oda.Fill(ds, tabla);
         dt = ds.Tables[tabla];
         Desconectar();
         return dt;
     }
     else { return null; }
 }

        private static void ReadExcelsFromDirectory(string filePath)
        {
            DataTable dt = new DataTable("newtable");

            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            List<SellsReport> repotst = new List<SellsReport>();
            for (int i = 2; i < dt.Rows.Count-2; i++)
            {
                string location = dt.Rows[0][0].ToString();

                SellsReport report = new SellsReport()
                {
                    Location = location,
                    ProductID = int.Parse(dt.Rows[i][0].ToString()),
                    Quantity = int.Parse(dt.Rows[i][1].ToString()),
                    UnitPrice = decimal.Parse(dt.Rows[i][2].ToString())
                };

                repotst.Add(report);
            }

            foreach (var item in repotst)
            {
                Console.WriteLine(item.UnitPrice);
            }

            //foreach (DataRow row in dt.Rows)
            //{
            //    for (int i = 0; i < row.ItemArray.Length; i++)
            //    {
            //        Console.Write(row.ItemArray[i].ToString()+"  ");
            //    }
            //    Console.WriteLine();
            //}
        }

        //private static readonly int m_maxSheelSize = 65000;
        #region 公用静态方法

        #region 从Excel读数据
        /// <summary>
        /// 从Excel读数据
        /// </summary>
        /// <param name="filePath">excel文档路径</param>
        /// <param name="excelVersion">文档版本</param>
        /// <param name="pHDR">第一行是否标题</param>
        /// <param name="bMerge">
        /// 如果有多页，是否合并数据，合并时必须保证多页的表结构一致
        /// </param>
        /// <returns>DataTable集</returns>
        public static DataTable[] GetExcelData(string filePath, ExcelVersion excelVersion, HeadRowType pHDR, bool bMerge)
        {
            List<DataTable> dtResult = new List<DataTable>();
            string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
              filePath, pHDR);
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                string[] sheels = GetExcelWorkSheets(filePath, excelVersion);
                foreach (string sheelName in sheels)
                {
                    try
                    {
                        DataTable dtExcel = new DataTable();
                        OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + sheelName + "$]", con);

                        adapter.FillSchema(dtExcel, SchemaType.Mapped);
                        adapter.Fill(dtExcel);

                        dtExcel.TableName = sheelName;
                        dtResult.Add(dtExcel);
                    }
                    catch
                    {
                        //容错处理：取不到时，不报错，结果集为空即可。
                    }
                }

                //如果需要合并数据，则合并到第一张表
                if (bMerge)
                {
                    for (int i = 1; i < dtResult.Count; i++)
                    {
                        //如果不为空才合并
                        if (dtResult[0].Columns.Count == dtResult[i].Columns.Count &&
                            dtResult[i].Rows.Count > 0)
                        {
                            dtResult[0].Load(dtResult[i].CreateDataReader());
                        }
                    }
                }
            }
            return dtResult.ToArray();
        }

        private static void InsertDataFromXls(string filePath, string dateString)
        {
            DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = filePath;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }

                connection.Close();
            }

            string[] dateParts = dateString.Split('-');
            int day = int.Parse(dateParts[0]);
            int month = GetMonthAsInt(dateParts[1]);
            int year = int.Parse(dateParts[2]);
            DateTime reportDate = new DateTime(year, month, day);

            int rowsCount = dt.Rows.Count - 1;
            for (int i = 2; i < rowsCount; i++)
            {
                Report report = new Report
                {
                    ProductId = Convert.ToInt32(dt.Rows[i].ItemArray[0]),
                    Quantity = Convert.ToInt32(dt.Rows[i].ItemArray[1]),
                    UnitPrice = Convert.ToDecimal(dt.Rows[i].ItemArray[2]),
                    Sum = Convert.ToDecimal(dt.Rows[i].ItemArray[3]),
                    Date = reportDate
                };

                string locationName = dt.Rows[0].ItemArray[0].ToString();
                InsertInSqlServer(report, locationName);
            }
        }