/// <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();
            }
        }
        /// <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>
        /// 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;
                }
            }
        }
Example #10
0
		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));
 }
Example #12
0
        /// <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 Username=@username";
            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;
        }
Example #13
0
		public async static Task<Speaker> Select( int Id, SqlConnection Connection, SqlTransaction Transaction = null ) {
			Speaker FoundSpeaker = null;

			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 = @Id;";
				Command.Parameters.Add( "Id", System.Data.SqlDbType.SmallInt ).Value = Id;
				using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) {
					if( DataReader.Read() ) {
						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 );
					}
				}
			}

			return FoundSpeaker;
		}
        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 Task<SqlDataReader> ExecuteReaderAsyncWithRetry(
                                                               SqlCommand command,
                                                               CancellationToken cancellationToken,
                                                               IRetryPolicy retryPolicy)
 {
     return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteReaderAsync(cancellationToken));
 }
Example #16
0
        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;
                    }
                }
            }
        }
	public async Task OpenConnectionAndExecuteReaderAsync()
	{
		m_connection = m_connectionFactory.CreateConnection();
		await m_connection.OpenAsync();
		m_command = m_connection.CreateCommand();
		m_setupCommandAction( m_command );
		m_reader = await m_command.ExecuteReaderAsync();
	}
    public async Task<object> Invoke(IDictionary<string, object> parameters)
    {
        var commandText = (string)parameters["source"];
        var connectionString = (string)parameters["connectionString"];
        var dataSets = new List<List<object>>();
        var rows = new List<object>();

        using (var connection = new SqlConnection(connectionString))
        {
            using (var command = new SqlCommand(commandText, connection))
            {
                await connection.OpenAsync();
                using (var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
                {
                    var record = (IDataRecord)reader;
                    do
                    {
                        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++)
                            {
                                var type = record.GetFieldType(i);
                                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);
                        }
                        dataSets.Add(rows);
                        rows = new List<object>();
                    }
                    while(await reader.NextResultAsync());
                }
            }
        }

        return dataSets;
    }
Example #20
0
    async Task<object> ExecuteQuery(string connectionString, string commandString, IDictionary<string, object> parameters)
    {
        List<object> rows = new List<object>();

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(commandString, connection))
            {
                this.AddParamaters(command, parameters);
                await connection.OpenAsync();
                using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
                {
                    object[] fieldNames = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        fieldNames[i] = reader.GetName(i);
                    }
                    rows.Add(fieldNames);

                    IDataRecord record = (IDataRecord)reader;
                    while (await reader.ReadAsync())
                    {
                        object[] 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>";
                            }
                        }

                        rows.Add(resultRecord);
                    }
                }
            }
        }

        return rows;
    }
Example #21
0
       async private void ExecuteButton_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                var builder = new SqlConnectionStringBuilder();
                builder.DataSource = serverIdTxt.Text;
               
                builder.IntegratedSecurity = SqlradioButton.IsChecked != null ? SqlradioButton.IsChecked.Value : false;
                if(!builder.IntegratedSecurity)
                {
                    builder.UserID = LoginTxt.Text;
                    builder.Password = PassTxt.Text;
                }
                //
                builder.InitialCatalog = Databasetxt.Text;
                status.Text = "Connecting to Database";
                SqlConnection conn = new SqlConnection(builder.ConnectionString);
                await  conn.OpenAsync();
                status.Text = "Executing SQLCommand";    
                
                var query = CommandTxt.Text;
                SqlCommand comm = new SqlCommand(query, conn);
                var reader =await comm.ExecuteReaderAsync();

                var result = new DataTable();  

                //
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    result.Columns.Add(reader.GetName(i));
                }

                while(await reader.ReadAsync())
                {
                    var row = result.NewRow();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[i] = reader[i];
                    }
                    result.Rows.Add(row);
                }
                conn.Close();
                conn.Dispose();

                dataGrid.ItemsSource = result.DefaultView;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Example #22
