C# OleDbConnection.OpenAsync方法代码示例

        /// <summary>
        /// Saves byte array to database
        /// </summary>
        /// <param name="name">File name</param>
        /// <param name="file">File bytes</param>
        public static async Task SaveFileAsync(string name, byte[] file)
        {
            string connectionString = defaultConnectionString;

            using (var connection = new OleDbConnection(connectionString))
            {
                try
                {
                    // Try to open connection to Db
                    await connection.OpenAsync();

                    string query;
                    // If there is a record with same name, use another query to overwrite its data
                    if (await FileExists(name, connection))
                        query = "UPDATE Files SET FileData = @data WHERE Name = @name;";
                    else
                        query = "INSERT INTO Files ([Name],[FileData]) VALUES (@name,@data);";

                    using (var command2 = new OleDbCommand(query, connection))
                    {
                        // Queries are parameterized as a measure to prevent SQL injection
                        command2.Parameters.AddWithValue("@name", name);
                        command2.Parameters.AddWithValue("@data", file);

                        // Nonquery method is good when there is nothing to return
                        await command2.ExecuteNonQueryAsync();
                    }

                }
                catch (Exception ex)
                {
                    // Possible more complex exception handling
                    TextEditor.WPFMessageBoxException(ex);
                }
            }
        }

        private async Task<OleDbConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            var con = new OleDbConnection(ConnectionString);
            await con.OpenAsync(cancellationToken).ConfigureAwait(false);

            //TODO: Add in needed PRAGMA statements

            return con;
        }

 private static async Task<DataSet> SetDataSet(string path)
 {
     var ds = new DataSet();
     var connectString = "";
     //connection strings for excel files from and rest code of this method based on
     //http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx
     if (Path.GetExtension(path).Equals(".xlsx"))
         connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
     else if (Path.GetExtension(path).Equals(".xls"))
         connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0 Xml;HDR=YES;IMEX=1;\"";
     using (var oleDbConnection = new OleDbConnection(connectString))
     {
         await oleDbConnection.OpenAsync();
         var dt = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
         if (dt == null)
             return null;
         var excelSheets = new String[dt.Rows.Count];
         var t = 0;
         foreach (DataRow row in dt.Rows)
         {
             excelSheets[t] = row["TABLE_NAME"].ToString();
             t++;
         }
         var query = string.Format("Select * from [{0}]", excelSheets[0]);
         using (var dataAdapter = new OleDbDataAdapter(query, oleDbConnection))
         {
             dataAdapter.Fill(ds);
         }
         oleDbConnection.Close();
     }
     return ds;
 }

        /// <summary>
        /// Gets a list of all the files stored in the database
        /// </summary>
        public static async Task<List<string>> GetAllFileNamessAsync()
        {
            string connectionString = defaultConnectionString;

            using (var connection = new OleDbConnection(connectionString))
            {
                try
                {
                    await connection.OpenAsync();

                    string query = "SELECT Name FROM Files;";
                    using (var command = new OleDbCommand(query, connection))
                    {
                        var reader = await command.ExecuteReaderAsync();

                        var list = new List<string>();

                        while (await reader.ReadAsync())
                            list.Add(reader.GetString(0));

                        return list;
                    }
                }
                catch (Exception ex)
                {
                    TextEditor.WPFMessageBoxException(ex);
                    return null;
                }
            }
        }

 public async Task<IList<ServiceZipDetailsSet>> GetServiceZips(string company, string filename, string month, int year)
 {
     var serviceZips = new List<ServiceZipDetailsSet>();
     var conn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", filename));
     await conn.OpenAsync();
     var cmd = new OleDbCommand();
     cmd.Connection = conn;
     cmd.CommandText = "SELECT * FROM [Лист1$]";
     var reader = await cmd.ExecuteReaderAsync();
     while (reader.Read())
     {
         int q = 0;
         if (reader["Quantity"] != DBNull.Value)
             int.TryParse(reader["Quantity"].ToString(), out q);
         if (reader["BlankNumber"].ToString().Trim() == "tr")
         {
             //here
         }
         serviceZips.Add(new ServiceZipDetailsSet
         {
             WorkDate = Convert.ToDateTime(reader["Date"]),
             BlankNumber = reader["BlankNumber"].ToString().Trim(),
             ZipName = reader["Work"].ToString().Trim(),
             StoreNumber = Convert.ToInt16(reader["StoreNumber"]),
             ZipPrice = Convert.ToDecimal(reader["Price"]),
             Company = company,
             ServiceMonth = month,
             ServiceYear = year,
             ZipQuantity = q
         });               
     }
     reader.Close();
     conn.Close();
     return serviceZips;
 }

 public async Task<bool> Connect(string connectionString, CancellationToken token)
 {
     _conn = new OleDbConnection(connectionString);
     try
     {
         await _conn.OpenAsync(token).ConfigureAwait(false);
     }
     catch (Exception ex)
     {
         return Error_(false, "Failed to connect to database.", L.f + ex.Details(false, false));
     }
     //return Debug_(true, "Successfully connected to database.", $"logged in as : “{userName}”");
     return Debug_(true, "Successfully connected to database.", "");
 }

        public static async Task<long> NrRows(string pathToExcel,string SheetName= "[Sheet1$]")
        {
            using (var m = new OleDbConnection())
            {
                m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(pathToExcel, false);
                await m.OpenAsync();
                var query = @"Select count(*) From " + SheetName;
                using (var cmd = new OleDbCommand(query, m))
                {
                    var dr = await cmd.ExecuteScalarAsync();
                    return long.Parse(dr.ToString());
                    
                }

            }
        }

        //static void getLegalitySource(int multiversid)
        //{
        //    WebRequest request = HttpWebRequest.Create("http://gatherer.wizards.com/Pages/Card/Printings.aspx?multiverseid=" + multiversid);
        //    request.Method = "GET";
        //    using (StreamReader reader = new StreamReader(request.GetResponse().GetResponseStream()))
        //    {
        //        string source = reader.ReadToEnd();
        //        getLegality(multiversid, source);
        //    }
        //}

        //static void getLegality(int multiversid, string source)
        //{
        //    string legacy = null;
        //    List<string> formats = new List<string>();
        //    List<string> legalities = new List<string>();

        //    int tablestart = source.IndexOf("<table class=\"cardList\" cellspacing=\"0\" cellpadding=\"2\">") + 1;
        //    legacy = source.Substring(tablestart, (source.Length - tablestart));
        //    tablestart = legacy.IndexOf("<table class=\"cardList\" cellspacing=\"0\" cellpadding=\"2\">");
        //    int tableend = legacy.IndexOf("</table>", tablestart);
        //    legacy = legacy.Substring(tablestart, (tableend - tablestart));
        //    string[] legacysplit = legacy.Split(new string[] { "<tr class=\"cardItem evenItem\">", "<tr class=\"cardItem oddItem\">" }, StringSplitOptions.RemoveEmptyEntries);
        //    for (int i = 1; i < legacysplit.Length; i++)
        //    {
        //        string[] split = legacysplit[i].Split(new string[] { "<td style=\"width:40%;\">", "<td style=\"text-align:center;\">", "<td>" }, StringSplitOptions.RemoveEmptyEntries);
        //        int end = split[1].IndexOf("</td>");
        //        string format = split[1].Substring(0, end);
        //        format = format.Replace("\n", string.Empty);
        //        format = format.Replace("\r", string.Empty);
        //        format = format.Replace("\t", string.Empty);
        //        format = format.Trim();
        //        formats.Add(format);
        //        end = split[2].IndexOf("</td>");
        //        string legality = split[2].Substring(0, end);
        //        legality = legality.Replace("\n", string.Empty);
        //        legality = legality.Replace("\r", string.Empty);
        //        legality = legality.Replace("\t", string.Empty);
        //        legality = legality.Trim();
        //        legalities.Add(legality);
        //    }
        //    saveLegality(multiversid, formats, legalities);
        //}



        /// <summary>
        /// Takes all the information for this one card and stores it as a new card in the database.
        /// </summary>
        /// <param name="Card"></param>
        /// <returns></returns>
        private static async Task saveCard(Classes.CardInfo Card)
        {

            if (Card.cardtext == null)
                Card.cardtext = "";
            if (Card.convmanacost == null)
                Card.convmanacost = "";
            if (Card.power == null)
                Card.power = "";
            if (Card.rarity == null)
                Card.rarity = "";
            if (Card.toughness == null)
                Card.toughness = "";

            try
            {
                OleDbConnection DBcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
                await DBcon.OpenAsync(); //opens OLEBD connection to Database

                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = "INSERT INTO Cards([MultiverseID], [Name], [ConvManaCost], [Type], [CardText], [Power], [Toughness], [Expansion], [Rarity], [ImgURL]) VALUES (@MultiverseID, @Name, @ConvManaCost, @Type, @CardText, @Power, @Toughness, @Expansion, @Rarity, @ImgURL)";
                //Adds a new card and all the information for it to the CardTable
                cmd.Parameters.Add("@MultiverseID", OleDbType.Integer).Value = Card.multiverseid;
                cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = Card.name;
                cmd.Parameters.Add("@ConvManaCost", OleDbType.VarChar).Value = Card.convmanacost;
                cmd.Parameters.Add("@Type", OleDbType.VarChar).Value = Card.type;
                cmd.Parameters.Add("@CardText", OleDbType.VarChar).Value = Card.cardtext;
                cmd.Parameters.Add("@Power", OleDbType.VarChar).Value = Card.power;
                cmd.Parameters.Add("@Toughness", OleDbType.VarChar).Value = Card.toughness;
                cmd.Parameters.Add("@Expansion", OleDbType.VarChar).Value = Card.expansion;
                cmd.Parameters.Add("@Rarity", OleDbType.VarChar).Value = Card.rarity;
                cmd.Parameters.Add("@ImgURL", OleDbType.VarChar).Value = Card.imgurl;
                cmd.Connection = DBcon;
                cmd.ExecuteNonQuery();
                DBcon.Close();

                Console.WriteLine(Card.multiverseid + " (" + Card.name + ") was added to the database.");
            }
            catch (Exception) { Console.WriteLine(Card.name + " was skipped"); };
        }

        /// <summary>
        /// Creates and opens a SQL connection.
        /// </summary>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        /// <remarks>
        /// The caller of this method is responsible for closing the connection.
        /// </remarks>
        private async Task<OleDbConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            var con = new OleDbConnection(ConnectionString);
            await con.OpenAsync(cancellationToken).ConfigureAwait(false);

            if (m_ServerDefaultSettings == null)
            {
                var temp = new SqlServerEffectiveSettings();
                await temp.ReloadAsync(con, null);
#if !Thread_Missing
                Thread.MemoryBarrier();
#endif 
                m_ServerDefaultSettings = temp;
            }

            var sql = BuildConnectionSettingsOverride();

            if (sql.Length > 0)
                using (var cmd = new OleDbCommand(sql.ToString(), con))
                    await cmd.ExecuteNonQueryAsync();

            return con;
        }

        private async void OleDBButton_Click(object sender, RoutedEventArgs e)
        {
            //此方式需要先安裝 http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
            List<Student> students = GenerateRandomData(100);

            try
            {
                SaveFileDialog saveDialog = new SaveFileDialog()
                {
                    DefaultExt = ".xlsx",
                    FileName = "Sample",
                    Filter = "Excel |*.xlsx"
                };
                if (saveDialog.ShowDialog() == true)
                {
                    string Connstring = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={saveDialog.FileName};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                    using (OleDbConnection Conn = new OleDbConnection(Connstring))
                    {
                        await Conn.OpenAsync();

                        //create sheet
                        string sql = "CREATE TABLE Test (ID int,Name VarChar,Age int,Gender VarChar,phone VarChar,Eng int,Math int)";
                        using (OleDbCommand cmd = new OleDbCommand(sql, Conn))
                        {
                            await cmd.ExecuteNonQueryAsync();

                            //insert data
                            cmd.CommandText = "Insert into Test values (@ID,@Name,@Age,@Gender,@phone,@Eng,@Math)";
                            for (int i = 0; i < students.Count; i++)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add("@ID", OleDbType.Integer).Value = students[i].ID;
                                cmd.Parameters.Add("@Name", OleDbType.VarChar).Value = students[i].Name;
                                cmd.Parameters.Add("@Age", OleDbType.Integer).Value = students[i].Age;
                                cmd.Parameters.Add("@Gender", OleDbType.VarChar).Value = students[i].Gender ? "男" : "女";
                                cmd.Parameters.Add("@phone", OleDbType.VarChar).Value = students[i].phone;
                                cmd.Parameters.Add("@Eng", OleDbType.Integer).Value = students[i].Eng;
                                cmd.Parameters.Add("@Math", OleDbType.Integer).Value = students[i].Math;
                                await cmd.ExecuteNonQueryAsync();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                StatusText.Text = ex.Message;
            }
        }

        /// <summary>
        /// Receives file bytes from database by given file name
        /// </summary>
        /// <param name="name">Name of file to load</param>
        public static async Task<byte[]> LoadFileAsync(string name)
        {
            string connectionString = defaultConnectionString;

            using (var connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                try
                {
                    await connection.OpenAsync();

                    string query = "SELECT FileData FROM Files WHERE name = @name;";

                    using (var command = new System.Data.OleDb.OleDbCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@name", name);

                        return (byte[])await command.ExecuteScalarAsync();
                    }
                }
                catch(Exception ex)
                {
                    TextEditor.WPFMessageBoxException(ex);
                    return null;
                }
            }
        }

    async Task<object> ExecuteQuery(string connectionString, string commandString)
    {
        OleDbConnection connection = null;
        try {
            using (connection = new OleDbConnection(connectionString))
            {
                await connection.OpenAsync();
                
                using (var command = new OleDbCommand(commandString, connection))
                {

                    List<object> rows = new List<object>();

                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        IDataRecord record = (IDataRecord)reader;
                        while (await reader.ReadAsync())
                        {
                            var dataObject = new ExpandoObject() as IDictionary<string, Object>;
                            var resultRecord = new object[record.FieldCount];
                            record.GetValues(resultRecord);

                            for (int i = 0; i < record.FieldCount; i++)
                            {      
                                Type type = record.GetFieldType(i);
                                if (resultRecord[i] is System.DBNull)
                                {
                                    resultRecord[i] = null;
                                }
                                else if (type == typeof(byte[]) || type == typeof(char[]))
                                {
                                    resultRecord[i] = Convert.ToBase64String((byte[])resultRecord[i]);
                                }
                                else if (type == typeof(Guid) || type == typeof(DateTime))
                                {
                                    resultRecord[i] = resultRecord[i].ToString();
                                }
                                else if (type == typeof(IDataReader))
                                {
                                    resultRecord[i] = "<IDataReader>";
                                }

                                dataObject.Add(record.GetName(i), resultRecord[i]);
                            }

                            rows.Add(dataObject);
                        }

                        return rows;
                    } 
                }
            }
        }
        catch(Exception e)
        {
            throw new Exception("ExecuteQuery Error", e);
        }
        finally
        {
            connection.Close();
        }
    }

    async Task<object> ExecuteNonQuery(string connectionString, string commandString)
    {
        OleDbConnection connection = null;
        try
        {
            using (connection = new OleDbConnection(connectionString))
            {
                await connection.OpenAsync();
                
                using (var command = new OleDbCommand(commandString, connection))
                {
                    return await command.ExecuteNonQueryAsync();
                }
            }
        }
        catch(Exception e)
        {
            throw new Exception("ExecuteNonQuery Error", e);
        }
        finally
        {
            connection.Close();
        }

    }