Esempio n. 1
1
 public void Cancel()
 {
     var cancellationSource = new CancellationTokenSource();
     using (var cmd = new NpgsqlCommand("SELECT pg_sleep(5)", Conn))
     {
         Task.Factory.StartNew(() =>
                                 {
                                     Thread.Sleep(300);
                                     cancellationSource.Cancel();
                                 });
         var t = cmd.ExecuteNonQueryAsync(cancellationSource.Token);
         Task.WaitAny(t);
         Assert.That(t.IsCanceled);
     }
 }
Esempio n. 2
0
 public async void NonQuery()
 {
     using (var cmd = new NpgsqlCommand("INSERT INTO data (field_int4) VALUES (4)", Conn))
     {
         await cmd.ExecuteNonQueryAsync();
     }
     Assert.That(ExecuteScalar("SELECT field_int4 FROM data"), Is.EqualTo(4));
 }
Esempio n. 3
0
 public async void NonQuery()
 {
     ExecuteNonQuery("CREATE TEMP TABLE data (int INTEGER)");
     using (var cmd = new NpgsqlCommand("INSERT INTO data (int) VALUES (4)", Conn))
     {
         await cmd.ExecuteNonQueryAsync();
     }
     Assert.That(ExecuteScalar("SELECT int FROM data"), Is.EqualTo(4));
 }
Esempio n. 4
0
 public async Task NonQuery()
 {
     using (var conn = OpenConnection())
     {
         conn.ExecuteNonQuery("CREATE TEMP TABLE data (int INTEGER)");
         using (var cmd = new NpgsqlCommand("INSERT INTO data (int) VALUES (4)", conn))
             await cmd.ExecuteNonQueryAsync();
         Assert.That(conn.ExecuteScalar("SELECT int FROM data"), Is.EqualTo(4));
     }
 }
Esempio n. 5
0
        /// <summary>
        /// executes an insert batch sql against a datastore
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="ds"></param>
        /// <param name="insertData"></param>
        /// <returns></returns>
        protected internal static async Task ExecuteInsertBatch(Npgsql.NpgsqlCommand cmd, DataStore ds, List <string> insertData)
        {
            cmd.CommandText = $@"INSERT INTO {ds.DataSource.Schema}.{ds.DataSource.Table}
({string.Join(",", ds.DataSource.Columns.Select(c => c.Name))})
{string.Join($"{Environment.NewLine} UNION ALL ", insertData)}
;";
            await cmd.ExecuteNonQueryAsync();

            cmd.Parameters.Clear();
            insertData.Clear();
        }
Esempio n. 6
0
        /// <summary>
        /// creates an index on a location column if it is present in the data store and of string data type
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="ds"></param>
        /// <returns></returns>
        protected static async Task CreateLocationIndex(Npgsql.NpgsqlCommand cmd, DataStore ds)
        {
            //location col indexing as required
            var locationCol = ds.DataSource.Columns.FirstOrDefault(c => c.Name == "location");

            if (locationCol != null && locationCol.Type == ColumnDataType.String)
            {
                cmd.CommandText = GetCreateIdxSql(ds, "location");
                await cmd.ExecuteNonQueryAsync();
            }
        }