0
        public async Task<User> AuthenticateAsync(string username, string password)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();

                var command = new SqlCommand(
                    " SELECT TOP 1 U.[Username], U.[EmailAddress], U.[ApiKey], C.[Type] AS [CredentialType], C.[Value] AS [CredentialValue]" +
                    " FROM [dbo].[Users] AS U" +
                    " INNER JOIN [dbo].[Credentials] AS C ON C.[UserKey] = U.[Key]" +
                    " WHERE (C.[Type] IN ('password.pbkdf2', 'password.sha512', 'password.sha1'))" +
                    "   AND (Username = @UsernameOrEmail OR EmailAddress = @UsernameOrEmail)",
                        connection);

                command.Parameters.AddWithValue("@UsernameOrEmail", username);

                var reader = await command.ExecuteReaderAsync();
                if (await reader.ReadAsync())
                {
                    // Validate credential
                    var validCredential = false;
                    var credentialType = reader["CredentialType"].ToString();
                    var credentialValue = reader["CredentialValue"].ToString();
                    if (credentialType == "password.pbkdf2")
                    {
                        validCredential = CryptographyService.ValidateSaltedHash(credentialValue, password, Constants.PBKDF2HashAlgorithmId);
                    }
                    else if (credentialType == "password.sha512")
                    {
                        validCredential = CryptographyService.ValidateSaltedHash(credentialValue, password, Constants.Sha512HashAlgorithmId);
                    }
                    else if (credentialType == "password.sha1")
                    {
                        validCredential = CryptographyService.ValidateSaltedHash(credentialValue, password, Constants.Sha1HashAlgorithmId);
                    }

                    // Valid? If so, return a user instance
                    if (validCredential)
                    {
                        return new User
                        {
                            Username = reader["Username"].ToString(),
                            EmailAddress = reader["EmailAddress"].ToString(),
                            ApiKey = reader["ApiKey"].ToString()
                        };
                    }
                }
            }

            return null;
        }
