/// <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 #2
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 [email protected]";
            SqlCommand sqlCommand = new SqlCommand(query, connection);
            SqlParameter usernameParameter = new SqlParameter("@username", SqlDbType.NVarChar);
            sqlCommand.Parameters.Add(usernameParameter);
            usernameParameter.Value = username;
            await connection.OpenAsync();

            var userData = new List<string>();

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

            connection.Close();
            //send back userdata list.
            return userData;
        }
Example #3
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);
            }
        }
Example #4
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 [email protected]";
                string IDD = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                cmdSelect.Parameters.AddWithValue("@IDD", IDD);

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

            }
            finally
            {
                connection.Close();
            }
        }
 public void BeginVerify()
 {
     string connectionString = ConnectionInfoToConnectionString(_connectionInfo);
     _connection = new SqlConnection(connectionString);
     _task = _connection.OpenAsync();
     _task.GetAwaiter().OnCompleted(OnOpenComplete);
 }
        public async Task<List<SysObject>> GetSysObjects()
        {
            const string sqlText = "select name as Name, object_id as ObjectId, type as Type, type_desc as TypeDescription from sys.objects";
            using (var connection = new SqlConnection(connectionString))
            using (var command = new SqlCommand(cmdText: sqlText, connection: connection))
            {
                await connection.OpenAsync().ConfigureAwait(continueOnCapturedContext: false);

                var result = new List<SysObject>();

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

                        result.Add(item);
                    }

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

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

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

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

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

                SqlDataReader dataReader = await command.ExecuteReaderAsync();

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

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

            var dt = DateTime.UtcNow;

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

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

                var cmd = sproc.Build(con);

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

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

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

                var cmd = sproc.Build(con);

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

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

                Assert.IsNotNull(objs);

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

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

                    await connection.OpenAsync();

                    foreach(var command in commands)
                        await command.ExecuteNonQueryAsync();
                }
            }
        }
Example #13
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 [email protected]";

                    await connection.OpenAsync();

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

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

            return person;
        }
Example #14
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;
            }
        }
Example #15
0
        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;
        }
        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"));
            }
        }
        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 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;
			}
		}
Example #19
0
 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);
 }
Example #20
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();
            }
        }
        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;
            }
        }
Example #22
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);
                }

            }
          
        }
        /// <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();
            }
        }
Example #24
0
		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;
					}
				}
			}
		}
Example #25
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 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();
                    }
                }
            }
Example #27
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;
 }
        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);
                }
            }
        }
Example #29
0
        public async static Task<SqlConnection> GetOpenConnection(string connectionString)
        {
            var connection = new SqlConnection(connectionString);

            await connection.OpenAsync();

            return connection;
        }
        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();
            }
        }