Esempio n. 7
0
        private static async Task TestPostgres()
        {
            try
            {
                using (var cn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=secretsquirrel"))
                {
                    await cn.OpenAsync();

                    using (var cmd = new NpgsqlCommand("CREATE DATABASE simple", cn))
                    {
                        await cmd.ExecuteNonQueryAsync();
                    }
                    System.Console.WriteLine("Created database");
                }
            } catch {}

            try
            {
                using (var cn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=secretsquirrel;Database=simple"))
                {
                    await cn.OpenAsync();

                    using (var cmd = new NpgsqlCommand("CREATE TABLE Starships (Id integer not null, Name varchar(100) not null)", cn))
                    {
                        await cmd.ExecuteNonQueryAsync();
                    }
                    using (var cmd = new NpgsqlCommand("INSERT INTO Starships VALUES (42, 'Heart of Gold')", cn))
                    {
                        await cmd.ExecuteNonQueryAsync();
                    }
                    System.Console.WriteLine("Created table");
                }
            } catch {}
            
            var db = new SimpleData().Open(@"Host=localhost;Username=postgres;Password=secretsquirrel;Database=simple",
                typeof(PostgresAdapter));
            
            var starship = await db.Starships.GetById(42);

            Console.WriteLine(starship.Name);
        }
Esempio n. 8
0
        /// <summary>
        /// executes an upsert sql against a data store
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="ds"></param>
        /// <param name="insertData"></param>
        /// <param name="key"></param>
        /// <param name="skipCols"></param>
        /// <param name="colCnt"></param>
        /// <returns></returns>
        protected internal static async Task ExecuteUpsertBatch(Npgsql.NpgsqlCommand cmd, DataStore ds, List <string> insertData, int colCnt, IEnumerable <string> key, IEnumerable <string> skipCols)
        {
            var colNameRow            = string.Empty;
            var discardColNameRowData = string.Empty;

            if (key?.Any() == true)
            {
                var colsData = new string[colCnt];
                var keyCols  = key.ToArray();
                for (var colIdx = 0; colIdx < colCnt; colIdx++)
                {
                    colsData[colIdx] = colIdx < keyCols.Length ? $"NULL as {keyCols[colIdx]}" : "NULL";
                }

                colNameRow = $"SELECT {string.Join(",", colsData)} UNION ALL";

                discardColNameRowData = $"WHERE {string.Join(" IS NOT NULL AND ", key)} IS NOT NULL";
            }

            cmd.CommandText = $@"INSERT INTO {ds.DataSource.Schema}.{ds.DataSource.Table}
({string.Join(",", ds.DataSource.Columns.Where(c => skipCols == null || !skipCols.Contains(c.Name)).Select(c => c.Name))})

{(key?.Any() == true ? $"SELECT DISTINCT ON ({string.Join(",", key)}) * FROM (" : string.Empty)}

{colNameRow}
{string.Join($"{Environment.NewLine} UNION ALL ", insertData)}

{(key?.Any() == true ? ") as data" : string.Empty)}
{discardColNameRowData}

ON CONFLICT({string.Join(",", key)}) DO UPDATE SET
{GetDoUpdateCols(ds, key, skipCols)}
;";

            await cmd.ExecuteNonQueryAsync();

            cmd.Parameters.Clear();
            insertData.Clear();
        }
Esempio n. 9
0
 protected async Task<int> ExecuteNonQueryAsync(string sql, NpgsqlConnection conn = null)
 {
     if (conn == null)
         conn = Conn;
     using (var cmd = new NpgsqlCommand(sql, conn))
         return await cmd.ExecuteNonQueryAsync();
 }
		public async Task DeleteDriverAsync(Driver theDriver)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand("DELETE from driver where id=:value1", Connection);
				aCommand.Parameters.AddWithValue("value1", theDriver.Id);

				await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
			}
			// no catch here, this is a reference project
			// TODO: add catch and actions here
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task UpdateDriverAsync(Driver theDriver)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"UPDATE driver SET firstname = :value1, lastname = :value2, address = :value3, city = :value4, state = :value5, postalcode = :value6, country = :value7, licensenumber = :value8, licensestate = :value9, customerid = :value10 where id=:value11;", Connection);
				aCommand.Parameters.AddWithValue("value1", theDriver.FirstName);
				aCommand.Parameters.AddWithValue("value2", theDriver.LastName);
				aCommand.Parameters.AddWithValue("value3", theDriver.Address);
				aCommand.Parameters.AddWithValue("value4", theDriver.City);
				aCommand.Parameters.AddWithValue("value5", theDriver.State);
				aCommand.Parameters.AddWithValue("value6", theDriver.PostalCode);
				aCommand.Parameters.AddWithValue("value7", theDriver.Country);
				aCommand.Parameters.AddWithValue("value8", theDriver.LicenseNumber);
				aCommand.Parameters.AddWithValue("value9", theDriver.LicenseState);
				aCommand.Parameters.AddWithValue("value10", theDriver.CustomerId);
				aCommand.Parameters.AddWithValue("value11", theDriver.Id);

				await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
			}
			// no catch here, this is a reference project
			// TODO: add catch and actions here
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task UpdateCustomerAsync(Customer theCustomer)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"UPDATE customer SET name=:value1, allowsadditionaldrivers=:value2, allowsadditions=:value3, hasmaxrentaldays=:value4, maxrentaldays=:value5 where id=:value6;", Connection);
				aCommand.Parameters.AddWithValue("value1", theCustomer.Name);
				aCommand.Parameters.AddWithValue("value2", theCustomer.AllowsAdditionalDrivers);
				aCommand.Parameters.AddWithValue("value3", theCustomer.AllowsAdditions);
				aCommand.Parameters.AddWithValue("value4", theCustomer.HasMaxRentalDays);
				aCommand.Parameters.AddWithValue("value5", theCustomer.MaxRentalDays);
				aCommand.Parameters.AddWithValue("value6", theCustomer.Id);

				await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
			}
			// no catch here, this is a reference project
			// TODO: add catch and actions here
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task UpdateLocationAsync(Location theLocation)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"UPDATE location SET customerid = :value1, name = :value2, address = :value3, city = :value4, state = :value5, postalcode = :value6, country = :value7, latitude = :value8, longitude = :value9 where id=:value10;", Connection);
				aCommand.Parameters.AddWithValue("value1", theLocation.CustomerId);
				aCommand.Parameters.AddWithValue("value2", theLocation.Name);
				aCommand.Parameters.AddWithValue("value3", theLocation.Address);
				aCommand.Parameters.AddWithValue("value4", theLocation.City);
				aCommand.Parameters.AddWithValue("value5", theLocation.State);
				aCommand.Parameters.AddWithValue("value6", theLocation.PostalCode);
				aCommand.Parameters.AddWithValue("value7", theLocation.Country);
				aCommand.Parameters.AddWithValue("value8", theLocation.Latitude);
				aCommand.Parameters.AddWithValue("value9", theLocation.Longitude);
				aCommand.Parameters.AddWithValue("value10", theLocation.Id);

				await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
			}
			// no catch here, this is a reference project
			// TODO: add catch and actions here
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task UpdateUserAsync(User theUser)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"UPDATE appuser SET firstname = :value1, lastname = :value2, email = :value3, customerid = :value4, isemployee = :value5 where id=:value6;", Connection);
				aCommand.Parameters.AddWithValue("value1", theUser.FirstName);
				aCommand.Parameters.AddWithValue("value2", theUser.LastName);
				aCommand.Parameters.AddWithValue("value3", theUser.Email);
				aCommand.Parameters.AddWithValue("value4", theUser.CustomerId);
				aCommand.Parameters.AddWithValue("value5", theUser.IsEmployee);
				aCommand.Parameters.AddWithValue("value6", theUser.Id);

				await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
			}
			// no catch here, this is a reference project
			// TODO: add catch and actions here
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task UpdateAutomobileAsync(Automobile theAutomobile)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"UPDATE automobile SET vin=:value1, vehiclenumber=:value2, name=:value3, class=:value4, style=:value5, color=:value6, manufacturer=:value7, model=:value8, code=:value9, locationid=:value10 where id=:value11;", Connection);
				aCommand.Parameters.AddWithValue("value1", theAutomobile.VIN);
				aCommand.Parameters.AddWithValue("value2", theAutomobile.VehicleNumber);
				aCommand.Parameters.AddWithValue("value3", theAutomobile.Name);
				aCommand.Parameters.AddWithValue("value4", theAutomobile.Class);
				aCommand.Parameters.AddWithValue("value5", theAutomobile.Style);
				aCommand.Parameters.AddWithValue("value6", theAutomobile.Color);
				aCommand.Parameters.AddWithValue("value7", theAutomobile.Manufacturer);
				aCommand.Parameters.AddWithValue("value8", theAutomobile.Model);
				aCommand.Parameters.AddWithValue("value9", theAutomobile.Code);
				aCommand.Parameters.AddWithValue("value10", theAutomobile.LocationId);
				aCommand.Parameters.AddWithValue("value11", theAutomobile.Id);

				await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
			}
			// no catch here, this is a reference project
			// TODO: add catch and actions here
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}