Example #23
0
        // Application transferring a large Xml Document from SQL Server in .NET 4.5
        private static async Task PrintXmlValues()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (SqlCommand command = new SqlCommand("SELECT [id], [ConstructData] FROM [Factors]", connection))
                {

                    // The reader needs to be executed with the SequentialAccess behavior to enable network streaming
                    // Otherwise ReadAsync will buffer the entire Xml Document into memory which can cause scalability issues or even OutOfMemoryExceptions
                    using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
                    {
                        while (await reader.ReadAsync())
                        {
                            Console.WriteLine("{0}: ", reader.GetGuid(0));

                            if (await reader.IsDBNullAsync(1))
                            {
                                Console.WriteLine("\t(NULL)");
                            }
                            else
                            {
                                using (XmlReader xmlReader = reader.GetXmlReader(1))
                                {
                                    int depth = 1;
                                    // NOTE: The XmlReader returned by GetXmlReader does NOT support async operations
                                    // See the example below (PrintXmlValuesViaNVarChar) for how to get an XmlReader with asynchronous capabilities
                                    while (xmlReader.Read())
                                    {
                                        switch (xmlReader.NodeType)
                                        {
                                            case XmlNodeType.Element:
                                                Console.WriteLine("{0}<{1}>", new string('\t', depth), xmlReader.Name);
                                                depth++;
                                                break;
                                            case XmlNodeType.Text:
                                                Console.WriteLine("{0}{1}", new string('\t', depth), xmlReader.Value);
                                                break;
                                            case XmlNodeType.EndElement:
                                                depth--;
                                                Console.WriteLine("{0}</{1}>", new string('\t', depth), xmlReader.Name);
                                                break;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
Example #24
0
	private async Task ReadSqlData()
	{
		using (var connection = new SqlConnection(_connectionString))
		{
			connection.Open();
			var command = new SqlCommand("SELECT Id, Forename, Surname FROM Customers WHERE ID = 'B9F83382-2A28-4461-83E2-9CC700DF1AB9'", connection);
			using (var reader = await command.ExecuteReaderAsync())
			{
				while (reader.Read())
				{
				}
			}
		}
	}
Example #25
0
		public async static Task<Session> Select( int Id, SqlConnection Connection, SqlTransaction Transaction = null ) {
			short SpeakerId = -1;
			Session FoundSession = null;

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

				Command.CommandText = "SELECT Sessions.Id, SpeakerId, Title, Abstract, Notes, Accepted, Text AS TimeSlot FROM Sessions LEFT JOIN TimeSlots ON Sessions.TimeSlotId = TimeSlots.Id WHERE Sessions.Id = @Id;";
				Command.Parameters.Add( "Id", System.Data.SqlDbType.SmallInt ).Value = Id;
				using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) {
					if( DataReader.Read() ) {
						FoundSession = new Session();
						FoundSession.Id = DataReader.GetInt16( 0 );
						SpeakerId = DataReader.GetInt16( 1 );
						FoundSession.Title = DataReader.GetString( 2 );
						FoundSession.Abstract = DataReader.GetString( 3 );
						FoundSession.Notes = DataReader.IsDBNull( 4 ) ? null : DataReader.GetString( 4 );
						FoundSession.Accepted = DataReader.IsDBNull( 5 ) ? false : DataReader.GetBoolean( 5 );
						FoundSession.TimeSlot = DataReader.IsDBNull( 6 ) ? null : DataReader.GetString( 6 );
					}
				}
			}

			if( null != FoundSession ) {
				FoundSession.Speaker = await Speaker.Select( SpeakerId, Connection );

				FoundSession.Tags = new List<Tag>();
				using( SqlCommand Command = new SqlCommand() ) {
					Command.Connection = Connection;
					if( null != Transaction )
						Command.Transaction = Transaction;

					Command.CommandText = "SELECT Id, Text FROM Tags WHERE Id IN ( SELECT TagId FROM SessionTags WHERE SessionId = @SessionId );";
					Command.Parameters.Add( "SessionId", System.Data.SqlDbType.SmallInt ).Value = FoundSession.Id;
					using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) {
						while( DataReader.Read() ) {
							FoundSession.Tags.Add( new Tag {
								Id = DataReader.GetInt16( 0 ),
								Text = DataReader.GetString( 1 )
							} );
						}
					}
				}
			}

			return FoundSession;
		}
Example #26
0
        public static async Task SelectMultipleResultSets()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ProgrammingCSharpConnection"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand("SELECT * FROM People; SELECT TOP 1 * FROM People ORDER BY LastName", connection);
                await connection.OpenAsync();

                SqlDataReader dataReader = await command.ExecuteReaderAsync();
                await ReadQueryResults(dataReader);
                await dataReader.NextResultAsync(); // Move to the next result set
                await ReadQueryResults(dataReader);
                dataReader.Close();
            }
        }
        public async Task<User> Authenticate(AuthenticationRequest authRequest)
        {
            
            string procName = "proc_Users_Authenticate";

            Task<User> t = Task.Run<User>(async () =>
            {
                User currentUser = null;
                SqlCommand cmd = null;
                using (SqlConnection con = new SqlConnection(conString))
                {
                    await con.OpenAsync();
                    using (cmd = new SqlCommand(procName, con))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@userName", authRequest.UserName);
                        cmd.Parameters.AddWithValue("@password", authRequest.Password);
                        var rdr = await cmd.ExecuteReaderAsync(System.Data.CommandBehavior.Default);
                        if (rdr.HasRows)
                        {
                            currentUser = new User();

                            while (rdr.Read())
                            {
                                currentUser.UserID = int.Parse(rdr["UserID"].ToString());
                                currentUser.UserName = rdr["UserName"].ToString();
                                currentUser.SessionGuid = rdr["SessionGuid"].ToString();
                                Board x = new Board()
                                {
                                    BoardID = int.Parse(rdr["BoardID"].ToString()),
                                    BoardName = rdr["BoardName"].ToString(),
                                    UserID = int.Parse(rdr["UserID"].ToString())
                                };
                                currentUser.BoardList.Add(x);
                            }
                            
                        }
                    }
                   
                }

                return currentUser;

            });

            return await t;

        }
Example #28
0
        public static async Task SelectDataFromTable()
        {
            // string connectionString = ConfigurationManager.ConnectionStrings[ConfigurationManager.ConnectionStrings["CONNECTIONSTRING"].ConnectionString].ConnectionString;
            string connectionString = ConfigurationManager.ConnectionStrings[".CONNECTIONSTRING"].ConnectionString;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //Executing a SQL query with multiple result sets
                SqlCommand command = new SqlCommand("SELECT * FROM KEPLER_CUSTOMER; SELECT * FROM WF_DATA", connection);
                await connection.OpenAsync();
                SqlDataReader dataReader = await command.ExecuteReaderAsync();
                await ReadQueryResults(dataReader);
                await dataReader.NextResultAsync(); // Move to the next result set
                await ReadQueryResults(dataReader);
                dataReader.Close();
            }
        }
        public async Task<IEnumerable<Car>> GetCarsViaSPAsync() {

            using (var conn = new SqlConnection(_connectionString)) {
                using (var cmd = new SqlCommand()) {

                    cmd.Connection = conn;
                    cmd.CommandText = _spName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    conn.Open();

                    using (var reader = await cmd.ExecuteReaderAsync()) {
                        
                        return reader.Select(r => carBuilder(r)).ToList();
                    }
                }
            }
        }
		protected async override Task<HttpResponseMessage> SendAsync( HttpRequestMessage Request, CancellationToken CancelToken ) {
			Guid SessionToken = GetSessionToken( Request );
			if( !SessionToken.Equals( Guid.Empty ) ) {
				using( SqlConnection Connection = new SqlConnection( RoleEnvironment.GetConfigurationSettingValue( "DatabaseConnectionString" ) ) ) {
					Connection.Open();

					using( SqlCommand Command = new SqlCommand() ) {
						Command.Connection = Connection;
						Command.CommandText = "SELECT EmailAddress FROM Speakers WHERE SessionToken = @SessionToken AND SessionExpires > @CurrentTime";
						Command.Parameters.Add( "SessionToken", System.Data.SqlDbType.UniqueIdentifier ).Value = SessionToken;
						Command.Parameters.Add( "CurrentTime", System.Data.SqlDbType.DateTime ).Value = DateTime.UtcNow;

						using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) {
							if( DataReader.Read() ) {
								GenericIdentity UserIdentity = new GenericIdentity( DataReader.GetString( 0 ) );
								GenericPrincipal UserPrincipal = new GenericPrincipal( UserIdentity, null );
								Thread.CurrentPrincipal = UserPrincipal;
								Request.GetRequestContext().Principal = UserPrincipal;
							} else {
								// return 401
								HttpResponseMessage Response401 = new HttpResponseMessage( System.Net.HttpStatusCode.Unauthorized );
								Response401.Headers.Add( "Access-Control-Allow-Origin", Request.Headers.GetValues( "Origin" ).First() );
								Response401.Headers.Add( "Access-Control-Allow-Credentials", "true" );
								return Response401;
							}
						}
					}

					HttpResponseMessage Response = await base.SendAsync( Request, CancelToken );

					using( SqlCommand Command = new SqlCommand() ) {
						Command.Connection = Connection;
						Command.CommandText = "UPDATE Speakers SET SessionExpires = @SessionExpires WHERE SessionToken = @SessionToken";
						Command.Parameters.Add( "SessionToken", System.Data.SqlDbType.UniqueIdentifier ).Value = SessionToken;
						Command.Parameters.Add( "SessionExpires", System.Data.SqlDbType.DateTime ).Value = DateTime.UtcNow.AddMinutes( 20 );
						await Command.ExecuteNonQueryAsync();
					}

					return Response;
				}
			}
				
			return await base.SendAsync( Request, CancelToken );
		}