/// <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);
                    }
                }
            }
        }
Beispiel #2
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);
        }
Beispiel #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);
        }
        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);
        }
        public async Task Get()
        {
            using (var con = new SqlConnection(connectionString))
            {
                var sproc = SimulatedSelectStatement.Create();

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

                var reader = await cmd.ExecuteReaderAsync();

                Assert.IsTrue(reader.Read());

                Assert.AreEqual(sproc.TestInt, reader.Get<int>("Identifier"));
                Assert.AreEqual(sproc.TestBigInt, reader.Get<long>("BigInt"));
                Assert.AreEqual(sproc.TestBit, reader.Get<bool>("Bit"));
                Assert.AreEqual(sproc.TestDate.Value.Date, reader.Get<DateTime>("Date").Date);
                Assert.AreEqual(sproc.TestDateTime.Value.Date, reader.Get<DateTime>("DateTime").Date);
                Assert.AreEqual(sproc.TestDateTime2.Value.Date, reader.Get<DateTime>("DateTime2").Date);
                Assert.AreEqual(sproc.TestDecimal, reader.Get<decimal>("Decimal"));
                Assert.AreEqual(sproc.TestFloat, reader.Get<float>("Float"));
                Assert.AreEqual(Math.Round((decimal)sproc.TestMoney, 4), reader.Get<decimal>("Money"));
                Assert.AreEqual(sproc.TestNChar, reader.Get<string>("NChar"));
                Assert.AreEqual(sproc.TestNText, reader.Get<string>("NText"));
                Assert.AreEqual(sproc.TestText, reader.Get<string>("Text"));
                Assert.AreEqual(sproc.TestGuid, reader.Get<Guid>("Unique"));
                CollectionAssert.AreEqual(sproc.TestBinary, reader.Get<byte[]>("Binary"));
                CollectionAssert.AreEqual(sproc.TestImage, reader.Get<byte[]>("Image"));
            }
        }
        /// <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();
            }
        }
        private async void ReadPeopleData()
        {
            Console.WriteLine("Reading people data from database");
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = new SqlCommand("SELECT * FROM People", connection);
                await connection.OpenAsync();

                SqlDataReader dataReader = await command.ExecuteReaderAsync();

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

                    if(dataReader["MiddleName"]==null)
                    {
                        Console.WriteLine(formatStringWithoutMiddleName,
                            dataReader["id"],
                            dataReader["FirstName"].ToString(),
                            dataReader["LastName"].ToString());
                    }
                    else
                    {
                        Console.WriteLine(formatStringwithMiddleName,
                            dataReader["id"],
                            dataReader["FirstName"].ToString(),
                            dataReader["MiddleName"].ToString(),
                            dataReader["LastName"].ToString());
                    }
                }
                dataReader.Close();
            }
        }
        public static async Task InsertPurchaseOrderHeaderAsync(string cnStr)
        {
            const string queryString =
                "INSERT INTO Purchasing.PurchaseOrderHeader " +
                "(RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight, ModifiedDate) " +
                "VALUES " +
                "(@RevisionNumber, @Status, @EmployeeID, @VendorID, @ShipMethodID, @OrderDate, @ShipDate, @SubTotal, @TaxAmt, @Freight, @ModifiedDate)";

            var dt = DateTime.UtcNow;

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

                    await cn.OpenAsync().ConfigureAwait(false);
                    await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }
        }
        public async Task DataTableDictionary()
        {
            using (var con = new SqlConnection(connectionString))
            {
                var sproc = SimulatedSelectStatement.Create();

                var cmd = sproc.Build(con);

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

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

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

                var cmd = sproc.Build(con);

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

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

                Assert.IsNotNull(objs);

                var i = 0;
                foreach (var obj in objs)
                {
                    Assert.AreEqual(i, obj["Identifier"]);
                    i++;
                }
            }
        }
        public static async Task EnsureTablesExist(string connectionString)
        {
            var asm = Assembly.GetExecutingAssembly();
            using (var stream = asm.GetManifestResourceStream("iSynaptic.Core.Persistence.AggregateStore.sql"))
            using (var reader = new StreamReader(stream))
            {
                string script = await reader.ReadToEndAsync();
                var matches = _scriptRegex.Matches(script);

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

                    await connection.OpenAsync();

                    foreach(var command in commands)
                        await command.ExecuteNonQueryAsync();
                }
            }
        }
