/// <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);
                    }
                }
            }
        }
Example #3
0
        protected async Task <Adapters.Oal.dbo_consignment_initial> SearchConsignmentInitialAsync(string consignmentNo)
        {
            var adapter = new Adapters.Oal.dbo_consignment_initialAdapter();
            var query   = $"SELECT [dt_created_date_field] FROM [dbo].[consignment_initial] WHERE [number] = '{consignmentNo}'";

            Adapters.Oal.dbo_consignment_initial consignmentInitial = null;

            using (var conn = new System.Data.SqlClient.SqlConnection(adapter.ConnectionString))
                using (var cmd = new System.Data.SqlClient.SqlCommand(query, conn))
                {
                    await conn.OpenAsync();

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            consignmentInitial = new Adapters.Oal.dbo_consignment_initial
                            {
                                dt_created_date_field = reader["dt_created_date_field"].ReadNullable <DateTime>()
                            };
                        }
                    }
                }

            return(consignmentInitial);
        }
Example #4
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;
        }
        private async Task <bool> DoLookupAsync(Bespoke.PosEntt.AcceptanceDatas.Domain.AcceptanceData item, Bespoke.PosEntt.EnttAcceptances.Domain.EnttAcceptance destination)
        {
            var          connectionString = ConfigurationManager.ConnectionStrings["brom"].ConnectionString;
            const string queryString      = @"SELECT [BranchCode],[BranchName] FROM [dbo].[BROMBranchProfile] WHERE BranchCostCenter = @branchCode";

            using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                using (var command = new System.Data.SqlClient.SqlCommand(queryString, connection))
                {
                    command.Parameters.Add("@branchCode", SqlDbType.VarChar, 255).Value = item.BranchCode.ToDbNull();
                    await connection.OpenAsync();

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            destination.LocationId   = reader["BranchCode"].ReadNullableString();
                            destination.LocationName = reader["BranchName"].ReadNullableString();
                        }
                    }
                }
            }

            return(true);
        }
        /// <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;
		}
Example #11
0
        protected async Task <Adapters.Oal.dbo_consignment_update> SearchConsignmentUpdateAsync(string consignmentNo)
        {
            var adapter = new Adapters.Oal.dbo_consignment_initialAdapter();
            var query   = $"SELECT [id],[weight_double] FROM [dbo].[consignment_update] WHERE [number] = '{consignmentNo}'";

            Adapters.Oal.dbo_consignment_update consignmentUpdate = null;

            using (var conn = new System.Data.SqlClient.SqlConnection(adapter.ConnectionString))
                using (var cmd = new System.Data.SqlClient.SqlCommand(query, conn))
                {
                    await conn.OpenAsync();

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            consignmentUpdate = new Adapters.Oal.dbo_consignment_update
                            {
                                id            = reader["id"].ReadNullableString(),
                                weight_double = reader["weight_double"].ReadNullable <double>()
                            };
                        }
                    }
                }

            return(consignmentUpdate);
        }
        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}");
                }
            }
        }
Example #13
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;
		}
Example #14
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;
        }
        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 static Task<SqlDataReader> ExecuteReaderAsyncWithRetry(
                                                               SqlCommand command,
                                                               CancellationToken cancellationToken,
                                                               IRetryPolicy retryPolicy)
 {
     return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteReaderAsync(cancellationToken));
 }
 public static Task<SqlDataReader> ExecuteReaderAsyncWithRetry(
                                                               SqlCommand command,
                                                               CommandBehavior behavior,
                                                               IRetryPolicy retryPolicy)
 {
     return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteReaderAsync(behavior));
 }
        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;
                    }
                }
            }
        }
