C# OleDbConnection.GetOleDbSchemaTable方法代码示例

        public static Dictionary<string, TableInfo> getSchema(OleDbConnection connection)
        {
            Dictionary<string, TableInfo> infos_dict = new Dictionary<string, TableInfo>();
            DataTable dt;
            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow row in dt.Rows)
            {

                if (row["TABLE_TYPE"] as string == "TABLE")
                {
                    TableInfo info = new TableInfo { TableName = row["TABLE_NAME"] as string };
                    infos_dict[info.TableName] = info;
                }
            }

            int i = 0;
            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
            foreach (DataRow row in dt.Rows)
            {
                string table_name = row["TABLE_NAME"] as string;
                if (infos_dict.ContainsKey(table_name))
                {
                    i++;
                    infos_dict[table_name].PrimaryKey = row["COLUMN_NAME"] as string;
                }
            }

            if (i != infos_dict.Count) throw new System.ApplicationException();

            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
            foreach (DataRow row in dt.Rows)
            {
                string fk_table_name = row["FK_TABLE_NAME"] as string;
                if (infos_dict.ContainsKey(fk_table_name))
                {
                    FKey key = new FKey
                    {
                        Name = row["FK_COLUMN_NAME"] as string,
                        Table = row["PK_TABLE_NAME"] as string,
                        Column = row["PK_COLUMN_NAME"] as string
                    };
                    infos_dict[fk_table_name].FKeys.Add(key);
                }
            }

            infos_dict["Users"] = infos_dict["USERS"];
            infos_dict.Remove("USERS");

            return infos_dict;
        }

    public int GetExcelDistictBTNCOunt_Sales(string sFileName)
    {
        Int32 NORec;
        DataSet objDataset1 = new DataSet();
        int count = 0;
        OleDbConnection objConn = new OleDbConnection();
        try
        {
             string FileExt = System.IO.Path.GetExtension(sFileName);
             if (FileExt == ".xls")
             {
                 //Excell connection
                 string Xls_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=1;ImportMixedTypes=Text\"";
                 objConn.ConnectionString = Xls_Con;
                 //Dim objConn As New OleDbConnection(Xls_Con)
                 objConn.Open();
                 DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                 string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

                 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn);
                 objAdapter1.Fill(objDataset1, "XLData");

                 count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
             }
             else if (FileExt == ".xlsx")
             {
                 //Excell connection
                 string Xls_Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                 objConn.ConnectionString = Xls_Con;
                 //Dim objConn As New OleDbConnection(Xls_Con)
                 objConn.Open();
                 DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                 string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

                 OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct(Phoneno) FROM " + SpreadSheetName, objConn);
                 objAdapter1.Fill(objDataset1, "XLData");

                 count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
             }
            objConn.Close();
        }
        catch (Exception ex)
        {
            throw ex;
            //Redirecting to error message page

            // Redirect(ConstantClass.StrErrorPageURL);
        }
        return count;
    }

        static void Main()
        {
            var connectionStringFor2007OrNewer = "Provider = Microsoft.ACE.OLEDB.12.0; Extended Properties = Excel 12.0 XML; Data Source = ../../../scores.xlsx;";
            //var connectionStringForOlder = "Provider = Microsoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0; Data Source = ../../../scores.xlsx;";

            using (var dbCon = new OleDbConnection(connectionStringFor2007OrNewer))
            {
                dbCon.Open();
                var docName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                var cmd = new OleDbCommand("SELECT * FROM [" + docName + "]", dbCon);

                using (var oleDbAdapter = new OleDbDataAdapter(cmd))
                {
                    var dataSet = new DataSet();
                    oleDbAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var name = reader["Name"];
                            var score = reader["Score"];
                            Console.WriteLine("{0}: {1}", name, score);
                        }
                    }
                }
            }
        }

        static void Main()
        {
            string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../score_db.xlsx;Extended Properties='Excel 12.0 xml;HDR=Yes';";
            var excelConnection = new OleDbConnection(excelConnectionString);
            excelConnection.Open();

            DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

            Console.Write("Username: ");
            string userName = Console.ReadLine();
            Console.Write("Score: ");
            int score = int.Parse(Console.ReadLine());

            OleDbCommand excelCommand = new OleDbCommand(@"INSERT INTO [" + sheetName + @"]
                                                           VALUES (@name, @score)", excelConnection);

            excelCommand.Parameters.AddWithValue("@name", userName);
            excelCommand.Parameters.AddWithValue("@age", score);

            using (excelConnection)
            {
                Console.WriteLine("\nINSERTING INTO EXCEL FILE DATABASE");
                Console.WriteLine("-----------------------------------\n");
                var queryResult = excelCommand.ExecuteNonQuery();
                Console.WriteLine("({0} row(s) affected)", queryResult);
            }
        }

 public static DataSet ReadFile(string filepath)
 {
     var result = new DataSet();
     var connectionstring = GetConnectionString((filepath.Contains("xlsx") ? ExcelFormat.Excel2007 : ExcelFormat.Excel2003), filepath);
     using (var connection = new OleDbConnection(connectionstring))
     {
         try
         {
             connection.Open();
             using (var datatable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
             {
                 if (datatable.Rows.Count == 0)
                     throw new Exception("Excel file doesn't contain sheet");
                 var sheetname = datatable.Rows[0]["TABLE_NAME"].ToString();
                 /// Only read First Sheet
                 using (var adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetname), connection))
                 {
                     adapter.Fill(result);
                 }
             }
         }
         catch (Exception err)
         {
             throw err;
         }
         finally
         {
             connection.Close();
         }
     }
     return result;
 }

        /// <summary>  
        /// 获取Excel文件数据表列表  
        /// </summary>  
        public static ArrayList GetExcelTables(string ExcelFileName)
        {
            DataTable dt = new DataTable();
            ArrayList TablesList = new ArrayList();
            if (File.Exists(ExcelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }

                    //获取数据表个数  
                    int tablecount = dt.Rows.Count;
                    for (int i = 0; i < tablecount; i++)
                    {
                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                        if (TablesList.IndexOf(tablename) < 0)
                        {
                            TablesList.Add(tablename);
                        }
                    }
                }
            }
            return TablesList;
        }

    public DataSet GetExcelDistictBTNCOunt_Sales(string sFileName)
    {
        Int32 NORec;
        DataSet objDataset1 = new DataSet();
        int count = 0;
        OleDbConnection objConn = new OleDbConnection();
        try
        {
            //Excell connection
            string Xls_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=1;ImportMixedTypes=Text\"";
            objConn.ConnectionString = Xls_Con;
            //Dim objConn As New OleDbConnection(Xls_Con)
            objConn.Open();
            DataTable ExcelSheets = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string SpreadSheetName = "[" + ExcelSheets.Rows[0]["TABLE_NAME"].ToString() + "]";

            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("SELECT distinct([CPhone]),SaleDate FROM " + SpreadSheetName + "", objConn);

            objAdapter1.Fill(objDataset1, "XLData");

            //count = Convert.ToInt32(objDataset1.Tables[0].Rows.Count);
            objConn.Close();
        }
        catch (Exception ex)
        {

        }
        return objDataset1;
    }

        private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
        {
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                             FileName + ";Extended Properties=\"Excel 8.0;HDR=" +
                             HDR + ";IMEX=1\"";

            DataSet output = new DataSet();

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();

                DataTable schemaTable = conn.GetOleDbSchemaTable(
                  OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                foreach (DataRow schemaRow in schemaTable.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString();

                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;

                    DataTable outputTable = new DataTable(sheet);
                    output.Tables.Add(outputTable);
                    new OleDbDataAdapter(cmd).Fill(outputTable);
                }
            }
            return output;
        }

 /// <summary>
 /// Get Metadata information about the tables in a schema in the current database
 /// </summary>
 /// <param name="schema">Name of the schema in the database.</param>
 /// <returns></returns>
 public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
 {
     SchemaTablesMetaData result = new SchemaTablesMetaData();
     result.schemaName = schema;
     try
     {
         using (OleDbConnection connector = new OleDbConnection(connectionString))
         {
             connector.Open();
             using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
             {
                 foreach (DataRow row in dt.Rows)
                 {
                     TableMetaData table = new TableMetaData();
                     table.tableName = row[2].ToString();
                     result.AddTable(table);
                 }
             }
             connector.Close();
         }
     }
     catch (OleDbException ex)
     {
         Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
     }
     return result;
 }

        public List<List<object>> Parse(string filename)
        {
            const string fileType = ".xlsx";
            var fileFullName = HttpContext.Current.Server.MapPath("~/Storage/" + filename + fileType);
            var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";

            var adapter = new OleDbDataAdapter();
            var conn = new OleDbConnection(connectionString);
            conn.Open();

            DataTable excelSheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                new object[] { null, null, null, "TABLE" });

            const int workSheetNumber = 0;
            string spreadSheetName = excelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString();

            string strQuery = "select * from [" + spreadSheetName + "] ";
            adapter.SelectCommand = new OleDbCommand(strQuery, conn);
            var dsExcel = new DataSet();
            adapter.Fill(dsExcel);
            conn.Close();
            var result = new List<List<object>>();
            var listDataRow = dsExcel.Tables[0].Rows.Cast<DataRow>().ToList();
            foreach (var item in listDataRow)
            {
                var row = new List<object>();
                for (var i = 0; i < item.ItemArray.Count(); i++)
                {
                    var value = (item.ItemArray[i] == DBNull.Value) ? null : item.ItemArray[i];
                    row.Add(value);
                }
                result.Add(row);
            }
            return result;
        }

        static void Main()
        {
            string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../score_db.xlsx;Extended Properties='Excel 12.0 xml;HDR=Yes';";
            var excelConnection = new OleDbConnection(excelConnectionString);
            excelConnection.Open();

            DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

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

            using (excelConnection)
            {
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(excelCommand))
                {
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var userName = reader["Name"];
                            var score = reader["Score"];

                            Console.WriteLine(userName + " -> " + score);
                        }
                    }
                }
            }
        }

        public static IEnumerable<DataRow> GetRowsFromDataSheets(OleDbConnection connection)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("Must provide a valid OleDbConnection object.", "connection");
            }
            
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            
            DataTable schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            var allDataRows = new List<DataRow>();

            foreach (DataRow sheet in schema.Rows)
            {
                string sheetName = sheet.Field<string>("TABLE_NAME");

                DataTable sheetData = new DataTable();

                OleDbDataAdapter sheetAdapter = new OleDbDataAdapter(String.Format("select * from [{0}]", sheetName), connection);

                sheetAdapter.Fill(sheetData);

                var sheetDataRows = sheetData.AsEnumerable();

                allDataRows.AddRange(sheetDataRows);
            }

            connection.Close();

            return allDataRows;
        }

        /// <summary>
        /// Read the specified Excel file and returns the content
        /// </summary>
        /// <param name="excelFile"></param>
        /// <returns></returns>
        public DataTable Read(string excelFile)
        {
            string connectionString = string.Empty;

            string fileExtension = Path.GetExtension(excelFile);
            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
            }

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

                using (OleDbCommand command = conn.CreateCommand())
                {
                    DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetName = dtSheet.Rows[0]["TABLE_NAME"].ToString();

                    command.CommandText = string.Format("SELECT * FROM [{0}]", sheetName);

                    using (OleDbDataAdapter da = new OleDbDataAdapter(command))
                    {
                        var dt = new DataTable();

                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }

        public List<Destination> SelectExcelFilesFromZip(string path)
        {
            var destinations = new List<Destination>();
            using (ZipArchive archive = ZipFile.Open(path, ZipArchiveMode.Update))
            {
                foreach (ZipArchiveEntry entry in archive.Entries)
                {
                    if (entry.FullName.EndsWith(".xlsx"))
                    {
                        entry.ExtractToFile(Path.Combine(extractPath, entry.Name));
                        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(extractPath, entry.Name) + ";Extended Properties='Excel 12.0 xml;HDR=Yes';";

                        OleDbConnection connection = new OleDbConnection(connectionString);

                        using (connection)
                        {
                            connection.Open();
                            var excelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
                            destinations = this.ReadExcelData(connection, sheetName);
                        }
                    }
                }

                return destinations;
            }
        }

        public List<SheetInfo> GetSheetlist()
        {
            if (excelpath == null||excelpath.Length<=0)
                return null;
            List<SheetInfo> list = new List<SheetInfo>();
            string connStr = "";
            string sql_F = "Select * FROM [{0}]";
            string fileType = System.IO.Path.GetExtension(excelpath);
            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

            OleDbConnection conn = new OleDbConnection(connStr);
            OleDbDataAdapter da = null;
            try
            {
                conn.Open();
                string sheetname = "";
                DataTable dtSheetName = 
                    conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                da = new OleDbDataAdapter();
                for (int i = 0; i < dtSheetName.Rows.Count;i++ )
                {
                    sheetname = (string)dtSheetName.Rows[i]["TABLE_NAME"];
                    if (sheetname.Contains("$") )
                    {
                        SheetInfo info = new SheetInfo();
                        info.SheetName = sheetname.Replace("$", "");

                        da.SelectCommand = new OleDbCommand(String.Format(sql_F, sheetname), conn);
                        DataSet dsItem = new DataSet();
                        da.Fill(dsItem, sheetname);
                        int cnum = dsItem.Tables[0].Columns.Count;
                        int rnum = dsItem.Tables[0].Rows.Count;
                        info.StartRange = "A1";
                        char c = (char)('A' + cnum - 1);
                        info.EndRange = c + Convert.ToString(rnum);
                        list.Add(info);
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误消息");
                return null; 
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    if(da!=null)
                        da.Dispose();
                    conn.Dispose();
                }
            }

            return list;
        }