Beispiel #12
0
        public async Task<Person> GetPersonById(int id)
        {
            Person person = null;
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select p.id, p.name, p.description from People as p where p.id=@id";

                    await connection.OpenAsync();

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

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

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

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

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

                connection.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
		public async Task<IEnumerable<string>> Get()
		{
			// Note that the entire DB code is async using async/await
			using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SensorDB"].ConnectionString))
			{
				await connection.OpenAsync();
				using (var command = connection.CreateCommand())
				{
					command.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Sensors'";
					if ((int)(await command.ExecuteScalarAsync()) == 0)
					{
						command.CommandText = "CREATE TABLE Sensors ( SensorName VARCHAR(50) PRIMARY KEY )";
						await command.ExecuteNonQueryAsync();
						command.CommandText = "INSERT INTO Sensors VALUES ( 'Temperature Sensor' )";
						await command.ExecuteNonQueryAsync();
						command.CommandText = "INSERT INTO Sensors VALUES ( 'Velocity Sensor' )";
						await command.ExecuteNonQueryAsync();
					}

					command.CommandText = "SELECT SensorName FROM Sensors";
					using (var reader = await command.ExecuteReaderAsync())
					{
						var result = new List<string>();
						while (await reader.ReadAsync())
						{
							result.Add(reader.GetString(0));
						}

						return result;
					}
				}
			}
		}
Beispiel #15
0
 public async Task<List<WindowsDtls>> GetWindows()
 {
     var constring = @"Data Source='c:\users\u3696174\documents\visual studio 2012\Projects\CLSBforNode\CLSBforNode\App_Data\Database1.sdf'";
     List<WindowsDtls> lst_result = new List<WindowsDtls>();
     using (SqlConnection con=new SqlConnection(constring))
     {
         await con.OpenAsync();
         using (SqlCommand cmd=new SqlCommand())
         {
             cmd.Connection = con;
             cmd.CommandText = @"select * from Windows";
             cmd.CommandType = CommandType.Text;
             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
             DataTable dt = new DataTable();
             adapter.Fill(dt);
             adapter.Dispose();
             con.Close();
            
             WindowsDtls obj_single = null;
             foreach (DataRow dr in dt.Rows)
             {
                 obj_single = new WindowsDtls();
                 obj_single.ID = Convert.ToInt32( dr[0].ToString());
                 obj_single.Name = dr[1].ToString();
                 obj_single.Quantity = dr[2].ToString();
                 obj_single.Price = dr[3].ToString();
                 obj_single.Image = dr[4].ToString();
                 lst_result.Add(obj_single);
                 obj_single = null;
             }
         }
         
     }
     return lst_result;
 }
Beispiel #16
0
        public async Task<HttpResponseMessage> Post(CancellationToken cancellationToken)
        {
            if (!Request.Content.IsMimeMultipartContent())
            {
                throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
            }

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbContext"].ConnectionString))
            {
                await conn.OpenAsync(cancellationToken);

                using (var multipartProvider = new MultipartBlobStreamProvider(conn))
                {
                    var blobProvider = await Request.Content.ReadAsMultipartAsync(multipartProvider, cancellationToken);
                    var firstBlobKey = blobProvider.BlobData.First().BlobKey;

                    if (Request.Headers.Accept.Any(a => a.MediaType == "application/json"))
                    {
                        return Request.CreateResponse(HttpStatusCode.OK, new { fileKey = firstBlobKey });
                    }
                    else
                    {
                        // fix for ie9 not supporting json
                        return Request.CreateResponse(HttpStatusCode.OK, new { fileKey = firstBlobKey }, "text/html");
                    }
                }
            }
        }
        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 async void ShouldNotDeadlock()
        {
            // Not all library code used to have .ConfigureAwait(false);
            // This caused this code to run forever. Async test, opening async connection, running the sync db commands which use the async versions internally. 
            // See these topics for more information about .ConfigureAwait(false);
            // http://stackoverflow.com/questions/13489065/best-practice-to-call-configureawait-for-all-server-side-code/13489639#13489639
            // http://blog.stephencleary.com/2012/07/dont-block-on-async-code.html

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

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

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

        }
Beispiel #19
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 void BeginVerify()
 {
     string connectionString = ConnectionInfoToConnectionString(_connectionInfo);
     _connection = new SqlConnection(connectionString);
     _task = _connection.OpenAsync();
     _task.GetAwaiter().OnCompleted(OnOpenComplete);
 }
        public async Task<List<TaskViewModels>> Get()
        {
            using (SqlConnection db = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["wmsConnection"].ConnectionString))
            {
                await db.OpenAsync();
                SqlCommand command = db.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = "select * from wmwhse1.taskdetail where status <>'9' and tasktype='PK' order by status desc,ORDERKEY desc,TASKDETAILKEY desc ";
                SqlDataReader reader = await command.ExecuteReaderAsync();
                List<TaskViewModels> list = new List<TaskViewModels>();
                while (await reader.ReadAsync())
                {
                    TaskViewModels item = new TaskViewModels();
                    list.Add(item);

                    item.TaskKey = reader["TASKDETAILKEY"].ToString();
                    item.OrderKey = reader["ORDERKEY"].ToString();
                    item.Owner = reader["USERKEY"].ToString();
                    item.Qty = Convert.ToInt32(reader["QTY"]);
                    item.Sku = reader["SKU"].ToString();
                    item.Status = reader["STATUS"].ToString();
                    item.Store = reader["STORERKEY"].ToString();
                    item.StartTime = Convert.ToDateTime(reader["STARTTIME"]);

                }
                db.Close();
                return list;
            }
        }