Example #19
0
        private async Task <bool> DoLookupAsync(Bespoke.PosEntt.IposPems.Domain.IposPem item, Bespoke.PosEntt.Adapters.Oal.UspConsigmentInitialRtsRequest destination)
        {
            var          connectionString = ConfigurationManager.ConnectionStrings["oal"].ConnectionString;
            const string queryString      = @"SELECT [id] FROM [dbo].[consignment_initial]  WHERE [number] = @babyConsignmentNo";

            using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                using (var command = new System.Data.SqlClient.SqlCommand(queryString, connection))
                {
                    command.Parameters.Add("@babyConsignmentNo", SqlDbType.VarChar, 255).Value = item.BabyConnoteNo.ToDbNull();
                    await connection.OpenAsync();

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            destination.baby_item = reader["id"].ReadNullableString();
                            destination.parent    = reader["id"].ReadNullableString();
                        }
                    }
                }
            }

            return(true);
        }
Example #20
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;
		}
        /// <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();
            }
        }
		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();
		}
Example #23
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;
    }
    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 #25
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 #26
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 #27
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 #28
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 #29
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 #30
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 #32
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 );
		}
        public dynamic CommandExecute(CommandExecuteType execType)
        {
            try
            {
                dynamic result = 0;
                switch (execType)
                {
                case CommandExecuteType.NonQuery:
                    result = command.ExecuteNonQuery();
                    break;

                case CommandExecuteType.NonQueryAsync:
                    result = command.ExecuteNonQueryAsync();
                    break;

                case CommandExecuteType.Reader:
                    result = command.ExecuteReader();
                    break;

                case CommandExecuteType.ReaderAsync:
                    result = command.ExecuteReaderAsync();
                    break;

                case CommandExecuteType.Scalar:
                    result = command.ExecuteScalar();
                    break;

                case CommandExecuteType.ScalarAsync:
                    result = command.ExecuteScalarAsync();
                    break;
                }
                ClearParametersCommand();
                return(result);
            }
            catch (Exception ex)
            {
                throw new ConnectionManagerException(string.Format("Unable to execute command caused by {0}", ex.Message), ex.InnerException);
            }
        }
Example #36
0
        protected async Task <System.Data.DataSet> ExecuteAsync(sql.SqlCommand command)   //, IContextInfo contextInfo
        {
            var dataSet = new System.Data.DataSet();

            using (var connection = new sql.SqlConnection(_connectionString))
            {
                //connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) =>
                //{
                //    if (e.CurrentState == System.Data.ConnectionState.Open)
                //        setContextInfo(connection, contextInfo);
                //});

                command.Connection = connection;
                await connection.TryOpenAsync();

                using (var reader = await command.ExecuteReaderAsync())
                {
                    do
                    {
                        if (reader.HasRows && reader.FieldCount > 0)
                        {
                            using (var table = dataSet.Tables.Add())
                            {
                                table.Load(reader);
                            }
                        }
                        else
                        {
                            reader.Close();
                        }
                    } while (!reader.IsClosed);
                }

                //clearContextInfo(connection, contextInfo);
                connection.TryClose();
            }

            return(dataSet);
        }
        public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request)
        {
            var sql = new StringBuilder("select ");

            if (request.PageSize == 0)
            {
                request.PageSize = 50;
            }

            sql.Append($"[{_stream.TimeStampFieldName}]");
            foreach (var fld in _stream.Fields)
            {
                sql.Append($", [{fld.FieldName}]");
            }

            sql.AppendLine();
            sql.AppendLine($"  from  [{_stream.DBTableName}]");
            sql.AppendLine($"  where [{_stream.DeviceIdFieldName}] = @deviceId");

            if (!String.IsNullOrEmpty(request.NextRowKey))
            {
                sql.AppendLine($"  and {_stream.TimeStampFieldName} < @lastDateStamp");
            }

            if (!String.IsNullOrEmpty(request.StartDate))
            {
                sql.AppendLine($"  and {_stream.TimeStampFieldName} >= @startDateStamp");
            }
            if (!String.IsNullOrEmpty(request.EndDate))
            {
                sql.AppendLine($"  and {_stream.TimeStampFieldName} <= @endDateStamp");
            }

            sql.AppendLine($"  order by [{_stream.TimeStampFieldName}] desc");
            sql.AppendLine("   OFFSET @PageSize * @PageIndex ROWS");
            sql.AppendLine("   FETCH NEXT @PageSize ROWS ONLY ");

            Console.WriteLine(sql.ToString());

            var responseItems = new List <DataStreamResult>();

            using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString))
                using (var cmd = new System.Data.SqlClient.SqlCommand(sql.ToString(), cn))
                {
                    cmd.Parameters.AddWithValue("@deviceId", deviceId);
                    cmd.Parameters.AddWithValue("@PageSize", request.PageSize);
                    cmd.Parameters.AddWithValue("@PageIndex", request.PageIndex);

                    if (!String.IsNullOrEmpty(request.NextRowKey))
                    {
                        cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime());
                    }
                    if (!String.IsNullOrEmpty(request.StartDate))
                    {
                        cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime());
                    }
                    if (!String.IsNullOrEmpty(request.EndDate))
                    {
                        cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime());
                    }

                    cmd.CommandType = System.Data.CommandType.Text;

                    await cmd.Connection.OpenAsync();

                    using (var rdr = await cmd.ExecuteReaderAsync())
                    {
                        while (rdr.Read())
                        {
                            var resultItem = new DataStreamResult();
                            resultItem.Timestamp = Convert.ToDateTime(rdr[_stream.TimeStampFieldName]).ToJSONString();

                            foreach (var fld in _stream.Fields)
                            {
                                resultItem.Fields.Add(fld.FieldName, rdr[fld.FieldName]);
                            }

                            responseItems.Add(resultItem);
                        }
                    }
                }

            var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>();

            response.Model          = responseItems;
            response.PageSize       = responseItems.Count;
            response.PageIndex      = request.PageIndex;
            response.HasMoreRecords = responseItems.Count == request.PageSize;
            if (response.HasMoreRecords)
            {
                response.NextRowKey = responseItems.Last().Timestamp;
            }

            return(response);
        }
