C# OleDbCommand.ExecuteNonQueryAsync方法代码示例

        /// <summary>
        /// Method to Insert Record in the Excel
        /// S1. If the EmpNo =0, then the Operation is Skipped.
        /// S2. If the Employee is already exist, then it is taken for Update
        /// </summary>
        /// <param name="Emp"></param>
        public async Task<bool> InsertOrUpdateRowInExcelAsync(Employee Emp)
        {
            bool IsSave = false;
            //S1
            if (Emp.EmpNo != 0)
            {
                await Conn.OpenAsync();
                Cmd = new OleDbCommand();
                Cmd.Connection = Conn;
                Cmd.Parameters.AddWithValue("@EmpNo", Emp.EmpNo);
                Cmd.Parameters.AddWithValue("@EmpName", Emp.EmpName);
                Cmd.Parameters.AddWithValue("@Salary", Emp.Salary);
                Cmd.Parameters.AddWithValue("@DeptName", Emp.DeptName);
                //S2
                if (!CheckIfRecordExistAsync(Emp).Result)
                {
                    Cmd.CommandText = "Insert into [Sheet1$] values (@EmpNo,@EmpName,@Salary,@DeptName)";
                }
                else
                {
                    if (Emp.EmpName != String.Empty || Emp.DeptName != String.Empty)
                    {
                        Cmd.CommandText = "Update [Sheet1$] set [email protected],[email protected],[email protected],[email protected] where [email protected]";
                    }
                }
                int result = await Cmd.ExecuteNonQueryAsync();
                if (result > 0)
                {
                    IsSave = true;
                }
                Conn.Close();
            }
            return IsSave;

        }

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

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

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

    }

        /// <summary>
        /// Executes the specified operation asynchronously.
        /// </summary>
        /// <param name="executionToken"></param>
        /// <param name="implementation"></param>
        /// <param name="cancellationToken"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        protected override async Task<int?> ExecuteAsync(CommandExecutionToken<OleDbCommand, OleDbParameter> executionToken, CommandImplementationAsync<OleDbCommand> implementation, CancellationToken cancellationToken, object state)
        {
            if (executionToken == null)
                throw new ArgumentNullException("executionToken", "executionToken is null.");
            if (implementation == null)
                throw new ArgumentNullException("implementation", "implementation is null.");
            var currentToken = executionToken as AccessCommandExecutionToken;
            if (currentToken == null)
                throw new ArgumentNullException("executionToken", "only AccessCommandExecutionToken is supported.");

            var startTime = DateTimeOffset.Now;

            try
            {
                using (var con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false))
                {
                    int? rows = null;
                    while (currentToken != null)
                    {
                        OnExecutionStarted(currentToken, startTime, state);
                        using (var cmd = new OleDbCommand())
                        {
                            cmd.Connection = con;
                            if (DefaultCommandTimeout.HasValue)
                                cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
                            cmd.CommandText = currentToken.CommandText;
                            cmd.CommandType = currentToken.CommandType;
                            foreach (var param in currentToken.Parameters)
                                cmd.Parameters.Add(param);

                            currentToken.ApplyCommandOverrides(cmd);

                            if (currentToken.ExecutionMode == AccessCommandExecutionMode.Materializer)
                                rows = await implementation(cmd);
                            else if (currentToken.ExecutionMode == AccessCommandExecutionMode.ExecuteScalarAndForward)
                                currentToken.ForwardResult(await cmd.ExecuteScalarAsync());
                            else
                                rows = await cmd.ExecuteNonQueryAsync();
                            executionToken.RaiseCommandExecuted(cmd, rows);
                            OnExecutionFinished(currentToken, startTime, DateTimeOffset.Now, rows, state);
                        }
                        currentToken = currentToken.NextCommand;
                    }
                    return rows;
                }
            }
            catch (Exception ex)
            {
                if (cancellationToken.IsCancellationRequested) //convert Exception into a OperationCanceledException 
                {
                    var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
                    OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
                    throw ex2;
                }
                else
                {
                    OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
                    throw;
                }
            }
        }

        public static void Main(string[] args)
        {
            var connectionString = @"Data Source=.;Initial Catalog=Northwind;Integrated Security=True;";
            var connectionStringForExcel = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=file.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';";

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

                SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Categories", connection);
                int rows = (int)command.ExecuteScalar();
                Console.WriteLine("All rows in table Categories are: {0}", rows);
            }

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

                SqlCommand command = new SqlCommand("SELECT * FROM Categories", connection);
                var rows = command.ExecuteReader();
                while (rows.Read())
                {
                    Console.WriteLine(rows["CategoryName"] + " " + rows["Description"]);
                }
            }

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

                SqlCommand command = new SqlCommand("SELECT p.ProductName as Product, c.CategoryName as Category FROM Categories c JOIN Products p ON c.CategoryID = p.CategoryID", connection);
                var rows = command.ExecuteReader();
                while (rows.Read())
                {
                    Console.WriteLine("Product:" + rows["Product"] + "; Category:" + rows["Category"]);
                }
            }

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

                SqlCommand command = new SqlCommand(
                    @"INSERT INTO Products 
                    (ProductName, SupplierID, CategoryID, Discontinued)
                    VALUES
                    (@productName,@supplierId,@categoryId,@discontinued)", connection);

                command.Parameters.AddWithValue("@productName", "test");
                command.Parameters.AddWithValue("@supplierId", 2);
                command.Parameters.AddWithValue("@categoryId", 2);
                command.Parameters.AddWithValue("@discontinued", 0);

                command.ExecuteNonQuery();
            }

            using (SqlConnection dbConn = new SqlConnection(connectionString))
            {
                dbConn.Open();

                SqlCommand cmd = new SqlCommand(
                    "SELECT CategoryID, Picture FROM Categories", dbConn);
                SqlDataReader reader = cmd.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        var categoryName = (int)reader["CategoryID"];
                        var image = (byte[])reader["Picture"];

                        WriteBinaryFile(categoryName + ".jpg", image);
                    }
                }
            }

            using (OleDbConnection dbCon = new OleDbConnection(connectionStringForExcel))
            {
                dbCon.Open();

                OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", dbCon);
                var reader = command.ExecuteReader();


                while (reader.Read())
                {
                    var name = reader["Name"];
                    var score = reader["Score"];

                    Console.WriteLine("Name: {0}, Score: {1}", name, score);
                }

                OleDbCommand com = new OleDbCommand("INSERT INTO [Sheet1$] VALUES(@Name, @Score)", dbCon);

                com.Parameters.AddWithValue("@Name", "test");
                com.Parameters.AddWithValue("@Score", 2);

                com.ExecuteNonQueryAsync();
            }

            Find(connectionString);
//.........这里部分代码省略.........