Beispiel #22
0
        public async Task InsertHttpRequestLogAsync(HttpRequestLog request)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                await conn.OpenAsync();
                var cmd = new SqlCommand("uspInsertRequestLog", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@Id", request.Id);
                cmd.Parameters.AddWithValue("@UserName", request.UserName);
                cmd.Parameters.AddWithValue("@UserIpAddress", request.UserIpAddress);
                cmd.Parameters.AddWithValue("@HttpAction", request.HttpAction);
                cmd.Parameters.AddWithValue("@RequestUrl", request.RequestUrl);
                cmd.Parameters.AddWithValue("@RequestHeader", request.RequestHeader);
                cmd.Parameters.AddWithValue("@RequestBody", request.RequestBody);
                cmd.Parameters.AddWithValue("@UserAgent", request.UserAgent);
                cmd.Parameters.AddWithValue("@DeviceInfo", request.DeviceInfo);
                cmd.Parameters.AddWithValue("@BrowserInfo", request.BrowserInfo);
                cmd.Parameters.AddWithValue("@IsAnonymous", request.IsAnonymous);
                cmd.Parameters.AddWithValue("@IsAuthenticated", request.IsAuthenticated);
                cmd.Parameters.AddWithValue("@IsGuest", request.IsGuest);
                cmd.Parameters.AddWithValue("@IsSystem", request.IsSystem);
                cmd.Parameters.AddWithValue("@RequestTimeStamp", request.RequestTimeStamp);

                await cmd.ExecuteNonQueryAsync();
                conn.Close();
            }
        }
Beispiel #23
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);
        }
		public async Task<int> AddVote(int id)
		{
			using (var connection = new SqlConnection(ConnectionString))
			using (var command = connection.CreateCommand())
			{
				command.CommandText = "INSERT INTO Votes (Id) VALUES (@Id)";
				command.Parameters.AddWithValue("@Id", id);

				await connection.OpenAsync();

				using (var transaction = connection.BeginTransaction())
				{
					command.Transaction = transaction;

					await command.ExecuteNonQueryAsync();

					// simulate db activity & roundtrip
					await Task.Delay(10);

					transaction.Commit();
				}

				IncrementVote(id);

				return 42;
			}
		}
        public static async Task <SystemData.SqlConnection> CreateSystemDataSqlConnectionAsync()
        {
            var sqlConnection = new SystemData.SqlConnection(TestConfiguration.SqlConnectionString);
            await sqlConnection.OpenAsync();

            return(sqlConnection);
        }
            public static async Task CreateDatabaseAsync()
            {
                var conn = ConnectionManager.GetDefaultConnection();

                var databaseName = conn.Connection.DatabaseName;
                var masterConn = conn.Connection.Clone() as Connection;
                masterConn.DatabaseName = "master";

                var sqlConn = new SqlConnection(masterConn.ConnectionString);

                // Create a command object identifying the stored procedure
                using (var cmd = sqlConn.CreateCommand())
                {
                    //
                    // Set the command object so it knows to execute a stored procedure
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText =
                        EmbeddedAssembly.GetFromResources("DatabaseCreatorQuery.sql")
                            .Replace("#DatabaseName", databaseName);
                    //
                    // execute the command
                    try
                    {
                        await sqlConn.OpenAsync();

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

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

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

            }
            finally
            {
                connection.Close();
            }
        }
 public async Task<ActionResult> FileUpload(IEnumerable<HttpPostedFileBase> files)
 {
     var result = new List<FileUpload>();
     foreach (var file in files)
     {
         byte[] data = new byte[file.ContentLength];
         await file.InputStream.ReadAsync(data, 0, file.ContentLength);
         string fileName = Path.GetFileName(file.FileName);
         int Id;
         using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SamplesDb"].ConnectionString))
         {
             using (var comm = new SqlCommand(
                 "INSERT INTO Files (FileName, Data, ContentType)" +
                 " VALUES(@FileName, @Data, @ContentType);" +
                 " SELECT @Id=SCOPE_IDENTITY()", conn))
             {
                 comm.Parameters.AddWithValue("@FileName", fileName);
                 comm.Parameters.AddWithValue("@Data", data);
                 comm.Parameters.AddWithValue("@ContentType", file.ContentType);
                 comm.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.Int)).Direction = System.Data.ParameterDirection.Output;
                 await conn.OpenAsync();
                 await comm.ExecuteNonQueryAsync();
                 Id = (int)comm.Parameters["@Id"].Value;
                 result.Add(new FileUpload { Id = Id, FileName = fileName,ContentType=file.ContentType });
             }
         }
     }
     return View(result);
 }
        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;
                }
            }
        }