Example #38
0
        public async Task <List <DynoCard> > GetPreviousCards(DynoCardAnomalyResult anomalyCard)
        {
            DateTime start      = anomalyCard.Timestamp.Subtract(TimeSpan.FromMinutes(30));
            DateTime end        = anomalyCard.Timestamp;
            int      startEpoch = (int)(start.ToUniversalTime().Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
            int      endEpoch   = (int)(end.ToUniversalTime().Subtract(new DateTime(1970, 1, 1))).TotalSeconds;

            System.Console.WriteLine($"Start: {startEpoch}");
            System.Console.WriteLine($"End: {endEpoch}");

            var sql = new StringBuilder()
                      .Append("WITH cteEndCardID (cardID) ")
                      .Append("AS ( ")
                      .Append("SELECT TOP 1 h.DC_ID ")
                      .Append("FROM ACTIVE.CARD_HEADER h ")
                      .Append($"WHERE h.CH_EPOC_DATE >= {endEpoch} ")
                      .Append("ORDER BY h.CH_EPOC_DATE ), ")
                      .Append("cteStartCardID(cardID) ")
                      .Append("AS ( ")
                      .Append("SELECT TOP 1 h.DC_ID ")
                      .Append("FROM ACTIVE.CARD_HEADER h ")
                      .Append($"WHERE h.CH_EPOC_DATE <= {startEpoch} ")
                      .Append("OR h.CH_EPOC_DATE = (SELECT MIN(CH_EPOC_DATE) FROM ACTIVE.CARD_HEADER) ")
                      .Append("ORDER BY h.DC_ID DESC) ")
                      .Append("SELECT h.CH_CARD_TYPE, ")
                      .Append("dc.DC_ID, ")
                      .Append("h.CH_ID, ")
                      .Append("h.CH_SCALED_MAX_LOAD, ")
                      .Append("h.CH_SCALED_MIN_LOAD, ")
                      .Append("h.CH_STROKE_LENGTH, ")
                      .Append("h.CH_STROKE_PERIOD, ")
                      .Append("h.CH_GROSS_STROKE, ")
                      .Append("h.CH_NET_STROKE, ")
                      .Append("h.CH_PUMP_FILLAGE, ")
                      .Append("h.CH_FLUID_LOAD, ")
                      .Append("d.CD_ID, ")
                      .Append("d.CD_POSITION, ")
                      .Append("d.CD_LOAD, ")
                      .Append("h.CH_EPOC_DATE, ")
                      .Append("dc.PU_ID, ")
                      .Append("h.CH_NUMBER_OF_POINTS ")
                      .Append("FROM [ACTIVE].[DYNO_CARD] dc ")
                      .Append("JOIN [ACTIVE].[CARD_HEADER] h ON dc.DC_ID = h.DC_ID ")
                      .Append("JOIN [ACTIVE].[CARD_DETAIL] d ON h.CH_ID = d.CH_ID ")
                      .Append("JOIN cteStartCardID sc ON h.DC_ID >= sc.cardID ")
                      .Append("JOIN cteEndCardID ec ON h.DC_ID <= ec.cardID ")
                      .Append("ORDER BY h.CH_ID DESC");

            Dictionary <int, DynoCard> cardList = new Dictionary <int, DynoCard>();

            // //Store the data in SQL db
            using (Sql.SqlConnection conn = new Sql.SqlConnection(ConnectionString))
            {
                conn.Open();

                using (Sql.SqlCommand cardHistorySelect = new Sql.SqlCommand(sql.ToString(), conn))
                {
                    var results = await cardHistorySelect.ExecuteReaderAsync();

                    if (results.HasRows)
                    {
                        PumpCard    pumpCard       = null;
                        SurfaceCard surfaceCard    = null;
                        int         previousCardID = 0;
                        DateTime    epoch          = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

                        while (await results.ReadAsync())
                        {
                            string cardTypeValue = results.GetString(0);
                            int    dynoCardID    = results.GetInt32(1);

                            CardType cardType = "P".Equals(cardTypeValue) ? CardType.Pump : CardType.Surface;

                            if (previousCardID != dynoCardID)
                            {
                                previousCardID = dynoCardID;
                                pumpCard       = null;
                                surfaceCard    = null;
                            }

                            if (cardType == CardType.Surface)
                            {
                                if (surfaceCard == null)
                                {
                                    surfaceCard = new SurfaceCard()
                                    {
                                        Id              = results.GetInt32(2),
                                        Timestamp       = epoch.AddSeconds(results.GetInt32(14)),
                                        ScaledMaxLoad   = (int)results.GetFloat(3),
                                        ScaledMinLoad   = (int)results.GetFloat(4),
                                        StrokeLength    = (int)results.GetFloat(5),
                                        StrokePeriod    = (int)results.GetFloat(6),
                                        NumberOfPoints  = results.GetInt32(16),
                                        CardType        = cardType,
                                        CardCoordinates = new List <CardCoordinate>()
                                    };

                                    surfaceCard.CardCoordinates.Add(new CardCoordinate()
                                    {
                                        Order    = results.GetInt32(11),
                                        Position = (int)results.GetFloat(12),
                                        Load     = (int)results.GetFloat(13)
                                    });

                                    DynoCard dynoCard = null;
                                    if (cardList.ContainsKey(dynoCardID))
                                    {
                                        dynoCard = cardList[dynoCardID];
                                    }
                                    else
                                    {
                                        dynoCard           = new DynoCard();
                                        dynoCard.Id        = dynoCardID;
                                        dynoCard.Timestamp = surfaceCard.Timestamp;
                                    }

                                    dynoCard.SurfaceCard = surfaceCard;
                                    cardList[dynoCardID] = dynoCard;
                                }
                                else
                                {
                                    cardList[dynoCardID].SurfaceCard.CardCoordinates.Add(new CardCoordinate()
                                    {
                                        Position = (int)results.GetFloat(12),
                                        Load     = (int)results.GetFloat(13)
                                    });
                                }
                            }
                            else if (cardType == CardType.Pump)
                            {
                                if (pumpCard == null)
                                {
                                    pumpCard = new PumpCard()
                                    {
                                        Id              = results.GetInt32(2),
                                        Timestamp       = epoch.AddSeconds(results.GetInt32(14)),
                                        ScaledMaxLoad   = (int)results.GetFloat(3),
                                        ScaledMinLoad   = (int)results.GetFloat(4),
                                        GrossStroke     = (int)results.GetFloat(7),
                                        NetStroke       = (int)results.GetFloat(8),
                                        PumpFillage     = (int)results.GetFloat(9),
                                        FluidLoad       = (int)results.GetFloat(10),
                                        NumberOfPoints  = results.GetInt32(16),
                                        CardType        = cardType,
                                        CardCoordinates = new List <CardCoordinate>()
                                    };

                                    pumpCard.CardCoordinates.Add(new CardCoordinate()
                                    {
                                        Order    = results.GetInt32(11),
                                        Position = (int)results.GetFloat(12),
                                        Load     = (int)results.GetFloat(13)
                                    });

                                    DynoCard dynoCard = null;
                                    if (cardList.ContainsKey(dynoCardID))
                                    {
                                        dynoCard = cardList[dynoCardID];
                                    }
                                    else
                                    {
                                        dynoCard           = new DynoCard();
                                        dynoCard.Id        = dynoCardID;
                                        dynoCard.Timestamp = pumpCard.Timestamp;
                                    }

                                    dynoCard.PumpCard    = pumpCard;
                                    cardList[dynoCardID] = dynoCard;
                                }
                                else
                                {
                                    cardList[dynoCardID].PumpCard.CardCoordinates.Add(new CardCoordinate()
                                    {
                                        Position = (int)results.GetFloat(12),
                                        Load     = (int)results.GetFloat(13)
                                    });
                                }
                            }
                        }
                    }
                }
            }

            var cards = cardList?.Values?.OrderBy(c => c.Timestamp).ToList();

            if (cards != null && cards.Count > 0)
            {
                cards.Last().TriggeredEvents = true;
            }

            return(await Task.FromResult(cards));
        }
Example #39
0
        public async Task <IActionResult> ExecuteQuery([FromBody] Query query)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest(ModelState));
            }

            string connectionString = await GetConnectionString(query);

            var queryResult = new QueryResult();

            using (SqlClient.SqlConnection sqlConnection = new SqlClient.SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();

                sqlConnection.InfoMessage += (sender, args) =>
                {
                    queryResult.Messages.Add(args.Message);
                };

                for (int batchIndex = 0; batchIndex < query.Sql.Count; batchIndex++)
                {
                    string sql = query.Sql[batchIndex];

                    Log.LogInformation("Executing T-SQL batch {BatchNumber} of {BatchCount}...",
                                       batchIndex + 1,
                                       query.Sql.Count
                                       );

                    using (var sqlCommand = new SqlClient.SqlCommand(sql, sqlConnection))
                    {
                        sqlCommand.CommandType = CommandType.Text;

                        foreach (Parameter parameter in query.Parameters)
                        {
                            sqlCommand.Parameters.Add(
                                parameter.ToSqlParameter()
                                );
                        }

                        try
                        {
                            using (SqlClient.SqlDataReader reader = await sqlCommand.ExecuteReaderAsync())
                            {
                                await ReadResults(reader, queryResult);

                                while (await reader.NextResultAsync())
                                {
                                    await ReadResults(reader, queryResult);
                                }
                            }

                            queryResult.ResultCode = 0;
                        }
                        catch (SqlClient.SqlException sqlException)
                        {
                            Log.LogError(sqlException, "Error while executing T-SQL: {ErrorMessage}", sqlException.Message);

                            queryResult.ResultCode = -1;
                            queryResult.Errors.AddRange(
                                sqlException.Errors.Cast <SqlClient.SqlError>().Select(
                                    error => new SqlError
                            {
                                Kind       = SqlErrorKind.TSql,
                                Message    = error.Message,
                                Class      = error.Class,
                                Number     = error.Number,
                                State      = error.State,
                                Procedure  = error.Procedure,
                                Source     = error.Source,
                                LineNumber = error.LineNumber
                            }
                                    )
                                );

                            if (query.StopOnError)
                            {
                                Log.LogInformation("Terminating query processing because the request was configured to stop on the first error encountered.");

                                break;
                            }
                        }
                        catch (Exception unexpectedException)
                        {
                            Log.LogError(unexpectedException, "Unexpected error while executing T-SQL: {ErrorMessage}", unexpectedException.Message);

                            queryResult.ResultCode = -1;
                            queryResult.Errors.Add(new SqlError
                            {
                                Kind    = SqlErrorKind.Infrastructure,
                                Message = $"Unexpected error while executing T-SQL: {unexpectedException.Message}"
                            });

                            if (query.StopOnError)
                            {
                                Log.LogInformation("Terminating query processing because the request was configured to stop on the first error encountered.");

                                break;
                            }
                        }
                    }

                    Log.LogInformation("Executed T-SQL batch {BatchNumber} of {BatchCount}.",
                                       batchIndex + 1,
                                       query.Sql.Count
                                       );
                }
            }

            return(Ok(queryResult));
        }
        public async Task <ValidationResult> ValidationConnection(DataStream stream)
        {
            var result = new ValidationResult();

            /* be careful when updating the SQL below, the rdr uses field indexes,
             * if this wasn't so small and self contained, I probably wouldn't be so lazy,
             * buf for one field...well...moving on.*/
            var sql = $@"
select
	b.name as ColumnName,
	type_name(b.xusertype) ColumnType,
	b.IsNullable,
	columnproperty(a.id, b.name, 'isIdentity') IsIdentity,
	sm.text AS DefaultValue
from sysobjects a 
   inner join syscolumns b on a.id = b.id
   LEFT JOIN sys.syscomments sm ON sm.id = b.cdefault
    WHERE a.xtype = 'U' and a.name = @tableName";

            var fields = new List <SQLFieldMetaData>();

            using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString))
                using (var cmd = new System.Data.SqlClient.SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@tableName", stream.DBTableName);
                    try
                    {
                        await cn.OpenAsync();

                        using (var rdr = await cmd.ExecuteReaderAsync())
                        {
                            while (await rdr.ReadAsync())
                            {
                                fields.Add(new SQLFieldMetaData()
                                {
                                    ColumnName   = rdr["ColumnName"].ToString(),
                                    IsRequired   = !Convert.ToBoolean(rdr["IsNullable"]),
                                    DataType     = rdr["ColumnType"].ToString(),
                                    IsIdentity   = Convert.ToBoolean(rdr["IsIdentity"]),
                                    DefaultValue = Convert.ToString(rdr["DefaultValue"])
                                });
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        result.AddUserError($"Could not access SQL Server: {ex.Message}.");
                        return(result);
                    }
                }

            if (fields.Count == 0)
            {
                result.AddUserError($"Table [{stream.DBTableName}] name not found on SQL Server database [{stream.DBName}] on server [{stream.DBURL}.");
            }
            else
            {
                result.Concat(stream.ValidateSQLSeverMetaData(fields));
            }

            return(result);
        }
        private async Task <bool> DoLookupAsync(Bespoke.PosEntt.Vasns.Domain.Vasn item, Bespoke.PosEntt.Adapters.Oal.dbo_ips_import destination)
        {
            Func <string, string> SetContent = (content) =>
            {
                if (string.IsNullOrWhiteSpace(content))
                {
                    return(null);
                }
                return(content.Equals("01") ? "M" : "D");
            };

            Func <string, string> SetOriginCountryCode = (origin) =>
            {
                if (!string.IsNullOrWhiteSpace(origin))
                {
                    return(origin);
                }
                else
                {
                    var pattern = @"\w{2}\d{9}(?<country>\w{2})";
                    var match   = System.Text.RegularExpressions.Regex.Match(item.ConsignmentNo, pattern);
                    return(match.Success ? match.Groups["country"].Value : "-");
                }
            };

            Func <string, string> SetDestinationCountryCode = (dest) =>
            {
                return(!string.IsNullOrWhiteSpace(dest) ? dest : null);
            };

            var          config           = ConfigurationManager.ConnectionStrings["oal"].ConnectionString;
            var          connectionString = @config;
            const string queryString      = @"SELECT [item_category], [shipper_address_country], [consignee_address_country], [weight_double] FROM [dbo].[consignment_initial]  WHERE [number] = @consignmentNo";

            using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                using (var command = new System.Data.SqlClient.SqlCommand(queryString, connection))
                {
                    command.Parameters.Add("@consignmentNo", SqlDbType.VarChar, 255).Value = item.ConsignmentNo.ToDbNull();
                    await connection.OpenAsync();

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            destination.content            = SetContent(reader["item_category"].ReadNullableString());
                            destination.orig_country_cd    = SetOriginCountryCode(reader["shipper_address_country"].ReadNullableString());
                            destination.dest_country_cd    = SetDestinationCountryCode(reader["consignee_address_country"].ReadNullableString());
                            destination.item_weight_double = reader["weight_double"].ReadNullable <double>();
                        }
                    }
                }
            }

            if (string.IsNullOrWhiteSpace(destination.orig_country_cd))
            {
                var pattern = @"\w{2}\d{9}(?<country>\w{2})";
                var match   = System.Text.RegularExpressions.Regex.Match(item.ConsignmentNo, pattern);

                if (match.Success)
                {
                    destination.orig_country_cd = match.Groups["country"].Value;
                }
            }

            if (null == destination.item_weight_double)
            {
                destination.item_weight_double = 0d;
            }
            return(true);
        }
        public async Task <IActionResult> Get()
        {
            var           method       = "";
            Stopwatch     stopwatch    = Stopwatch.StartNew();
            StringBuilder stringLogger = new StringBuilder();

            try
            {
                var req = Request;
                var res = Response;
                // echo headers
                foreach (var header in req.Headers)
                {
                    res.Headers.Add(header);
                }

                var queryDictionary = Microsoft.AspNetCore.WebUtilities.QueryHelpers.ParseQuery(req.QueryString.Value);
                if (queryDictionary.ContainsKey("method") == false)
                {
                    queryDictionary.Add("method", "plain");
                }

                switch (queryDictionary["method"])
                {
                case "sql-client":

                    method = "sql-client";
                    var stringBuilder = new StringBuilder();

                    using (var conn = new System.Data.SqlClient.SqlConnection(getConnectionstring()))
                    {
                        using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 350 * FROM dbo.[users-test];", conn))
                        {
                            cmd.CommandType = System.Data.CommandType.Text;

                            conn.Open();
                            var reader = cmd.ExecuteReader();

                            while (reader.Read())
                            {
                                Guid id = reader.GetGuid(0);
                                stringBuilder.AppendLine(id.ToString());
                            }
                        }
                    }
                    break;

                case "sql-client-async":

                    method = "sql-client-async";
                    var stringBuilder2 = new StringBuilder();

                    using (var conn = new System.Data.SqlClient.SqlConnection(getConnectionstring()))
                    {
                        using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 350 * FROM dbo.[users-test];", conn))
                        {
                            cmd.CommandType = System.Data.CommandType.Text;

                            conn.Open();
                            var reader = await cmd.ExecuteReaderAsync();

                            while (await reader.ReadAsync())
                            {
                                Guid id = reader.GetGuid(0);
                                stringBuilder2.AppendLine(id.ToString());
                            }
                        }
                    }
                    break;

                default:

                    method = "plain";
                    break;
                }
            }
            catch (System.Exception ex)
            {
                dynamic errorResponse = new System.Dynamic.ExpandoObject();
                errorResponse.Message             = ex.Message;
                errorResponse.StackTrace          = ex.StackTrace;
                errorResponse.Url                 = HttpContext.Request.GetDisplayUrl();
                errorResponse.ElapsedMilliseconds = stopwatch.ElapsedMilliseconds;
                Console.WriteLine(JsonConvert.SerializeObject(errorResponse));
                return(StatusCode(500, errorResponse));
            }

            return(Ok(method + $" took {stopwatch.ElapsedMilliseconds}ms" + Environment.NewLine + stringLogger.ToString()));
        }