C# SqlCommand.ExecuteReaderAsync方法代码示例

        /// <inheritdoc/>
        public async Task<FileDetails> GetDetailsForFileById(int id)
        {
            FileDetails details = null;

            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand("GetFileMetaDatabyId", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.AddWithValue("Id", id);

                    await connection.OpenAsync();

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            details = new FileDetails();
                            details.ApplicationName = reader["ApplicationName"].ToString();
                            details.FileName = reader["FileName"].ToString();
                            details.FileId = (int)reader["Id"];
                            details.MimeType = reader["MimeType"].ToString();
                            details.StoragePath = reader["InternalStoragePath"].ToString();
                            details.Checksum = reader["Checksum"].ToString();
                            details.DateStoredUtc = (DateTime)reader["DateStoredUtc"];
                        }
                    }
                }
            }

            return details;
        }

        /// <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>
        /// Performs a sequence of non blocking database operations.
        /// </summary>
        private async static Task NonBlockingDatabaseOperations()
        {
            // Build the database connection.
            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleHttpApplication"].ConnectionString))
            {
                // Open the database connection.
                await sqlConnection.OpenAsync();

                // Insert the database row.
                SqlCommand insertSqlCommand = new SqlCommand("INSERT INTO [Session] VALUES('" + Guid.NewGuid() + "', 'Session Alpha', '2012-06-27 10:05:45'); SELECT CAST(SCOPE_IDENTITY() AS INT);", sqlConnection);
                int sessionID = (int)await insertSqlCommand.ExecuteScalarAsync();

                // Select the database row.
                SqlCommand selectSqlCommand = new SqlCommand("SELECT * FROM [Session] WHERE [SessionID] = " + sessionID, sqlConnection);
                SqlDataReader sqlDataReader = await selectSqlCommand.ExecuteReaderAsync();
                await sqlDataReader.ReadAsync();
                sqlDataReader.Close();

                // Update the database row.
                SqlCommand updateSqlCommand = new SqlCommand("UPDATE [Session] SET [SessionCode] = '" + Guid.NewGuid() + "', [Name] = 'Session Beta', [StartDate] = '2013-07-28 11:06:46' WHERE [SessionID] = " + sessionID, sqlConnection);
                await updateSqlCommand.ExecuteNonQueryAsync();

                // Delete the database row.
                SqlCommand deleteSqlCommand = new SqlCommand("DELETE FROM [Session] WHERE [SessionID] = " + sessionID, sqlConnection);
                await deleteSqlCommand.ExecuteNonQueryAsync();
            }
        }

        /// <summary>
        /// Returns List of Property objects based on the table
        /// </summary>
        /// <param name="conString">User input Connection String</param>
        /// <param name="query">Query based on table</param>
        /// <returns>Returns list of columns, reference to a table</returns>
        public async Task<IEnumerable<Property>> ExecuteReaderOnColumnsAsync(string conString, string query)
        {
            var resultList = new List<Property>();
            using (var connection = new SqlConnection(conString))
            {
                connection.Open();
                string commandText = query;
                SqlCommand command = new SqlCommand
                {
                    CommandText = commandText,
                    Connection = connection
                };

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        resultList.Add(new Property
                        {
                            TableName = reader.GetString(0),
                            ColumnName = reader.GetString(1),
                            DataType = reader.GetString(2),
                            MaxLength = reader.GetValue(3).ToString(),
                            IsNullable = reader.GetString(4) == "YES",
                            Schema = reader.GetString(5),
                            TableCatalog = reader.GetString(6)
                        });
                    }
                }                
            }
            return resultList;
        }

        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 async Task<List<Session>> GetSpeakerSessions(int speakerId)
		{
			Debug.WriteLine("START: GetSpeakerSessions");
			var list = new List<Session>();
			using (var connection = new SqlConnection("Data Source=(local); Initial Catalog=MADExpoKJ;Integrated Security=SSPI;"))
			{
				using (var command = new SqlCommand("SELECT * FROM Session WHERE SpeakerId = @SpeakerId ORDER BY Title", connection))
				{
					command.Parameters.AddWithValue("SpeakerId", speakerId);
					connection.Open();
					using (var reader = await command.ExecuteReaderAsync())
					{
						while (await reader.ReadAsync())
						{
							var session = new Session
							{
								SessionId = reader.GetInt32(0),
								SpeakerId = reader.GetInt32(1),
								Title = reader.GetString(2),
								Level = reader.GetInt16(3),
								Abstract = reader.GetString(4)
							};
							list.Add(session);
						}
					}
				}
			}
			Debug.WriteLine("END: GetSpeakerSessions");
			return list;
		}

		public async Task<Speaker> GetSpeaker(int speakerId)
		{
			Debug.WriteLine("START: GetSpeaker");
			using (var connection = new SqlConnection("Data Source=(local); Initial Catalog=MADExpoKJ;Integrated Security=SSPI;"))
			{
				using (var command = new SqlCommand("SELECT * FROM Speaker WHERE SpeakerId = @SpeakerId", connection))
				{
					command.Parameters.AddWithValue("SpeakerId", speakerId);
					connection.Open();
					using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SingleRow))
					{
						while (await reader.ReadAsync())
						{
							Debug.WriteLine("END: GetSpeaker");
							return new Speaker
							{
								SpeakerId = reader.GetInt32(0),
								FirstName = reader.GetString(1),
								LastName = reader.GetString(2),
								Twitter = reader.IsDBNull(3) ? null : reader.GetString(3),
								Bio = reader.GetString(4)
							};
						}
						return null;
					}
				}
			}
		}

		public async Task<IList<Speaker>> GetSpeakers()
		{
			var list = new List<Speaker>();
			using (var connection = new SqlConnection("Data Source=(local); Initial Catalog=MADExpoKJ;Integrated Security=SSPI;"))
			{
				using (var command = new SqlCommand("SELECT * FROM Speaker ORDER BY LastName, FirstName", connection))
				{
					connection.Open();
					using (var reader = await command.ExecuteReaderAsync())
					{
						while (await reader.ReadAsync())
						{
							var speaker = new Speaker
							{
								SpeakerId = reader.GetInt32(0),
								FirstName = reader.GetString(1),
								LastName = reader.GetString(2),
								Twitter = reader.IsDBNull(3) ? null : reader.GetString(3),
								Bio = reader.GetString(4)
							};
							list.Add(speaker);
						}
					}
				}
			}
			return list;
		}

        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 static Task<List<Speaker>> Select( SqlConnection Connection, SqlTransaction Transaction = null ) {
			List<Speaker> Speakers = new List<Speaker>();

			using( SqlCommand Command = new SqlCommand() ) {
				Command.Connection = Connection;
				if( null != Transaction )
					Command.Transaction = Transaction;

				Command.CommandText = "SELECT Id, EmailAddress, DisplayName, PasswordHash, Bio, Twitter, Website, DisplayEmail, DisplayTwitter, DisplayWebsite, SessionToken, SessionExpires FROM Speakers WHERE ID IN ( SELECT DISTINCT SpeakerId FROM Sessions WHERE Accepted = 1 ) ORDER BY DisplayName";
				using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) {
					while( DataReader.Read() ) {
						Speaker FoundSpeaker = new Speaker();
						FoundSpeaker.Id = DataReader.GetInt16( 0 );
						FoundSpeaker.EmailAddress = DataReader.GetString( 1 );
						FoundSpeaker.DisplayName = DataReader.GetString( 2 );
						FoundSpeaker.PasswordHash = DataReader.GetSqlBinary( 3 ).Value;
						FoundSpeaker.Bio = DataReader.IsDBNull( 4 ) ? null : DataReader.GetString( 4 );
						FoundSpeaker.Twitter = DataReader.IsDBNull( 5 ) ? null : DataReader.GetString( 5 );
						FoundSpeaker.Website = DataReader.IsDBNull( 6 ) ? null : DataReader.GetString( 6 );
						FoundSpeaker.DisplayEmail = DataReader.IsDBNull( 7 ) ? false : DataReader.GetBoolean( 7 );
						FoundSpeaker.DisplayTwitter = DataReader.IsDBNull( 8 ) ? false : DataReader.GetBoolean( 8 );
						FoundSpeaker.DisplayWebsite = DataReader.IsDBNull( 9 ) ? false : DataReader.GetBoolean( 9 );
						FoundSpeaker.SessionToken = DataReader.IsDBNull( 10 ) ? Guid.Empty : DataReader.GetGuid( 10 );
						FoundSpeaker.SessionExpires = DataReader.IsDBNull( 11 ) ? DateTime.MinValue : DataReader.GetDateTime( 11 );

						Speakers.Add( FoundSpeaker );
					}
				}
			}

			return Speakers;
		}

 public static Task<SqlDataReader> ExecuteReaderAsyncWithRetry(
                                                               SqlCommand command,
                                                               CommandBehavior behavior,
                                                               IRetryPolicy retryPolicy)
 {
     return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteReaderAsync(behavior));
 }

        /// <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 static async Task ReadAsync(int productId)
        {
            var connection = new SqlConnection(GetConnectionString());


            string sql = "SELECT Prod.ProductID, Prod.Name, Prod.StandardCost, Prod.ListPrice, CostHistory.StartDate, CostHistory.EndDate, CostHistory.StandardCost " +
                "FROM Production.ProductCostHistory AS CostHistory  " +
                "INNER JOIN Production.Product AS Prod ON CostHistory.ProductId = Prod.ProductId " +
                  "WHERE Prod.ProductId = @ProductId";
            var command = new SqlCommand(sql, connection);
            var productIdParameter = new SqlParameter("ProductId", SqlDbType.Int);
            productIdParameter.Value = productId;
            command.Parameters.Add(productIdParameter);

            await connection.OpenAsync();

            using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
            {
                while (await reader.ReadAsync())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    DateTime from = reader.GetDateTime(4);
                    DateTime? to = reader.IsDBNull(5) ? (DateTime?)null : reader.GetDateTime(5);
                    decimal standardPrice = reader.GetDecimal(6);
                    WriteLine($"{id} {name} from: {from:d} to: {to:d}; price: {standardPrice}");
                }
            }
        }

        public static async Task<ServiceTable> GetServiceTableAsync(string ConnString, int ServiceTableID, TaskLoggingHelper Log)
        {
            ServiceTable result = new ServiceTable();

            SqlConnection conn = new SqlConnection(ConnString);
            conn.Open();

            SqlCommand cmd = new SqlCommand("select ServiceTableID, DescServiceTable, Value, CreationDate, StringField1, StringField2 " +
                    "from ServiceTable where ServiceTableID = @ServiceTableID", conn);

            using (conn)
            {
                SqlParameter p1 = cmd.Parameters.Add("@ServiceTableID", SqlDbType.Int);
                p1.Value = ServiceTableID;

                SqlDataReader rd = await cmd.ExecuteReaderAsync();
                rd.Read();
                using (rd)
                {
                    result.ServiceTableID = rd.GetInt32(0);
                    result.DescServiceTable = rd.GetString(1);
                    result.Value = (float)rd.GetDouble(2);
                    result.CreationDate = rd.GetDateTime(3);
                    result.StringField1 = rd.GetString(4);
                    result.StringField2 = rd.GetString(5);
                }
            }

            if (Log != null)
                Log.LogMessage("Getting ServiceTableID: " + ServiceTableID.ToString());

            return result;
        }

        public async Task<IReadOnlyCollection<Entry>> Query()
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = new SqlCommand(@"
SELECT [Owner], [Type], [Value] FROM [Data] WHERE [Owner] <> @Owner
", connection, transaction))
                    {
                        command.Parameters.AddWithValue("Owner", owner).DbType = DbType.AnsiString;

                        var results = new List<Entry>();
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (reader.Read())
                            {
                                results.Add(new Entry((string)reader[0], (string)reader[1], (string)reader[2]));
                            }
                        }
                        return results;
                    }
                }
            }
        }