Beispiel #30
0
        public async Task <IdentityResult> UpdateAsync(User u)
        {
            //cancellationToken.ThrowIfCancellationRequested();

            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB")))
            {
                var param = new DynamicParameters();
                param.Add("@UserID", u.UserID, DbType.Int32);
                param.Add("@FirstName", u.FirstName, DbType.String);
                param.Add("@LastName", u.LastName, DbType.String);
                param.Add("@TargetCals", u.TargetCals, DbType.Int32);
                param.Add("@TargetMacC", u.TargetMacC, DbType.Int32);
                param.Add("@TargetMacP", u.TargetMacP, DbType.Int32);
                param.Add("@TargetMacF", u.TargetMacF, DbType.Int32);

                await connection.OpenAsync();

                await connection.ExecuteAsync(
                    "dbo.UpdateUser",
                    param,
                    commandType : CommandType.StoredProcedure);
            }

            return(IdentityResult.Success);
        }
Beispiel #31
0
        public async Task ReaderModel()
        {
            using (var con = new SqlConnection(connectionString))
            {
                var sproc = SimulatedSelectStatement.Create();

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

                var reader = await cmd.ExecuteReaderAsync();

                Assert.IsTrue(reader.Read());

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

                Assert.IsNotNull(obj);
                Assert.AreEqual(sproc.TestInt, obj.Identifier);
                Assert.AreEqual(sproc.TestBigInt, obj.BigInt);
                Assert.AreEqual(sproc.TestBit, obj.Bit);
                Assert.AreEqual(sproc.TestDate.Value.Date, obj.Date.Date);
                Assert.AreEqual(sproc.TestDateTime.Value.Date, obj.DateTime.Date);
                Assert.AreEqual(sproc.TestDateTime2.Value.Date, obj.DateTime2.Date);
                Assert.AreEqual(sproc.TestDecimal, obj.Decimal);
                Assert.AreEqual(sproc.TestFloat, obj.Float);
                Assert.AreEqual(Math.Round((decimal)sproc.TestMoney, 4), obj.Money);
                Assert.AreEqual(sproc.TestNChar, obj.NChar.ToString());
                Assert.AreEqual(sproc.TestNText, obj.NText);
                Assert.AreEqual(sproc.TestText, obj.Text);
                CollectionAssert.AreEqual(sproc.TestBinary, obj.Binary);
                CollectionAssert.AreEqual(sproc.TestImage, obj.Image);
                Assert.AreEqual(sproc.TestGuid, obj.Unique);
            }
        }
Beispiel #32
0
        async private void btnGetDataAsynch_Click(object sender, EventArgs e)
        {
          
            using (SqlConnection connection = new SqlConnection())
            {

                connection.ConnectionString = @"Data Source=(localdb)\v11.0;AttachDbFilename=D:\KIN\DB\Shop.mdf;Integrated Security=True";
          
                await connection.OpenAsync();   // асинхронное открытие соединения

                SqlCommand cmdSelect = connection.CreateCommand();
                cmdSelect.CommandText = " WAITFOR DELAY '0:0:05' SELECT * FROM Customers";

                SqlDataReader reader = await cmdSelect.ExecuteReaderAsync();

                listInfo.Items.Clear();
                listInfo.Items.Add("Данные получены в асинхронном режиме");
                while (await reader.ReadAsync())
                {
                    String info = String.Format("{0} {1} {2} {3} {4} {5}", reader[0], reader[1], reader[2], reader[3], reader[4], reader[5]);
                    listInfo.Items.Add(info);
                }

            }
          
        }
        private static async Task Can_use_an_existing_closed_connection_test(bool openConnection)
        {
            var serviceCollection = new ServiceCollection();
            serviceCollection
                .AddEntityFramework()
                .AddSqlServer();

            var serviceProvider = serviceCollection.BuildServiceProvider();

            using (var store = await SqlServerNorthwindContext.GetSharedStoreAsync())
            {
                var openCount = 0;
                var closeCount = 0;
                var disposeCount = 0;

                using (var connection = new SqlConnection(store.Connection.ConnectionString))
                {
                    if (openConnection)
                    {
                        await connection.OpenAsync();
                    }

                    connection.StateChange += (_, a) =>
                        {
                            if (a.CurrentState == ConnectionState.Open)
                            {
                                openCount++;
                            }
                            else if (a.CurrentState == ConnectionState.Closed)
                            {
                                closeCount++;
                            }
                        };
#if !DNXCORE50
                    connection.Disposed += (_, __) => disposeCount++;
#endif

                    using (var context = new NorthwindContext(serviceProvider, connection))
                    {
                        Assert.Equal(91, await context.Customers.CountAsync());
                    }

                    if (openConnection)
                    {
                        Assert.Equal(ConnectionState.Open, connection.State);
                        Assert.Equal(0, openCount);
                        Assert.Equal(0, closeCount);
                    }
                    else
                    {
                        Assert.Equal(ConnectionState.Closed, connection.State);
                        Assert.Equal(1, openCount);
                        Assert.Equal(1, closeCount);
                    }

                    Assert.Equal(0, disposeCount);
                }
            }
        }
