C# SqlConnection.OpenAsync方法代码示例

        /// <summary>
        ///   The execute.
        /// </summary>
        protected override void Execute()
        {
            WorkflowTrace.Verbose("DoDbOperationTask Executing");

            using (var connection = new SqlConnection(this.ConnectionString))
            {
                WorkflowTrace.Verbose("DoDbOperationTask Opening Database");

                // Pass the this.CancellationToken to all async operations
                connection.OpenAsync(this.CancellationToken).Wait();
                var cmd = new SqlCommand("SELECT * FROM Customers WHERE CUST_ID = @CustomerId", connection);

                // Access arguments setup in the BeforeExecute method
                cmd.Parameters.AddWithValue("@CustomerId", this.Inputs.CustomerId);

                WorkflowTrace.Verbose("DoDbOperationTask Executing Reader");

                // Pass the this.CancellationToken to all async operations
                using (var reader = cmd.ExecuteReaderAsync(this.CancellationToken).Result)
                {
                    // Pass the this.CancellationToken to all async operations
                    while (reader.ReadAsync(this.CancellationToken).Result)
                    {
                        // Inside of a loop, check the cancellation this.CancellationToken
                        this.CancellationToken.ThrowIfCancellationRequested();

                        // Pass the this.CancellationToken to other classes and methods
                        DoSomethingWithCustomer(reader, this.CancellationToken);
                    }
                }
            }
        }

        /// <summary>
        ///     Retrives an given user.
        /// </summary>
        /// <param name="username">Username of given user.</param>
        /// <returns><c>SqlDataReader</c> with user data.</returns>
        public static async Task<List<string>> RetrieveUser(string username)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);

            //search for given username
            const string query = "SELECT * FROM Users WHERE [email protected]";
            SqlCommand sqlCommand = new SqlCommand(query, connection);
            SqlParameter usernameParameter = new SqlParameter("@username", SqlDbType.NVarChar);
            sqlCommand.Parameters.Add(usernameParameter);
            usernameParameter.Value = username;
            await connection.OpenAsync();

            var userData = new List<string>();

            using (SqlDataReader sqlReader = await sqlCommand.ExecuteReaderAsync())
            {
                while (await sqlReader.ReadAsync())
                {
                    userData.Add(sqlReader["Username"].ToString());
                    userData.Add(sqlReader["Hash"].ToString());
                    userData.Add(sqlReader["Role"].ToString());
                }
            }

            connection.Close();
            //send back userdata list.
            return userData;
        }

        public async Task ReaderModel()
        {
            using (var con = new SqlConnection(connectionString))
            {
                var sproc = SimulatedSelectStatement.Create();

                var cmd = sproc.Build(con);
                await con.OpenAsync();

                var reader = await cmd.ExecuteReaderAsync();

                Assert.IsTrue(reader.Read());

                var loader = new Loader<SelectData>();
                var obj = loader.Model(reader);

                Assert.IsNotNull(obj);
                Assert.AreEqual(sproc.TestInt, obj.Identifier);
                Assert.AreEqual(sproc.TestBigInt, obj.BigInt);
                Assert.AreEqual(sproc.TestBit, obj.Bit);
                Assert.AreEqual(sproc.TestDate.Value.Date, obj.Date.Date);
                Assert.AreEqual(sproc.TestDateTime.Value.Date, obj.DateTime.Date);
                Assert.AreEqual(sproc.TestDateTime2.Value.Date, obj.DateTime2.Date);
                Assert.AreEqual(sproc.TestDecimal, obj.Decimal);
                Assert.AreEqual(sproc.TestFloat, obj.Float);
                Assert.AreEqual(Math.Round((decimal)sproc.TestMoney, 4), obj.Money);
                Assert.AreEqual(sproc.TestNChar, obj.NChar.ToString());
                Assert.AreEqual(sproc.TestNText, obj.NText);
                Assert.AreEqual(sproc.TestText, obj.Text);
                CollectionAssert.AreEqual(sproc.TestBinary, obj.Binary);
                CollectionAssert.AreEqual(sproc.TestImage, obj.Image);
                Assert.AreEqual(sproc.TestGuid, obj.Unique);
            }
        }

        async private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count == 0)
                return;
            SqlConnection connection = new SqlConnection();
            try
            {
                connection.ConnectionString = @"Data Source=(localdb)\v11.0;AttachDbFilename=D:\programming\cours\ITStepProjects\ADO.NET\6_CarsStore\6_CarsStore\DBAuto.mdf;Integrated Security=True";
                await connection.OpenAsync();

                SqlCommand cmdSelect = connection.CreateCommand();
                cmdSelect.CommandText = "SELECT * FROM TMotor WHERE [email protected]";
                string IDD = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                cmdSelect.Parameters.AddWithValue("@IDD", IDD);

                SqlDataReader reader = await cmdSelect.ExecuteReaderAsync();
                
                await reader.ReadAsync();
                iDDTextBox.Text = reader[0].ToString();
                markaTextBox.Text = reader[1].ToString();
                mTextBox.Text = reader[2].ToString();
                countryTextBox.Text = reader[3].ToString();
                dateTextBox1.Text = reader[4].ToString();

            }
            finally
            {
                connection.Close();
            }
        }

 public void BeginVerify()
 {
     string connectionString = ConnectionInfoToConnectionString(_connectionInfo);
     _connection = new SqlConnection(connectionString);
     _task = _connection.OpenAsync();
     _task.GetAwaiter().OnCompleted(OnOpenComplete);
 }

        public async Task<List<SysObject>> GetSysObjects()
        {
            const string sqlText = "select name as Name, object_id as ObjectId, type as Type, type_desc as TypeDescription from sys.objects";
            using (var connection = new SqlConnection(connectionString))
            using (var command = new SqlCommand(cmdText: sqlText, connection: connection))
            {
                await connection.OpenAsync().ConfigureAwait(continueOnCapturedContext: false);

                var result = new List<SysObject>();

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while(await reader.ReadAsync())
                    {
                        var item = new SysObject
                        {
                            Name = reader.GetString(0),
                            ObjectId = reader.GetInt32(1),
                            Type = reader.GetString(2),
                            TypeDescription = reader.GetString(3)
                        };

                        result.Add(item);
                    }

                    return result;
                }
            }
        }

        public async void ShouldNotDeadlock()
        {
            // Not all library code used to have .ConfigureAwait(false);
            // This caused this code to run forever. Async test, opening async connection, running the sync db commands which use the async versions internally. 
            // See these topics for more information about .ConfigureAwait(false);
            // http://stackoverflow.com/questions/13489065/best-practice-to-call-configureawait-for-all-server-side-code/13489639#13489639
            // http://blog.stephencleary.com/2012/07/dont-block-on-async-code.html

            string connectionstring = "Server=.;Database=master;Integrated Security=true";

            SqlConnection conn = new SqlConnection(connectionstring);
            await conn.OpenAsync();

            string query = "select top 1 TABLE_NAME from information_schema.tables";
            DatabaseHelper.ExecuteDataSet(connectionstring, CommandType.Text, query);
            DatabaseHelper.ExecuteNonQuery(connectionstring, CommandType.Text, query);
            DatabaseHelper.ExecuteScalar<string>(connectionstring, CommandType.Text, query);
            using (var dbReader = DatabaseHelper.ExecuteReader(connectionstring, CommandType.Text, query))
            {
                if (dbReader.HasRows)
                {
                    while (dbReader.Read())
                    {
                    }
                }
            }

        }

        private async void ReadPeopleData()
        {
            Console.WriteLine("Reading people data from database");
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = new SqlCommand("SELECT * FROM People", connection);
                await connection.OpenAsync();

                SqlDataReader dataReader = await command.ExecuteReaderAsync();

                while(await dataReader.ReadAsync())
                {
                    string formatStringwithMiddleName = "Person ({0}) is named {1} {2} {3}";
                    string formatStringWithoutMiddleName = "Person ({0}) is names {1} {2}";

                    if(dataReader["MiddleName"]==null)
                    {
                        Console.WriteLine(formatStringWithoutMiddleName,
                            dataReader["id"],
                            dataReader["FirstName"].ToString(),
                            dataReader["LastName"].ToString());
                    }
                    else
                    {
                        Console.WriteLine(formatStringwithMiddleName,
                            dataReader["id"],
                            dataReader["FirstName"].ToString(),
                            dataReader["MiddleName"].ToString(),
                            dataReader["LastName"].ToString());
                    }
                }
                dataReader.Close();
            }
        }

        public static async Task InsertPurchaseOrderHeaderAsync(string cnStr)
        {
            const string queryString =
                "INSERT INTO Purchasing.PurchaseOrderHeader " +
                "(RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight, ModifiedDate) " +
                "VALUES " +
                "(@RevisionNumber, @Status, @EmployeeID, @VendorID, @ShipMethodID, @OrderDate, @ShipDate, @SubTotal, @TaxAmt, @Freight, @ModifiedDate)";

            var dt = DateTime.UtcNow;

            using (var cn = new SqlConnection(cnStr))
            {
                using (var cmd = new SqlCommand(queryString, cn))
                {
                    cmd.Parameters.AddWithValue("@RevisionNumber", 1);
                    cmd.Parameters.AddWithValue("@Status", 4);
                    cmd.Parameters.AddWithValue("@EmployeeID", 258);
                    cmd.Parameters.AddWithValue("@VendorID", 1580);
                    cmd.Parameters.AddWithValue("@ShipMethodID", 3);
                    cmd.Parameters.AddWithValue("@OrderDate", dt);
                    cmd.Parameters.AddWithValue("@ShipDate", dt);
                    cmd.Parameters.AddWithValue("@SubTotal", 123.40M);
                    cmd.Parameters.AddWithValue("@TaxAmt", 12.34M);
                    cmd.Parameters.AddWithValue("@Freight", 5.76M);
                    cmd.Parameters.AddWithValue("@ModifiedDate", dt);

                    await cn.OpenAsync().ConfigureAwait(false);
                    await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }
        }

        public async Task DataTableDictionary()
        {
            using (var con = new SqlConnection(connectionString))
            {
                var sproc = SimulatedSelectStatement.Create();

                var cmd = sproc.Build(con);

                var loader = new DynamicLoader();
                await con.OpenAsync();
                var adapter = new SqlDataAdapter(cmd);

                var ds = new DataSet();
                adapter.Fill(ds);
                var table = ds.Tables[0];
                var obj = loader.Dictionary(table);

                Assert.IsNotNull(obj);
                Assert.AreEqual(sproc.TestInt, obj["Identifier"]);
                Assert.AreEqual(sproc.TestBigInt, obj["BigInt"]);
                Assert.AreEqual(sproc.TestBit, obj["Bit"]);
                Assert.AreEqual(sproc.TestDate.Value.Date, ((DateTime)obj["Date"]).Date);
                Assert.AreEqual(sproc.TestDateTime.Value.Date, ((DateTime)obj["DateTime"]).Date);
                Assert.AreEqual(sproc.TestDateTime2.Value.Date, ((DateTime)obj["DateTime2"]).Date);
                Assert.AreEqual(sproc.TestDecimal, obj["Decimal"]);
                Assert.AreEqual(sproc.TestFloat, obj["Float"]);
                Assert.AreEqual(Math.Round((decimal)sproc.TestMoney, 4), obj["Money"]);
                Assert.AreEqual(sproc.TestNChar, obj["NChar"]);
                Assert.AreEqual(sproc.TestNText, obj["NText"]);
                Assert.AreEqual(sproc.TestText, obj["Text"]);
                CollectionAssert.AreEqual(sproc.TestBinary, obj["Binary"] as byte[]);
                CollectionAssert.AreEqual(sproc.TestImage, obj["Image"] as byte[]);
                Assert.AreEqual(sproc.TestGuid, obj["Unique"]);
            }
        }

        public async Task DataTableDictionaries()
        {
            using (var con = new SqlConnection(connectionString))
            {
                var sproc = new dboSelectMultipleStatement();

                var cmd = sproc.Build(con);

                var loader = new DynamicLoader();
                await con.OpenAsync();
                var adapter = new SqlDataAdapter(cmd);

                var ds = new DataSet();
                adapter.Fill(ds);
                var objs = loader.Dictionaries(ds.Tables[0]);

                Assert.IsNotNull(objs);

                var i = 0;
                foreach (var obj in objs)
                {
                    Assert.AreEqual(i, obj["Identifier"]);
                    i++;
                }
            }
        }

        public static async Task EnsureTablesExist(string connectionString)
        {
            var asm = Assembly.GetExecutingAssembly();
            using (var stream = asm.GetManifestResourceStream("iSynaptic.Core.Persistence.AggregateStore.sql"))
            using (var reader = new StreamReader(stream))
            {
                string script = await reader.ReadToEndAsync();
                var matches = _scriptRegex.Matches(script);

                using (var connection = new SqlConnection(connectionString))
                {
                    var commands = matches.OfType<Match>()
                        .Select(x => x.Groups["script"].Value)
                        .Select(sql => new SqlCommand
                        {
                            Connection = connection,
                            CommandType = CommandType.Text,
                            CommandText = sql
                        });

                    await connection.OpenAsync();

                    foreach(var command in commands)
                        await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task<Person> GetPersonById(int id)
        {
            Person person = null;
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select p.id, p.name, p.description from People as p where [email protected]";

                    await connection.OpenAsync();

                    command.Parameters.Add(new SqlParameter("@id", id));

                    var reader = await command.ExecuteReaderAsync();
                    while (await reader.ReadAsync())
                    {
                      person = new Person()
                      {
                          Id = reader.Get<int>("id"),
                          Description = reader.ReadString("Description"),
                          Name = reader.ReadString("Name")
                      };
                    }
                    reader.Close();
                }
                connection.Close();
            }

            return person;
        }

        /// <summary>
        ///     Creates a new user
        /// </summary>
        /// <param name="username">Username</param>
        /// <param name="password">Password</param>
        /// <returns><c>True</c> if successful</returns>
        public static async Task<bool> CreateUser(string username, string password)
        {
            const string query = "INSERT INTO Users (Username, Hash, Role) VALUES(@username, @hash, @role)";

            string passwordHash = BCryptHelper.HashPassword(password, BCryptHelper.GenerateSalt());

            try
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                SqlCommand sqlCommand = new SqlCommand(query, connection);
                SqlParameter usernameParameter = new SqlParameter("@username", SqlDbType.NVarChar);
                SqlParameter hashParameter = new SqlParameter("@hash", SqlDbType.NVarChar);
                SqlParameter roleParameter = new SqlParameter("@role", SqlDbType.NVarChar);
                sqlCommand.Parameters.Add(usernameParameter);
                sqlCommand.Parameters.Add(hashParameter);
                sqlCommand.Parameters.Add(roleParameter);
                usernameParameter.Value = username;
                hashParameter.Value = passwordHash;
                //TODO: make sure Admin can change the role depending on what he needs.
                roleParameter.Value = "Admin";
                await connection.OpenAsync();
                sqlCommand.ExecuteNonQuery();

                connection.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public async Task<Album> GetAlbum(long albumId)
        {
            Album albumAggregate;

            using(var connection = new SqlConnection(_connectionString))
            {
                var cmd = new CommandDefinition(SqlStatements.SelectAlbumAggregate, new { albumId = albumId });
                await connection.OpenAsync();
                var albums = await connection.QueryAsync<Album, Artist, AlbumArt, Album>(cmd, (album, artist, art) => { album.Artist = artist; album.AlbumArt = art; return album; });
                albumAggregate = albums.FirstOrDefault();

                if (albumAggregate == null)
                    return null;

                cmd = new CommandDefinition(SqlStatements.SelectAlbumTracksByAlbum, new { albumId = albumId });
                var tracks = await connection.QueryAsync<AlbumTrack>(cmd);
                var trackList = tracks.OrderBy(track => track.TrackNumber)
                    .Select(track => new Track { Id = track.Id, Name = track.Name, DuationMs = track.DurationMs })
                    .ToList();

                albumAggregate.TrackList = trackList;
            }

            return albumAggregate;
        }