C# OleDb.OleDbConnectionStringBuilder类代码示例

        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);
                }
            }
        }

    protected void btnCS_Click(object sender, EventArgs e)
    {
        try
        {

           
            OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
            OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
            conn.Open();
            string updateQuery = "UPDATE Orders SET [email protected] WHERE ID= @ID";
            OleDbCommand com = new OleDbCommand(updateQuery, conn);
            
            com.Parameters.AddWithValue("@status", ddlOrderStatus.SelectedValue.ToString());
            com.Parameters.AddWithValue("@ID", txtOrderID.Text);




            com.ExecuteNonQuery();

            lblMessage.Text = "The Order No " + txtOrderID.Text + " was updated successfully !";

            conn.Close();
            Response.AppendHeader("Refresh", "2;url=Orders.aspx");
        }
        catch (Exception ex)
        {
            Response.Write("Error: " + ex.ToString());
            lblMessage.Text = "Error !";
        }
    }

    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);
                }
            }
    }

        public static string GetConnectionString(string fileName)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();

            string[] newExtensions = new string[]
            {
                ".xlsx", ".xlsb", ".xlsm"
            };
            string[] oldExtensions = new string[]
            {
                ".xls"
            };
            string extension = Path.GetExtension(fileName);
            if (newExtensions.Contains(extension))
            {
                builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                builder.Add("Extended Properties", "Excel 12.0 Xml; HDR=No; READONLY=true; IMEX=1");
            }
            else if (oldExtensions.Contains(extension))
            {
                builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                builder.Add("Extended Properties", "Excel 8.0; HDR=No; READONLY=true; IMEX=1");
            }
            else
            {
                throw new ArgumentException(Resources.UnknownExcelExtension, "fileName");
            }

            builder.DataSource = fileName;
            return builder.ConnectionString;
        }

    protected void Page_Load(object sender, EventArgs e)
    {

        if (IsPostBack)
        {
            
            OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
            sb.Provider = "Microsoft.ACE.OLEDB.12.0";
            sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");


            OleDbConnection conn = new OleDbConnection(sb.ConnectionString);


            conn.Open();
            string checkUser = "select count(*) from UserData where UserName='" + txtUserName.Text + "'";
            OleDbCommand com = new OleDbCommand(checkUser, conn);
            temp = Convert.ToInt32(com.ExecuteScalar().ToString());
            if (temp != 0)
            {
                Response.Write("User Already Exists !");
            }

            conn.Close();
        }

    }

        private static void InsertDataToExcel(OleDbConnectionStringBuilder csbuilder, Data data)
        {
            OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString);
            connection.Open();

            using (connection)
            {
                OleDbCommand command = new OleDbCommand("INSERT INTO [Sheet1$] (Id, Name, Score) " + 
                    "VALUES (@id, @name, @score)", connection);

                OleDbParameter id = new OleDbParameter("@id", data.Id);
                OleDbParameter name = new OleDbParameter("@name", data.Name);
                OleDbParameter score = new OleDbParameter("@score", data.Score);
                command.Parameters.AddRange(new OleDbParameter[] { id, name, score });

                try
                {
                    command.ExecuteNonQuery();

                    Console.WriteLine("Row inserted successfully.");
                }
                catch (OleDbException exception)
                {
                    Console.WriteLine("SQL Error occured: " + exception);
                }
            }
        }

        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);

            try
            {
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
                builder.Add("Provider", Properties.Settings.Default.provider);
                builder.Add("Data Source", Properties.Settings.Default.path);

                string connString = builder.ConnectionString;
                OleDbConnection conn = new OleDbConnection(connString);

                conn.Open();
                conn.Close();

                LoadingScreen splashy = new LoadingScreen();
                splashy.Show();

                Application.Run(new Form1(splashy));

                /*Form1 f = new Form1();
                f.Show();*/

            }
            catch
            {
                Application.Run(new ConnectionDefinition());
                /*ConnectionDefinition c = new ConnectionDefinition();
                c.Show();*/
            }

            //Application.Run(/*new ConnectionDefinition()*/);
        }

        public Connect(string dbhost = "localhost", string dbname = "test", string dbusername = "root", string dbpass = "", string dbdriver = "SQLOLEDB")
        {
            try
            {
                OleDbConnectionStringBuilder connection = new OleDbConnectionStringBuilder();

                connection["Provider"] = dbdriver;
                connection["Data Source"] = dbhost;
                connection["Database"] = dbname;
                connection["User ID"] = dbusername;
                connection["Password"] = dbpass;

                _conn = new OleDbConnection();
                _conn.ConnectionString = connection.ConnectionString;
                _conn.Open();
            }
            catch (InvalidOperationException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (OleDbException ex)
            {
                this.DisplayOleDbErrorCollection(ex);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            
        }

        //Ruft die einträge einer Person ab und gibt sie als Eintrag objekt zurück
        private Eintrag EintragAbrufen(Person p)
        {
            Eintrag eintrag = new Eintrag();

            OleDbConnectionStringBuilder bldr = new OleDbConnectionStringBuilder("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\\Eigene Dateien\\Visual Studio 2013\\Fi12\\Projekt\\Datenbank11.accdb");
            OleDbConnection con = new OleDbConnection();
            OleDbCommand com = new OleDbCommand();

            con.ConnectionString = bldr.ConnectionString;
            con.Open();

            com.Connection = con;

            com.CommandText = "SELECT Tweet FROM Tweets WHERE (AccID = pId)";
            com.Parameters.Add("pId", OleDbType.Numeric);
            com.CommandType = CommandType.Text;

            com.Parameters["pId"].Value = p.Id;
            com.ExecuteNonQuery();
            com.Connection = con;

            OleDbDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                eintrag.addEintrag((string)reader["Tweet"]);
            }
            reader.Close();

            return eintrag;
        }

        public AccessDatabase()
        {
            SyncRoot = new object();

            Name = "Access 2013";
            CollectionName = "table1";
            Category = "SQL";
            Description = "Access 2013";
            Website = "http://www.microsoft.com/en-us/download/details.aspx?id=13255";
            Color = Color.FromArgb(218, 66, 127);

            Requirements = new string[]
            {
                "Microsoft Access Database Engine 2010 Redistributable",
                "Microsoft.Office.Interop.Access.Dao.dll"
            };

            DataDirectory = Path.Combine(MainForm.DATABASES_DIRECTORY, Name);

            OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
            cb.Provider = "Microsoft.ACE.OLEDB.12.0";
            cb.DataSource = String.Format(@"{0}\{1}.accdb", DataDirectory, Name);
            ConnectionString = cb.ConnectionString;

            Logger = LogManager.GetLogger(Properties.Settings.Default.TestLogger);
        }

        /// <summary>
        /// Reads the contents of an excel report file into a list of sales arrays;
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="salesDate"></param>
        /// <returns>rows: Supermarket, Data, ProductID, Quantity, Unit Price</returns>
        public static List<string[]> ReadOneExcelFile(OleDbConnectionStringBuilder connectionString, string supermarket, string salesDate)
        {
            List<String[]> data = new List<String[]>();
            using (OleDbConnection connection = new OleDbConnection(connectionString.ToString()))
            {
                connection.Open();
                var command = new OleDbCommand("select * from [Sales$B3:E1000]", connection);
                using (OleDbDataReader oneRow = command.ExecuteReader())
                {
                    oneRow.Read();  // Skip row - ProductID, Quantity, Unit Price

                    while (oneRow.Read())
                    {
                        string productID = oneRow[0].ToString();
                        string quantity = oneRow[1].ToString();
                        string unitPrice = oneRow[2].ToString();
                        int result;  // only to check if the line containd a sale
                        if (int.TryParse(productID, out result))
                        // If not the Last rows
                        {
                            string[] line = { supermarket, salesDate, productID, quantity, unitPrice };
                            data.Add(line);
                        }
                    }
                }
            }
            return data;
        }

        public static IEnumerable<string[]> GetReportsData(string zipPath, string extractPath)
        {
            if (Directory.Exists(extractPath))
            {
                Directory.Delete(extractPath, true);
            }

            using (ZipFile archive = ZipFile.Read(zipPath))
            {
                archive.ExtractAll(extractPath, ExtractExistingFileAction.OverwriteSilently);
            }

            IEnumerable<string> excelFiles = DirSearch(extractPath);

            var connectionString = new OleDbConnectionStringBuilder();
            connectionString.Provider = "Microsoft.ACE.OLEDB.12.0";
            connectionString.Add("Extended Properties", "Excel 12.0");

            var sales = new List<String[]>();
            foreach (var file in excelFiles)
            {
                connectionString.DataSource = file;

                // Get the date from the name of the file.
                string pattern = "-Sales-Report-";
                string salesDate = file.Substring(file.LastIndexOf(pattern) + pattern.Length);
                string supermarket = Path.GetFileNameWithoutExtension(file.Substring(0, file.LastIndexOf(pattern)));
                salesDate = salesDate.Substring(0, salesDate.IndexOf(".xls"));

                sales.AddRange(ReadOneExcelFile(connectionString, supermarket, salesDate));
            }
            Directory.Delete(extractPath, true);
            return sales;
        }

        public static DbConnectionStringBuilder CreateConnectionStringBuilder(DataProvider dataProvider)
        {
            DbConnectionStringBuilder dbConnectionStringBuilder;

            switch (dataProvider)
            {
                case DataProvider.Odbc:
                    dbConnectionStringBuilder = new OdbcConnectionStringBuilder();
                    break;

                case DataProvider.OleDB:
                    dbConnectionStringBuilder = new OleDbConnectionStringBuilder();
                    break;

                case DataProvider.SqlServer:
                    dbConnectionStringBuilder = new SqlConnectionStringBuilder();
                    break;

                default:
                    dbConnectionStringBuilder = null;
                    break;
            }

            return dbConnectionStringBuilder;
        }

    public void LoadDelegates()
    {
        OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
        sb.Provider = "Microsoft.ACE.OLEDB.12.0";
        sb.DataSource = Server.MapPath("~/../uploads/db1.accdb");

        OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
        string sqlQuery = "SELECT * FROM Bag";

        OleDbCommand command = new OleDbCommand(sqlQuery, conn);
        OleDbDataReader myReader;

        //Open connection
        conn.Open();

        //Retrieve the data and display by using a DataList
        myReader = command.ExecuteReader();
        dlstDataList.DataSource = myReader;
        dlstDataList.DataBind();

        myReader.Close();

        //Close connection
        conn.Close();
    }

    protected void Page_Load(object sender, EventArgs e)
    {

        //Define connection
        OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
        sb.Provider = "Microsoft.ACE.OLEDB.12.0";
        sb.DataSource = Server.MapPath("~/../uploads/db1.accdb");

        OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
        string sqlQuery = "SELECT [BagID],[BagName], [Quantity],[UserName], [Color], [Cost], [Status]FROM [Orders]";
        _myAdapter = new OleDbDataAdapter(sqlQuery, conn);

        //Event handlers
        dlstDataList.ItemCommand += new DataListCommandEventHandler(dlstDataList_ItemCommand);
        Save.Click += new EventHandler(Save_Click);
        Clear.Click += new EventHandler(Clear_Click);

        if (!this.IsPostBack)
        {
            this.LoadDelegates();

            //Load table
            //No need to open and close connection. DataAdapter does it.
            _myAdapter.Fill(_myDs, _orderItemsName);
            _myDs.Clear();
            this.Session[_orderName] = _myDs;
        }

    }