Beispiel #34
0
        public async static Task<SqlConnection> GetOpenConnection(string connectionString)
        {
            var connection = new SqlConnection(connectionString);

            await connection.OpenAsync();

            return connection;
        }
Beispiel #35
0
        public async Task <User> FindByIdAsync(string ID, CancellationToken cancellationToken)
        {
            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB")))
            {
                await connection.OpenAsync(cancellationToken);

                return(await connection.QuerySingleOrDefaultAsync <User>("dbo.GetUser @UserID", new { UserID = ID }));
            }
        }
Beispiel #36
0
 public static Task TryOpenAsync(this sql.SqlConnection connection)
 {
     if (connection?.State == System.Data.ConnectionState.Closed)
     {
         return(connection?.OpenAsync());
     }
     else
     {
         return(Task.CompletedTask);
     }
 }
        private async Task OpenWithCheckFastAsync(string checkQueryString, CancellationToken cancellationToken)
        {
            var connectionChecked = false;
            var restoreTriggered  = false;

            while (!connectionChecked)
            {
                cancellationToken.ThrowIfCancellationRequested();
                await underlyingConnection.OpenAsync(cancellationToken).ConfigureAwait(false);

                try {
                    var command = underlyingConnection.CreateCommand();
                    await using (command.ConfigureAwait(false)) {
                        command.CommandText = checkQueryString;
                        _ = await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
                    }
                    connectionChecked = true;
                }
                catch (Exception exception) {
                    if (InternalHelpers.ShouldRetryOn(exception))
                    {
                        if (restoreTriggered)
                        {
                            throw;
                        }
                        var newConnection = new SqlServerConnection(underlyingConnection.ConnectionString);
                        try {
                            underlyingConnection.Close();
                            underlyingConnection.Dispose();
                        }
                        catch { }

                        underlyingConnection = newConnection;
                        restoreTriggered     = true;
                        continue;
                    }

                    throw;
                }
            }
        }
