C# OleDbConnection.GetSchema方法代码示例

    public static void ReadExcel()
    {
        string sheetName = string.Empty;
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\test.xlsx;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
        OleDbConnection connection = new OleDbConnection(connectionString);
        connection.Open();
        DataTable table = connection.GetSchema("Tables");

        if (table.Rows.Count > 0)
            sheetName = table.Rows[0]["TABLE_NAME"].ToString();

        if (!string.IsNullOrEmpty(sheetName))
        {
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = string.Format("select * from [{0}]", sheetName);
            command.CommandType = CommandType.Text;
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                StringBuilder strBuilder = new StringBuilder();
                for (int i = 0; i < reader.FieldCount; i++)
                    strBuilder.Append(string.Format("{0}\t", reader[i]));
                Console.WriteLine(strBuilder.ToString());
            }
            reader.Close();
        }

        connection.Close();

        Console.ReadKey();
    }

        //public string ExcelFile {private get; set; }
        public static DataTable ReadData(string excelFile)
        {
            if (!System.IO.File.Exists(excelFile))
                return null;

            OleDbConnection excelConnection = new OleDbConnection();
            excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile);
            excelConnection.Open();
            DataTable dtSchema = excelConnection.GetSchema("Tables");
            if (dtSchema.Rows.Count == 0)
                return null;

            string strTableName = dtSchema.Rows[0]["Table_Name"] as string;
            string strSQL = string.Format("select * from [{0}]", strTableName);
            OleDbCommand cmdSelect = excelConnection.CreateCommand();
            cmdSelect.CommandText = strSQL;
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect);
            DataTable dtResult=new DataTable();
            dbAdapter.Fill(dtResult);

            dbAdapter.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();

            return dtResult;
        }

 public DataTable GetTablesName()
 {
     OleDbConnection connection = new OleDbConnection();
     DataTable dtTables = new DataTable();
     connection.ConnectionString = this._DbConectionString;
     try
     {
         connection.Open();
         dtTables = connection.GetSchema("TABLES");
     }
     catch (OleDbException oledbex)
     {
         throw oledbex;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         if (connection.State == ConnectionState.Open)
         {
             connection.Close();
         }
     }
     return dtTables;
 }

        /// <summary>
        /// Imports all sheets from an Excel file
        /// </summary>
        /// <param name="excelFile">The excel file path.</param>
        /// <returns></returns>
        public static DataSet ImportAll(string excelFile)
        {
            DataSet ds = new DataSet();

            string connectionString = GetExcelConnectionString(excelFile, false, true);

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                //csv doesn't have worksheets, and table name == file name
                if (Path.GetExtension(excelFile).Equals(".csv", StringComparison.OrdinalIgnoreCase))
                {
                    LoadTableIntoDataSet(connection, Path.GetFileName(excelFile), ds);
                    return ds;
                }

                //xls and xlsx have worksheets, so load each one as a datatable
                DataTable worksheets = connection.GetSchema("Tables");
                foreach (DataRow row in worksheets.Rows)
                {
                    //this can also return Excel named ranges
                    string tabName = (string)row["TABLE_NAME"];
                    //so look for sheets (excel puts $ after the name and may single-quote the name)
                    if (tabName.EndsWith("$") || tabName.EndsWith("$'"))
                        LoadTableIntoDataSet(connection, tabName, ds);
                }
            }
            return ds;
        }

        public string[] GetColumnsList(string worksheet)
        {
            string[] columns;

            try
            {
                OleDbConnection connection = new OleDbConnection(strConnection);
                connection.Open();
                DataTable tableColumns = connection.GetSchema("Columns", new string[] {null, null, worksheet + '$', null});
                connection.Close();

                columns = new string[tableColumns.Rows.Count];

                for (int i = 0; i < columns.Length; i++)
                {
                    columns[i] = (string)tableColumns.Rows[i]["COLUMN_NAME"];
                }
            }
            catch
            {
                throw;
            }

            return columns;
        }

 public DataTable GetTablesName(string FileName)
 {
     OleDbConnection connection = new OleDbConnection();
     DataTable dtTables = new DataTable();
     connection.ConnectionString = ConectionStringManagerAccess(FileName);
     try
     {
         connection.Open();
         dtTables = connection.GetSchema("TABLES");
         for (int i = 0; i < dtTables.Rows.Count; i++)
         {
             if (dtTables.Rows[i]["TABLE_TYPE"].ToString() != "TABLE")
             {
                 dtTables.Rows.RemoveAt(i);
                 i--;
             }
         }
     }
     catch (OleDbException oleex)
     {
         throw oleex;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         if (connection.State == ConnectionState.Open)
         {
             connection.Close();
         }
     }
     return dtTables;
 }

        public DataTable GetSchema(string collectionName)
        {
            DataTable results;

            conn = new OleDbConnection(connection);
            conn.Open();
            results =  conn.GetSchema(collectionName);
            conn.Close();
            return results;
        }

 public void load_sheet_names()
 {
     connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File_PathtextBox1.Text + ";Extended Properties=Excel 12.0;";
     OleDbConnection connection = new OleDbConnection(connectionstring);
     connection.Open();
     connection.GetSchema();
     connection.GetSchema("Tables");
     List<string> tables = new List<string>();
     foreach (DataRow r in connection.GetSchema("Tables").Select("TABLE_TYPE = 'TABLE'"))
     {
         tables.Add(r["TABLE_NAME"].ToString());
     }
     foreach (var item in tables)
     {
         Excel_Sheet_Name_comboBox.Items.Add(item);
     }
     if (connection.State == ConnectionState.Open)
     {
         connection.Close();
     }
 }

    public IEnumerable<NFLEPMarkov> GetMarkovData(string FileName)
    {
        string sSheetName = null;
        string sConnection = null;
        DataTable dtTablesList = default(DataTable);
        OleDbCommand oleExcelCommand = default(OleDbCommand);
        OleDbDataReader oleExcelReader = default(OleDbDataReader);
        OleDbConnection oleExcelConnection = default(OleDbConnection);

        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
        oleExcelConnection = new OleDbConnection(sConnection);
        oleExcelConnection.Open();

        dtTablesList = oleExcelConnection.GetSchema("Tables");

        if (dtTablesList.Rows.Count > 0)
        {
            sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
        }

        dtTablesList.Clear();
        dtTablesList.Dispose();


        if (!string.IsNullOrEmpty(sSheetName))
        {
            oleExcelCommand = oleExcelConnection.CreateCommand();
            oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
            oleExcelCommand.CommandType = CommandType.Text;
            oleExcelReader = oleExcelCommand.ExecuteReader();

            //nOutputRow = 0;
            //var sheets = oleExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            //oleExcelCommand.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

            //var adapter = new OleDbDataAdapter(oleExcelCommand);
            //var ds = new DataSet();
            //adapter.Fill(ds);

            //object[] dataFill = new object[17];

            using (var reader = oleExcelReader) { //will this work?
            //using (var reader = oleExcelCommand.ExecuteReader())
            //I think using gets rid of the reader, hopefully.
                while (reader.Read()) {
                     yield return NFLEPMarkov.Create(reader);
                }
            }
        }
        oleExcelConnection.Close();
    }

 //对下拉列表进行数据绑定
 private void CBoxBind()
 {
     cbox_SheetName.Items.Clear();//清空下拉列表项
     //连接Excel数据库
     OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_Path.Text + ";Extended Properties=Excel 8.0");
     olecon.Open();//打开数据库连接
     System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
     DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
     while (DTReader.Read())//循环读取
     {
         cbox_SheetName.Items.Add(DTReader["Table_Name"].ToString().Replace('$',' ').Trim());//将工作表名添加到下拉列表中
     }
     DTable = null;//清空表对象
     DTReader = null;//清空表读取对象
     olecon.Close();//关闭数据库连接
     cbox_SheetName.SelectedIndex = 0;//设置下拉列表默认选项为第一项
 }

 //获取所有工作表名称
 private List<string> GetSheetName(string P_str_Excel)
 {
     List<string> P_list_SheetName = new List<string>();//实例化泛型集合对象
     //连接Excel数据库
     OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Excel + ";Extended Properties=Excel 8.0");
     olecon.Open();//打开数据库连接
     System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
     DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
     while (DTReader.Read())//循环读取
     {
         string P_str_Name = DTReader["Table_Name"].ToString().Replace('$', ' ').Trim();//记录工作表名称
         if (!P_list_SheetName.Contains(P_str_Name))//判断泛型集合中是否已经存在该工作表名称
             P_list_SheetName.Add(P_str_Name);//将工作表名添加到泛型集合中
     }
     DTable = null;//清空表对象
     DTReader = null;//清空表读取对象
     olecon.Close();//关闭数据库连接
     return P_list_SheetName;//返回得到的泛型集合
 }

 private void button1_Click(object sender, EventArgs e)
 {
     try
     {
         comboBox1.Items.Clear();
         OleDbConnection dbCon = new OleDbConnection(@"Provider=SQLNCLI10;Server=(local);Database=McDac;Trusted_Connection=yes");
         dbCon.Open();
         DataTable tables = dbCon.GetSchema("Tables", new string[] { null, null, null, "TABLE" }); //список всех таблиц
         foreach (DataRow row in tables.Rows)
         {
             string TableName = row["TABLE_NAME"].ToString();
             comboBox1.Items.Add(TableName);
         }
         dbCon.Close();
         MessageBox.Show("Ready!");
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
 }

        public string[] GetWorksheetList()
        {
            string[] worksheets;

            try
            {
                OleDbConnection connection = new OleDbConnection(strConnection);
                connection.Open();
                DataTable tableWorksheets = connection.GetSchema("Tables");
                connection.Close();

                worksheets = new string[tableWorksheets.Rows.Count];

                for (int i = 0; i < worksheets.Length; i++)
                {
                    worksheets[i] = (string)tableWorksheets.Rows[i]["TABLE_NAME"];
                    worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
                    // removes the trailing $ and other characters appended in the table name
                    while (worksheets[i].EndsWith("$"))
                        worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
                }
            }
            catch 
            {
                /* 
                    for (int i = 0; i < ex.Errors.Count; i++)
                    {
                        MessageBox.Show("Index #" + i + "\n" +
                        "Message: " + myException.Errors[i].Message + "\n" +
                        "Native: " +
                            myException.Errors[i].NativeError.ToString() + "\n" +
                        "Source: " + myException.Errors[i].Source + "\n" +
                        "SQL: " + myException.Errors[i].SQLState + "\n");
                    }
                 */
                throw;
            }

            return worksheets;
        }

 public ActionResult Excel(HttpPostedFileBase uploadFile)
 {
     StringBuilder strValidations = new StringBuilder(string.Empty);
     try
     {
         string filePath = Path.Combine(HttpContext.Server.MapPath("../ufile"), Path.GetFileName(uploadFile.FileName));
         if (uploadFile.ContentLength > 0)
         {
             Path.GetFileName(uploadFile.FileName);
             uploadFile.SaveAs(filePath);
             string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("../files/") + uploadFile.FileName.Split('\\')[3].ToString() + ";Extended Properties=Excel 12.0;";
             using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
             {
                 conn.Open();
                 using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
                 {
                     string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                     string query = "SELECT * FROM [" + sheetName + "]";
                     OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
                     DataSet ds = new DataSet();
                     adapter.Fill(ds, "Items");
                     if (ds.Tables.Count > 0)
                     {
                         if (ds.Tables[0].Rows.Count > 0)
                         {
                             for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                             {
                                 //这里添加到数据库操作
                             }
                         }
                     }
                 }
             }
         }
     }
     catch
     { }
     return View();
 }

        private static void OleDbReadCollectionName(string name)
        {
            using (OleDbConnection conn = new OleDbConnection(connstr))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                StringBuilder sb = new StringBuilder();

                DataTable dataTable = conn.GetSchema(name);

                foreach (DataColumn dataColumn in dataTable.Columns)
                {
                    sb.Append(dataColumn.ColumnName.PadRight(40));
                }

                sb.AppendLine();

                foreach (DataRow row in dataTable.Rows)
                {
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        sb.Append(row[column].ToString().PadRight(40));
                    }

                    sb.AppendLine();
                }

                using (StreamWriter writer = new StreamWriter(String.Format("oledb-{0}-schema.txt", name.ToLowerInvariant())))
                {
                    writer.Write(sb.ToString());
                    writer.Flush();
                }

            }
        }