Beispiel #38
0
        public async Task <User> FindByNameAsync(string Username, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB")))
            {
                await connection.OpenAsync(cancellationToken);

                return(await connection.QuerySingleOrDefaultAsync <User>($@"SELECT * FROM [Users]
                    WHERE [Username] = @{nameof(Username)}", new { Username }));
            }
        }
Beispiel #39
0
        public async Task <User> GetUserByIdAsync(int id)
        {
            string Connectionstring = "Data Source=.\\SQLExpress;Initial Catalog=DapperTest;Integrated Security=True";

            using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
            {
                await sqlConnection.OpenAsync();

                var sqlM    = @"SELECT * from threads order by activities desc";
                var resultM = await sqlConnection.QueryAsync <User>(sqlM);

                return(resultM.FirstOrDefault());
            }
        }
Beispiel #40
0
        public async Task <IdentityResult> CreateAsync(User user, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB")))
            {
                await connection.OpenAsync(cancellationToken);

                user.UserID = await connection.QuerySingleAsync <int>($@"INSERT INTO [Users] ([Username], [NormalizedUserName],
                    [PasswordHash])
                    VALUES (@{nameof(User.Username)}, @{nameof(User.NormalizedUserName)},
                     @{nameof(User.PasswordHash)});
                    SELECT CAST(SCOPE_IDENTITY() as int)", user);
            }

            return(IdentityResult.Success);
        }
Beispiel #41
0
        //public static TResult QuerySingle<TResult>(string queryName, DbQueryOption queryOption)
        //{
        //    using (var sqlConnection = new SqlConnection(queryOption.ConnectionID)) {
        //        try {

        //            if (sqlConnection.State != ConnectionState.Open) {
        //                sqlConnection.Open();
        //            }
        //            return (TResult)sqlConnection.QuerySingle<TResult>(queryName, queryOption.ParameterModel, commandType: CommandType.StoredProcedure);

        //        }
        //        catch (Exception) {
        //            throw;
        //        }
        //        //finally {
        //        //    sqlConnection.Close();
        //        //}
        //    }

        //}

        public static async Task <TResult> QuerySingleAsync <TResult>(string queryName, DbQueryOption queryOption)
        {
            using (var sqlConnection = new System.Data.SqlClient.SqlConnection(queryOption.ConnectionID)) {
                try {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        await sqlConnection.OpenAsync();
                    }
                    return(await(Task <TResult>) sqlConnection.QuerySingleAsync <TResult>(queryName, queryOption.ParameterModel, commandType: CommandType.StoredProcedure));
                }
                catch (Exception) {
                    throw;
                }
                //finally {
                //    sqlConnection.Close();
                //}
            }
        }
Beispiel #42
0
 public int DeletePerson(long amka)
 {
     using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB")))
     {
         connection.OpenAsync();
         try
         {
             connection.Execute("dbo.DeletePatient @AMKA", new { AMKA = amka });
             return(1);
         }
         //$"select * from People where LastName='{lastName}'" - SQL injection threat
         catch (Exception ex)
         {
             MessageBox.Show(string.Format("Patient has not been deleted due to :{0}", ex.Message), "Not Deleted");
             return(0);
         }
     }
 }
Beispiel #43
0
        public void EditPerson(long amka, int diagnosis, string comment)
        {
            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB")))
            {
                connection.OpenAsync();
                var person = new List <Patient>();

                person.Add(new Patient {
                    AMKA = amka, Diagnosis = diagnosis, Comment = comment
                });

                try
                {
                    connection.Execute("dbo.EditPatient @AMKA, @Diagnosis, @Comment", person);
                }
                //$"select * from People where LastName='{lastName}'" - SQL injection threat
                catch (Exception ex)
                {
                    MessageBox.Show(string.Format("Patient has not been Updated due to :{0}", ex.Message), "Not Updated");
                }
            }
        }
Beispiel #44
0
        public void InsertPerson(long amka, int sex, float height, float armLength, float legLength, string comment)
        {
            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB")))
            {
                connection.OpenAsync();
                var person = new List <Patient>();

                person.Add(new Patient {
                    AMKA = amka, Sex = sex, Height = height, ArmLength = armLength, LegLength = legLength, Comment = comment
                });

                try
                {
                    connection.Execute("dbo.Patient_Insert @AMKA, @Sex, @Height, @ArmLength, @Leglength, @Comment", person);
                }
                //$"select * from People where LastName='{lastName}'" - SQL injection threat
                catch (Exception ex)
                {
                    MessageBox.Show(string.Format("Patient has not been Imported due to :{0}", ex.Message), "Not Imported");
                }
            }
        }
Beispiel #45
0
        public int DeleteMeasurement(long amka, long pk)
        {
            var measurementid = new List <MeasurementID>();

            measurementid.Add(new MeasurementID {
                AMKA = amka, PK = pk
            });
            using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB")))
            {
                connection.OpenAsync();
                try
                {
                    connection.Execute("dbo.DeleteMeasurement @AMKA, @PK", measurementid);
                    return(1);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(string.Format("Patient has not been deleted due to :{0}", ex.Message), "Not Deleted");
                    return(0);
                }
            }
        }
Beispiel #46
0
 /// <summary>
 /// Open connection the asynchronous.
 /// </summary>
 /// <param name="cancellationToken">The cancellation token.</param>
 /// <returns>void</returns>
 /// <exception cref="System.InvalidOperationException">
 /// The Connection was not Closed.
 /// The Connection current state is Open.
 /// </exception>
 public async Task OpenAsync(System.Threading.CancellationToken cancellationToken)
 {
     //
     // Can not use Lock block for async methods
     await SqlConn.OpenAsync(cancellationToken);
 }
Beispiel #47
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));
        }
Beispiel #48
0
        /// <summary>
        /// <c>InsertExcel</c> inserts an Excel study file into dbo.Measurements and dbo.MeasurementsID.
        /// </summary>
        public void InsertExcel(long amka, float limpLength, string date, string fileName, int neuron, int side, float mLat, float marea = 0, float mamp = 0, float fper = 0, float fRepPer = 0, int rNs = 0, int rN5rep = 0, int fRepsTotal = 0)
        {
            /********** Insert Excel File to dbo.Measurements *************/

            // Create Connection to Excel Workbook
            try
            {
                string constr            = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", fileName);
                System.Data.DataTable dt = null;

                OleDbConnection Econ = new OleDbConnection(constr);

                Econ.Open();
                dt = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                String[] excelSheets = new String[dt.Rows.Count];
                int      i           = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }
                string Query = string.Format("Select * FROM [{0}]", excelSheets[0]); //query for what to select from excel sheet

                Econ.Close();

                Econ.Open();

                DataSet          ds  = new DataSet();
                OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
                Econ.Close();
                oda.Fill(ds);
                DataTable Exceldt = ds.Tables[0];

                //creating object of SqlBulkCopy
                var objbulk = new SqlBulkCopy(Helper.CnnVal("fwavesDB"));
                //assigning Destination table name
                objbulk.DestinationTableName = "dbo.Measurements";
                //Mapping Table column
                string[] columnNames = Exceldt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray();

                if (columnNames.Length >= 5)
                {
                    objbulk.ColumnMappings.Add(columnNames[0], "WAVEID");
                    objbulk.ColumnMappings.Add(columnNames[1], "LATENCY");
                    objbulk.ColumnMappings.Add(columnNames[2], "DURATION");
                    objbulk.ColumnMappings.Add(columnNames[3], "AMPLITUDE");
                    objbulk.ColumnMappings.Add(columnNames[4], "AREA");
                }
                else if (columnNames.Length == 4)
                {
                    objbulk.ColumnMappings.Add(columnNames[0], "WAVEID");
                    objbulk.ColumnMappings.Add(columnNames[1], "LATENCY");
                    objbulk.ColumnMappings.Add(columnNames[3], "AMPLITUDE");
                    objbulk.ColumnMappings.Add(columnNames[4], "AREA");
                }

                /* Computation of FCV */

                object minLatObject;                                                                 // object that contains the FLatMin
                minLatObject = Exceldt.Compute("Min(" + columnNames[1] + ")", string.Empty);
                float fcvMax    = 2 * limpLength * 10 / (Convert.ToSingle(minLatObject) - mLat - 1); // limpLength * 10 to convert it to mm
                float numerator = fcvMax * (Convert.ToSingle(minLatObject) - 1);
                Exceldt.Columns.Add("FCV", typeof(float));
                int notUsedRows = 0;

                foreach (DataRow row in Exceldt.Rows)
                {
                    if (row[columnNames[0]] == DBNull.Value || row[columnNames[1]] == DBNull.Value) //check if WaveID or LATENCY columns are empty
                    {
                        row.Delete();                                                               // if a row is empty, delete it from the DataTable
                        notUsedRows++;                                                              // unfortunately, even if you delete the row Exceldt.Rows.Count still counts this row
                    }
                    else if (row[columnNames[1]] != DBNull.Value)                                   //check Latency column
                    {
                        row["FCV"] = numerator / (Convert.ToSingle(row[columnNames[1]]) - 1);       // computation of FCV for that row
                    }
                }

                objbulk.ColumnMappings.Add("FCV", "FCV");

                /* Insert Datatable Records to DataBase */
                try
                {
                    SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB"));
                    objbulk.WriteToServer(Exceldt);
                    objbulk.Close();

                    /********** Insert Data to dbo.MeasurementsID *************/
                    using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB")))
                    {
                        long lastPK;
                        connection.OpenAsync();
                        lastPK   = (connection.Query <long>("dbo.GetLastPK", CommandType.StoredProcedure).First());
                        lastPK   = lastPK + 1 - Exceldt.Rows.Count + notUsedRows;// Exceldt.Rows.Count contains the empty rows as well
                        fileName = System.IO.Path.GetFileName(fileName);
                        //$"select * from People where LastName='{lastName}'" - SQL injection threat

                        List <AddMeasurement> measurement = new List <AddMeasurement>();
                        measurement.Add(new AddMeasurement {
                            AMKA = amka, Date = date, FileName = fileName, PK = lastPK, Neuron = neuron, Side = side, Mlat = mLat, Marea = marea, Mamp = mamp, Fper = fper, FRepPer = fRepPer, RNs = rNs, RN5Rep = rN5rep, FRepsTotal = fRepsTotal
                        });
                        try
                        {
                            connection.Execute("dbo.MeasurementsInsert @AMKA, @DATE, @FILENAME, @PK, @Neuron, @Side, @Mlat, @Marea, @Mamp, @Fper, @FRepPer, @RNs, @RN5Rep, @FRepsTotal", measurement);
                        }
                        //$"select * from People where LastName='{lastName}'" - SQL injection threat
                        catch (Exception ex)
                        {
                            MessageBox.Show(string.Format("Data has not been Imported due to: {0}", ex.Message), "Not Imported");
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(string.Format("Data has not been Imported due to: {0}", ex.Message), "Not Imported");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("Data has not been Imported due to: {0}", ex.Message), "Not Imported");
            }
        }
        public async Task <InvokeResult> AddItemAsync(DataStreamRecord item)
        {
            var fields = String.Empty;
            var values = String.Empty;

            foreach (var fld in _stream.Fields)
            {
                /* validation should happen long before this point, however if someone manipulated the value, it could be very, very bad
                 * with a SQL injection attack, so error on the side of caution and never let it get through.
                 */
                if (!Validator.Validate(fld).Successful)
                {
                    throw new Exception($"Invalid field name {fld.FieldName}");
                }

                fields += String.IsNullOrEmpty(fields) ? $"{fld.FieldName}" : $",{fld.FieldName}";
                values += String.IsNullOrEmpty(values) ? $"@{fld.FieldName}" : $",@{fld.FieldName}";
            }

            fields += $",{_stream.DeviceIdFieldName},{_stream.TimeStampFieldName}";
            values += $",@{_stream.DeviceIdFieldName},@{_stream.TimeStampFieldName}";

            var sql = $"insert into [{_stream.DBTableName}] ({fields}) values ({values})";

            using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString))
                using (var cmd = new System.Data.SqlClient.SqlCommand(sql, cn))
                {
                    cmd.CommandType = System.Data.CommandType.Text;

                    foreach (var field in _stream.Fields)
                    {
                        object value = System.DBNull.Value;

                        if (item.Data.ContainsKey(field.FieldName))
                        {
                            value = item.Data[field.FieldName];
                            if (value == null)
                            {
                                value = System.DBNull.Value;
                            }
                        }

                        if (value != System.DBNull.Value && field.FieldType.Value == DeviceAdmin.Models.ParameterTypes.GeoLocation)
                        {
                            var geoParts = value.ToString().Split(',');
                            if (geoParts.Count() != 2)
                            {
                                return(InvokeResult.FromError($"Attmept to insert invalid geo code {value}"));
                            }

                            // Note geo codes ares stored HH.MMMMMM,HH.MMMMMM where lat comes first, SQL expects those to come lon then lat
                            var parameter = new SqlParameter($"@{field.FieldName}", $"POINT({geoParts[1]} {geoParts[0]})")
                            {
                                Direction = ParameterDirection.Input,
                            };

                            cmd.Parameters.Add(parameter);
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue($"@{field.FieldName}", value);
                        }
                    }

                    if (String.IsNullOrEmpty(item.Timestamp))
                    {
                        item.Timestamp = DateTime.UtcNow.ToJSONString();
                    }

                    cmd.Parameters.AddWithValue($"@{_stream.TimeStampFieldName}", item.Timestamp.ToDateTime());
                    cmd.Parameters.AddWithValue($"@{_stream.DeviceIdFieldName}", item.DeviceId);

                    await cn.OpenAsync();

                    var insertResult = await cmd.ExecuteNonQueryAsync();
                }

            return(InvokeResult.Success);
        }
        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 <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 static async Task WriteLinksAsync(IDictionary<string, int> zones, ILookup<string, string> aliases)
        {
            var cs = ConfigurationManager.ConnectionStrings["tzdb"].ConnectionString;
            using (var connection = new SqlConnection(cs))
            {
                var command = new SqlCommand("[Tzdb].[AddLink]", connection) { CommandType = CommandType.StoredProcedure };
                command.Parameters.Add("@LinkZoneId", SqlDbType.Int);
                command.Parameters.Add("@CanonicalZoneId", SqlDbType.Int);

                await connection.OpenAsync();

                foreach (var alias in aliases)
                {
                    var canonicalId = zones[alias.Key];
                    foreach (var link in alias)
                    {
                        command.Parameters[0].Value = zones[link];
                        command.Parameters[1].Value = canonicalId;
                        await command.ExecuteNonQueryAsync();
                    }
                }

                connection.Close();
            }
        }
Beispiel #53
-1
        public async Task<Album> GetAlbum(long albumId)
        {
            Album albumAggregate;

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

                if (albumAggregate == null)
                    return null;

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

                albumAggregate.TrackList = trackList;
            }

            return albumAggregate;
        }